【金仓数据库征文】让一条慢查询快 1000 倍：KingbaseES V9 一次体系化 SQL 性能调优实战

这篇文章我用的是金仓自带的那些工具，像sys_stat_statements、sys_hypo、sys_sqltune、sys_kwr还有sys_buffercache这些。我围着同一条慢查询，完整跑了一遍从「定位」到「诊断」，再到「评估」、「落地」、「调参」最后「佐证」的整个过程。并且把每一步的原理都讲了讲。文里面的那些数值还有执行计划，都是真实环境里面测出来的，你们自己也可以照着做一遍。

一、引言：一条把系统卡住的订单查询

在做国产化替换的项目的时候，很多团队把库从 Oracle 换到金仓。换完之后的第一反应往往是，怎么有些查询变慢了呢。去年我们那边把一套订单系统迁到了金仓 KingbaseES V9。功能测试的时候其实挺顺利的。但是一上准生产环境，问题就来了。客服后台那个按用户查订单的页面开始转圈。这条查询在 Oracle 上可能几十毫秒就返回了。现在呢，动不动就要两三秒。到了高峰期，直接就把连接池给打满了。

业务那边的人第一反应就是，金仓不行。但我作为 DBA，我不太信这个。我觉得其实是另外一种情况。不是数据库本身不行，而是这套库搬到金仓上之后，根本就没做过一次正经的调优。迁移工具把表结构和数据搬过来了。但是 Oracle 上那些年攒下来的索引策略还有内存参数的经验，它是搬不过来的。刚迁过去慢是很正常的情况。但是慢下去一直不管，那就是你的问题了。

慢的那条 SQL 其实长下面这样。就是那种很典型的按用户加上状态去查订单。基本上所有 C 端的订单系统都绕不开这条查询：

SELECT*FROMt_orderWHEREuser_id=?ANDstatus=?;

这篇文章我不讲那些空泛的东西，什么加个索引、调个参数之类的。我是想带你们用金仓自己带的一整套调优工具，把这条查询的单次执行耗时往下压。大概能压掉 1000 倍那么多。从百毫秒级别一直打到亚毫秒级别。而且每一步都是有依据的，每个数字你们都能自己复现。看完你能得到的，绝对不只是“加了个索引就快了”这么个结论。而是一套能直接拿到你自己系统里面用的方法论：

• 怎么用sys_stat_statements从几百上千条 SQL 里面，准确找出最该优化的那一条；
• 怎么一个字段一个字段地去读EXPLAIN (ANALYZE, BUFFERS)，去判断慢到底是慢在了哪里；
• 怎么用sys_hypo去做假设索引。不建真的索引就能提前看优化效果。这样就不用在大表上瞎折腾了；
• 怎么把shared_buffers、work_mem、effective_cache_size这几个参数调到合适的位置。既能提速，又不会把实例搞得 OOM；
• 怎么用sys_kwr（这个是金仓版的 AWR）出一份负载报告。去证明改善的是整体情况，而不是单单一条 SQL 变快了。

这一整套原生的工具链，其实也就是金仓的一个好处。从慢 SQL 的采集、假设索引一直到出负载报告，很多调优的能力它自己就带了，装上就能用。不需要你再去额外引入第三方的组件。这也是写这篇文章最主要的一个目的。

二、环境与测试数据准备：先造一张百万级的大表出来

调优这事儿，必须得有一个可以复现的负载环境。为了不影响现有的业务库，我先建了一个单独的演示库perf_demo。

-- 用 system 用户连接实例-- ./ksql -U system -p 54321 -d testCREATEDATABASEperf_demo;\c perf_demo

接着就是造一张贴近真实业务的订单大表。在字段的选择上，user_id我设成了高基数列，大概有十万级别的用户。status是低基数列，就五个状态值。然后amount和created_at是用来模拟真实的宽表回表开销的。这个设计其实就是为了后面讲“复合索引列顺序”还有“避免 SELECT *”的时候做铺垫的。

CREATETABLEt_order(id bigserialPRIMARYKEY,user_idbigintNOTNULL,statusvarchar(16)NOTNULL,-- pending / paid / shipped / done / canceledamountnumeric(10,2)NOTNULL,created_attimestampNOTNULL);

用generate_series一次性往里面灌 200 万行数据。金仓是完全兼容这个函数的。用一条INSERT ... SELECT走集合操作去批量生成，比你写个脚本一行一行去循环插入要快多了。

