MySQL · 答疑解惑 · MySQL 優化器 range 的代價計算 · 數據庫內核月報

本文我們從一個索引選擇的問題出發，來研究一下 MySQL 中 range 代價的計算過程，進而分析這種計算過程中存在的問題。 ## 問題現象第一種情況：situation_unique_key_id ~~~ mysql> show create table cpa_order\G *************************** 1\. row *************************** Table: cpa_order Create Table: CREATE TABLE `cpa_order` ( `cpa_order_id` bigint(20) unsigned NOT NULL, ... `settle_date` date DEFAULT NULL COMMENT, `id` bigint(20) NOT NULL, PRIMARY KEY (`cpa_order_id`), UNIQUE KEY `id` (`id`), KEY `cpao_settle_date_id` (`settle_date`,`id`), ) ENGINE=InnoDB DEFAULT CHARSET=gbk 1 row in set (0.00 sec) mysql> explain select * from cpa_order where settle_date='2015-11-05' and id > 15 \G *************************** 1\. row *************************** id: 1 select_type: SIMPLE table: cpa_order type: ref possible_keys: id,cpao_settle_date_id key: cpao_settle_date_id key_len: 4 ref: const rows: 7 Extra: Using index condition 1 row in set (0.00 sec) ~~~ SQL 語句執行過程可以看出，當 id 為 unique key 的時候，key_len= 4, 不難發現聯合索引只使用了字段 cpao_settle_date_id ，而 id 并沒有使用；第二種情況：situation_without_key_id ~~~ mysql> alter table cpa_order drop index id; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> explain select * from cpa_order where settle_date='2015-11-05' and id > 15 \G （我們稱之為 situation_without_key_id） *************************** 1\. row *************************** id: 1 select_type: SIMPLE table: cpa_order type: range possible_keys: cpao_settle_date_id key: cpao_settle_date_id key_len: 12 ref: NULL rows: 3 Extra: Using index condition 1 row in set (0.00 sec) ~~~ 第三種情況: situation_plain_key_id ~~~ mysql> explain select * from cpa_order where settle_date='2015-11-05' and id > 15 \G （我們稱之為 situation_plain_key_id） *************************** 1\. row *************************** id: 1 select_type: SIMPLE table: cpa_order type: range possible_keys: cpao_settle_date_id,id key: cpao_settle_date_id key_len: 12 ref: NULL rows: 3 Extra: Using index condition 1 row in set (0.01 sec) ~~~ 以上的兩個 SQL 語句在使用索引 cpao_settle_date_id 的時候兩個字段都使用到了，因此過濾性應該更好，我們將上面的3種情況分別稱之為 situation_unique_key_id，situation_without_key_id，situation_plain_key_id，以方便我們分析問題。為什么在 id 為 unique 的時候聯合索引只使用了其中的一個字段而沒有字段 id ? ## 原因分析 MySQL 有一個很好的東東叫 optimizer trace，它提供了 MySQL 執行計劃生成的各個階段的詳細信息，其中索引部分的分析更是詳細，但是由于 optimizer trace 的東西比較多，我們在分析的時候只將本文相關的內容進行展開，optimizer trace 的[詳細使用](https://dev.mysql.com/doc/internals/en/optimizer-tracing.html)。打開并使用 optimizer_trace 功能，觀察situation_unique_key_id 的代價生成過程的： ~~~ mysql> set optimizer_trace="enabled=on"; Query OK, 0 rows affected (0.00 sec) mysql> select * from cpa_order where settle_date='2015-11-05' and id > 15 \G 3 rows in set (0.00 sec) mysql> select * from information_schema.OPTIMIZER_TRACE\G ~~~ 在 range 代價計算后，優化器會選擇一個代價較小的 index 生成一個 read_plan 緩存起來，根據下面的代價計算過程可以看到，索引在代價計算過程中雖然是相等的，但先入為主，選擇的其實是 id 這個索引。 range 部分的代價計算過程： ~~~ "range_scan_alternatives": [ { "index": "id", "ranges": [ "15 < id" ], "index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": false, "index_only": false, "rows": 3, "cost": 4.61, "chosen": true }, { "index": "cpao_settle_date_id", "ranges": [ "2015-11-05 <= settle_date <= 2015-11-05 AND 15 < id" ], "index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": false, "index_only": false, "rows": 3, "cost": 4.61, "chosen": false, "cause": "cost" } ~~~ 表的索引選擇過程，主要是 ref & range 的索引方式的選擇： ~~~ "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`cpa_order`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "cpao_settle_date_id", "rows": 7, "cost": 3.4, "chosen": true }, { "access_type": "range", "rows": 3, "cost": 5.21, "chosen": false } ] }, "cost_for_plan": 3.4, "rows_for_plan": 7, "chosen": true } ] ~~~ 可以看到優化器在比較 ref & range 的代價的時候，ref 的代價更小，所以選擇的是ref，到這里我們覺得選擇 ref 是“合理”的，但是當我們想到聯合索引的作用時，我們應該覺得這是“不正常的”，至少這不應該是最終的索引選擇方式。觀察 situation_without_key_id 的代價及生成過程，其 optimizer_trace 如下： range 部分的代價計算過程： ~~~ "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "cpao_settle_date_id", "ranges": [ "2015-11-05 <= settle_date <= 2015-11-05 AND 15 < id" ], "index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": false, "index_only": false, "rows": 3, "cost": 4.61, "chosen": true } ], "analyzing_roworder_intersect": { "usable": false, "cause": "too_few_roworder_scans" } }, ~~~ 表的索引選擇過程： ~~~ "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`cpa_order`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "cpao_settle_date_id", "rows": 7, "cost": 3.4, "chosen": true }, { "access_type": "range", "rows": 3, "cost": 5.21, "chosen": false } ] }, "cost_for_plan": 3.4, "rows_for_plan": 7, "chosen": true } ] ~~~ 可以看到，由于 where 條件中只有 cpao_settle_date_id & id 部分，索引選擇的仍是ref, 其代價的計算結果與 situation_unique_key_id 中的代價是一致的，但是在 optimizer_trace 后面發現了如下的優化： ~~~ "attaching_conditions_to_tables": { "original_condition": "((`cpa_order`.`settle_date` = '2015-11-05') and (`cpa_order`.`id` > 15))", "attached_conditions_computation": [ { "access_type_changed": { "table": "`cpa_order`", "index": "cpao_settle_date_id", "old_type": "ref", "new_type": "range", "cause": "uses_more_keyparts" } } ~~~ 這里我們不難看出，在計算的結尾處優化器做了個優化，就是把 id 字段也考慮了進來，我們根據 attached_conditions_computation 的提示找到了如下代碼: ~~~ if (tab->type == JT_REF && // 1) !tab->ref.depend_map && // 2) tab->quick && // 3) (uint) tab->ref.key == tab->quick->index && // 4) tab->ref.key_length < tab->quick->max_used_key_length) // 5) { tab->type=JT_ALL; use_quick_range=1; tab->use_quick=QS_RANGE; tab->ref.key= -1; tab->ref.key_parts=0; } ~~~ 結合注釋，我們可以這樣理解： * ref 與 range 使用的是相同的索引； * 當前 table 選擇的索引采用的是ref； * ref key 的使用的長度小于 range 的長度，則優先使用 range。因此，在 situation_without_key_id 時，三個條件都滿足，所以使用了 range 中的聯合索引，那為什么 situation_unique_key_id 沒有使用 id 呢，原因是在range 的代價計算過程中使用的是 id 這個索引，導致 unique id 這個索引與聯合索引 cpao_settle_date_id 并不是同樣的索引，不滿足第一個條件，因此不進行優化。有了上面的分析，我們觀察 situation_plain_key_id 的代價及生成過程，situation_plain_key_id 在 range 的代價計算過程中選擇的是 cpao_settle_date_id 索引，計算過程是將后者的計算結果與前者進行比較，因此即使相等，也是先入為主，其optimizer_trace如下： range 部分的代價計算過程： ~~~ "range_scan_alternatives": [ { "index": "cpao_settle_date_id", "ranges": [ "2015-11-05 <= settle_date <= 2015-11-05 AND 15 < id" ], "index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": false, "index_only": false, "rows": 3, "cost": 4.61, "chosen": true }, { "index": "id", "ranges": [ "15 < id" ], "index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": false, "index_only": false, "rows": 3, "cost": 4.61, "chosen": false, "cause": "cost" } ] ~~~ 表的索引選擇過程： ~~~ "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`cpa_order`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "cpao_settle_date_id", "rows": 7, "cost": 3.4, "chosen": true }, { "access_type": "range", "rows": 3, "cost": 5.21, "chosen": false } ] }, "cost_for_plan": 3.4, "rows_for_plan": 7, "chosen": true } ] ~~~ 結合上面的分析我們發現，ref & range 選擇都是索引 cpao_settle_date_id，因此在最后的選擇階段也會進行索引的優化，與開頭的問題表現相符。 ## range 代價計算過程優化器在索引選擇的過程中會將where 條件、join 條件等信息進行收集，對于非等值的索引會放到 possible keys 中，進行 range 部分的代價計算，對于等值相關字段的索引會進行 ref 部分的代價計算，如果是單表，其主要過程如下： * 調用?`get_key_scans_params`?從已知的索引中選擇一個代價最小的 read_plan，利用 read_plan 生成一個讀表的計劃，緩存至 tab->quick 中； * 在?`best_access_path`?中計算: 1. 全表的代價 2. 如果有覆蓋索引則計算覆蓋索引的代價 3. 如果有quick，則利用一些校驗值計算上一步產生的 range 的代價然后取其中最小的值用做當前表的代價； * 在?`make_join_select`?中對已經生成的執行計劃進行較正，如 situation_plain_key_id 的優化部分。多表的計算過程更為復雜，不在此描述。 ## 問題解答為什么在 id 為 unique 的時候聯合索引只使用了其中的一個字段而沒有字段 id ? 由于 situation_unique_key_id 中在計算 range 的過程中使用的是索引 id 而不是 cpao_settle_date_id，因此不符合最后優化的條件，因此只使用了 cpao_settle_date_id 的前一部分而沒有使用 id，這是優化器在實現過程中的問題。 ## range 代價計算過程可能引起的問題我們已經了解了 range 代價計算的過程，可以發現可能會有以下問題： * 當多個索引得到的代價是相同的，由于先入為主，只能緩存第一個，所以會有索引出錯的問題； * 每一次計算 range 的代價都會將緩存清空，如 order by limit 操作，這樣有可能將之前的索引清空且走錯索引，詳情見?[bug#78993](http://bugs.mysql.com/bug.php?id=78993)。 ## 小結當執行計劃出錯的時候，我們可以有效的利用 optimizer_trace 來進行初步的分析，大部分還是有解的。另外由于執行計劃的內容比較多，從本篇起，小編會盡量將優化器相關的東西給大家介紹一下，主要包括 optimizer_swith 的選項、含義、作用、以及在內核中是如何實現的，達到一起學習的目的。