介绍 某一天早上来到公司,接到业务同学反馈,线上某个SQL之前查询速度很快,从某个时间点开始查询速度突然变慢了,希望DBA帮忙查看下。业务同学反馈的原话如下: 看到这个问题,我第一时…
背景介绍
某一天早上来到公司,接到业务同学反馈,线上某个SQL之前查询速度很快,从某个时间点开始查询速度突然变慢了,希望DBA帮忙查看下。业务同学反馈的原话如下:
看到这个问题,我第一时间询问了业务对这个表的基本操作,得到的反馈如下:
这个表的SQL语法没有发生过变化
这个表的表结构近期未发生变更
这个表是个日志表,近期只有写入insert,没有大量delete、update操作
分析过程
1、SQL分析
首先,我们来看下这条SQL(脱敏之后):
SQL的语义本身比较简单,是一个单表查询,不涉及复杂查询:
从某一张表里面,利用l_mid和l_opertime这两个字段作为过滤条件,输出表里面的其他字段,并按照l_opertime排序。
2、表结构分析
这样一条简单的SQL,如果有索引的话,应该不会出现问题才对,我们看下表结构:
1 | show index from log_xxxx_2022_4; |
从上述索引结构,可以看出来,我们的l_mid字段和l_opertime字段,都有索引。
从索引原理上看,这个SQL的执行计划至少应该是一个IndexRangeScan(索引范围扫描)。
3、执行计划分析
传统的MySQL中,使用Explain语句来分析MySQL的执行计划。在TiDB中,我们可以使用2种方法查看TiDB的执行计划:
a、Explain + SQL :这种方法不会真正执行语句,会直接返回执行计划
b、Explain Analyze + SQL : 这种方法会执行SQL语句,并返回SQL的执行计划
我们使用上述方法b来查看执行计划(原因是这种方法可以看到SQL的执行时间),上述SQL的执行计划如下:
1 | +----------------------------------+----------+----------+-----------+-----------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------+----------+------+ |
上述SQL的执行时间是:26.15 sec
我们对TiDB的执行计划进行分析:
id 列:算子名称.
从图中可以看出,我们当前的SQL算子包含:
IndexLookUp:先汇总 Build 端 TiKV 扫描上来的 RowID,再去 Probe 端上根据这些
1 | RowID |
精确地读取 TiKV 上的数据。
IndexFullScan:另一种“全表扫描”,扫的是索引数据,不是表数据。
TableRowIDScan:根据上层传递下来的 RowID 扫描表数据。时常在索引读操作后检索符合条件的行。
estRows 列:显示TiDB预计会处理的行数
actRows 列:显示TiDB算子实际输出的数据条数
预估扫描行数最多是2w行,但是实际的输出条数是2000w行。
task 列:显示算子在执行语句时的所在位置,root代表tidb,cop代表tikv
access object 列:代表被访问的表对象和索引
execution info 列:算子的实际执行信息,包含执行时间等
这部分内容可以看到每个步骤的执行时间,但是不是特别直观,后面我们会通过Dashboard页面去分析执行时间。
operator info 列:显示访问表、分区、索引的其他信息
range: [2022-03-20 10:56:37,2022-04-20 10:56:37] 表示查询的 WHERE 字句 (l_opertime = 2022-04-20 10:56:37) 被下推到了 TiKV,对应的 task 为 cop[tikv]
keep order:true 表示这个查询需要TiKV按照顺序返回结果
stats:pseudo 它表示estRows显示的预估行数可能不准,TiDB定期在后台更新统计信息,也可以通过Analyze table 来手动更新信息。
memory 列:算子占用的内存空间大小
disk 列:算子占用磁盘空间的大小
4、TiDB DashBoard分析
上述Explain Analyze分析的执行计划内容,execution info列不够直观。我们看下TiDB 的Dashboard,其实也能发现一些端倪。
进入TiDB 的 Dashboard页面—>点击左侧的慢查询—>按照SQL语句(或者提炼的SQL指纹)进行搜索—>查看SQL执行耗时情况,看到类似的SQL执行耗时情况如下:
可以看到,大部分执行耗时都在Coprocessor执行耗时阶段,其他阶段占用的时间非常少。
值得注意的是,Coprocessor累计执行耗时看起来大于SQL执行时间,这个是因为TiKV 会并行处理任务,因此累计执行耗时不是自然流逝时间
我们再看看SQL的基本信息:
从SQL基本信息上,也可以看到,当前SQL使用的统计信息是pseudo,而pseudo代表统计信息不准确,就有可能导致TiDB基于成本的执行计划选择错误。
解决办法
有了上述的理论基础,问题的解决就变得简单了。
根据官方文档描述,我们使用Analyze table log_xxxx_2022_4 来重新收集下这个表的统计信息,然后重新执行查询:
1 | analyze table log_cmnt_2022_4; |
从最新的SQL执行计划中,我们不难发现:
1、执行计划中,预估的行数estRows,从一开始的2w行到现在的2.15行,实际执行行数actRows,从一开始的2000w行,到现在的0行,有了很大的一个改善。
2、SQL的执行时间变成了0.00s,意味着执行时间在10ms之内。
现在我们对比下执行时间:
统计信息收集之前:SQL执行26s
统计信息收集之后:SQL执行0.00s
一个Analyze操作,让整个SQL执行时间,足足翻了1000倍还多!!!
修改之后,业务同学反馈查询速度提升明显,监控肉眼可见:
Pseudo状态的SQL如何主动排查?如何解决?
从我们上述案例中可以发现,如果一个表的统计信息采用了pseudo,很可能造成查询慢的情况。因此,在实际应用中,我们需要对使用了pseudo统计信息的SQL进行摸排,可以使用下面的方法来进行摸排:
方案1、SQL排查并手动analyze
1 | select |
使用上述SQL查找到所有的使用了pseudo统计信息的SQL,并对它们访问的表,手动做一次analyze table操作。
上述SQL的输出样例如下:
1 | +-----------------------------+-------------+---------------------------------+ |
方案2、修改参数:
1 | pseudo-estimate-ratio |
这个参数代表修改的行数/表的总行数的比值,超过该值的时候,系统会认为统计信息已经过期,就会使用pseudo,这个值的默认值是0.8,最小值是0,最大值是1。它是统计信息是否失效的判断标准。
可以将这个参数调整成1,从而让TiKV执行SQL的时候不选择pseudo统计信息。
方案3、修改参数:
1 | tidb_enable_pseudo_for_outdated_stats |
这个变量用来控制TiDB优化器在某一张表上的统计信息过期之后的行为,默认值是On。
如果使用默认值On,在某张表的统计信息过期之后,代表优化器认为当前表除了总行数之外,其他的统计信息已经失效,所以会采用pseudo统计信息;
如果使用Off,即使一张表上的统计信息失效,也会使用当前表的统计信息,不会使用pseudo。如果你的表更新频繁,又没有即使对表进行analyze table,那么建议使用off选项。
方案4、TiDB Dashboard排查
登录TiDB的Dashboard,点击TiDB—>statistics—>pseudo estimation OPS面板即可。
如果监控中使用Pseudo统计信息的SQL过多,那么说明我们的统计信息存在大量失效的情况,需要对这类SQL访问的表重新进行信息统计。
总结
到这里,上面的问题算是解决了,我们也知道了如何对使用了Pseudo统计信息的SQL进行排查了。
我们先尝试写一些总结:
1、遇到慢查询,我们一般需要进行一系列分析,包括SQL历史运行状态了解、SQL语义分析、SQL访问的表对应的表结构分析、执行计划分析等等
2、TiDB的Dashboard中的慢日志模块已经帮用户整理了相关信息,要学会借助已有的功能去排查问题。
3、问题解决后,还应该想办法从源头上杜绝问题再次发生。
其实如果更近一步去思考,既然TiDB本身会进行统计信息收集,那么它的收集策略又是怎样的呢???为什么它有收集统计信息的功能,我们的表还会使用到pseudo统计信息呢???这些,其实都是值得思考的问题。这里我给出一点官方文档的提示:
关于统计信息的更多细节,等待大家在实践中去探索,去发现。:)
原作者:Asiaye 发表时间:2022/4/26 原文链接:https://tidb.net/blog/df697598
本文标题: 推荐系列-一个小操作,SQL查询速度翻了1000倍。
本文作者: OSChina
发布时间: 2022年05月11日 05:14
最后更新: 2023年06月29日 07:10
原始链接: https://haoxiang.eu.org/6c4fde0f/
版权声明: 本文著作权归作者所有,均采用CC BY-NC-SA 4.0许可协议,转载请注明出处!