INSERTINTOt_order(user_id,status,amount,created_at)SELECT(random()*100000)::bigint,-- 10 万用户，高基数(ARRAY['pending','paid','shipped','done','canceled'])[floor(random()*5+1)],round((random()*1000)::numeric,2),now()-(random()*interval'365 days')FROMgenerate_series(1,2000000);-- 造完必须手动收集统计信息，否则优化器还以为这是张空表ANALYZEt_order;

这里有一个很容易被忽略的关键点。INSERT弄完之后，你一定要去跑一下ANALYZE。为什么这么说呢。优化器去做代价估算，完全是靠sys_statistic里面的统计信息的。比如行数啊、列的唯一值数量啊、数据分布的直方图啊这些。如果你不跑 ANALYZE，优化器可能还是会按照一张空表去估算。这就会导致后面的执行计划是错的。你可能误以为某个索引没用，其实呢，只是统计信息没更新而已。这一步是后面所有判断的基础。

三、把调优要用的工具扩展装齐

金仓把很多调优的能力做成了扩展。用之前得先装上。在这套实例里面，sys_stat_statements（版本是1.11）已经跟着库装好并且预加载了。剩下几个我们就一个一个去CREATE EXTENSION：

-- 慢 SQL 采集统计（本实例已装，确认即可）CREATEEXTENSIONIFNOTEXISTSsys_stat_statements;-- 假设索引：不建真索引也能让优化器"假装"索引存在，预估计划CREATEEXTENSIONIFNOTEXISTSsys_hypo;-- SQL Tuning Advisor + Plan Monitor：让数据库自己给调优建议CREATEEXTENSIONIFNOTEXISTSsys_sqltune;-- 缓冲区观察：看哪些对象被缓存、命中情况CREATEEXTENSIONIFNOTEXISTSsys_buffercache;

实际跑下来呢，sys_hypo和sys_buffercache一下就装好了。sys_stat_statements提示说已经存在了。唯独到了sys_sqltune这里，当场就报错了：

ERROR: This module can only be loaded via shared_preload_libraries

看到这个报错其实就应该明白了，这里有个关键的区别。像sys_hypo还有sys_buffercache这种扩展，装完就能用。但是sys_sqltune还有sys_kwr这种，它们是要在实例启动的时候就挂载后台钩子的模块。光是CREATE EXTENSION是不够的。你必须先在kingbase.conf文件的shared_preload_libraries里面把它预加载好。然后再重启实例，这样去建才能成功。下一节我仔细讲讲正确的安装姿势。

四、定位：用 sys_stat_statements 找出最该优化的那条 SQL

调优的第一原则，我觉得是先量化，再动手。一个系统里面慢 SQL 可能会有几十条。但是真正把系统拖垮的，往往是那种单次看起来不是特别慢、但是调用得非常频繁的那几条。你要是凭直觉去挑，很容易就把精力花在错的地方了。

sys_stat_statements就是干这个的。它的采集机制是这样的。它在解析的阶段会对语句做归一化。什么意思呢，就是把user_id = 12345里面的具体数字替换成一个占位符$1。这样“同一条 SQL 传不同参数”的调用，就会被归并成一条记录。然后去累计它的调用次数、总耗时、平均耗时、返回行数还有缓冲读写这些指标。这个思路其实跟 Oracle 里面按SQL_ID归并是一样的。从 Oracle 迁过来的 DBA 应该会觉得很熟悉。

我们先重置一下统计，跑一批可以复现的模拟负载，然后再去查 Top N。开始之前先确认一下采集范围。sys_stat_statements.track这个参数控制了记录哪些语句。top是只记顶层语句。all是连嵌套的语句也一起记。none就是完全不记。为了确保演示里面每条查询都能被采到，这里我就显式地设成all。然后热加载让它生效，这样就不用重启了：

-- 确认并打开采集范围（默认可能为 none，那样将采不到任何语句）SHOWsys_stat_statements.track;ALTERSYSTEMSETsys_stat_statements.track='all';SELECTsys_reload_conf();-- 返回 t 即已生效-- 清空历史统计，从干净状态开始观察SELECTsys_stat_statements_reset();-- 模拟业务负载：反复以不同参数查询（实际可用脚本循环上千次）SELECT*FROMt_orderWHEREuser_id=12345ANDstatus='pending';SELECT*FROMt_orderWHEREuser_id=67890ANDstatus='paid';-- …… 循环执行若干轮，模拟真实流量 ……-- 按总耗时排序，找出最该优化的 SQLSELECTquery,calls,total_exec_time,mean_exec_time,rowsFROMsys_stat_statementsORDERBYtotal_exec_timeDESCLIMIT5;

