mysql优化——5.内核查询成本计算_mysql 索引成本计算

每天多学一点点~
话不多说，这就开始吧…

1.前文

两年前写过一篇博客，mysql优化——3.trace工具以及排序优化(order by 以及文件排序filesort) ，主要是介绍了trace工具的使用。今儿复习一下之前写的东西，很好奇，mysql的成本到底是如何计算出来的。偶得空，在这里简单记录一下。

2.准备工作

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

往里面随便插个1w数据，然后用trace工具看一下，数据不同分析处的成本也不一定相同。
然后执行：

SET optimizer_trace="enabled=on";
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;
SELECT * FROM information_schema.OPTIMIZER_TRACE
1
2
3

可以看见全表扫描的
使用索引idx_order_no的成本为72.61：

使用索引idx_expire_time的成本为47.81：

3.什么是成本

MySQL执行一个查询可以有不同的执行方案，它会选择其中成本最低，或者说代价最低的那种方案去真正的执行查询。什么是执行成本呢？其实在MySQL中一条查询语句的执行成本是由下边这两个方面组成的：
I/O成本
我们的表经常使用的MyISAM、InnoDB存储引擎都是将数据和索引都存储到磁盘上的，当我们想查询表中的记录时，需要先把数据或者索引加载到内存中然后再操作。这个从磁盘到内存这个加载的过程损耗的时间称之为I/O成本。
CPU成本
读取以及检测记录是否满足对应的搜索条件、对结果集进行排序等这些操作损耗的时间称之为CPU成本。
对于InnoDB存储引擎来说，页是磁盘和内存之间交互的基本单位，MySQL规定读取一个页面花费的成本默认是1.0，读取以及检测一条记录是否符合搜索条件的成本默认是0.2。1.0、0.2这些数字称之为成本常数，这两个成本常数我们最常用到，当然还有其他的成本常数。
注意，不管读取记录时需不需要检测是否满足搜索条件，其成本都算是0.2。

4.单表查询的成本

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

我们依然以上面的查询语句来分析：

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

一步一步分析一下。

4.1. 根据搜索条件，找出所有可能使用的索引

MySQL把一个查询中可能使用到的索引称之为possible keys。
我们分析一下上边查询中涉及到的几个搜索条件：
order_no IN (‘DD00_6S’, ‘DD00_9S’, ‘DD00_10S’) ，这个搜索条件可以使用二级索引idx_order_no。
expire_time> ‘2021-03-22 18:28:28’ AND expire_time<= ‘2021-03-22 18:35:09’，这个搜索条件可以使用二级索引idx_expire_time。
insert_time> expire_time，这个搜索条件的索引列由于没有和常数比较，所以并不能使用到索引。
order_note LIKE ‘%hello%’，order_note即使有索引，但是通过LIKE操作符和以通配符开头的字符串做比较，不可以适用索引。
order_status = 0，由于该列上只有联合索引，而且不符合最左前缀原则，所以不会用到索引。
综上所述，上边的查询语句可能用到的索引，也就是possible keys只有idx_order_no,idx_expire_time。

4.2. 计算全表扫描的代价

对于InnoDB存储引擎来说，全表扫描的意思就是把聚簇索引中的记录都依次和给定的搜索条件做一下比较，把符合搜索条件的记录加入到结果集，所以需要将聚簇索引对应的页面加载到内存中，然后再检测记录是否符合搜索条件。由于查询成本=I/O成本+CPU成本，所以计算全表扫描的代价需要两个信息：
描的代价需要两个信息：
聚簇索引占用的页面数 和 该表中的记录数

这两个信息从哪来呢？MySQL为每个表维护了一系列的统计信息，关于这些统计信息是如何收集起来的我们放在后边再说，现在看看怎么查看这些统计信息。
MySQL给我们提供了SHOW TABLE STATUS语句来查看表的统计信息，如果要看指定的某个表的统计信息，在该语句后加对应的LIKE语句就好了，比方说我们要查看order_exp这个表的统计信息可以这么写：

