案例
查询在长沙的女性VIP顾客
sql语句:select * from customers1s where city=“长沙” and gender=0;
一、开启优化器
开启trace查看优化器
set optimizer_trace="enabled=on";--开启trace查看优化器的结果
set end_markers_in_json=on;--增加注释
show WARNINGS\G; //分析上面执行语句,底层的的重写
select * from information_schema.optimizer_trace \G;--查询打印执行计划
执行优化的sql语句
select id,`name`,city,monthsalary,gender from customers1s where city=“长沙” and gender=0 and monthsalary=99;
二、优化器执行过程解析
2.1 优化器执行过程与解析注释
*************************** 1. row ***************************
QUERY: select id,name
,city,monthsalary,gender from customers1s where city=“长沙” and gender=0 and monthsalary=99
TRACE: {
“steps”: [
{
##join_preparation展示了准备阶段的执行过程
“join_preparation”: {
“select#”: 1,
“steps”: [
{
##重写查询语句,加库名称 列出字段及别名
“expanded_query”: "/* select#1 / select customers1s
.id
AS id
,customers1s
.name
AS name
,customers1s
.city
AS city
,customers1s
.monthsalary
AS monthsalary
,customers1s
.gender
AS gender
from customers1s
where ((customers1s
.city
= ‘长沙’) and (customers1s
.gender
= 0) and (customers1s
.monthsalary
= 99))"
}
] / steps /
} / join_preparation */
},
{
##join_optimization:展示了优化阶段的执行过程,是分析OPTIMIZER TRACE的重点
“join_optimization”: {
“select#”: 1,
“steps”: [
{
/condition_processing:该段用来做条件处理,主要对WHERE条件进行优化处理。/
“condition_processing”: {
“condition”: “WHERE”, /*优化对象类型,如where,having */
#优化前的原始语句
“original_condition”: “((customers1s
.city
= ‘长沙’) and (customers1s
.gender
= 0) and (customers1s
.monthsalary
= 99))”,
#steps:主要包括三步, transformation:转换类型句 resulting_condition:转换之后的结果输出
“steps”: [
{
/*等值条件 equality_propagation(等值条件句转换) */
“transformation”: “equality_propagation”,
“resulting_condition”: “((customers1s
.city
= ‘长沙’) and (customers1s
.monthsalary
= 99) and multiple equal(0, customers1s
.gender
))”
},
{
/*constant_propagation(常量条件句转换)无意义的条件,移除,如1=1 */
“transformation”: “constant_propagation”,
“resulting_condition”: “((customers1s
.city
= ‘长沙’) and (customers1s
.monthsalary
= 99) and multiple equal(0, customers1s
.gender
))”
},
{
/*trivial_condition_removal(无效条件移除的转换)条件字段数据类型转化 如 gender
= ‘0’ => gender
= 0 /
“transformation”: “trivial_condition_removal”,
“resulting_condition”: “((customers1s
.city
= ‘长沙’) and (customers1s
.monthsalary
= 99) and multiple equal(0, customers1s
.gender
))”
}
] / steps /
} / condition_processing /
},
{
#substitute_generated_columns用于替换虚拟生成列
“substitute_generated_columns”: {
} / substitute_generated_columns /
},
{
/table_dependencies:分析表之间的依赖关系/
“table_dependencies”: [
{
“table”: “customers1s
”,#涉及的表名,如果有别名,也会展示出来
“row_may_be_null”: false,#行是否可能为NULL,这里是指JOIN操作之后,这张表里的数据是不是可能为 NULL。如果语句中使用了LEFT JOIN,则后一张表的row_may_be_null会显示为true
“map_bit”: 0,#表的映射编号,从0开始递增
#depends_on_map_bits:依赖的映射表。主要是当使用STRAIGHT_JOIN强行控制连接顺序或者LEFT JOIN/RIGHT JOIN有顺序差别时,会在depends_on_map_bits中展示前置表的map_bit值。
“depends_on_map_bits”: [
] / depends_on_map_bits /
}
] / table_dependencies /
},
{
#ref_optimizer_key_uses:列出所有可用的ref类型的索引。如果使用了组合索引的多个部分,(例 如本例,用到了index(from_date, to_date) 的多列索引),则会在ref_optimizer_key_uses下列 出多个元素,每个元素中会列出ref使用的索引及对应值.
“ref_optimizer_key_uses”: [
{
“table”: “customers1s
”,
“field”: “gender”,
“equals”: “0”,
“null_rejecting”: false
},
{
“table”: “customers1s
”,
“field”: “city”,
“equals”: “‘长沙’”,
“null_rejecting”: false
}
] / ref_optimizer_key_uses /
},
{
#rows_estimation:顾名思义,用于估算需要扫描的记录数。
“rows_estimation”: [
{
“table”: “customers1s
”,#表名
“range_analysis”: {
table_scan:如果全表扫描的话,需要扫描多少行(row,5073417),以及需要的代价(cost, 542989)
“table_scan”: {
“rows”: 5073417,#扫描行数
“cost”: 542989 #性能
} / table_scan /,
#可能使用到的索引.列出表中所有的索引并分析其是否可用。如果不可用的话,会列出不可用的原因是什么;如果可用会列出索引中可用的字段
“potential_range_indexes”: [
{
“index”: “PRIMARY”,
“usable”: false,
“cause”: “not_applicable”
},
{
“index”: “idx_gender_city_name_monthsalary_yearbonus”,
“usable”: true,
“key_parts”: [
“gender”,
“city”,
“name”,
“monthsalary”,
“yearbonus”,
“id”
] / key_parts /
},
{
“index”: “idx_name_photo”,
“usable”: false,
“cause”: “not_applicable”
},
{
“index”: “testfulltext”,
“usable”: false,
“cause”: “not_applicable”
}
] / potential_range_indexes */,
“best_covering_index_scan”: {
“index”: “idx_gender_city_name_monthsalary_yearbonus”,
“cost”: 605355,
“chosen”: false,
“cause”: “cost”
} /* best_covering_index_scan /,
#setup_range_conditions:如果有可下推的条件,则带条件考虑范围查询
“setup_range_conditions”: [
] / setup_range_conditions /,
#group_index_range:当使用了GROUP BY或DISTINCT时,是否有合适的索引可用。当未使用GROUP BY或DISTINCT时,会显示chosen=false, cause=not_group_by_or_distinct;如使用了GROUP BY或 DISTINCT,但是多表查询时,会显示chosen=false,cause =not_single_table。其他情况下会尝试 分析可用的索引(potential_group_range_indexes)并计算对应的扫描行数及其所需代价
“group_index_range”: {
“chosen”: false,
“cause”: “not_group_by_or_distinct”
} / group_index_range /,
#analyzing_range_alternatives:分析各个索引的使用成本
“analyzing_range_alternatives”: {
#range_scan_alternatives:range扫描分析
“range_scan_alternatives”: [
{
“index”: “idx_gender_city_name_monthsalary_yearbonus”,#索引
#range扫描的条件范围
“ranges”: [
“0 <= gender <= 0 AND 长沙 <= city <= 长沙”
] / ranges /,
#index_dives_for_eq_ranges:是否使用了index dive,该值会被参数 eq_range_index_dive_limit变量值影响
“index_dives_for_eq_ranges”: true,
“rowid_ordered”: false,#rowid_ordered:该range扫描的结果集是否根据PK值进行排序
“using_mrr”: false,#是否使用了mrr
“index_only”: true,#表示是否使用了覆盖索引
“rows”: 471868,#扫描的行数
“cost”: 56304,#索引的使用成本
“chosen”: true #表示是否使用了该索引
}
] / range_scan_alternatives /,
#analyzing_roworder_intersect:分析是否使用了索引合并(index merge),如果未使用, 会在cause中展示原因;如果使用了索引合并,会在该部分展示索引合并的代价。
“analyzing_roworder_intersect”: {
“usable”: false,
“cause”: “too_few_roworder_scans”
} / analyzing_roworder_intersect /
} / analyzing_range_alternatives /,
#chosen_range_access_summary:在前一个步骤中分析了各类索引使用的方法及代价,得出了 一定的中间结果之后,在summary阶段汇总前一阶段的中间结果确认最后的方案
“chosen_range_access_summary”: {
#range_access_plan:range扫描最终选择的执行计划。
“range_access_plan”: {
“type”: “range_scan”,#展示执行计划的type,如果使用了索引合并,则会显示index_roworder_intersect
“index”: “idx_gender_city_name_monthsalary_yearbonus”,#索引名
“rows”: 471868,#扫描的行数
#range扫描的条件范围
“ranges”: [
“0 <= gender <= 0 AND 长沙 <= city <= 长沙”
] / ranges /
} / range_access_plan /,
“rows_for_plan”: 471868,#该执行计划的扫描行数
“cost_for_plan”: 56304,#该执行计划的执行代价
“chosen”: true #是否选择该执行计划
} / chosen_range_access_summary /
} / range_analysis /
}
] / rows_estimation /
},
{
#considered_execution_plans:负责对比各可行计划的开销,并选择相对最优的执行计划。
“considered_execution_plans”: [
{
#plan_prefix:当前计划的前置执行计划。
“plan_prefix”: [
] / plan_prefix /,
“table”: “customers1s
”,#涉及的表名,如果有别名,也会展示出来
#best_access_path:通过对比considered_access_paths,选择一个最优的访问路径
“best_access_path”: {
#当前考虑的访问路径
“considered_access_paths”: [
{
“access_type”: “ref”,#使用索引的方式,可参考explain中的type字段
“index”: “idx_gender_city_name_monthsalary_yearbonus”,#索引
“rows”: 471868,#行数
“cost”: 56304,#开销
“chosen”: true #是否选用这种执行路径
},
{
“access_type”: “range”,
“range_details”: {
“used_index”: “idx_gender_city_name_monthsalary_yearbonus”
} / range_details /,
“chosen”: false,
“cause”: “heuristic_index_cheaper”
}
] / considered_access_paths /
} / best_access_path /,
“condition_filtering_pct”: 100, #类似于explain的filtered列,是一个估算值
“rows_for_plan”: 471868,#执行计划最终的扫描行数,由considered_access_paths.rows X condition_filtering_pct计算获得。
“cost_for_plan”: 56304,#执行计划的代价,由considered_access_paths.cost相加获得
“chosen”: true #是否选择了该执行计划
}
] / considered_execution_plans /
},
{
#attaching_conditions_to_tables 基于considered_execution_plans中选择的执行计划,改造原有where条件,并针对表增加适当的附 加条件,以便于单表数据的筛选。
“attaching_conditions_to_tables”: {
#original_condition:原始的条件语句
“original_condition”: “((customers1s
.gender
= 0) and (customers1s
.city
= ‘长沙’) and (customers1s
.monthsalary
= 99))”,
#attached_conditions_computation:使用启发式算法计算已使用的索引,如果已使用的索引的访问 类型是ref,则计算用range能否使用组合索引中更多的列,如果可以,则用range的方式替换ref。
“attached_conditions_computation”: [
] / attached_conditions_computation /,
#attached_conditions_summary:附加之后的情况汇总
“attached_conditions_summary”: [
{
“table”: “customers1s
”,#表名
“attached”: “(customers1s
.monthsalary
= 99)” #附加的条件或原语句中能直接下推给单表筛选的条件。
}
] / attached_conditions_summary /
} / attaching_conditions_to_tables /
},
{
#refine_plan 改善执行计划
“refine_plan”: [
{
“table”: “customers1s
” #表名
}
] / refine_plan /
}
] / steps /
} / join_optimization /
},
{
“join_execution”: {
“select#”: 1,
“steps”: [
] / steps /
} / join_execution /
}
] / steps */
}
finalizing_table_conditions 最终的、经过优化后的表条件。
{
“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 */
}
2.2 优化器都做了什么
1.确定查询的表(多对多那就是多表)
2.查询字段信息,字段类型信息,索引信息等
3.重写sql语句或者是where条件。
4.判断索引使用
2.3 优化器执行过程分析
在explain条件下
alter table customers1s add index idx_city_gender(city,gender);
select * from customers1s where city="长沙" and gender=0;
S:9s多
explain select * from customers1s where city="长沙" and gender=0;
原因分析 使用了索引idx_city_gender,idx_city_gender索引树上存储的是主键、city、gender,但查询的是所有字段,又根据索引对应的主键,查找了满足条件的数据 ,即产生了回表。
回表查询的本质:索引当中并不存在sql语句需要查询的数据
- 连接层 -》 sql层 (优化器:得到最优的执行计划 idx_city_gender)=》查询数据的时候发现索引
并不满足需要查询数据 =》 通过查询的数据的主键索引去磁盘重查询数据 =》返回数据
select id,city,gender from customers1s where city="长沙" and gender=0;
s:0.21s
explain select id,city,gender from customers1s where city="长沙" and gender=0;
-
连接层 =》 sql层 (优化器:得到最优的执行计划 idx_city_gender)=》查询数据 =》 返回数据
-
连接层 =》 sql层 (优化器:得到最优的执行计划 idx_city_gender)=》查询数据的时候发现索引
并不满足需要查询数据 =》 通过查询的数据的主键索引去磁盘重查询数据 =》还需要通过where来进行数据过滤 =》 返回数据
即:索引过滤 =》回表 =》非索引过滤
三、总结
1. 先看查询使用的索引
2. 确定索引数据
3. 在预判sql语句扫描的数据与索引数据做对比
4. 在确定这个sql执行是不是最好