看一下结果：这里主要盯住两个字段。一个是total_exec_time（总执行耗时），它代表了这条 SQL 对整个系统的压力有多大。另一个是mean_exec_time（平均单次执行耗时），它代表了单次执行到底有多慢。我们要找的就是t_order那条查询。你看，三次不同参数的调用被归一化成了同一条记录，就是那个user_id = $1 AND status = $2。它单次的平均耗时居然高达167 ms。在一个高频调用的业务查询上，这么大的单次开销累积起来，那肯定就是系统的主要压力来源了。所以它的total_exec_time自然就排在最前面。这也是我们投入产出比最高的优化目标。

query | calls | total_exec_time | mean_exec_time | rows ---------------------------------------------+-------+-----------------+------------------+------ SELECT * FROM t_order WHERE user_id = $1... | 3 | 501.004673 | 167.001557666666 | 21

优化之前的基线（后面对比要用到的锚点）：这里的mean_exec_time ≈ 167 ms。这就是第七节我们建完索引之后要去打下来的那个数字。

后台模块的正确安装姿势：预加载加上重启（sys_sqltune / sys_kwr）

上一节我们在CREATE EXTENSION sys_sqltune的时候报了那个错。这其实不是卡住了。而是金仓对这类后台常驻模块的一个明确要求。它们需要在实例启动的时候就预加载。你理解了这一点，安装就很顺了。

说一下原理：sys_sqltune（还有第九节要用的sys_kwr）是依赖一个在实例启动时就加载的后台模块的。CREATE EXTENSION只是在当前的库里面注册了一些函数和视图。它并不会把这个模块挂到实例的启动流程里面去。模块没有预加载起来，扩展自然就建不了。所以正确的顺序应该是，先预加载，然后重启，最后再去建扩展。

第一步，先问库自己配置文件在哪。不要想当然地去套默认路径。我用的这套实例，数据目录就不在默认的位置：

SHOWconfig_file;-- 实测：/data/kingbase/kingbase.confSHOWdata_directory;-- 实测：/data/kingbaseSHOWshared_preload_libraries;-- 看当前预加载了哪些模块

看了一下shared_preload_libraries，发现里面本来就有sys_kwr和sys_stat_statements。这就是为什么sys_stat_statements一直在采集，sys_kwr等会儿能直接建的原因。就是缺了一个sys_sqltune。那么我们就去编辑/data/kingbase/kingbase.conf这个文件。在原来列表的最后面把它补上，别的都不动：

# kingbase.conf —— 原值末尾追加 , sys_sqltune，别整行重敲以免漏项 shared_preload_libraries = '……, sys_kwr, sys_stat_statements, ……, sys_sqltune'

第二步去重启实例

# ① 直接用 root 跑 sys_ctl 会被拒绝：# sys_ctl: 无法以 root 用户运行，请以服务器进程所属用户登录（或使用 su）su- kingbase# ② su - 之后工作目录切到了家目录，再用相对路径 ./sys_ctl 会「没有那个文件或目录」# 改用绝对路径，并把 -D 指向真实数据目录：/opt/Kingbase/ES/V9/Server/bin/sys_ctl restart-D/data/kingbase

看到日志里面提示服务器进程已经启动了，就说明预加载生效了。我们重新连上perf_demo，把这两个后台模块的扩展补齐：

CREATEEXTENSIONIFNOTEXISTSsys_sqltune;CREATEEXTENSIONIFNOTEXISTSsys_kwr;\dx

这次\dx列出来，sys_sqltune（1.1）和sys_kwr（1.9）就都在了。

这类后台模块的安装方法，记住三条就顺了：需要后台常驻加载的扩展，装之前先SHOW shared_preload_libraries确认一下。改配置之前用SHOW config_file问清楚真实的路径，别硬套默认目录。重启的时候一定要用实例属主的账号，加上绝对路径。走通这三步，sys_sqltune和sys_kwr就都能装上了。

五、诊断：一个字段一个字段去读懂 EXPLAIN (ANALYZE, BUFFERS)

锁定了 SQL，下一步就是搞清楚它到底慢在哪。这一步其实最考验功底。很多人就是只看最后那个几百毫秒的数字。但是计划里每一行在说什么，他根本看不懂。我们对目标 SQL 做一个完整的执行计划分析：

EXPLAIN(ANALYZE,BUFFERS)SELECT*FROMt_orderWHEREuser_id=12345ANDstatus='pending';

