前言
之前的几篇博客已经把InnoDB和Index原理详细的分析了一下，本篇博客将会和大家一起分享如何使用查询优化器，探索Mysql底层对查询的优化以及Mysql查询成本的估算。更多Mysql调优内容请点击【Mysql优化-深度讲解系列目录】。
开启查询优化器
俗话说工欲善其事必先利其器，Mysql查询语句分析也是一样，有一个良好的分析工具自然可以做到事半功倍，这里就要介绍到Mysql自带的查询优化器了，基本功能如下。
set optimizer_trace="enabled=on"; -- 开启
select * from t1 where a=1 and b> 1; -- 执行sql语句
select * from information_schema.OPTIMIZER_TRACE; -- 日志输出上一条sql的优化过程
set optimizer_trace="enabled=off"; -- 关闭
短短四行就把查询优化器的使用说完了，是不是非常简单，那么我们看下查询优化器给我们执行的sql语句输出了什么日志。
分析日志
把输出的信息辅助出来，一点一点的分析。
select * from t1 where a=1 and b > 1
LIMIT 0, 1000 {
"steps": [
{
"join_preparation": { //从名字和内容看，是在解析我们输入的sql语句，加上了一些Mysql内部认为必要的限定条件。
"select#": 1,
"steps": [
{
"expanded_query": "/* select#1 */ select `t1`.`a` AS `a`,`t1`.`b` AS `b`,`t1`.`c` AS `c`,`t1`.`d` AS `d`,`t1`.`e` AS `e` from `t1` where ((`t1`.`a` = 1) and (`t1`.`b` > 1)) limit 0,1000"
}
]
}
},
{
"join_optimization": { //进行SQL优化，本篇着重分析的内容之一
"select#": 1,
"steps": [
{
"condition_processing": { //首先condition_processing是对Condition进行解析，解析出来的条件是Where。
"condition": "WHERE",
"original_condition": "((`t1`.`a` = 1) and (`t1`.`b` > 1))",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "((`t1`.`b` > 1) and multiple equal(1, `t1`.`a`))"
/*equality_propagation是指等值传递，这个字段是针对等值列进行优化，比如：
a = b and b = c and c = 5
会被优化为：
a = 5 and b = 5 and c = 5 */
},
{
"transformation": "constant_propagation",
"resulting_condition": "((`t1`.`b` > 1) and multiple equal(1, `t1`.`a`))"
/*constant_propagation被称为常量传递，当发现条件中有固定的列值时，会优化查询条件，比如：
a = 1 AND b > a
会被优化为：
a = 1 AND b > 1 */
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "((`t1`.`b` > 1) and multiple equal(1, `t1`.`a`))"
/*trivial_condition_removal这一步的目的是移除无效条件，当发现条件中有与查询结果不相关的条件时，移除该条件，比如：
a = 1 and 2 > 1
会被优化为：
a = 1 */
}
]
}
}, ......
} 0 0
最终在trivial_condition_removal标签里面的内容就是经过Mysql查询优化器优化以后的最终结果，例如下面这个sql：
set optimizer_trace="enabled=on";
select * from t1 where a=1 and b > a; //经过优化前
select * from information_schema.OPTIMIZER_TRACE;
set optimizer_trace="enabled=off";
最终会被优化为select * from t1 where a=1 and b > 1;，下面是优化的过程。
select * from t1 where a=1 and b > a
LIMIT 0, 1000 {
"steps": [
{
"join_preparation": { ...... },
{
"join_optimization": {
"select#": 1,
"steps": [
{
"condition_processing": {
"condition": "WHERE",
"original_condition": "((`t1`.`a` = 1) and (`t1`.`b` > `t1`.`a`))",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "((`t1`.`b` > 1) and multiple equal(1, `t1`.`a`))"
},
{
"transformation": "constant_propagation",
"resulting_condition": "((`t1`.`b` > 1) and multiple equal(1, `t1`.`a`))"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "((`t1`.`b` > 1) and multiple equal(1, `t1`.`a`))" //最终的优化结果
}
]
}
}, ......
} 0 0
查询成本的概念
一般来说一个sql查询可以有不同的执行方案，可以选择某个索引进行查询，也可以选择全表扫描，查询优化器则会比较并选择其中成本最低的方案去执行查询。查询成本分大体为两种：I/O成本和CPU成本。Mysql使用的InnoDB引擎会把数据和索引都存储到磁盘上，当查询的时候需要先把数据先加载到内存中在进行下一步操作，这个加载的时间就是I/O成本。当数据被加载到内存中后，CPU会计算查询条件，对数据排序等等操作，这一步所消耗的时间就是CPU成本。但是查询优化器并不会真正的去执行sql，只会去根据优化的结果去预估一个成本。InnoDB引擎规定读取一个页面花费的成本默认约是0.25，读取以及检测一条记录是否符合搜索条件的成本默认约是0.1。为什么都是约呢，因为Mysql内部的计算成本比较复杂这里提取了两个主要的计算参数。
感兴趣的同学可以自行执行下面sql查看系统表中配置的参数：
select * from mysql.engine_cost; -- 仅保留重要列，sql执行输出如下
| cost_name | default_value | comment |
|---|---|---|
| disk_temptable_create_cost | 20 | NULL |
| disk_temptable_row_cost | 0.5 | NULL |
| key_compare_cost | 0.05 | NULL |
| memory_temptable_create_cost | 1 | NULL |
| memory_temptable_row_cost | 0.1 | 查询一行的消耗 |
| row_evaluate_cost | 0.1 | NULL |
select * from mysql.engine_cost; -- 仅保留重要列，sql执行输出如下
| engine_name | cost_name | default_value | comment |
|---|---|---|---|
| default | io_block_read_cost | 1 | NULL |
| default | memory_block_read_cost | 0.25 | 查询一页的消耗 |
基于成本的优化
在一条单表查询语句真正执行之前，Mysql的查询优化器会找出执行该语句所有可能使用的方案，对比之后找出成本最低的方案，这个成本最低的方案就是所谓的执行计划，之后才会调用存储引擎提供的接口真正的执行查询。基本上来说所谓的优化就是寻找当前成本最低的方案，那么我们就模拟一个语句计算成本的方式，比如一个表’city’的基本信息如下(仅保留重要信息，表是Mysq自带的)：
show table status like 'city';
| Name | Engine | Row_format | Rows | Data_length | Index_length | …… |
|---|---|---|---|---|---|---|
| city | InnoDB | Dynamic | 4117 | 393216 | 81920 | …… |
表里的关于成本计算的基本属性已经被筛选出来了，下图是表的格式。这里面一共有两个索引，一个是PRIMARY属于自创建的主键索引，是Mysql对主键字段ID自动创建的索引；第二个是自己创建的索引idx_city_NP，是给字段Name和Popluation创建的联合索引。我们要进行成本计算的sql是：
select * from city where Name like 'A%' and id>1024 and id<1977;
估算全表扫描成本
首先我们先计算一下这个查询语句如果进行全表扫描要多少成本。做全表扫描，就要加载所有页数到CPU，然后每个页进行逐行比较。所以计算全表的成本第一步要找到总页数。要提醒的是由于InnoDB创建表的时候会默认把聚簇索引创建出来，所以主键索引在计算全表扫描的时候是可以使用的。
IO成本 (默认每页16KB)：
总页数(Pages) * 0.25 = Data_length ÷ 16KB=393216 ÷ 16 ÷ 1024 * 0.25 = 24(pages)* 0.25 = 6
CPU成本：
总行数(Rows) *0.1 = 4117 (rows) * 0.1 = 411.7
合计：
Total = Pages*0.25 + Rows*0.1 = 6 + 411.7 = 417.7
估算联合索引扫描成本
接着计算有根据我们之前的分析这个条件语句中，条件Name like 'A%'可以利用到idx_city_NP索引，条件id>1024 and id<1977是一个范围查询可以利用到主键索引PRIMARY。关于范围查询就要再引入一个概念：范围区间。
范围区间
当我们从索引中查询记录时，不管是=、in、>、<这些操作都需要从索引中确定一个范围，不论这个范围区间的索引到底占用了多少页面，查询优化器粗暴的认为读取索引的一个范围区间的I/O成本和读取一个页面是相同的。本例中使用PRIMARY的范围区间只有一个(1024, 1977)，所以相当于访问这个范围区间的索引付出的I/O成本就是：1*1.0=1。如果有两个范围区间则是2*1.0=2。
预估范围的记录数
优化器需要计算索引的某个范围区间到底包含多少条记录，对于本例来说就是要计算PRIMARY在(1024, 1977)这个范围区间中包含多少条数据记录，计算过程是这样的：
- 步骤1：先根据id>1024这个条件访问一下PRIMARY对应的B+树索引，找到满足id>1024这个条件的第一条记录，我们把这条记录称之为区间最左记录。
- 步骤2：然后再根据id<1977这个条件继续从PRIMARY对应的B+树索引中找出第一条满足 这个条件的记录，我们把这条记录称之为区间最右记录。
- 步骤3：如果区间最左记录和区间最右记录相隔不太远（只要相隔不大于10个页面即可），那就可 以精确统计出满足id>1024 and id<1977条件的记录条数。否则只沿着区间最 左记录向右读10个页面，计算平均每个页面中包含多少记录，然后用这个平均值乘以区间最左记 录和区间最右记录之间的页面数量就可以了。那么问题又来了，怎么估计区间最左记录和区间最右 记录之间有多少个页面呢？计算它们父节点中对应的目录项记录之间隔着几条记录就可以了。
PRIMARY索引成本估算
根据上面的步骤可以算出来PRIMARY索引的记录条数，所以读取记录的CPU成本为：(1977-1024)*0.1=952*0.1=95.2，其中 952是预估的需要读取的数据记录条数，0.1是读取一条记录成本常数。再加上一个区间范围成本总计是95.2+1=96.2。
idx_city_NP索引成本估算
之前说过辅助索引找出来的最终是主键，通过主键再去主键索引里面查找，所以通过二级索引查询需要回表，在计算二级索引需要成本时还要加上回表的成本，而回表的成本就相当于执行：Select * from city where key_* in (key_1,key_2,key_3,……)。
所以idx_city_NP的成本 = 辅助索引的查询成本 + 回表查询的成本=272*(0.1+0.25)= 95.2。其中272是like 'A%'范围内的总行数。
所以联合索引扫描成本总计：95.2+96.2=191.4。这个值远远小于全表扫描的成本417.7。
Mysql计算的成本
我们可以看下Mysql计算的成本是多少：
explain select * from city where Name like 'A%' and id>1024 and id<1977 {
"steps": [
{
"join_preparation": {
"select#": 1,
"steps": [ //原始数据读取
{
"expanded_query": "/* select#1 */ select `city`.`ID` AS `ID`,`city`.`Name` AS `Name`,`city`.`CountryCode` AS `CountryCode`,`city`.`District` AS `District`,`city`.`Population` AS `Population` from `city` where ((`city`.`Name` like 'A%') and (`city`.`ID` > 1024) and (`city`.`ID` < 1977))"
}
]
}
},
{
"join_optimization": { //优化步骤
"select#": 1,
"steps": [
{
"condition_processing": { //分解条件
"condition": "WHERE",
"original_condition": "((`city`.`Name` like 'A%') and (`city`.`ID` > 1024) and (`city`.`ID` < 1977))",
"steps": [
{
"transformation": "equality_propagation", //等值优化
"resulting_condition": "((`city`.`Name` like 'A%') and (`city`.`ID` > 1024) and (`city`.`ID` < 1977))"
},
{
"transformation": "constant_propagation", //常量优化
"resulting_condition": "((`city`.`Name` like 'A%') and (`city`.`ID` > 1024) and (`city`.`ID` < 1977))"
},
{
"transformation": "trivial_condition_removal", //冗余删除，最终的执行sql在这里
"resulting_condition": "((`city`.`Name` like 'A%') and (`city`.`ID` > 1024) and (`city`.`ID` < 1977))"
}
]
}
},
{
"substitute_generated_columns": {
}
},
{
"table_dependencies": [
{
"table": "`city`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
]
}
]
},
{
"ref_optimizer_key_uses": [
]
},
{
"rows_estimation": [
{
"table": "`city`",
"range_analysis": {
"table_scan": { //全表扫描的成本
"rows": 4117,
"cost": 419.8 //我们计算的417.7
},
"potential_range_indexes": [ //查看是否有潜在的索引可以使用
{
"index": "PRIMARY",
"usable": true,
"key_parts": [ //主键索引
"ID"
]
},
{
"index": "CountryCode", //另外一个辅助索引
"usable": false,
"cause": "not_applicable"
},
{
"index": "idx_city_NP", //自创建的索引
"usable": true,
"key_parts": [
"Name",
"Population",
"ID"
]
}
],
"setup_range_conditions": [
],
"group_index_range": {
"chosen": false,
"cause": "not_group_by_or_distinct"
},
"skip_scan_range": {
"potential_skip_scan_indexes": [
{
"index": "PRIMARY",
"usable": false,
"cause": "query_references_nonkey_column"
},
{
"index": "idx_city_NP",
"usable": false,
"cause": "query_references_nonkey_column"
}
]
},
"analyzing_range_alternatives": { //分析范围成本，不涉及到CountryCode所以没有分析这个索引
"range_scan_alternatives": [
{
"index": "PRIMARY", //分析主键索引花费的成本
"ranges": [
"1024 < ID < 1977"
],
"index_dives_for_eq_ranges": true,
"rowid_ordered": true,
"using_mrr": false,
"index_only": false,
"rows": 952,
"cost": 96.097,
"chosen": true
},
{
"index": "idx_city_NP", //使用索引idx_city_NP花费的成本
"ranges": [
"A\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000 <= Name <= Aÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ"
],
"index_dives_for_eq_ranges": true,
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
"rows": 272,
"cost": 95.46,
"chosen": true
}
],
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
}
},
"chosen_range_access_summary": { //最终决定要使用的查询方式
"range_access_plan": {
"type": "range_scan",
"index": "idx_city_NP",
"rows": 272,
"ranges": [
"A\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000 <= Name <= Aÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ"
]
},
"rows_for_plan": 272,
"cost_for_plan": 95.46,
"chosen": true
}
}
}
]
},
{
"considered_execution_plans": [ //执行本条语句要使用的总成本
{
"plan_prefix": [
],
"table": "`city`",
"best_access_path": {
"considered_access_paths": [
{
"rows_to_scan": 272,
"filtering_effect": [
],
"final_filtering_effect": 0.2312,
"access_type": "range",
"range_details": {
"used_index": "idx_city_NP" //使用索引idx_city_NP
},
"resulting_rows": 62.896,
"cost": 122.66, //我们简单估计的191.4
"chosen": true
}
]
},
"condition_filtering_pct": 100,
"rows_for_plan": 62.896,
"cost_for_plan": 122.66,
"chosen": true //确定使用索引idx_city_NP
}
]
},
{
"attaching_conditions_to_tables": {
"original_condition": "((`city`.`Name` like 'A%') and (`city`.`ID` > 1024) and (`city`.`ID` < 1977))",
"attached_conditions_computation": [
],
"attached_conditions_summary": [
{
"table": "`city`",
"attached": "((`city`.`Name` like 'A%') and (`city`.`ID` > 1024) and (`city`.`ID` < 1977))"
}
]
}
},
{
"refine_plan": [
{
"table": "`city`",
"pushed_index_condition": "((`city`.`Name` like 'A%') and (`city`.`ID` > 1024) and (`city`.`ID` < 1977))",
"table_condition_attached": null
}
]
}
]
}
},
{
"join_explain": {
"select#": 1,
"steps": [
]
}
}
]
} 0 0
差不多但是有所出入，因为毕竟Mysql内部考虑的东西比我们还要多很多，有出入是正常的，但是索引查找依然比全表扫描成本消耗少的多，所以Mysql会在真正查询的时候使用索引。
总结
本篇博客介绍了Mysql如何开启查询优化器，如何分析查询优化器的日志，以及Mysql对于查询语句是如何进行成本估算的，并且实际做了一个例子演示成本估算。但是这篇文章想表达的其实是说Mysql这种成本估算都是预估的，并不是真正执行的实际时间，只是一个参考用于优化查询的大概值。比如一个查询语句如果你自己估计应该会使用某一个索引，但是Mysql最终并没有使用。这个时候就可以使用优化器辅助分析一下是否可以使用该索引，并强制指定使用这个索引。真正执行的逻辑，其实我们根本无法操控，全部由Mysql自动管理，真正关心的就是估算的成本，以及使用的条件都是什么。下一篇【Mysql深度讲解 - 查询优化器（二）】将会对这些数据的储存和更新策略做一个讲解。
扫一扫
10万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
