一、背景

之前用KES数据库做的项目上线之后，前期数据量不大，刚开始都还挺正常的。后面慢慢的随着业务跑起来了，数据量也蹭蹭往上涨起来，这个时候问题就来了，SQL执行慢得离谱。然后我去查看了执行的日志，一看是全表扫描。几张大表，几百万行数据，没有索引，或者索引根本没用上才导致的这么慢。那时候我就知道了，数据库自己的查询优化器肯定不够用，我们后面还是需要手动调优来解决的。从那之后，我就开始学习研究KingbaseES的很多SQL优化方法。今天这篇文章，就是把我前前后后学习的踩坑经验总结出来，给友友们分享一下，省的跟我踩一样的坑。

结论博主先丢这：KingbaseES的手动SQL优化，核心就是建对索引、调大work_mem、用好分区表、上物化视图。接下来博主一个一个讲，看完应该就可以理解了。

---

二、索引

2.1 我对索引的理解

很多人觉得索引就是建了就完事了，但是这样是错的。我刚开始的时候也这么想，觉得哪个字段经常出现在WHERE条件里，就给它建个索引就行了。结果建了一堆索引，查询更慢了。其实索引不是越多越好，主要是要建对。KingbaseES的索引机制和我之前用的Oracle有一些相似的地方，KES也有自己的特点。比如支持B-tree、Hash、GIN、GiST等多种索引的类型，不同的场景要用不同的索引，效率最高化。

2.2 什么时候该建索引

我在项目里总结了几条经验

第一，WHERE条件里经常出现的字段，必须建。 这个没啥好说的，但要注意，如果这个字段的区分度很低（比如性别字段，就男和女两个值），建了索引效果也不大，优化器可能直接就忽略了。

第二，JOIN关联的字段，必须建。 这个我踩过坑。我们有一张sys_order表和一张sys_user表，经常要关联查询，但我之前只在sys_order的order_id上建了主键索引，没在user_id上建。结果每次JOIN都是嵌套循环，慢得要死。后来在sys_order.user_id上加了个普通索引，查询速度直接从十几秒降到了零点几秒。

第三，ORDER BY和GROUP BY的字段，建议建。 如果你的查询经常要排序或者分组，给这些字段建个索引，可以避免排序操作，这个提升是很明显的。

2.3 索引的类型怎么选

这个是很多人容易忽略的。KingbaseES里最常用的就是B-tree索引，大部分场景都够用了。但有些特殊场景，你得用别的。

举个例子，我们项目里有个sys_documents表，存的是文档内容，有个字段是全文检索用的。如果用B-tree索引，根本没法做模糊匹配。后来我换成了GIN索引，配合全文检索的语法，效果立竿见影。

还有一种情况，如果你的字段是JSONB类型的，存储的是结构化的JSON数据，那GIN索引也是必须的。不然你每次查JSON里面的某个key，都是全表扫描。

我的建议是：默认用B-tree，特殊场景再考虑GIN或者GiST。不要一上来就用GIN，因为GIN索引的维护成本比B-tree高不少。

2.4 实战示例

我们项目里有一张sys_transaction表，大概有2000万行数据，结构大概是这样的：

CREATE TABLE sys_transaction (
    trans_id      BIGINT PRIMARY KEY,
    user_id       BIGINT NOT NULL,
    trans_type    VARCHAR(20),
    amount        DECIMAL(18,2),
    trans_time    TIMESTAMP,
    status        VARCHAR(10)
);

之前有个报表查询，要查某个用户在某个时间段内的交易记录：

SELECT * FROM sys_transaction 
WHERE user_id = 10086 
  AND trans_time BETWEEN '2025-01-01' AND '2025-06-23'
  AND status = 'SUCCESS';

这个查询之前要跑8秒多。我看了执行计划，发现是全表扫描。

我的优化步骤是这样的：

第一步，建复合索引。

