DWS優化實戰：40億行大表點查從秒級到毫秒級的優化之路

原創 Rune悠然 2026-01-04

607

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場景，但必須配套定期的增量數據聚簇維護，以保持性能。

數據庫實操性能優化墨力計劃 gaussdb(dws)大表點查

「喜歡這篇文章，您的關注和贊賞是給作者最好的鼓勵」

關注作者

文章被以下合輯收錄

墨天輪數據庫性能優化征文-優質文章合集（共37篇）

本合輯匯總了「實操看我的」數據庫性能優化篇征文活動中“最佳實操獎” 文章及部分高閱讀量合格文章，歡迎參考~

DWS數倉服務專題（共1篇）

DWS數倉服務專題，分享一些運維案例

特色一级强游戏,海奥华预言免费阅读,51漫画兑换码,美女裸体无遮挡永久免费观看网站,lubuntu线路检测入口