KingbaseES SQL 调优六步法：定位、诊断、下药、验证，一步都别省

前言

咱们从一个挺常见的场面说起。

页面上一条查询点了执行，进度圈转啊转。去后台一查，好家伙，这条 SQL 已经跑了半分钟。于是加索引，试了，没反应；把数据库重启一遍，还是老样子。一下午就这么耗进去了。

讲真，这种时候缺的不是干劲，是路子。东试一下西试一下，运气好蒙对一回，多数时候纯属白费劲，倒霉的话还得拉上别的 SQL 陪葬。

后来翻金仓数据库的官方文档，才发现里面早就备了一套挺完整的 SQL 调优方法论。它不逼你背参数，路给你画好了：哪个语句值得优化、病根出在哪、该下什么药、药效到底行不行，一步一步来，拢共六步。这篇文章我就照着这六步往下捋，例子都给全了，复制过去自己就能跑。

一、动手前先想清楚：你要的"快"是哪一种

调优调优，图个啥？多数人脑子里的答案就一个字：快。可惜这个字，在这儿得掰成两半看。

半边叫响应时间——请求发出去，到结果回来，中间隔多久。报表、页面查询，大家等的就是它。另外半边叫吞吐量——同样一堆硬件，到底能同时伺候多少活儿。

文档里有个例子我印象很深，干脆拎出来单独说。假设手里这条 SQL 就是 30 秒。

机器宽裕的时候，图的是用户少等，那就开并行查询，多喊几个 worker 一起搬砖，30 秒有机会摁到 5 秒上下。可机器要是本来就快扛不住了咋办？这时候硬开并行，纯纯火上浇油。正确姿势反而是收着来：建索引、写 HINT，先把这条 SQL 的资源胃口降下来。

明白了吧，题是同一道，做法完全相反。差别不在 SQL 身上，在你这次到底图什么。所以动键盘之前，先把目标写下来——这回就是要让响应时间从 30 秒进到 5 秒以内，别的先不谈。

调优目标	关注点	适合的场景	典型手段
减少响应时间	单条语句多快出结果	交互式查询、报表导出，硬件有余量	并行查询、索引、HINT 干预计划
提高吞吐量	单位资源能处理多少请求	高并发、批量任务，硬件吃紧	索引、SQL 改写、物化视图、计划缓存

目标落到纸面上，才好谈怎么动手。下面要说的六个步骤，就是干这件事的。

二、为啥调优得讲方法论：优化器不是神

先补个背景知识，不然后面有些地方不好理解：SQL 这门语言，是非过程化的。啥意思？你写一句 select * from t where id = 1，只说了要啥，没说咋拿。咋拿这件事，数据库里的优化器包了。同一个查询，能走的路子经常不止一条：扫全表是一种，走索引是一种，多表连接时先连谁后连谁、用哈希还是用嵌套循环，又各自是不同的走法。反正条条路都能算出对的答案，最后优化器挑一条它觉得最快的。

挑路子这事，金仓数据库的优化器分两个阶段干。头一个阶段叫逻辑优化，按关系代数那套等价变换规则把你的语句改写一遍——子查询上拉、谓词下推、外连接消除，都发生在这会儿，改的原则是"算出来一样、干起来更省"。第二个阶段是物理优化，也就是常说的 CBO：把能走的物理路径都摆出来，一条一条算账，哪条便宜走哪条。账本的模样大概是这样：

总代价 = I/O 代价 + CPU 代价 + 通信代价（第三项只在并行时才有）

那算账的本钱从哪来？统计信息。优化器得靠它估一个数：过滤条件跑完，还剩几行。这个剩余比例，行话叫选择率。举个具体的：一张 10000 行的表，条件 id < 50，选择率 0.49%，估出来就是 49 行：

create table t_sel(id int, name text);
insert into t_sel values(generate_series(1,10000), random());
analyze t_sel;

explain select * from t_sel where id < 50;

                QUERY PLAN
---------------------------------------------------------
 Seq Scan on T_SEL (cost=0.00..189.00 rows=49 width=21)
   Filter: (ID < 50)

留意计划里那个 rows=49。这个数没人真去查过，是优化器拿统计信息估出来的。统计信息一旧、一歪，估出来的数跟着歪，路径选择也就跟着错。不少慢 SQL，根子其实埋在这儿。

再泼盆冷水：优化器不是全能选手。列跟列之间有没有关联，它算不太准；数据变得快，统计信息的更新也未必追得上。所以它挑的计划未必回回都是最好的，总有些时候得人出手。人出手不能瞎出手，得有章法——章法，就是下面这六步。