CREATE INDEX idx_sys_trans_user_time_status 
ON sys_transaction(user_id, trans_time, status);

注意这里字段的顺序。我把user_id放在最前面，因为它的区分度最高，而且是等值查询。trans_time放第二，因为是范围查询。status放最后，因为它也是等值查询但区分度低。

这个顺序很重要！ 很多人建复合索引不注意顺序，结果索引根本用不上。

建完索引之后，同样的查询，执行时间降到了0.3秒。从8秒到0.3秒，你说这个索引值不值？

第二步，我还做了一个优化。 我发现这个查询其实经常只需要前100条，所以我加了个LIMIT：

SELECT * FROM sys_transaction 
WHERE user_id = 10086 
  AND trans_time BETWEEN '2025-01-01' AND '2025-06-23'
  AND status = 'SUCCESS'
LIMIT 100;

加上LIMIT之后，因为有索引，数据库可以快速定位到前100条就停止扫描了，执行时间进一步降到了0.05秒以内。

2.5 索引的坑

再说几个我踩过的坑：

坑1：索引建了但没用上。 这种情况特别常见。原因一般是统计信息不准，或者查询写法导致优化器认为全表扫描更快。解决办法是执行ANALYZE sys_transaction;来更新统计信息。

坑2：索引太多导致写操作变慢。 我们有张表我之前建了六七个索引，结果INSERT和UPDATE慢得要命。后来我把不常用的索引删了，写操作速度恢复了。所以索引要定期清理，不用的就删掉。

坑3：唯一索引和普通索引搞混。 如果你建了唯一索引，但实际上数据里有重复值，建索引的时候就会报错。所以建之前一定要先查一下有没有重复数据。

---

三、work_mem

3.1 work_mem是啥

如果说索引是空间换时间，那work_mem就是内存换时间。

work_mem这个参数控制的是每个查询操作能使用的内存大小。排序、哈希连接、位图扫描这些操作，都需要内存。如果work_mem太小，内存不够用，数据库就会把数据写到磁盘上（也就是所谓的落盘），速度会慢非常多。

我之前一直没怎么关注这个参数，觉得默认的应该够用了。直到有一天，我看到一个查询的执行计划里出现了Disk: 256000kB这样的字样，我才意识到问题大了。

这意味着这个查询在排序或者哈希的时候，内存不够用，数据写到磁盘上去了。256MB的数据写到磁盘上再读回来，能不慢吗？

3.2 怎么调

KingbaseES的work_mem是可以在多个级别设置的：

• 全局级别：修改kingbase.conf文件
• 会话级别：当前连接单独设置
• 操作级别：单条SQL单独设置

我的建议是：不要一上来就改全局配置，先在会话级别试。因为work_mem是每个操作都会用的，如果你设得太大，并发高的时候，每个连接都占用大量内存，服务器内存很快就不够用了。

比如你设了work_mem=256MB，有100个连接同时在做排序，那光work_mem就要占25GB内存。你服务器有那么多内存吗？

所以博主建议，要先分析哪些SQL确实需要更多内存，然后针对这些SQL单独设置。

3.3 实战示例

还是用刚才那个sys_transaction表。我们有个统计查询，要按用户分组统计交易金额：

SELECT user_id, SUM(amount) as total_amount, COUNT(*) as trans_count
FROM sys_transaction
WHERE trans_time >= '2025-01-01'
GROUP BY user_id
HAVING SUM(amount) > 10000
ORDER BY total_amount DESC;

这个查询涉及到GROUP BY和ORDER BY，都是需要内存的操作。

我先看了一下默认work_mem下的执行计划：

GroupAggregate  (cost=158234.56..162345.78 rows=5000 width=32)
  Group Key: user_id
  ->  Sort  (cost=158234.56..159234.56 rows=400000 width=24)
        Sort Key: user_id
        Sort Method: external merge  Disk: 256000kB
        ->  Seq Scan on sys_transaction  (cost=0.00..98234.56 rows=400000 width=24)
              Filter: (trans_time >= '2025-01-01'::timestamp without time zone)

