MySQL内核查询成本计算

在《索引优化(一)》的文章中，提到可以通过trace工具来查看MySQL是如何选择索引的。
使用方式：

set session optimizer_trace="enabled=on",end_markers_in_json=on;
select * from employees where name > 'lizhi' order by position;
SELECT * FROM information_schema.OPTIMIZER_TRACE;

在选择索引的过程中，最重要的指标就是查询成本，通过比较成本的值来选择合适的索引，这篇文章的重点就是介绍MySQL是如何来计算查询成本。

一、什么是成本

在MySQL中，一条查询语句的执行成本主要有两个方面组成：

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

2、CPU成本

读取以及检索记录是否满足对应的搜索条件、对结果集进行排序等这些操作损耗的时间称之为CPU成本。

对于InnoDB存储引擎来说，页是磁盘和内存交互的基本单位。MySQL规定读取一个页面花费的成本默认是1.0，读取以及检测一条记录是否符合搜索条件的成本默认是0.2。

1.0、0.2这些数字称之为成本常数，这两个是用的最多的，还有其他成本常数。

注：不管读取记录时需不需要检测是否满足搜索条件，其成本都算是0.2

二、单表查询成本计算

2.1 数据准备

测试数据依然使用之前文章中employees表的数据，hir_time列新创建了一个索引，建表语句如下：