三、六大步骤，一步步走

第一步：先把病灶找出来（识别高负载语句）

系统一慢，最忌讳的就是一头扎进某条 SQL 的细节里出不来。先回答另一个问题：哪条 SQL 最值得花力气？

判据不复杂，谁吃资源最多，谁就是 TOP SQL。金仓数据库在这步给的工具不止一件。sys_stat_statements 是累积统计视图，能按总耗时、平均耗时、调用次数、IO 量这些维度给 SQL 排名，开销小，日常巡检基本都用它。kbbadger 走日志路线，离线啃日志文件、出图文报告，适合事后复盘。SYS_KWR 是自动负载信息库，周期性打快照，能回溯一段时间的整体负载。SQL_MONITOR 更细，盯单次执行，一条 SQL 的时间具体耗在哪个计划节点上，它能给你看清楚。

真到干活，这几个里头 sys_stat_statements 用着最顺手。想揪总耗时前 10 的语句，一句话的事：

SELECT queryid,
       calls,                                          -- 执行次数
       round(total_exec_time::numeric, 2) AS total_ms, -- 总耗时
       round(mean_exec_time::numeric,  2) AS avg_ms,   -- 平均耗时
       left(query, 60) AS sql_preview                  -- SQL 预览
FROM sys_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

把 ORDER BY 换个字段，看到的就是另一个世界了。按 mean_exec_time 排，出来的净是一跑就好几秒的大块头；按 calls 排，出来的就是反反复复被执行的小碎句。小碎句这种最容易漏——单次几十毫秒，谁瞧得上？可它一天被调几十万回，账一累计就吓人了。

走到这儿，手上应该攥着一两条嫌疑 SQL。我说的是"嫌疑"，定罪还早着呢。

第二步：给优化器喂准数（收集性能数据）

人抓到了，接着收集证据。证据里最要紧的一样，叫统计信息。文档在这一节把话说得很重，大意是：统计信息缺了、旧了、不准了，优化器就别指望给出什么好计划，后面干啥都白搭。

光有统计信息还不够。这条 SQL 碰到的那些表和视图，底细也得摸一摸：表多大？数据怎么分布？上面挂了哪些索引、什么类型？回头分析计划的时候，这些全是原材料。

收统计信息的命令，简单到有点不好意思写出来：

analyze;            -- 整库
analyze t_order;    -- 精确到单表

这步看着土，救场的时候可一点不含糊。有些慢查询邪门得很，怎么调都不见好，一次 analyze 下去，好了大半。至于统计信息旧没旧怎么看——把计划里的估算行数和实际行数搁一块儿对，对不上号，先怀疑它。这招第三步马上要用，先记着。

第三步：看执行计划，这步最见功夫

前两步都是铺垫，到这儿才算正式坐诊。要弄清一条 SQL 为啥慢，手里最管用的家伙什，就是执行计划。

文档里给的分析路数，我搬过来，掺几句自己的话。先拿到慢语句的计划，explain 或者真跑一遍的 explain analyze 都行，然后自上而下把层次理顺——注意执行是从下往上的，下层节点的输出喂给上层。这里有个关键动作：先看统计信息准不准，估算行数和实际行数差得离谱的话，先 analyze 相关表，回到开头重看一遍。统计没问题，再找计划里最耗时的节点，一条语句可能不止一个毛病，先治最疼的。最后分析那个耗时节点合不合理，具体盯五处：扫描方式能不能走索引、分区、物化视图；多表连接顺序对不对，过滤性强的表有没有先连；连接算法选没选对；行数估得准不准；内存够不够，有没有在写临时文件。

读计划的时候，有行信息得练成条件反射：

Index Scan using idx_stu on student
    (cost=0.14..8.15 rows=1 width=10)
    (actual time=0.074..0.074 rows=0 loops=1)

cost=0.14..8.15 是启动代价和总代价，注意这是估的。rows=1 是估算行数。actual time 那串和后面的 rows=0 是实际耗时和实际行数，explain analyze 才有。估算 rows 和实际 rows 隔多远，优化器就猜得有多离谱。

那毛病一般藏在哪？文档归纳了六类。按我碰到的频率说：缺访问结构排第一，没索引、没分区、没物化视图，最典型也最好治，补上就能翻几个数量级。其次是次优计划，属于优化器自己的局限，比如选择率很低的条件偏在大表上走了全表扫，或者连接顺序不理想。第三是统计信息陈旧，DML 猛灌数据统计没跟上，优化器拿着旧地图找新路。然后是写法问题：漏连接条件搞出笛卡尔积，该 UNION ALL 的写成 UNION，子查询跟着外层每行跑一遍。这种优化器救不了，它再聪明，也架不住你让它干无用功。最后两类，硬件真到顶了、架构真撑不住了，那属于扩硬件、上读写分离集群的范畴，不用在 SQL 层面死磕。

