KES 性能调优实战：执行计划、索引优化与查询重写完全指南

KES 性能调优实战：执行计划、索引优化与查询重写完全指南

开篇的话

做数据库这行,性能问题是最让人头疼的。白天系统跑得挺顺,一到晚高峰就开始卡,用户投诉电话一个接一个。排查下来,十有八九是 SQL 写得有问题或者索引没建对。我干 DBA 这些年,处理过的性能工单没有一千也有八百,总结下来就一句话:大部分性能问题都是可以避免的,关键是你要懂执行计划,会看索引,知道怎么改写查询。

这篇文章专门聊 KingbaseES 的性能调优,重点讲三个核心技能:读懂执行计划、设计高效索引、优化慢查询。内容偏实操,理论部分点到为止,大量使用真实案例和对比数据。如果你经常要跟慢 SQL 打交道,或者想系统学习 KES 的性能优化方法,这篇文章应该能帮到你。

一、执行计划基础：看懂优化器的决策

执行计划(EXPLAIN)是性能调优的起点。它告诉你数据库准备怎么执行这条 SQL:用哪个索引、用什么连接方式、预计返回多少行。看不懂执行计划,调优就无从谈起。

EXPLAIN 的基本用法

— 最简单的用法,只看计划不执行
EXPLAIN SELECT * FROM orders WHERE user_id = 1001;

— 加上 ANALYZE,实际执行并显示真实统计信息
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 1001;

— 显示缓冲区使用情况(需要开启 track_io_timing)
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE user_id = 1001;

— JSON 格式输出,方便程序解析
EXPLAIN (ANALYZE, FORMAT JSON) SELECT * FROM orders WHERE user_id = 1001;

日常调优我最常用的是 EXPLAIN (ANALYZE, BUFFERS),既能看到真实的执行时间,又能看到 IO 情况。不过要注意,ANALYZE 会真正执行 SQL,所以别在生产环境随便跑,特别是那些涉及大量数据的查询。

执行计划的核心要素

一条典型的执行计划长这样:

Nested Loop (cost=0.86..23.45 rows=10 width=128) (actual time=0.032..0.089 rows=8 loops=1)
–> Index Scan using idx_orders_user_id on orders (cost=0.43..8.45 rows=10 width=64)
(actual time=0.018..0.045 rows=8 loops=1)
Index Cond: (user_id = 1001)
Buffers: shared hit=12
–> Index Scan using idx_users_pkey on users (cost=0.43..1.45 rows=1 width=64)
(actual time=0.002..0.003 rows=1 loops=8)
Index Cond: (id = orders.user_id)
Buffers: shared hit=8

看着复杂,拆解开来就几个关键信息:

1. 节点类型(Node Type)

常见的节点类型有:

• Seq Scan: 全表扫描,最慢的操作,数据量大时要避免
• Index Scan: 索引扫描,通过索引找到数据行
• Index Only Scan: 只查索引就能拿到所有需要的字段,不用回表,最快
• Bitmap Index Scan: 位图索引扫描,适合多个条件组合查询
• Nested Loop: 嵌套循环连接,小表驱动大表时效率高
• Hash Join: 哈希连接,适合大表关联
• Merge Join: 归并连接,适合两个都已排序的大表关联
• Sort: 排序操作,如果内存不够会用临时文件
• Aggregate: 聚合操作(GROUP BY、COUNT、SUM 等)
• Limit: 限制返回行数

2. cost(成本估算)

cost=0.86..23.45

第一个数字是启动成本(返回第一行的代价),第二个是总成本(返回所有行的代价)。这个值是优化器估算的,单位不是时间,而是一个抽象的成本值。不同节点的 cost 不能直接比较,但同一种操作的 cost 可以用来判断优劣。

3. rows(预估行数)

rows=10

优化器预计这个节点会返回多少行。如果跟 actual rows 差距很大,说明统计信息不准确,需要 ANALYZE 更新。

4. width(平均每行宽度)

width=128

预计每行数据的字节数。这个值影响内存使用和传输开销。

