mysql优化九：内核查询成本计算实战

mysql内核成本计算

有些时候我们通过explain 的时候，明明可以用到索引，但是却没有使用，而是走了全盘扫描。因为优化器最终会选择成本最低的那种方案来作为最终的执行计划。但是这些成本怎么来的呢？搞明白成本的计算由来，就不会再有为什么mysql使用全盘扫描而不使用索引，为什么使用A索引，而不使用B索引等疑问。

mysql的执行成本

mysql主要的执行成本有两方面：一个是I/O成本，一个是CPU成本。
I/O成本:我们的表经常使用的MyISAM、InnoDB存储引擎都是将数据和索引都存储到磁盘上的，当我们想查询表中的记录时，需要先把数据或者索引加载到内存中然后再操作。这个从磁盘到内存这个加载的过程损耗的时间称之为I/O成本。
CPU成本：读取以及检测记录是否满足对应的搜索条件、对结果集进行排序等这些操作损耗的时间 称之为CPU成本。
对于InnoDB存储引擎来说，页是磁盘和内存之间交互的基本单位，MySQL规定读取一个页面花费的成本默认是1.0，读取以及检测一条记录是否符合搜索条件的成本默认是0.2。 1.0、0.2这些数字称之为成本常数，这两个成本常数我们最常用到，当然还有其他的成本常数。

mysql成本计算实战

单表成本计算实战

使用到的sql