看到了吧，Sort Method: external merge Disk: 256000kB。这就是内存不够，数据写磁盘了。

我在会话级别把work_mem调大试试：

SET work_mem = '256MB';

再执行同样的查询，执行计划变成了：

GroupAggregate  (cost=128234.56..132345.78 rows=5000 width=32)
  Group Key: user_id
  ->  Sort  (cost=128234.56..129234.56 rows=400000 width=24)
        Sort Key: user_id
        Sort Method: quicksort  Memory: 45000kB
        ->  Seq Scan on sys_transaction  (cost=0.00..98234.56 rows=400000 width=24)
              Filter: (trans_time >= '2025-01-01'::timestamp without time zone)

Sort Method变成了quicksort，内存占用45MB，没有落盘了！

执行时间从原来的12秒降到了3秒。

3.4 我的调参经验

说实话，work_mem这个参数我调了好多次才找到一个比较合理的值。

如果你的查询主要是简单的单表查询，work_mem设个16MB~32MB就够了，涉及到GROUP BY、ORDER BY、DISTINCT这些操作，建议64MB起步，要是大表关联+排序，可以考虑128MB~256MB，但是有一点，就是千万不要超过512MB，除非你确定这个查询真的需要那么多内存，而且并发很低

还有一个技巧：如果你只是偶尔需要大内存，可以在单条SQL里用SET命令临时调整，比如：

SET LOCAL work_mem = '256MB';
SELECT ... 你的复杂查询 ...;

这样只有这条SQL会用到大内存，不影响其他连接。

---

四、分区表

4.1 为什么要用分区表

我们有张sys_log表，记录系统操作日志，数据量特别大，上线半年就到了5000万行。这种表你不做分区，查询起来就是灾难。我之前试过各种办法，建索引、调参数，效果都有限。因为数据量太大了，索引再好，扫描的范围也太广。后来我跟一个做数据库内核的朋友聊，他说了一句话让我醍醐灌顶：数据量到了一定程度，单表优化就是在挣扎，必须分治。分区表的核心思想就是把一张大表切成很多小张，查询的时候只扫描相关的分区，不相关的分区直接跳过。这叫分区裁剪，效果是立竿见影的。

4.2 KingbaseES的分区方式

KingbaseES支持好几种分区方式

范围分区：按值的范围分，比如按时间分
列表分区：按值的列表分，比如按地区分
哈希分区：按哈希值分，比较均匀但不适合范围查询

我在项目里用得最多的是范围分区，尤其是按时间分区。 因为我们的业务数据基本都有时间字段，按时间分区是很正常的。

4.3 实战示例

还是那个sys_log表，结构大概是这样：

CREATE TABLE sys_log (
    log_id      BIGSERIAL,
    user_id     BIGINT,
    action      VARCHAR(50),
    log_detail  TEXT,
    log_time    TIMESTAMP NOT NULL,
    level       VARCHAR(10)
);

我把它改成按月范围分区：

CREATE TABLE sys_log (
    log_id      BIGSERIAL,
    user_id     BIGINT,
    action      VARCHAR(50),
    log_detail  TEXT,
    log_time    TIMESTAMP NOT NULL,
    level       VARCHAR(10)
) PARTITION BY RANGE (log_time);

-- 建分区
CREATE TABLE sys_log_202501 PARTITION OF sys_log
    FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');

CREATE TABLE sys_log_202502 PARTITION OF sys_log
    FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');

CREATE TABLE sys_log_202503 PARTITION OF sys_log
    FOR VALUES FROM ('2025-03-01') TO ('2025-04-01');

-- ... 以此类推

-- 建一个默认分区，防止插入的数据没有匹配的分区
CREATE TABLE sys_log_default PARTITION OF sys_log DEFAULT;

分区建好之后，效果有多明显呢？

