从源码分析，MySQL优化器如何估算SQL语句的访问行数
本文分享自华为云社区《【华为云MySQL技术专栏】MySQL优化器如何估算SQL语句的访问行数》，作者：GaussDB数据库。
一、背景介绍
在数据库运维工作中，慢SQL是一个常见问题。导致慢SQL问题的原因很多，常见的包括资源瓶颈（CPU、磁盘、网络等资源打满）、不合理的参数配置、SQL语句自身问题以及SQL代价估算不准确等。其中，SQL代价估算不准确是慢SQL的TOP根因之一。
这类问题的复杂性通常与客户业务强相关，且往往需要详细查看执行计划才能确定错误原因。相比之下，其他慢SQL场景则通过资源监控、参数对比等手段，就可以较快速地定位到原因。在分析慢SQL的过程中，DBA经常需要执行EXPLAIN命令来查看SQL访问每张表的路径和预估访问行数，来判断是否是最优的执行计划。
本文将从源码角度分析SQL优化器代价估算的基础——行数估算，并总结MySQL当前设计存在的局限性。最后用一个现网问题的定位过程作为例子，给出该类问题的解决方案。
二、原理介绍
MySQL优化器使用的是基于代价的CBO（Cost Based Optimizer）模型，为方便理解后续的原理，这里先介绍一些基础概念。
2.1 逻辑优化和物理优化
在MySQL中，一条SQL语句的执行要经过逻辑优化和物理优化两个步骤。逻辑优化的目的是优化语句结构，尽可能减少数据读取量，例如，常量折叠、谓词下推、JOIN顺序选择等技术。物理优化则基于逻辑优化的结果，进一步确定访问路径（Access Path），例如选择哪一个索引来读取数据。
举个例子，假设有一张名为employees的薪资表，有id（员工id）、salary（工资收入）、allowance（津贴收入），当我们要查询总收入（工资+津贴）不少于10000且津贴为1000的员工的SQL语句为：
select id from employees where allowance = 1000 and salary > 10000 - allowance;首先，逻辑优化过程会根据allowance = 1000，将其传播到表达式salary > 10000 – allowance中，得到salary > 10000 – 1000，并进一步计算出salary > 9000，以此简化查询条件。
逻辑优化完成后，物理优化阶段会根据统计信息以及SQL的查询条件来计算不同执行路径的代价，并据此确定具体的Access Path。例如，如果salary或者allowance字段上有索引，物理优化会分别计算访问索引的代价和全表扫描的代价，将两者相比较得出代价最小的Access Path。
2.2 代价计算模型
一条SQL语句执行的代价维度可以有很多，例如CPU、磁盘、内存、网络等资源。然而，MySQL代价模型只保留CPU和IO两个维度，即评估一条SQL语句的代价时，只考虑CPU代价和IO代价。
由于CPU和IO是不同维度的评估方式，所以为了使总的代价可以比较，MySQL使用加权的方法，将CPU代价和IO代价折算成一个总的数值。由于CPU和IO性能强依赖于具体的硬件情况，MySQL提供了两张系统表mysql.server_cost和mysql.engine_cost，允许用户查看并修改代价常数，表的具体字段含义客户可以查阅官方手册（参见引用[1]）。不过，为了保证存量业务SQL语句的稳定性，一般不会修改该表来解决慢SQL问题。
2.3 统计信息
代价计算的输入有两个，一是表上的过滤条件（SQL中的WHERE/JOIN条件），二是表的统计信息。优化器根据上述两个输入，来计算每个Access Path的代价。由于表上的过滤条件是确定的，所以统计信息的准确性直接影响代价的计算准确性，从而可能影响最终的执行计划选择。
三、源码阅读
下面将基于社区MySQL 8.0.32版本对统计信息采集的源码以及优化器实时下探（Index Dive）的源码进行解析。实时下探是指优化器不使用已有的统计信息，而是在优化阶段，实时访问索引B+树来统计数据的分布情况。
3.1 统计信息采集
MySQL中的统计信息涵盖表和索引两个维度，具体信息包括总行数、以数据页为单位的统计磁盘空间大小以及基数（Cardinality，每个索引/索引前缀组合不同值的个数）。为了保证执行计划的稳定性，MySQL使用两张系统表持久化了上述统计信息，分别是mysql.innodb_table_stats和mysql.innodb_index_stats。
通过参数innodb_stats_persistent可以控制是否启用持久化机制，其默认设置为启用持久化。当使用ANALYZE TABLE命令（手动方式）或者表中的数据修改行数超过10%时（自动触发），系统会重新计算统计信息并将其持久化存储。
3.1.1 统计算法简介
InnoDB通过对索引树的叶子节点进行采样的方式获取统计信息，innodb_stats_persistent_sample_pages参数控制了采样的叶子节点个数，显然，该参数值越大，采样精度越高，相应地采样耗时会增加。采样算法总体逻辑为：随机选取一些叶子节点，读取其记录信息，并基于这些信息推算整个索引的统计信息。
在介绍具体的采样算法之前，先熟悉下InnoDB的索引存储结构。
-
在InnoDB中，每张表的底层数据通过一个聚簇索引（索引的叶子节点记录存放的是用户记录，而不是指针）来存放。
-
此外，用户可以根据业务需要创建多个二级索引来加速查询，这些二级索引的叶子节点存储的是索引键值和主键值（用于回表访问聚簇索引记录）。
-
无论是聚簇索引还是二级索引，InnoDB都会使用B+树来存放整个索引结构，其结构如图1所示。在B+树中，叶子节点记录的是索引键值，而非叶子节点（中间节点）记录的是索引键值和指向下一层节点的指针
图一 InnoDB B+树结构示意图
了解了B+树结构之后，下面开始详细介绍具体的算法。
首先，算法定义了一个“n-prefix-boring记录”的概念。这个概念是说，n-prefix-boring记录是指一条中间节点的用户记录，它的n-prefix列的值等于其紧邻的下一个记录（可跨索引页，忽略infimum和supremum记录）。之所以称这样的记录为”boring记录“，是因为这些记录对应的子节点所有记录的n-prefix列的值都相等，没有采样意义。
例如，对于图1所示的记录，它的索引结构为Index(col1, col2)，那么，对于1-prefix，即col1列来说，level 1层的数据页[0,1|0,4|1,1]中的记录(0,1)以及记录(1,1)都是boring的，但是，记录(0,4)对col1列来说就不是boring的，因为它的下一个记录为(1,1)，col1列的值不同。
算法的流程如图2所示，针对索引的每个prefix组合都会使用该流程统计出数据分布信息：
这里解释下”每个prefix组合“是什么含义，由于InnoDB的联合索引使用最左匹配原则，例如对于一个联合索引Index(col1, col2, col3)，那么有效的匹配条件有(col1),(col1, col2),(col1, col2, col3)。所以，为了使所有的匹配条件都可以使用到统计信息，统计算法会计算每个“prefix”列组合的数据分布，来给优化器提供信息。
算法关键参数：
A = 采样页数（一般通过innodb_stats_persistent_sample_pages参数指定）；
LA = 第L层n-prefix列不同值的个数；
D = A * 10，算法认为只有找到包含A*10个不同值的层级才可以进行后续叶节点下探。
图 2 索引统计信息生成算法
3.1.2 统计算法源码分析
InnoDB进行索引信息统计的核心函数是dict_stats_analyze_index_low()，该函数简化版逻辑和源码如下所示：
首先，该函数会获取索引树的总大小和叶子节点大小（以数据页个数计算），下面的代码可以看到这两项也是统计信息的一部分。
/* 1. 获取索引B+树的总大小（以数据页为单位） */
size = btr_get_size(index, BTR_TOTAL_SIZE, &mtr);
if (size != ULINT_UNDEFINED) {
index->stat_index_size = size;
/* 2.获取索引B+树的叶子节点大小 */
size = btr_get_size(index, BTR_N_LEAF_PAGES, &mtr);
}
index->stat_n_leaf_pages = size;遇到下面特殊情况：
-
整个B+树只有1个根节点；
-
用户指定的采样页面数量很大（超过B+树叶子节点的总个数），那么采样过程将退化为全表扫描，这样一来，获得的统计信息结果会更准确。
if (root_level == 0 || n_sample_pages * n_uniq >
std::min<ulint>(index->stat_n_leaf_pages, 1e6)) {
/* 对叶子节点进行全表扫描，并将扫描结果直接放入索引的dict_index_t内存对象中。
dict_stats_analyze_index_level函数的实现后面会分析。 */
(void)dict_stats_analyze_index_level(
index, 0 /* leaf level */, index->stat_n_diff_key_vals, &total_recs,
&total_pages, nullptr /* boundaries not needed */, wait_start_time,
&mtr);
return true;
}通过定义了一些辅助变量，用来实现3.1.1节的算法：
/* 对于B+树的每一层，该数组存放了所有n-prefix列的不同值个数。 */
uint64_t *n_diff_on_level = ut::new_arr_withkey<uint64_t>(
ut::make_psi_memory_key(mem_key_dict_stats_n_diff_on_level),
ut::Count{n_uniq});
/* 对于B+树的每一层，该数组存放了每一组相同记录（用n-prefix列对比）最后一条记录的序号。
举个例子，假设扫描X层级时记录如下（某个n-prefix）：
record value: 0 0 0 1 1 2 3 3 3
no : 0 1 2 3 4 5 6 7 8
则数组里该n-prefix存放的记录则是{2,4,5,8}
*/
boundaries_t *n_diff_boundaries = ut::new_arr_withkey<boundaries_t>(
UT_NEW_THIS_FILE_PSI_KEY, ut::Count{n_uniq});
/* 该数组存放为了计算每个n-prefix列不同值所需要的输入。n_diff_data_t结构体包含如下成员：
level：对应的层级；n_recs_on_level：该层的记录总数；n_diff_on_level：该层不同值的个数；
n_leaf_pages_to_analyze：要分析的叶子节点个数；
n_diff_all_analyzed_pages：分析过的不同值的总数；n_external_pages_sum：溢出页总数。*/
n_diff_data_t *n_diff_data = ut::new_arr_withkey<n_diff_data_t>(
UT_NEW_THIS_FILE_PSI_KEY, ut::Count{n_uniq});下面是3.1.1节的算法总体实现流程。另外，实现上对算法做了简化，详见注释：
/* 针对3.1.1的算法，实现上做了一些优化：
如果对于第n_prefix列，L是第一个包含>=D个不同值的层级时，则：
对于第n_prefx -1列，它第一个包含>=D个不同值的层级必然<=L(即更低层)。
所以为了简化查找，函数先找到对于n_prefix列包含>=D个不同值的层级L，接着从L开始查找n_prefix-1列需要的层级。 */
level = root_level;
level_is_analyzed = false;
for (n_prefix = n_uniq; n_prefix >= 1; n_prefix--) {
/* 从根节点开始访问B+树，找到一个满足不同值个数>=D的一层。 */
for (;;) {
const uint64_t prev_total_recs = total_recs;
/* 针对1到n_uniq前缀列，找到本层的总记录数，和不同前缀列的不同值个数，填入n_diff_on_level中。 */
if (!dict_stats_analyze_index_level(
index, level, n_diff_on_level, &total_recs, &total_pages,
n_diff_boundaries, wait_start_time, &mtr)) {
n_sample_pages = prev_total_recs / 2;
/* 省略异常场景处理 */
}
level_is_analyzed = true;
/* 如果已经搜索到最后一层中间节点，或者已经找到了包含足够多不同值的level，则退出for循环。 */
if (level == 1 || n_diff_on_level[n_prefix - 1] >= n_diff_required) {
break;
}
level--;
level_is_analyzed = false;
}
found_level:
/* 找到合适的level后，从该层随机挑选一些记录，并下探到对应的叶子节点分析n-prefix的不同值个数。
后面有针对dict_stats_analyze_index_for_n_prefix()函数的详细分析。*/
if (!dict_stats_analyze_index_for_n_prefix(index, n_prefix,
&n_diff_boundaries[n_prefix - 1],
data, wait_start_time, &mtr)) {
/* 省略异常情况，比如B+树结构发生了变更。 */
}
}
}在上述过程中，函数dict_stats_analyze_index_level()被多次调用，用来获取指定层级的记录总数和不同值总数。该函数会填充n_diff_on_level和n_diff_boundaries两个数组。
而函数dict_stats_analyze_index_for_n_prefix()用来估算索引前n_prefix列不同值的个数。该函数会选择n_diff_data_t::n_leaf_pages_to_analyze个本层（中间节点）的记录，并下探到对应的叶子节点，扫描叶子节点上的记录后，将统计值存入n_diff_data_t::n_diff_all_analyzed_pages。
函数dict_stats_analyze_index_below_cur()负责根据当前的中间节点记录，下探到对应的叶子节点，并计算叶子节点上n-prefix列不同值的个数。至此，所有采样操作已经完成。
函数dict_stats_index_set_n_diff()会根据采样结果和3.1.1中的算法，根据每个页的统计结果，计算出整个索引n-prefix列的结果。另外，主键索引对应的统计信息会被作为表的统计信息。感兴趣的读者可以在源码中搜索函数名详细看一下实现逻辑。
3.2 行数估算方式
优化器对一张表的访问行数估算有以下2种方式：
-
使用统计信息进行估算。例如，对于非唯一索引的等值查询条件个数大于eq_range_index_dive_limit个时，优化器会使用统计信息中的 (记录总数/不同值个数)来估算平均访问行数；对于全表扫描，优化器会直接使用统计信息中的表总记录数进行估算。
-
优化器实时下探（Index Dive）。在SQL优化阶段，根据条件谓词进行B+树的下探。例如，对于索引列的范围查询（index_column > x），优化器会使用x下探采样，估算大于x的记录个数；对于非唯一索引的等值查询，如果等值条件数小于eq_range_index_dive_limit个数，也会进行下探以获取更精确的估算结果。
3.2.1 实时下探算法源码分析
针对SQL查询中，对非唯一索引的查找或者对索引前缀的查找（这种场景可能返回多行结果），优化器会在优化阶段，利用范围条件对索引B+树进行下探，来估算扫描行数。由于是实时下探，所以下探的代价不能太大。例如，对于一个查询条件(col1 >= A AND col1 <= B)，如果范围[A,B]之间的记录数很多，那么，下探只能通过估算的方式来确定记录总数，而无法读取所有的叶子节点来计算记录总数。
在InnoDB存储引擎中，Index Dive算法会从索引的根节点开始，每层读取并计算满足条件的行数，直到叶子节点。下面通过一个例子来理解下Index Dive的算法。
假设，表t1有一个联合索引Index(col1, col2)，这个索引的结构如图3所示。
图3 Index(col1, col2)索引树结构示意图
如果这个时候优化器要对条件(col1 = 2 AND col2 >=1 AND col2 <= 7)，即范围[(2,1),(2,7)]内的记录数进行估算，那么，算法将会按照下面流程进行：
-
使用值(2,1)和(2,7)定位到B+树的叶子节点，并记录路径上的节点，这样可以在每个B+树层级得到左右两个边界，如图4中的两条黄色边界。
-
从索引树的根节点开始，计算每层中在条件范围内的记录数（下文会解释为何需要这样做），记为n_rec(level)。这里的关键问题是，如果范围边界的两个记录都在一个或者几个数据页（如图4中的根节点和level1的第二个节点），那么直接读取所有记录计算出总数即可。但如果范围很大，包含多个节点（代码中这个值是10个），则无法把每个节点的实际记录数都算出来，只能采用估算的方式。
图4 B+树范围估算示意图
对于节点数量庞大的层级，算法会读取该层级在条件范围内的前10个页面，并计算出这10个页面的平均记录数avg_rec(level)。但要估算出本层的记录总数，还需要知道范围内的节点数，这里也不可能遍历所有的节点来计算，那么如何计算或者估算出本层的节点呢？
结合3.1.1节提到B+树的特性，每个中间节点记录都对应一个子节点页面。故本层的节点数，就是上一层的记录数。由于我们是从根节点开始遍历的（可以通过遍历整个根节点获取范围内的记录数），通过这种方式，依次类推，可以算出每一层的节点数。以图4为例，level 1中范围记录数为3，那么，对应的level 0中的节点个数也是3。
重复前面的流程，直到叶子节点，可以计算出叶子节点的记录总数n_rec=avg_rec(level 0) * n_rec(level 1)。
以上是Index Dive算法的大致思想。
下面看一下代码实现。算法的实现函数为btr_estimate_n_rows_in_range_low()，该函数的简化版代码如下所示：
-
定义一些辅助变量，用于记录路径信息：
/* path1和path2分别是定位到tuple1和tuple2的路径信息（从根节点到叶子节点的路径）。
btr_path_t结构体会记录每一层路径的页号，层高，记录个数，记录位置信息。*/
std::array<btr_path_t, BTR_PATH_ARRAY_N_SLOTS> path1;
std::array<btr_path_t, BTR_PATH_ARRAY_N_SLOTS> path2;-
使用边界值进行下探操作，注意有可能条件只有一个边界，例如，col > 100就只有左边界：
/* 如果范围包含起点，则使用起点的值进行下探。 */
if (dtuple_get_n_fields(tuple1) > 0) {
/* btr_cur_search_to_nth_level会将cursor定位到起点的位置，并将路径信息记录到cursor.path_arr，即path1中。 */
btr_cur_search_to_nth_level(index, 0, tuple1, mode1,
BTR_SEARCH_LEAF | BTR_ESTIMATE, &cursor, 0,
__FILE__, __LINE__, &mtr);
} else {
/* 如果范围不包含起点，则直接将游标定位到B+树最左侧。 */
btr_cur_open_at_index_side(true, index, BTR_SEARCH_LEAF | BTR_ESTIMATE,
&cursor, 0, UT_LOCATION_HERE, &mtr);
}
cursor.path_arr = path2.data();
/* 同样的，针对范围终点进行下探，并记录路径信息到path2. */
if (dtuple_get_n_fields(tuple2) > 0) {
btr_cur_search_to_nth_level(index, 0, tuple2, mode2,
BTR_SEARCH_LEAF | BTR_ESTIMATE, &cursor, 0,
__FILE__, __LINE__, &mtr);
} else {
/* 如果不存在范围终点，则将游标定位到B+树最右侧。 */
btr_cur_open_at_index_side(false, index, BTR_SEARCH_LEAF | BTR_ESTIMATE,
&cursor, 0, UT_LOCATION_HERE, &mtr);
}-
下探完成后，path1和path2内保存了左右边界信息，开始通过这个信息进行每层的记录数估算：
for (i = 0;; ++i) {
slot1 = &path1[i];
slot2 = &path2[i];
/* path数组已经遍历完 */
if (slot1->nth_rec == ULINT_UNDEFINED ||
slot2->nth_rec == ULINT_UNDEFINED) {
/* 下面的逻辑说明，如果范围包含的行数超过整张表的1/2则不再进行估算，直接取表的1/2行数作为估计值*/
if (n_rows > table_n_rows / 2 && !is_n_rows_exact) {
n_rows = table_n_rows / 2;
}
return (n_rows);
}
if (!diverged && slot1->nth_rec != slot2->nth_rec) {
/* 如果之前没有分叉，且slot1的记录位置和slot2不同，说明从下一层开始，两个path将会走在不同的数据页上。 */
diverged = true;
if (slot1->nth_rec < slot2->nth_rec) {
n_rows = slot2->nth_rec - slot1->nth_rec - 1;
if (n_rows > 0) {
/* n_rows > 0说明下一层中间至少隔1个节点，将diverged_lot设置为true。*/
diverged_lot = true;
divergence_level = i;
}
}
} else if (diverged && !diverged_lot) {
/* 同样，的如果从本层开始path已经开始分叉，则如果中间存在至少1个节点，则设置diverged_lot为true。 */
if (slot1->nth_rec < slot1->n_recs || slot2->nth_rec > 1) {
diverged_lot = true;
divergence_level = i;
n_rows = 0;
/* 计算本层起点和终点之间的记录数n_rows，这个n_rows将会当做下一层节点的页数。*/
if (slot1->nth_rec < slot1->n_recs) {
n_rows += slot1->n_recs - slot1->nth_rec;
}
if (slot2->nth_rec > 1) {
n_rows += slot2->nth_rec - 1;
}
}
} else if (diverged_lot) {
/* btr_estimate_n_rows_in_range_on_level函数会从slot1的位置向右探测10个页面（如果碰到slot2则停止），
计算出每个页面的平均记录数后，使用n_rows作为本层在条件区间内的页面数，则可以估算本层在条件区间内的记录数为：
AVG(已探测的页面记录数) * n_rows。注意这里的n_rows是上一层区间的记录数。该函数源码分析见下文。 */
n_rows = btr_estimate_n_rows_in_range_on_level(index, slot1, slot2,
n_rows, &is_n_rows_exact);
}
}函数btr_estimate_n_rows_in_range_on_level()用于估算某一层在条件区间内的记录数量，它会从左侧开始，最多向右读取10个页面（碰到右边界则停止），计算出每个页面的平均记录数后，则将平均记录数乘以该层的节点数，来估算出本层的记录总数。函数简化版的源码，如下所示：
-
指定读取的最大节点数为10：
/* 这个变量指定了最大读取的页面数，如果向后读取了10个页面还没有碰到slot2，则使用这10个页面的
记录数平均值来估算总行数。*/
constexpr uint32_t N_PAGES_READ_LIMIT = 10;-
开始从左向右进行节点读取，并计算记录数：
do {
/* 获取slot1(左边界)指向的数据页。 */
block =
buf_page_get_gen(page_id, page_size, RW_S_LATCH, nullptr,
Page_fetch::POSSIBLY_FREED, UT_LOCATION_HERE, &mtr);
page = buf_block_get_frame(block);
/* --省略一些和B+树结构调整相关异常情况处理-- */
n_pages_read++;
if (page_id.page_no() != slot1->page_no) {
/* 将本页的所有记录数累加到n_rows。 */
n_rows += page_get_n_recs(page);
}
/* 继续向右读取一个页面。 */
page_id.set_page_no(btr_page_get_next(page, &mtr));
if (n_pages_read == N_PAGES_READ_LIMIT || page_id.page_no() == FIL_NULL) {
/* 如果已经读取了足够多的页面（10个）或者树结构发生变更，则直接使用已经读取的数据来估算。 */
goto inexact;
}
} while (page_id.page_no() != slot2->page_no);-
根据已读取页面的平均记录数，来计算本层级条件范围内的记录总数：
if (n_pages_read > 0) {
/* 算出每个页面的平均记录数：n_rows/n_pages_read,之后乘以区间内的页面数，即上一层的记录数n_rows_on_prev_level。 */
n_rows = n_rows_on_prev_level * n_rows / n_pages_read;
} else {
/* 如果树结构发生变更，则直接赋值为常量10。 */
n_rows = 10;
}3.3 限制总结
从上面的分析中可以看到，在设计上，MySQL进行扫描行数估算的时候存在以下限制：
统计信息不准：当前InnoDB采样算法的假设是基于数据均匀分布。然而，InnoDB在采样时只会随机采样innodb_stats_persistent_sample_pages个页面，这对于大表或者存在数据倾斜的表，是很难精确估计其统计信息。
统计信息更新延迟：统计信息只有在手动执行ANALYZE TABLE命令或者表的更新行数超过10%时才会更新。例如，在瞬时大量更新的场景中，优化器可能使用过时的统计信息，从而导致选择非最优的执行计划。
四、现网案例
4.1 问题现象
客户某业务上的一条报表生成的SQL语句突然执行时间变长，期间并没有进行任何变更操作。另外，客户内部已经做了初步排查，发现该SQL语句在测试环境和生成环境上的执行计划不同，且测试环境中执行时间更短。
4.2 定位过程
由于已经有了执行计划，我们首先直接对比测试环境和生成环境的执行计划，两者如图5所示：
图5 执行计划详情，其中上边为生产环境，下边为测试环境
在排查过程中，由于其他表的Access Path没有变化，所以首先排查变化的两个表TDO和TFC，图中已用红线标出对应的执行计划。接着，对比下访问行数，由于其他表访问行数都小于或等于1，所以这里只对比TDO和TFC这两张表。
对比发现，生产实例的执行计划访问约为（23 * 100% * 1723 * 1.1% ≈ 438）行，而测试实例执行计划访问行数约为（71526 * 100% * 1 * 90.24% ≈ 64545）行。通过计算得出，生产实例的执行计划看起来确实更优，但是测试实例实际执行却更快。因此，首先怀疑是统计信息不准确，导致生产环境的执行计划行数估算出现偏差。
接下来确定TDO和TFC这两张表的实际数据情况。首先查看两张表的记录总数：
1、TFC表的记录总数：
2、TDO表的记录总数：
3、TDO表中Index_TT_DELIVERY_ORDER_01索引的基数，由于该索引只有F_COMPANY_ID字段，所以使用直接使用count(distinct(F_COMPANY_ID))计算：
可以看到，TFC表的记录总数为23行，和EXPLAIN命令的结果一致。但是，对TDO表的实际访问数据和EXPLAIN结果差距很大。根据上述手工执行SQL所得到的结果，对于ref类型的索引访问方式，平均扫描行数应该为（7236062 / 21 ≈ 344526）行，远大于EXPLAIN所显示的1733行。
与客户沟通后得知，该字段的数据倾斜较严重，通过第3节的源码分析可知，采样算法无法很好的估算出行数信息。由此可以判断，该问题是由于Index_TT_DELIVERY_ORDER_01索引统计信息不准导致的。
4.3 解决方案
由于是业务原因导致的F_COMPANY_ID字段存在数据倾斜，即便重新执行ANALYZE TABLE操作，该问题依然存在。考虑到该索引选择性较低，最后决定把该索引删除掉，之后MySQL优化器可以选择正确的执行计划。
针对此类统计信息不准的问题，以下方案也可能有效：
-
增大innodb_stats_persistent_sample_pages参数的值：通过第3章的源码分析可知，该参数控制采样的叶子节点个数，增大该参数可以使采样更准确。
-
使用直方图：从MySQL 8.0.3版本开始，系统支持直方图。通过建立针对某个列的直方图，可以更准确地获取数据分布信息，从而使行数估算更加准确。
-
使用Statement Outline特性：华为云自主创新特性，可以给指定的SQL添加索引提示（index hints），实现给语句人工指定正确的索引而无需修改业务SQL。
五、总结
本文解析了MySQL代价估算中行数估算的源码，并指出可能存在的问题，最后通过一个现网问题的定位过程，给出了该类问题的定位思路和解决方案，让大家再遇到此类问题时可以自己动手分析并解决问题。
六、引用
[1] MySQL帮助文档：https://dev.mysql.com/doc/refman/8.0/en/cost-model.html
[2] GaussDB for MySQL Statement Outline特性介绍：https://support.huaweicloud.com/kerneldesc-gaussdbformysql/gaussdbformysql_20_0018.html
华为开发者空间，汇聚鸿蒙、昇腾、鲲鹏、GaussDB、欧拉等各项根技术的开发资源及工具，致力于为每位开发者提供一台云主机、一套开发工具及云上存储空间，让开发者基于华为根生态创新。点击链接，免费领取您的专属云主机
