背景

早上收到某系統(tǒng)的告警tidb節(jié)點(diǎn)掛掉無(wú)法訪問(wèn)，情況十萬(wàn)火急。登錄中控機(jī)查了一下display信息，4個(gè)TiDB、Prometheus、Grafana全掛了，某臺(tái)機(jī)器hang死無(wú)法連接，經(jīng)過(guò)快速重啟后集群恢復(fù)，經(jīng)排查后是昨天上線的某個(gè)SQL導(dǎo)致頻繁O(jiān)OM。

于是開(kāi)始亡羊補(bǔ)牢，來(lái)一波近期慢SQL巡檢 #手動(dòng)狗頭#。。。

隨便找了一個(gè)出現(xiàn)頻率比較高的慢SQL，經(jīng)過(guò)優(yōu)化后竟然性能提升了1500倍以上，感覺(jué)有點(diǎn)東西，分享給大家。

分析過(guò)程

該慢SQL邏輯非常簡(jiǎn)單，就是一個(gè)單表聚合查詢，但是耗時(shí)達(dá)到8s以上，必有蹊蹺。

脫敏后的SQL如下：

SELECT
    cast( cast( CAST( SUM( num ) / COUNT( time ) AS CHAR ) AS DECIMAL ( 9, 2 )) AS signed ) speed,
    ... -- 此處省略n個(gè)字段
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不用多想，第一步先上執(zhí)行計(jì)劃：

很明顯，這張900多萬(wàn)行的表因?yàn)閯?chuàng)建了TiFlash副本，在碰到聚合運(yùn)算的時(shí)候優(yōu)化器選擇了走列存查詢，最終結(jié)果就是在TiFlash完成暴力全表掃描、排序、分組、計(jì)算等一系列操作，返回給TiDB Server時(shí)基本已經(jīng)加工完成，總共耗時(shí)8.02s。

咋一看好像沒(méi)啥優(yōu)化空間，但仔細(xì)觀察會(huì)發(fā)現(xiàn)一個(gè)不合理的地方。執(zhí)行計(jì)劃倒數(shù)第二排的Selection算子，也就是SQL里面子查詢的where過(guò)濾，實(shí)際有效數(shù)據(jù)1855行，卻掃描了整個(gè)表接近950W行，這是一個(gè)典型的適合索引加速的場(chǎng)景。但遺憾的是，在TiFlash里面并沒(méi)有索引的概念，所以只能默默地走全表掃描。

那么優(yōu)化的第一步，先看過(guò)濾字段是否有索引，通常來(lái)說(shuō)create_time這種十有八九都建過(guò)索引，檢查后發(fā)現(xiàn)確實(shí)有。

第二步，嘗試讓優(yōu)化器走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個(gè)字段
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;

再次生成執(zhí)行計(jì)劃，發(fā)現(xiàn)還是走了TiFlash查詢。這里就引申出一個(gè)重要知識(shí)點(diǎn)，關(guān)于hint作用域的問(wèn)題，也就是說(shuō)hint只能在指定的查詢范圍內(nèi)生效。具體到上面這個(gè)例子，雖然指定了db1.table走TiKV查詢，但是對(duì)于它所在的查詢塊來(lái)說(shuō)，壓根不知道db1.table是誰(shuí)直接就忽略掉了。所以正確的寫法是把hint寫到子查詢中：

SELECT
    cast( cast( CAST( SUM( num ) / COUNT( time ) AS CHAR ) AS DECIMAL ( 9, 2 )) AS signed ) speed,
    ... -- 此處省略n個(gè)字段
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;

對(duì)應(yīng)的執(zhí)行計(jì)劃為：

小提示：也可以通過(guò)set session tidb_isolation_read_engines = 'tidb,tikv';來(lái)讓優(yōu)化器走tikv查詢。

發(fā)現(xiàn)這次雖然走了TiKV查詢，但還是用的TableFullScan算子，整體時(shí)間不降反升，和我們預(yù)期的有差距。

