MySQL正常執行的SQL在TiDB中變慢了

語言: CN / TW / HK

作者:張政俊

原文來源:https://tidb.net/blog/23c9c2e2

前言

在測試過程中,發現有一部分在MySQL裡執行很流暢的SQL,放入TiDB中執行耗時明顯變長,有些甚至都跑不出結果。

這裡簡單總結下,上述情況產生的原因、優化辦法、以及遇到無法優化的,如何向社群提供背景資料。

情況與方案

表結構以及資料量MySQL和TiDB都是一致的,資料庫所在伺服器的硬體配置也差不多。

一. 統計資訊問題

在我們的測試場景中,這類情況很容易出現,因為我們每次跑測試任務前,都是通過br去恢復資料的。

如何判斷

這類問題也比較好判斷,首先就是看執行計劃:

explain analyze SQL;

1.png

可以看到operator info這列裡出現了stats:pseudo,這就代表 paycore_orderinfo 這張表需要重新收集下統計資訊。

優化方案

重新收集該表的統計資訊:

analyze table paycore_orderinfo;

收集完統計資訊後,我們再跑下sql:

1.png

可以看到原本是掃描了paycore_orderinfo全表,現在用到create_time索引了,執行時間從7秒減少到0.15秒。

如果想一次找出所有慢查詢裡計資訊為 pseudo的SQL,可以使用以下語句:

select query, query_time, stats from information_schema.slow_query where is_internal = false and stats like '%pseudo%';

二. 優化器問題一

統計資訊的問題比較常見也比較好解決,如果想要解決優化器導致執行計劃偏差的問題,就需要下一定功夫了。

如何判斷

因為整個SQL比較複雜,就擷取當中的一小段,先看下這條SQL在MySQL下的執行計劃:

1.png

可以看到整個SQL的執行計劃還是較好的,執行速度也很快。

相同的SQL放到TiDB中執行,執行計劃如下:

1.png

在MySQL裡t表用到了primary key,而在TiDB中,t表則使用了idx_ta_ack_2(ta_no)這個索引,導致實際影響的行數actRows達到了200多萬行,最終整個SQL執行失敗,報錯為:

[Err] 1105 - Out Of Memory Quota![conn_id=226083]

優化方案

現在單獨把這條SQL表關聯的地方拿出來:

FROM
t
LEFT JOIN d ON (t.app_no = d.ack_no),
e
WHERE

為了讓TiDB優化器更好地去判斷,把表關聯順序改為:

FROM
e
STRAIGHT_JOIN t
LEFT JOIN d ON (t.app_no = d.ack_no)
WHERE

執行計劃如下:

1.png

t表用回了primary key,跑起來的耗時也比MySQL快了不少。

三. 優化器問題二

還有種情況,在MySQL裡執行計劃正常,但是在TiDB中表關聯被轉為了全表的hashjoin。

如何判斷

先看下MySQL中的執行計劃:

1.png

TiDB中的執行計劃:

1.png

可以看到g表是TableFullScan,這樣整個SQL的執行時間就變得很長。

優化方案

為了讓g表能正常的走到索引關聯,這邊在SQL里加了hint,加完hint的執行計劃如下:

1.png

SQL執行時間也恢復了正常。

因為SQL實在是太長,不便於放在文章展示,所以只截取了一部分。

這裡想說的是,一般優化器的問題會出現很複雜、join關係很密集的SQL中,處理的方式大致有三種:

  1. SQL加hint;
  2. 通過binding繫結執行計劃;
  3. 更改表的的連線關係;

大家可以通過實際情況進行優化,如果還是解決不了,可以收集相關資訊在社群進行提問。

四. 向社群提問

如果想向社群求助,那需要那些東西呢?

1. 問題SQL

完整的SQL,如果有隱私資訊記得替換掉。

2. 表結構

SQL中所有表的建表語句,以及表中所包含的索引。

3. 執行計劃

通過 explain analyze 執行後輸出的執行計劃。

如果遇到SQL過大,被kill掉,無發跑出執行計劃的情況,那可以通過EXPLAIN FOR CONNECTION命令獲取動態的執行計劃,命令如下:

EXPLAIN FOR CONNECTION ID; #ID為正在執行的SQL ID

4. 表的統計資訊

收集方式:

curl http://172.16.XXX.XXX:10080/stats/dump/schema_name/table_name > dump.txt

收集完以上四樣東西,就可以去社群發帖啦。

總結

如果遇到MySQL裡執行的話,而在TiDB裡跑不動的SQL,可以按以下幾個步驟去做:

  1. 仔細分析執行計劃,執行計劃裡有足夠多的資訊。
  2. 遇到pseudo,擇時進行analyze table操作。
  3. 如果是優化器判斷的問題,根據統計資訊進行sql繫結或更改表連線方式(這個需要非常謹慎,不像加hint,更改連線方式需要動程式碼,關聯邏輯和結果必須得是正確的)。
  4. 收集所有相關的資訊向社群求助。

個人覺得此類問題大家可以大膽向社群尋求幫助,一來可以解決自己的問題,二來也可以給官方反饋更多的實際案例。

畢竟TiDB目前已相容了幾乎所有的MySQL語法,如果SQL執行也能保證一致或者更優,那整個從MySQL遷移至TiDB的過程將更加絲滑,所需要的測試和驗證的成本會更低。