落到执行计划上，高频问题能归成五类。我做了张速查表，看计划的时候对号入座：

问题类型	执行计划里的信号	处理思路
统计信息不准确	估算 rows 与实际 rows 差距巨大，计划明显反常	analyze 相关表，重看计划
缺少索引	大表 Seq Scan + Filter，Rows Removed by Filter 上百万	在过滤/连接列上建合适类型的索引
连接方式不当	小结果集却上了 HashJoin，或该 Hash 的选了 NestLoop	HINT 干预连接方式与顺序
基数估算偏差	预估与实际行数差几个数量级（窗口函数、多列条件常见）	扩展统计信息、rows 类 HINT 更正
内存不足写临时文件	Sort Method: external merge Disk: xxx kB	调大 work_mem，或改写 SQL 减少排序量

挑两个最典型的展开讲讲。

案例一，统计信息不准，索引把优化器带沟里去了。 建张 10000 行的表，sno 从 1 递增，建好索引直接查：

create table student(sno int, sname text, ssex int);
create index idx_stu on student(sno);
insert into student select generate_series(1,10000), left(random()::text, 10), 1;

explain select * from student where sno > 2;

                      QUERY PLAN
---------------------------------------------------------
 Bitmap Heap Scan on student (cost=66.12..171.78 rows=3333 width=40)
   Recheck Cond: (sno > 2)
   -> Bitmap Index Scan on idx_stu (cost=0.00..65.28 rows=3333 width=0)
        Index Cond: (sno > 2)

计划以为只返回 3333 行，走了索引。可 sno 是递增的，sno > 2 实际命中 9998 行，99.98% 的数据都得拿。这种选择率走索引纯属给自己加戏：索引多扫一遍不说，还得回表捞 9998 行记录。

补一刀 analyze 再看：

analyze;
explain select * from student where sno > 2;

                  QUERY PLAN
-------------------------------------------------------------
 Seq Scan on student (cost=0.00..189.00 rows=9998 width=19)
   Filter: (sno > 2)

rows 立马修正成 9998，计划也换成了顺序扫描。一条 analyze，让计划从绕远路变成抄近道。

案例二，内存不够，排序溢出到磁盘。 400 万行的表排个序：

create table big(id int);
insert into big values(generate_series(1, 4000000));
analyze big;

explain analyze select * from big order by id;

                     QUERY PLAN
---------------------------------------------------------------
 Sort (cost=636916.43..646915.50 rows=3999630 width=14)
      (actual time=4037.259..5010.737 rows=4000000 loops=1)
   Sort Key: id
   Sort Method: external merge Disk: 99648kB
   -> Seq Scan on big (...)
 Execution Time: 5611.819 ms

盯住 Sort Method: external merge Disk: 99648kB 这行——排序内存（work_mem）装不下了，溢出去写磁盘临时文件。一个排序从内存活儿变成磁盘活儿，5.6 秒就这么耗掉了。把 work_mem 调大重跑：

set work_mem = 640000;   -- 单位 kB

explain analyze select * from big order by id;

                     QUERY PLAN
---------------------------------------------------------------
 Sort (...)
   Sort Key: id
   Sort Method: quicksort Memory: 110514kB
   -> Seq Scan on big (...)
 Execution Time: 945.714 ms

排序回到内存里做（quicksort Memory），5611ms 干到 945ms。不过这里得多嘴一句：work_mem 不是给了就用一份，一个会话里可能同时跑好几个排序、哈希操作，每个都按这个值来，调之前先掂量并发量，别一刀切拉满。

再送个小技巧。想跑 explain analyze 又怕 DML 语句动了数据，套个事务回滚就完事：

begin;
explain analyze update student set ssex = 0 where sname = 'ada';
rollback;

第四步：想清楚改动的影响面

诊断完别急着开药，先问自己一句：这个改动会影响谁？

文档里有个反例我一直记着。明明只是一条 SQL 没走上索引，有人图省事，直接把库级参数 enable_seqscan 改成 off，全局打压顺序扫描。那条 SQL 说不定真好了，可全库几百条本来就该全表扫的语句全被殃及，整体性能反而掉。而且这类开关并不是彻底禁用，优化器只是把对应特性的代价加到极大，真没别的路可走时它照样选。

