MySQL优化器估算SQL语句访问行数的深入分析

MySQL优化器估算SQL语句访问行数的深入分析

一、背景介绍

在数据库运维工作中，慢SQL是一个常见问题。导致慢SQL问题的原因很多，常见的包括资源瓶颈（CPU、磁盘、网络等资源打满）、不合理的参数配置、SQL语句自身问题以及SQL代价估算不准确等。其中，SQL代价估算不准确是慢SQL的TOP根因之一。这类问题的复杂性通常与客户业务强相关，且往往需要详细查看执行计划才能确定错误原因。

二、原理介绍

1. 行数估算的重要性

在分析慢SQL的过程中，DBA经常需要执行EXPLAIN命令来查看SQL访问每张表的路径和预估访问行数，来判断是否是最优的执行计划。优化器对一张表的访问行数估算有以下两种方式：使用统计信息进行估算和优化器实时下探（Index Dive）。

2. 统计信息的作用

这些统计信息平时是自动更新的（例如 ANALYZE TABLE 命令会更新统计信息），优化器在查询过程中会实时使用这些统计信息。MySQL 优化器会通过 TABLE_SHARE 结构体来获取表的基本信息。这其中包括表的行数（ TABLE_SHARE::table_rows）和列基数（ TABLE_SHARE::column_cardinality）。

三、行数估算方式

1. 使用统计信息进行估算

对于非唯一索引的等值查询条件个数大于 eq_range_index_dive_limit 个时，优化器会使用统计信息中的 (记录总数 / 不同值个数) 来估算平均访问行数；对于全表扫描，优化器会直接使用统计信息中的表总记录数进行估算。

2. 实时下探（Index Dive）

在SQL优化阶段，根据条件谓词进行B+树的下探。例如，对于索引列的范围查询（index_column > x），优化器会使用x下探采样，估算大于x的记录个数；对于非唯一索引的等值查询，如果等值条件数小于 eq_range_index_dive_limit 个数，也会进行下探以获取更精确的估算结果。

四、实时下探算法源码分析

1. Index Dive算法流程

针对SQL查询中，对非唯一索引的查找或者对索引前缀的查找（这种场景可能返回多行结果），优化器会在优化阶段，利用范围条件对索引B+树进行下探，来估算扫描行数。由于是实时下探，所以下探的代价不能太大。

2. 源码实现

在InnoDB存储引擎中，Index Dive算法会从索引的根节点开始，每层读取并计算满足条件的行数，直到叶子节点。函数 btr_estimate_n_rows_in_range_on_level() 用于估算某一层在条件区间内的记录数量，它会从左侧开始，最多向右读取10个页面（碰到右边界则停止），计算出每个页面的平均记录数后，则将平均记录数乘以该层的节点数，来估算出本层的记录总数。

五、基于条件估算行数

1. 条件处理

在 SELECT_LEX::estimate_rowcount() 函数中，优化器会根据 WHERE 条件估算行数。这个过程首先根据条件中的列选择索引，再通过条件中的具体范围（如 >, <, BETWEEN 等）应用估算公式。

2. 选择性估算

以 range_select() 函数为例，double selectivity = ...; // 选择性估算 双精度浮点数 selectivity 通过条件筛选比例来计算，进一步估算出符合条件的行数。double estimated_rows = selectivity * rows; 这里，selectivity 通过条件筛选比例来计算，进一步估算出符合条件的行数。

六、索引选择

1. 选择性判断

choose_index() 函数会检查各索引的选择性，根据列的基数、索引的类型来判断。基数较高的列通常是优先选择的，因为它们能更好地筛选数据，降低扫描的行数。当满足选择条件时，优化器就会选择该索引，这也是优化器选择访问路径的关键步骤之一。

七、实战案例：行数估算如何影响SQL性能

假设我们有一张 employees 表，包含以下数据：

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    department VARCHAR(20)
);

数据总量为10万条，且 age 列有较高的基数。我们执行以下查询语句：

SELECT * FROM employees WHERE age > 40;

优化器的行数估算流程将根据 age 列的基数和统计信息来估算符合条件的行数，进而影响查询性能。

八、总结

MySQL优化器通过解析、预处理、分析、优化等一系列复杂过程，结合统计信息和成本模型，为SQL查询生成最优执行计划。理解优化器的工作原理可以帮助DBA进行SQL优化，确保数据库系统的高效运行