首頁 > 軟體

查一次left join沒有走索引以及原因分析

2023-03-23 22:06:23

查一次left join沒有走索引的原因

線上有個查詢sql,原來是inner join 查詢沒有問題,後來應業務要求改成left join之後, 查詢時間就暴漲了 需要長達24s

通過explain分析,發現訂單表沒有走索引 ,分析之後解決了,記錄下來。

為了簡潔起見,這裡就將無關的查詢欄位都用*

具體sql如下

SELECT  * 
 from t_item_detail a
 left join t_order_detail d on a.order_code=d.order_code
 left join t_connection b on a.unique_code = b.funds_unique 
 left join t_capital_detail c on b.capital_unique = c.unique_code 
 where item_receipt_disbursement=1 and a.is_deleted=0
  and order_type_code=00901 group by a.unique_code LIMIT 10 

用explain命令分析如下

發現table d 的type為all, rows居然高達20萬行 。

d對應的表為order_detail 表,type為all 說明並沒有走索引。

這裡首先看關聯條件

from t_item_detail a
 left join t_order_detail d on a.order_code=d.order_code

該條件並無問題,然後這兩張表的order_code欄位是否加索引.

兩張表的order_code欄位均有索引。

其次再看, 如果兩個欄位或者兩張表的編碼不同,也會導致索引失效。

但是這兩張表的編碼和欄位編碼也均相同,因此也排除掉。

最後發現,

如果寫成

 explain SELECT  * 
 from t_item_detail a
 left join t_order_detail d on a.order_code=d.order_code  and d.order_type_code=00901
 left join t_connection b on a.unique_code = b.funds_unique 
 left join t_capital_detail c on b.capital_unique = c.unique_code 
 where item_receipt_disbursement=1 and a.is_deleted=0
  group by a.unique_code LIMIT 10 

也就是將原來在where條件的order_type_code=00901 寫到left join的條件後面

d的索引就生效了,所有的索引都生效了。

查詢時間也從原來的24秒 變成了不到1秒。

這是為什麼呢?

其實問題就出在這個 d.order_type_code=00901 這個條件上

當有這個條件時候

全文掃描

沒有這個條件的時候

從sql的執行順序來分析:

SELECT  * 
 from t_item_detail a
 left join t_order_detail d on a.order_code=d.order_code
 left join t_connection b on a.unique_code = b.funds_unique 
 left join t_capital_detail c on b.capital_unique = c.unique_code 
 where item_receipt_disbursement=1 and a.is_deleted=0
  and order_type_code=00901 group by a.unique_code LIMIT 10 

這裡面的執行順序為

  • 1.from
  • 2.on
  • 3.join
  • 4.where
  • 5.select
  • 6.group by
  • 7.limit

寫的順序:select … from… where… group by… having… order by… limit [offset,](rows)

執行順序:from… where…group by… having… select … order by… limit

知道這個,我們再看這個sql

不走索引 有order_type_code條件

SELECT *
from t_item_detail a
left join t_order_detail d on a.order_code=d.order_code
left join t_connection b on a.unique_code = b.funds_unique
left join t_capital_detail c on b.capital_unique = c.unique_code
where item_receipt_disbursement=1 and a.is_deleted=0
and order_type_code=00901 group by a.unique_code LIMIT 10

走索引 沒有order_type_code條件

SELECT *
from t_item_detail a
left join t_order_detail d on a.order_code=d.order_code
left join t_connection b on a.unique_code = b.funds_unique
left join t_capital_detail c on b.capital_unique = c.unique_code
where item_receipt_disbursement=1 and a.is_deleted=0
group by a.unique_code LIMIT 10

和走索引有沒有order_type_code條件

SELECT *
from t_item_detail a
left join t_order_detail d on a.order_code=d.order_code and d.order_type_cod=‘00901'
left join t_connection b on a.unique_code = b.funds_unique
left join t_capital_detail c on b.capital_unique = c.unique_code
where item_receipt_disbursement=1 and a.is_deleted=0
group by a.unique_code LIMIT 10

會發現 在不走索引有order_type_code條件的那個sql中, 在執行到where的時候,需要去找到條件 order_type_code=00901 ,但是order_type_code這個欄位沒有索引,所以資料庫就去對order_detail進行全表掃描。

因此解決方案

就是給order_type_code加上索引,或者給 left join on就加上條件order_type_code=xxx ,直接過濾掉

因此,謹記,大表查詢的時候,where 的條件千萬記得加上索引!!!!

總結

以上為個人經驗,希望能給大家一個參考,也希望大家多多支援it145.com。


IT145.com E-mail:sddin#qq.com