MySQL進階【五】—— MySQL查詢優化器是如何選擇索引的
MySQL查詢優化器是如何選擇索引的
MySQL的查詢優化器，具有非常強大的功能，那MySQL的查詢優化器是如何選擇索引的呢？
本文只探究查詢優化器選擇索引的大體邏輯，沒有深入到C++原始碼，開發同學瞭解到這個程度足夠了，如果是DBA同學，顯然需要去讀一讀MySQL的C++原始碼
通過MySQL提供的trace工具，我們一起探究下查詢優化器都做了什麼
mysql提供了trace工具，預設是關閉的，可以通過命令將其開啟
mysql> set session optimizer_trace="enabled=on",end_markers_in_json=on; -- 開啟trace工具
Query OK, 0 rows affected (0.00 sec)
mysql> explain select * from employees where name > 'a' order by position;
+----+-------------+-----------+------------+------+-----------------------+------+---------+------+------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+-----------------------+------+---------+------+------+----------+-----------------------------+
| 1 | SIMPLE | employees | NULL | ALL | idx_name_age_position | NULL | NULL | NULL | 3 | 100.00 | Using where; Using filesort |
+----+-------------+-----------+------------+------+-----------------------+------+---------+------+------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> SELECT * FROM information_schema.OPTIMIZER_TRACE; -- 開啟trace
+------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------+-------------------------+
| QUERY | TRACE | MISSING_BYTES_BEYOND_MAX_MEM_SIZE | INSUFFICIENT_PRIVILEGES |
+------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------+-------------------------+
| select * from employees where name > 'a' order by position | {
"steps": [
{
"join_preparation": { -- 第一階段:SQL準備階段，格式化sql
"select#": 1,
"steps": [
{
"expanded_query": "/* select#1 */ select `employees`.`id` AS `id`,`employees`.`name` AS `name`,`employees`.`age` AS `age`,`employees`.`position` AS `position`,`employees`.`hire_time` AS `hire_time` from `employees` where (`employees`.`name` > 'a') order by `employees`.`position`"
}
] /* steps */
} /* join_preparation */
},
{
"join_optimization": { ‐‐ 第二階段:SQL優化階段
"select#": 1,
"steps": [
{
"condition_processing": { ‐‐條件處理
"condition": "WHERE",
"original_condition": "(`employees`.`name` > 'a')",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "(`employees`.`name` > 'a')"
},
{
"transformation": "constant_propagation",
"resulting_condition": "(`employees`.`name` > 'a')"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "(`employees`.`name` > 'a')"
}
] /* steps */
} /* condition_processing */
},
{
"substitute_generated_columns": {
} /* substitute_generated_columns */
},
{
"table_dependencies": [ ‐‐表依賴詳情
{
"table": "`employees`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
] /* depends_on_map_bits */
}
] /* table_dependencies */
},
{
"ref_optimizer_key_uses": [
] /* ref_optimizer_key_uses */
},
{
"rows_estimation": [ ‐‐預估表的訪問成本
{
"table": "`employees`",
"range_analysis": {
"table_scan": { ‐‐全表掃描情況
"rows": 3, ‐‐掃描行數
"cost": 3.7 ‐‐查詢成本
} /* table_scan */,
"potential_range_indexes": [ ‐‐查詢可能使用的索引
{
"index": "PRIMARY", ‐‐主鍵索引
"usable": false,
"cause": "not_applicable"
},
{
"index": "idx_name_age_position", ‐‐輔助索引
"usable": true,
"key_parts": [
"name",
"age",
"position",
"id"
] /* key_parts */
}
] /* potential_range_indexes */,
"setup_range_conditions": [
] /* setup_range_conditions */,
"group_index_range": {
"chosen": false,
"cause": "not_group_by_or_distinct"
} /* group_index_range */,
"analyzing_range_alternatives": { ‐‐分析各個索引使用成本
"range_scan_alternatives": [
{
"index": "idx_name_age_position",
"ranges": [
"a < name" ‐‐索引使用範圍
] /* ranges */,
"index_dives_for_eq_ranges": true,
"rowid_ordered": false, ‐‐使用該索引獲取的記錄是否按照主鍵排序
"using_mrr": false,
"index_only": false, ‐‐是否使用覆蓋索引
"rows": 3, ‐‐索引掃描行數
"cost": 4.61, ‐‐索引使用成本
"chosen": false, ‐‐是否選擇該索引
"cause": "cost"
}
] /* range_scan_alternatives */,
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
} /* analyzing_roworder_intersect */
} /* analyzing_range_alternatives */
} /* range_analysis */
}
] /* rows_estimation */
},
{
"considered_execution_plans": [
{
"plan_prefix": [
] /* plan_prefix */,
"table": "`employees`",
"best_access_path": { ‐‐最優訪問路徑
"considered_access_paths": [ ‐‐最終選擇的訪問路徑
{
"rows_to_scan": 3,
"access_type": "scan",
"resulting_rows": 3, ‐‐訪問型別:為scan，全表掃描
"cost": 1.6,
"chosen": true, ‐‐確定選擇
"use_tmp_table": true
}
] /* considered_access_paths */
} /* best_access_path */,
"condition_filtering_pct": 100,
"rows_for_plan": 3,
"cost_for_plan": 1.6,
"sort_cost": 3,
"new_cost_for_plan": 4.6,
"chosen": true
}
] /* considered_execution_plans */
},
{
"attaching_conditions_to_tables": {
"original_condition": "(`employees`.`name` > 'a')",
"attached_conditions_computation": [
] /* attached_conditions_computation */,
"attached_conditions_summary": [
{
"table": "`employees`",
"attached": "(`employees`.`name` > 'a')"
}
] /* attached_conditions_summary */
} /* attaching_conditions_to_tables */
},
{
"clause_processing": {
"clause": "ORDER BY",
"original_clause": "`employees`.`position`",
"items": [
{
"item": "`employees`.`position`"
}
] /* items */,
"resulting_clause_is_simple": true,
"resulting_clause": "`employees`.`position`"
} /* clause_processing */
},
{
"reconsidering_access_paths_for_index_ordering": {
"clause": "ORDER BY",
"steps": [
] /* steps */,
"index_order_summary": {
"table": "`employees`",
"index_provides_order": false,
"order_direction": "undefined",
"index": "unknown",
"plan_changed": false
} /* index_order_summary */
} /* reconsidering_access_paths_for_index_ordering */
},
{
"refine_plan": [
{
"table": "`employees`"
}
] /* refine_plan */
}
] /* steps */
} /* join_optimization */
},
{
"join_execution": { ‐‐第三階段:SQL執行階段
"select#": 1,
"steps": [
{
"filesort_information": [
{
"direction": "asc",
"table": "`employees`",
"field": "position"
}
] /* filesort_information */,
"filesort_priority_queue_optimization": {
"usable": false,
"cause": "not applicable (no LIMIT)"
} /* filesort_priority_queue_optimization */,
"filesort_execution": [
] /* filesort_execution */,
"filesort_summary": {
"rows": 3,
"examined_rows": 3,
"number_of_tmp_files": 0,
"sort_buffer_size": 200704,
"sort_mode": "<sort_key, packed_additional_fields>"
} /* filesort_summary */
}
] /* steps */
} /* join_execution */
}
] /* steps */
} | 0 | 0 |
+------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------+-------------------------+
1 row in set (0.01 sec)
mysql> set session optimizer_trace="enabled=off"; -- 關閉trace工具
Query OK, 0 rows affected (0.00 sec)
結論:全表掃描的成本低於索引掃描，所以mysql最終選擇全表掃描
總結
MySQL中是否走索引是有查詢優化器來選擇的，通過trace工具我們可以窺探MySQL選擇索引的過程；通過計算全表掃描、使用可能用到的索引等情況的cost成本，最終選擇成本最小的，這就是為什麼有些情況存在索引卻沒有用到索引的原因。
相關文章
- MySQL索引與查詢優化MySql索引優化
- MySQL學習之影響優化器選擇索引因素MySql優化索引
- MySQL-效能優化-索引和查詢優化MySql優化索引
- MySQL 索引及查詢優化總結MySql索引優化
- MySQL索引原理及慢查詢優化MySql索引優化
- 《MySQL 進階篇》十五：索引最佳化和查詢最佳化MySql索引
- MySQL 優化五（關聯查詢子查詢以及 in 的效率問題）（高階篇）MySql優化
- mysql效能優化-慢查詢分析、優化索引和配置MySql優化索引
- MySQL 的查詢優化MySql優化
- 談談MYSQL索引是如何提高查詢效率的MySql索引
- MySQL查詢優化MySql優化
- 如何選擇普通索引和唯一索引《死磕MySQL系列 五》索引MySql
- [玩轉MySQL之六]MySQL查詢優化器MySql優化
- 《MySQL慢查詢優化》之SQL語句及索引優化MySql優化索引
- MySQL進階篇(03)：合理的使用索引結構和查詢MySql索引
- MySQL查詢索引的選擇性、索引欄位、註釋等基本資訊的SQLMySql索引
- MySQL 查詢索引的選擇性、索引欄位、註釋等基本資訊的SQLMySql索引
- 三高Mysql - Mysql索引和查詢優化（偏理論部分）MySql索引優化
- 三高Mysql - Mysql索引和查詢優化（偏實戰部分）MySql索引優化
- MySQL 慢查詢優化MySql優化
- MySQL優化COUNT()查詢MySql優化
- 優化-mysql子查詢索引失效問題解決優化MySql索引
- MySQL索引的最左字首原理與查詢的相關優化MySql索引優化
- 理解索引（中）：MySQL查詢過程和高階查詢索引MySql
- 五分鐘，讓你明白MySQL是怎麼選擇索引《死磕MySQL系列 六》MySql索引
- mysql查詢優化檢查 explainMySql優化AI
- MySQL子查詢的優化薦MySql優化
- MySQL調優之查詢優化MySql優化
- mysql千萬級資料量根據索引優化查詢速度MySql索引優化
- Mysql優化系列之——優化器對子查詢的處理MySql優化
- MySQL查詢優化利刃-EXPLAINMySql優化AI
- MySQL分頁查詢優化MySql優化
- mysql關聯查詢優化MySql優化
- 提高mysql千萬級大資料SQL查詢優化30條經驗（Mysql索引優化注意）MySql大資料優化索引
- 一文讀懂MySQL的索引結構及查詢優化MySql索引優化
- MySQL字首索引和索引選擇性MySql索引
- MySQL索引優化MySql索引優化
- mysql優化索引MySql優化索引