TiDB 查詢優化及調優系列（五）調優案例實踐

語言: CN / TW / HK
時間 2022-05-31 03:15:15
主題:

本篇文章為 TiDB 查詢優化及調優系列的最終篇，主要彙集了一些使用者常見的 SQL 優化案例，從背景、分析、影響、建議、實操幾個角度進行解析。關於 SQL 調優原理的介紹見前面章節。

相關閱讀：

TiDB 查詢優化及調優系列（一）TiDB 優化器簡介

TiDB 查詢優化及調優系列（二）TiDB 查詢計劃簡介

TiDB 查詢優化及調優系列（三）慢查詢診斷監控及排查

TiDB 查詢優化及調優系列（四）查詢執行計劃的調整及優化原理

注：以下語句及結果基本為當時實際環境所記錄的情況，因為版本更新原因，可能和現有格式略有差別，如 count 等價於現在的 estRows.

案例1: Delete 涉及資料量過大導致 OOM

MySQL [db_stat]> explain delete from t_stat where imp_date<='20200202'; +---------------------+--------------+------+------------------------------------------------------+ | id | count | task | operator info | +---------------------+--------------+------+------------------------------------------------------+ | TableReader_6 | 220895815.00 | root | data:Selection_5 | | └─Selection_5 | 220895815.00 | cop | le(db_stat.t_stat.imp_date, "20200202") | | └─TableScan_4 | 220895815.00 | cop | table:t_stat, range:[-inf,+inf], keep order:false | +---------------------+--------------+------+------------------------------------------------------+ 3 rows in set (0.00 sec) MySQL [db_stat]> select count(*) from t_stat where imp_date<='20200202'; +-----------+ | count(*) | +-----------+ | 184340473 | +-----------+ 1 row in set (17.88 sec)

背景

大批量清理資料時系統資源消耗高，在 TiDB 節點記憶體不足時可能導致 OOM

分析

imp_date欄位上雖然有索引，但是掃描的時間範圍過大，無論優化器選擇 IndexScan 還是 Table Scan，TiDB 都要向 TiKV Coprocessor 請求讀取大量的資料

影響

  • TiKV 節點 Coprocessor CPU 使用率快速上漲
  • 執行 Delete 操作的 TiDB 節點記憶體佔用快速上漲，因為要將大批量資料載入到 TiDB 記憶體

建議

  • 刪除資料時，縮小資料篩選範圍，或者加上 limit N 每次刪除一批資料
  • 建議使用 Range 分割槽表，按照分割槽快速刪除

案例2 執行計劃不穩定導致查詢延遲增加

MySQL [db_stat]> explain SELECT * FROM `tbl_article_check_result` `t` WHERE (articleid = '20190925A0PYT800') ORDER BY checkTime desc LIMIT 100 ; +--------------------------+----------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | id | count | task | operator info | +--------------------------+----------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Projection_7 | 100.00 | root | db_stat.t.type, db_stat.t.articleid, db_stat.t.docid, db_stat.t.version, db_stat.t.checkid, db_stat.t.checkstatus, db_stat.t.seclevel, db_stat.t.t1checkstatus, db_stat.t.t2checkstatus, db_stat.t.mdaichannel, db_stat.t.mdaisubchannel, db_stat.t.checkuser, db_stat.t.checktime, db_stat.t.addtime, db_stat.t.havegot, db_stat.t.checkcode | | └─Limit_12 | 100.00 | root | offset:0, count:100 | | └─IndexLookUp_34 | 100.00 | root | | | ├─IndexScan_31 | 30755.49 | cop | table:t, index:checkTime, range:[NULL,+inf], keep order:true, desc | | └─Selection_33 | 100.00 | cop | eq(db_dayu_1.t.articleid, "20190925A0PYT800") | | └─TableScan_32 | 30755.49 | cop | table:tbl_article_check_result, keep order:false | +--------------------------+----------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 6 rows in set (0.00 sec)

背景

articleid和 checkTime欄位上分別建有單列索引，正常情況下走 articleid上的索引比較快，偶爾執行計劃不穩定時走 checkTime上的索引，導致查詢延遲達到分鐘級別

