MySQL 执行计划中的rows到底是什么，你真的了解过？

吾爱主题阅读：111 2024-04-01 23:26:01 评论：0

1. 事件背景

周五下班后，或是DBA同学已下班没找到，或是考虑到我在公司维护着数据库中间件，对数据库这类问题会有一些经验，邢老师找来说是讨论一个奇怪的sql执行计划问题，我本是稍有自信，但经过简单上下文同步和一番操作演示讨论后，我也觉得这个情况挺奇怪，让人有点懵。。。

原始案例完整同步的性价比不高，我简单描述一下，能跟读者认知对齐就好；情况大概是这样：一个表里除了有主键，还涉及到另外3个索引，A索引、B索引、A+B组合索引，使用不同的索引explain中显示的预估行数rows的结果是不同的，情况如下，其中第3条很让人疑惑：

索引情况	查询计划	实际结果行数	预估扫描行数
存在A、B两个字段的独立索引	仅命中A索引检索（where a= xxx）	26	26
存在A、B两个字段的独立索引	仅命中B索引（where b=yyy）	256	255
存在A、B两个字段的独立索引	命中A索引和B索引（where a= xxx and b = yyy）	9	4
有A+B两个字段的组合索引	命中A+B组合索引（where a= xxx and b = yyy）	9	9

已经好久没专门研究数据库底层的东西了，多年前曾对《SQL Server技术内幕》系列丛书有潜心研读，略有积累，这几本书分别是：T-SQL程序设计，T-SQL查询，存储引擎，查询调整及优化（如果用到SQL Server的话，这些书推荐去看看）；虽然对SQL Server执行计划调优这方面有一些认知储备，但当天讨论的毕竟是MySQL，张冠李戴并不是技术人的作风，原理及现象不敢太肯定。

2. 相关技术简述

2.1 B+树组织结构

这种索引情况MySQL 是以B+树结构来组织管理索引页和数据页

数据页是说包含完整行记录的页（如上图左下侧的黄框圈注的4个页），索引页是说只包含索引记录的页（上图剩余的其他页）
索引是排序的，页的组织管理也依赖于这个有序性
聚簇索引（左边）的叶子节点是数据页，非聚簇索引（右边）的叶子节点不是数据页，
在非聚簇索引中检索的最终结果是聚簇索引的key，而不是数据页的rowID；这样低耦合设计是有好处的，比如当空间压缩时，会避免很多页内记录的变更。

2.2 执行计划

执行计划是什么

执行计划是数据库的查询优化器根据用户输入的SQL语句，以及其内部的执行策略和统计信息选择出一个其认为执行效率最优的计划，然后使用这个计划获取数据。我们通常借助执行计划查看数据库如何处理SQL语句，分析性能瓶颈。

查看执行计划：

在select前面加explain关键字，执行后可看到下图中的执行计划信息

下表是对执行计划信息各字段的简单介绍，本文的重点是其中的rows字段。

3. rows官网怎么解释

3.1 资料显示

从官网可看到以下描述

rows (JSON name: rows)

Therows column indicates the number of rows MySQL believes it must examine to execute the query.For InnoDB tables, this number is an estimate, and may not always be exact.

汉化：rows 列表示MySQL认为执行查询必须检查的行数。对于InnoDB，这个数字是一个估计，不一定准确。

3.2 所思所想

官网这话很精辟，但其内部的一些关键设计却并未提及。

4. 关于Rows的一种解释A

4.1 资料显示

在查询优化器决定使用全表扫描的方式对某个表执行查询时，执行计划的rows列就代表该表的估计行数。
如果使用索引来执行查询，执行计划的rows列就代表预计扫描的索引记录行数。

4.2 所思所想

全表扫描时：rows对应的是仅数据页中预计要扫描的行记录数量嘛？
索引扫描时：rows对应的是预计扫描的索引记录行数

如果是聚簇索引，那这个行数是索引页+数据页中的记录行数嘛？

如果是非聚簇索引，那这个行数是仅非聚簇索引页中的索引记录行数嘛？

既然是扫描，那为什么又会说数据不准呢？这里为什么没提及统计信息呢？SQL Server中执行计划评估的扫描行数是跟统计信息有关的，莫非MySQL不是？