假设我要查2025年3月份的日志：

SELECT * FROM sys_log 
WHERE log_time >= '2025-03-01' AND log_time < '2025-04-01'
  AND level = 'ERROR';

没分区之前，这个查询要扫描5000万行。分区之后，数据库只会扫描sys_log_202503这个分区，大概只有400万行。扫描量直接降了一个数量级。

执行时间从原来的15秒降到了1秒以内。

4.4 分区表的注意事项

第一，分区键一定要选好。 最好是WHERE条件里经常出现的字段，而且最好能让数据均匀分布。如果你按用户ID分区，但用户ID分布不均匀，有的分区特别大，有的特别小，那效果就打折扣了。

第二，分区之后，索引要在每个分区上单独建。 这个很多人会忘。你在父表上建索引是没用的，必须在每个分区上都建。

-- 在每个分区上建索引
CREATE INDEX idx_sys_log_202501_time ON sys_log_202501(log_time);
CREATE INDEX idx_sys_log_202502_time ON sys_log_202502(log_time);
-- ...

当然，如果你不想每个分区都手动建，可以在建分区的时候就指定：

CREATE TABLE sys_log_202501 PARTITION OF sys_log
    FOR VALUES FROM ('2025-01-01') TO ('2025-02-01')
    CREATE INDEX idx_sys_log_202501_time ON sys_log_202501(log_time);

不过KingbaseES目前好像不支持在CREATE TABLE … PARTITION OF里直接写CREATE INDEX，所以还是得手动建。这个稍微有点麻烦，但没办法。

第三，分区表的维护。 每个月要手动建新分区，或者写个脚本自动建。不然新数据插入的时候会报错，因为没有匹配的分区。

我在项目里写了个定时任务，每个月1号自动建下个月的分区：

DO $$
DECLARE
    next_month TEXT;
    start_date DATE;
    end_date DATE;
BEGIN
    start_date := DATE_TRUNC('month', CURRENT_DATE) + INTERVAL '1 month';
    end_date := start_date + INTERVAL '1 month';
    next_month := TO_CHAR(start_date, 'YYYYMM');
    
    EXECUTE format('
        CREATE TABLE IF NOT EXISTS sys_log_%I PARTITION OF sys_log
        FOR VALUES FROM (%L) TO (%L)',
        next_month, start_date, end_date);
END $$;

这个脚本我每个月跑一次，很省心。

---

五、物化视图：用空间换时间的另一种玩法

5.1 物化视图是啥

如果说分区表是把大表切小，那物化视图就是把常用的查询结果提前算好存起来。

普通视图是不存数据的，每次查询都要重新算。物化视图不一样，它会把查询结果物理化地存到磁盘上，查询的时候直接读结果，不用重新计算。

这个东西特别适合那些计算量大、但数据不需要实时更新的场景。

5.2 什么场景适合用物化视图

我在项目里用物化视图最多的场景是报表。

比如我们有个日报统计，要算每个用户每天的交易总额、交易笔数、平均金额。这个查询涉及到GROUP BY，数据量又大，每次跑都要好几秒。

但日报嘛，数据又不需要实时的，每天凌晨算一次就够了。这种场景就特别适合物化视图。

5.3 实战示例

先建物化视图：

CREATE MATERIALIZED VIEW sys_daily_user_stat AS
SELECT 
    user_id,
    DATE(trans_time) as stat_date,
    COUNT(*) as trans_count,
    SUM(amount) as total_amount,
    AVG(amount) as avg_amount
FROM sys_transaction
WHERE status = 'SUCCESS'
GROUP BY user_id, DATE(trans_time);

-- 建索引，不然查询还是慢
CREATE INDEX idx_sys_daily_stat_user_date 
ON sys_daily_user_stat(user_id, stat_date);

建好之后，查询就变成了：

SELECT * FROM sys_daily_user_stat 
WHERE user_id = 10086 
  AND stat_date BETWEEN '2025-01-01' AND '2025-06-23';