分析

LIMIT 100限定了獲取 100 條記錄，如果 checkTime和 articleid列之間的相關度不高，在獨立性假設失效時，優化器估算走 checkTime上的索引並滿足 articleid條件時掃描的行數，可能比走 articleid上的索引掃描的行數更少

影響

業務響應延遲不穩定，監控 Duration 偶爾出現抖動

建議

  • 手動 analyze table，配合 crontab 定期 analyze，維持統計資訊準確度

  • 自動 auto analyze，調低 analyze ratio 閾值，提高收集頻次，並設定執行時間窗

    • set global tidb_auto_analyze_ratio=0.2;
    • set global tidb_auto_analyze_start_time='00:00 +0800';
    • set global tidb_auto_analyze_end_time='06:00 +0800';

  • 業務修改 SQL ，使用 force index 固定使用 articleid 列上的索引

  • 業務可以不用修改 SQL，使用 SPM （見上述章節）的 create binding 建立 force index 的繫結 SQL，可以避免執行計劃不穩定導致的效能下降

案例3 查詢欄位與值的資料型別不匹配

MySQL [db_stat]> explain select * from t_like_list where person_id=1535538061143263; +---------------------+------------+------+-----------------------------------------------------------------------------------+ | id | count | task | operator info | +---------------------+------------+------+-----------------------------------------------------------------------------------+ | Selection_5 | 1430690.40 | root | eq(cast(db_stat.t_like_list.person_id), 1.535538061143263e+15) | | └─TableReader_7 | 1788363.00 | root | data:TableScan_6 | | └─TableScan_6 | 1788363.00 | cop | table:t_like_list, range:[-inf,+inf], keep order:false | +---------------------+------------+------+-----------------------------------------------------------------------------------+ 3 rows in set (0.00 sec)

背景

person_id列上建有索引且選擇性較好，但執行計劃沒有按預期走 IndexScan

分析

person_id是字串型別，但是儲存的值都是數字，業務認為可以直接賦值；而優化器需要在欄位上做 cast型別轉換，導致無法使用索引

建議

where條件的值加上引號，之後執行計劃使用了索引：

MySQL [db_stat]> explain select * from table:t_like_list where person_id='1535538061143263'; +-------------------+-------+------+----------------------------------------------------------------------------------------------------------+ | id | count | task | operator info | +-------------------+-------+------+----------------------------------------------------------------------------------------------------------+ | IndexLookUp_10 | 0.00 | root | | | ├─IndexScan_8 | 0.00 | cop | table:t_like_list, index:person_id, range:["1535538061143263","1535538061143263"], keep order:false | | └─TableScan_9 | 0.00 | cop | table:t_like_list, keep order:false | +-------------------+-------+------+----------------------------------------------------------------------------------------------------------+ 3 rows in set (0.00 sec)

案例4 讀熱點導致 SQL 延遲增加

背景

某個資料量 600G 左右、讀多寫少的 TiDB 叢集，某段時間發現 TiDB 監控的 Query Summary - Duration 指標顯著增加，p99 如下圖。

1.jpg

檢視 TiDB 監控下的 KV Duration 明顯升高，其中 KV Request Duration 999 by store 監控看到多個 TiKV 節點 Duration 均有上漲。

2.jpg

檢視 TiKV 監控 Coprocessor Overview：

3.jpg

檢視監控 Coprocessor CPU：

4.jpg

發現 Coprocessor CPU 執行緒池幾乎打滿。下面開始分析日誌，調查 Duration 和 Coprocessor CPU 升高的原因。

慢查詢日誌分析

使用 pt-query-digest工具分析 TiDB 慢查詢日誌：

./pt-query-digest tidb_slow_query.log > result

分析慢日誌解析出來的 TopSQL 發現 Process keys和 Process time並不是線性相關，Process keys數量多的 SQL 的 Process time處理時間不一定更長，如下面 SQL 的 Process keys為 22.09M，Process time為 51s。

