一簡介
在檢查某業務資料庫的slowlog 時發現一個慢查詢,查詢時間 1.57s ,檢查表結構 where條件欄位存在正確的組合索引,正確的情況下最佳化器應該選擇組合索引,而非為啥會導致慢查詢呢？且看本文慢慢分析。
二分析
案例中的MySQL資料庫版本 5.6.16 將生產環境的sql做適當修改,where條件不變。讀者朋友可以測試一下其他的版本。

root@rac1 10:48:11>explain select id,
-> gmt_create,
-> gmt_modified,
-> order_id,
-> service_id,
-> seller_id,
-> seller_nick,
-> sale_type
-> from lol
-> where seller_id= 1501204
-> and service_id= 1
-> and sale_type in(3, 4)
-> and use_status in(3, 4, 5, 6)
-> and process_node_id= 6 order by id desc limit 0,20 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: lol
type: index
possible_keys: idx_sellerid,idx_usestatus_saletype,idx_sellerid_saletype,idx_sidustsvidtype
key: PRIMARY
key_len: 8
ref: NULL
rows: 3076
Extra: Using where
1 row in set (0.00 sec)

分析
MySQL選擇的執行計劃利用主鍵進行訪問資料。注意執行計劃中的 access type是index,而index 意味著這個SQL在查詢二級索引的時候,對二級索引進行了全索引掃描，根本沒有進行過濾
這個行為是不合理的,因為where條件中含有 in 查詢,合理的執行計劃的access type應該是range。
我們採用強制索引，看看結果

root@rac1 10:48:07>explain select id,
-> gmt_create,
-> gmt_modified,
-> order_id,
-> service_id,
-> seller_id,
-> seller_nick,
-> sale_type
-> from lol force index(idx_sidustsvidtype)
-> where seller_id= 1501204
-> and service_id= 1
-> and sale_type in(3, 4)
-> and use_status in(3, 4, 5, 6)
-> and process_node_id= 6 order by id desc limit 0,20 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: lol
type: range
possible_keys: idx_sidustsvidtype
key: idx_sidustsvidtype
key_len: 19
ref: NULL
rows: 5178
Extra: Using where; Using filesort
1 row in set (0.00 sec)

分析
強制加上索引之後的執行計劃是符合預期的,執行sql的時間由 1.57s 減少為 0.01s 。因此我們推測是在最佳化器選擇索引的時候出現了問題
結合原始碼和optimize_trace我們發現第一階段最佳化的時候，最佳化器確實選擇了idx_sidustsvidtype 並且選擇採用range訪問,因為sql 語句中含有order by,在optimizer試圖最佳化 order by limit的時候
清空了儲存訪問方式的quick變數(原本儲存的是range,但是被請空),最終發現採用排序索引(這裡是id)的代價高於組合索引(這裡是idx_sidustsvidtype)時,還是選擇了idx_sidustsvidtype
但是悲劇的是這時候正確的訪問方式已經被清空,無法還原,這就是這個的根本成因。
根據分析，我們還可以使用另一種解決方法----去掉 order by 。當然這個對業務所有入侵必須和開發溝通確認sql的結果集是否唯一，如果不唯一還是要使用其他方法。

root@rac1 10:48:15>explain select id,
-> gmt_create,
-> gmt_modified,
-> order_id,
-> service_id,
-> seller_id,
-> seller_nick,
-> sale_type
-> from lol
-> where seller_id= 1501204
-> and service_id= 1
-> and sale_type in(3, 4)
-> and use_status in(3, 4, 5, 6)
-> and process_node_id= 6 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: lol
type: range
possible_keys: idx_sellerid,idx_uts_stp,idx_sid_stpe,idx_sidustsvidtype
key: idx_sidustsvidtype
key_len: 19
ref: NULL
rows: 5178
Extra: Using where
1 row in set (0.00 sec)

三總結
a 修改SQL,新增正確hint。
b 去掉不必要的order by 需要和開發溝通確認是否影響業務邏輯。
c 修改最佳化的bug,保留多個訪問路徑，不清理儲存訪問方式的quick變數,發現orderby 的代價高於組合索引時，可以選擇最優的訪問路徑。
特別感謝的分析，同時也推薦《排序sql升級5.6變慢原因分析》

【MySQL】全索引掃描的bug

相關文章