SHOW TABLE STATUS LIKE 'order_exp'\G
1

出现了很多统计选项，但我们目前只需要两个：
Rows
本选项表示表中的记录条数。对于使用MyISAM存储引擎的表来说，该值是准确的，对于使用InnoDB存储引擎的表来说，该值是一个估计值。从查询结果我们也可以看出来，由于我们的order_exp表是使用InnoDB存储引擎的，所以虽然实际上表中有10567条记录，但是SHOW TABLE STATUS显示的Rows值只有10354条记录。
Data_length
本选项表示表占用的存储空间字节数。使用MyISAM存储引擎的表来说，该值就是数据文件的大小，对于使用InnoDB存储引擎的表来说，该值就相当于聚簇索引占用的存储空间大小，也就是说可以这样计算该值的大小：
Data_length = 聚簇索引的页面数量 x 每个页面的大小
我们的order_exp使用默认16KB的页面大小，而上边查询结果显示Data_length的值是1589248，所以我们可以反向来推导出聚簇索引的页面数量：
聚簇索引的页面数量 = 1589248 ÷ 16 ÷ 1024 = 97
我们现在已经得到了聚簇索引占用的页面数量以及该表记录数的估计值，所以就可以计算全表扫描成本了。

现在可以看一下全表扫描成本的计算过程：
I/O成本
97 x 1.0 + 1.1 = 98.1
97指的是聚簇索引占用的页面数，1.0指的是加载一个页面的IO成本常数，后边的1.1是一个微调值。
CPU成本
10354x 0.2 + 1.0 = 2071.8
10354指的是统计数据中表的记录数，对于InnoDB存储引擎来说是一个估计值，0.2指的是访问一条记录所需的CPU成本常数，后边的1.0是一个微调值。
总成本：
98.1 + 2071.8= 2169.9

综上所述，对于order_exp的全表扫描所需的总成本就是2169.9。
ps：前边说过表中的记录其实都存储在聚簇索引对应B+树的叶子节点中，所以只要我们通过根节点获得了最左边的叶子节点，就可以沿着叶子节点组成的双向链表把所有记录都查看一遍。
也就是说全表扫描这个过程其实有的B+树非叶子节点是不需要访问的，但是MySQL在计算全表扫描成本时直接使用聚簇索引占用的页面数作为计算I/O成本的依据，是不区分非叶子节点和叶子节点的。

4.3. 计算使用不同索引执行查询的代价

从第1步分析我们得到，上述查询可能使用到idx_order_no，idx_expire_time这两个索引，我们需要分别分析单独使用这些索引执行查询的成本，最后还要分析是否可能使用到索引合并。这里需要提一点的是，MySQL查询优化器先分析使用唯一二级索引的成本，再分析使用普通索引的成本，我们这里两个索引都是普通索引，先算哪个都可以。我们先分析idx_expire_time的成本，然后再看使用idx_order_no的成本。