5.jpg

下面 SQL 的 Process keys為 12.68M，但是 Process time高達 142353s。

6.jpg

過濾 Process time較多的 SQL，發現 3 個典型的 slow query，分析具體的執行計劃。

SQL1

select a.a_id, a.b_id,uqm.p_id from a join hsq on a.b_id=hsq.id join uqm on a.a_id=uqm.id;

7.jpg

SQL2

select distinct g.abc, g.def, g.ghi, h.abcd, hi.jq from ggg g left join ggg_host gh on g.id = gh.ggg_id left join host h on gh.a_id = h.id left join a_jq hi on h.id = hi.hid where h.abcd is not null and h.abcd <> '' and hi.jq is not null and hi.jq <> '';

8.jpg

SQL3

select tb1.mt, tb2.name from tb2 left join tb1 on tb2.mtId=tb1.id where tb2.type=0 and (tb1.mt is not null and tb1.mt != '') and (tb2.name is not null or tb2.name != '');

9.jpg

分析執行計劃未發現異常，檢視相關表的統計資訊也都沒有過期，繼續分析 TiDB 和 TiKV 日誌。

常規日誌分析

檢視 TiKV 日誌中標記為 [slow-query] 的日誌行中的 region 分佈情況。

more tikv.log.2019-10-16-06:28:13 |grep slow-query |awk -F ']' '{print $1}' | awk '{print $6}' | sort | uniq -c | sort –n

找到訪問頻率最大的 3 個 region：

73 29452 140 33324 757 66625

這些 region 的訪問次數遠遠高於其它 region，之後定位這些 region 所屬的表名。首先檢視 [slow-query] 所在行記錄的 table_id 和 start_ts，然後查詢 TiDB 日誌獲取表名，比如 table_id 為 1318，start_ts 為 411837294180565013，使用如下命令過濾，發現是上述慢查詢 SQL 涉及的表。

more tidb-2019-10-14T16-40-51.728.log | grep '"/[1318/]"' |grep 411837294180565013

解決

對這些 region 做 split 操作，以 region 66625 為例，命令如下（需要將 x.x.x.x 替換為實際的 pd 地址）。

pd-ctl –u http://x.x.x.x:2379 operator add split-region 66625

操作後檢視 PD 日誌

[2019/10/16 18:22:56.223 +08:00] [INFO] [operator_controller.go:99] ["operator finish"] [region-id=30796] [operator=""admin-split-region (kind:admin, region:66625(1668,3), createAt:2019-10-16 18:22:55.888064898 +0800 CST m=+110918.823762963, startAt:2019-10-16 18:22:55.888223469 +0800 CST m=+110918.823921524, currentStep:1, steps:[split region with policy SCAN]) finished""]

日誌顯示 region 已經分裂完成，之後檢視該 region 相關的 slow-query：

more tikv.log.2019-10-16-06:28:13 |grep slow-query | grep 66625

觀察一段時間後確認 66625 不再是熱點 region，繼續處理其它熱點 region。所有熱點 region 處理完成後，監控 Query Summary - Duration 顯著降低。

10.jpg

Duration 穩定了保持一段時間，18:55 之後仍然有較高的 Duration 出現：

11.jpg

觀察壓力較重的 tikv，移走熱點 region 的 leader：

pd-ctl –u http://x.x.x.x:2379 operator add transfer-leader 1 2 //把 region1 的 leader 排程到 store2

leader 遷走之後，原 TiKV 節點的 Duration 立刻下降，但是遷移到新 TiKV 節點的 Duration 隨之上升。

12.jpg

之後多次對熱點 region 進行 split 操作，最終 Duration 明顯下降並恢復穩定。

13.jpg

案例總結

對於分散式資料庫的讀熱點問題，有時難以通過優化 SQL 的方式解決，需要分析整個 TiDB 叢集的監控和日誌來定位原因。嚴重的讀熱點可能導致部分 TiKV 達到資源瓶頸，這種短板效應限制了整個叢集效能的充分發揮，通過分裂 region 的方式可以將熱點 region 分散到更多的 TiKV 節點上，讓每個 TiKV 的負載儘可能達到均衡，緩解讀熱點對 SQL 查詢效能的影響。更多熱點問題的處理思路可以參考 TiDB 查詢優化及調優系列（四）查詢執行計劃的調整及優化原理 。