这个查询原来要跑8秒，现在0.01秒。因为数据已经提前算好了，直接读就行。

5.4 物化视图的刷新

物化视图有个问题：数据不会自动更新。 你原表的数据变了，物化视图里的数据还是旧的。

所以你得定期刷新。KingbaseES支持两种刷新方式：

完全刷新： 把整个物化视图删了重新算。

REFRESH MATERIALIZED VIEW sys_daily_user_stat;

并发刷新： 不锁表，可以边刷新边查询（但刷新期间查询到的可能是旧数据）。

REFRESH MATERIALIZED VIEW CONCURRENTLY sys_daily_user_stat;

我在项目里是每天凌晨3点跑一次完全刷新：

-- 写到定时任务里
REFRESH MATERIALIZED VIEW sys_daily_user_stat;

5.5 物化视图的坑

如果你的物化视图查询很复杂，刷新可能要很长时间。我之前有个物化视图，刷新一次要20分钟，导致凌晨3点开始刷新，3点20才刷新完，期间报表系统读到的都是前一天的数据。后来我优化了物化视图的查询逻辑，把刷新时间降到了2分钟。

物化视图占用空间。这个是肯定的，因为它要存一份数据。我那个sys_daily_user_stat大概占了2GB空间。所以你得评估一下，这个空间换来的速度提升值不值。

不要在物化视图上再套物化视图。我见过有人搞了好几层物化视图，结果刷新的时候层层依赖，一个刷新失败全部报废。老老实实一层就够了。

---

六、其他一些零零散散但很有用的技巧

除了上面的一些，我在实际项目里还积累了一些小技巧，一起说说。

6.1 EXPLAIN ANALYZE是你最好的朋友

不管优化什么SQL，第一步永远是看执行计划。

EXPLAIN ANALYZE 
SELECT * FROM sys_transaction 
WHERE user_id = 10086 AND trans_time > '2025-01-01';

这个命令可以让我们看出来，数据库实际是怎么执行这条SQL的，每个步骤花了多少时间，扫了多少行，用没用上索引。

我的习惯是，任何慢SQL，先跑EXPLAIN ANALYZE，看完再动手改。 不看执行计划就瞎改，十有八九改不到点子上。

6.2 避免在WHERE里对字段做函数操作

这个是经典错误了。比如：

-- 坏写法
SELECT * FROM sys_transaction 
WHERE DATE(trans_time) = '2025-06-23';

这样写的话，trans_time上的索引就用不上了，因为数据库要对每一行都执行DATE()函数。

应该改成：

-- 好写法
SELECT * FROM sys_transaction 
WHERE trans_time >= '2025-06-23' 
  AND trans_time < '2025-06-24';

这样索引就能正常用了。

6.3 用CTE代替临时表

KingbaseES支持WITH语句（也就是CTE，公共表表达式）。有些场景下，用CTE比建临时表更简洁，而且执行计划有时候更优。

比如：

WITH user_trans AS (
    SELECT user_id, SUM(amount) as total 
    FROM sys_transaction 
    GROUP BY user_id
)
SELECT * FROM user_trans WHERE total > 10000;

这个比先建临时表再查询要简洁得多。

6.4 批量操作用COPY不用INSERT

如果你要导入大量数据，千万别用INSERT一条一条插。用COPY命令，速度能快几十倍。

COPY sys_log FROM '/data/sys_log_202506.csv' WITH CSV;

我之前有个数据迁移任务，用INSERT插了500万条数据，花了40分钟。后来改用COPY，2分钟搞定。

---

七、一个完整的优化案例

说了这么多，我用一个完整的案例把上面的东西串起来。

场景

我们有个sys_order表，800万行数据。业务需要一个查询，统计每个商户近30天的订单数和金额，按金额排序取前100。

原始SQL：

SELECT 
    merchant_id,
    COUNT(*) as order_count,
    SUM(amount) as total_amount
