原文地址：
本文我們從一個索引選擇的問題出發，來研究一下 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,
`name` varchar(10) DEFAULT NULL,
`settle_date` date DEFAULT NULL,
`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)

ERROR:
No query specified
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: 1
Extra: Using where; Using index
1 row in set (0.00 sec)

第二種情況：situation_without_key_id

mysql> alter table cpa_order drop index id;
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: range
possible_keys: cpao_settle_date_id
key: cpao_settle_date_id
key_len: 12
ref: NULL
rows: 1
Extra: Using index condition
1 row in set (0.00 sec)

ERROR:
No query specified

第三種情況: 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 的詳細使用。

開啟並使用 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;
        Empty set (0.00 sec)

        mysql> select * from information_schema.OPTIMIZER_TRACE\G;

1.首先分析situation_without_key_id的過程

1. 刪除id的唯一索引：
2. mysql> alter table cpa_order drop index id;
3. 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;
  Empty set (0.00 sec)
  
  mysql> select * from information_schema.OPTIMIZER_TRACE\G;
  *************************** 1. row ***************************
  QUERY: select * from cpa_order where settle_date='2015-11-05' and id > 15
  TRACE: {
  "steps": [
  {
  "join_preparation": {
  "select#": 1,
  "steps": [
  {
  "expanded_query": "/* select#1 */ select `cpa_order`.`cpa_order_id` AS `cpa_order_id`,`cpa_order`.`name` AS `name`,`cpa_order`.`settle_date` AS `settle_date`,`cpa_order`.`id` AS `id` from `cpa_order` where ((`cpa_order`.`settle_date` = '2015-11-05') and (`cpa_order`.`id` > 15))"
  }
  ]
  }
  },
  {
  "join_optimization": {
  "select#": 1,
  "steps": [
  {
  "condition_processing": {
  "condition": "WHERE",
  "original_condition": "((`cpa_order`.`settle_date` = '2015-11-05') and (`cpa_order`.`id` > 15))",
  "steps": [
  {
  "transformation": "equality_propagation",
  "resulting_condition": "((`cpa_order`.`id` > 15) and multiple equal('2015-11-05', `cpa_order`.`settle_date`))"
  },
  {
  "transformation": "constant_propagation",
  "resulting_condition": "((`cpa_order`.`id` > 15) and multiple equal('2015-11-05', `cpa_order`.`settle_date`))"
  },
  {
  "transformation": "trivial_condition_removal",
  "resulting_condition": "((`cpa_order`.`id` > 15) and multiple equal('2015-11-05', `cpa_order`.`settle_date`))"
  }
  ]
  }
  },
  {
  "table_dependencies": [
  {
  "table": "`cpa_order`",
  "row_may_be_null": false,
  "map_bit": 0,
  "depends_on_map_bits": [
  ]
  }
  ]
  },
  {
  "ref_optimizer_key_uses": [
  {
  "table": "`cpa_order`",
  "field": "settle_date",
  "equals": "'2015-11-05'",
  "null_rejecting": false
  }
  ]
  },
  {
  "rows_estimation": [
  {
  "table": "`cpa_order`",
  "range_analysis": {
  "table_scan": {
  "rows": 1,
  "cost": 3.3
  },
  "potential_range_indices": [
  {
  "index": "PRIMARY",
  "usable": false,
  "cause": "not_applicable"
  },
  {
  "index": "cpao_settle_date_id",
  "usable": true,
  "key_parts": [
  "settle_date",
  "id",
  "cpa_order_id"
  ]
  }
  ],
  "setup_range_conditions": [
  ],
  "group_index_range": {
  "chosen": false,
  "cause": "not_group_by_or_distinct"
  },
8. "analyzing_range_alternatives": { -------range代價計算過程
  "range_scan_alternatives": [
  {
  "index": "cpao_settle_date_id", -----range計算使用的是這個索引
  "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": 1,
  "cost": 2.21,
  "chosen": true
  }
  ],
11. "analyzing_roworder_intersect": {
  "usable": false,
  "cause": "too_few_roworder_scans"
  }
  },
  "chosen_range_access_summary": {
  "range_access_plan": {
  "type": "range_scan",
  "index": "cpao_settle_date_id",
  "rows": 1,
  "ranges": [
  "2015-11-05 <= settle_date <= 2015-11-05 AND 15 < id"
  ]
  },
  "rows_for_plan": 1,
  "cost_for_plan": 2.21,
  "chosen": true
  }
  }
  }
  ]
  },
  {
15. "considered_execution_plans": [ ----索引選擇過程
  {
  "plan_prefix": [
  ],
  "table": "`cpa_order`",
  "best_access_path": {
  "considered_access_paths": [
  {
  "access_type": "ref",
  "index": "cpao_settle_date_id", -----ref使用的是cpao_settle_date_id這個索引，rang與ref使用的是相同的索引
  "rows": 1,
  "cost": 1.2,
  "chosen": true
  },
  {
  "access_type": "range",
  "rows": 1,
  "cost": 2.41,
  "chosen": false
  }
  ]
  },
  "cost_for_plan": 1.2,
  "rows_for_plan": 1,
  "chosen": true
  }
  ]
  },
17. #####################可以看到最佳化器在比較 ref & range 的代價的時候，ref 的代價更小，所以選擇的是ref，到這裡我們覺得選擇 ref 是“合理”的，但是當我們想到聯合索引的作用時，我們應該覺得這是“不正常的”，至少這不應該是最終的索引選擇方式。
3. {
  "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"
  }
  }
  ],
4. ###############在計算的結尾處最佳化器做了個最佳化，就是把 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;
  }
7. "attached_conditions_summary": [
  {
  "table": "`cpa_order`",
  "attached": "((`cpa_order`.`settle_date` = '2015-11-05') and (`cpa_order`.`id` > 15))"
  }
  ]
  }
  },
  {
  "refine_plan": [
  {
  "table": "`cpa_order`",
  "pushed_index_condition": "((`cpa_order`.`settle_date` = '2015-11-05') and (`cpa_order`.`id` > 15))",
  "table_condition_attached": null,
  "access_type": "range"
  }
  ]
  }
  ]
  }
  },
  {
  "join_execution": {
  "select#": 1,
  "steps": [
  ]
  }
  }
  ]
  }
  MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
  INSUFFICIENT_PRIVILEGES: 0
  1 row in set (0.00 sec)
  
  ERROR:
  No query specified
結合註釋，我們可以這樣理解：
- ref 與 range 使用的是相同的索引；
- 當前 table 選擇的索引採用的是ref；
- ref key 的使用的長度小於 range 的長度，則優先使用 range。
因此，在 situation_without_key_id 時，三個條件都滿足，所以使用了 range 中的聯合索引,那為什麼 situation_unique_key_id 沒有使用 id 呢，

2分析situation_unique_key_id 的過程

加上id的唯一索引後
mysql> select * from information_schema.OPTIMIZER_TRACE\G;
*************************** 1. row ***************************
QUERY: select * from cpa_order where settle_date='2015-11-05' and id > 15
TRACE: {
"steps": [
{
"join_preparation": {
"select#": 1,
"steps": [
{
"expanded_query": "/* select#1 */ select `cpa_order`.`cpa_order_id` AS `cpa_order_id`,`cpa_order`.`name` AS `name`,`cpa_order`.`settle_date` AS `settle_date`,`cpa_order`.`id` AS `id` from `cpa_order` where ((`cpa_order`.`settle_date` = '2015-11-05') and (`cpa_order`.`id` > 15))"
}
]
}
},
{
"join_optimization": {
"select#": 1,
"steps": [
{
"condition_processing": {
"condition": "WHERE",
"original_condition": "((`cpa_order`.`settle_date` = '2015-11-05') and (`cpa_order`.`id` > 15))",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "((`cpa_order`.`id` > 15) and multiple equal('2015-11-05', `cpa_order`.`settle_date`))"
},
{
"transformation": "constant_propagation",
"resulting_condition": "((`cpa_order`.`id` > 15) and multiple equal('2015-11-05', `cpa_order`.`settle_date`))"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "((`cpa_order`.`id` > 15) and multiple equal('2015-11-05', `cpa_order`.`settle_date`))"
}
]
}
},
{
"table_dependencies": [
{
"table": "`cpa_order`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
]
}
]
},
{
"ref_optimizer_key_uses": [
{
"table": "`cpa_order`",
"field": "settle_date",
"equals": "'2015-11-05'",
"null_rejecting": false
}
]
},
{
"rows_estimation": [
{
"table": "`cpa_order`",
"range_analysis": {
"table_scan": {
"rows": 1,
"cost": 3.3
},
"potential_range_indices": [
{
"index": "PRIMARY",
"usable": false,
"cause": "not_applicable"
},
{
"index": "id",
"usable": true,
"key_parts": [
"id"
]
},
{
"index": "cpao_settle_date_id",
"usable": true,
"key_parts": [
"settle_date",
"id",
"cpa_order_id"
]
}
],
"setup_range_conditions": [
],
"group_index_range": {
"chosen": false,
"cause": "not_group_by_or_distinct"
},
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "id", ---range代價計算過程，使用的是id這個索引
"ranges": [
"15 < id"
],
"index_dives_for_eq_ranges": true,
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
"rows": 1,
"cost": 2.21,
"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": 1,
"cost": 2.21,
"chosen": false,
"cause": "cost"
}
],
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
}
},
"chosen_range_access_summary": {
"range_access_plan": {
"type": "range_scan",
"index": "id",
"rows": 1,
"ranges": [
"15 < id"
]
},
"rows_for_plan": 1,
"cost_for_plan": 2.21,
"chosen": true
}
}
}
]
},
{
"considered_execution_plans": [
{
"plan_prefix": [
],
"table": "`cpa_order`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "cpao_settle_date_id", -----ref索引的選擇，使用的是cpao_settle_date_id這個索引
"rows": 1,
"cost": 1.2,
"chosen": true
},
{
"access_type": "range",
"rows": 1,
"cost": 2.41,
"chosen": false
}
]
},
"cost_for_plan": 1.2,
"rows_for_plan": 1,
"chosen": true
}
]
},
{
"attaching_conditions_to_tables": {
"original_condition": "((`cpa_order`.`settle_date` = '2015-11-05') and (`cpa_order`.`id` > 15))",
"attached_conditions_computation": [
],
"attached_conditions_summary": [
{
"table": "`cpa_order`",
"attached": "(`cpa_order`.`id` > 15)"
}
]
}
},
{
"refine_plan": [
{
"table": "`cpa_order`",
"pushed_index_condition": "(`cpa_order`.`id` > 15)",
"table_condition_attached": null
}
]
}
]
}
},
{
"join_execution": {
"select#": 1,
"steps": [
]
}
}
]
}
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
INSUFFICIENT_PRIVILEGES: 0
1 row in set (0.00 sec)

ERROR:
No query specified
總和分析過程一的三個條件發現：在range 的代價計算過程中使用的是 id 這個索引，導致 unique id 這個索引與聯合索引 cpao_settle_date_id 並不是同樣的索引，不滿足第一個條件，因此不進行最佳化。

3分析situation_plain_key_id 的過程
有了上面的分析，我們觀察 situation_plain_key_id 的代價及生成過程，situation_plain_key_id 在 range 的代價計算過程中選擇的是 cpao_settle_date_id 索引，計算過程是將後者的計算結果與前者進行比較，因此即使相等，也是先入為主，其optimizer_trace和過程一situation_without_key_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 操作，這樣有可能將之前的索引清空且走錯索引，詳情見。

小結

當執行計劃出錯的時候，我們可以有效的利用 optimizer_trace 來進行初步的分析，大部分還是有解的。另外由於執行計劃的內容比較多，從本篇起，小編會盡量將最佳化器相關的東西給大家介紹一下，主要包括 optimizer_swith 的選項、含義、作用、以及在核心中是如何實現的，達到一起學習的目的。

【Mysql】MySQL · 答疑解惑 · MySQL 最佳化器 range 的代價計算

問題現象

range 代價計算過程

問題解答

range 代價計算過程可能引起的問題

小結

相關文章