摘要:本文将从源码角度分析SQL优化器代价估算的基础——行数估算,并总结MySQL当前设计存在的局限性。最后用一个现网问题的定位过程作为例子,给出该类问题的解决方案。
本文分享自华为云社区《【华为云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+树中,叶子节点记录的是索引键值,而非叶子节点(中间节点)记录的是索引键值和指向下一层节点的指针。
图1 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;
遇到下面特殊情况:
1)整个B+树只有1个根节点;
2)用户指定的采样页面数量很大(超过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,该特性的详细介绍见[2]。
五、总结
本文解析了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、欧拉等各项根技术的开发资源及工具,致力于为每位开发者提供一台云主机、一套开发工具及云上存储空间,让开发者基于华为根生态创新。点击链接,领取您的专属云主机!!