共计 9683 个字符,预计需要花费 25 分钟才能阅读完成。
自动写代码机器人,免费开通
这篇文章主要介绍了 MySQL 怎样选择合适的索引,具有一定借鉴价值,感兴趣的朋友可以参考下,希望大家阅读完这篇文章之后大有收获,下面让丸趣 TV 小编带着大家一起了解一下。
先来看一个栗子
EXPLAIN select * from employees where name a
如果用 name 索引查找数据需要遍历 name 字段联合索引树,然后根据遍历出来的主键值去主键索引树里再去查出最终数据,成本比全表扫描还高。
可以用覆盖索引优化,这样只需要遍历 name 字段的联合索引树就可以拿到所有的结果。
EXPLAIN select name,age,position from employees where name a
可以看到通过 select 出的字段是覆盖索引,MySQL 底层使用了索引优化。在看另一个 case:
EXPLAIN select * from employees where name zzz
对于上面的这两种 name a 和 name zzz 的执行结果,mysql 最终是否选择走索引或者一张表涉及多个索引,mysql 最终如何选择索引,可以通过 trace 工具来一查究竟,开启 trace 工具会影响 mysql 性能,所以只能临时分析 sql 使用,用完之后需要立即关闭。
SET SESSION optimizer_trace= enabled=on ,end_markers_in_json=on; -- 开启 trace
SELECT * FROM employees WHERE name a ORDER BY position;
SELECT * FROM information_schema.OPTIMIZER_TRACE;
看 trace 字段:
{
steps : [
{
join_preparation : { -- 第一阶段: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 */
},
{
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_indices : [ -- 查询可能使用的索引
{
index : PRIMARY , -- 主键索引
usable : false,
cause : not_applicable
},
{
index : idx_name_age_position , -- 辅助索引
usable : true,
key_parts : [
name ,
age ,
position ,
id
] /* key_parts */
},
{
index : idx_age ,
usable : false,
cause : not_applicable
}
] /* potential_range_indices */,
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 : [
{
access_type : scan ,
rows : 3,
cost : 1.6,
chosen : true,
use_tmp_table : true
}
] /* considered_access_paths */
} /* best_access_path */,
cost_for_plan : 1.6,
rows_for_plan : 3,
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 */
},
{
refine_plan : [
{
table : `employees` ,
access_type : table_scan
}
] /* refine_plan */
},
{
reconsidering_access_paths_for_index_ordering : {
clause : ORDER BY ,
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 */
}
] /* 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, additional_fields
} /* filesort_summary */
}
] /* steps */
} /* join_execution */
}
] /* steps */
}
全表扫描的成本低于索引扫描,索引 MySQL 最终会选择全表扫描。
SELECT * FROM employees WHERE name zzz ORDER BY position;
SELECT * FROM information_schema.OPTIMIZER_TRACE;
steps : [
{
join_preparation : {
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` zzz) order by `employees`.`position`
}
] /* steps */
} /* join_preparation */
},
{
join_optimization : {
select# : 1,
steps : [
{
condition_processing : {
condition : WHERE ,
original_condition : (`employees`.`name` zzz) ,
steps : [
{
transformation : equality_propagation ,
resulting_condition : (`employees`.`name` zzz)
},
{
transformation : constant_propagation ,
resulting_condition : (`employees`.`name` zzz)
},
{
transformation : trivial_condition_removal ,
resulting_condition : (`employees`.`name` zzz)
}
] /* steps */
} /* condition_processing */
},
{
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_indices : [
{
index : PRIMARY ,
usable : false,
cause : not_applicable
},
{
index : idx_name_age_position ,
usable : true,
key_parts : [
name ,
age ,
position ,
id
] /* key_parts */
},
{
index : idx_age ,
usable : false,
cause : not_applicable
}
] /* potential_range_indices */,
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 : [
zzz name
] /* ranges */,
index_dives_for_eq_ranges : true,
rowid_ordered : false,
using_mrr : false,
index_only : false,
rows : 1,
cost : 2.21,
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 : idx_name_age_position ,
rows : 1,
ranges : [
zzz name
] /* ranges */
} /* range_access_plan */,
rows_for_plan : 1,
cost_for_plan : 2.21,
chosen : true
} /* chosen_range_access_summary */
} /* range_analysis */
}
] /* rows_estimation */
},
{
considered_execution_plans : [
{
plan_prefix : [ ] /* plan_prefix */,
table : `employees` ,
best_access_path : {
considered_access_paths : [
{
access_type : range ,
rows : 1,
cost : 2.41,
chosen : true,
use_tmp_table : true
}
] /* considered_access_paths */
} /* best_access_path */,
cost_for_plan : 2.41,
rows_for_plan : 1,
sort_cost : 1,
new_cost_for_plan : 3.41,
chosen : true
}
] /* considered_execution_plans */
},
{
attaching_conditions_to_tables : { original_condition : (`employees`.`name` zzz) ,
attached_conditions_computation : [ ] /* attached_conditions_computation */,
attached_conditions_summary : [
{
table : `employees` ,
attached : (`employees`.`name` zzz)
}
] /* 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 */
},
{
refine_plan : [
{
table : `employees` ,
pushed_index_condition : (`employees`.`name` zzz) ,
table_condition_attached : null,
access_type : range
}
] /* refine_plan */
},
{
reconsidering_access_paths_for_index_ordering : {
clause : ORDER BY ,
index_order_summary : {
table : `employees` ,
index_provides_order : false,
order_direction : undefined ,
index : idx_name_age_position ,
plan_changed : false
} /* index_order_summary */
} /* reconsidering_access_paths_for_index_ordering */
}
] /* steps */
} /* join_optimization */
},
{
join_execution : {
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 : 0,
examined_rows : 0,
number_of_tmp_files : 0,
sort_buffer_size : 200704,
sort_mode : sort_key, additional_fields
} /* filesort_summary */
}
] /* steps */
} /* join_execution */
}
] /* steps */
}
查看 trace 字段可知索引扫描的成本低于全表扫描的成本,所以 MySQL 最终选择索引扫描。
SET SESSION optimizer_trace= enabled=off -- 关闭 tra
感谢你能够认真阅读完这篇文章,希望丸趣 TV 小编分享的“MySQL 怎样选择合适的索引”这篇文章对大家有帮助,同时也希望大家多多支持丸趣 TV,关注丸趣 TV 行业资讯频道,更多相关知识等着你来学习!
向 AI 问一下细节