4.3.1 使用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搜索会使用用二级索引 + 回表方式的查询，MySQL计算这种查询的成本依赖两个方面的数据：
范围区间数量
不论某个范围区间的二级索引到底占用了多少页面，查询优化器认为读取索引的一个范围区间的I/O成本和读取一个页面是相同的。本例中使用idx_expire_time的范围区间只有一个，所以相当于访问这个范围区间的二级索引付出的I/O成本就是：1 x 1.0 = 1.0
需要回表的记录数
优化器需要计算二级索引的某个范围区间到底包含多少条记录，对于本例来说就是要计算idx_expire_time在(‘2021-03-22 18:28:28’ ，‘2021-03-22 18:35:09’)这个范围区间中包含多少二级索引记录，计算过程是这样的：
步骤1：先根据expire_time> ‘2021-03-22 18:28:28’这个条件访问一下idx_expire_time对应的B+树索引，找到满足expire_time> ‘2021-03-22 18:28:28’这个条件的第一条记录，我们把这条记录称之为区间最左记录。我们前头说过在B+数树中定位一条记录的过程是很快的，是常数级别的，所以这个过程的性能消耗是可以忽略不计的。
步骤2：然后再根据expire_time<= ‘2021-03-22 18:35:09’这个条件继续从idx_expire_time对应的B+树索引中找出最后一条满足这个条件的记录，我们把这条记录称之为区间最右记录，这个过程的性能消耗也可以忽略不计的。
步骤3：如果区间最左记录和区间最右记录相隔不太远（在MySQL 5.7这个版本里，只要相隔不大于10个页面即可），那就可以精确统计出满足expire_time> ‘2021-03-22 18:28:28’ AND expire_time<= ‘2021-03-22 18:35:09’条件的二级索引记录条数。否则只沿着区间最左记录向右读10个页面，计算平均每个页面中包含多少记录，然后用这个平均值乘以区间最左记录和区间最右记录之间的页面数量就可以了。那么问题又来了，怎么估计区间最左记录和区间最右记录之间有多少个页面呢？解决这个问题还得回到B+树索引的结构中来。
我们假设区间最左记录在页b中，区间最右记录在页c中，那么我们想计算区间最左记录和区间最右记录之间的页面数量就相当于计算页b和页c之间有多少页面，而它们父节点中记录的每一条目录项记录都对应一个数据页，所以计算页b和页c之间有多少页面就相当于计算它们父节点（也就是页a）中对应的目录项记录之间隔着几条记录。在一个页面中统计两条记录之间有几条记录的成本就很小了。
不过还有问题，如果页b和页c之间的页面实在太多，以至于页b和页c对应的目录项记录都不在一个父页面中怎么办？既然是树，那就继续递归，之前我们说过一个B+树有4层高已经很了不得了，所以这个统计过程也不是很耗费性能。
知道了如何统计二级索引某个范围区间的记录数之后，就需要回到现实问题中来，MySQL根据上述算法测得idx_expire_time在区间(‘2021-03-22 18:28:28’ ，‘2021-03-22 18:35:09’)之间大约有39条记录。这里可以用explain执行计划简单看一下

explain SELECT * FROM order_exp WHERE expire_time> '2021-03-22 18:28:28' AND expire_time<= '2021-03-22 18:35:09';
1

读取这39条二级索引记录需要付出的CPU成本就是：
39 x 0.2 + 0.01 = 7.81
其中39是需要读取的二级索引记录条数，0.2是读取一条记录成本常数，0.01是微调。
在通过二级索引获取到记录之后，还需要干两件事儿：

1. 根据这些记录里的主键值到聚簇索引中做回表操作
   MySQL评估回表操作的I/O成本依旧很简单粗暴，他们认为每次回表操作都相当于访问一个页面，也就是说二级索引范围区间有多少记录，就需要进行多少次回表操作，也就是需要进行多少次页面I/O。我们上边统计了使用idx_expire_time二级索引执行查询时，预计有39 条二级索引记录需要进行回表操作，所以回表操作带来的I/O成本就是：
   39 x 1.0 = 39 .0
   其中39 是预计的二级索引记录数，1.0是一个页面的I/O成本常数。
2. 回表操作后得到的完整用户记录，然后再检测其他搜索条件是否成立
   回表操作的本质就是通过二级索引记录的主键值到聚簇索引中找到完整的用户记录，然后再检测除expire_time> ‘2021-03-22 18:28:28’ AND expire_time< '2021-03-22 18:35:09’这个搜索条件以外的搜索条件是否成立。
   因为我们通过范围区间获取到二级索引记录共39 条，也就对应着聚簇索引中39 条完整的用户记录，读取并检测这些完整的用户记录是否符合其余的搜索条件的CPU成本如下：
   39 x 0.2 =7.8
   其中39 是待检测记录的条数，0.2是检测一条记录是否符合给定的搜索条件的成本常数。
   所以本例中使用idx_expire_time执行查询的成本就如下所示：

