一、測試環境說明
數據庫版本：KingbaseES V009R002C013
測試庫：perfdb
核心業務表：orders
數據量：
orders 表約 3000 萬行
user_id 基數約 100 萬
二、構造測試模型（真實業務模擬）
為了貼近真實業務，我們構造三個典型表：
用戶表：users
訂單表：orders
訂單明細：order_items
1. 創建測試數據庫和用戶
CREATE DATABASE perfdb;
CREATE USER perf WITH PASSWORD 'Perf@123';
GRANT ALL PRIVILEGES ON DATABASE perfdb TO perf;
連接數據庫：
ksql -U perf -d perfdb
2. 建表結構
CREATE TABLE users (
user_id BIGINT PRIMARY KEY,
user_name VARCHAR(50),
create_time TIMESTAMP
);
CREATE TABLE orders (
order_id BIGINT PRIMARY KEY,
user_id BIGINT,
status INT,
amount NUMERIC(10,2),
create_time TIMESTAMP
);
CREATE TABLE order_items (
item_id BIGINT PRIMARY KEY,
order_id BIGINT,
product_id BIGINT,
price NUMERIC(10,2)
);
三、造測試數據（千萬級）
本節目標是構造“數據量足夠大、分布足夠真實”的測試數據，讓優化器、統計視圖、執行器優化都能在壓力下體現差異。
1. 用戶表：100 萬
INSERT INTO users
SELECT
generate_series(1,1000000),
'user_' || generate_series(1,1000000),
NOW() - random() * interval '365 days';
2. 訂單表：3000 萬
INSERT INTO orders
SELECT
generate_series(1,30000000),
(random() * 1000000)::BIGINT,
(random() * 5)::INT,
round(random() * 1000, 2),
NOW() - random() * interval '180 days';
3. 明細表：7000 萬
INSERT INTO order_items
SELECT
generate_series(1,70000000),
(random() * 30000000)::BIGINT,
(random() * 100000)::BIGINT,
round(random() * 1000, 2);
四、建立索引（優化基礎）
這一節不追求“面面俱到”，只建立最貼近業務訪問路徑的基礎索引，為后續場景提供可對比的執行路徑。
CREATE INDEX idx_orders_user ON orders(user_id);
CREATE INDEX idx_orders_time ON orders(create_time);
CREATE INDEX idx_items_order ON order_items(order_id);
五、測試場景一：SQL 參數值統計實測
這個場景要解決的核心問題是：
同一條 SQL，因為參數值不同，返回行數不同，執行路徑（Index / Seq Scan）和耗時也可能不同。
1）開啟 SQL 采集開關
在數據庫配置文件 kingbase.conf 中開啟 SQL 統計相關參數，例如：
track_sql = on
track_instance = on
track_counts = on
track_activities = on
track_wait_timing = on
track_io_timing = on
sys_stat_statements.max = 10000
sys_stat_statements.track = 'top'
sys_stat_statements.track_utility = off
sys_stat_statements.save = on
通過 sys_ctl reload 讓配置生效，數據庫開始為每條 SQL 采集執行統計信息。
2）測試 SQL（逐參數執行）
本次測試使用固定參數：
SELECT * FROM orders WHERE user_id = 300172;
執行結果如下（部分節選）：
order_id | user_id | status | amount | create_time
–––––+———+––––+––––+––––––––––––––
14903693 | 300172 | 4 | 810.64 | 2025-11-26 14:42:05.651282
12990199 | 300172 | 5 | 648.42 | 2025-09-21 15:36:58.382901
14966513 | 300172 | 1 | 121.48 | 2025-12-02 15:00:14.610596
…
5432652 | 300172 | 3 | 303.72 | 2025-11-13 02:20:31.287637
(25 rows)
不同 user_id 返回行數不同，也意味著執行計劃（Index / Seq Scan）和耗時也可能不同。
3）查看 SQL 參數執行統計（sys_stat_statements）
執行統計查詢：
SELECT
query,
calls,
total_exec_time,
mean_exec_time,
rows
FROM sys_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;
4）真實統計結果（Top SQL 實測數據）
（1）復雜聚合 SQL：耗時最高
SELECT user_id, sum(amount)
FROM orders
GROUP BY user_id
ORDER BY sum(amount) DESC
LIMIT $1
calls：1
total_exec_time：16961 ms
rows：20
（2）我們測試的重點 SQL：參數 user_id 的性能表現
SELECT * FROM orders where user_id=300172;
? calls：3
? total_exec_time：6320 ms
? mean_exec_time：2106 ms
? rows：75
說明：
對 user_id=300172 執行了 3 次，共返回 75 行，平均每次執行約 2 秒。
這是典型的 參數敏感 SQL（Parameter-Sensitive Plan） 行為：
不同參數 → 不同返回行數 → 不同執行耗時。
（3）其他 SQL 的統計信息
例如：
SELECT count(*) FROM orders
? total_exec_time：1674 ms
SELECT * FROM orders WHERE user_id=$1 LIMIT $2
? total_exec_time：736 ms
? rows：10
這些數據與實際 workload 完全一致，已經成功被 sys_stat_statements 捕獲。
5）開啟 SQL 參數采集后，可以看到什么？
你現在已經可以清晰觀察到：
1、哪些參數值觸發 Seq Scan
例如：
? user_id 分布極度不均，會導致某些參數觸發大量 Block 讀取。
你可以進一步通過：
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE user_id = 300172;
來查看真實計劃。
2、 哪些參數走 Index Scan
如果 user_id 有更好的選擇度，執行計劃會切換為 Index Scan，響應時間會大幅下降。
3、 哪些參數值導致極端慢（高耗時 outlier）
sys_stat_statements 中你看到：
SQL calls total_exec_time mean_exec_time rows
SELECT * FROM orders where user_id=$1 3 6320 ms 2106 ms 75
說明 特定參數造成明顯慢查詢，而不是 SQL 本身有問題。
六、測試場景二：數據庫時間模型視圖
1）執行壓測
可以使用例如 JMeter、sysbench、自寫循環 SQL 等方式進行壓力模擬。
例如執行 30 萬次 user_id 查詢：
DO $$
DECLARE i int;
BEGIN
FOR i IN 1..300000 LOOP
PERFORM * FROM orders WHERE user_id = floor(random()*900000);
END LOOP;
END$$;
2）查詢數據庫時間模型
執行壓測后查詢：
SELECT *
FROM sys_stat_dbtime
ORDER BY metric;
metric | calls | total_time | avg_time | dbtime_pct
-------------------+---------+------------+------------+------------
Analyze Event | 168 | 87452 | 520.55 | 0.04
CommitTransaction | 141 | 2579 | 18.29 | 0.00
DB CPU | | 125614524 | | 55.21
DB Time | | 227531123 | | 100.00
Execute Event | 139 | 227130255 | 1634030.61 | 99.82
FG Wait | 1807238 | 101916599 | 56.39 | 44.79
InitializePlan | 105 | 25006 | 238.15 | 0.01
Net Read | 156 | 1683 | 10.79 | 0.00
Net Write | 271375 | 26889619 | 99.09 | 11.82
Parse Event | 175 | 12161 | 69.49 | 0.01
Plan Event | 133 | 92939 | 698.79 | 0.04
PLPGSQL Compile | 2 | 833 | 416.50 | 0.00
PLPGSQL Execute | 2 | 43339 | 21669.50 | 0.02
Rewrtie Event | 167 | 16222 | 97.14 | 0.01
Simple Message | 152 | 227509433 | 1496772.59 | 99.99
Wait Message | 317 | 21690 | 68.42 | 0.01
(16 rows)
七、優化器能力實測
這一節只圍繞一個點：
優化器是否能把“業務寫法”自動轉成“更合理的執行方式”，并且行為可解釋。
1. NOT IN 優化：從子查詢到 Hash Anti Join
在一些中大型業務系統里，類似下面的語句非常常見：
EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM users
WHERE user_id NOT IN (
SELECT user_id
FROM orders
WHERE status = 0
);
語義很直觀：
? users：用戶主表
? orders：訂單表
? 希望找出沒有 status=0 訂單的用戶
但在傳統數據庫實現里，這種寫法有兩個經典的坑：
1. 性能問題：
早期實現經常會走 Nested Loop + 子查詢，外層 users 一行行去掃 orders，在數據量大的時候非常慢。
2. NULL 語義問題：
一旦子查詢返回的 user_id 列里混入了 NULL，NOT IN 的三值邏輯會讓結果變得“出人意料”。
1.1 測試環境準備（示意）
假設我們有：
? users 表：100 萬用戶
? orders 表：3000 萬訂單（上一節已經準備好的那張大表）
1.2 原始 SQL：NOT IN 子查詢
我們先直接執行原始寫法，并觀察執行計劃：
EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM users
WHERE user_id NOT IN (
SELECT user_id
FROM orders
WHERE status = 0
);
在 KingbaseES V9 上，優化器如果判斷數據量足夠大、統計信息完善，會嘗試把它改寫成 Anti Join 形式。
理想情況下，你應該能在執行計劃中看到類似這樣的節點（計劃文本略有差異沒關系）：
--------------------------------------------------------------------------------------------------------------------------------
Hash Anti RSNA Join (cost=1085654.98..1149259.12 rows=51114 width=27) (actual time=8271.625..11100.020 rows=49433 loops=1)
Hash Cond: (users.user_id = orders.user_id)
Buffers: shared hit=17956 read=241556, temp read=16413 written=16413
I/O Timings: read=1360.948
-> Seq Scan on users (cost=0.00..17408.00 rows=1000000 width=27) (actual time=0.015..197.510 rows=1000000 loops=1)
Buffers: shared hit=2 read=7406
I/O Timings: read=51.565
-> Hash (cost=627101.25..627101.25 rows=2999002 width=8) (actual time=8265.331..8265.332 rows=3001680 loops=1)
Buckets: 131072 Batches: 64 Memory Usage: 2858kB
Buffers: shared hit=17951 read=234150, temp written=10069
I/O Timings: read=1309.383
-> Seq Scan on orders (cost=0.00..627101.25 rows=2999002 width=8) (actual time=0.015..6385.056 rows=3001680 loops=1)
Filter: (status = 0)
Rows Removed by Filter: 26998320
Buffers: shared hit=17951 read=234150
I/O Timings: read=1309.383
Planning Time: 2.039 ms
Execution Time: 11103.603 ms
(18 rows)
1. 計劃解讀：優化器已經在“幫你改寫”
這份計劃里，有幾個關鍵點可以直接寫進文章總結優化器能力：
1）. NOT IN → Hash Anti Join
? 頂層節點是：Hash Anti RSNA Join
? 說明優化器并不是“死磕子查詢”，而是把 NOT IN 自動等價改寫成了 反連接（Anti Join）：
? 外表：users
? 內表：orders(status = 0)
? 語義：找出在 orders(status=0) 中不存在的 users.user_id
也就是說，這條簡單的業務 SQL，優化器已經自動做了：
? 子查詢重寫 → Anti Join
? 再選擇具體算法：Hash Anti Join
2）. 外層 users：順序掃描成本可控
Seq Scan on users
actual time=0.015…197.510 rows=1000000
? 全表 100 萬行，197ms 掃完
? 說明 users 體量相對可控，即便 Seq Scan 整體開銷也不算太大
? 在這樣的大表對大表場景里，讓 users 當外表做 Anti Join 是合理的選擇
3）. 內層 orders：status=0 過濾 + Hash 構建
Seq Scan on orders
actual time=0.015…6385.056 rows=3001680
Filter: (status = 0)
Rows Removed by Filter: 26998320
? orders 共約 3000 萬行，其中 status = 0 命中約 300 萬行
? 這 300 萬行被用于構建 Hash 表（Hash 節點），供上層 Anti Join 使用
? Hash 節點的 Buckets: 131072 Batches: 64 + temp read/write，說明：
? 數據量較大
? Hash 構建需要分批（多 Batch），并在磁盤上產生了臨時文件（temp read=16413 written=16413）
4）. I/O 主導的 11 秒執行時間
? 總執行時間：11.1 秒
? 其中：
? orders 順序掃描 IO 時間：I/O Timings: read=1309.383 ms
? 總體 IO 時間（包括 Hash 等）：read=1360.948 ms
? 結合 Buffers: shared hit / read 可以看出：
? 這是典型的 “大表全掃 + 大量磁盤讀 + Hash 需要落盤” 場景
? 性能瓶頸主要在 存儲 IO + Hash 過程的外部批處理，而不是優化器策略錯誤
- 從優化器視角看：這算“合格”還是“優秀”？
可以在文章里給出你作為 DBA 的判斷：
? 優化策略層面：是“合格甚至優秀”的
? ? NOT IN 能夠自動改寫為 Hash Anti Join，而不是 na?ve 的子查詢嵌套循環
? ? 正確識別“大表 orders + 相對較小的 users”的場景，讓 orders 當內表構建 Hash
? ? 結合過濾條件 status = 0 先做篩選再 Hash，避免把 3000 萬行全部寫入 Hash
1.3. OR 自動改寫為 UNION ALL
在 orders（約 3000 萬行）表上，測試如下 SQL：
SELECT *
FROM orders
WHERE status = 1 OR user_id = 100;
以及同字段 OR 的寫法：
SELECT *
FROM orders
WHERE status = 1 OR status = 2;
即使在 status、user_id 上均已建立索引，執行計劃依然選擇：
Seq Scan on orders
Filter: ((status = 1) OR (user_id = 100))
或：
Seq Scan on orders
Filter: ((status = 1) OR (status = 2))
從執行結果可以看到：
? OR 條件命中行數占比高（約 40%）
? SELECT * 導致走索引時需要大量隨機回表
? 拆分為 UNION ALL 后的索引路徑綜合成本高于順序掃描
因此，優化器基于成本模型選擇了 Seq Scan + Filter，而不是 OR → UNION ALL 改寫。
這說明：OR 條件并不會“必然觸發” UNION ALL 改寫，是否改寫取決于數據分布、選擇性以及訪問路徑成本。
1.4. UNION 外層條件下推
測試 SQL：
SELECT * FROM (
SELECT * FROM orders WHERE status = 1
UNION ALL
SELECT * FROM orders WHERE status = 2
) a
WHERE create_time > NOW() - interval '7 days';
很多人在寫 UNION ALL 時，會把時間條件放在最外層。邏輯正確，但性能是否好，取決于優化器能不能把外層謂詞 提前下推 到各分支內部。
（1）實測執行計劃：謂詞已經被下推到每個分支
執行計劃中最關鍵的證據是：外層的時間條件不再“最后過濾”，而是直接變成兩個分支的聯合過濾條件：
? 分支 1（status = 1）：
Filter: ((status = 1) AND (create_time > (now() - ‘7 days’::interval)))
Rows Removed by Filter: 9967536
? 分支 2（status = 2）：
Filter: ((status = 2) AND (create_time > (now() - ‘7 days’::interval)))
Rows Removed by Filter: 14951402
這說明優化器已經把原本外層的：
WHERE create_time > now() - interval ‘7 days’
自動下推到了 UNION ALL 的每個子查詢。
這就是典型的 Predicate Pushdown（謂詞下推）：
把“最后過濾”提前到“每個分支先過濾”。
（2）并行執行 + Append 合并：典型的大表優化路徑
計劃頂層結構是：
Gather
-> Parallel Append
-> Parallel Seq Scan on orders (status=1 AND create_time>7days)
-> Parallel Seq Scan on orders (status=2 AND create_time>7days)
說明三件事：
1. UNION ALL 被實現為 Append（合并結果集，不做去重）
2. 兩個分支各自并行掃描（Workers Planned/Launched = 2）
3. 合并階段由 Gather 匯總輸出
（3）收益點：提前過濾讓“參與合并的數據”顯著變少
你這次實測的最終結果行數：
? 總輸出：約 194,589 行
? 但是在兩個分支中，被提前過濾掉的行數分別是：
? status=1：過濾掉 9,967,536 行
? status=2：過濾掉 14,951,402 行
也就是說：如果不下推，UNION ALL 的合并階段會吞下接近 2500 萬行，再在外層過濾；而現在優化器把過濾提前做掉，讓合并階段只處理“7 天內的數據”。
執行時間上，你這次的整體耗時約：
? 6.1 秒（第一條 SQL）
? I/O read 時間約 2.8 秒（read=2775ms）
DBA 視角：
這類優化的價值不在于“語法更優雅”，而在于減少參與 UNION/Append 的數據規模，從而讓大表查詢更穩定。
（4）NOT MATERIALIZED 對比：這次差異不大，但結論一致
你額外跑了：
WITH a AS NOT MATERIALIZED (…)
SELECT * FROM a WHERE create_time > …
結果同樣顯示謂詞在分支內生效，且總體耗時在 6.3 秒 左右，和第一條差異不大。
這也說明：
? 本次是否物化并不是瓶頸核心
? 關鍵點仍然是：謂詞已經下推成功
八、自治事務性能實測
在合規審計、運維留痕、金融風控等系統里，有一個很現實的要求：
業務事務可以失敗回滾，但審計日志必須可靠落庫。
如果審計和業務綁定在同一個事務里，一旦回滾，日志也會一起消失，導致“留痕鏈條斷裂”。
KingbaseES 的 自治事務（Autonomous Transaction），就是為了解決這個問題。
1）準備：創建審計日志表
CREATE TABLE audit_log (
id BIGINT PRIMARY KEY,
msg TEXT,
create_time TIMESTAMP
);
2）創建自治事務過程：write_audit_log
關鍵點：使用 LANGUAGE plsql 并通過 $$…$$ 一次性提交完整塊，避免客戶端拆句解析。
CREATE OR REPLACE PROCEDURE write_audit_log(p_id BIGINT, p_msg TEXT)
AS $$
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO audit_log(id, msg, create_time)
VALUES (p_id, p_msg, now());
COMMIT;
END;
$$ LANGUAGE plsql;
執行結果：
CREATE PROCEDURE
3）模擬業務失敗：主事務回滾
BEGIN;
CALL write_audit_log(1, 'biz do something, then rollback');
ROLLBACK;
輸出：
CALL
ROLLBACK
4）驗證：業務回滾不影響審計落庫
SELECT * FROM audit_log WHERE id = 1;
輸出（核心證據）：
id | msg | create_time
----+---------------------------------+----------------------------
1 | biz do something, then rollback | 2025-12-13 06:24:59.996775
(1 row)
結論非常清晰：
? 主事務已回滾（ROLLBACK 生效）
? 審計日志仍然成功提交（自治事務獨立提交）
十、DBA 總結
這次測試覆蓋了四類 DBA 最關心的能力點：可觀測、可拆解、可解釋、可兜底。
1）SQL 性能不再靠猜
通過 SQL 統計與參數采集，sys_stat_statements 能把同一條 SQL 在不同參數下的真實執行行為記錄下來。對 DBA 來說，最直接的價值是：
? 性能抖動到底是 SQL 問題，還是參數分布導致的，不再靠經驗判斷。
2）數據庫時間模型把“慢”拆成結構
壓測后查詢 sys_stat_dbtime，能把 DB Time 拆成 CPU、等待、網絡、解析、執行等維度。這樣 DBA 在進入細節優化之前，就能先回答一個更關鍵的問題：
? 慢主要耗在哪一類時間上？
3）優化器行為更像工程決策，而不是機械改寫
? NOT IN 能改寫為 Hash Anti Join，說明優化器愿意為業務寫法做等價重寫；
? OR 條件沒有強制改寫為 UNION ALL，說明優化器不是“看到 OR 就改”，而是基于成本模型做選擇；
? UNION 外層條件下推，說明優化器能把“最后過濾”提前到“分支先過濾”，減少參與合并的數據量，讓大表查詢更穩定。
4）自治事務證明：失敗路徑也能可靠留痕
主事務回滾，但審計日志仍然提交成功，這是典型的“失敗路徑兜底能力”。對審計、合規、運維留痕類系統來說，這類能力的意義往往比單條 SQL 的快慢更大。
性能優化不是把參數調到極限，而是讓問題可被觀測、路徑可被解釋、結果可被復現，異常路徑也能被兜住。
作者：Digital Observer（施嘉偉）
Oracle?ACE?Pro
PostgreSQL ACE Partner
Oracle?OCM、KCM、PGCM、YCP、DB2 、MySQL OCP、PCTP、PCSD、OCI、PolarDB技術專家、達夢師資認證，從業11年+
ITPUB認證專家、崖山YVP、PolarDB開源社區技術顧問、HaloDB技術顧問、TiDB社區技術布道師、青學會MOP技術社區專家顧問、國內某高校企業實踐指導教師
公眾號/墨天輪/金倉社區/IF Club：Digital Observer；CSDN/PGfans：施嘉偉；ITPUB：sjw1933