所以我给自己定的规矩是：语句级能解决的事，不碰数据库级配置。单条语句的毛病，用 HINT、改写 SQL、会话级参数；一批语句的共同问题，才轮到调参数、补索引这种影响面大的动作。

HINT 干的就是语句级干预这个活。它藏在 /*+ ... */ 形式的注释里，只对写它的那条 SQL 生效，指哪打哪，不惊动邻居：

-- 先在 kingbase.conf 里配置 enable_hint = on
-- 指定走索引扫描
explain select /*+ IndexScan(t1 idx_t1) */ * from t1 where name like 'test99%';

-- 指定连接顺序和连接方式
explain select /*+ Leading((t2 t1)) NestLoop(t1 t2) */ ...

金仓数据库的 HINT 挺全乎。Scan 类有 SeqScan、IndexScan、BitmapScan 加一堆 No 系列；Join 类有 NestLoop、HashJoin、MergeJoin；管连接顺序的 Leading、Ordered；管行数更正的 rows；还有 Parallel，甚至能在 HINT 里临时设 GUC 参数。这里不铺开了，记住它的定位就够：语句级干预执行计划，首选它。

第五步：开药方

病根清楚了，影响面划定了，剩下就是开药。金仓数据库给的优化手段有十来种：索引、HINT、调参数、并行、Query Mapping、物化视图、分区表计划优化、逻辑优化规则、SQL 优化建议、函数结果集缓存。不用都背，实战里高频的就几样，挨个配个场景。

先说索引，性价比之王。1000 万行的表做前缀模糊查询：

create table t1(id int, name text);
insert into t1 select (random()*100)::integer, 'test' || id
    from generate_series(1, 10000000) as id;

set max_parallel_workers_per_gather = 0;  -- 关掉并行，排除干扰
explain analyze select * from t1 where name like 'test99%';

                        QUERY PLAN
--------------------------------------------------------------------
 Seq Scan on t1 (cost=0.00..179053.25 rows=101009 width=15)
      (actual time=2.629..3221.751 rows=111111 loops=1)
   Filter: (name ~~ 'test99%'::text)
   Rows Removed by Filter: 9888889
 Execution time: 3227.571 ms

全表扫，988 万行被过滤掉，3.2 秒。建个支持模式匹配的索引再跑：

create index idx_t1 on t1(name text_pattern_ops);
analyze;

explain analyze select * from t1 where name like 'test99%';

                        QUERY PLAN
--------------------------------------------------------------------
 Bitmap Heap Scan on t1 (cost=2787.80..58200.43 rows=101010 width=15)
      (actual time=46.336..110.928 rows=111111 loops=1)
   -> Bitmap Index Scan on idx_t1 (cost=0.00..2762.55 rows=108611 width=0)
        Index Cond: ((name ~>=~ 'test99'::text) AND (name ~<~ 'test9:'::text))
 Execution time: 118.710 ms

3227ms 变 118ms，27 倍。文档里说"最佳访问结构能将性能提升几个数量级"，说的就是这种。不过索引也不是越多越好，函数列（除非建函数索引）、选择率差的列、常驻小表、超长字段，都不建议建，维护代价会把收益吃掉。拿不准的话，金仓数据库自带索引建议工具，sys_qualstats 配合 sys_hypo 插件，跑一句 select * from index_recommendation_by_qual;，它根据运行期的 where、join 统计，直接把 create index 的 DDL 建议端到你面前。

再说并行，典型的资源换时间，就是开头讲的那个场景：资源充足、目标是响应时间，用它。几个参数的关系先拎清：

max_parallel_workers_per_gather + max_parallel_maintenance_workers
    <= max_parallel_workers
    <= max_worker_processes   -- 默认 8，修改后要重启

要不要并行、开几个 worker，优化器会综合表的体量（min_parallel_table_scan_size，默认 8MB 起才可能触发）、并行启动成本（parallel_setup_cost 默认 1000）、元组传输成本（parallel_tuple_cost 默认 0.1）一起算。那怎么知道一条语句跑没跑并行？去计划里找 Gather 节点，旁边写着 Workers Planned: N，就是它了。

还有一路子一分钱资源不花——改写 SQL。有些语句慢，纯属写法作出来的：

-- 两个结果集本无重复，UNION 的去重是白干的
select * from t1 union select * from t2;      -- 排序去重，白慢
select * from t1 union all select * from t2;  -- 直出结果，快

-- 删全表数据
delete from t1;   -- 逐行标记删除，事后还得等 VACUUM 回收空间
truncate t1;      -- 立即释放