5. actual time(实际执行时间)

actual time=0.032..0.089

只有在 EXPLAIN ANALYZE 模式下才有。第一个数字是返回第一行的时间(毫秒),第二个是返回所有行的总时间。这是真实性能数据,比 cost 更有参考价值。

6. Buffers(缓冲区命中)

Buffers: shared hit=12

显示缓存命中情况:

• hit: 从共享缓冲区读取(内存,快)
• read: 从磁盘读取(慢)
• dirty: 脏页数量
• written: 写回的页数

hit 越多越好,read 多了说明缓存命中率低,可能需要加大 shared_buffers 或者优化查询减少 IO。

7. loops(循环次数)

loops=1

这个节点被执行了多少次。Nested Loop 的内层节点 loops 通常大于 1,表示对每一行外层数据都执行了一次内层查询。

执行计划的阅读顺序

执行计划是从下往上、从右往左读的。最底层的节点最先执行,结果传给上层节点。以上面的例子为例:

理解了这个执行顺序,才能看出哪里可以优化。比如上面的例子,如果 orders 表有 100 万行,但符合条件的只有 8 行,那用索引扫描就很合适。但如果符合条件的有 10 万行,可能全表扫描反而更快(因为避免了大量的随机 IO)。

常见执行计划模式解读

模式一:理想的高效查询

Index Only Scan using idx_orders_user_id_status on orders
(cost=0.43..8.45 rows=10 width=32)
(actual time=0.015..0.025 rows=8 loops=1)
Index Cond: (user_id = 1001 AND status = 1)
Buffers: shared hit=5

特点:

• Index Only Scan,不需要回表
• 实际行数跟预估接近
• 全部 buffer hit,没有磁盘 IO
• 执行时间在毫秒级

这种查询基本不用优化,已经是最优了。

模式二:需要警惕的全表扫描

Seq Scan on orders (cost=0.00..23456.78 rows=500000 width=128)
(actual time=12.345..456.789 rows=498234 loops=1)
Filter: (created_at > '2026-01-01')
Rows Removed by Filter: 1501766
Buffers: shared hit=1234 read=5678

特点:

• Seq Scan 全表扫描
• 过滤掉了大量行(Rows Removed by Filter 很大)
• 有很多磁盘 read
• 执行时间长

这种查询通常是因为缺少合适的索引,或者统计信息不准导致优化器选错了计划。

模式三:嵌套循环的性能陷阱

Nested Loop (cost=0.86..123456.78 rows=1000000 width=128)
(actual time=0.032..12345.678 rows=998234 loops=1)
–> Seq Scan on large_table_a (cost=0.00..1234.56 rows=10000 width=64)
(actual time=0.015..12.345 rows=10000 loops=1)
–> Index Scan using idx_b_fk on large_table_b (cost=0.43..12.34 rows=100 width=64)
(actual time=0.001..1.200 rows=100 loops=10000)
Index Cond: (a_id = large_table_a.id)
Buffers: shared hit=12345 read=6789

特点:

• Nested Loop 的外层是大表(10000 行)
• 内层索引扫描执行了 10000 次(loops=10000)
• 总执行时间很长(12 秒+)
• 大量磁盘 IO

这种场景应该考虑改用 Hash Join,或者优化索引让内层查询更快。

二、索引设计与优化策略

索引是提升查询性能最直接有效的手段,但也不是越多越好。索引建对了事半功倍,建错了适得其反。这部分讲讲 KES 里常用的索引类型和设计原则。

B-Tree 索引:最常用的选择

B-Tree 是默认索引类型,适合等值查询、范围查询、排序等操作。

— 创建普通索引
CREATE INDEX idx_orders_user_id ON orders(user_id);

— 创建复合索引
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

— 创建唯一索引
CREATE UNIQUE INDEX idx_users_email ON users(email);

— 降序索引(适合 ORDER BY … DESC 的场景)
CREATE INDEX idx_orders_created_desc ON orders(created_at DESC);

复合索引的顺序很重要