那么这三个关键字为什么要放一起用呢。EXPLAIN的话，它只是给你看优化器估算出来的计划。加上ANALYZE之后，它就会真的去执行这条 SQL。然后把估算的值和实际的值放在一起给你看。这两个值差得越多，就说明统计信息越不准。再加上BUFFERS这个选项，它就能告诉你这次查询到底摸了多少个数据块。这是判断 I/O 压力最直接的东西。

优化之前因为没有合适的索引，实测出来的计划长这样：

Gather (cost=1000.00..30307.40 rows=4 width=36) (actual time=107.341..163.076 rows=6 loops=1) Workers Planned: 2 Workers Launched: 2 Buffers: shared hit=1344 read=15463 -> Parallel Seq Scan on t_order (cost=0.00..29307.00 rows=2 width=36) (actual time=102.874..150.230 rows=2 loops=3) Filter: ((user_id = 12345) AND ((status)::text = 'pending'::text)) Rows Removed by Filter: 666665 Buffers: shared hit=1344 read=15463 Planning Time: 0.110 ms Execution Time: 163.110 ms

一个字段一个字段来看（这是本节的核心）：

• Parallel Seq Scan on t_order——这叫并行顺序扫描。因为没有能用的索引，那就只能全表扫描了。又因为这张表挺大的，算出来的代价挺高。优化器干脆就拉起好几个工作进程一起扫。走到“并行全表扫描”这一步，本身就是一个信号了。意思就是为了从 200 万行里面捞出那么几行，数据库不得不动用多进程去硬扫。
• Gather/Workers Planned: 2/Workers Launched: 2——Gather是并行计划里面的“汇总”节点。计划并启动了 2 个 worker。加上 leader 自己，一共是3 个进程。它们分片去扫表，然后再由Gather来汇总。这就是下面那个loops=3的由来。
• loops=3配上Rows Removed by Filter: 666665——扫描节点被 3 个进程各跑了一遍。这里显示的是单进程的平均值。每个进程平均丢掉了大概 66.7 万行。三个加起来差不多就是 200 万。整张表被完整扫了一遍。结果呢，就为了最后那 6 行结果。这就是全表扫描浪费的地方。
• cost=0.00..29307.00（还有Gather那里的..30307.40）——这是优化器估算出来的抽象代价。注意它不是毫秒。冒号前面是启动代价，后面是总代价。这个数字本身没有单位。它的价值在于横向去比较不同计划的相对好坏。等建完索引我们再来看这个数，它会掉得非常厉害。
• rows=4（这是估算的）对上rows=6（这是实际的）——估算和实际是在同一个量级。这就说明第二节我们做的ANALYZE是起作用了，统计信息是准的。
• Buffers: shared hit=1344 read=15463——命中缓存是 1344 个块。但是还要从磁盘去物理读15463 个块。大量的read就是耗时的直接原因。
• Execution Time: 163.110 ms——这是总执行时间。跟第四节sys_stat_statements采到的mean_exec_time ≈ 167 ms这个基线是对得上的。两个视角互相印证了一下。

讲一下原理：Seq Scan、Index Scan 还有 Bitmap Index Scan 的区别

理解了“为什么慢”，还得知道“快起来会走哪条路”。金仓的优化器在扫描一张表的时候，主要是有三种策略。它选哪一种是基于代价估算自动去决定的：

• Seq Scan（顺序扫描）：不管你的条件是什么，把整张表按物理顺序读一遍，然后再逐行去过滤。当查询返回的行占全表比例很高的时候，比如 30% 以上，它反而是最优的。因为顺序读磁盘比在索引和堆表之间来回跳着读要快。但是对于“200 万里面捞几行”这种高选择性的查询，它就是最差的选择了。在大表上还会像上面那样，退化成并行全表扫描，好几个进程一起硬扫。
• Index Scan（索引扫描）：先在索引的 B 树里面定位到满足条件的键。然后再逐条回表去取整行的数据。它适合返回极少行的情况，也就是高选择性。代价是什么呢。就是每命中一个键，就要做一次随机的回表读。一旦命中的行数变多了，这种随机 I/O 就会把它拖慢。
• Bitmap Index Scan（位图索引扫描）：那如果是返回中等行数的情况呢。就有了第三种策略。它先去扫索引，把满足条件的行位置攒成一个内存里面的位图。然后按物理块的顺序排好。最后再一次性成批地去读堆表。这样就避免了 Index Scan 那种反复随机跳读的开销。如果你的计划里出现了Bitmap Index Scan加上Bitmap Heap Scan的组合，那就是走了这条路。