案例5 SQL 執行計劃不準

背景

SQL 執行時間突然變長

分析

  • SQL 語句

select count(*) from tods.bus_jijin_trade_record a, tods.bus_jijin_info b where a.fund_code=b.fund_code and a.type in ('PURCHASE','APPLY') and a.status='CANCEL_SUCCESS' and a.pay_confirm_status = 1 and a.cancel_app_no is not null and a.id >= 177045000 and a.updated_at > date_sub(now(), interval 48 hour) ;

執行結果，需要 1 分 3.7s：

mysql> select count(*) -> from tods.bus_jijin_trade_record a, tods.bus_jijin_info b -> where a.fund_code=b.fund_code and a.type in ('PURCHASE','APPLY') -> and a.status='CANCEL_SUCCESS' and a.pay_confirm_status = 1 -> and a.cancel_app_no is not null and a.id >= 177045000 -> and a.updated_at > date_sub(now(), interval 48 hour) ; +----------+ | count(*) | +----------+ | 708 | +----------+ 1 row in set (1 min 3.77 sec)

  • 索引資訊

14.jpg

  • 檢視執行計劃

``` mysql> explain -> select count(*) -> from tods.bus_jijin_trade_record a, tods.bus_jijin_info b -> where a.fund_code=b.fund_code and a.type in ('PURCHASE','APPLY')
-> and a.status='CANCEL_SUCCESS' and a.pay_confirm_status = 1 -> and a.cancel_app_no is not null and a.id >= 177045000
-> and a.updated_at > date_sub(now(), interval 48 hour) ; +----------------------------+--------------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | id | count | task | operator info | +----------------------------+--------------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | StreamAgg_13 | 1.00 | root | funcs:count(1) | | └─HashRightJoin_27 | 421.12 | root | inner join, inner:TableReader_18, equal:[eq(a.fund_code, b.fund_code)] | | ├─TableReader_18 | 421.12 | root | data:Selection_17 | | │ └─Selection_17 | 421.12 | cop | eq(a.pay_confirm_status, 1), eq(a.status, "CANCEL_SUCCESS"), gt(a.updated_at, 2020-03-03 22:31:08), in(a.type, "PURCHASE", "APPLY"), not(isnull(a.cancel_app_no)) | | │ └─TableScan_16 | 145920790.55 | cop | table:a, range:[177045000,+inf], keep order:false | | └─TableReader_37 | 6442.00 | root | data:TableScan_36 | | └─TableScan_36 | 6442.00 | cop | table:b, range:[-inf,+inf], keep order:false | +----------------------------+--------------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+

TableScan_16，TableScan_36：表示在 TiKV 端分別對錶 a 和 b 的資料進行掃描，其中 TableScan_16 掃描了 1.46 億的行數； Selection_17：表示滿足表 a 後面 where 條件的資料； TableReader_37： 由於表 b 沒有獨立的附加條件，所以直接將這部分資料返回給 TiDB； TableReader_18：將各個 coprocessor 滿足 a 表條件的結果返回給 TiDB； HashRightJoin_27：將 TableReader_37 和 TableReader_18 上的結果進行 hash join； StreamAgg_13：進一步統計所有行數，將資料返回給客戶端； ```

可以看到語句中 a 表(bus_jijin_trade_record)的條件 id >= 177045000，和 updated_at > date_sub(now(), interval 48 hour)上，這兩個列分別都有索引，但是 TiDB 還是選擇了全表掃描。

按照上面兩個條件分別查詢資料分割槽情況

``` mysql> SELECT COUNT() FROM tods.bus_jijin_trade_record WHERE id >= 177045000 ; +-----------+ | COUNT() | +-----------+ | 145917327 | +-----------+ 1 row in set (16.86 sec)

mysql> SELECT COUNT() FROM tods.bus_jijin_trade_record WHERE updated_at > date_sub(now(), interval 48 hour) ; +-----------+ | COUNT() | +-----------+ | 713682 | +-----------+ ```

