一、测试环境说明
数据库版本：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
