## 問題描述 bug 觸發條件如下： 1. 優化器先選擇了 where 條件中字段的索引，該索引過濾性較好； 2. SQL 中必須有 order by limit 從而引導優化器嘗試使用 order by 字段上的索引進行優化，最終因代價問題沒有成功。 ### 復現case 表結構 ~~~ create table t1( id int auto_increment primary key, a int, b int, c int, key iabc (a, b, c), key ic (c) ) engine = innodb; ~~~ 構造數據 ~~~ insert into t1 select null,null,null,null; insert into t1 select null,null,null,null from t1; insert into t1 select null,null,null,null from t1; insert into t1 select null,null,null,null from t1; insert into t1 select null,null,null,null from t1; insert into t1 select null,null,null,null from t1; update t1 set a = id / 2, b = id / 4, c = 6 - id / 8; ~~~ 觸發SQL ~~~ mysql> explain select id from t1 where a<3 and b in (1, 13) and c>=3 order by c limit 2\G *************************** 1\. row *************************** id: 1 select_type: SIMPLE table: t1 type: index possible_keys: iabc,ic key: iabc key_len: 15 ref: NULL rows: 32 Extra: Using where; Using index; Using filesort ~~~ 使用 force index 可以選擇過濾性好的索引 ~~~ mysql> explain select id from t1 force index(iabc) where a<3 and b in (1, 13) and c>=3 order by c limit 2\G *************************** 1\. row *************************** id: 1 select_type: SIMPLE table: t1 type: range possible_keys: iabc key: iabc key_len: 5 ref: NULL rows: 3 Extra: Using where; Using index; Using filesort ~~~ ## 問題分析 optimizer_trace 可以幫助分析這個問題。 SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE\G ~~~ "range_scan_alternatives": [ { "index": "iabc", "ranges": [ "NULL < a < 3" ], "index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": false, "index_only": true, "rows": 3, "cost": 1.6146, "chosen": true }, { "index": "ic", "ranges": [ "3 <= c" ], "index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": false, "index_only": false, "rows": 17, "cost": 21.41, "chosen": false, "cause": "cost" } ], ~~~ range_scan_alternatives 計算 range_scan，各個索引的開銷，從上面的結果可以看出，聯合索引 iabc 開銷較小，應該選擇 iabc。 ~~~ "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`t1`", "best_access_path": { "considered_access_paths": [ { "access_type": "range", "rows": 3, "cost": 2.2146, "chosen": true } ] }, "cost_for_plan": 2.2146, "rows_for_plan": 3, "chosen": true } ] ~~~ considered_execution_plans 表索引選擇過程，access_type 是 range，rows_for_plan=3，到這里為止，執行計劃還是符合預期的。 ~~~ { "clause_processing": { "clause": "ORDER BY", "original_clause": "`t1`.`c`", "items": [ { "item": "`t1`.`c`" } ], "resulting_clause_is_simple": true, "resulting_clause": "`t1`.`c`" } }, { "refine_plan": [ { "table": "`t1`", "access_type": "index_scan" } ] }, { "reconsidering_access_paths_for_index_ordering": { "clause": "ORDER BY", "index_order_summary": { "table": "`t1`", "index_provides_order": false, "order_direction": "undefined", "index": "unknown", "plan_changed": false } } } ~~~ clause_processing 用于簡化 order by，經過 clause_processing access_type 變成 index_scan（全索引掃描，過濾性較range差），此時出現了和預期不符的結果。 因此可以推測優化器試圖優化 order by 時出現了錯誤： * 第一階段，優化器選擇了索引 iabc，采用 range 訪問； * 第二階段，優化器試圖進一步優化執行計劃，使用 order by 的列訪問，并清空了第一階段的結果； * 第三階段，優化器發現使用 order by 的列訪問，代價比第一階段的結果更大，但是第一階段結果已經被清空了，無法還原，于是選擇了代價較大的訪問方式（index_scan），觸發了bug。 ## 問題解決 1. 我們在索引優化函數`SQL_SELECT::test_quick_select`?最開始的時候保存訪問計劃變量（quick）； 2. 在索引沒變的時候，還原這個變量； 3. 在索引發生改變的時候，刪除這個變量。 在不修改 mysql 源碼的情況下，可以通過 force index 強制指定索引規避這個bug。 `SQL_SELECT::test_quick_select`?調用棧如下 ~~~ #0 SQL_SELECT::test_quick_select #1 make_join_select #2 JOIN::optimize #3 mysql_execute_select #4 mysql_select #5 mysql_explain_unit #6 explain_query_expression #7 execute_sqlcom_select #8 mysql_execute_command #9 mysql_parse #10 dispatch_command #11 do_command ~~~