I/O成本：
1.0 + 39 x 1.0 = 40 .0 (范围区间的数量 + 预估的二级索引记录条数)
CPU成本：
39 x 0.2 + 0.01 + 39 x 0.2 = 15.61 （读取二级索引记录的成本 + 读取并检测回表后聚簇索引记录的成本）
总成本：
40 .0 + 15.61 = 55.61

4.3.2 使用idx_order_no执行查询的成本分析

idx_order_no对应的搜索条件是：order_no IN (‘DD00_6S’, ‘DD00_9S’, ‘DD00_10S’)，也就是说相当于3个单点区间。
与使用idx_expire_time的情况类似，我们也需要计算使用idx_order_no时需要访问的范围区间数量以及需要回表的记录数，计算过程与上面类似，我们不详列所有计算步骤和说明了。

范围区间数量
使用idx_order_no执行查询时很显然有3个单点区间，所以访问这3个范围区间的二级索引付出的I/O成本就是：
3 x 1.0 = 3.0

需要回表的记录数
由于使用idx_expire_time时有3个单点区间，所以每个单点区间都需要查找一遍对应的二级索引记录数，三个单点区间总共需要回表的记录数是58。（explain看一下）
explain SELECT * FROM order_exp WHERE order_no IN (‘DD00_6S’, ‘DD00_9S’, ‘DD00_10S’);

读取这些二级索引记录的CPU成本就是：58 x 0.2 + 0.01 = 11.61

得到总共需要回表的记录数之后，就要考虑：
根据这些记录里的主键值到聚簇索引中做回表操作，所需的I/O成本就是：58 x 1.0 = 58.0
回表操作后得到的完整用户记录，然后再比较其他搜索条件是否成立
此步骤对应的CPU成本就是：
58 x 0.2 = 11.6
所以本例中使用idx_order_no执行查询的成本就如下所示：
I/O成本：
3.0 + 58 x 1.0 = 61.0 (范围区间的数量 + 预估的二级索引记录条数)
CPU成本：
58 x 0.2 + 58 x 0.2 + 0.01 = 23.21 （读取二级索引记录的成本 + 读取并检测回表后聚簇索引记录的成本）
总成本：
61.0 + 23.21 = 84.21

4.3.3 是否有可能使用索引合并（Index Merge）

本例中SQL语句不满足索引合并的条件，所以并不会使用索引合并。而且MySQL查询优化器计算索引合并成本的算法也比较麻烦，我们不去了解。

4.4. 对比各种方案，找出成本最低的那一个

下边把执行本例中的查询的各种可执行方案以及它们对应的成本列出来：
全表扫描的成本：2169.9
使用idx_expire_time的成本：55.61
使用idx_order_no的成本：84.21
很显然，使用idx_expire_time的成本最低，所以当然选择idx_expire_time来执行查询。来和Tracer中的比较一下：



嗯？除了全表扫描，其他的怎么好像有点对不上呢？翻车了？？？？

请注意：

1. 在MySQL的实际计算中，在和全文扫描比较成本时，使用索引的成本会去除读取并检测回表后聚簇索引记录的成本，也就是说，我们通过MySQL看到的成本将会是：idx_expire_time为47.81(55.61-7.8)，idx_order_no为72.61(84.21-11.6)。但是MySQL比较完成本后，会再计算一次使用索引的成本，此时就会加上前面去除的成本，也就是我们计算出来的值。
2. MySQL的源码中对成本的计算实际要更复杂，但是基本思想和算法是没错的。
   

到此为止，已经大体简单的算出了mysql的计算成本，当然这里是比较简单的，实际mysql真正计算比这里复杂的多。

