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

背景

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

分析过程

脱敏后的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不用多想，第一步先上执行计划：

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

create_time这种十有八九都建过索引，检查后发现确实有。

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查询。

TableFullScan算子，整体时间不降反升，和我们预期的有差距。

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优化器在估算的时候并不知道它是个什么值，走索引和不走索引哪个成本更高，最终导致索引失效。

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），但是在尝试只修改sysdate(为now(的情况下，发现依然走了TiFlash，说明这个可能性不大。

总结

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

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

编程笔记 » TiDB SQL调优案例之避免TiFlash帮倒忙

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

背景

分析过程

解决方案

深度思考

总结

相关文章

Hi，您需要填写昵称和邮箱！