TiDB SQL 调优案例之避免 TiFlash 帮倒忙

2023-03-17
北京
本文字数：3078 字
阅读完需：约 10 分钟

原文来源：https://tidb.net/blog/cf272d4c

背景

早上收到某系统的告警 tidb 节点挂掉无法访问，情况十万火急。登录中控机查了一下 display 信息，4 个 TiDB、Prometheus、Grafana 全挂了，某台机器 hang 死无法连接，经过快速重启后集群恢复，经排查后是昨天上线的某个 SQL 导致频繁 OOM。

于是开始亡羊补牢，来一波近期慢 SQL 巡检 # 手动狗头 #。。。

随便找了一个出现频率比较高的慢 SQL，经过优化后竟然性能提升了 1500 倍以上，感觉有点东西，分享给大家。

分析过程

该慢 SQL 逻辑非常简单，就是一个单表聚合查询，但是耗时达到 8s 以上，必有蹊跷。

脱敏后的 SQL 如下：

SELECT    cast( cast( CAST( SUM( num ) / COUNT( time ) AS CHAR ) AS DECIMAL ( 9, 2 )) AS signed ) speed,    ... -- 此处省略n个字段FROM    (    SELECT         DATE_FORMAT( receive_time, '%Y-%m-%d %H:%i:00' ) AS time,        COUNT(*) AS num     FROM        db1.table     WHERE        create_time > DATE_SUB( sysdate(), INTERVAL 20 MINUTE )     GROUP BY        time     ORDER BY    time     ) speed;

复制代码

碰到慢 SQL 不用多想，第一步先上执行计划：

很明显，这张 900 多万行的表因为创建了 TiFlash 副本，在碰到聚合运算的时候优化器选择了走列存查询，最终结果就是在 TiFlash 完成暴力全表扫描、排序、分组、计算等一系列操作，返回给 TiDB Server 时基本已经加工完成，总共耗时 8.02s。

咋一看好像没啥优化空间，但仔细观察会发现一个不合理的地方。执行计划倒数第二排的 Selection 算子，也就是 SQL 里面子查询的 where 过滤，实际有效数据 1855 行，却扫描了整个表接近 950W 行，这是一个典型的适合索引加速的场景。但遗憾的是，在 TiFlash 里面并没有索引的概念，所以只能默默地走全表扫描。

那么优化的第一步，先看过滤字段是否有索引，通常来说create_time这种十有八九都建过索引，检查后发现确实有。

第二步，尝试让优化器走 TiKV 查询，这里直接使用 hint 的方式：

SELECT /*+ READ_FROM_STORAGE(TIKV[db1.table]) */    cast( cast( CAST( SUM( num ) / COUNT( time ) AS CHAR ) AS DECIMAL ( 9, 2 )) AS signed ) speed,    ... -- 此处省略n个字段FROM    (    SELECT         DATE_FORMAT( receive_time, '%Y-%m-%d %H:%i:00' ) AS time,        COUNT(*) AS num     FROM        db1.table     WHERE        create_time > DATE_SUB( sysdate(), INTERVAL 20 MINUTE )     GROUP BY        time     ORDER BY    time     ) speed;

复制代码

再次生成执行计划，发现还是走了 TiFlash 查询。这里就引申出一个重要知识点，关于 hint 作用域的问题，也就是说 hint 只能在指定的查询范围内生效。具体到上面这个例子，虽然指定了db1.table走 TiKV 查询，但是对于它所在的查询块来说，压根不知道db1.table是谁直接就忽略掉了。所以正确的写法是把 hint 写到子查询中：

SELECT    cast( cast( CAST( SUM( num ) / COUNT( time ) AS CHAR ) AS DECIMAL ( 9, 2 )) AS signed ) speed,    ... -- 此处省略n个字段FROM    (    SELECT  /*+ READ_FROM_STORAGE(TIKV[db1.table]) */        DATE_FORMAT( receive_time, '%Y-%m-%d %H:%i:00' ) AS time,        COUNT(*) AS num     FROM        db1.table     WHERE        create_time > DATE_SUB( sysdate(), INTERVAL 20 MINUTE )     GROUP BY        time     ORDER BY    time     ) speed;

复制代码

对应的执行计划为：

小提示：
也可以通过set session tidb_isolation_read_engines = 'tidb,tikv';来让优化器走 tikv 查询。

发现这次虽然走了 TiKV 查询，但还是用的TableFullScan算子，整体时间不降反升，和我们预期的有差距。

没走索引那肯定是和查询字段有关系，分析上面 SQL 的逻辑，开发是想查询 table 表创建时间在最近 20 分钟的数据，用了一个sysdate()函数获取当前时间，问题就出在这。

