HTAP 還可以這麼玩？丨TiDB 在 IoT 智慧園區的應用

作者：某物聯網公司設施雲平臺負責人
使用者簡介：我們是一家提供全鏈智慧園區整體解決方案的物聯網公司，致力於打造可持續發展的智慧園區。

基礎設施平臺簡介

基礎設施平臺是集團一線作業人員日常工作中高度依賴的重要系統，涵蓋了各類基礎設施的管理、報修、保養等一系列數字化辦公功能，同時實現了對基礎設施、作業人員、專案成本等多種維度的精細化管理。其使命在於不斷提升一線作業效率，提高業主的服務滿意度。

以下為該平臺手機端應用截圖。 工作中心頁面提供了大量常用的業務功能，同時還包含了面對一線、專案、集團等多個維度的 報表功能，是一個典型的 HTAP 混合負載場景。

面臨的挑戰

隨著資料規模達到億級別、業務規模不斷擴大以及數字化需求的增加，該平臺面臨的挑戰變得愈發嚴峻。在日常的業務高峰期，特別是在一些關鍵業務期間，如每週初、月初和月末，一線作業人員和集團運營業務部門的工作都可能受到一些影響。這主要是由於底層資料庫在資源、架構和效能方面存在一些不足。

應用架構

原先採用 MySQL 資料庫的主從架構來為平臺提供服務(16VC/64GB)，其中包括：

• 主庫儲存業務資料（450GB），用於支撐應用中的絕大部分的業務功能，峰值 QPS 2500+；
• 從庫除了業務資料外，還有 ETL 轉化之後的彙總資料（200GB），峰值 QPS 1000+，支撐：
  
• 應用中的 BI 報表功能，集團的大屏實時展示；
• ETL 資料計算，在業務資料基礎上進行加工，並寫入到彙總庫中；
• 當主庫不可用時，支撐應用的高可用。

MySQL  不堪重負

1. 在業務高峰期，主庫的 CPU 使用率達到 90%，應用響應非常慢；
2. 在業務高峰期，從庫的 CPU 使用率也達到 90%，個別 BI 報表無法開啟；
3. 凌晨在從庫上執行的 ETL 作業耗時非常久，有時到需要執行到第二天工作時間，嚴重影響 BI 報表服務。

為何引入 TiDB

在引入 TiDB 前，我們研討了基於 MySQL 當前架構的擴容方案，將例項規格從 16VC/64G 擴充套件到 32VC/128G，甚至再加一個從節點，然後在應用層進行讀寫分離設計。

該擴容方案可以解決在併發時資源不足的問題，從而在一定程度上緩解高峰期服務響應慢的情況。然而，它並不能解決 SQL 本身執行速度較慢的問題。例如，在從庫執行凌晨的 ETL 作業期間，雖然負載並不高，但是 ETL 執行時間很長。方案上還是存在一定的不足：

1. 核心工單表 5 億多行，擴容後，該表的查詢和寫入 SQL 效能仍然沒有提升；
2. BI 報表查詢以及 ETL 轉化作業時，涉及大量複雜的 Join/聚合計算，涵蓋了多張大表，即使擴容了也還是慢；
3. 花費成本高，同時改善空間有限：
   
1. 3 倍的資源成本，從 32VC/128GB 變成 96VC/384GB；
2. 改造成本，需要對應用程式碼進行讀寫分離的改造，隨著未來該系統接入更多專案時，可能還需要投入更多精力進行分庫分表的改造工作。

最終我們計劃選擇原生分散式資料庫來解決該系統面臨的問題，其中 TiDB 分散式資料庫這些能力非常吸引我們：

1. 原生分散式架構，能夠徹底解決大資料量下讀寫效能問題；
2. HTAP 混合負載，BI 或者 ETL 中包含大量複雜”Join/聚合“的 SQL，效能可以大幅提升；
3. MySQL 相容，使得應用架構和程式碼幾乎不用調整便能完成應用的移植。

以下為  TiDB  的叢集配置（合計約 76VC）

OLTP 與 OLAP 負載徹底隔離的 HTAP 架構設計

應用架構無需改動

為了實現最小成本的系統移植，我們在一套 TiDB 中同時支撐 OLTP（應用基礎功能）、OLAP（BI 報表、ETL 作業）兩種負載，同時確保兩種負載之間互不影響，並且提供了最大的可用性保障。以下是我們針對 TiDB HTAP 架構進行的一些設計。

儲存節點規劃

我們將 TiKV1 和 TiKV2 規劃為 OLTP 區，將 TiKV3 和 TiFlash 規劃為 OLAP 區

資料 Leader 隔離

預設情況下 TiDB 中資料 Leader 的分佈是隨機的，而 SQL 請求預設都是發往 Leader 執行，所以預設機制下無法滿足我們的隔離要求。所以我們進行了如下設計：