搞清楚这三者的差别，我们的目标就明确了。就是让这条高选择性的查询从Seq Scan切换到Index Scan。不过在真正动手建索引之前，金仓其实给了我们一个更聪明的办法。就是先预演一下。

六、评估：用 sys_hypo 假设索引，不建真索引先预演

传统的做法是，觉得该建索引了，就直接建上去试试。但是在 200 万行的大表上建索引，可能要几十秒甚至更久。而且还会占磁盘，还会产生锁。如果建完发现优化器根本不走这个索引，那就是白忙一场。还平白无故给运维增加了负担。

金仓的sys_hypo（假设索引）扩展，解决的正是这个麻烦。它的原理是这样的：去创建一个只存在于优化器元数据里面的“虚拟索引”。它有列的定义，也有基于统计信息估算出来的大小和代价。但是呢，它不占磁盘空间，也不会去读写任何一个真实的数据块。当你对一条 SQL 做EXPLAIN的时候（注意只能EXPLAIN，不能加ANALYZE），优化器就会把这个虚拟索引纳入到代价比较里面去。它会告诉你假如这个索引真存在，我会不会用，代价能降到多少。这就相当于动土之前先做一次沙盘推演。这是“先评估、再落地”这套方法里面最核心的一个工具。

-- 创建一个假设的复合索引 (user_id, status)，返回它的虚拟 oid 与名字SELECT*FROMsys_hypo_create_index('CREATE INDEX ON t_order (user_id, status)');-- 在假设索引存在的前提下看计划——只能 EXPLAIN，不能加 ANALYZE-- （虚拟索引没有真实数据，ANALYZE 无法真的执行索引扫描）EXPLAINSELECT*FROMt_orderWHEREuser_id=12345ANDstatus='pending';-- 查看当前有哪些假设索引SELECT*FROMsys_hypo_list_indexes();-- 预演结束，清理掉所有假设索引，不留痕迹SELECTsys_hypo_reset();

sys_hypo对外提供的这套函数其实很直观。sys_hypo_create_index传进去一句建索引的 DDL，就能造出虚拟索引。sys_hypo_list_indexes可以看当前有哪些。sys_hypo_reset一键清空。整个过程完全不会碰到磁盘。

实测的结果：sys_hypo_create_index造出了一个虚拟索引<12603>btree_t_order_user_id_status。名字里面的<12603>是它的虚拟 oid。用来标示这是一个假设索引，不是真的。紧接着的EXPLAIN计划就变成了这样：

Index Scan using <12603>btree_t_order_user_id_status on t_order (cost=0.05..20.13 rows=4 width=36) Index Cond: ((user_id = 12345) AND ((status)::text = 'pending'::text))

看一下这个结果：计划从原来的Parallel Seq Scan（总代价大概 30307）一下子变成了Index Scan（总代价变成了20.13）。代价降了三个数量级。之前那种扫全表再逐行丢弃的浪费彻底没了。Index Cond直接用索引就定位到了目标行。这就说明复合索引(user_id, status)确实是有效的，值得落地。而整个判断的过程里面，我们没有在磁盘上真的去建索引，也没有读写一个数据块。这就是“先评估、再落地”的底气。在大表上动手之前，先零成本确认一下方向对不对。

顺便跟 Oracle 对比一下：Oracle 如果要预估加个索引会怎样，通常得借助 SQL Access Advisor 或者真的建了再看。金仓把假设索引做成了原生的扩展。这种零成本预演的体验用起来还是挺顺手的。

除了自己用假设索引去推演，金仓还内置了一套 SQL 调优顾问，就是sys_sqltune。它能让数据库直接给你调优建议。最常用的就是index_advisor。你把一条 SQL 喂给它。它就会去分析语义和统计信息，然后在后台生成索引和改写的建议。调用成功的话会返回t。还有一个quick_tune_by_sql，它一步就能产出更完整的调优报告。（我这个实例的sys_sqltune函数是装在perf这个模式下的，所以调用的时候带上模式名就行了。）

-- 让调优顾问分析目标 SQL，返回 t 表示已成功生成建议SELECTperf.index_advisor('SELECT * FROM t_order WHERE user_id = 12345 AND status = ''pending''');-- 要一份更完整的调优报告（含索引与改写建议）SELECTperf.quick_tune_by_sql('SELECT * FROM t_order WHERE user_id = 12345 AND status = ''pending''');

