背景 早上收到某系統的告警tidb節點掛掉無法訪問,情況十萬火急。登錄中控機查了一下display信息,4個TiDB、Prometheus、Grafana全掛了,某台機器hang死無法連接,經過快速重啟後集群恢復,經排查後是昨天上線的某個SQL導致頻繁OOM。 於是開始亡羊補牢,來一波近期慢SQL巡 ...
背景
早上收到某系統的告警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交付服務,涵蓋金融、證券、物流、電力、政府、零售等重點行業。
文章作者:hoho 首發論壇:博客園 文章出處:http://www.cnblogs.com/hohoa/ 歡迎大家一起討論分享,喜歡請點右下角的推薦鼓勵一下,我會有更多的動力來寫出好文章!歡迎持續關註我的博客! 歡迎轉載,轉載的時候請註明作者和原文鏈接。