5. 关于Rows的一种解释B

5.1 资料显示

如果查询优化器决定使用全表扫描的方式对某个表执行查询时，执行计划的 rows 列就代表预计需要扫描的行数。

如果使用索引来执行查询时，执行计划的 rows 列就代表预计扫描的索引记录行数。

这有可能是个精确值，也可能是个估算值，计算方法有 index dive 和基于索引统计的估算。

5.2 所思所想

1、2两条跟A说法相似，且未提到更多的细节，但第3条信息就很重要了，给前边的疑问提供了一些线索，MySQL也是会基于统计信息来选择执行计划的，统计信息是会有误差的；只是 index dive 是什么呢？统计信息又是怎样的实现机制呢？

6. 关于index div的解释

6.1 Index dive是什么

获取索引对应的B+树的区间最左记录和区间最右记录，然后再计算这两条记录之间有多少记录（记录条数少的时候可以做到精确计算，多的时候只能估算）。MySQL把这种通过直接访问索引对应的B+树来计算某个范围区间对应的索引记录条数的方式称之为Index dive。

跟Index dive相关的有一个配置参数 eq_range_index_dive_limit，作用大概是这样：

当where语句in条件中参数个数小于这个值的时候，MySQL就采用Index dive的方式预估扫描行数，非常准确。
当where语句in条件中参数个数大于等于这个值的时候，MySQL就采用另一种方式索引统计预估扫描行数，误差较大。
MySQL 不同的版本中这个默认值不同，可以根据需求场景进行调整。

6.2 所思所想

从这个信息再次看出，采用Index div会较精准的预估扫描行数，但估算成本较高，适合小数据量。

索引统计估算成本较低，适合数据量大的情况。但使用索引统计的话，评估不准，甚至误差很大，为什么误差大以及误差到底有多大，接下来再搜集相关资料来了解。

7. 关于统计信息的解释

7.1 统计信息介绍

查询优化是在代价统计分析的基础上进行的，合理的代价模型和准确的代价统计信息决定了查询优化的优劣。My SQL的代价模型依赖的主要因素是IO和CPU，IO主要跟数据量和缓存相关，CPU主要跟参与排序比较的记录数相关。因此统计信息的指标主要是数据量和记录数，如：

table scan：全表扫描统计信息包括数据量和记录数。
index scan：索引统计信息，索引键值分布情况，即cardinality。
range scan：索引范围扫描统计信息，一定范围内的记录数和数据量。

7.2 查看索引统计

innodb的统计信息

mysql.innodb_table_stats ：存储了关于表的统计数据，每一条记录对应着一个表的统计数据
mysql.innodb_index_stats ：存储了关于索引的统计数据，每一条记录对应着一个索引的一个统计项的统计数据。

以innodb_table_stats表为例，各个列的说明：

列名	说明
database_name	数据库名
table_name	表名
last_update	本条记录最后更新时间
n_rows	表中记录的条数
clustered_index_size	表的聚簇索引占用的页面数量
sum_of_other_index_sizes	表的其他索引占用的页面数量

显而易见，这其中的n_rows很关键，那他的值是怎么算的呢？

7.3 统计信息的采样

执行计划中的预估的行数依赖n_rows，InnoDB中n_rows的统计是这样的：

按照一定算法（并不是纯粹随机的）选取几个叶子节点页面
计算每个页面中主键值记录数量
计算平均一个页面中主键值的记录数量乘以全部叶子节点的数量就算是该表的n_rows值

由此可知n_rows值是否精确取决于统计时采样的页面数量，通过innodb_stats_persistent_sample_pages设置，设置的越大，统计出的相对越精确，但是耗时也会增加；设置得越小，统计出的值越不精确，但是统计耗时就少，要视实际情况而定。

7.4 统计信息的更新

MySQL中以下情况会触发统计信息更新：

距离上一次更新统计信息，发生变化的行数超过一定数值时自动更新(transient：1/16， persistent ：1/10）
analyze table
create table/truncate table 会初始化统计信息
查询information_schema.tables information_schema.statistic(innodb_stats_on_metadata=ON)

其第一种是发生变动的记录数量超过了表大小的10%，那么服务器会自动触发一次异步的统计数据的计算；其他方式是手动触发。