获取当前时间常用的函数有now()和sysdate()，但这两者是有明显区别的。引用自官网的解释：

now()得到的是语句开始执行的时间，是一个固定值
sysdate()得到的是该函数实际执行的时间，是一个动态值

听起来比较饶，来个栗子一看便知：

mysql> select now(),sysdate(),sleep(3),now(),sysdate();+---------------------+---------------------+----------+---------------------+---------------------+| now()               | sysdate()           | sleep(3) | now()               | sysdate()           |+---------------------+---------------------+----------+---------------------+---------------------+| 2023-03-16 15:55:18 | 2023-03-16 15:55:18 |        0 | 2023-03-16 15:55:18 | 2023-03-16 15:55:21 |+---------------------+---------------------+----------+---------------------+---------------------+1 row in set (3.06 sec)

复制代码

这个动态时间就意味着 TiDB 优化器在估算的时候并不知道它是个什么值，走索引和不走索引哪个成本更高，最终导致索引失效。

从业务上来看，这个 SQL 用now()和sysdate()都可以，那么就尝试改成now()看看效果：

SELECT    cast( cast( CAST( SUM( num ) / COUNT( time ) AS CHAR ) AS DECIMAL ( 9, 2 )) AS signed ) speed,    ... -- 此处省略n个字段FROM    (    SELECT  /*+ READ_FROM_STORAGE(TIKV[db1.table]) */        DATE_FORMAT( receive_time, '%Y-%m-%d %H:%i:00' ) AS time,        COUNT(*) AS num     FROM        db1.table     WHERE        create_time > DATE_SUB( now(), INTERVAL 20 MINUTE )     GROUP BY        time     ORDER BY    time     ) speed;

复制代码

最终结果 4.43ms 搞定，从 8.02s 到 4.43ms，1800 倍的提升。

滥用函数，属于是开发给自己挖的坑了。

解决方案

经过以上分析，优化思路已经很清晰了，甚至都是常规优化不值得专门拿出来讲，但前后效果差异太大，很适合作为一个反面教材来提醒大家认真写 SQL。

其实就两点：

让优化器不要走 TiFlash 查询，改走 TiKV，可通过 hint 或 SQL binding 解决
非必须不要使用动态时间，避免带来索引失效的问题

深度思考

优化完成之后，我开始思考优化器走错执行计划的原因。

在最开始的执行计划当中，优化器对 Selection 算子的估算值 estRows 和实际值 actRows 相差非常大，再加上本身计算和聚合比较多，这可能是导致误走 TiFlash 的原因之一。不清楚 TiFlash 的 estRows 计算原理是什么，如果在估算准确的情况并且索引正常的情况下会不会走 TiKV 呢？

另外，我还怀疑过动态时间导致优化器判断失误（认为索引失效才选择走 TiFlash），但是在尝试只修改sysdate()为now()的情况下，发现依然走了 TiFlash，说明这个可能性不大。

在索引字段没问题的时候，按正常逻辑来说，我觉得一个成熟的优化器应该要能够判断出这种场景走 TiKV 更好。

总结

TiFlash 虽然是个好东西，但是优化器还在进化当中，难免有判断失误的时候，那么会导致适得其反的效果，我们要及时通过人工手段介入。再给 TiDB 优化器一些时间。

良好的 SQL 习惯至关重要，这也是老生常谈的问题了，再好的数据库也扛不住乱造的 SQL。

作者介绍：hey-hoho，来自神州数码钛合金战队，是一支致力于为企业提供分布式数据库 TiDB 整体解决方案的专业技术团队。团队成员拥有丰富的数据库从业背景，全部拥有 TiDB 高级资格证书，并活跃于 TiDB 开源社区，是官方认证合作伙伴。目前已为 10+ 客户提供了专业的 TiDB 交付服务，涵盖金融、证券、物流、电力、政府、零售等重点行业。

发布于: 刚刚阅读数: 5

原文链接:【http://xie.infoq.cn/article/3873df4f46355a3c8b3308012】。文章转载请联系作者。

TiDB 社区干货传送门

关注

TiDB 社区官网:https://tidb.net/ 2021-12-15 加入

TiDB 社区干货传送门是由 TiDB 社区中布道师组委会自发组织的 TiDB 社区优质内容对外宣布的栏目，旨在加深 TiDBer 之间的交流和学习。一起构建有爱、互助、共创共建的 TiDB 社区 https://tidb.net/

发布

暂无评论

创作场景

TiDB SQL 调优案例之避免 TiFlash 帮倒忙

背景

分析过程

解决方案

深度思考

总结

TiDB 社区干货传送门

评论