每天进步一点点，本篇尝试时序记录
欢迎关注公众号「架构染色」交流和学习
1. 事件背景
周五下班后，或是 DBA 同学已下班没找到，或是考虑到我在公司维护着数据库中间件，对数据库这类问题会有一些经验，邢老师找来说是讨论一个奇怪的 sql 执行计划问题，我本是稍有自信，但经过简单上下文同步和一番操作演示讨论后，我也觉得这个情况挺奇怪，让人有点懵。。。
原始案例完整同步的性价比不高，我简单描述一下，能跟读者认知对齐就好；情况大概是这样：一个表里除了有主键，还涉及到另外 3 个索引，A 索引、B 索引、A+B 组合索引，使用不同的索引 explain 中显示的预估行数 rows 的结果是不同的，情况如下，其中第 3 种情况很让人疑惑，为什么只预估扫描4行？
| 索引情况 | 查询计划 | 实际结果行数 | 预估扫描行数 |
| 存在 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，张冠李戴并不是技术人的作风，原理及现象不敢太肯定。
当晚未快速得出结论，各自都还有其他紧急事情要加班处理就没再继续；下班到家时已经 11 点比较晚了，但这个问题一直在大脑里转来转去，导致无法入睡；于是开始查找关于 MySQL explain 跟 rows 有关的资料梳理学习，周末整理出本篇文章。
如果对你有用，欢迎关注公众号「架构染色」交流和学习。
2. 相关技术简述
2.1 B+树组织结构
这种索引情况 MySQL 是以 B+树结构来组织管理索引页和数据页
- 数据页是说包含完整行记录的页（如上图左下侧的 黄框圈注的4个页），索引页是说只包含索引记录的页（上图剩余的其他页）
- 索引是排序的，页的组织管理也依赖于这个有序性
- 聚簇索引（左边）的叶子节点是数据页，非聚簇索引（右边）的叶子节点不是数据页，
- 在非聚簇索引中检索的最终结果是聚簇索引的 key，而不是数据页的 rowID，通过聚簇索引的key再去查找记录；这样低耦合设计是有好处的，比如当空间压缩时，会避免很多页内记录的变更。
2.2 执行计划
- 执行计划是什么
执行计划是数据库的查询优化器根据用户输入的 SQL 语句，以及其内部的执行策略和统计信息选择出一个其认为执行效率最优的计划，然后使用这个计划获取数据。我们通常借助执行计划查看数据库如何处理 SQL 语句，分析性能瓶颈。
- 查看执行计划：
在select前面加explain关键字，执行后可看到下图中的执行计划信息
下表是对执行计划信息各字段的简单介绍，本文的重点是其中的 rows 字段。
3. rows 官网怎么解释
3.1 资料显示
从官网可看到以下描述
rows(JSON name:rows)
Therowscolumn indicates the number of rows MySQL believes it must examine to execute the query.ForInnoDBtables, this number is an estimate, and may not always be exact.
汉化：rows 列表示 MySQL 认为执行查询必须检查的行数。对于 InnoDB，这个数字是一个估计，不一定准确。
2.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.5 统计信息的更新
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%，那么服务器会自动触发一次异步的统计数据的计算 ；其他方式是手动触发。
8. 总结
本篇主要是基于一次日常工作中的技术沟通，以执行计划中 rows 为主线，搜集资料梳理认知；可知识是无限的，到现在也还未能探索出跟预期不一致的实际的计算过程，也只是达到对此知识点有个浅层的系统的认知，帮助后续继续分析探索其内幕；希望本次学习中的记录能够对你有益。
9. 最后说一句(求关注，莫错过)
如果这篇文章对您有所帮助，或者有所启发的话，帮忙扫描下方二维码关注一下，关注公众号：「架构染色」，进行交流和学习。您的支持是我坚持写作最大的动力。
