一、那个“第6次执行就拉胯”的灵异事件上个月我们组把一个核心政务系统从 MySQL 8.0 迁到人大金仓KingbaseES V8R6PG兼容模式。开发阶段一切顺利CRUD跑得飞起。结果上压测那天监控大屏直接红了并发 100TPS 5000丝般顺滑。并发 500TPS 掉到 800而且每隔几秒钟就出现一次毛刺响应时间从 5ms 飙到 2000ms。我抓了慢查询日志发现全是同一条MyBatis的预编译SQL– MyBatis 生成的预编译 SQL (PreparedStatement)SELECT * FROM t_user WHERE dept_id ? AND status ?见鬼了这条SQL在MySQL里闭着眼睛走索引怎么到金仓里就时不时全表扫描 我当时第一反应是“金仓的统计信息没更新吧”手动 ANALYZE 了一遍没用。又怀疑是连接池问题换了HikariCP、Druid还是毛刺。最后金仓原厂大佬幽幽地回了一句“你们用的是PreparedStatement吧去看看金仓的 Generic Plan 和 Custom Plan 机制。”那一刻我感觉自己像个傻子。原来 MySQL 和 金仓PG系在处理绑定变量预编译 时底层逻辑完全不同二、核心差异MySQL优化器 vs 金仓CBO优化器在动手写代码前咱得先搞懂这两个数据库的“大脑”是怎么想的。维度 MySQL (8.0) 人大金仓 KingbaseES (PG系) 迁移影响优化器类型 基于代价CBO但相对简单 纯正的复杂CBO路径搜索极深 金仓对统计信息极度敏感执行计划缓存 Query Cache (8.0已废弃) / Prepared Statement 缓存 Custom Plan vs Generic Plan (超级大坑) 预编译SQL行为完全不同Hint 支持 原生支持 /* INDEX() */ 原生不支持需开启 ksh 或 pg_hint_plan 插件 MySQL的Hint直接失效执行计划查看 EXPLAIN (看 type, rows, Extra) EXPLAIN (ANALYZE, BUFFERS) (看 Node, Cost, Actual Time) 看不懂金仓的执行计划 魔性比喻MySQL 的优化器像个快餐店厨师看一眼菜单SQL凭经验简单代价快速给你炒出来快但不够精细。金仓的优化器像个米其林三星主厨他要看食材新鲜度统计信息、火候代价模型、甚至考虑今天天气数据倾斜算出一条“完美路径”。但如果他拿到的食材信息是错的统计信息过期他就会做出一坨屎。三、深度拆解1执行计划的“跨服聊天”从 MySQL 迁到金仓第一件事就是重新学习看执行计划。3.1 MySQL 的执行计划你熟悉的EXPLAIN SELECT * FROM t_user WHERE dept_id 10 AND status 1;你重点看type是不是 ref 或 range如果是 ALL 就完了。rows预估扫描行数。Extra有没有 Using filesort文件排序或 Using temporary临时表。3.2 金仓的执行计划你必须掌握的在金仓里永远不要只写 EXPLAIN那只是优化器的“预估”往往不准。必须加参数– 金仓执行计划的“完全体”EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)SELECT * FROM t_user WHERE dept_id 10 AND status 1;输出示例与逐行翻译墨夶独家批注QUERY PLANIndex Scan using idx_user_dept_status on t_user (cost0.43…8.45 rows1 width120) (actual time0.025…0.028 rows1 loops1)Index Cond: ((dept_id 10) AND (status 1))Buffers: shared hit4Planning Time: 0.150 msExecution Time: 0.055 ms逐行翻译收藏这段Index Scan using idx_user_dept_status✅ 走了索引扫描相当于MySQL的 type: ref。如果是 Seq Scan 就是全表扫描相当于 type: ALL。cost0.43…8.45优化器预估的代价。0.43 是启动代价返回第一行的代价8.45 是总代价。金仓选执行计划只看这个数谁小选谁rows1优化器预估返回1行。⚠️ 如果这个数和实际差10倍以上说明统计信息过期了actual time0.025…0.028实际执行时间。0.025 是拿到第一行的时间0.028 是拿到所有行的时间。rows1 loops1实际返回了1行这个节点循环了1次。⚠️ 如果是 Nested Looploops10000那实际处理行数就是 rows * loops这里最容易踩坑Buffers: shared hit4 核心指标 从内存Buffer Pool中命中了4个数据块。如果是 shared read1000说明从磁盘读了1000个块IO爆炸四、深度拆解2绑定变量的“惊天巨坑”Generic vs Custom Plan这是 MySQL 迁金仓死亡率最高的坑没有之一。4.1 机制差异MySQLPreparedStatement 每次执行都会带着参数值去生成执行计划或者复用缓存的计划参数值参与优化。金仓PG系为了节省 CPU避免每次都硬解析金仓有一个 “5次法则”前 5 次执行 PreparedStatement金仓会带入具体的参数值生成 Custom Plan定制计划。第 6 次执行时金仓会尝试生成一个不带参数值的 Generic Plan通用计划。如果 Generic Plan 的预估代价 小于 前5次 Custom Plan 的平均代价以后就永远用 Generic Plan4.2 翻车现场数据倾斜 Generic Plan 灾难假设 t_user 表有 1000万行status 字段严重倾斜status 1正常用户999万行。status 0禁用用户1万行。– MyBatis 预编译SELECT * FROM t_user WHERE status ?前5次如果传入的都是 0金仓生成 Custom Plan走索引极快0.1ms。第6次金仓生成 Generic PlanSELECT * FROM t_user WHERE status 1。优化器一看status 的平均选择性是 500万行走索引回表太慢了决定走全表扫描Seq Scan第7次及以后即使你传入 0金仓也强制使用全表扫描的 Generic Plan耗时 2000ms 金句MySQL 的预编译是“看菜下饭”金仓的预编译是“前5次看菜第6次开始盲狙”。4.3 破局方案控制 Plan Cache金仓V8R6提供了 plan_cache_mode 参数可以强制改变这个行为。– 方案1强制每次都生成 Custom Plan最安全但耗费CPU– 适用于数据倾斜严重、参数对执行计划影响巨大的SQLSET plan_cache_mode force_custom_plan;– 方案2强制使用 Generic Plan最省CPU但可能走错索引– 适用于参数对执行计划没影响的简单点查SET plan_cache_mode force_generic_plan;– 方案3让优化器自己决定默认值也就是坑你的值SET plan_cache_mode auto;五、完整代码框架执行计划诊断与Hint自动注入生产级⚠️ 重点以下代码经过我们在 .NET 8 / Java 17 人大金仓V8R6 环境下压测验证直接抄作业5.1 执行计划自动诊断脚本Python这个脚本用于在 CI/CD 流水线中自动对比 MySQL 和 金仓的执行计划差异拦截全表扫描。“”人大金仓执行计划自动诊断与对比工具核心职责连接 MySQL 和 金仓执行 EXPLAIN解析金仓的 EXPLAIN (ANALYZE, BUFFERS) 输出识别致命问题全表扫描、高IO、预估行数偏差过大生成诊断报告⚠️ 易错点金仓的 EXPLAIN 输出是树状文本解析需要用正则或专门的库如 pglastANALYZE 会真实执行SQL如果是 UPDATE/DELETE 必须包在事务里并 ROLLBACK“”import reimport psycopg2import pymysqlimport jsonfrom dataclasses import dataclassfrom typing import List, Dict, Optionaldataclassclass PlanNode:“”“执行计划节点数据结构”“”node_type: str # 节点类型 (Seq Scan, Index Scan, Hash Join)relation: str # 表名estimated_rows: float # 预估行数actual_rows: float # 实际行数actual_time: float # 实际耗时(ms)shared_hit: int # 内存命中块数shared_read: int # 磁盘读取块数loops: int # 循环次数warnings: List[str] # 诊断警告class KingbasePlanAnalyzer:“”“金仓执行计划分析器”“”# 致命节点类型相当于MySQL的 type: ALL FATAL_NODES [Seq Scan, Materialize, Sort] def init(self, kb_conn_params: dict): 初始化金仓连接 技巧 诊断脚本建议用只读账号连接防止误操作 self.conn psycopg2.connect(**kb_conn_params) self.conn.autocommit False # 必须关闭自动提交方便ROLLBACK def analyze(self, sql: str, params: tuple None) - List[PlanNode]: 执行 EXPLAIN (ANALYZE, BUFFERS) 并解析 Args: sql: 待诊断的SQL params: 绑定变量参数用于 Custom Plan 诊断 Returns: PlanNode 列表 # ⚠️ 核心必须加 ANALYZE 和 BUFFERS否则看不到真实IO和行数 explain_sql fEXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) {sql} cursor self.conn.cursor() try: # 开启事务执行完 ROLLBACK防止 DML 污染数据 cursor.execute(BEGIN) cursor.execute(explain_sql, params) rows cursor.fetchall() plan_text n.join([row[0] for row in rows]) # 强制回滚 cursor.execute(ROLLBACK) # 解析执行计划文本 return self._parse_plan_text(plan_text) except Exception as e: cursor.execute(ROLLBACK) raise RuntimeError(f执行计划分析失败: {e}) finally: cursor.close() def _parse_plan_text(self, plan_text: str) - List[PlanNode]: 解析金仓执行计划文本正则提取法 ⚠️ 易错点 金仓的执行计划缩进表示层级这里简化处理 只提取包含表扫描和JOIN的核心节点。 生产环境建议用 pglast 库解析 JSON 格式的执行计划。 nodes [] # 匹配节点类型和表名 # 例如: Index Scan using idx_xxx on t_user node_pattern re.compile(r-s(wsw).*?ons(w)) # 匹配预估和实际行数 # 例如: (cost0.43..8.45 rows1 width120) (actual time0.025..0.028 rows1 loops1) rows_pattern re.compile(rrows(d)?actual.?rows(d)sloops(d)) # 匹配 Buffers # 例如: Buffers: shared hit4 read2 buffers_pattern re.compile(rBuffers:ssharedshit(d)(?:sread(d))?) lines plan_text.split(n) current_node None for line in lines: node_match node_pattern.search(line) if node_match: if current_node: nodes.append(current_node) current_node PlanNode( node_typenode_match.group(1), relationnode_match.group(2), estimated_rows0, actual_rows0, actual_time0, shared_hit0, shared_read0, loops1, warnings[] ) if current_node: rows_match rows_pattern.search(line) if rows_match: current_node.estimated_rows float(rows_match.group(1)) current_node.actual_rows float(rows_match.group(2)) current_node.loops int(rows_match.group(3)) buffers_match buffers_pattern.search(line) if buffers_match: current_node.shared_hit int(buffers_match.group(1)) current_node.shared_read int(buffers_match.group(2) or 0) if current_node: nodes.append(current_node) # 诊断规则引擎 for node in nodes: # 规则1全表扫描警告 if node.node_type Seq Scan and node.actual_rows 1000: node.warnings.append(f 致命: 大表 {node.relation} 发生全表扫描 (Seq Scan)实际扫描 {node.actual_rows} 行) # 规则2预估行数偏差过大统计信息过期 if node.estimated_rows 0 and node.actual_rows 0: ratio max(node.estimated_rows, node.actual_rows) / min(node.estimated_rows, node.actual_rows) if ratio 10: node.warnings.append(f⚠️ 警告: 预估行数({node.estimated_rows})与实际({node.actual_rows})偏差 {ratio:.1f} 倍统计信息可能过期) # 规则3磁盘IO过高 if node.shared_read 100: node.warnings.append(f⚠️ 警告: 磁盘读取 {node.shared_read} 个块Buffer Pool 命中率低考虑增加 shared_buffers。) return nodes 使用示例 if name ‘main’:analyzer KingbasePlanAnalyzer({‘host’: ‘192.168.1.100’, ‘port’: 54321,‘dbname’: ‘testdb’, ‘user’: ‘system’, ‘password’: ‘xxx’})# 模拟 MyBatis 的预编译 SQL sql SELECT * FROM t_user WHERE dept_id %s AND status %s # 传入倾斜数据status0 是少数status1 是多数 nodes analyzer.analyze(sql, params(10, 1)) for node in nodes: print(f[{node.node_type}] on {node.relation}) for w in node.warnings: print(f {w})5.2 MyBatis 拦截器自动注入金仓 Hint 与 Plan Cache 控制在 Java 生态中我们不可能去改几百个 Mapper XML。最好的方式是写一个 MyBatis Interceptor在 SQL 执行前自动注入金仓的 Hint 和控制 plan_cache_mode。 背景金仓 V8R6 支持通过 /* … */ 注入 Hint需开启 ksh 插件或兼容模式这能强行固定执行计划。package com.mouwei.kingbase.interceptor;import org.apache.ibatis.executor.statement.StatementHandler;import org.apache.ibatis.mapping.BoundSql;import org.apache.ibatis.mapping.MappedStatement;import org.apache.ibatis.plugin.*;import org.apache.ibatis.reflection.MetaObject;import org.apache.ibatis.reflection.SystemMetaObject;import org.apache.ibatis.session.ResultHandler;import org.slf4j.Logger;import org.slf4j.LoggerFactory;import java.sql.Connection;import java.sql.Statement;import java.util.Properties;/**人大金仓 SQL 拦截器 (MyBatis Plugin)核心职责识别慢查询 Mapper自动注入金仓 Hint (如强制走索引)针对特定 SQL动态设置 plan_cache_mode force_custom_plan防止 Generic Plan 翻车 设计思想非侵入式业务代码零修改全在拦截器里搞定配置驱动通过注解或外部配置文件控制 Hint 规则⚠️ 易错点必须在 StatementHandler.prepare 阶段拦截这时候 SQL 已经生成但还没发给数据库修改 BoundSql 需要用反射因为 MyBatis 没提供 setter*/Intercepts({Signature(type StatementHandler.class, method “prepare”, args {Connection.class, Integer.class})})public class KingbaseHintInterceptor implements Interceptor {private static final Logger log LoggerFactory.getLogger(KingbaseHintInterceptor.class);Overridepublic Object intercept(Invocation invocation) throws Throwable {StatementHandler handler (StatementHandler) invocation.getTarget();MetaObject metaObject SystemMetaObject.forObject(handler);// 获取 MappedStatement (包含 Mapper 接口和 XML 信息) MappedStatement mappedStatement (MappedStatement) metaObject.getValue(delegate.mappedStatement); String mapperId mappedStatement.getId(); // 获取原始 SQL BoundSql boundSql handler.getBoundSql(); String originalSql boundSql.getSql(); // 核心逻辑 1动态控制 plan_cache_mode // 假设我们在配置文件中定义了哪些 Mapper 方法需要强制 Custom Plan // 例如com.xxx.UserMapper.selectByStatus 数据倾斜严重 if (mapperId.endsWith(.selectByStatus) || mapperId.endsWith(.selectByDept)) { Connection conn (Connection) invocation.getArgs()[0]; // 性能提示不要每条SQL都 set可以通过 ThreadLocal 或连接池的 initSQL 统一设置 // 这里为了演示直接执行 SET try (Statement stmt conn.createStatement()) { stmt.execute(SET LOCAL plan_cache_mode force_custom_plan); log.debug([Kingbase] 强制使用 Custom Plan for: {}, mapperId); } } // 核心逻辑 2自动注入 Hint // 假设 t_order 表的 order_no 索引失效我们需要强制走索引 String newSql originalSql; if (originalSql.contains(t_order) originalSql.contains(order_no)) { // 金仓 Hint 语法 (需开启 pg_hint_plan 或 ksh 插件) // /* IndexScan(t_order idx_order_no) */ String hint /* IndexScan(t_order idx_order_no) */; // ⚠️ 易错点Hint 必须紧跟在 SELECT 关键字后面 newSql originalSql.replaceFirst((?i)SELECT, SELECT hint); // 通过反射修改 BoundSql 中的 sql 字段 metaObject.setValue(delegate.boundSql.sql, newSql); log.info([Kingbase] 注入 Hint: {} - {}, originalSql, newSql); } // 继续执行原方法 return invocation.proceed();}Overridepublic Object plugin(Object target) {return Plugin.wrap(target, this);}Overridepublic void setProperties(Properties properties) {// 可在此处加载外部 Hint 规则配置文件}}5.3 金仓 SPMSQL Plan Management执行计划绑定如果 Hint 也救不了或者你不想改代码金仓提供了类似 Oracle 的 SPMSQL Plan Management 功能可以在数据库层面强行绑定执行计划。– – 终极武器金仓 SPM (执行计划基线管理)– – 背景:– 当优化器死活不走正确的索引且无法修改应用代码时– 使用 SPM 在数据库层面锁定执行计划。– 设计思想:– 1. 让 SQL 跑一次正确的执行计划加 Hint 或改 SQL– 2. 把这个计划 capture 为 “基线 (Baseline)”– 3. 以后这条 SQL 再执行优化器必须用基线里的计划– – Step 1: 开启 SPM 功能 (需要 DBA 权限)ALTER SYSTEM SET kingbase_spm.enable on;SELECT pg_reload_conf();– Step 2: 手动执行一次正确的 SQL带上 Hint强制走索引– 假设原始 SQL 是SELECT * FROM t_order WHERE user_id 123 AND status 1;– 优化器错误地走了全表扫描。我们加 Hint 强制走索引SELECT /* IndexScan(t_order idx_order_uid) */ *FROM t_order WHERE user_id 123 AND status 1;– Step 3: 从 Shared Pool 中捕获刚才的执行计划– 查找刚才执行的 SQL 的 queryidSELECT queryid, query, plan_idFROM sys_stat_statementsWHERE query LIKE ‘%t_order%user_id%’;– 假设查到的 queryid 是 ‘123456789’– Step 4: 将该计划绑定为基线CALL dbms_spm.load_plans_from_cursor(sql_id ‘123456789’,fixed ‘YES’ – fixedYES 表示固定该计划优化器不可更改);– Step 5: 验证基线是否生效SELECT sql_handle, plan_name, origin, enabled, accepted, fixedFROM dba_sql_plan_baselines;– 如果看到 fixed ‘YES’说明绑定成功– 避坑指南– 1. SPM 绑定的计划如果底层索引被 DROP 了计划会失效并自动退化。– 2. 表结构大改加减列可能导致基线失效。– 3. 定期清理过期的 Baseline否则 SPM 字典表会无限膨胀。CALL dbms_spm.purge_sql_plan_baseline(older_than 30); – 清理30天前的六、踩坑实录我在这套迁移上犯的3个傻 坑1EXPLAIN 不加 ANALYZE被预估行数骗了症状看 EXPLAIN 输出rows1以为很快。结果实际跑了 10 秒。原因EXPLAIN 只是优化器的“脑补”。如果统计信息过期脑补的 rows1实际 rows1000000。解决永远使用 EXPLAIN (ANALYZE, BUFFERS)看 actual rows。 金句EXPLAIN 是渣男的承诺EXPLAIN ANALYZE 才是他的银行流水。 坑2MyBatis 的 {} 和 #{} 在金仓里的性能天壤之别症状用 {} 拼接的 SQL 跑得飞快用 #{} 预编译的 SQL 慢成狗。原因{} 是字符串拼接金仓每次都生成 Custom Plan带具体值走索引。#{} 是 PreparedStatement触发了 Generic Plan不带值全表扫描。解决对于数据倾斜严重的字段如 status, type在 MyBatis 拦截器里强制 SET LOCAL plan_cache_mode force_custom_plan。 坑3金仓的 Hint 插件没开写了白写症状在 SQL 里加了 /* IndexScan(t) */执行计划纹丝不动。原因金仓PG系原生不支持 Oracle 风格的 Hint必须安装并启用 ksh金仓自带或 pg_hint_plan 插件。解决– 检查插件是否安装SELECT * FROM pg_extension WHERE extname ‘pg_hint_plan’;– 如果没有DBA 执行CREATE EXTENSION pg_hint_plan;– 并在 postgresql.conf 中添加到 shared_preload_libraries七、避坑清单收藏这张表序号 坑点 症状 解决方案1 Generic Plan 翻车 预编译SQL第6次执行突变全表扫描 设置 plan_cache_mode force_custom_plan2 统计信息过期 执行计划预估行数与实际差100倍 配置定时 Job 执行 ANALYZE3 Hint 不生效 加了 /* … */ 没用 安装 pg_hint_plan 或启用金仓 ksh 插件4 Nested Loop 爆炸 执行计划里 loops100000 检查驱动表是否选错用 Hash Join 替代5 内存参数太小 Buffers: shared read 极高 调大 shared_buffers 和 work_mem6 排序溢出磁盘 出现 Disk: 5000kB 调大 work_mem避免 Sort 节点写临时文件八、金句总结 MySQL 的优化器是“差不多就行”金仓的优化器是“差一点都不行”。从 MySQL 迁到人大金仓不是换个 JDBC URL 就完事了。你必须理解 CBO 的代价模型理解 Custom Plan 与 Generic Plan 的博弈理解 Buffer Pool 的命中逻辑。迁移的本质不是让新数据库去兼容你的烂 SQL而是借这个机会把以前 MySQL 帮你兜底的债连本带利地还上。