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場景，但必須配套定期的增量數據聚簇維護，以保持性能。
「喜歡這篇文章，您的關注和贊賞是給作者最好的鼓勵」
關注作者
【版權聲明】本文為墨天輪用戶原創內容，轉載時必須標注文章的來源（墨天輪），文章鏈接，文章作者等基本信息，否則作者和墨天輪有權追究責任。如果您發現墨天輪中有涉嫌抄襲或者侵權的內容，歡迎發送郵件至：contact@modb.pro進行舉報，并提供相關證據，一經查實，墨天輪將立刻刪除相關內容。