复合索引遵循"最左前缀"原则。比如 idx_orders_user_status(user_id, status) 这个索引:

• ✅ WHERE user_id = 1001 – 能用上索引
• ✅ WHERE user_id = 1001 AND status = 1 – 能用上索引
• ❌ WHERE status = 1 – 用不上索引(跳过了第一列)
• ✅ WHERE user_id = 1001 ORDER BY status – 能用上索引排序

设计复合索引时,要把区分度高、常用于等值查询的列放前面。比如 (user_id, status) 比 (status, user_id) 更好,因为 user_id 的区分度更高。

覆盖索引(Covering Index)

如果索引包含了查询需要的所有字段,就可以实现 Index Only Scan,避免回表:

— 查询只需要 user_id 和 status
SELECT user_id, status FROM orders WHERE user_id = 1001;

— 创建覆盖索引
CREATE INDEX idx_orders_cover ON orders(user_id, status);

— 执行计划会显示 Index Only Scan
EXPLAIN SELECT user_id, status FROM orders WHERE user_id = 1001;

覆盖索引能显著提升性能,特别是当表有很多列而查询只需要其中几列的时候。

部分索引(Partial Index)

如果只需要索引表中的一部分数据,可以用部分索引节省空间:

— 只索引未完成的订单
CREATE INDEX idx_orders_pending ON orders(user_id, created_at)
WHERE status = 0;

— 查询时自动用上这个索引
SELECT * FROM orders
WHERE user_id = 1001 AND status = 0
ORDER BY created_at DESC;

部分索引特别适合状态字段有明显倾斜的场景。比如订单表里 90% 都是已完成的,只索引未完成的那 10%,索引体积小很多,查询也快。

GIN 索引:JSONB 和数组的利器

前面文章提到过 JSONB,GIT 索引是查询 JSONB 和数组类型的最佳选择:

— JSONB 字段的 GIN 索引
CREATE INDEX idx_profiles_gin ON user_profiles USING GIN (profile);

— 数组字段的 GIN 索引
CREATE INDEX idx_tags_gin ON articles USING GIN (tags);

— 查询会自动用上索引
SELECT * FROM user_profiles
WHERE profile @> '{"city": "北京"}';

SELECT * FROM articles
WHERE tags && ARRAY['技术', '数据库'];

GIN 索引的缺点是写入性能比普通索引差,因为要维护倒排列表。如果表写入频繁,要权衡一下。

GiST 索引:空间和全文检索

GiST 索引适合几何数据类型和全文检索:

— 地理位置查询
CREATE INDEX idx_locations_gist ON locations USING GiST (coordinates);

— 查询附近的点
SELECT * FROM locations
WHERE coordinates <–> point(116.4, 39.9) < 0.01
ORDER BY coordinates <–> point(116.4, 39.9)
LIMIT 10;

— 全文检索
CREATE INDEX idx_articles_tsvector ON articles USING GiST (to_tsvector('chinese', content));

SELECT * FROM articles
WHERE to_tsvector('chinese', content) @@ to_tsquery('数据库 & 性能');

索引维护与监控

索引不是建完就完了,需要定期维护和监控。

检查索引使用情况

— 查看哪些索引从来没被用过
SELECT schemaname, relname, indexrelname, idx_scan
FROM sys_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

长期不用的索引可以删掉,节省空间并提升写入性能。但要注意,有些索引可能在特定场景下才用到(比如月度报表),删除前要确认。

检查索引膨胀

频繁的 UPDATE 和 DELETE 会导致索引膨胀:

— 检查索引大小与表大小的比例
SELECT
schemaname || '.' || relname AS table_name,
indexrelname AS index_name,
pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
pg_size_pretty(pg_relation_size(relid)) AS table_size,
round(100.0 * pg_relation_size(indexrelid) / NULLIF(pg_relation_size(relid), 0), 1) AS ratio
FROM sys_stat_user_indexes
WHERE pg_relation_size(indexrelid) > 100 * 1024 * 1024 — 大于 100MB
ORDER BY pg_relation_size(indexrelid) DESC;

