KingbaseES慢SQL调优实战：Query Mapping与函数缓存全解析

慢 SQL 这个东西每个做数据库的人早晚都会碰上。有时候一条 SQL 昨天跑 3 秒今天跑 3 分钟有时候明明加了索引执行计划就是不走还有时候应用代码动不了DBA 只能对着一条烂 SQL 干瞪眼。我在生产环境里折腾 KingbaseES 调优也有几年了踩过不少坑也积累了一些真正管用的手段。今天想重点聊聊 Query Mapping 和函数缓存这两个方向它们不像索引、分区那么“日常”但在处理执行计划漂移、高并发重复计算这类问题上效果往往比常规手段好得多。这篇文章不是教科书式的功能罗列而是我从实际调优现场总结的思路和操作适合正在维护 KingbaseES 生产库的 DBA、还没完全摸透优化器脾气的开发人员以及想系统提升 SQL 调优水平的同学参考。我会先把调优的整体思路理清楚再深入到 Query Mapping 和函数缓存的具体机制、操作步骤和踩坑经验。1. 调优前的全局认知1.1 为什么你的调优总是事倍功半很多人一接到慢 SQL第一反应就是“加索引”。索引当然重要但我在生产上见过太多被索引坑了的案例加完索引执行计划变了SQL 跑得更慢了或者索引建了好几个优化器偏偏选了最差的那个。根因在于优化器是基于统计信息、系统参数和成本模型来选执行计划的它不是万能的更不是每次都能做对选择。拿统计信息来说如果表的数据分布发生了大变化但统计信息没及时更新优化器拿到的就是“过期情报”做出来的执行计划自然不靠谱。这时候你光看 SQL 本身是看不出问题的必须结合执行计划去分析。而更麻烦的是同一套 SQL 在不同时间点可能生成完全不同的执行计划今天我们在一台机器上调好了换到另一台同样的配置可能就不是那么回事了。所以我把调优的思路分成了三个层次语句层改写 SQL、调整关联顺序、拆分大查询靠的是对 SQL 语义的理解。物理层索引、分区、表空间布局、统计信息采集靠的是对数据分布的理解。执行计划治理层把优化器“选错”的计划强行纠正过来或者把重复计算的结果缓存起来复用靠的是对系统运行机制的理解。前两个层次大家平时接触得多第三个层次才是今天的主角。这也是我说“高级 SQL 调优手段”的含义——不是去猜优化器的脾气而是直接接管那些优化器搞不定的场景。1.2 高级调优手段的本质绕过优化器的不确定性KingbaseES 作为一个兼容 Oracle 语法和特性的关系型数据库执行计划生成机制本质上仍然遵循“解析→改写→优化→生成计划”这条链路。优化器每一步都在做估算只要有估算就有误差有误差就有选错计划的可能性。Query Mapping 的思路很简单既然优化器有时候选不对那我在它选对的时候把执行计划“拍下来”下次同样一条 SQL 来了我不让它重新选直接用拍下来的计划执行。这就像你到了一个陌生的城市导航第一次给你指了一条绕远的路但你自己摸索出了一条捷径于是你把捷径记在了本子上以后再出门就直接走捷径不给导航重新规划的机会。函数缓存的思路则是另一个方向不是所有查询都需要重新算一遍很多复杂计算在高并发场景下是重复的。如果能把“某类计算的结果”存起来下次遇到同样的输入直接返回缓存值省掉整个计算过程性能提升是数量级的。这两种手段本质上都在做同一件事把运行期的事情变成“存量复用”。不过它们的适用场景、配置方式、失效机制完全不同下面逐个说清楚。2. Query Mapping让执行计划“钉死”在正确路径上2.1 Query Mapping 是什么它解决了什么本质问题Query Mapping 在 KingbaseES 中属于执行计划稳定性管理的能力核心作用是把一条 SQL 语句与一个指定的执行计划绑定起来。绑定之后不管统计信息怎么变、系统参数怎么调只要 SQL 文本匹配上了就直接复用绑定的计划。它解决的问题非常明确执行计划漂移同一 SQL 在不同时间因为统计信息变化走了不同计划性能忽好忽坏。SQL 无法修改很多遗留系统的 SQL 写死在代码里DBA 不能随意改动但可以通过 Query Mapping 在不改 SQL 的前提下纠正执行计划。优化器误判某些 SQL 的优化器估算明显不合理比如对数据分布严重倾斜的列估算行数差了上千倍导致选错 join 方式或索引。我记得有一次处理一个省级政务系统的慢查询那条 SQL 嵌在第三方厂商的编译器里根本没法改。业务方反馈每天上午 10 点后查询变得特别慢一查执行计划发现优化器在关联两张表时选择了 Nested Loop而实际上其中一张表的数据量在上午会涨到几十万行Nested Loop 的代价急剧上升。当时就是用 Query Mapping 把执行计划固定成 Hash Join问题立刻解决而且再也没复发过。2.2 Query Mapping 的核心原理与工作机制从实现机制来看Query Mapping 大致分为“识别”和“应用”两个环节。识别环节数据库会把传入的 SQL 文本做规范化处理去掉空格差异、大小写转换、常量参数化等生成一个唯一的 SQL ID 或哈希值。这个过程和我们平时说的 cursor sharing 有相似之处——目的都是把“看起来一样”的 SQL 归到同一个映射条目里。应用环节当一条 SQL 经过解析后系统会先去查询 Mapping 表或映射文件看这个 SQL ID 是否命中。如果命中就直接取出对应的执行计划或计划模板交给执行器不再走完整的代价优化流程。这里有一个关键细节Query Mapping 绑定的不是执行计划本身而是“如何生成执行计划”的指示。有些实现绑定的是一组 Hint 或计划基线有些绑定的是执行计划的哈希签名。绑定计划签名的好处是更精确但如果表结构发生变化签名可能失效绑定 Hint 的优点是灵活性更高表结构变化后还能继续按 Hint 的约束尝试但如果 Hint 写得不完整也可能生成和预期不一致的计划。在实际使用中KingbaseES 的 Query Mapping 往往和计划管理相关视图配合使用。我一般是这样操作的先在测试环境里把 SQL 跑一遍通过EXPLAIN ANALYZE验证目标执行计划是最优的。用CREATE OUTLINE如果这条记录不存在或者对应的管理函数将当前 SQL 的执行计划保存为映射项。这里还要区分一个概念和 Oracle 的 OutLine、SQL Plan Management 类似KingbaseES 中做执行计划固定的手段也有几种叫法底层实现不尽相同。Query Mapping 是其中比较直接的一种它侧重“文本映射”即“SQL 文本变化了就不命中”而计划基线如果版本支持更侧重“计划演化”允许在一定范围内接受新计划。大家在实际使用时要确认当前版本支持的是哪种能力避免按错文档做配置。2.3 实操如何使用 Query Mapping 固定执行计划下面我用一个生产环境里常见的案例演示完整的操作流程。假设有下面这条 SQL负责统计某个机构的订单汇总SELECT o.org_id, COUNT(*), SUM(o.amount) FROM orders o JOIN org_info i ON o.org_id i.org_id WHERE o.create_time DATE 2024-01-01 AND i.level 3 GROUP BY o.org_id;问题是org_info.level 3的数据大约只占全表的 5%优化器对i.level 3的选择率估算很准确但在和orders表做关联时优化器误判了orders按org_id过滤后的行数导致它选了一个糟糕的 Nested Loop 计划执行耗时从原来的 0.8 秒变成 12 秒。第一步确认当前执行计划EXPLAIN (ANALYZE, BUFFERS) SELECT o.org_id, COUNT(*), SUM(o.amount) FROM orders o JOIN org_info i ON o.org_id i.org_id WHERE o.create_time DATE 2024-01-01 AND i.level 3 GROUP BY o.org_id;如果输出的计划里出现了Nested Loop并且内层循环扫描次数很高基本可以确认是计划选择问题。第二步在测试环境里验证目标计划。可以通过加 Hint、调整参数或改写 SQL 的方式找出最快路径比如强制 Hash JoinSELECT /* hash_join(o i) */ o.org_id, COUNT(*), SUM(o.amount) FROM orders o JOIN org_info i ON o.org_id i.org_id WHERE o.create_time DATE 2024-01-01 AND i.level 3 GROUP BY o.org_id;确认这个计划确实最快后第三步才是一个核心操作让这条 Hint 版本的计划成为默认计划。在生产 KingbaseES 中如果版本支持 Query Mapping大致操作方式是先执行目标 SQL可以带 Hint然后调用管理接口将其保存为映射项再把原始不带 Hint 的 SQL 关联到这个映射。具体命令不同版本有差异我用过的常见形式类似-- 创建映射将当前会话中最近执行的 SQL 与最优执行计划绑定 SELECT create_query_mapping(query_mapping_name, sql_id_or_text);之后可以通过系统视图确认映射状态SELECT * FROM sys_query_mapping;最后验证效果直接执行原始不带 Hint 的 SQL检查执行计划是否已经变成期望的 Hash Join。这里有几个实操心得创建映射前务必确认 SQL 文本是稳定的。如果应用层每次生成的 SQL 都带不同的常量值或者没有开启参数化映射可能永远命中不了。生产环境里这种问题我遇到过不止一次排查起来非常隐蔽。绑定计划前要在尽可能接近生产的数据分布下验证。测试环境数据量差 10 倍选出来的最优计划可能完全不同。最稳妥的办法是在生产低峰期做EXPLAIN ANALYZE验证。映射不是一劳永逸。如果表结构变更比如加了分区、删了索引绑定的计划可能失效或变得次优。建议建立巡检机制定期检查映射命中率和执行情况这个后面会详细说。注意Query Mapping 适合的是“优化器选错了但你能明确给出正确计划”的场景。如果你自己都不确定哪个计划最优强行绑定只会把问题固化反而不如让优化器自己去试。3. 函数缓存把重复计算变成“查字典”3.1 函数缓存的分类与工作原理函数缓存这个概念在不同数据库里叫法不太一样Oracle 里叫 Result CacheSQL Server 里也有类似机制。KingbaseES 中的函数缓存通俗讲就是把一个函数的“输入参数 返回值”存到内存里下次同样的参数进来直接返回缓存结果不重新执行函数体。它和 Query Mapping 有一个本质区别Query Mapping 管的是“怎么执行”函数缓存管的是“要不要执行”。函数缓存在 KingbaseES 中主要有两个层面的实现表达式级别的结果集缓存优化器在执行某些确定性表达式或查询片段时如果发现输入相同可以复用之前计算的结果。用户自定义函数UDF的缓存对确定性函数DETERMINISTIC系统可以将函数计算结果缓存典型场景是 PL/pgSQL 或 SQL 函数。我自己的理解是函数缓存适合的场景用一个词概括就是“重复”。同一个函数在一条 SQL 里被反复调用几万次参数却只有那么十几种或者同一个函数在高并发下被大量会话用同样的参数调用。这种场景下与其每次现算不如把热点参数的结果提前准备好。举一个印象深刻的例子某保险公司的佣金计算 SQL业务逻辑里有一个计算客户等级的 UDF这个函数体里有大量的判断分支和子查询单次执行耗时约 5 毫秒。单看 5 毫秒不长但这条 SQL 要扫描 200 万笔保单每笔调两次这个函数就是 400 万次调用总耗时 20000 秒完全不可接受。但客户等级的实际取值只有 5 种也就是说 400 万次调用里绝大多数是在用重复参数做重复计算。启用函数缓存后命中率超过 99%整条 SQL 从跑不完缩短到 3 分钟。3.2 函数缓存的适用场景与收益测算不是所有函数都适合缓存。我在实践中总结了适合和不适合的边界条件。适合函数缓存的场景函数是确定性的同样的输入一定产生同样的输出。函数计算代价高体内有子查询、大量逻辑判断、复杂运算。参数重复率高热点参数有限重复调用频繁。数据变更频率低函数依赖的表不是每秒钟都在 update。不适合函数缓存的场景函数体依赖会话状态如当前用户、当前时间。函数依赖的表写入非常频繁导致缓存刚建好就失效。参数组合几乎没有重复缓存命中率极低反而白白消耗内存。关于收益测算我习惯用三个数来评估单次函数执行成本 C可以通过EXPLAIN ANALYZE或函数内加日志来统计。调用总次数 N可以从 SQL 执行统计信息里看。预期命中率 H可以先对参数分布做一个简单统计或者小范围开缓存观察。如果N * C * (1 - H)算出来还是很大那函数缓存的意义就不大得换个思路从 SQL 结构上解决问题。如果这个数压到很低函数缓存就是一个性价比极高的优化手段。3.3 实操配置与使用函数缓存KingbaseES 中启用函数缓存核心是两件事函数要声明为确定性函数缓存区域要配置够大。第一步在创建函数时加上DETERMINISTIC不同版本语法可能有差异也可能是IMMUTABLE。IMMUTABLE在 PostgreSQL 系中表示函数输入相同则输出相同KingbaseES 也兼容这一语义。CREATE OR REPLACE FUNCTION calc_customer_level(cust_id BIGINT) RETURNS INTEGER DETERMINISTIC AS $$ DECLARE lvl INTEGER; BEGIN -- 复杂的等级计算逻辑 SELECT level INTO lvl FROM customer_score WHERE id cust_id; RETURN lvl; END; $$ LANGUAGE plpgsql;第二步检查数据库函数缓存相关参数。以我常用的配置为例SHOW kdb_function_cache_size; SHOW kdb_function_cache_enabled;如果没有开启可以通过SET kdb_function_cache_enabled on;或者直接在配置文件里改重启后生效。第三步在 SQL 中正常调用函数但要用确定性的入参并保证函数的调用符合缓存条件。这里有一个容易忽略的细节如果函数在 SQL 里被当作过滤条件或关联条件使用优化器可能做常数化处理导致缓存逻辑不生效。遇到这种情况可以试试把函数调用放到 SELECT 列表中或者在函数外层再包一层具体要结合执行计划来调整。第四步监控缓存效果-- 查看函数缓存命中情况 SELECT * FROM sys_function_cache_stat;如果命中率很低要排查是不是参数化没有生效或者缓存区太小导致频繁淘汰。注意函数缓存是在“确定性”前提下才能安全使用的。如果你把一个非确定性函数声明成了确定性函数轻则数据算错重则业务事故。这一点务必向开发团队强调——加DETERMINISTIC不是提高性能的装饰是给数据库的一份承诺。4. 两条手段的组合使用一次生产调优实录4.1 场景描述与瓶颈定位前面分开讲了两种手段的原理和操作但在实际生产里它们经常要组合使用。拿我最近处理的一个供应链系统来说。业务方反馈月末对账报表生成极慢原来 10 分钟能跑完的任务现在要 1 个多小时偶尔超时失败。整个对账流程涉及三步从订单表、回款表、退款表抽取数据对每一条订单调用一个运费重算函数recalc_freight()修正历史数据中错误的运费按供应商分组汇总写入对账结果表。我拿到现场后先看慢 SQL 日志锁定了一条核心汇总 SQL单次执行就要 40 分钟。用EXPLAIN ANALYZE一看问题很明显优化器在多表关联时选择了一个中间结果集极大的 Nest Loop这张中间结果集是订单表和回款表关联后产生的行数估算偏差超过 1000 倍。同时我又注意到recalc_freight()函数被调用了 80 多万次而实际运费模板类型只有 6 种几乎所有订单都在重复计算同样的逻辑。这其实是两种问题的叠加执行计划选错了而且重复计算太多。只处理任何一个另一个都会拖后腿。4.2 选型决策为什么这张 SQL 用 Query Mapping那张用函数缓存明确问题后我对症下药对汇总主 SQL 用 Query Mapping。原因有三第一SQL 本身无法修改是从 ERP 系统里带出来的存储过程第二经过测试环境验证强制 Hash Join 后计划最优且稳定第三这个 SQL 每天固定跑一次参数固定映射非常稳定。所以我直接通过 Query Mapping 把执行计划固定下来。对重算函数用函数缓存。原因也有三第一recalc_freight()是确定性函数输入一个订单ID输出固定第二函数内部有 20 多行复杂 CASE 逻辑还扫描了两张基础费率表单次执行约 8 毫秒第三模板类型只有 6 种参数重复率极高。最终我先创建了函数缓存再创建 Query Mapping。这里我建议的顺序是先解决计划问题再解决计算问题。因为计划错误会导致中间结果集爆炸函数即使缓存了调用次数也会因为计划问题被放大。4.3 调配优前后效果对比与注意事项调优后我做了完整的对比记录指标调优前调优后降幅主 SQL 单次执行耗时约 40 分钟3 分 20 秒92%recalc_freight 函数执行次数80 万6 次缓存后实际计算接近 100%总任务耗时超 1 小时约 10 分钟83%以上要注意的几个点函数缓存后要验证业务结果。我调优后会随机抽取一部分订单对比缓存前后的重算结果确保一致。这一步必不可少。Query Mapping 绑定后要持续监控。后面出过一个小插曲供应商加了一张新表导致主 SQL 的关联条件变化绑定的计划还在用旧签名出现了执行计划不匹配的告警。这就是前面说的“映射不是一劳永逸”需要有监控手段。不要同时调太多东西。建议一次只动一个变量确认生效后再动下一个。同时改 SQL、加索引、配缓存出问题后根本没法定位责任。5. 常见问题与排查技巧实录5.1 Query Mapping 不生效的排查我在生产环境里排查过很多次“为什么映射没生效”的问题总结下来主要有几个原因SQL 文本不一致应用层每次生成 SQL 时在注释里带了时间戳或随机参数导致 SQL 哈希值不一致映射匹配不上。绑定变量没有参数化如果 SQL 每次都是字面量拼接同一个逻辑可能产生几千个不同的 SQL ID映射维度无法收敛。这种情况需要先开启参数化或者调整应用的 SQL 写法。权限或会话设置问题有些版本里enable_query_mapping这类参数是会话级的如果应用连接池在连接初始化时重置了参数映射就不生效。建议全局配置后重启应用并在系统视图里确认参数状态。绑定的计划已经过期表结构、索引或统计信息变化后老计划的计算代价可能不是最优但映射仍然强制使用它。这种“生效了但效果变差”的问题最难察觉我建议每个季度做一次映射计划的回放验证。排查方法先看系统视图里映射是否存在、状态是否正常再开启执行计划调试日志确认 SQL 是否走了映射分支最后用EXPLAIN查看实际计划是否和映射一致。一步步缩小范围比瞎猜快得多。5.2 函数缓存命中率低甚至负优化的排查有时候函数缓存开了性能反而下降。我碰到过的情况大概有这么几类缓存区配置过小函数缓存区域被其他数据挤占导致刚建好的缓存立刻被淘汰。解决方法是适当调大缓存区或者控制缓存对象数量。参数组合过于分散函数调用参数看起来是重复的但实际上每个订单ID都不同根本不存在“重复参数”。这种情况应该重构函数把“订单ID”这个粒度改成“模板类型”这个粒度或者直接用物化视图。函数内依赖了非确定性函数比如在函数体内用了random()或now()数据库识别到非确定性后会直接禁用缓存命中率自然为零。并发写入导致缓存频繁失效如果函数依赖的表每秒都在更新缓存刚写入就失效甚至会因为缓存管理开销拖慢整体性能。排查思路也很直接先看缓存命中统计命中率低于 80% 就要警觉再分析函数依赖表的写入频率最后检查函数声明是否真的是确定性函数。5.3 两种手段的取舍与使用建议到文章末尾我想把这两种手段放在一起做个对比方便你在实际调优时选择合适的工具。维度Query Mapping函数缓存核心作用固定 SQL 的执行计划缓存确定性函数的计算结果适用场景计划漂移、SQL 不可改、优化器误判高重复率计算、复杂 UDF、高并发读失效风险表结构变更后计划可能次优依赖表数据变更频繁时命中率低配置复杂程度中需验证最优计划低声明确定性 配置缓存区对业务正确性影响中选错计划影响性能高非确定性函数误声明会产生错误结果我的建议是这两个手段应该是核武器级别的高级手段先用平常手段把 SQL 写对、索引建对、统计信息维护好最后再考虑它们。但如果遇到了常规手段确实解决不了的问题比如代码改不动、优化器反复横跳、重复计算积重难返那这两个手段就是最直接有效的解法。我在实际调优中还有一个习惯做完一次 Query Mapping 或函数缓存的优化后会把整个分析过程记录下来包括当时的执行计划、统计数据、参数配置和优化前后对比。因为这类问题有一个特点——它会周期性复发。统计信息更新了、数据量涨了、业务逻辑改了都可能导致新的计划漂移。有一份完整的调优档案下次排查能省掉一半时间。这个建议希望对你也一样适用。