11-Mysql内核查询成本计算实战-03

EXPLAIN输出成本

如何通过EXPLAIN语句查看成本呢？MySQL已经为我们提供了一种查看某个执行计划花费的成本的方式：
在EXPLAIN单词和真正的查询语句中间加上FORMAT=JSON。这样我们就可以得到一个json格式的执行计划，里边包含该计划花费的成本，比如这样：

explain format=json SELECT * FROM order_exp WHERE order_no IN ('DD00_6S',
'DD00_9S', 'DD00_10S') AND expire_time> '2021-03-22 18:28:28' AND
expire_time<= '2021-03-22 18:35:09' AND insert_time> expire_time AND
order_note LIKE '%7排1%' AND order_status = 0\G

EXPLAIN: {
"query_block": {
"select_id": 1, # 整个查询语句只有1个SELECT关键字，该关键字对应的id号为1
"cost_info": {
"query_cost": "55.61" # 整个查询的执行成本预计为55.61
},
"table": {
"table_name": "order_exp",
"access_type": "range",
"possible_keys": [
"idx_order_no",
"idx_expire_time"
],
"key": "idx_expire_time",
"used_key_parts": [
"expire_time"
],
"key_length": "5",
"rows_examined_per_scan": 39,
"rows_produced_per_join": 0,
"filtered": "0.13",
"index_condition": "((`mysqladv`.`order_exp`.`expire_time` > '2021-03-22
18:28:28') and (`mysqladv`.`order_exp`.`expire_time` <= '2021-03-22 18:35:09'))",
"cost_info": {
"read_cost": "55.60",
"eval_cost": "0.01",
"prefix_cost": "55.61", #单独查询表的成本，也就是：read_cost + eval_cost
"data_read_per_join": "24" #和连接查询相关的数据量，单位字节，这里无用
},
"used_columns": [
"id",
"order_no",
"order_note",
"insert_time",
"expire_duration",
"expire_time",
"order_status"
],
"attached_condition": "((`mysqladv`.`order_exp`.`order_status` = 0) and
(`mysqladv`.`order_exp`.`order_no` in ('DD00_6S','DD00_9S','DD00_10S')) and
(`mysqladv`.`order_exp`.`insert_time` > `mysqladv`.`order_exp`.`expire_time`) and
(`mysqladv`.`order_exp`.`order_note` like '%7排1%'))"
}
}
}
1 row in set, 1 warning (0.00 sec)

连接查询的成本

MySQL中连接查询采用的是嵌套循环连接算法，驱动表会被访问一次，被驱动表可能会被访问多次，所以对于两表连接查询来说，它的查询成本由下边两个部分构成：
单次查询驱动表的成本
多次查询被驱动表的成本（具体查询多少次取决于对驱动表查询的结果集中有多少条记录）

对驱动表进行查询后得到的记录条数称之为驱动表的扇出（英文名：fanout）。很显然驱动表的扇出值越小，对被驱动表的查询次数也就越少，连接查询的总成本也就越低。
当查询优化器想计算整个连接查询所使用的成本时，就需要计算出驱动表的扇出值，有的时候扇出值的计算是很容易的，比如下边这两个查询：

查询一：

SELECT * FROM order_exp AS s1 INNER JOIN order_exp2 AS s2;

假设使用s1表作为驱动表，很显然对驱动表的单表查询只能使用全表扫描的方式执行，驱动表的扇出值也很明确，那就是驱动表中有多少记录，扇出值就是多少。统计数据中s1表的记录行数是10573，也就是说优化器就直接会把10573当作s1表的扇出值。
查询二：

SELECT * FROM order_exp AS s1 INNER JOIN order_exp2 AS s2
WHERE s1.expire_time> '2021-03-22 18:28:28' AND s1.expire_time<= '2021-03-22
18:35:09';

仍然假设s1表是驱动表的话，很显然对驱动表的单表查询可以使用idx_expire_time索引执行查询。此时范围区间( ‘2021-03-22 18:28:28’, ‘2021-03-22 18:35:09’)中有多少条记录，那么扇出值就是多少。

但是有的时候扇出值的计算就变得很棘手，比方说下边几个查询：

SELECT * FROM order_exp AS s1 INNER JOIN order_exp2 AS s2 WHERE s1.order_note
> 'xyz';

本查询和查询一类似，只不过对于驱动表s1多了一个order_note > 'xyz’的搜索条件。查询优化器又不会真正的去执行查询，所以它只能猜这10573记录里有多少条记录满足order_note > 'xyz’条件。

查询四：

SELECT * FROM order_exp AS s1 INNER JOIN order_exp2 AS s2 WHERE
s1.expire_time> '2021-03-22 18:28:28' AND s1.expire_time<= '2021-03-22
18:35:09' AND s1.order_note > 'xyz'; 

本查询和查询二类似，只不过对于驱动表s1也多了一个order_note > 'xyz’的搜索条件。不过因为本查询可以使用idx_expire_time索引，所以只需要从符合二级索引范围区间的记录中猜有多少条记录符合order_note > 'xyz’条件，也就是只需要猜在39条记录中有多少符合order_note > 'xyz’条件。

查询五：

SELECT * FROM order_exp AS s1 INNER JOIN order_exp2 AS s2 WHERE
s1.expire_time> '2021-03-22 18:28:28' AND s1.expire_time<= '2021-03-22
18:35:09' AND s1.order_no IN ('DD00_6S', 'DD00_9S', 'DD00_10S') AND
s1.order_note > 'xyz'; 

本查询和查询四类似，不过在驱动表s1选取idx_expire_time索引执行查询后，优化器需要从符合二级索引范围区间的记录中猜有多少条记录符合下边两个条件：
order_no IN (‘DD00_6S’, ‘DD00_9S’, ‘DD00_10S’)
order_note > ‘xyz’
也就是优化器需要猜在39条记录中有多少符合上述两个条件的。
说了这么多，其实就是想表达在这两种情况下计算驱动表扇出值时需要靠猜：如果使用的是全表扫描的方式执行的单表查询，那么计算驱动表扇出时需要猜满足搜索条件的记录到底有多少条。
如果使用的是索引执行的单表扫描，那么计算驱动表扇出的时候需要猜满足除使用到对应索引的搜索条件外的其他搜索条件的记录有多少条。
MySQL把这个猜的过程称之为condition filtering。当然，这个过程可能会使用到索引，也可能使用到统计数据，也可能就是MySQL单纯的瞎猜，整个评估过程非常复杂。
在MySQL 5.7之前的版本中，查询优化器在计算驱动表扇出时，如果是使用全表扫描的话，就直接使用表中记录的数量作为扇出值，如果使用索引的话，就直接使用满足范围条件的索引记录条数作为扇出值。
在MySQL 5.7中，MySQL引入了这个condition filtering的功能，就是还要猜一猜剩余的那些搜索条件能把驱动表中的记录再过滤多少条，其实本质上就是为了让成本估算更精确。 我们所说的纯粹瞎猜其实是很不严谨的，MySQL称之为启发式规则。