09-Mysql内核查询成本计算实战-01_mysql跟踪sql执行成本

Mysql内核查询成本计算实战

Optimizer Trace
在MySQL 5.6以及之后的版本中，MySQL提出了一个optimizer trace的功能，这个功能可
以让我们方便的查看优化器生成执行计划的整个过程
比如对于下面这个SQL语句：

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
2
3
4

我们执行如下的命令：

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
4
5
6

可以看见全表扫描的成本：2169.9

使用索引idx_order_no的成本为72.61：

使用索引idx_expire_time的成本为47.81：

最终MySQL使用了idx_expire_time作为这个SQL查询过程中索引：

**优化器最终会选择成本最低的方案来作为最终的执行计划。**

什么是成本

在MySQL中一条查询语句的执行成本是由下边这两个方面组成的：

I/O成本

我们的表经常使用的MyISAM、InnoDB存储引擎都是将数据和索引都存储到磁盘上的，当我们想查询表中的记录时，需要先把数据或者索引加载到内存中然后再操作。这个从磁盘到内存这个加载的过程损耗的时间称之为I/O成本。

CPU成本

读取以及检测记录是否满足对应的搜索条件、对结果集进行排序等这些操作损耗的时间称之为CPU成本。
对于InnoDB存储引擎来说，页是磁盘和内存之间交互的基本单位，MySQL规定读取一个页面花费的成本默认是1.0，读取以及检测一条记录是否符合搜索条件的成本默认是0.2。1.0、0.2这些数字称之为成本常数，这两个成本常数我们最常用到，当然还有其他的成本常数。
注意，不管读取记录时需不需要检测是否满足搜索条件，其成本都算是0.2。

单表查询的成本

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

MySQL为每个表维护了一系列的统计信息
MySQL给我们提供了SHOW TABLE STATUS语句来查看表的统计信息，如果要看指定的某个表的统计信息，在该语句后加对应的LIKE语句就好了，比方说我们要查看order_exp这个表的统计信息可以这么写：
SHOW TABLE STATUS LIKE ‘order_exp’\G

出现了很多统计选项，但我们目前只需要两个：

• 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是一个微调值。
TIPS：MySQL在真实计算成本时会进行一些微调，这些微调的值是直接硬编码到代码里的，没有注释而且这些微调的值十分的小，并不影响我们大方向的分析。

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

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