4.5. 基于索引统计数据的成本 index dive

有时候使用索引执行查询时会有许多单点区间，比如使用IN语句就很容易产生非常多的单点区间，比如下边这个查询（下边查询语句中的…表示还有很多参数）：
SELECT * FROM order_exp WHERE order_no IN (‘aa1’, ‘aa2’, ‘aa3’, … , ‘zzz’);
很显然，这个查询可能使用到的索引就是idx_order_no，由于这个索引并不是唯一二级索引，所以并不能确定一个单点区间对应的二级索引记录的条数有多少，需要我们去计算。就是先获取索引对应的B+树的区间最左记录和区间最右记录，然后再计算这两条记录之间有多少记录（记录条数少的时候可以做到精确计算，多的时候只能估算）。MySQL把这种通过直接访问索引对应的B+树来计算某个范围区间对应的索引记录条数的方式称之为index dive。
有零星几个单点区间的话，使用index dive的方式去计算这些单点区间对应的记录数也不是什么问题，如果IN语句里20000个参数怎么办？
这就意味着MySQL的查询优化器为了计算这些单点区间对应的索引记录条数，要进行20000次index dive操作，这性能损耗就很大，搞不好计算这些单点区间对应的索引记录条数的成本比直接全表扫描的成本都大了。MySQL考虑到了这种情况，所以提供了一个系统变量eq_range_index_dive_limit，我们看一下在MySQL 5.7.21中这个系统变量的默认值：

show variables like '%dive%';
1

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

Cardinality属性，Cardinality直译过来就是基数的意思，表示索引列中不重复值的个数。比如对于一个一万行记录的表来说，某个索引列的Cardinality属性是10000，那意味着该列中没有重复的值，如果Cardinality属性是1的话，就意味着该列的值全部是重复的。不过需要注意的是，对于InnoDB存储引擎来说，使用SHOW INDEX语句展示出来的某个索引列的Cardinality属性是一个估计值，并不是精确的。
前边说道，当IN语句中的参数个数大于或等于系统变量eq_range_index_dive_limit的值的话，就不会使用index dive的方式计算各个单点区间对应的索引记录条数，而是使用索引统计数据，这里所指的索引统计数据指的是这两个值：
使用SHOW TABLE STATUS展示出的Rows值，也就是一个表中有多少条记录。
使用SHOW INDEX语句展示出的Cardinality属性。
结合上一个Rows统计数据，我们可以针对索引列，计算出平均一个值重复多少次。
一个值的重复次数 ≈ Rows ÷ Cardinality

以order_exp表的idx_order_no索引为例，它的Rows值是10354，它对应的Cardinality值是10225，所以我们可以计算order_no列平均单个值的重复次数就是：
10354÷ 10225≈ 1.0126（条）
此时再看上边那条查询语句：
SELECT * FROM order_exp WHERE order_no IN (‘aa1’, ‘aa2’, ‘aa3’, … , ‘zzz’);
假设IN语句中有20000个参数的话，就直接使用统计数据来估算这些参数需要单点区间对应的记录条数了，每个参数大约对应1.012条记录，所以总共需要回表的记录数就是：
20000 x1.0126= 20252
使用统计数据来计算单点区间对应的索引记录条数比index dive的方式简单，但是它的致命弱点就是：不精确！。使用统计数据算出来的查询成本与实际所需的成本可能相差非常大。
注意，在MySQL 5.7.3以及之前的版本中，eq_range_index_dive_limit的默认值为10，之后的版本默认值为200。所以如果大家采用的是5.7.3以及之前的版本的话，很容易采用索引统计数据而不是index dive的方式来计算查询成本。当你的查询中使用到了IN查询，但是却实际没有用到索引，就应该考虑一下是不是由于 eq_range_index_dive_limit 值太小导致的。

5.结语

今儿先记录这么多。
世上无难事，只怕有心人，每天积累一点点，fighting！！！