手撕面试官必备技能-Mysql内核成本计算初探

置顶一个厨子_1

已于 2023-12-08 17:51:49 修改

阅读量690

点赞数 2

分类专栏：数据库文章标签： mysql 数据库 java

于 2022-11-27 21:00:45 首次发布

本文链接：https://blog.csdn.net/zhuxiaoyu_91/article/details/128069648

版权

数据库专栏收录该内容

8 篇文章 0 订阅

订阅专栏

mysql内核成本计算初探

有些时候我们通过explain 的时候，明明可以用到索引，但是却没有使用，而是走了全盘扫描。因为优化器最终会选择成本最低的那种方案来作为最终的执行计划。但是这些成本怎么来的呢？下面让我们来一探究竟。

mysql的执行成本

mysql主要的执行成本有两方面：

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

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

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

1.0、0.2这些数字称之为成本常数，后面会用到，当然还有其他的成本常数，不同版本的mysql相关参数有所差异：

SHOW TABLES FROM mysql LIKE '%cost%';

SELECT * FROM mysql.engine_cost;

SELECT * FROM mysql.server_cost;

单表成本计算实战

使用到的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 DEFAULT CHARSET = utf8

全盘扫描成本计算

全盘扫描就是把聚集索引的数据依次根据条件判断后从磁盘放入到内存，这个查询成本=IO成本+CPU成本，所以要知道IO成本就要知道聚集索引有多少数据页，总的数据量是多少。这两个信息从哪里来呢。这个可以通过查表来获取,MySQL给我们提供了SHOW TABLE STATUS语句来查看表的统计信息。

SHOW TABLE STATUS LIKE 'order_exp';

Rows:表示表中的记录条数。对于使用MyISAM存储引擎的表来说，该值是准确的，对于使用InnoDB存储引擎的表来说，该值是一个估计值。查出来的数据是10328，我这张表的总记录数是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成本=10328*0.2+1.0=2066.6

10328指的是统计数据中表的记录数，对于InnoDB存储引擎来说是一个估计值，0.2指的

是访问一条记录所需的CPU成本常数，后边的1.0是一个微调值。

全盘扫描成本=98.1+2066.6=2164.7

计算二级索引成本

上述查询可能使用到idx_order_no，idx_expire_time这两个索引，我们需要分别分析单独使用这些索引执行查询的成本，最后还要分析是否可能使用到索引合并（可参考https://blog.csdn.net/sermonlizhi/article/details/124537849）。

这里需要提一点的是，MySQL 查询优化器先分析使用唯一二级索引的成本，再分析使用普通索引的成本，我们这里两个索引都是普通索引，先算哪个都可以。我们先分析idx_expire_time的成本，然后再看使用idx_order_no的成本。

计算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计算这种查询的

成本依赖两个方面的数据：

1、范围区间数量

不论某个范围区间的二级索引到底占用了多少页面，查询优化器认为读取索引的一个范

围区间的I/O成本和读取一个页面是相同的。本例中使用idx_expire_time的范围区间只

有一个，所以相当于访问这个范围区间的二级索引付出的I/O成本就是：1 x 1.0 = 1.0

2、需要回表的记录数

优化器需要计算二级索引的某个范围区间到底包含多少条记录，对于本例来说就是要计

算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 SELECT * FROM order_exp WHERE expire_time> '2021-03-22 18:28:28'

AND expire_time<= '2021-03-22 18:35:09';

读取这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 （读取二级索引记录的成本 + 读取并检测回表

后聚簇索引记录的成本）

综上所述，使用idx_expire_time执行查询的总成本就是：

40 .0 + 15.61 = 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

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

本例中SQL语句不满足索引合并的条件，所以并不会使用索引合并。而且MySQL查询优化

器计算索引合并成本的算法也比较麻烦，我们不去了解。

下边把执行本例中的查询的各种可执行方案以及它们对应的成本列出来：

全表扫描的成本：2164.7

使用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索引。

通过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": "((`testdb`.`order_exp`.`expire_time` > '2021-03-22 18:28:28') and (`testdb`.`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": "((`testdb`.`order_exp`.`order_status` = 0) and (`testdb`.`order_exp`.`order_no` in ('DD00_6S','DD00_9S','DD00_10S')) and (`testdb`.`order_exp`.`insert_time` > `testdb`.`order_exp`.`expire_time`) and (`testdb`.`order_exp`.`order_note` like '%7排1%'))"

    }

  }

}