每天进步一点点，本篇测验时序记载

欢迎重视公众号「架构染色」沟通和学习

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+树结构来安排办理索引页和数据页

滴水穿石系列：MySQL 执行计划中的rows到底是什么，你真的了解过？

  • 数据页是说包括完好行记载的页（如上图左下侧的 黄框圈注的4个页），索引页是说只包括索引记载的页（上图剩余的其他页）
  • 索引是排序的，页的安排办理也依靠于这个有序性
  • 聚簇索引（左面）的叶子节点是数据页，非聚簇索引（右边）的叶子节点不是数据页，
  • 在非聚簇索引中检索的终究成果是聚簇索引的 key，而不是数据页的 rowID，经过聚簇索引的key再去查找记载；这样低耦合规划是有优点的，比如当空间紧缩时，会防止许多页内记载的改变。
2.2 履行方案
  • 履行方案是什么

履行方案是数据库的查询优化器根据用户输入的 SQL 句子，以及其内部的履行策略和核算信息挑选出一个其认为履行功率最优的方案，然后运用这个方案获取数据。咱们通常借助履行方案检查数据库如何处理 SQL 句子，分析功能瓶颈。

  • 检查履行方案：

在select前面加explain关键字，履行后可看到下图中的履行方案信息

滴水穿石系列：MySQL 执行计划中的rows到底是什么，你真的了解过？

下表是对履行方案信息各字段的简略介绍，本文的重点是其间的 rows 字段。

滴水穿石系列：MySQL 执行计划中的rows到底是什么，你真的了解过？

3. rows 官网怎样解说

3.1 材料显现

从官网可看到以下描绘

  • rows (JSON name: rows)

    The rows 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，这个数字是一个估量，不必定准确。

2.2 所思所想

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

4. 关于 Rows 的一种解说 A

4.1 材料显现
  • 在查询优化器决议运用全表扫描的方法对某个表履行查询时，履行方案的 rows 列就代表该表的估量行数。
  • 假如运用索引来履行查询，履行方案的 rows 列就代表估计扫描的索引记载行数。
4.2 所思所想
  • 全表扫描时：rows 对应的是仅数据页中估计要扫描的行记载数量嘛？
  • 索引扫描时：rows 对应的是估计扫描的索引记载行数
    • 假如是聚簇索引，那这个行数是 索引页+数据页中的记载行数嘛？
    • 假如是非聚簇索引，那这个行数是仅非聚簇索引页中的索引记载行数嘛？
  • 既然是扫描，那为什么又会说数据禁绝呢？这儿为什么没提及核算信息呢？SQL Server 中履行方案评价的扫描行数是跟核算信息有关的，莫非 MySQL 不是？

5. 关于 Rows 的一种解说 B

5.1 材料显现

  1. 假如查询优化器决议运用全表扫描的方法对某个表履行查询时，履行方案的 rows 列就代表估计需求扫描的行数。

  2. 假如运用索引来履行查询时，履行方案的 rows 列就代表估计扫描的索引记载行数。

  3. 这有可能是个准确值，也可能是个预算值，核算方法有 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 中以下状况会触发核算信息更新：

  1. 间隔上一次更新核算信息，产生变化的行数超越必定数值时主动更新(transient：1/16， persistent ：1/10）

  2. analyze table

  3. create table/truncate table 会初始化核算信息

  4. 查询 information_schema.tables information_schema.statistic(innodb_stats_on_metadata=ON)

​ 其第一种是产生变动的记载数量超越了表巨细的 10%，那么服务器会主动触发一次异步的核算数据的核算 ​；其他方法是手动触发。

8. 总结

本篇主要是根据一次日常工作中的技能沟通，以履行方案中 rows 为主线，搜集材料整理认知；可知识是无限的，到现在也还未能探究出跟预期不一致的实践的核算进程，也仅仅到达对此知识点有个浅层的系统的认知，协助后续继续分析探究其内情；希望本次学习中的记载可以对你有益。

9. 最终说一句(求重视，莫错过)

假如这篇文章对您有所协助，或许有所启发的话，帮助扫描下方二维码重视一下，重视公众号：「架构染色」，进行沟通和学习。您的支持是我坚持写作最大的动力。

求一键三连：点赞、保藏、转发。

滴水穿石系列：MySQL 执行计划中的rows到底是什么，你真的了解过？

长按可订阅

Reference：

dev.mysql.com/doc/refman/…

blog.csdn.net/u022812849/…

www.cnblogs.com/ldws/p/1234…

baijiahao.baidu.com/s?id=174065…

mp.weixin.qq.com/s/-7qU1MPlB…

baijiahao.baidu.com/s?id=174065…