有了假设索引的亲手推演，再加上调优顾问的自动分析。“该建(user_id, status)复合索引”这个方向就有双重保障了。一个是你自己动手验证的，一个是数据库主动出的招。两个办法得出了同一个结论。这样我们就能放心地在下一节正式去落地了。

七、落地：建复合索引加上 SQL 重写，前后做个对比

沙盘推演确认有效了，现在正式动手。先把计时开关打开，这样能直观感受到前后的差异：

\timingon-- 正式创建复合索引，列顺序 (user_id, status) 有讲究CREATEINDEXidx_torder_user_statusONt_order(user_id,status);-- 建完索引必须重新 ANALYZE，让优化器感知新索引的统计信息ANALYZEt_order;-- 再次执行同一条查询，对比计划与耗时EXPLAIN(ANALYZE,BUFFERS)SELECT*FROMt_orderWHEREuser_id=12345ANDstatus='pending';

为什么列顺序是(user_id, status)而不是反过来的呢？复合索引是遵循“最左前缀”原则的。它先按第一列排序，再按第二列排序。user_id是高基数列，有十万个不同的值。把它放在最左边，能让索引第一步就把候选行砍到非常少。status只有五个值，选择性很差。如果把它放前面，几乎起不到过滤的作用。把高选择性的列放在最左边，这是设计复合索引的一个基本操作。

建索引本身是很快的。在 200 万行的表上，CREATE INDEX大概 2 秒（时间: 2023.569 ms）就搞定了。随后ANALYZE让优化器感知到了新索引。再跑同一条查询，计划就彻底变了：

Index Scan using idx_torder_user_status on t_order (cost=0.43..20.51 rows=4 width=37) (actual time=0.055..0.100 rows=6 loops=1) Index Cond: ((user_id = 12345) AND ((status)::text = 'pending'::text)) Buffers: shared hit=4 read=8 Planning Time: 0.266 ms Execution Time: 0.127 ms

看一下这个结果：对照第五节的基线，改善是非常明显的——

• 执行方式变了：从Parallel Seq Scan（多进程全表硬扫）变成了Index Scan using idx_torder_user_status（索引直接定位）。连并行的 worker 都不需要了，loops=1；
• Rows Removed by Filter这一整行消失了：索引靠Index Cond精准定位到了 6 行。不再去扫那 200 万行，也不再去丢那 199 万多行了；
• Buffers 变了：物理读read从15463 块降到了8 块。连同缓存命中，总共也就摸了 12 个块；
• 执行耗时变了：Execution Time从基线的≈163 ms压到了0.127 ms。快了大概1300 倍。从百毫秒级别一步跨进了亚毫秒级别。

每个数字都是同一条 SQL 前后两次EXPLAIN (ANALYZE, BUFFERS)跑出来的。可以复现，可以对照。这就是变化如此之大的硬证据。

还有一点，SELECT *会把包括amount、created_at在内的所有列都取出来。这会带来不必要的回表和网络传输。如果业务只需要部分列，就明确把它写出来：

-- 只取业务真正需要的列，减少回表与网络开销SELECTid,amount,created_atFROMt_orderWHEREuser_id=12345ANDstatus='pending';

生产环境的实践：大表建索引用 CONCURRENTLY 不阻塞业务

在演示库里面用普通的CREATE INDEX图个快，这没什么。但是有一点，到了生产环境你得提前想到。常规的CREATE INDEX在执行期间，别的话去对t_order做写入，是会被阻塞一段时间的。

原因是什么呢：常规的CREATE INDEX会对目标表加一把SHARE锁。它会阻塞这张表上的 INSERT/UPDATE/DELETE（读是不受影响的）。一直到索引构建完成才放开。200 万行的表建索引要扫全表，还要排序，还要落盘。耗时是不短的。这段时间在线上的写入就会被挡住。

正确的做法：在生产环境要用CONCURRENTLY来在线建索引。它不会长时间持有阻塞写的锁。代价就是构建的过程会更慢一些。而且不能放在事务块里面去执行：

-- 在线建索引，不阻塞业务写入（生产环境首选）CREATEINDEXCONCURRENTLY idx_torder_user_statusONt_order(user_id,status);

做决策的思路：演示环境你就用普通的CREATE INDEX，图个快。一旦到了有并发写入的生产库，一律加上CONCURRENTLY。场景决定了你用什么手法。这比死记硬背命令要重要得多。

八、参数调优：把内存用在合适的地方，又不把实例搞挂

