目录
单列索引 vs 组合索引
- SQL存在多个条件，多个单例索引，会使用索引合并（取交集）
- 如果出现索引合并，往往说明索引不够合理
- 如果SQL暂时没有行能问题，暂时可以不管
- 组合索引要注意索引列顺序 【最左前缀原则】
示例语句
-- type=ALL 全表扫描
-- 在没有索引情况下，平均耗时 0.493s 范围
EXPLAIN
SELECT * FROM salaries
WHERE from_date = '1986-06-26'
and to_date = '1987-06-26';
创建单列索引
-- 创建单例索引后，平均耗时 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, salaries_to_date_index | 可能的索引选择 |
| key | salaries_from_date_index, salaries_to_date_index | 实际选择的索引 |
| Extra | Using intersect(salaries_to_date_index,salaries_from_date_index); Using where | 使用索引交集，使用条件查询 |
OPTIMIZER_TRACE 跟踪
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 结果分析
- 主要部分使用 ## 标记
- rows_estimation 块下的内容，
- 索引、行数、开销
{
"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 */
}
创建组合索引
-- 删除原来单例索引
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关注的字段
-- 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好
对比单例索引查询耗时
-- 单例：0.045s
-- 组合：0.043s
SELECT * FROM salaries
WHERE from_date = '1986-06-26'
and to_date = '1987-06-26';
会发现其实差异并不大，主要是因为数据量不大，索引的交集不大而查询耗时没有太大的差异
OPTIMIZER_TRACE 跟踪
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 结果分析
- 主要部分使用 ## 标记
- rows_estimation 块下的内容，
- 索引、行数、开销
{
"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 */
}