CREATE TABLE employees (
  id int(11) NOT NULL AUTO_INCREMENT,
  name varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
  age int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
  position varchar(20) NOT NULL DEFAULT '' COMMENT '职位',
  hire_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
  PRIMARY KEY (id),
  KEY idx_name_age_position (name,age,position) USING BTREE,
  KEY idx_hire_time (hire_time) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='员工记录表'

2.2 查询所有可能使用的索引

一个MySQL查询语句中可能使用的索引可以在执行计划的possible keys中看到
以下面的SQL为例：

SELECT * FROM employees where name IN ('lizhi','linan','zhuyuzhu') AND hire_time > '2022-03-30 22:00:00' AND hire_time < '2022-03-30 23:00:00';

employees表有以name开头的一个联合索引，还有一个hire_time的单值索引，所以通过分析，可以确定：

name IN ('lizhi','linan','zhuyuzhu')可以使用idx_name_age_position索引，hire_time > '2022-03-30 22:00:00' AND hire_time < '2022-03-30 23:00:00'可以使用idx_hire_time索引，也可以直接通过Explain查看可能使用的索引

2.3 计算全表扫描的代价

Trace工具中查询出来的全表扫描的成本如下：

全表扫面就是把聚簇索引所有页的数据全部加载进内存，然后依次与查询的条件进行比对。

由于查询成本 = I/O成本 + CPU成本，所以计算全表扫描的代价就包含了这两部分内容。

计算这两部分的内容就需要直到employees表的数据页数和总记录数，可以通过下面的命令来查看:

show table status LIKE 'employees';

2.3.1 数据统计

表中统计数据如下：

表的状态信息包含了很多字段，我们只需要关注Rows和Data_length这两个字段的值，这两个值都是统计数据，并不是绝对准确的。
Rows
本选项表示表中的记录条数。对于使用MyISAM存储引擎的表来说，该值是准确的，对于使用InnoDB存储引擎的表来说，该值是一个估计值。从查询结果我们也可以看出来，employees表使用的是InnoDB存储引擎，所以Rows是一个估计值94182，实际上该表有100003条数据
Data_length
该项记录表中数据的占用的存储空间记录数，使用MyISAM存储引擎的表来说，该值就是数据文件的大小，对于使用InnoDB存储引擎的表来说，该值就相当于聚簇索引占用的存储空间大小，也就是：

data_length = 聚簇索引页面数量*页面大小

employees表默认页的大小为16KB，查询显示的数据量为5783552，占用的数据页为：

page = data_length/16kb = 5783552/16kb=353

2.3.2 成本计算

根据统计数据，我们可以获得表中总记录数以及通过计算得到总的数据页数，这样就可以计算全表扫描的成本了：

I/O成本

353 * 1.0 + 1.1 = 354.1

353是聚簇索引占用的页面数，1.0指的是加载一个页面的I/O成本常数，后面的1.1是一个微调值

MySQL在真实计算成本时会进行一些微调，这些微调的值是直接硬编码到代码里的，这些微调的值十分的小，并不影响分析

CPU成本

94182 * 0.2 +1.0 = 18837.4

94182是MySQL统计的表中记录数，对于Innodb存储引擎这是一个估计值。0.2是访问一条记录的CPU成本常数，后面的1.0是一个微调数。

总成本

354.1+18837.4 = 19191.5

通过trace工具查看的全表扫描的成本为19192,与我们计算出来的结果基本对的上。

2.4 计算不同索引执行查询的代价

在第一步中，通过分析知道上述查询可以用idx_name_age_position和idx_hire_time两个索引，就要分别计算使用这两个索引的成本，最后还要分析是否使用索引合并(后面文章会介绍)，MySQL查询优化器先分析使用唯一二级索引的成本，再分析使用普通索引的成本，这里两个索引都是普通索引，就先分析使用idx_name_age_position的成本，再分析idx_hire_time。

2.4.1 使用索引idx_name_age_position

Trace工具查询出来的使用该索引的成本：

idx_name_age_position索引对应的查询条件是：

name IN ('lizhi','linan','zhuyuzhu')

使用idx_name_age_position查询会使用二级索引和回表的方式，所以这种MySQL查询的成本就包含了两方面的成本。

范围区间数量

不论某个范围区间的二级索引到底占用了多少页面，查询优化器认为读取索引的一个范围区间的I/O成本和读取一个页面是相同的。

而这个查询条件相当于是三个单点区间，所以访问这三个区间范围的二级索引的I/O成本为：

3 * 1.0 = 3.0

需要回表的记录数

从二级索引查询到满足条件的记录后，就需要进行回表，而每次回表都是一次I/O操作。

通过EXPLAIN SELECT * FROM employees where name IN ('lizhi','linan','zhuyuzhu')语句可以看到回表的记录数为3。

所以读取这些记录的CPU成本：

3 * 0.2 + 0.01(微调值) = 0.61

回表的I/O成本：

3 * 1.0 = 3

回表将数据加载进内存后，还要判断这些数据是否满足其他条件，这里面还涉及到了回表后的CPU成本：

3 * 0.2 = 0.6
所以最后总的成本就是：

3.0 + 0.61 + 3.0 + 0.6 = 7.21

从计算结果看，与Trace工具中查询到的成本好像对不上，这个问题下面会统一解释。

2.4.2 使用索引idx_hire_time

Trace中查询到的使用该索引的成本如下：

范围区间数量

使用idx_hire_time索引对应的查询条件是：

hire_time > '2022-03-30 22:00:00' AND hire_time < '2022-03-30 23:00:00'

也是就是查询的范围是(2022-03-30 22:00:00,2022-03-30 23:00:00)，前面说过读取索引的一个范围区间的I/O成本和读取一个页面是相同的。所以访问这个范围区间的二级索引的I/O成本就是：1 * 1.0 = 1.0。

计算回表记录数

对于一个二级索引，计算某个范围包含多少条记录的步骤大概如下：

1、从二级索引树中找到满足条件的第一个数据记录和最后一个数据记录(这个过程是很快的，消耗忽略不计)

2、如果区间最左记录和区间最右记录相隔不太远(在MySQL 5.7这个版本里，只要相隔不大于10个页面即可)，那就可以精确统计出满足条件的记录数(B+树叶子节点是链表连接的)。

如果区间范围超过了10个页面，则从最左边记录开始，向右读取10个页面，然后计算平均每个页包含的记录数，然后根据这个平均数乘以区间的页面数即可。这样计算出来的就只是一个大概值。

注：计算区间有多少个页面的方式需要参考B+树的结构了，可以计算他们父节点间的间隔数，就可以得到区间的页面数，如果父节点也相隔较远，就再向父节点的父节点查找。

通过EXPLAIN SELECT * FROM employees where hire_time > '2022-03-30 22:00:00' AND hire_time < '2022-03-30 23:00:00';语句可以看到需要回表的记录数为523。

因此CPU从二级索引中取出这些数据的成本：

523 * 0.2 + 0.01 = 104.61

从二级索引取到这些数据之后还需要进行回表，回表后还要判断其他条件，因此回表操作的成本也包含了I/O成本和CPU成本。

I/O成本：

523 * 1.0 = 523.0

CPU成本：

523 * 0.2 = 104.6

所以使用idx_hire_time查询的成本如下：

总I/O成本:

1.0 + 523.0 = 524.0

总CPU成本：

104.61 + 104.6 = 209.21

总成本：

524.0 + 209.21 = 733.21

2.4.3 是否可能使用索引合并

该SQL语句里面使用索引的方式不符合索引合并的条件，所以这里就没有使用索引合并。

2.5 比对方案，找出成本最低的一个

Trace工具中看到MySQL最终选择的方案以及成本如下：

通过上面的计算，全表扫描和分别使用两种索引的查询成本如下：

上面计算不同索引的查询成本时还遗留了一个问题，除了全表扫描的成本，使用索引的查询成本与自己计算的有较大出入，这是因为在MySQL的实际计算中，在和全文扫描比较成本时，使用索引的成本会去除掉读取和检测回表后聚簇索引记录的成本，也就是说，我们通过MySQL看到的成本将会是idx_name_age_position为6.61(7.21-0.6),idx_hire_time为628.21(733.21-104.6)。但是MySQL比较完成本后，会再计算一次使用索引的成本，此时就会加上前面去除的成本，也就是我们计算出来的值

2.6 基于索引统计数据成本

2.6.1 Index Dive

在介绍这一部分前，先介绍一下什么是单点区间：

对于InnoDB来说，除了主键索引和唯一索引外，其他索引的等值判断是通过区间来完成的，比如查询条件为name = lizhi，但执行的时候会按照lizhi <= name <= lizhi来查找，这就是一个单点区间。至于为什么这样，主要原因是对于一个非唯一的字段来说，它作为索引，可能很多记录中该字段的值都是一样的，这样可以加速查找。

有时候通过索引查询时有许多单点区间，比如：

EXPLAIN SELECT * FROM employees where name IN ('lizhi','linan','zhuyuzhu'...'zzz');

idx_name_age_position又不是唯一二级索引，所以并不能确定一个单点区间中对应二级索引的记录数，每次都需要先获取区间最左和最右的记录，然后计算每个单点区间的记录数。MySQL把这种通过直接访问索引对应的B+树来计算某个范围区间对应的索引记录条数的方式称之为index dive。

但如果单点区间太多了，比如IN语句里20000个参数，还要对每个区间进行精细计算就是非常浪费时间的。(记录条数少的时候可以做到精确计算，多的时候只能估算)

MySQL考虑到了这种情况，所以提供了一个系统变量eq_range_index_dive_limit，在MySQL 5.7.21中这个系统变量的默认值：

show variables like '%dive%';

也就是说如果我们的IN语句中的参数个数小于200个的话，将使用index dive的方式计算各个单点区间对应的记录条数，如果大于或等于200个的话，可就不能使用index dive了，要使用所谓的索引统计数据来进行估算。

2.6.2 非唯一二级索引单点区间的记录数估算

MySQL为表中的每一个索引维护一份统计数据(不精确)，查看某个表中索引的统计数据可以使用SHOW INDEX FROM 表名的语法，比如：

show index from employees;

统计数据如下：

idx_name_age_position是一个联合索引，所以索引信息里面分别展示了这三个字段对应的信息。

其中最重要的就是Cardinality字段，它记录了索引列中不重复值的数量。

我们可以通过它来计算每一个非唯一二级索引的单点区间平均对应多少行记录。

以idx_hire_time为例：

它的hire_time属性对应的Rows是94182(可以通过show table STATUS LIKE 'employees'查看)，而它的Cardinality也是8383，所以我们可以计算name列平均单个值的重复次数就是:

94182 ÷ 8383= 11.2348

就意味着hire_time每个单点区间有11.238条记录。

假设IN语句中有20000个参数的话，就直接使用统计数据来估算这些参数需要单点区间对应的记录条数了，每个参数大约对应11.2348条记录，所以总共需要回表的记录数就是：

20000 * 11.2348= 224,696

使用统计数据来计算单点区间对应的索引记录条数比index dive的方式简单，但是它的致命弱点就是：不精确！使用统计数据算出来的查询成本与实际所需的成本可能相差非常大。

注：在MySQL 5.7.3以及之前的版本中，eq_range_index_dive_limit的默认值为10，之后的版本默认值为200

三、Explain输出成本信息

除了使用trace工具输出SQL执行的细节外，还可以通过Explain输出查询使用的成本，使用方式就是在Explain关键字与查询语句之间加上FORMAT=JSON,比如：

explain format=json SELECT * FROM employees where name IN ('lizhi','linan','zhuyuzhu') AND hire_time > '2022-03-30 22:00:00' AND hire_time < '2022-03-30 23:00:00';

查询出来的查询成本的详细信息如下：

{
  "query_block": {
    "select_id": 1, -- 整个查询语句只有1个SELECT关键字，该关键字对应的id号为1
    "cost_info": {
      "query_cost": "7.21"  -- 整个查询的执行成本预计为7.21
    } /* cost_info */,
    "table": {
      "table_name": "employees",
      "access_type": "range",
      "possible_keys": [
        "idx_name_age_position",
        "idx_hire_time"
      ] /* possible_keys */,
      "key": "idx_name_age_position",
      "used_key_parts": [
        "name"
      ] /* used_key_parts */,
      "key_length": "74",
      "rows_examined_per_scan": 3,
      "rows_produced_per_join": 0,
      "filtered": "1.67",
      "index_condition": "(`test`.`employees`.`name` in ('lizhi','linan','zhuyuzhu'))",
      "cost_info": {
        "read_cost": "7.20",
        "eval_cost": "0.01",
        "prefix_cost": "7.21", -- 单独查询表的成本，也就是：read_cost + eval_cost
        "data_read_per_join": "7"
      } /* cost_info */,
      "used_columns": [
        "id",
        "name",
        "age",
        "position",
        "hire_time"
      ] /* used_columns */,
      "attached_condition": "((`test`.`employees`.`hire_time` > '2022-03-30 22:00:00') and (`test`.`employees`.`hire_time` < '2022-03-30 23:00:00'))"
    } /* table */
  } /* query_block */
}

使用Explain查询到的成本信息，只有MySQL真正的执行方案对应的一些成本信息，没有全表扫描或者其他索引的成本信息。

四、连接查询的成本

对驱动表进行查询后得到的记录条数称之为驱动表的扇出（fanout）。很显然驱动表的扇出值越小，对被驱动表的查询次数也就越少，连接查询的总成本也就越低。

连接查询的成本计算公式是这样的：

连接查询总成本 = 单次访问驱动表的成本 + 驱动表扇出数 x 单次访问被驱动表的成本

对于外连接来说，它们的驱动表是固定的，所以最有的查询方案就是分别为驱动表和被驱动表选择最优的查询方案即可。

但对于内连接而言，驱动表由MySQL通过查询成本计算来决定，因为参与连接的每一个表都有可能是驱动表，所以需要分别计算每张表作为驱动表时的总的查询成本，然后选择总成本最低的方案。

以下面的内连接为例(employees和employees_copy表结构一样，只是后者数据量较少)：

SELECT * FROM employees ems INNER JOIN employees_copy emc ON ems.name = emc.name AND ems.hire_time > '2022-03-30 22:00:00' AND ems.hire_time < '2022-03-30 23:00:00' AND emc.hire_time > '2022-04-01 14:00:00' AND emc.hire_time < '2022-04-01 15:00:00';

可以有两种连接顺序：

1、employees连接employees_copy，即employees作为驱动表

2、employees_copy连接employees，即employees_copy作为驱动表

所以需要分别计算这两种查询方式的查询成本，然后选择最优的。

4.1 以employees作为驱动表

首先看一下employees表关联的查询条件：

ems.name = emc.name AND ems.hire_time > '2022-03-30 22:00:00' AND ems.hire_time < '2022-03-30 23:00:00'

所以这个查询可能用到idx_hire_time索引，从全表扫描和idx_hire_time索引中选择查询成本最低的方案。
然后分析对于被驱动表的成本最低的执行方案，此时涉及被驱动表employees_copy的搜索条件就是：

1、emc.name = 常数(这是因为对驱动表结果集中的每一条记录，都需要进行一次被驱动表的访问，此时那些涉及两表的条件现在相当于只涉及被驱动表了。)

2、emc.hire_time > ‘2022-04-01 14:00:00’ AND emc.hire_time < ‘2022-04-01 15:00:00’

由于第一个条件name也有索引，所以访问被驱动表的方式有：全表扫描，使用idx_name_age_position索引和使用idx_hire_time索引

所以此时使用employees作为驱动表时的总成本就是（暂时不考虑使用join buffer对成本的影响）：

使用idx_hire_time访问employees的成本 + employees的扇出 × 使用最低成本访问employees_copy的成本

4.2 以employees_copy作为驱动表

使用employees_copy作为驱动表的成本与使用employees作为驱动表的成本计算方式是一样的

4.3 通过Explain输出查询成本

{
  "query_block": {
    "select_id": 1,    -- 整个查询语句只有1个SELECT关键字，该关键字对应的id号为1
    "cost_info": {
      "query_cost": "5.20"   -- 整个查询的成本
    } /* cost_info */,
    "nested_loop": [   -- 几个表之间采用嵌套循环连接算法执行
      {
        "table": {
          "table_name": "emc",   -- employees_copy是驱动表
          "access_type": "ALL",  -- 采用全表扫描的方式(数据量很少)
          "possible_keys": [
            "idx_name_age_position",
            "idx_hire_time"
          ] /* possible_keys */,
          "rows_examined_per_scan": 3,  -- 查询employees_copy大致需要扫描的行数
          "rows_produced_per_join": 3,  -- 驱动表employees_copy的扇出数
          "filtered": "100.00",         -- condition filtering代表的百分比
          "cost_info": {
            "read_cost": "1.00",
            "eval_cost": "0.60",
            "prefix_cost": "1.60",     -- 查询employees总的成本(read_cost+eval_cost)
            "data_read_per_join": "456"  -- 读取的数据量
          } /* cost_info */,
          "used_columns": [
            "id",
            "name",
            "age",
            "position",
            "hire_time"
          ] /* used_columns */,
          "attached_condition": "((`test`.`emc`.`hire_time` > '2022-04-01 14:00:00') and (`test`.`emc`.`hire_time` < '2022-04-01 15:00:00'))"
        } /* table */
      },
      {
        "table": {
          "table_name": "ems",   -- employees是被驱动表
          "access_type": "ref",
          "possible_keys": [
            "idx_name_age_position",
            "idx_hire_time"
          ] /* possible_keys */,
          "key": "idx_name_age_position",
          "used_key_parts": [
            "name"
          ] /* used_key_parts */,
          "key_length": "74",
          "ref": [
            "test.emc.name"
          ] /* ref */,
          "rows_examined_per_scan": 1,  -- 查询一次employees表大致需要扫描1条记录
          "rows_produced_per_join": 0,  -- 被驱动表employees的扇出
          "filtered": "5.00",           -- condition filtering代表的百分比
          "cost_info": {
            "read_cost": "3.00",
            "eval_cost": "0.03",
            "prefix_cost": "5.20",     -- 单词查询employees_copy和多次查询employees的总成本
            "data_read_per_join": "22"
          } /* cost_info */,
          "used_columns": [
            "id",
            "name",
            "age",
            "position",
            "hire_time"
          ] /* used_columns */,
          "attached_condition": "((`test`.`ems`.`hire_time` > '2022-03-30 22:00:00') and (`test`.`ems`.`hire_time` < '2022-03-30 23:00:00'))"
        } /* table */
      }
    ] /* nested_loop */
  } /* query_block */
}

五、多表连接的成本分析

与双表连接一样，多表连接只是有更多种组合方式而已，MySQL需要一次计算每种组合方式的查询成本，然后选择最优的方案。
所以非常不推荐使用多表进行内连接。

六、调节成本常数

前面成本分析时提到了两个成本常数，I/O访问和CPU的成本常数。

读取一个页面花费的成本默认是1.0

检测一条记录是否符合搜索条件的成本默认是0.2

MySQL还支持很多，它们被存储到了MySQL数据库的两个表中：

SHOW TABLES FROM mysql LIKE '%cost%';

6.1 mysql.server_cost

server_cost表中在server层进行的一些操作对应的成本常数

SELECT * FROM mysql.server_cost;

从server_cost中的内容可以看出来，目前在server层的一些操作对应的成本常数有以下几种：

6.2 mysql.engine_cost

engine_cost表表中在存储引擎层进行的一些操作对应的成本常数，具体内容如下：

SELECT * FROM mysql.engine_cost;

engine_name列
指成本常数适用的存储引擎名称。如果该值为default，意味着对应的成本常数适用于所有的存储引擎。

device_type列
指存储引擎使用的设备类型，这主要是为了区分常规的机械硬盘和固态硬盘，不过在MySQL 5.7.X这个版本中并没有对机械硬盘的成本和固态硬盘的成本作区分，所以该值默认是0。

从engine_cost表中的内容可以看出来，目前支持的存储引擎成本常数只有两个：