单条 SQL 优化到位了，实例级别的内存参数就决定了整体吞吐的上限。从 Oracle 迁过来的实例，往往还留着安装时的那些保守默认值。内存远远没用足。这里我们就聚焦三个最关键的参数，配合sys_buffercache来观察效果。

# kingbase.conf —— 改完按参数类型 reload 或 restart # 共享缓冲区：数据库自己管理的页缓存，命中它就免去磁盘 I/O # 经验起点为物理内存的 25%（需重启生效） shared_buffers = 4GB # 单个排序 / 哈希操作可用的内存上限（可 reload 生效） # 这是"每操作每连接"的量，不是全局共享，务必谨慎 work_mem = 32MB # 告诉优化器"操作系统 + 数据库总共有多少内存可用于缓存" # 只影响代价估算，不实际占用内存；调大会让优化器更倾向走索引 effective_cache_size = 12GB

一个一个来讲讲作用和取值的思路：

• shared_buffers是金仓自己管理的一块页缓存。查询要读的数据块如果已经在这里面了，也就是命中了，那就不用去读磁盘了。设成物理内存的 25% 是一个通用的起点。为什么不设满呢。因为操作系统自己也是有文件缓存的。双缓存互补一下。这个参数改完需要重启才能生效。
• work_mem决定了单个排序、哈希连接、哈希聚合能用多少内存。原理是这样的：如果一次操作所需的内存没有超过work_mem，那它就全程在内存里面完成，这样就很快。一旦超过了，它就会溢写到磁盘的临时文件里面去。计划里面会显示Sort Method: external merge，那就慢得多了。把这个值调大，就能让排序和哈希不落盘。
• effective_cache_size是一个“纯预估”的参数。它一个字节的内存都不会真正去分配。它只是告诉优化器，系统整体大概有多少内存可以用来做缓存。你把这个值调大，优化器就会觉得数据大概率是在缓存里面的，走索引的随机读好像也没那么贵。这样它就会更倾向于走 Index Scan。

我们可以用sys_buffercache直接去看缓冲区里面到底缓存了哪些对象，各占了多少块。以此来验证热表是不是驻留在内存里了：

-- 看共享缓冲区里缓存了哪些对象、各缓存了多少个块SELECTc.relname,count(*)AScached_buffersFROMsys_buffercache bJOINsys_class cONb.relfilenode=c.relfilenodeGROUPBYc.relnameORDERBYcached_buffersDESCLIMIT10;

看一下这个结果：t_order以1542 块的数量排在了缓存的第一位。它是当前共享缓冲区里面占用最大的对象。说明这张业务热表已经被大量缓存进内存了。紧跟着的那些_dep、_desc、_stat都是体量很小、长期常驻的系统目录表。这就直接印证了shared_buffers确实把访问最频繁的数据留在了内存里。配合第七节索引扫描“总共只摸了十几个块”的情况，热点数据的物理读确实被压到了极低。

顺带提一嘴：索引idx_torder_user_status并没有出现在这个列表里面。这不是问题，反而是个优点。因为索引扫描每次只需要定位极少的数据块（第七节实测hit=4 read=8），它根本不需要占用大量的缓存。这正是“高选择性查询走索引”省资源的直接体现。

参数的实践：work_mem 要匹配你的并发规模

work_mem有个很容易被忽略的地方。调之前一定要想清楚。它其实不是全局共享的一块内存。它是每个操作、每个连接的上限。如果你不加区分地把全局默认值调得很大，比如直接从 32MB 拉到 512MB。那么在高并发的情况下，内存占用会被急剧放大。

原理是这样的：一条复杂的 SQL 里面可能有好几个排序或者哈希的节点。每个节点都能吃满一份work_mem。你再乘以并发的连接数，实际的内存占用就被成倍放大了。粗略估算一下的话，潜在峰值大约等于work_mem × 并发连接数 × 每条 SQL 里面的排序/哈希节点数。如果是 512MB 乘以上百个连接，再乘以每条 SQL 多个节点。峰值内存很容易就超过你的物理内存了。

做法和决策的思路：把work_mem设一个稳妥的全局默认值，比如 32 到 64MB。只针对那些确实需要大内存排序的个别会话或者个别查询，临时去调高它。用完马上恢复：

-- 只在当前会话临时调大，用于跑一个重排序查询SETwork_mem='256MB';-- …… 执行那条重查询 ……RESET work_mem;

用一句话来总结就是：内存参数不是越大就越快，而是要匹配你的并发规模。全局默认值求稳，局部按需放大。这才是比较稳妥的调参姿势。