这儿再点一个阴沟里翻船的高发点：隐式类型转换把索引弄歇菜。字段 id 明明是 varchar，条件偏写成 where id = 1000，数据库在背后偷偷做了类型转换，索引当场撂挑子。条件老老实实写成 id = '1000'，世界清静。

压轴说个我私心很喜欢的功能——SQL 优化建议，说白了就是让数据库自己给自己开药。sys_sqltune 插件装好，嫌疑 SQL 扔进去，它分析完直接吐一份报告：

-- kingbase.conf: shared_preload_libraries = 'plsql, sys_stat_statements, sys_sqltune'
create extension sys_sqltune;

select PERF.QUICK_TUNE_BY_SQL(
    'select * from t where id = 1 union select * from t where id = 1');

报告内容比我预想的全：统计信息要不要重收、索引建议（连 DDL、预期收益、优化前后计划对比都附上）、还有 SQL 改写建议——UNION 换 UNION ALL、DELETE 换 TRUNCATE、隐式类型转换的告警，一样不落。等于白捡了个初级 DBA 打下手。

第六步：复查，别嫌麻烦

药吃下去，总得回头看看疗效。手段上了线，原场景重新跑一遍，执行时间、IO、返回行数，挨个跟优化前对，当初定的目标实现了没，一目了然。

复查我常用两套动作：

-- 姿势一：explain analyze 直接对比执行时间
explain analyze select * from t1 where name like 'test99%';

-- 姿势二：清空统计后跑真实业务，看新的 TOP SQL 分布
select sys_stat_statements_reset();
-- ……跑一段业务……
-- 再查 sys_stat_statements，看这条 SQL 还在不在榜上、耗时降了多少

达标，收工，皆大欢喜。没达标呢？或者更尴尬——这条快了，别的 SQL 却被你拖下水了？那没什么好说的，退回第一步，从头再来。调优这活儿本质上就是转圈，六步是个环，不是一条道跑到黑。

对了，还有件事容易被忘掉：数据不是死的。量会涨，索引会膨胀，统计信息会过期，执行计划也就跟着挪窝。今天嗖嗖快的 SQL，半年后啥样谁也不敢打包票。所以复查这步，既是一轮的收尾，也算下一轮巡检的起跑线。

四、把六步串成一条线走一遍

每一步单拎出来都不难，难的是连成一套不假思索的动作。找个慢 SQL，从头到尾过一遍：

-- ① 识别高负载：sys_stat_statements 里总耗时第一的就是它
SELECT queryid, calls, round(total_exec_time::numeric,2) AS total_ms,
       left(query, 60) AS sql_preview
FROM sys_stat_statements
ORDER BY total_exec_time DESC LIMIT 5;

-- ② 收集数据：确认涉及的表和索引定义，顺手 analyze
analyze t1;

-- ③ 确定原因：explain analyze 一看——
--    Seq Scan + Filter，Rows Removed by Filter: 9888889，缺索引实锤
explain analyze select * from t1 where name like 'test99%';

-- ④ 定义范围：就这一条语句的事，语句级解决，不动全局参数

-- ⑤ 实施手段：建支持模式匹配的索引
create index idx_t1 on t1(name text_pattern_ops);
analyze;

-- ⑥ 评估效果：重跑对比，3227ms → 118ms，达标，收工
explain analyze select * from t1 where name like 'test99%';

回头看，从 30 秒到 5 秒这段路，没有哪一步靠的是某个参数的"神来之笔"。靠的是链路上每个环节都不糊弄：语句找对了，数据喂准了，计划读透了，范围卡住了，药下对了，疗效也验过了。少一环，这 5 秒都到不了。

最后

写到这儿差不多了，收尾前啰嗦几句我自己的体会。

目标没想清楚之前，别碰键盘——响应时间和吞吐量是两码事，同样 30 秒的 SQL，机器家底不一样，方子能开得完全相反。然后，永远让工具先说话：优化一条 SQL 从来不是难事，难的是知道该优化哪条，这事拍脑袋不靠谱。碰上邪门问题，先查统计信息，八成的"玄学"都栽在这儿，一次 analyze 说不定就省你半天工夫。改动范围能小则小，语句级摆得平的事，犯不着动全库配置；一轮不灵，退回第一步老老实实再走一遍就是。

经验这东西当然值钱，只是它能告诉你"这次大概是什么"。方法论值钱在另一个地方：它保证你哪回都不漏。六步练熟了，往后再撞上慢 SQL，你头一个打开的就不会是救火群，而是执行计划。