【华为云MySQL技术专栏】MySQL优化器如何估算SQL语句的访问行数

举报
GaussDB 数据库 发表于 2024/11/04 17:32:33 2024/11/04
【摘要】 一、背景介绍在数据库运维工作中,慢SQL是一个常见问题。导致慢SQL问题的原因很多,常见的包括资源瓶颈(CPU、磁盘、网络等资源打满)、不合理的参数配置、SQL语句自身问题以及SQL代价估算不准确等。其中,SQL代价估算不准确是慢SQL的TOP根因之一。这类问题的复杂性通常与客户业务强相关,且往往需要详细查看执行计划才能确定错误原因。相比之下,其他慢SQL场景则通过资源监控、参数对比等手段,...

MySQL顶部banner.jpg

一、背景介绍

在数据库运维工作中,慢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_costmysql.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_statsmysql.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_leveln_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

【版权声明】本文为华为云社区用户原创内容,转载时必须标注文章的来源(华为云社区)、文章链接、文章作者等基本信息, 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱: cloudbbs@huaweicloud.com
  • 点赞
  • 收藏
  • 关注作者

评论(0

0/1000
抱歉,系统识别当前为高风险访问,暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称,即可参与社区互动!

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。