九、佐证：用 sys_kwr 生成 AWR 式的负载报告

走到这里，单条 SQL 已经从百毫秒级压到亚毫秒级了。但是一份有说服力的调优报告，不能只盯着一条 SQL 看。真正要回答的问题是，整体负载到底改善了没有。这正是sys_kwr（Kingbase Workload Repository）该出场的时候了。它是金仓内置的自动负载仓库。功能对标的就是Oracle 的 AWR。

它的工作方式其实很直观。在两个时间点各打一个快照。系统会记录下这段区间里面的累计统计。比如 Top SQL、缓冲命中率、等待事件、资源消耗这些。然后再基于“首快照”和“尾快照”的差值，生成一份区间负载报告。落到调优场景里面，标准的用法就是三步：

1. 打首快照——做优化动作之前，先跑一段有代表性的业务负载。然后创建第一个快照，把它当作基线；
2. 打尾快照——做完索引加上参数优化之后，再跑同样的一段负载。创建第二个快照；
3. 生成区间报告——基于这两个快照的 snap_id 去生成区间负载报告。横向对比一下优化前后的整体表现。

-- sys_kwr 依赖预加载，本实例已随库预置并 CREATE EXTENSION 完成（sys_kwr 1.9）-- ① 基线：跑一段业务负载后打首快照SELECTsys_kwr_create_snapshot();-- （完成索引 + 参数优化，再跑同样一段业务负载）-- ② 打尾快照SELECTsys_kwr_create_snapshot();-- ③ 基于首/尾两个快照的 snap_id 生成区间负载报告-- （快照与报告的具体函数名/参数以实例中 sys_kwr 版本的实际接口为准）

一份sys_kwr报告里面，最值得盯的有三块指标：

• Top SQL——优化之后，原来那条t_order查询应该从榜首消失，或者大幅往下沉。说明它不再是系统的主要负载来源了；
• 缓冲命中率——随着热数据驻留在shared_buffers里面，物理读大幅减少，这个命中率会明显往上升；
• 等待事件——跟这条查询相关的 I/O 等待事件会明显变少。

这三点如果都在往好的方向变，才能证明这次的优化不是拆东墙补西墙。而是实打实地降低了系统整体负载。这正是“单点加上全局”双视角里面，那份不可或缺的全局证据。

再顺便跟 Oracle 对比一下：老 Oracle DBA 对 AWR 报告的 Top SQL、Buffer Hit Ratio、等待事件这套东西肯定再熟悉不过了。金仓sys_kwr把这套“快照加上区间报告”的方法论直接原生生搬了过来。迁移过来的团队几乎不需要什么学习成本就能上手。这也是“不止于替代”很实在的一个体现。

十、量化对比与方法论的总结

把整条链路的关键指标汇成一张对比表。全部都是同一条 SQL 优化前后两次EXPLAIN (ANALYZE, BUFFERS)真实测出来的：

一条查询，执行耗时压掉了大概1300 倍。物理读降到了大概1/1900。从百毫秒级一步跨进亚毫秒级。这就是一次体系化调优实打实能看到的好处。

方法论的小结，这四条是可以直接拿走复用到任何系统里面的经验：

1. 先量化，再动手——用sys_stat_statements按total_exec_time或者mean_exec_time去找准最痛的 SQL。别凭感觉去挑目标；
2. 先评估，再落地——用sys_hypo做假设索引，做零成本的沙盘推演。确认有效再在大表上真建。到了生产环境记得加上CONCURRENTLY；
3. 单点加上全局双视角——单条 SQL 用EXPLAIN (ANALYZE, BUFFERS)一个字段一个字段去抠。整体负载用sys_kwr的报告去佐证。这两个视角缺一个都不行；
4. 参数要匹配规模——shared_buffers、work_mem、effective_cache_size它们各管各的事。尤其是work_mem，一定要按并发规模来量力而行。全局求稳，局部放大。避免把实例搞 OOM。

回到开头那个“客服后台转圈”的痛点。慢从来都不是“换了金仓”造成的。而是缺了一次体系化的调优。金仓真正有用的地方在于，它把“定位 → 诊断 → 评估 → 落地 → 调参 → 佐证”这整个流程所需要的工具。从慢 SQL 采集、假设索引、SQL Tuning Advisor 一直到 AWR 式的负载报告。全部都原生备齐了。你不需要东拼西凑去找第三方的组件。剩下要做的，就是耐心地一步一步去量化，把耗时一点一点往下压。