FROM sys_order
WHERE create_time >= CURRENT_DATE - INTERVAL '30 days'
  AND status = 'COMPLETED'
GROUP BY merchant_id
HAVING SUM(amount) > 1000
ORDER BY total_amount DESC
LIMIT 100;

这个查询原来要跑25秒，业务方天天催我优化。

优化过程

第一步：看执行计划。

Limit  (cost=456789.12..456789.37 rows=100 width=32)
  ->  Sort  (cost=456789.12..467890.12 rows=4444 width=32)
        Sort Key: (sum(amount)) DESC
        Sort Method: external merge  Disk: 512000kB
        ->  HashAggregate  (cost=445678.12..456789.12 rows=4444 width=32)
              Group Key: merchant_id
              ->  Seq Scan on sys_order  (cost=0.00..398765.12 rows=2345678 width=24)
                    Filter: ((create_time >= ...) AND (status = 'COMPLETED'::text))

全表扫描+外部排序落盘，典型的慢查询。

第二步：建分区表。

按create_time按月分区：

CREATE TABLE sys_order_new (
    LIKE sys_order INCLUDING ALL
) PARTITION BY RANGE (create_time);

-- 建最近6个月的分区
CREATE TABLE sys_order_202501 PARTITION OF sys_order_new
    FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');
-- ... 其他月份类似

-- 把数据导过去
INSERT INTO sys_order_new SELECT * FROM sys_order;

-- 改名
ALTER TABLE sys_order RENAME TO sys_order_old;
ALTER TABLE sys_order_new RENAME TO sys_order;

第三步：建索引。

CREATE INDEX idx_sys_order_merchant_time_status 
ON sys_order(merchant_id, create_time, status);

第四步：调work_mem。

SET work_mem = '128MB';

第五步：上物化视图（可选）。

因为这个查询每天都要跑好几次，我直接上了物化视图：

CREATE MATERIALIZED VIEW sys_merchant_daily_stat AS
SELECT 
    merchant_id,
    DATE(create_time) as stat_date,
    COUNT(*) as order_count,
    SUM(amount) as total_amount
FROM sys_order
WHERE status = 'COMPLETED'
GROUP BY merchant_id, DATE(create_time);

CREATE INDEX idx_sys_merchant_stat ON sys_merchant_daily_stat(merchant_id, stat_date);

然后定时刷新。

优化结果

优化阶段	执行时间
原始SQL	25秒
建分区+索引	4秒
调work_mem	2秒
上物化视图	0.02秒

从25秒到0.02秒，你说这优化值不值？

---

八、总结

说到底，SQL优化这件事没有什么的，就是理解数据库是怎么执行你的SQL的，然后针对性地消除瓶颈。索引消除扫描瓶颈，work_mem消除内存瓶颈，分区消除数据量瓶颈，物化视图消除计算瓶颈。

这些技术点你要是都掌握了，KingbaseES上百分之八九十的慢SQL你都能搞定。剩下那百分之十？说实话，有些SQL就是写得太烂了，优化到极限也就那样。这种时候你得跟业务方沟通，那就应该是他们给的需求有问题。

最后——同行者计划

无论您是金仓用户、客户，还是生态合作伙伴；无论您是技术岗还是业务岗，是管理者还是一线工程师——只要您对数据库市场有敏锐的洞察，身边有潜在的业务需求，您就是我们最珍视的同行者。

1. 为什么加入“同行者计划”？

最好的合作是“共赢”。只需提供真实线索，后续的跟进、转化由金仓团队接手。朋友们将获得从 “即时激励” 到 “长期权益” 的全方位回报。

2. 如何提供线索？

https://bbs.kingbase.com.cn/collectionForm/10013
一键填写表单即可，金仓将及时跟踪反馈。

一次线索推荐，可能促成一次系统的国产化替代；一条关键信息，可能为行业生态带来新的活力。