如果索引大小超过表大小的 50%,可能需要重建索引:

— 重建索引(会锁表,建议在低峰期执行)
REINDEX INDEX idx_orders_user_id;

— 或者并发重建(不锁表,但耗时更长)
REINDEX INDEX CONCURRENTLY idx_orders_user_id;

统计信息更新

优化器依赖统计信息来做决策,统计信息不准会导致选错执行计划:

— 手动更新统计信息
ANALYZE orders;

— 更新所有表
ANALYZE;

— 查看上次更新时间
SELECT relname, last_analyze, last_autoanalyze
FROM sys_stat_user_tables
ORDER BY last_analyze DESC;

autovacuum 会自动执行 ANALYZE,但对于写入量特别大的表,可能需要手动增加频率。

三、慢查询定位与分析

知道了怎么看执行计划和索引,下一步就是找出系统中的慢查询并优化它们。

开启慢查询日志

最简单的方式是通过日志记录慢查询:

— kingbase.conf 配置
log_min_duration_statement = 1000 — 记录超过 1 秒的查询
log_statement = 'none' — 不记录所有语句(性能影响大)
log_duration = off — 不记录每条语句的耗时

— 重新加载配置
sys_ctl –D /data/kingbase/data reload

日志文件在数据目录的 sys_log 子目录下:

# 查找慢查询
grep "duration:" /data/kingbase/data/sys_log/kingbase-2026-06-05.log | sort -t':' -k2 -rn | head -20

这种方式简单直接,但日志量大的时候分析起来费劲。更好的办法是用 sys_stat_statements 扩展。

使用 sys_stat_statements

sys_stat_statements 会累积统计所有执行过的 SQL,包括调用次数、总耗时、平均耗时等:

— 启用扩展
CREATE EXTENSION IF NOT EXISTS sys_stat_statements;

— 查询最慢的 TOP 20 SQL
SELECT query, calls, total_time, mean_time, rows
FROM sys_stat_statements
ORDER BY mean_time DESC
LIMIT 20;

— 查询总耗时最高的 TOP 20 SQL
SELECT query, calls, total_time, mean_time, rows
FROM sys_stat_statements
ORDER BY total_time DESC
LIMIT 20;

— 重置统计数据(一般在性能测试前后执行)
SELECT sys_stat_statements_reset();

mean_time 高说明单次执行慢,total_time 高说明调用频繁。优先优化 total_time 高的 SQL,对整体性能提升更大。

实时监控活跃会话

对于正在发生的性能问题,可以实时查看活跃会话:

— 当前正在执行的查询
SELECT pid, usename, application_name, client_addr,
now() – query_start AS duration, state, wait_event_type, wait_event, query
FROM sys_stat_activity
WHERE state = 'active'
AND query NOT LIKE '%sys_stat_activity%'
ORDER BY duration DESC;

— 等待锁的会话
SELECT pid, usename, now() – query_start AS duration, query
FROM sys_stat_activity
WHERE wait_event_type = 'Lock';

— 长时间未提交的事务
SELECT pid, usename, now() – xact_start AS xact_duration, query
FROM sys_stat_activity
WHERE state = 'idle in transaction'
AND now() – xact_start > INTERVAL '5 minutes';

把这些查询做成定时任务,每分钟采集一次,存入历史表,就能形成完整的性能监控体系。

四、查询重写技巧

有时候光靠加索引解决不了问题,需要改写 SQL。以下是一些实用的重写技巧。

避免 SELECT *

— 不好的写法
SELECT * FROM orders WHERE user_id = 1001;

— 好的写法
SELECT id, order_no, amount, created_at
FROM orders WHERE user_id = 1001;

SELECT * 会返回所有列,增加网络传输开销,而且无法使用覆盖索引。只查需要的字段,性能会更好。

优化分页查询

传统的 LIMIT/OFFSET 在大偏移量时性能很差:

— 不好的写法:偏移量越大越慢
SELECT * FROM orders
ORDER BY created_at DESC
LIMIT 20 OFFSET 100000;

