MySQL 全索引掃描的bug

MySQL 全索引掃描的bug

一 簡介

在檢查某業務資料庫的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

但是悲劇的是這時候正確的訪問方式已經被清空,無法還原,這就是這個 bug#78993  的根本成因。

根據分析，我們還可以使用另一種解決方案----去掉 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升級MySQL 5.6變慢原因分析》 

本文永久更新連結地址：