原SQL
注意原SQL之索引慢不僅是在左連接配接的時候在on中加入了其它判定比如on a.id=b.id and a.status=1這種
并且在判定in含有時在where子句使用and關聯了兩個id主鍵的where in子句
摘錄:子查詢的結果是未知的，不能作為外層的索引判斷 。【高版本mysql優化器應該會優化成連接配接查詢】
以上就是利用，在SQL 查詢語言執行流程中，優化器執行計劃生成已經索引選擇階段，子查詢的結果無法提供任何的判斷依據，
是以不能作為外層判斷索引的依據，由此導緻外層直接全表掃描了。
-- EXPLAIN
SELECT
tc.commodity_code commodity_code,
ccs.cabinet_id cabinet_id,
ccs.task_id ,
ccs.id id,
ca.id as caid,
tc.id as tcid,
ccs.status as ccsstatus,
ca.status as castatus,
tc.status as tcstatus,
tc.commodity_features commodity_features,
tc.commodity_file_path commodity_file_path,
tc.international_category_name international_category_name,
tc.comment comment,
tc.shelf_life shelf_life,
tc.place_origin place_origin,
tc.unit_value unit_value,
tc.unit_id unit_id,
tc.commodity_flag commodity_flag,
tc.sell_time sell_time,
tc.create_time create_time,
tc.commodity_status commodity_status,
tc.commodity_unit commodity_unit,
tc.commodity_operate_mode commodity_operate_mode,
cc.category_name category_name,
tc.category_id category_id,
tc.commodity_specification commodity_specification,
tc.create_date create_date,
tc.supplier_id supplier_id,
tc.commodity_name commodity_name,
s.supplier_name supplier_name,
ca.cabinet_name cabinet_name,
ca.cabinet_code cabinet_code,
ccs.commodity_purchase_price purchase_price,
ccs.commodity_guide_price commodity_guide_price,
ccs.current_commodity_price current_price,
ccs.commodity_sell_price plan_price,
ccs.check_flag check_flag,
ccs.check_user_id check_user_id,
ccs.check_user_name check_user_name,
ccs.user_id user_id,
ccs.user_name user_name,
ccs.commodity_id commodity_id,
ca.external_code external_code
FROM
t_cabinet_commodity_sell ccs
LEFT JOIN t_cabinet ca ON ccs.cabinet_id = ca.id AND ca.status = 1
LEFT JOIN t_commodity tc ON ccs.commodity_id = tc.id AND tc.status = 1
LEFT JOIN t_supplier s ON tc.supplier_id = s.id AND s.status = 1
LEFT JOIN t_commodity_category cc ON tc.category_id = cc.id AND cc.status = 1
WHERE
ccs.status = 1
AND ccs.task_id = 7757
AND tc.id in
(1, 2, 4, 5, 6, 7, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 29, 30, 31, 32, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 100, 101, 102, 103, 104, 105, 106, 107, 108, 109, 110, 111, 112, 113, 114, 115, 116, 117, 118, 119, 120, 121, 122, 123, 124, 125, 126, 127, 128, 129, 130, 131, 132, 133, 134, 135, 136, 137, 138, 139, 141, 143, 145, 147, 149, 151, 153)
AND ca.id in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30)
ORDER BY ccs.check_flag ASC 上面這個原SQL執行計劃看下來是這樣的
可以看到tc表直接走了全表查詢,沒有走索引(包括主鍵也沒走)
在改善時,通過最左主表的相關判定給定改入後,接下來判定的子表的where子句進行了關聯,但此時資料是備援的,
最後需要判定例如這個例子中tc.id和ca.id is not null
-- EXPLAIN
SELECT
tc.commodity_code commodity_code,
ccs.cabinet_id cabinet_id,
ccs.task_id ,
ccs.id id,
ca.id as caid,
tc.id as tcid,
ccs.status as ccsstatus,
ca.status as castatus,
tc.status as tcstatus,
tc.commodity_features commodity_features,
tc.commodity_file_path commodity_file_path,
tc.international_category_name international_category_name,
tc.comment comment,
tc.shelf_life shelf_life,
tc.place_origin place_origin,
tc.unit_value unit_value,
tc.unit_id unit_id,
tc.commodity_flag commodity_flag,
tc.sell_time sell_time,
tc.create_time create_time,
tc.commodity_status commodity_status,
tc.commodity_unit commodity_unit,
tc.commodity_operate_mode commodity_operate_mode,
cc.category_name category_name,
tc.category_id category_id,
tc.commodity_specification commodity_specification,
tc.create_date create_date,
tc.supplier_id supplier_id,
tc.commodity_name commodity_name,
s.supplier_name supplier_name,
ca.cabinet_name cabinet_name,
ca.cabinet_code cabinet_code,
ccs.commodity_purchase_price purchase_price,
ccs.commodity_guide_price commodity_guide_price,
ccs.current_commodity_price current_price,
ccs.commodity_sell_price plan_price,
ccs.check_flag check_flag,
ccs.check_user_id check_user_id,
ccs.check_user_name check_user_name,
ccs.user_id user_id,
ccs.user_name user_name,
ccs.commodity_id commodity_id,
ca.external_code external_code
FROM
(select ccs0.id,ccs0.commodity_purchase_price,ccs0.commodity_guide_price,ccs0.current_commodity_price,ccs0.task_id,ccs0.commodity_sell_price,ccs0.check_flag,ccs0.check_user_id,ccs0.check_user_name,ccs0.user_id,ccs0.user_name,ccs0.status,
ccs0.commodity_id,ccs0.cabinet_id from t_cabinet_commodity_sell ccs0 where ccs0.status=1 and ccs0.task_id = 7757 )ccs
LEFT JOIN
(select ca0.cabinet_name,ca0.cabinet_code,ca0.external_code,ca0.id,ca0.status from t_cabinet ca0 where ca0.status=1 and ca0.id in
(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30))ca ON ccs.cabinet_id = ca.id
LEFT JOIN
(select tc0.id,tc0.commodity_features,tc0.commodity_file_path,tc0.international_category_name,tc0.comment, tc0.shelf_life,tc0.place_origin,tc0.unit_value,tc0.unit_id,tc0.commodity_flag,tc0.sell_time,tc0.create_time,tc0.commodity_status,tc0.commodity_unit,tc0.commodity_operate_mode,tc0.category_id,tc0.commodity_specification,tc0.create_date,tc0.supplier_id,tc0.commodity_code,tc0.status,
tc0.commodity_name from t_commodity tc0 where tc0.status = 1 and tc0.id in
(1, 2, 4, 5, 6, 7, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 29, 30, 31, 32, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 100, 101, 102, 103, 104, 105, 106, 107, 108, 109, 110, 111, 112, 113, 114, 115, 116, 117, 118, 119, 120, 121, 122, 123, 124, 125, 126, 127, 128, 129, 130, 131, 132, 133, 134, 135, 136, 137, 138, 139, 141, 143, 145, 147, 149, 151, 153)
)tc ON ccs.commodity_id = tc.id
LEFT JOIN
(select s0.supplier_name,s0.id from t_supplier s0 where s0.status=1)s ON tc.supplier_id = s.id
LEFT JOIN
(select cc0.category_name,cc0.id from t_commodity_category cc0 where cc0.status=1)cc ON tc.category_id = cc.id
where ca.id is not null and tc.id is not null
ORDER BY ccs.check_flag ASC 最後的where判定:
where tc.id is not null and ca.id is not null 但此時tc主鍵依舊沒有走索引
這裡ALL并且tc表展示了rows 431條,也就說明了tc表含有數量為431條對一條慢SQL的優化記錄 20220629
這裡隻判定ca.id is not null是非常快的查詢出來,但是那樣的資料還是備援的,
隻有這兩個id都不為null才是最後應該查詢的資料.
之後我懷疑是子表(tc)表的查詢是跟ac資料量不一樣的,并且将這段代碼改改少了id的判定
and tc0.id in
(1, 2, 4, 5, 6, 7, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 29, 30, 31, 32, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 100, 101, 102, 103, 104, 105, 106, 107, 108, 109, 110, 111, 112, 113, 114, 115, 116, 117, 118, 119, 120, 121, 122, 123, 124, 125, 126, 127, 128, 129, 130, 131, 132, 133, 134, 135, 136, 137, 138, 139, 141, 143, 145, 147, 149, 151, 153) 改為了:
and tc0.id in
(1, 2, 4, 5, 6, 7, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 29, 30, 31) 這是看執行計劃中type改為了range區間,走了主鍵索引,但是依舊非常慢
此時看到已經通過主鍵的range判定rows變為了26條(傳入了26個tc的id,并且也能對應到該表)
而這裡tc的id保留至92時,相關tc表走了status的索引,而也沒有走PRIMARY,并且時間依舊非常久(33s)
因為這裡關鍵的是要id主鍵in子句.
在調整tc的id保留至91時,依舊是range,在保留的id到91時的資料量在tc表中為78條
而78占原有tc表資料量比例為
将近有五分之一的資料量時開始走的range
而不管range還是ref(走的tc的status索引)
速度都是要很慢的
而最終回歸到使用到至153的tc的id,
并且從根源上檢視改掉為什麼tc的id沒有走主鍵索引
where tc.id is not null 而看索引不起效中雖然null也是一個很關鍵的點
網上的索引不起效的七字口訣(感覺并不太準确)
- 模：代表模糊查詢。
- 型：代表資料類型。
- 數：代表函數。
- 空：是Null的意思。
- 運：代表運算。
- 最：代表最左原則。
- 快：全表掃描更快的意思。
但是為了便于将判定不為空改為其他方式,這裡使用了if
(本來使用ifnull但是并不好用)
改好後的SQL,最後的where改為
where IF(ca.id is null,1,0) + IF(tc.id is null,1,0) = 0 作為一個判定也作為兩個函數傳回結果的計算,這裡其實最終是走了索引