[TOC] # 目录 ## 单列索引 vs 组合索引 - SQL存在多个条件，多个单例索引，会使用索引合并（取交集） - 如果出现索引合并，往往说明索引不够合理 - 如果SQL暂时没有行能问题，暂时可以不管 - ++组合索引++要注意索引列顺序 【**最左前缀原则**】 ### 示例语句 ```sql -- type=ALL 全表扫描 -- 在没有索引情况下，平均耗时 0.493s 范围 EXPLAIN SELECT * FROM salaries WHERE from_date = '1986-06-26' and to_date = '1987-06-26'; ``` #### 创建单列索引 ```sql -- 创建单例索引后，平均耗时 0.045s CREATE INDEX salaries_from_date_index ON salaries (from_date); CREATE INDEX salaries_to_date_index ON salaries (to_date); ``` ##### EXPLAIN关注的字段 字段 | 值 | 说明 ---|---|--- type | index_merge | 索引合并 possible_keys | salaries_from_date_index, <br> salaries_to_date_index | 可能的索引选择 key | salaries_from_date_index, <br> salaries_to_date_index | 实际选择的索引 Extra | Using intersect(salaries_to_date_index,salaries_from_date_index); <br> Using where | 使用索引交集，使用条件查询 ##### OPTIMIZER_TRACE 跟踪 SQL语句 ```sql -- 开启 OPTIMIZER_TRACE SET OPTIMIZER_TRACE="enabled=on",END_MARKERS_IN_JSON=on; SET optimizer_trace_offset=-30, optimizer_trace_limit=30; -- 查询 salaries，使用索引字段 SELECT * FROM salaries WHERE from_date = '1986-06-26' and to_date = '1987-06-26'; -- 通过 OPTIMIZER_TRACE 查看刚刚 查询 salaries 表的结果 SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE WHERE QUERY LIKE '%salaries%' LIMIT 30; ``` OPTIMIZER_TRACE 结果分析 1. 主要部分使用 ## 标记 1. rows_estimation 块下的内容， 1. 索引、行数、开销 ```json { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select `salaries`.`emp_no` AS `emp_no`,`salaries`.`salary` AS `salary`,`salaries`.`from_date` AS `from_date`,`salaries`.`to_date` AS `to_date` from `salaries` where ((`salaries`.`from_date` = '1986-06-26') and (`salaries`.`to_date` = '1987-06-26'))" } ] /* steps */ } /* join_preparation */ }, { "join_optimization": { "select#": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "((`salaries`.`from_date` = '1986-06-26') and (`salaries`.`to_date` = '1987-06-26'))", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "(multiple equal('1986-06-26', `salaries`.`from_date`) and multiple equal('1987-06-26', `salaries`.`to_date`))" }, { "transformation": "constant_propagation", "resulting_condition": "(multiple equal('1986-06-26', `salaries`.`from_date`) and multiple equal('1987-06-26', `salaries`.`to_date`))" }, { "transformation": "trivial_condition_removal", "resulting_condition": "(multiple equal(DATE'1986-06-26', `salaries`.`from_date`) and multiple equal(DATE'1987-06-26', `salaries`.`to_date`))" } ] /* steps */ } /* condition_processing */ }, { "substitute_generated_columns": { } /* substitute_generated_columns */ }, { "table_dependencies": [ { "table": "`salaries`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "ref_optimizer_key_uses": [ { "table": "`salaries`", "field": "from_date", "equals": "DATE'1986-06-26'", "null_rejecting": false }, { "table": "`salaries`", "field": "to_date", "equals": "DATE'1987-06-26'", "null_rejecting": false } ] /* ref_optimizer_key_uses */ }, { "rows_estimation": [ ## 主要查看 { "table": "`salaries`", "range_analysis": { "table_scan": { "rows": 2709068, ## 行 "cost": 274687 ## 开销 } /* table_scan */, "potential_range_indexes": [ ## 分析这张表所有的索引 { "index": "PRIMARY", ## 主键不可用 "usable": false, "cause": "not_applicable" }, { "index": "salaries_from_date_index", ## 可以的 from_date 索引 "usable": true, "key_parts": [ "from_date", "emp_no" ] /* key_parts */ }, { "index": "salaries_to_date_index", ## 可以的 to_date 索引 "usable": true, "key_parts": [ "to_date", "emp_no", "from_date" ] /* 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 */, "skip_scan_range": { ## MySQL 8.0 后出的 "potential_skip_scan_indexes": [ { "index": "salaries_from_date_index", "usable": false, "cause": "query_references_nonkey_column" }, { "index": "salaries_to_date_index", "usable": false, "cause": "query_references_nonkey_column" } ] /* potential_skip_scan_indexes */ } /* skip_scan_range */, "analyzing_range_alternatives": { ## 最重要的部分 "range_scan_alternatives": [ { "index": "salaries_from_date_index", ## 使用from_date索引 "ranges": [ "0xda840f <= from_date <= 0xda840f" ] /* ranges */, "index_dives_for_eq_ranges": true, "rowid_ordered": true, "using_mrr": false, "index_only": false, "rows": 88, ## 预估扫描 88 行 "cost": 66.155, ## 预估开销 66.155 "chosen": true ## 证明已经在用索引了 }, { "index": "salaries_to_date_index", ## 使用to_date索引 "ranges": [ "0xda860f <= to_date <= 0xda860f" ] /* ranges */, "index_dives_for_eq_ranges": true, "rowid_ordered": true, "using_mrr": false, "index_only": false, "rows": 86, ## 预估扫描 86 行 "cost": 64.667, ## 预估开销 64.667 "chosen": true ## 证明已经在用索引了 } ] /* range_scan_alternatives */, "analyzing_roworder_intersect": { ## 索引合并 "intersecting_indexes": [ ## 有哪些交集索引 { "index": "salaries_to_date_index", "index_scan_cost": 1.1034, "cumulated_index_scan_cost": 1.1034, "disk_sweep_cost": 55.312, "cumulated_total_cost": 56.415, "usable": true, "matching_rows_now": 86, "isect_covering_with_this_index": false, "chosen": true ## salaries_to_date_index 交集索引 }, { "index": "salaries_from_date_index", "index_scan_cost": 1.1061, "cumulated_index_scan_cost": 2.2095, "disk_sweep_cost": 0, "cumulated_total_cost": 2.2095, "usable": true, "matching_rows_now": 0.0028, "isect_covering_with_this_index": false, "chosen": true ## salaries_from_date_index 交集索引 } ] /* intersecting_indexes */, "clustered_pk": { "clustered_pk_added_to_intersect": false, "cause": "no_clustered_pk_index" } /* clustered_pk */, "rows": 1, "cost": 2.2095, ## 求交集的开销 "covering": false, "chosen": true } /* analyzing_roworder_intersect */ } /* analyzing_range_alternatives */, "chosen_range_access_summary": { "range_access_plan": { "type": "index_roworder_intersect", "rows": 1, "cost": 2.2095, "covering": false, "clustered_pk_scan": false, "intersect_of": [ { "type": "range_scan", "index": "salaries_to_date_index", "rows": 86, "ranges": [ "0xda860f <= to_date <= 0xda860f AND 0xda840f <= from_date <= 0xda840f" ] /* ranges */ }, { "type": "range_scan", "index": "salaries_from_date_index", "rows": 88, "ranges": [ "0xda840f <= from_date <= 0xda840f" ] /* ranges */ } ] /* intersect_of */ } /* range_access_plan */, "rows_for_plan": 1, "cost_for_plan": 2.2095, "chosen": true } /* chosen_range_access_summary */ } /* range_analysis */ } ] /* rows_estimation */ }, { "considered_execution_plans": [ { "plan_prefix": [ ] /* plan_prefix */, "table": "`salaries`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "salaries_from_date_index", "rows": 88, "cost": 65.501, "chosen": true }, { "access_type": "ref", "index": "salaries_to_date_index", "rows": 86, "cost": 64.012, "chosen": true }, { "rows_to_scan": 1, "access_type": "range", "range_details": { "used_index": "intersect(salaries_to_date_index,salaries_from_date_index)" } /* range_details */, "resulting_rows": 1, "cost": 2.3095, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "condition_filtering_pct": 100, "rows_for_plan": 1, "cost_for_plan": 2.3095, "chosen": true } ] /* considered_execution_plans */ }, { "attaching_conditions_to_tables": { "original_condition": "((`salaries`.`to_date` = DATE'1987-06-26') and (`salaries`.`from_date` = DATE'1986-06-26'))", "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "`salaries`", "attached": "((`salaries`.`to_date` = DATE'1987-06-26') and (`salaries`.`from_date` = DATE'1986-06-26'))" } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "finalizing_table_conditions": [ { "table": "`salaries`", "original_table_condition": "((`salaries`.`to_date` = DATE'1987-06-26') and (`salaries`.`from_date` = DATE'1986-06-26'))", "final_table_condition ": "((`salaries`.`to_date` = DATE'1987-06-26') and (`salaries`.`from_date` = DATE'1986-06-26'))" } ] /* finalizing_table_conditions */ }, { "refine_plan": [ { "table": "`salaries`" } ] /* refine_plan */ } ] /* steps */ } /* join_optimization */ }, { "join_execution": { "select#": 1, "steps": [ ] /* steps */ } /* join_execution */ } ] /* steps */ } ``` #### 创建组合索引 ```sql -- 删除原来单例索引 ALTER TABLE `employees`.`salaries` DROP INDEX `salaries_from_date_index`, DROP INDEX `salaries_to_date_index` -- 创建组合索引 ALTER TABLE `employees`.`salaries` ADD INDEX `salaries_from_date_to_date_index`(`from_date`, `to_date`) -- 查看 salaries 有哪些索引 SHOW INDEX FROM `employees`.`salaries` ``` ##### EXPLAIN关注的字段 ```sql -- type=ref EXPLAIN SELECT * FROM salaries WHERE from_date = '1986-06-26' and to_date = '1987-06-26'; ``` 字段 | 值 | 说明 ---|---|--- type | ref | 当满足索引的最左前缀规则，或者索引不是主键也不是唯一索引时才会发生。如果使用的索引只会匹配到少量的行，性能也是不错的 possible_keys | salaries_from_date_to_date_index | 可能的索引选择 key | salaries_from_date_to_date_index | 实际选择的索引 Extra | null | 使用索引交集，使用条件查询 > ref会比index_merge好 ##### 对比单例索引查询耗时 ```sql -- 单例：0.045s -- 组合：0.043s SELECT * FROM salaries WHERE from_date = '1986-06-26' and to_date = '1987-06-26'; ``` > 会发现其实差异并不大，主要是因为数据量不大，索引的交集不大而查询耗时没有太大的差异 ##### OPTIMIZER_TRACE 跟踪 SQL语句 ```sql -- 开启 OPTIMIZER_TRACE SET OPTIMIZER_TRACE="enabled=on",END_MARKERS_IN_JSON=on; SET optimizer_trace_offset=-30, optimizer_trace_limit=30; -- 查询 salaries，使用索引字段 SELECT * FROM salaries WHERE from_date = '1986-06-26' and to_date = '1987-06-26'; -- 通过 OPTIMIZER_TRACE 查看刚刚 查询 salaries 表的结果 SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE WHERE QUERY LIKE '%salaries%' LIMIT 30; ``` OPTIMIZER_TRACE 结果分析 1. 主要部分使用 ## 标记 1. rows_estimation 块下的内容， 1. 索引、行数、开销 ```json { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select `salaries`.`emp_no` AS `emp_no`,`salaries`.`salary` AS `salary`,`salaries`.`from_date` AS `from_date`,`salaries`.`to_date` AS `to_date` from `salaries` where ((`salaries`.`from_date` = '1986-06-26') and (`salaries`.`to_date` = '1987-06-26'))" } ] /* steps */ } /* join_preparation */ }, { "join_optimization": { "select#": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "((`salaries`.`from_date` = '1986-06-26') and (`salaries`.`to_date` = '1987-06-26'))", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "(multiple equal('1986-06-26', `salaries`.`from_date`) and multiple equal('1987-06-26', `salaries`.`to_date`))" }, { "transformation": "constant_propagation", "resulting_condition": "(multiple equal('1986-06-26', `salaries`.`from_date`) and multiple equal('1987-06-26', `salaries`.`to_date`))" }, { "transformation": "trivial_condition_removal", "resulting_condition": "(multiple equal(DATE'1986-06-26', `salaries`.`from_date`) and multiple equal(DATE'1987-06-26', `salaries`.`to_date`))" } ] /* steps */ } /* condition_processing */ }, { "substitute_generated_columns": { } /* substitute_generated_columns */ }, { "table_dependencies": [ { "table": "`salaries`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "ref_optimizer_key_uses": [ { "table": "`salaries`", "field": "from_date", "equals": "DATE'1986-06-26'", "null_rejecting": false }, { "table": "`salaries`", "field": "to_date", "equals": "DATE'1987-06-26'", "null_rejecting": false } ] /* ref_optimizer_key_uses */ }, { "rows_estimation": [ ## 主要查看 { "table": "`salaries`", "range_analysis": { "table_scan": { "rows": 2709068, ## 行 vs 单例：2709068 "cost": 273561 ## 开销 vs 单例：274687 } /* table_scan */, "potential_range_indexes": [ { "index": "PRIMARY", "usable": false, "cause": "not_applicable" }, { "index": "salaries_from_date_to_date_index", "usable": true, "key_parts": [ "from_date", "to_date", "emp_no" ] /* 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 */, "skip_scan_range": { "potential_skip_scan_indexes": [ { "index": "salaries_from_date_to_date_index", "usable": false, "cause": "query_references_nonkey_column" } ] /* potential_skip_scan_indexes */ } /* skip_scan_range */, "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "salaries_from_date_to_date_index", "ranges": [ "0xda840f <= from_date <= 0xda840f AND 0xda860f <= to_date <= 0xda860f" ] /* ranges */, "index_dives_for_eq_ranges": true, "rowid_ordered": true, "using_mrr": false, "index_only": false, "rows": 86, ## 预估扫描 86 行 vs 单例：88 "cost": 47.963, ## 预估开销 47.963 vs 单例：66.155 "chosen": true } ] /* range_scan_alternatives */, "analyzing_roworder_intersect": { "usable": false, "cause": "too_few_roworder_scans" } /* analyzing_roworder_intersect */ } /* analyzing_range_alternatives */, "chosen_range_access_summary": { "range_access_plan": { "type": "range_scan", "index": "salaries_from_date_to_date_index", "rows": 86, "ranges": [ "0xda840f <= from_date <= 0xda840f AND 0xda860f <= to_date <= 0xda860f" ] /* ranges */ } /* range_access_plan */, "rows_for_plan": 86, "cost_for_plan": 47.963, "chosen": true } /* chosen_range_access_summary */ } /* range_analysis */ } ] /* rows_estimation */ }, { "considered_execution_plans": [ { "plan_prefix": [ ] /* plan_prefix */, "table": "`salaries`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "salaries_from_date_to_date_index", "rows": 86, "cost": 47.501, "chosen": true }, { "access_type": "range", "range_details": { "used_index": "salaries_from_date_to_date_index" } /* range_details */, "chosen": false, "cause": "heuristic_index_cheaper" } ] /* considered_access_paths */ } /* best_access_path */, "condition_filtering_pct": 100, "rows_for_plan": 86, "cost_for_plan": 47.501, "chosen": true } ] /* considered_execution_plans */ }, { "attaching_conditions_to_tables": { "original_condition": "((`salaries`.`to_date` = DATE'1987-06-26') and (`salaries`.`from_date` = DATE'1986-06-26'))", "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "`salaries`", "attached": "((`salaries`.`to_date` = DATE'1987-06-26') and (`salaries`.`from_date` = DATE'1986-06-26'))" } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "finalizing_table_conditions": [ { "table": "`salaries`", "original_table_condition": "((`salaries`.`to_date` = DATE'1987-06-26') and (`salaries`.`from_date` = DATE'1986-06-26'))", "final_table_condition ": null } ] /* finalizing_table_conditions */ }, { "refine_plan": [ { "table": "`salaries`" } ] /* refine_plan */ } ] /* steps */ } /* join_optimization */ }, { "join_execution": { "select#": 1, "steps": [ ] /* steps */ } /* join_execution */ } ] /* steps */ } ```