1. 將 OLTP 所用到的業務資料的 leader 固定在 TiKV1/TiKV2 上
   

策略一： policy_eyas（業務庫策略）

預期效果：資料的 Leader 在 zone1 和 zone2 上，zone3 上不會產生 Leader（只有 Follower）

   -- 建立策略  create placement policy policy_eyas leader_constraints = "[-zone=zone3]"; 
  -- 為業務庫指定策略,讓業務讀寫都發生在 TiKV 節點 1,2  alter database eyas placement policy policy_eyas;

1. 將 OLAP 中 ETL 作業產生的彙總資料的 leader 固定在 TiKV3 上

策略二：policy_bi（彙總庫策略）

預期效果：資料的 Leader 只在 zone3 上，zone1 和 zone2 上只有 Follower （沒有 Leader）

 -- 建立策略create placement policy policy_bi leader_constraints="[+zone=zone3]" follower_constraints ='{"+zone=zone1": 1,"+zone=zone2": 1}';-- 為彙總庫指定策略,讓彙總庫上的讀寫都發生在 TiKV 節點 3 
alter database bi_eyas placement policy policy_bi;alter database da_ping placement policy policy_bi;

1. 為所有資料設定一份 TiFlash 副本
   

alter database eyas set tiflash replica 1;alter database bi_eyas set tiflash replica 1;alter database da_ping set tiflash replica 1;

目的：加速 BI 報表和 ETL 中複雜查詢 SQL 的效能

計算隔離

我們同樣也針對計算節點進行了隔離，TiDB1 和 TiDB2 兩個計算節點接入負載均衡供應用節點使用，並且限制其只能訪問 TiKV 上的資料（原因：應用基礎功能無須使用 TiFlash）。

  config:      isolation-read.engines:      - tikv      - tidb

對於 BI 應用和 ETL 平臺，我們則是讓其直連 TiDB3 計算節點。由於它們都存在大量複雜”Join/聚合“的SQL，我們希望這樣的 SQL 可以透過最佳化器自動決定去 TiKV 或 TiFlash 引擎執行，以獲得最大的效能，引數如下：

 config:      isolation-read.engines:      - tikv      - tiflash      - tidb      labels.zone: zone3

注意：在 TiDB 資料庫中，預設所有的讀請求都是發往資料的 Leader，而在 ETL 作業中的輸入基本都是查詢業務庫（非常複雜的 Join/聚合）、BI 報表中也有相當一部分會實時查詢業務庫（而業務庫的資料 Leader 在 TiKV1/TiKV2 上），這樣的 SQL 一旦出現將發往 TiKV1/TiKV2 上執行，此時會影響到應用核心功能。

為了避免這一問題，我們為 TiDB3 設定了一個 Label: zone3（與 TiKV3 的 Label 一致），此時便可以實現就近副本的讀取（TiDB3 上即使有非常複雜的業務庫的查詢，也只會在 TiKV3 的 Follower 副本上或 TiFlash 上執行）：

 set global tidb_replica_read = 'closest-replicas’;

使用效果：效能大幅提升

應用部分（頁面端到端耗時）

1. 應用核心功能基本最佳化到 1 秒內，大幅提升了一線的使用體驗和作業效率；
2. BI 報表效能大幅提升，之前打不開的報表均能快速展示，提升運營管理效率。

注：一張報表包含若干條 SQL，最大的報表包含 200 多條 SQL（序列執行、頁面逐一渲染展示）

ETL 作業

ETL 作業效能大幅提升，之前最久的作業執行超過 5 小時，現在的執行時間僅為 48 分鐘，效能提升了 80% 以上。

遇到的問題和解決辦法

雖然 TiDB 高度相容 MySQL，幾乎不需要改造，但在測試過程中發現了以下兩處問題。

SQL 不相容

5 條 SQL 報錯：ON condition doesn't support subqueries yet，原因為 TiDB 在 join 中不支援 on 判斷使用子查詢，如：

1. select t1.* from sbtest1 t1 join sbtest2 t2 on t1.id=t2.id and t2.id in (select id from sbtest3)2. select t1.* from sbtest1 t1 join sbtest2 t2 on t1.id=t2.id and exists (select 1 from sbtest3 t3 where t3.id=t1.id)

解決辦法：改寫成 join 表的形式

特定寫法下 SQL 效能退化

像查詢列中包含 case when in subquery 這樣的寫法時，執行時與這個 subquery join 的時候變成了笛卡爾積，效能退化十倍以上。

解決辦法：上線版本為 TiDB 6.5.0，當時透過將 case when in subquery 改寫成 exists 後解決，目前該問題已經在高版本中已經修復。

總結

TiDB 自今年 3 月上線以來，效能提升非常明顯。目前已經平穩執行了超過半年時間，這充分增強了我們深度使用 TiDB 的信心。未來，我們計劃利用 TiDB HTAP 架構來支撐更多業務場景的需求，持續創造價值。