“沒有任何一種資料庫是銀彈，業務場景的適配和降本增效永遠是最重要的。” 資料庫的效能優化能夠幫助企業最大限度地利用系統資源，提高業務支撐能力和使用者體驗。本文為 TiDB 效能調優專題的第一篇，在這個專題中，我們將邀請更多 TiDBer 從實際的業務場景出發，分享 TiDB 優化的最佳實踐。
作者介紹
鄭赫揚（藝名：潛龍），理想汽車 DBA。負責公司分散式資料庫的技術探索和業務場景落地，熱愛開源。就職於金融、網際網路教育、電商、新能源汽車等領域。
理想汽車作為豪華智慧電動車品牌，以 “創造移動的家，創造幸福的家” 為使命。隨著電動汽車業務的不斷髮展，公司業務既有 OLTP 也有 OLAP 的需求，因此需要一款 HTAP 資料庫幫助公司實現實時業務決策。在 TUG 企業行 —— 走進 58 同城活動中，來自理想汽車的鄭赫揚老師為大家介紹了理想汽車 HTAP 讀流量在物理環境、業務環境、SQL 優化、熱點問題、流量環境、版本及架構等方面的優化方案。
以下為演講實錄。
理想汽車選擇 TiDB 的理由
1）一棧式 HTAP：簡化企業技術棧
TiDB 可以在一份資料來源上同時支撐理想汽車 OLTP 和 OLAP 需求，不但能很好地支援實時資料落地儲存，也能提供一體化的分析能力。另外，TiDB 也可以整合常見的大資料計算技術框架，比如 Spark、Flink 等等，可以使用其構建離線或者實時的資料倉儲體系。
2）解決 MySQL 傳統拆庫拆表問題
隨著資料量的激增，單機資料庫存不下怎麼辦？傳統關係型資料庫再擴充套件性問題，如果業務上已經用了 MySQL，那隻能去做分庫分表，或者利用中介軟體去轉化，業務層要把程式碼改個遍。而 TiDB 與 MySQL 完全相容，MySQL 應用無需修改便可直接執行。支援包括傳統 RDBMS 和 NoSQL 的特性，可以隨著資料增長而無縫水平擴充套件，只需要通過增加更多的機器來滿足業務增長需求。
3）簡約但不簡單的擴縮容
在經歷產品更新的演進中，TiDB 4.0 和 5.0 版本基本上可以通過一些操作控制來靈活便捷地擴縮容，例如通過增加節點來線性擴充套件計算能力，儲存節點同樣可以根據儲存需求不斷進行擴容增加儲存。在進行節點縮容時，對線上服務的影響也幾乎無感知。
4）完善的生態工具
目前理想汽車正在使用 DM（TiDB Data Migration）、TiCDC、TiSpark 等工具，在實際操作中也很強烈的感受到 TiDB 生態工具非常全面，這些生態工具還在不停地演進發展中，我們也在和官方小夥伴一起探索。
5）豐富的場景支援
TiDB 在理想汽車的業務場景包括 OLAP 和 OLTP 兩個維度。OLAP 包括離線數倉，實時數倉、DMP 平臺（包括日常的決策排程、財務排程、報表等）；OLTP 類的商業前端、演算法訓練（包括線上派單、交付、資訊上報）等等。
6）良好的社群環境
社群一直是培養 TiDB 不斷髮展的優渥土壤，在日常維護中一旦出現故障和難點，官方的技術人員就會馬上處理，這也給理想汽車很大的信心把 TiDB 引入更多業務場景中使用。
接下來跟大家介紹理想汽車針對讀流量在以下 7 個方面的優化實踐。
HTAP 讀流量如何優化？
1）物理環境優化
理想汽車目前把 TiDB 和 PD 叢集的配置從原來的 16 核 32G 升級成了 32 核 128G。對於 TiDB 來說，支援 AP 類的大 SQL 可能要跑 7 - 8G 左右的記憶體，對記憶體的需求更高。為了解決 PD 的算力問題，我們會預估一下數倉的資料級，預估之後我們會再調大，例如一個單點的話，一個 PD 在 50 萬以上可能有排程問題。所以我們可以調大 Region，來橫向避免 Region 數量過多的情況。
TiKV 剛開始使用的是 32C32G 2T 的百度雲 SSD，現在和 TiFlash 一樣全部採用 32 核 64G 的 4T NVMe 物理盤，這兩個升級都是為了更好地統籌計算資源。
2）業務環境優化
目前在理想汽車主要的 TiDB 叢集都由 DM 同步上游的 MySQL，針對 DM 叢集管理做了大量優化。做完之後，就是現在所有的上游 MySQL 主要庫在 TiDB 裡面都會有副本，因此 TiDB 就具備了 MySQL 從庫 + 業務主庫 + DMP 平臺結果庫的功能。
關鍵業務庫表 DDL 變更和業務變更
- 上游 MySQL DDL 變更是否允許都會做一個規範，防止同步任務中斷。
- 新業務完整的測試環境支援。
上游 MySQL 刷業務資料會有大量寫流量，DM-sync 執行緒擴容。刷資料之前大家需要提前自動化，調整到高峰以應對流量衝擊，因為下游有很多重要的業務，資料延遲的話會很有影響。
分類開發規範:
- OLTP 開發規範：
目前理想汽車的單個事務 SQL 結果集大小不能超過 20MB 結果集 50W 以下，或者 TiDB 計算節點記憶體小於 120MB，因為 TiDB 要對結果集做一個處理，可能最大擴大至 6 倍。 - OLAP 開發規範：
- 複雜 SQL 大表走 TiFlash （一般 2KW），小表走 TiKV。
- 結果集最大值小於 7KW 或者 TiDB 計算結果記憶體小於 8G。
- 不斷探索 TiDB 的 OLAP 效能邊界。
DM 優化：
DDL 的問題是不支援變更，假如下游讀流量業務受到影響，例如公司上游掛了很多個 MySQL，你希望做 MySQL 同步關聯，你只要同步在一個 TiDB 叢集裡面，你也可以做一個小的數倉，調整方法，首先調整 TiDB 支援 DDL 變更。
解決方法:
(1)調整 TiDB 支援 DDL 變更
(2)上游新建表 --> 插入業務資料 --> rename 表名 --> 跳過報錯（針對上游流量和資料量小，要做資料修補）
(3)下游新建表 --> 插入業務資料 --> rename 表名 --> 跳過報錯（下游不會丟失資料）
業務環境優化中，典型的 TP 型別的 SQL 對結果集和運算元要求就是 20MB，比如需要考慮環境規劃，我們要求結果集在多少，總共不能少過多少萬行或者多少的記憶體，下圖所示最大記憶體是 25.3MB。
再來看下 AP 型別的 SQL 實時數倉，最大記憶體是 8G，總共少了 7700 萬的資料，執行時間是381 秒，是用 TiFlash 跑的。因為有的報表類或者是定時任務實時性不高，所以分類的話，還是 OK 的。我們現在好多類似的 SQL 在這麼跑，TiFlash 5.0.2 叢集版本，對於我們來說還可以，業務上比較穩定。
3）SQL優化
首先得定一下規則
SQL 索引：
OLTP 類：決定查詢速度，所有 SQL 必須走索引
a. 注意不走索引的情況（where 條件中左側等式函式，隱式轉化，不等式) b. 優化方式 MySQL 索引基本一致OLAP 類：根據表的數量級和 SQL 複雜度
a. 行存 where 條件查一條資料，行存 + 索引更快。 b. 列存沒有細粒度索引，掃描資料效能更好。
DDL 大表（數量過 10 億）索引上線可能會影響線上業務:
- 引數調整：業務高峰調小調低優先順序
tidb_ddl_reorg_batch_size = 128
tidb_ddl_reorg_worker_cnt = 2
tidb_ddl_reorg_priority = PRIORITY_LOW
- 業務低峰調大調高優先順序（看監控觀察業務）
tidb_ddl_reorg_batch_size = 1024
tidb_ddl_reorg_worker_cnt = 16
tidb_ddl_reorg_priority = PRIORITY_HIGH
SQL 執行計劃：
讀表運算元 TiDB 和 MySQL 不一樣。MySQL 的話是 Type、Reader 之類的，但是 TiDB 是有分成運算元再往下去讀像 TableReader，點查大於索引覆蓋，相當於 MySQL 的索引覆蓋，相當於 TiDB 普通索引。
讀表運算元優劣：PointGet/BatchPointGet>IndexReader（MySQL覆蓋索引）> IndexLookupReader（普通索引）> TableReader。對於 TP 型別 SQL 來說盡可能消除 TableReader 讀表運算元或者減少結果集。
SQL 錯誤索引與統計資訊：
TiDB 統計資訊和表格健康度會直接影響你的索引，通常就不走了，所以你的業務突然就變慢了，只能說越來越小了。對於理想汽車來說，看錶的健康度只要是大於 80% 的話，正確索引的概率基本上是可以保證的。
解決方法：
手動或者自動更新表和索引統計資訊
(1)自動更新條件
- 表中至少 1000 行資料
- 預設 1 分鐘無 DML
- modify_count > tidb_auto_analyze_ratio引數（預設 0.5，設定成 0.8）
- tidb_build_stats_concurrency（預設 4，資源充足可以調大）
- tidb_distsql_scan_concurrency（預設 15，AP 30，TP 10）
- tidb_index_serial_scan_concurrency（預設 1，TP 不調，AP 調成 4）
解決方法：
(1) 設定自動 analyze 時間 -tidb_auto_analyze_start_time（這裡是 UTC 時間，如果是預設值00:00 + 0000，則早上 8 點開始執行）。所以建議設定時間為業務低峰 -8 小時，比如凌晨執行（16:00 + 0000），結束 tidb_auto_analyze_end_time 設定（23:59 + 0000）。
(2) 如果仍然不準確,可以適當入侵繫結 SQL 計劃或者強制走索引。
CREATE [GLOBAL | SESSION] BINDING FOR BindableStmt USING BindableStmt;
(3) 如果避開了自動 analyze 時間，則應該手動重新統計表資訊。
show stats_meta where table_name='xxx'
show stats_healthy where table_name='xxx'
show STATS_HISTOGRAMS where table_name='xxx' analyze table xxx;
SQL 邏輯優化
TiFlash 函式下推，大家一定要看一下，因為 TiFlash 不是所有的函式都下推。假如你走 TiFlash 資料量很大，又沒有函式下推，代表你會在 TiDB 裡面計算，這個過程可能就會比較耗時。建議對準官方的支援列表，（下圖為部分擷取）按照下推列表去規範 SQL。
4）熱點問題優化
業務報慢 SQL，監控看單個節點 Coprocessor CPU（Coprocessor 是 TiKV 中讀取 資料並計算的模組）異常飆升。（v3.0.14 未開啟 Unified read pool，皮膚指標 TiKV - Details -> Thread CPU）
- 看讀熱點，可以基於命令列去做一個指令碼，比如 pt-ctl，看 hot read 和 hot write。
(1) 去看 region 對應的表是哪張，獲取熱點的單個 region。
(2) 4.0 以上 dashboard 熱力圖。
(3) TiDB_HOT_REGIONS 表中記載了是那張表，有多少個位元組。
然後手動 split Region，然後 hot read schedule 進行排程，CPU 使用資源下降，單個讀熱點 region 就消失了。
V4 版本有一個引數 Load base Split，預設 10 秒內 3000 次查詢，或者是流量超過了 30MB/秒自動分類。每一家的業務都不一樣，每一家的叢集也不一樣，預設只是說是一個挺好的配置，但是大多數的部署 TiDB 可能用的不是 NVMe，可能用的是雲盤的 SSD 或者是普通的 SSD，每一家的讀流量標準應該根據各自的硬碟配置標準，設定一個正常流量均衡的 Region。
這個可以通過熱力圖，也可以通過抓取的方式去看，可以做成自動化監控。所以 TiDB 還有個好處就是 Information Schema 裡面幾乎涵蓋了你所有的資訊，比 MySQL 更全。
像監控、命令可以看最高的讀取熱點，每天都可以看，用命令列做一個篩選，找到對應的庫表切割，切割完之後或者自動切割都可以，然後再觀察。但是這個 Load base split 有的時候也有可能，也可以每天跑一遍檢驗它的結果。
5）流量環境優化
第一，強制 SQL 裡面走一些執行計劃，執行內容可以限定一個引數，比如說 10 秒，或者記憶體 20M。我們針對線上 TP 型別是有的，比如下圖這個業務的 SQL，就是點選類的報表系統，因為點選類的報表系統由於業務人員選的維度不同，最後的資料量級也不同，你可以設定一個限制，否則可能他點很多次的話，一個大的流量就會把你的叢集衝擊掉。
Optimizer Hints 熔斷：
- 執行時間: MAX_EXECUTION_TIME(N)
- 執行記憶體: MEMORY_QUOTA(N)
- 線上 TP SQL
第二，Global 引數配置確保資源不溢位和連線質量。
- 長連線控制: pt-kill 控制殺掉 Sleep 執行緒或者重啟 TiDB-Server（慎用）
- 執行記憶體: tidb_mem_quota_query
第三，理想汽車 AP 和 TP 讀流量是分流的，TP 走的是 TiKV，AP 走的是 TiFlash，線上預設所有庫表都加了 TiFlash 副本，有 3000 多個同步任務，大概是 3000 多張表，做一個流量分流。有的時候可能走 TiKV 更快，但是有的時候是一個整體切割，如下圖所示，現在是兩個流量分流了。
- AP 和 TP 的流量均攤，可以更改執行計劃，但是我們用 Hint 更改執行計劃，發現對 AP 型別非常不友好，測試的時候可能還會走到 TiKV 上面。
SELECT + READ_FROM_STORAGE(TIFLASH[test1.t1,test2.t2]) / t1.a FROM test1.t t1, test2.t t2 WHERE t1.a = t2.a; - Session 會話控制，需要配合 rollback 回滾引數使用，強行走 TiFlash。
set @@session.tidb_isolation_read_engines = "tiflash"; TiFlash 下面有一個引數一定要設定，就是如果 TiFlash 發生查詢錯誤，業務語句返回 TiKV 查詢資料。確保萬一 TiFlash 副本不能用了，還有讀取資料的機會。
set global tidb_allow_fallback_to_tikv='tiflash';6）版本優化：
5.0 版本因為有 MPP 功能，我們去嘗試從輕 AP 型別轉到 AP 型別，看是否可以免除業務遷移的麻煩。我們的測試結果拿 2 臺 TiFlash 做 MPP 測試作為參考，結果如下：
- 2 臺 TiFlash MPP 業務整體提升 35%。
- 業務 SQL 比較複雜，5.0.2 之後官方做了更多的下推優化。
下圖可以看出優化前後的執行時間對比。這些都是線上業務，線上的數倉 SQL 特別複雜，都是一兩千行的大 SQL，開啟文字看大概有 1400 行，所以這個優化效果還是很棒的。
生產環境中，我們用 5 個 TiFlash node 跑，這是前後的流量對比圖，整體有 32 秒，後來基本都降到了 1 秒以下。
但是這個圖很騙人的，大家千萬不能相信這個 999，你真正需要優化的，可能就不在這個 999 裡面，全在那個 0.01 裡面。但是整體優化的效果還是很明顯的，包括事務延遲執行 lantency，都降低了很多。整體的響應更具說服力，以下是一天時間內的對比圖，大於 30 秒的 SQL 少了 87%，大於 60 秒的 SQL 也降低了 87%，大於 2 分鐘的 SQL 同比降低了 99%，同樣的流量，5.0.2 對我們整體的優化提升還是非常大的。
7）架構優化
架構優化的核心思想是用技術架構來轉化 SQL 壓力，下圖是實時數倉的技術架構。上面是 DM 同步 MySQL 的資料來源寫入到 TiDB，TiDB 做一個 ODS 層之後，再匯入到 TiCDC，之後通過分割槽打入到 Kafka，再分批消費進入 Flink，然後資料回寫回 TiDB，提供實時資料和物化檢視功能。因為 TiDB 本身不支援物化檢視，可以在 Flink 裡面解決這個技術難題，打造一個流批一體的實時數倉。
考慮到 OLAP 業務 SQL，我們選擇了 TiKV 儲存引擎，這個時候在 Flink 做完計算的表再寫回 TiDB 的話，有一些 AP 的 SQL 就可以變成 TP 了，像 Table Reader 的運算元有的時候可以變成 PointGet 或者是 BatchPointGet（點查或者表查），範圍查詢就會少很多，這樣就在側面緩解了壓力。選擇 TiFlash 就可以完成各種維度的聚合操作，實現離線報表和線上統計這些功能。這些都是我們已經實施上線的，但是還有幾個使用問題，例如：
- 在理想汽車的 DM 場景下，對錶做了 rename 後 TiCDC 就無法識別了。因為 TiCDC 預設是以 Table ID 算的，表名一樣，但是裡面的 Table ID 已經變了，TiCDC 識別不了，就只能重啟；
- 新增的 Kafka 分割槽，流量增大時增加 Kafka 分機，TiCDC 無法識別，也需要重啟；
V4.0.14 版本以前的 TiCDC 穩定性存在問題。
總結
業務發展推動技術革新，目前理想汽車的發展非常快。TiDB 的讀流量優化是個全域性視角，除了 SQL 本身外，官方提供了非常全面的優化手段，包括引擎、架構、執行計劃、引數控制等。大家可以去按照自己的業務發展去做各種不同的嘗試。
當然，優化都不能只看表面，TiDB Duration SQL 999 線是最常看的指標，但是也有欺騙性，但有時候最需要優化的是那 0.01%。
最後，沒有任何一種資料庫是銀彈，業務場景的適配和降本增效永遠是最重要的。TiDB 更像是集百家之長，而不是專精於一技，在解決分庫分表的基礎上，基本覆蓋所有場景和生態支援。理想汽車也希望能和 TiDB 一起走得更遠。