— 改进方案1:基于游标的分页
SELECT * FROM orders
WHERE created_at < :last_created_at
OR (created_at = :last_created_at AND id < :last_id)
ORDER BY created_at DESC, id DESC
LIMIT 20;

— 改进方案2:延迟关联
SELECT o.* FROM orders o
INNER JOIN (
SELECT id FROM orders
ORDER BY created_at DESC
LIMIT 20 OFFSET 100000
) t ON o.id = t.id;

延迟关联的思路是先在索引上完成分页(只查主键),然后再回表查完整数据。这样避免了大量的回表操作。

用 EXISTS 替代 IN

— 不好的写法:子查询会返回所有结果
SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders WHERE amount > 1000);

— 好的写法:EXISTS 找到第一个匹配就返回
SELECT * FROM users u
WHERE EXISTS (
SELECT 1 FROM orders o
WHERE o.user_id = u.id AND o.amount > 1000
);

当子查询结果集很大时,EXISTS 的性能优势很明显。

避免在索引列上使用函数

— 不好的写法:函数导致索引失效
SELECT * FROM orders
WHERE DATE(created_at) = '2026-06-05';

— 好的写法:用范围查询
SELECT * FROM orders
WHERE created_at >= '2026-06-05'
AND created_at < '2026-06-06';

— 另一个例子
— 不好的写法
SELECT * FROM users
WHERE UPPER(username) = 'ADMIN';

— 好的写法:创建函数索引
CREATE INDEX idx_users_username_upper ON users(UPPER(username));

— 或者在应用层处理好大小写
SELECT * FROM users
WHERE username = 'admin';

在索引列上使用函数会让优化器无法使用索引,除非创建对应的函数索引。

合理使用 UNION vs UNION ALL

— UNION 会去重,需要额外的排序操作
SELECT user_id FROM orders_2025
UNION
SELECT user_id FROM orders_2026;

— 如果确定没有重复,用 UNION ALL 更快
SELECT user_id FROM orders_2025
UNION ALL
SELECT user_id FROM orders_2026;

UNION ALL 不做去重,性能比 UNION 好。只有在确实需要去重时才用 UNION。

批量操作代替逐条处理

— 不好的写法:在应用层循环插入
for order in orders:
INSERT INTO orders VALUES (...);

— 好的写法:批量插入
INSERT INTO orders (user_id, amount, created_at) VALUES
(1, 100, '2026-06-05'),
(2, 200, '2026-06-05'),
(3, 300, '2026-06-05');

— 或者用 COPY 命令(最快)
COPY orders FROM '/data/orders.csv' WITH (FORMAT csv);

批量操作能大幅减少网络往返和事务开销。百万级数据导入,COPY 比逐条 INSERT 快几十倍。

五、参数调优实战

KES 有很多配置参数会影响性能,但不是所有参数都要改。这里挑几个最关键的说。

内存相关参数

shared_buffers

共享缓冲区大小,建议设为物理内存的 25%:

— 32GB 内存的服务器
shared_buffers = 8GB

这个参数改完要重启。设太大反而不好,因为操作系统也需要缓存。

work_mem

每个排序或哈希操作使用的内存:

— 根据并发情况调整
work_mem = 64MB

注意 work_mem 是每个操作各自分配的。如果有 100 个并发查询都在做排序,可能会用到 6.4GB 内存。所以并发高的话不要设太猛。

maintenance_work_mem

VACUUM、CREATE INDEX 等维护操作使用的内存:

maintenance_work_mem = 512MB

这个可以适当设大一点,加快维护操作的速度。

effective_cache_size

优化器假设的可用缓存大小(包括 shared_buffers 和操作系统缓存):

— 一般设为物理内存的 50%-75%
effective_cache_size = 24GB — 32GB 内存的服务器

这个参数不影响实际内存分配,只是给优化器提供参考。设得太小会导致优化器倾向于选择 Seq Scan。

并发相关参数

max_connections

最大连接数:

max_connections = 200

不要盲目加大这个参数。每个连接都会占用内存,连接太多会导致内存不足。更好的做法是在应用层用连接池,控制并发连接数。