可以看到，表 bus_jijin_trade_record 有 1.7 億的資料量，應該走 updated_at 欄位上的索引。 使用強制 hint 進行執行，6.27 秒就執行完成了，速度從之前 63s 到現在的 6.3s，提升了 10 倍。

mysql> select count(*) -> from tods.bus_jijin_trade_record a use index(idx_bus_jijin_trade_record_upt), tods.bus_jijin_info b -> where a.fund_code=b.fund_code and a.type in ('PURCHASE','APPLY') -> and a.status='CANCEL_SUCCESS' and a.pay_confirm_status = 1 -> and a.cancel_app_no is not null and a.id >= 177045000 -> and a.updated_at > date_sub(now(), interval 48 hour) ; +----------+ | count(*) | +----------+ | 709 | +----------+ 1 row in set (6.27 sec)

強制 hint 後的執行計劃：

mysql> explain -> select count(*) -> from tods.bus_jijin_trade_record a use index(idx_bus_jijin_trade_record_upt), tods.bus_jijin_info b -> where a.fund_code=b.fund_code and a.type in ('PURCHASE','APPLY') -> and a.status='CANCEL_SUCCESS' and a.pay_confirm_status = 1 -> and a.cancel_app_no is not null and a.id >= 177045000 -> and a.updated_at > date_sub(now(), interval 48 hour) ; +------------------------------+--------------+------+----------------------------------------------------------------------------------------------------------------------------+ | id | count | task | operator info | +------------------------------+--------------+------+----------------------------------------------------------------------------------------------------------------------------+ | StreamAgg_13 | 1.00 | root | funcs:count(1) | | └─HashRightJoin_24 | 421.12 | root | inner join, inner:IndexLookUp_20, equal:[eq(a.fund_code, b.fund_code)] | | ├─IndexLookUp_20 | 421.12 | root | | | │ ├─Selection_18 | 146027634.83 | cop | ge(a.id, 177045000) | | │ │ └─IndexScan_16 | 176388219.00 | cop | table:a, index:UPDATED_AT, range:(2020-03-03 23:05:30,+inf], keep order:false | | │ └─Selection_19 | 421.12 | cop | eq(a.pay_confirm_status, 1), eq(a.status, "CANCEL_SUCCESS"), in(a.type, "PURCHASE", "APPLY"), not(isnull(a.cancel_app_no)) | | │ └─TableScan_17 | 146027634.83 | cop | table:bus_jijin_trade_record, keep order:false | | └─TableReader_31 | 6442.00 | root | data:TableScan_30 | | └─TableScan_30 | 6442.00 | cop | table:b, range:[-inf,+inf], keep order:false | +------------------------------+--------------+------+----------------------------------------------------------------------------------------------------------------------------+

使用 hint 後的執行計劃，預估 updated_at 上的索引會掃描 176388219，沒有選擇索引而選擇了全表掃描，可以判定是由於錯誤的統計資訊導致執行計劃有問題。

查看錶 bus_jijin_trade_record上的統計資訊。

``` mysql> show stats_meta where table_name like 'bus_jijin_trade_record' and db_name like 'tods'; +---------+------------------------+---------------------+--------------+-----------+ | Db_name | Table_name | Update_time | Modify_count | Row_count | +---------+------------------------+---------------------+--------------+-----------+ | tods | bus_jijin_trade_record | 2020-03-05 22:04:21 | 10652939 | 176381997 | +---------+------------------------+---------------------+--------------+-----------+

mysql> show stats_healthy where table_name like 'bus_jijin_trade_record' and db_name like 'tods'; +---------+------------------------+---------+ | Db_name | Table_name | Healthy | +---------+------------------------+---------+ | tods | bus_jijin_trade_record | 93 | +---------+------------------------+---------+ ```