CREATE TABLE `order_exp` (
  `id` bigint(22) NOT NULL AUTO_INCREMENT COMMENT '订单的主键',
  `order_no` varchar(50) NOT NULL COMMENT '订单的编号',
  `order_note` varchar(100) NOT NULL COMMENT '订单的说明',
  `insert_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '插入订单的时间',
  `expire_duration` bigint(22) NOT NULL COMMENT '订单的过期时长，单位秒',
  `expire_time` datetime NOT NULL COMMENT '订单的过期时间',
  `order_status` smallint(6) NOT NULL DEFAULT '0' COMMENT '订单的状态，0：未支付；1：已支付；-1：已过期，关闭',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `u_idx_day_status` (`insert_time`,`order_status`,`expire_time`) USING BTREE,
  KEY `idx_order_no` (`order_no`) USING BTREE,
  KEY `idx_expire_time` (`expire_time`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=10819 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

至于数据嘛在mysql优化三有存储过程复制过来改改执行以下就好了。
在一条单表查询语句真正执行之前，MySQL的查询优化器会找出执行该语句所有可能使用的方案，对比之后找出成本最低的方案，才会调用存储引擎提供的接口真正的执行查询，这个过程总结一下就是这样：
1、根据搜索条件，找出所有可能使用的索引
2、计算全表扫描的代价
3、计算使用不同索引执行查询的代价
4、对比各种执行方案的代价，找出成本最低的那一个
案例：

EXPLAIN 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;

这里了显示可能用到两个索引idx_order_no和idx_expire_time，那么为什么最终选择了idx_expire_time呢。接下来通过trace工具看看执行计划(trace 在之前的mysql优化三中有讲到)。

这个是全盘扫描的成本 就是cost=2167.3

这是idx_order_no索引的成本 cost=72.61

这是idx_expire_time的使用成本cost=47.81。
因此根据成本计算知道最终选择了idx_expire_time。
那么这些成本是怎么计算的呢。上面提到过mysql的成本主要是IO和CPU。其中IO的成本常数1.0 、CPU成本常数0.2。
那么我们首先来计算下全盘扫描的成本
全盘扫描成本计算
全盘扫描就是把聚集索引的数据依次根据条件判断后从磁盘放入到内存，这个查询成本=IO成本+CPU成本，所以要知道IO成本就要知道聚集索引有多少数据页，总的数据量是多少。这两个信息从哪里来呢。这个可以通过查表来获取,MySQL给我们提供了SHOW TABLE STATUS语句来查看表的统计信息。
SHOW TABLE STATUS LIKE 'order_exp';

Rows:表示表中的记录条数。对于使用MyISAM存储引擎的表来说，该值是准确的，对于使用InnoDB存储引擎的表来说，该值是一个估计值。查出来的数据是10341，我这张表的总记录数是10567。
Data_length：表示表占用的存储空间字节数。使用MyISAM存储引擎的表来说，该值就是数据文件的大小，对于使用InnoDB存储引擎的表来说，该值就相当于聚集索引占用的存储空间大小
Data_length = 聚簇索引的页面数量 x 每个页面的大小。由于mysql默认每个数据是16KB
也就是说可以这样计算聚集索引有多少数据页：
聚簇索引的页面数量 = 1589248 ÷ 1024 ÷ 16 = 97
那么IO成本=97*1.0+1.1=98.1
这个1.1 是微调值。MySQL在真实计算成本时会进行一些微调，这些微调的值是直接硬编码到代码里的，没有注释而且这些微调的值十分的小，并不影响我们大方向的分析。
CPU成本就是根据每条记录数进行计算
CPU成本=10341*0.2+1.0=2069.2
全盘扫描成本=98.1+2069.2=2167.3
计算二级索引成本
上述查询可能使用到idx_order_no，idx_expire_time这两个索引，我们需要分别分析单独使用这些索引执行查询的成本，最后还要分析是否可能使用到索引合并（后续优化中会讲到）。这里需要提一点的是，MySQL 查询优化器先分析使用唯一二级索引的成本，再分析使用普通索引的成本，我们这里两个索引都是普通索引，先算哪个都可以。我们先分析idx_expire_time的成本，然后再看 使用idx_order_no的成本。
使用到二级索引就会涉及到回表。那么二级索引成本主要来源：

1. 范围区间数量
   不论某个范围区间的二级索引到底占用了多少页面，查询优化器认为读取索引的一个范围区间的I/O成本和读取一个页面是相同的。
2. 需要回表的记录数
   优化器需要计算二级索引的某个范围区间到底包含多少条记录。

计算idx_expire_time成本
idx_expire_time对应的搜索条件是：expire_time> ‘2021-03-22 18:28:28’ AND expire_time<= ‘2021-03-22 18:35:09’ ，也就是说对应的范围区间就是：(‘2021-03- 22 18:28:28’ , ‘2021-03-22 18:35:09’ )。
idx_expire_time查询的IO成本=1*1.0=1
接着就是计算这个范围内的记录数。

mysql首先会定位2021-03-22 18:35:09 在叶子节点的那个位置，在定位2021-03-22 18:35:09的位置，如果区间最左记录和区间最右记录相隔不太远（在MySQL 5.7这个版本里，只要相隔不大于10个页面即可），那就可以精确统计出满足expire_time> ‘2021-03-2218:28:28’ AND expire_time<= ‘2021-03-22 18:35:09’条件的二级索引记录条数。
如果大于十个页面，就去取前十个页面总数的平均值，代表每个页面的数据量，然后在乘上范围区间的数据页就是总的数据量。

这是mysql内部计算范围区间的数据量，那我们要怎么计算。我们可以通过Explain来获取
EXPLAIN SELECT * FROM order_exp where expire_time > '2021-03-22 18:28:28' AND expire_time <= '2021-03-22 18:35:09';

这里的rows就是这个范围内的数量
那么这个范围内的idx_expire_time查询的CPU成本=39*0.2+0.01=7.81 其中0.01是微调数。
接下来计算回表需要的成本。回表的成本同样有IO成本和CPU成本。
回表的IO成本简单暴力，默认就是一条记录就相当于一个数据页。因为回表的时候是无序的(产生随机IO)，回表的过程在优化八中讲到。
回表的IO成本=39*1.0=39.0
回表的CPU成本=39*0.2=7.8
整个idx_expire_time的成本：
idx_expire_time查询的IO成本=1*1.0=1
idx_expire_time查询的CPU成本=39*0.2+0.01=7.81
回表的IO成本=39*1.0=39.0
回表的CPU成本=39*0.2=7.8
整个idx_expire_time成本=1+7.81+39.0+7.8=55.61
计算idx_order_no成本
根据上面的过程，idx_order_no对应的搜索条件是：order_no IN (‘DD00_6S’, ‘DD00_9S’, ‘DD00_10S’)，也就是说相当于3个单点区间。
idx_order_no查询的IO成本=3*1.0=3
在这三个区间范围内的记录总数：EXPLAIN SELECT * FROM order_exp where order_no IN ('DD00_6S', 'DD00_9S', 'DD00_10S');

idx_order_no查询的CPU成本=58*0.2+0.01=11.61
回表的IO成本=58*1.0=58.0
回表的CPU成本=58*0.2=11.6
总的idx_order_no成本=3+11.61+58.0+11.6=84.21

下边把执行本例中的查询的各种可执行方案以及它们对应的成本列出来：
全表扫描的成本：2169.9
使用idx_expire_time的成本：55.61
使用idx_order_no的成本：84.21
很显然，使用idx_expire_time的成本最低，所以当然选择idx_expire_time来执行查 询。来和Tracer中的比较一下：好像除了全盘扫描以外其他的对不上。
请注意：
1、在MySQL的实际计算中，在和全文扫描比较成本时，使用索引的成本会去除读取并检测回表后聚簇索引记录的成本(就是回表的cpu成本)，也就是说，我们通过MySQL看到的成本将会是： idx_expire_time为47.81(55.61-7.8)，idx_order_no为72.61(84.21-11.6)。
但是MySQL 比较完成本后，选择最终的索引的时候，就会加上前面去除的成本，也就是我们计算出来的值。

2、MySQL的源码中对成本的计算实际要更复杂，但是基本思想和算法是没错的。
跟trace计算的值有小差距是正常，因为一些数据值是估计值，但大方向是不会有影响。不会说trace计算使用了A索引，我们通过上述方法计算使用了B索引。

基于索引统计数据和index dive的成本计算

有时候使用索引执行查询时会有许多单点区间，比如使用IN语句就很容易产生非常多的单点区间，根据上面的例子order_no IN (‘DD00_6S’, ‘DD00_9S’, ‘DD00_10S’)，就产生三个单点区间。由于这个索引并不是唯一索引，所以并不能确定一个单点区间对应的二级索引记录的条数有多少，需要我们去计算。就是先获取索引对应的B+树的区间最左记录和区间最右记录，然后再计算这两条记 录之间有多少记录（记录条数少的时候可以做到精确计算，多的时候只能估算）。MySQL 把这种通过直接访问索引对应的B+树来计算某个范围区间对应的索引记录条数的方式称 之为index dive。
如果单点区间比较少，通过index dive 去计算记录数并不是什么问题，但是如果多的话，成百上千个性能损耗就很大了。
MySQL考虑到了这种情况，所以提供了一个系统变量eq_range_index_dive_limit，我们看一下在MySQL 5.7中这个系统变量的默认值：SHOW VARIABLES LIKE '%dive%';

也就是说如果我们的IN语句中的参数个数小于200个的话，将使用index dive的方式计算 各个单点区间对应的记录条数，如果大于或等于200个的话，可就不能使用index dive 了，要使用所谓的索引统计数据来进行估算。怎么个估算法？
MySQL会为表中的每一个索引维护一份统计数据，查看某个表中索引的统计数据可以使用SHOW INDEX FROM 表名的语法，SHOW INDEX FROM order_exp;

每个字段表示的含义：

table:索引所属表的名称
Non_unique:是否是唯一性索引 具有唯一性为0 普通的二级索引为1
Key_name：索引的名称
Seq_in_index：索引列在索引中的位置，从1开始计数。比如对于联合索引 u_idx_day_status，来说，insert_time, order_status, expire_time对应的位 置分别是1、2、3。
Column_name：索引列名称
Collation：索引列中的值是按照何种排序方式存放的，值为A时代表升序存放，为 NULL时代表降序存放。
Cardinality：索引列中不重复值的数量
Sub_part：对于存储字符串或者字节串的列来说，有时候我们只想对这些字符串的前n 个字符或字节建立索引，这个属性表示的就是那个n值。如果对完整的列建立索引的话， 该属性的值就是NULL。
Packed：索引列如何被压缩，NULL值表示未被压缩。这个属性我们暂时不了解，可以先忽略掉。
Null：该索引列是否允许存储NULL值。
Index_type：使用索引的类型
Comment：索引列注释信息。
Index_comment： 索引注释信息。

我们主要来看Cardinality这个属性。Cardinality表示索引列中不重复的数量，不过需要注意的是，对于InnoDB存储引擎来说，使用SHOW INDEX语句展示出来的 某个索引列的Cardinality属性是一个估计值，并不是精确的。本次查询中的order_no 索引Cardinality的值是10225。
之前我们通过SHOW TABLE STATUS LIKE 'order_exp';查出来的Rows为10341
结合Rows统计数据，我们可以针对索引列，计算出平均一个值重复多少次。 一个值的重复次数 ≈ Rows ÷ Cardinality
本次案例 order_no的重复次数≈10341÷10225≈1.011
如果order_no IN (…) in里面的数据量超过200的话，单点区间内的记录数会进行估算。假设in里面有10000个值 那么这个估算值=10000*1.011=10110 那么总记录数的估计值就是10110。
总结一下：在MySQL 5.7.3以及之前的版本中，eq_range_index_dive_limit的 默认值为10，之后的版本默认值为200。所以如果大家采用的是5.7.3以及之前的版本的话，很容易采用索引统计数据而不是index dive的方式来计算查询成本。当你的查询中 使用到了IN查询，但是却实际没有用到索引，就应该考虑一下是不是由于 eq_range_index_dive_limit 值太小导致的。

通过Explain 输出成本

之前成本数据我们可以通过trace工具来查看。不过trace查看的是sql中可能用到的所有索引的成本。并且trace工具非常影响性能，不能长时间在生产环境中使用。如果说我们想查看使用到的索引成本，要怎么查看呢。MySQL已经为我们提供了一种查看某个执行计划花费的成本的方式： 在EXPLAIN单词和真正的查询语句中间加上FORMAT=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;

把里面的数据复制出来就是这个样子

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "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": "((`tuling`.`order_exp`.`expire_time` > '2021-03-22 18:28:28') and (`tuling`.`order_exp`.`expire_time` <= '2021-03-22 18:35:09'))",
      "cost_info": {
        "read_cost": "55.60",
        "eval_cost": "0.01",
        "prefix_cost": "55.61",
        "data_read_per_join": "24"
      },
      "used_columns": [
        "id",
        "order_no",
        "order_note",
        "insert_time",
        "expire_duration",
        "expire_time",
        "order_status"
      ],
      "attached_condition": "((`tuling`.`order_exp`.`order_status` = 0) and (`tuling`.`order_exp`.`order_no` in ('DD00_6S','DD00_9S','DD00_10S')) and (`tuling`.`order_exp`.`insert_time` > `tuling`.`order_exp`.`expire_time`) and (`tuling`.`order_exp`.`order_note` like '%7排1%'))"
    }
  }
}

这一部分就是成本结果。

连接查询的成本计算

说到连接查询就要想到连接查询的两种算法。NLJ和BNL算法。这两种算法在mysql优化五中说到。
对于两表连接查询来说，它的查询成本由下边两个部分构成：

1. 单次查询驱动表的成本
2. 多次查询被驱动表的成本（具体查询多少次取决于对驱动表查询的结果集中有多少条记录）

对驱动表进行查询后得到的记录条数称之为驱动表的扇出（英文名：fanout）。很显然驱动表的扇出值越小，对被驱动表的查询次数也就越少，连接查询的总成本也就越低。有些时候扇出值很好计算，有些时候就要猜测。比如说

1. SELECT * FROM order_exp AS s1 INNER JOIN order_exp2 AS s2;以s1为驱动表来说s1的扇出值就是s1的记录数。这个记录数可以根据SHOW TABLE STATUS LIKE 'order_exp';的Rows获取
2. 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为驱动表来说s1的扇出值就是(2021-03-22 18:28:28,2021-03-22 18:35:09) 这个范围内的记录数。这个也好计算
3. SELECT * FROM order_exp AS s1 INNER JOIN order_exp2 AS s2 WHERE s1.order_note > 'xyz'; 以s1为驱动表来说，成本计算的时候并不会真正去执行sql，因此s1的扇出值就在s1的总记录数中猜测里有多少条记录满足 order_note > 'xyz’条件。MySQL把这个猜的过程称之为condition filtering。当然，这个过程可能会使用到索引，也可能使用到统计数据，也可能就是MySQL单纯的瞎猜，整个评估过程非常复杂，所以不去细讲

本次以NLJ算法为案例，计算连接查询的成本。

NLJ算法下的连接查询成本分析

连接查询的成本计算公式是这样的： 连接查询总成本 = 单次访问驱动表的成本 + 驱动表扇出数 x 单次访问被驱动表的成本.对于左（外）连接和右（外）连接查询来说，它们的驱动表是固定的，所以只需要选择成本最低的访问方法即可。对于内连接来说，驱动表和被驱动表的位置是可以互换的，所以需要考虑两个方面的问题，需要考虑最优的表连接顺序。然后再选择成本最低的访问方法。
很显然，计算内连接查询成本的方式更麻烦一些，下边我们就以内连接为例来看看如何 计算出最优的连接查询方案。当然在某些情况下，左（外）连接和右（外）连接查询在某些特殊情况下可以被优化为内连接查询。
SELECT * FROM order_exp AS s1 INNER JOIN order_exp2 AS s2 ON s1.order_no= s2.order_note WHERE s1.expire_time> '2021-03-22 18:28:28' AND s1.expire_time<= '2021-03-22 18:35:09' AND s2.expire_time> '2021-03-22 18:35:09' AND s2.expire_time<= '2021-03-22 18:35:59';
可以选择的连接顺序有两种：
s1连接s2，也就是s1作为驱动表，s2作为被驱动表。
s2连接s1，也就是s2作为驱动表，s1作为被驱动表。
查询优化器需要分别考虑这两种情况下的最优查询成本，然后选取那个成本更低的连接顺序以及该连接顺序下各个表的最优访问方法作为最终的查询计划。
连接查询成本计算结果，也是根据单表计算来的，根据上面的连接查询成本计算，先计算驱动表的单表成本，算出扇出数，在计算被驱动表的单表成本。具体的计算过程就不算了。