连接池推荐

推荐使用 PgBouncer 或 Kingbase Proxy 做连接池:

# pgbouncer.ini 配置
[databases]
testdb = host=192.168.1.100 port=54321 dbname=testdb

[pgbouncer]
listen_port = 6432
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20

应用连 PgBouncer,PgBouncer 复用后端连接,能有效控制并发。

WAL 和检查点参数

wal_buffers

WAL 缓冲区大小:

wal_buffers = 64MB

写入频繁的系统可以适当加大这个值。

checkpoint_completion_target

检查点完成目标:

checkpoint_completion_target = 0.9

这个值越大,检查点分散得越均匀,IO 波动越小。默认 0.5 意味着检查点在 50% 的时间内完成,改成 0.9 意味着在 90% 的时间内完成,更平滑。

min_wal_size / max_wal_size

WAL 文件大小范围:

min_wal_size = 1GB
max_wal_size = 4GB

写入量大的系统可以加大这些值,减少 WAL 文件的频繁切换。

查询优化器参数

random_page_cost

随机 IO 的成本估算:

— SSD 环境下可以降低这个值
random_page_cost = 1.1

— 传统机械硬盘
random_page_cost = 4.0

SSD 的随机读性能比机械硬盘好很多,降低这个值可以让优化器更倾向于使用索引扫描。

effective_io_concurrency

并发 IO 数量:

— SSD 可以设高一些
effective_io_concurrency = 200

— 机械硬盘
effective_io_concurrency = 2

这个参数影响预读和并行 IO 的行为。

六、实战案例分析

最后分享几个真实的性能优化案例。

案例一:电商订单查询优化

问题描述

某电商平台的订单列表查询很慢,高峰期响应时间超过 5 秒:

SELECT o.id, o.order_no, o.amount, o.status,
u.username, u.phone
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.user_id = 1001
AND o.status IN (1, 2, 3)
ORDER BY o.created_at DESC
LIMIT 20 OFFSET 0;

排查过程

Sort (cost=1234.56..1234.67 rows=100 width=128)
Sort Key: o.created_at DESC
-> Nested Loop (cost=0.86..1230.45 rows=100 width=128)
-> Index Scan using idx_orders_user_id on orders o
Index Cond: (user_id = 1001)
Filter: (status IN (1, 2, 3))
-> Index Scan using idx_users_pkey on users u
Index Cond: (id = o.user_id)

发现虽然用了索引,但还是做了 Sort 操作,而且 Nested Loop 的效率不高。

SELECT indexname, indexdef
FROM sys_indexes
WHERE tablename = 'orders';

— 发现只有 idx_orders_user_id(user_id)

优化方案

创建复合索引,覆盖查询条件和排序字段:

CREATE INDEX idx_orders_user_status_created
ON orders(user_id, status, created_at DESC);

同时改写 SQL,利用覆盖索引:

— 先查主键,再回表
SELECT o.id, o.order_no, o.amount, o.status,
u.username, u.phone
FROM (
SELECT id, user_id, order_no, amount, status, created_at
FROM orders
WHERE user_id = 1001
AND status IN (1, 2, 3)
ORDER BY created_at DESC
LIMIT 20
) o
JOIN users u ON o.user_id = u.id;

优化效果

• 优化前:平均响应时间 5.2 秒
• 优化后:平均响应时间 0.08 秒
• 提升:65 倍

关键在于复合索引消除了 Sort 操作,延迟关联减少了回表次数。

案例二:报表统计查询优化

问题描述

一个月度销售报表查询要跑 30 多秒:

SELECT
date_trunc('month', o.created_at) AS month,
p.category,
count(*) AS order_count,
sum(o.amount) AS total_amount,
avg(o.amount) AS avg_amount
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
WHERE o.created_at >= '2026-01-01'
AND o.created_at < '2027-01-01'
GROUP BY 1, 2
ORDER BY 1, 2;

排查过程