根據統計資訊，表 bus_jijin_trade_record有 176381997，修改的行數有 10652939，該表的健康度為：(176381997-10652939)/176381997 *100=93。

解決

重新收集統計資訊

``` mysql> set tidb_build_stats_concurrency=10; Query OK, 0 rows affected (0.00 sec)

調整收集統計資訊的併發度，以便快速對統計資訊進行收集

mysql> analyze table tods.bus_jijin_trade_record; Query OK, 0 rows affected (3 min 48.74 sec) ```

檢視沒有使用 hint 語句的執行計劃

mysql> explain select count(*) -> from tods.bus_jijin_trade_record a, tods.bus_jijin_info b -> where a.fund_code=b.fund_code and a.type in ('PURCHASE','APPLY') -> and a.status='CANCEL_SUCCESS' and a.pay_confirm_status = 1 -> and a.cancel_app_no is not null and a.id >= 177045000 -> and a.updated_at > date_sub(now(), interval 48 hour) ;; +------------------------------+-----------+------+----------------------------------------------------------------------------------------------------------------------------+ | id | count | task | operator info | +------------------------------+-----------+------+----------------------------------------------------------------------------------------------------------------------------+ | StreamAgg_13 | 1.00 | root | funcs:count(1) | | └─HashRightJoin_27 | 1.99 | root | inner join, inner:IndexLookUp_23, equal:[eq(a.fund_code, b.fund_code)] | | ├─IndexLookUp_23 | 1.99 | root | | | │ ├─Selection_21 | 626859.65 | cop | ge(a.id, 177045000) | | │ │ └─IndexScan_19 | 757743.08 | cop | table:a, index:UPDATED_AT, range:(2020-03-03 23:28:14,+inf], keep order:false | | │ └─Selection_22 | 1.99 | cop | eq(a.pay_confirm_status, 1), eq(a.status, "CANCEL_SUCCESS"), in(a.type, "PURCHASE", "APPLY"), not(isnull(a.cancel_app_no)) | | │ └─TableScan_20 | 626859.65 | cop | table:bus_jijin_trade_record, keep order:false | | └─TableReader_37 | 6442.00 | root | data:TableScan_36 | | └─TableScan_36 | 6442.00 | cop | table:b, range:[-inf,+inf], keep order:false | +------------------------------+-----------+------+----------------------------------------------------------------------------------------------------------------------------+ 9 rows in set (0.00 sec)

可以看到，收集完統計資訊後，現在的執行計劃走了索引掃描，與手動新增 hint 的行為一致，且掃描的行數 757743 符合預期。

此時執行時間變為 1.69s ，在執行計劃沒變的情況下，應該是由於快取命中率上升帶來的提升。

mysql> select count(*) -> from tods.bus_jijin_trade_record a, tods.bus_jijin_info b -> where a.fund_code=b.fund_code and a.type in ('PURCHASE','APPLY') -> and a.status='CANCEL_SUCCESS' and a.pay_confirm_status = 1 -> and a.cancel_app_no is not null and a.id >= 177045000 -> and a.updated_at > date_sub(now(), interval 48 hour) ; +----------+ | count(*) | +----------+ | 712 | +----------+ 1 row in set (1.69 sec)

案例總結

可以看出該 SQL 執行效率變差是由於統計資訊不準確造成的，在通過收集統計資訊之後得到了正確的執行計劃。

從最終結果 712 行記錄來看，建立聯合索引可以更大的降低掃描資料的量，更進一步提升效能。在效能已經滿足業務要求情況下，聯合索引會有額外的成本，留待以後嘗試。

本文為「TiDB 查詢優化及調優」系列文章的第五篇，也是最終篇。通過這個系列文章，我們詳細介紹了 TiDB 優化器、查詢計劃、慢查詢以及調優的理論知識，並在本章節中進行了實戰的分享。希望通過這個系列文章，大家能夠更加深入地理解 TiDB 優化器，並通過這些調優技巧更好地提升系統性能。

如果您對 TiDB 的產品有任何建議，歡迎來到 internals.tidb.io 與我們交流。

「」