TiDB SQL調優案例之避免TiFlash幫倒忙-有解無憂

背景

早上收到某系統的告警tidb節點掛掉無法訪問，情況十萬火急，登錄中控機查了一下display資訊，4個TiDB、Prometheus、Grafana全掛了，某臺機器hang死無法連接，經過快速重啟后集群恢復，經排查后是昨天上線的某個SQL導致頻繁OOM，

企業微信截圖_20230316113735.png

于是開始亡羊補牢，來一波近期慢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不用多想，第一步先上執行計劃：

企業微信截圖_20230316150702.png

很明顯，這張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;

對應的執行計劃為：

企業微信截圖_20230316153949.png

小提示：

也可以通過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;

企業微信截圖_20230316160428.png

最終結果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交付服務，涵蓋金融、證券、物流、電力、政府、零售等重點行業，

文章作者：hoho 首發論壇：博客園文章出處：http://www.cnblogs.com/hohoa/ 歡迎大家一起討論分享，喜歡請點右下角的推薦鼓勵一下，我會有更多的動力來寫出好文章！歡迎持續關注我的博客！歡迎轉載，轉載的時候請注明作者和原文鏈接，

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/548502.html

標籤：其他

上一篇：超詳細【入門精講】資料倉庫原理&實戰一步一步搭建資料倉庫內附相應實驗代碼和鏡像資料和腳本

下一篇：讀SQL進階教程筆記01_CASE運算式