DWS環境下,客戶要求將40億行大表的點查SQL從秒級優化至毫秒級。雖然是聽起來有些離譜的需求,但筆者經多次嘗試,最終仍實現了秒級到毫秒級的性能飛躍,完成挑戰。下面分享該案例的操作過程。
一、問題與背景
-
環境配置:DWS3節點8.2.1版本。
-
表信息:查詢僅涉及一個單表,以下簡稱target_table_name,該表數據量40億行,大小約3.5TB,132列,行存表,無分區,分布鍵為?id?,在?batch_no?和?date_col?上有組合索引,統計信息最新。
-
業務查詢:固定點查SQL,使用?batch_no?和?date_col?(范圍2-3個月)過濾,帶?ORDER BY?和?LIMIT 20?。
-
性能痛點:首次執行耗時3~5秒,緩存后雖可降至毫秒級,但要求首次查詢即穩定達到毫秒級。
-
脫敏后SQL內容如下:
--原始SQL
select
column_name1, column_name2, ... column_name73 --省略71個字段
from target_table_name
where date_col between 'date_start' and 'date_end' --時間范圍
and batch_no = 'filter_val1'
and filter_col2 in ('filter_val2')
and filter_col3 = 'filter_val3'
and filter_col4 in ('filter_val4')
and filter_col5 in ('filter_val5')
order by order_col1, order_col2, order_col3, order_col4, order_col5, order_col6 desc
limit 20
;
- 原SQL執行計劃(這里A-time毫秒級源于客戶提供的是多次查詢之后的執行計劃,實際首次查詢為秒級)
| id | operation | A-rows | E-rows | E-cost | A-time |
|-----|------------------------------------------------------------------------------------------|--------|--------|--------------|--------------------|
| 1 | Limit | 20 | 20 | 20933.08 | 289.211 |
| 2 | -> Streaming (type: GATHER) | 20 | 20 | 20933.08 | 289.204 |
| 3 | -> Limit | 20 | 20 | 20922.90 | [279.336, 279.336] |
| 4 | -> Sort | 20 | 3354 | 20932.52 | [279.331, 279.331] |
| 5 | -> Index Scan using index_name on target_table_name | 54096 | 20124 | 20819.87 | [164.052, 164.052] |
二、優化歷程
1. 第一次嘗試:日分區 + 日期前導索引(失敗)
- 操作:按?date_col?創建日分區,并建立?(date_col, batch_no)?的本地索引(中間寫入原表數據的操作省略)。
-- 創建日分區表
CREATE TABLE test_day_part (...)
DISTRIBUTE BY HASH(id)
PARTITION BY RANGE(date_col)
(
PARTITION p20250101 VALUES LESS THAN ('2025-01-02'::date),
PARTITION p20250102 VALUES LESS THAN ('2025-01-03'::date),
PARTITION p20250103 VALUES LESS THAN ('2025-01-04'::date),
... -- 省略其他日分區
PARTITION pmax VALUES LESS THAN (maxvalue)
);
--寫入測試數據
--在業務空閑期間,抽取原表數據寫入,耗時1小時,分批Insert,具體步驟在此不計。
-- 寫入數據后再創建本地分區索引,以date_col為前導列 耗時1小時
CREATE INDEX test_day_part_idx ON test_day_part(date_col, batch_no) LOCAL;
-- 收集統計信息
ANALYZE test_day_part;
-
結果:查詢劣化至62秒。
-
EXPLAIN ANALYZE/PERFORMANCE? 執行計劃展示:
| id | operation | A-rows | E-rows | E-cost | A-time |
|-----|--------------------------------------------------------------------------- ------|--------|--------|--------------|------------------------|
| 1 | Limit | 20 | 20 | 6000009.41 | 62567.305 |
| 2 | -> Streaming (type: GATHER) | 20 | 20 | 6000009.41 | 62567.292 |
| 3 | -> Limit | 20 | 20 | 5999999.40 | [62559.373, 62559.373] |
| 4 | -> Sort | 20 | 20124 | 6000000.93 | [62556.767, 62556.767] |
| 5 | -> Partition Iterator | 54096 | 3354 | 5999909.90 | [62433.361, 62433.361] |
| 6 | -> Partitioned Index Scan using test_day_part_idx on test_day_part | 54096 | 20124 | 5999909.90 | [62421.604, 62421.604] |
- 個人復盤:O記經驗主義,陷入了之前O記運維的經典優化手段,大表日分區剪枝+本地索引。
-
分區粒度過細:查詢跨2-3個月,需掃描60-90個分區,引入大量元數據與調度開銷。
-
索引前導列錯誤:?date_col?作為前導列,無法利用?batch_no?的高選擇性快速過濾。
2. 第二次嘗試:月分區 + batch_no前導索引(改善但未達標)
- 操作:改為月分區,建立?(batch_no, date_col)?的本地索引。
-- 創建日分區表
CREATE TABLE test_day_part (...)
DISTRIBUTE BY HASH(id)
PARTITION BY RANGE(date_col)
(
PARTITION p20250101 VALUES LESS THAN ('2025-01-02'::date),
PARTITION p20250102 VALUES LESS THAN ('2025-01-03'::date),
PARTITION p20250103 VALUES LESS THAN ('2025-01-04'::date),
... -- 省略其他日分區
PARTITION pmax VALUES LESS THAN (maxvalue)
);
--寫入測試數據
--在業務空閑期間,抽取原表數據寫入,耗時1小時,分批Insert,具體步驟在此不計。
-- 寫入數據后再創建本地分區索引,以date_col為前導列 耗時1小時
CREATE INDEX test_day_part_idx ON test_day_part(date_col, batch_no) LOCAL;
-- 收集統計信息
ANALYZE test_day_part;
-
結果:首次查詢耗時約4秒,與原性能持平。
-
EXPLAIN ANALYZE/PERFORMANCE? 執行計劃展示:
| id | operation | A-rows | E-rows | E-cost | A-time |
|-----|------------------------------------------------------------------------------------|--------|--------|--------------|----------------------|
| 1 | Limit | 20 | 20 | 15704.06 | 4027.172 |
| 2 | -> Streaming (type: GATHER) | 20 | 20 | 15704.06 | 4027.165 |
| 3 | -> Limit | 20 | 20 | 15694.14 | [4012.219, 4012.219] |
| 4 | -> Sort | 20 | 3354 | 15702.42 | [4012.208, 4012.208] |
| 5 | -> Partition Iterator | 54096 | 20212 | 15605.40 | [3879.718, 3879.718] |
| 6 | -> Partitioned Index Scan using test_mon_part_idx on test_mon_part | 54096 | 20124 | 15605.40 | [3859.981, 3859.981] |
- 瓶頸分析:
-
物理存儲仍無序:索引可定位約5萬行數據,但回表需隨機讀取分散的數據頁。
-
排序開銷未消除:?ORDER BY?字段與索引無關,仍需內存排序。
3. 第三次優化:月分區 + 索引聚簇(成功)
- 操作:在月分區及?(batch_no, date_col)?索引基礎上,執行:
CLUSTER test_mon_part USING test_mon_part_idx;
ANALYZE test_mon_part;
-
EXPLAIN ANALYZE/PERFORMANCE? 執行計劃展示:
-
注:為了實驗結果合理,盡量減少buffer命中對執行效率提升的干擾因素,第三次優化間隔一段時間后再次進行EXPLAIN PERFORMANCE
| id | operation | A-rows | E-rows | E-cost | A-time |
|-----|------------------------------------------------------------------------------------|--------|--------|--------------|--------------------|
| 1 | Limit | 20 | 20 | 15704.06 | 301.906 |
| 2 | -> Streaming (type: GATHER) | 20 | 20 | 15704.06 | 301.892 |
| 3 | -> Limit | 20 | 20 | 15694.14 | [298.010, 298.010] |
| 4 | -> Sort | 20 | 3354 | 15702.42 | [297.997, 297.997] |
| 5 | -> Partition Iterator | 54096 | 20212 | 15605.40 | [170.168, 170.168] |
| 6 | -> Partitioned Index Scan using test_mon_part_idx on test_mon_part | 54096 | 20124 | 15605.40 | [156.252, 156.252] |
-
原理:?CLUSTER?命令按索引順序物理重組表數據,使符合查詢條件的數據在磁盤上連續存儲。
-
效果:首次查詢耗時降至約300毫秒,且替換條件和日期區間多次執行依然保持毫秒級,實現毫秒級穩定響應。
-
性能提升原因分析:
第二次優化執行計劃Datanode Information:
Datanode Information (identified by plan id)
---------------------------------------------
1 --Limit
(actual time=4027.163..4027.172 rows=20 loops=1)
(CPU: ex c/r=43, ex row=20, ex cyc=879, inc cyc=402714094)
2 --Streaming (type: GATHER)
(actual time=4027.159..4027.165 rows=20 loops=1)
(Buffers: shared hit=4)
(CPU: ex c/r=20135601, ex row=20, ex cyc=402713215, inc cyc=402713215)
3 --Limit
dn_xxx_xxx (actual time=4012.209..4012.219 rows=20 loops=1)
dn_xxx_xxx (CPU: ex c/r=46, ex row=20, ex cyc=926, inc cyc=401211712)
4 --Sort
dn_xxx_xxx (actual time=4012.205..4012.208 rows=20 loops=1)
dn_xxx_xxx (Buffers: shared hit=27 read=7931)
dn_xxx_xxx (CPU: ex c/r=250, ex row=54096, ex cyc=13531916, inc cyc=401210784)
5 --Partition Iterator
dn_xxx_xxx (actual time=0.175..3879.718 rows=54096 loops=1)
dn_xxx_xxx (CPU: ex c/r=39, ex row=54096, ex cyc=12828549, inc cyc=387678669)
6 --Partitioned Index Scan using index_name on table_name
dn_xxx_xxx (actual time=1.673..3859.981 rows=54096 loops=1) (filter time=65.727 projection time=30.799)
dn_xxx_xxx (Buffers: shared hit=10 read=7931)
dn_xxx_xxx (CPU: ex c/r=7130, ex row=54096, ex cyc=385750319, inc cyc=385750319)
第三次優化執行計劃Datanode Information:
Datanode Information (identified by plan id)
---------------------------------------------
1 --Limit
(actual time=317.899..317.911 rows=20 loops=1)
(CPU: ex c/r=51, ex row=20, ex cyc=1035, inc cyc=31790600)
2 --Streaming (type: GATHER)
(actual time=317.894..317.903 rows=20 loops=1)
(Buffers: shared hit=1)
(CPU: ex c/r=1589478, ex row=20, ex cyc=31789565, inc cyc=31789565)
3 --Limit
dn_xxx_xxx (actual time=306.557..306.568 rows=20 loops=1)
dn_xxx_xxx (CPU: ex c/r=52, ex row=20, ex cyc=1053, inc cyc=30655802)
4 --Sort
dn_xxx_xxx (actual time=306.553..306.556 rows=20 loops=1)
dn_xxx_xxx (Buffers: shared hit=20 read=5997)
dn_xxx_xxx (CPU: ex c/r=212, ex row=54096, ex cyc=11497009, inc cyc=30654749)
5 --Partition Iterator
dn_xxx_xxx (actual time=0.177..193.910 rows=54096 loops=1)
dn_xxx_xxx (CPU: ex c/r=24, ex row=54096, ex cyc=1341027, inc cyc=19157740)
6 --Partitioned Index Scan using index_name on table_name
dn_xxx_xxx (actual time=0.624..180.625 rows=54096 loops=4) (filter time=54.221 projection time=28.917)
dn_xxx_xxx (Buffers: shared hit=3 read=5997)
dn_xxx_xxx (CPU: ex c/r=329, ex row=54096, ex cyc=17816713, inc cyc=17816713)
-
數據物理有序后,對比Datanode Information的Plan id 4和6發現,排序和索引掃描時間?actual time?大幅減少。
-
關注?Buffers: shared hit?與?read?的比例,第一個計劃需要從磁盤讀取約7931個數據塊,而第二個計劃僅需讀取約5997個,減少了近25%。這直接導致了第二個計劃各層算子的I/O等待時間大幅縮短,進而使得CPU能夠更高效地工作,整體響應時間更快(cyc執行周期數的降低)。
三、局部聚簇適用條件與操作指南
適用場景
-
查詢模式固定(WHERE、ORDER BY條件穩定)。
-
返回數據量小(如?LIMIT 20?)。
-
表為分析型(AP)負載,極少有DML操作(INSERT/UPDATE/DELETE會破壞聚簇順序)。
操作步驟
-
創建索引:按查詢條件創建本地分區索引,等值字段在前(如?(batch_no, date_col)?)。
-
執行聚簇:
-
歷史數據:全表?CLUSTER?(需在業務低峰期進行,鎖表耗時較長)。
-
增量數據:對單個分區執行?CLUSTER?(推薦,影響小)。
-
更新統計信息:聚簇后立即執行?ANALYZE?。
-
驗證監控:檢查執行計劃,定期監控查詢性能。
避坑要點
-
維護成本:聚簇非一勞永逸,新增數據會破壞順序,需定期對增量分區執行聚簇。
-
索引匹配:聚簇依賴的索引必須與高頻查詢條件高度匹配。
-
鎖與資源:?CLUSTER?需要?ACCESS EXCLUSIVE?鎖,且消耗大量I/O與臨時空間(尤其注意防止集群只讀)。
-
分區策略:先確保分區粒度(如月分區)與查詢范圍匹配,再實施聚簇。
四、總結
對于海量數據表的固定點查優化,在正確設計分區和索引的基礎上,通過局部聚簇(CLUSTER)將數據物理重組,是實現毫秒級穩定查詢的關鍵。該方案僅適用于極少更新、查詢模式固定的分析型AP場景,但必須配套定期的增量數據聚簇維護,以保持性能。




