共計 9683 個字符，預計需要花費 25 分鐘才能閱讀完成。
自動寫代碼機器人，免費開通
這篇文章主要介紹了 MySQL 怎樣選擇合適的索引，具有一定借鑒價值，感興趣的朋友可以參考下，希望大家閱讀完這篇文章之后大有收獲，下面讓丸趣 TV 小編帶著大家一起了解一下。
先來看一個栗子
EXPLAIN select * from employees where name a
如果用 name 索引查找數據需要遍歷 name 字段聯合索引樹，然后根據遍歷出來的主鍵值去主鍵索引樹里再去查出最終數據，成本比全表掃描還高。
可以用覆蓋索引優(yōu)化，這樣只需要遍歷 name 字段的聯合索引樹就可以拿到所有的結果。
EXPLAIN select name,age,position from employees where name a
可以看到通過 select 出的字段是覆蓋索引，MySQL 底層使用了索引優(yōu)化。在看另一個 case：
EXPLAIN select * from employees where name zzz
對于上面的這兩種 name a 和 name zzz 的執(zhí)行結果，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 優(yōu)化階段
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 執(zhí)行階段
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 行業(yè)資訊頻道，更多相關知識等著你來學習!
向 AI 問一下細節(jié)
正文完
