MySQL为什么选择执行计划A而不选择B？(下)

谦虚的小K
1,282 阅读4分钟

导读

在《MySQL为什么选择执行计划A而不选择B(上)？》中，我讲解了基于索引的两种查询成本分析方案。

在MySQL分析成本的过程中，除了基于索引的分析外，还有一个基于全表扫描的成本分析，今天，我就给大家讲解一下MySQL是如何做全表扫描的成本分析的？

同样，我们就以用户表为例，从CPU成本和IO成本两个维度来看一下MySQL是如何计算全表扫描的成本？

IO成本

MySQL在计算全表扫描的IO成本，采用如下计算公式：

io cost = 全表扫描时间 * 单个节点的io成本 + 1.1

全表扫描时间

那么，全表扫描时间如何计算呢？

全表扫描时间的计算在MyISAM和InnoDB两种存储引擎中是不同的，而我们比较常用的存储引擎为InnoDB，并且案例表user它使用的存储引擎也是InnoDB，所以，我就说一下InnoDB引擎的全表扫描时间的计算方法。

在InnoDB中全表扫描的时间为聚簇索引的所有节点的数量。

以user表为例，我们看下这张表的聚簇索引：

image-20201123214640348.png

上图中是user表的聚簇索引结构，我们数一下所有页节点的个数，发现个数为7，所以，user表的全表扫描时间为7。

单个节点的io成本

那么，单个节点的io成本如何计算呢？计算公式：

内存中节点读成本 + 磁盘中节点的读成本

内存中节点读成本

其中，内存中节点读成本计算如下：

内存中节点读成本 = 内存中节点大小 * MEMORY_BLOCK_READ_COST

其中，MEMORY_BLOCK_READ_COST是一个常量，默认值为0.25，我们也可以使用如下命令修改：

UPDATE mysql.engine_cost
SET cost_value=0.5
WHERE cost_name="memory_block_read_cost"

而内存中节点大小的计算公式如下：

内存中节点大小 = 总节点大小(包含内存和磁盘) * 内存中节点占用百分比

在上面提到计算的是单个节点的io成本，所以，总节点大小 = 1，即一个节点。

内存中节点占用百分比分以下3种情况计算：

  • 总节点大小 < buffer_pool_size的20%

image-20201122225940173.png

如上图，table_size表示表对应的总节点大小，总节点大小 < buffer_pool_size的20%，说明所有节点都在内存中，所以，

内存中节点占用百分比 = 1.0

  • buffer_pool_size的20% < 总节点大小 < buffer_pool_size

image-20201122225756943.png

如上图，buffer_pool_size的20% < 总节点大小(table_size) < buffer_pool_size

内存中节点占用百分比 = 1.0 - (总节点占用buffer_pool_size的百分比 - 0.2)/(1.0 - 0.2)

对应上图就是

内存中节点占用百分比 = 1.0 - (0.75 - 0.2)/(1.0 - 0.2) = 0.3125

  • 总节点大小 > buffer_pool_size

image-20201122230006924.png

如上图，如果总节点大小 > buffer_pool_size，说明内存中放不下所有节点，只能将节点放到磁盘中，内存中无节点，所以，

内存中节点占用百分比 = 0.0

由此，我们得到了内存中节点占用百分比后，又知道了总节点数=1，那么，假设内存中节点占用百分比满足上面第二种情况，然后，结合上面内存中节点大小的计算公式，就可以得出内存中节点数，即

内存中节点大小 = 1 * 0.3125 = 0.3125

内存中节点大小知道后，结合上面内存中节点读成本的计算公式，我们就可以得出内存中节点读成本：

内存中节点读成本 = 0.3125 * 0.25 = 0.078125

磁盘中节点读成本

磁盘中节点读成本计算公式如下：

磁盘中节点读成本 = 磁盘中节点大小 * IO_BLOCK_READ_COST

其中，IO_BLOCK_READ_COST为一个常量，默认为1.0，可以使用如下命令修改：

UPDATE mysql.engine_cost
SET cost_value=0.5
WHERE cost_name="io_block_read_cost"

而磁盘中节点大小的计算公式为

磁盘中节点大小 = 总节点大小 - 内存中节点大小，通过上面的讲解，我们已经知道总节点大小和内存中节点大小，所以，磁盘中节点大小为

1 - 0.3125 = 0.6875

所以，结合上面磁盘中节点读成本的计算公式，得到

磁盘中节点读成本 = 0.6875 * 1.0 = 0.6875

结合上面单个节点的io成本的计算公式，得到

单个节点的io成本 = 0.078125 + 0.6875 = 0.765625

所以，最后，IO总成本计算如下：

IO成本 = 全表扫描时间 * 单个节点的io成本 + 1.1 = 7 * 0.765625 + 1.1 = 6.459375

其中，1.1为MySQL微调固定参数。

CPU成本

CPU成本 = 全表扫描时间 = 7

总成本

最终，全表扫描的成本为CPU成本 + IO成本 = 7 + 6.459375 = 13.459375

小结

现在，我们汇总一下《MySQL为什么选择执行计划A而不选择B(上)？》中案例SQL的查询成本。针对案例SQL：

SELECT * FROM user WHERE age >= 16 AND age < 25 ORDER BY age LIMIT 0, 20

得到查询成本分析结果如下：

  1. 使用索引index_age_birth的成本：6.61
  2. 使用全表扫描的成本：13.459375

由于使用索引index_age_birth的成本比全表扫描低，所以，MySQL最终选择索引index_age_birth执行上面这条SQL。

avatar
文章
阅读
粉丝