执行计划显示用了 Hash Join,但因为数据量大(一年约 500 万订单),哈希表的构建和探测都很慢。而且 GROUP BY 需要做大量的聚合计算。

优化方案

方案一:创建物化视图

CREATE MATERIALIZED VIEW mv_monthly_sales AS
SELECT
date_trunc('month', o.created_at) AS month,
p.category,
count(*) AS order_count,
sum(o.amount) AS total_amount,
avg(o.amount) AS avg_amount
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
GROUP BY 1, 2;

CREATE INDEX idx_mv_monthly_sales_month ON mv_monthly_sales(month);

— 每天凌晨刷新
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_monthly_sales;

方案二:预聚合表

— 创建日粒度聚合表
CREATE TABLE daily_sales_summary (
stat_date DATE,
category VARCHAR(50),
order_count INT,
total_amount NUMERIC(15,2),
avg_amount NUMERIC(10,2),
PRIMARY KEY (stat_date, category)
);

— 每天定时任务汇总前一天的数据
INSERT INTO daily_sales_summary
SELECT
date_trunc('day', o.created_at)::date AS stat_date,
p.category,
count(*),
sum(o.amount),
avg(o.amount)
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
WHERE date_trunc('day', o.created_at) = CURRENT_DATE – 1
GROUP BY 1, 2
ON CONFLICT (stat_date, category)
DO UPDATE SET
order_count = EXCLUDED.order_count,
total_amount = EXCLUDED.total_amount,
avg_amount = EXCLUDED.avg_amount;

— 查询时从聚合表查,按月汇总
SELECT
date_trunc('month', stat_date) AS month,
category,
sum(order_count) AS order_count,
sum(total_amount) AS total_amount,
avg(avg_amount) AS avg_amount
FROM daily_sales_summary
WHERE stat_date >= '2026-01-01'
AND stat_date < '2027-01-01'
GROUP BY 1, 2
ORDER BY 1, 2;

优化效果

• 物化视图方案:查询时间从 30 秒降到 0.2 秒
• 预聚合表方案:查询时间从 30 秒降到 0.5 秒,且支持实时更新

物化视图简单但实时性差,预聚合表复杂但灵活。根据业务需求选择。

案例三:死锁问题排查

问题描述

某个库存扣减功能频繁报死锁错误:

ERROR: deadlock detected
DETAIL: Process 12345 waits for ShareLock on transaction 67890; blocked by process 54321.

排查过程

解决方案

统一更新顺序,所有业务都按"先商品后库存"的顺序操作:

@Transactional
public void deductStock(Long productId, int quantity) {
// 第一步:更新商品信息(加锁)
productMapper.update(productId);

// 第二步:更新库存
stockMapper.deduct(productId, quantity);
}

另外,对于高并发场景,可以用 SKIP LOCKED 避免锁等待:

— 查询可用的库存记录,跳过已被锁定的
SELECT * FROM stock
WHERE product_id = 1001
AND quantity > 0
FOR UPDATE SKIP LOCKED
LIMIT 1;

预防措施

— kingbase.conf
lock_timeout = 5000 — 5 秒
deadlock_timeout = 1000 — 1 秒

写在最后

性能调优是个系统工程,需要从多个维度入手:执行计划分析帮你找到瓶颈,索引优化提供加速手段,查询重写改善逻辑效率,参数调优优化资源配置。这四个环节缺一不可。

KingbaseES 的性能优化思路跟其他关系型数据库大同小异,关键是要掌握方法论。遇到问题不要慌,按照"监控发现 → 定位瓶颈 → 分析原因 → 实施优化 → 验证效果"的流程一步步来,大部分性能问题都能解决。

最后提醒一点:不要过早优化。先把功能做对,再做性能测试,找出真正的瓶颈再优化。很多时候你以为的瓶颈其实不是瓶颈,盲目优化反而浪费时间。用数据说话,用执行计划说话,这才是性能调优的正确姿势。

希望这篇文章能帮你建立起系统的性能优化思维。遇到问题多看看执行计划,多试试不同的方案,慢慢就能写出高效的 SQL 了。共勉。