沒(méi)走索引那肯定是和查詢字段有關(guān)系，分析上面SQL的邏輯，開(kāi)發(fā)是想查詢table表創(chuàng)建時(shí)間在最近20分鐘的數(shù)據(jù)，用了一個(gè)sysdate()函數(shù)獲取當(dāng)前時(shí)間，問(wèn)題就出在這。

獲取當(dāng)前時(shí)間常用的函數(shù)有now()和sysdate()，但這兩者是有明顯區(qū)別的。引用自官網(wǎng)的解釋：

• now()得到的是語(yǔ)句開(kāi)始執(zhí)行的時(shí)間，是一個(gè)固定值
• sysdate()得到的是該函數(shù)實(shí)際執(zhí)行的時(shí)間，是一個(gè)動(dòng)態(tài)值

聽(tīng)起來(lái)比較饒，來(lái)個(gè)栗子一看便知：

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)

這個(gè)動(dòng)態(tài)時(shí)間就意味著TiDB優(yōu)化器在估算的時(shí)候并不知道它是個(gè)什么值，走索引和不走索引哪個(gè)成本更高，最終導(dǎo)致索引失效。

從業(yè)務(wù)上來(lái)看，這個(gè)SQL用now()和sysdate()都可以，那么就嘗試改成now()看看效果：

SELECT
    cast( cast( CAST( SUM( num ) / COUNT( time ) AS CHAR ) AS DECIMAL ( 9, 2 )) AS signed ) speed,
    ... -- 此處省略n個(gè)字段
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;

最終結(jié)果4.43ms搞定，從8.02s到4.43ms，1800倍的提升。

濫用函數(shù)，屬于是開(kāi)發(fā)給自己挖的坑了。

解決方案

經(jīng)過(guò)以上分析，優(yōu)化思路已經(jīng)很清晰了，甚至都是常規(guī)優(yōu)化不值得專門拿出來(lái)講，但前后效果差異太大，很適合作為一個(gè)反面教材來(lái)提醒大家認(rèn)真寫SQL。

其實(shí)就兩點(diǎn)：

• 讓優(yōu)化器不要走TiFlash查詢，改走TiKV，可通過(guò)hint或SQL binding解決
• 非必須不要使用動(dòng)態(tài)時(shí)間，避免帶來(lái)索引失效的問(wèn)題

深度思考

優(yōu)化完成之后，我開(kāi)始思考優(yōu)化器走錯(cuò)執(zhí)行計(jì)劃的原因。

在最開(kāi)始的執(zhí)行計(jì)劃當(dāng)中，優(yōu)化器對(duì)Selection算子的估算值estRows和實(shí)際值actRows相差非常大，再加上本身計(jì)算和聚合比較多，這可能是導(dǎo)致誤走TiFlash的原因之一。不清楚TiFlash的estRows計(jì)算原理是什么，如果在估算準(zhǔn)確的情況并且索引正常的情況下會(huì)不會(huì)走TiKV呢？

另外，我還懷疑過(guò)動(dòng)態(tài)時(shí)間導(dǎo)致優(yōu)化器判斷失誤（認(rèn)為索引失效才選擇走TiFlash），但是在嘗試只修改sysdate()為now()的情況下，發(fā)現(xiàn)依然走了TiFlash，說(shuō)明這個(gè)可能性不大。

在索引字段沒(méi)問(wèn)題的時(shí)候，按正常邏輯來(lái)說(shuō)，我覺(jué)得一個(gè)成熟的優(yōu)化器應(yīng)該要能夠判斷出這種場(chǎng)景走TiKV更好。

總結(jié)

TiFlash雖然是個(gè)好東西，但是優(yōu)化器還在進(jìn)化當(dāng)中，難免有判斷失誤的時(shí)候，那么會(huì)導(dǎo)致適得其反的效果，我們要及時(shí)通過(guò)人工手段介入。再給TiDB優(yōu)化器一些時(shí)間。

良好的SQL習(xí)慣至關(guān)重要，這也是老生常談的問(wèn)題了，再好的數(shù)據(jù)庫(kù)也扛不住亂造的SQL。?