MySQL迁人大金仓，预编译SQL越跑越慢？我扒了执行计划和绑定变量的底裤，附万字避坑指南

一、那个“第6次执行就拉胯”的灵异事件

，上个月我们组把一个核心政务系统从 MySQL 8.0 迁到人大金仓（KingbaseES V8R6，PG兼容模式）。

开发阶段一切顺利，CRUD跑得飞起。结果上压测那天，监控大屏直接红了：
并发 100：TPS 5000，丝般顺滑。
并发 500：TPS 掉到 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 PLAN

Index Scan using idx_user_dept_status on t_user (cost=0.43…8.45 rows=1 width=120) (actual time=0.025…0.028 rows=1 loops=1)
Index Cond: ((dept_id = 10) AND (status = 1))
Buffers: shared hit=4
Planning Time: 0.150 ms
Execution Time: 0.055 ms

逐行翻译（收藏这段！）：
Index Scan using idx_user_dept_status：✅ 走了索引扫描（相当于MySQL的 type: ref）。如果是 Seq Scan 就是全表扫描（相当于 type: ALL）。
cost=0.43…8.45：优化器预估的代价。0.43 是启动代价（返回第一行的代价），8.45 是总代价。金仓选执行计划只看这个数，谁小选谁！
rows=1：优化器预估返回1行。（⚠️ 如果这个数和实际差10倍以上，说明统计信息过期了！）
actual time=0.025…0.028：实际执行时间。0.025 是拿到第一行的时间，0.028 是拿到所有行的时间。
rows=1 loops=1：实际返回了1行，这个节点循环了1次。（⚠️ 如果是 Nested Loop，loops=10000，那实际处理行数就是 rows * loops，这里最容易踩坑！）
Buffers: shared hit=4：🔥 核心指标！ 从内存（Buffer Pool）中命中了4个数据块。如果是 shared read=1000，说明从磁盘读了1000个块，IO爆炸！

四、深度拆解2：绑定变量的“惊天巨坑”（Generic vs Custom Plan）

这是 MySQL 迁金仓死亡率最高的坑，没有之一。

4.1 机制差异
MySQL：PreparedStatement 每次执行都会带着参数值去生成执行计划（或者复用缓存的计划），参数值参与优化。
金仓（PG系）：为了节省 CPU（避免每次都硬解析），金仓有一个 “5次法则”：
前 5 次执行 PreparedStatement，金仓会带入具体的参数值生成 Custom Plan（定制计划）。
第 6 次执行时，金仓会尝试生成一个不带参数值的 Generic Plan（通用计划）。
如果 Generic Plan 的预估代价 小于 前5次 Custom Plan 的平均代价，以后就永远用 Generic Plan！

4.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 Plan（SELECT * 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）
– 适用于数据倾斜严重、参数对执行计划影响巨大的SQL
SET 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 输出是树状文本，解析需要用正则或专门的库（如 pglast）
ANALYZE 会真实执行SQL，如果是 UPDATE/DELETE 必须包在事务里并 ROLLBACK！
“”"

import re
import psycopg2
import pymysql
import json
from dataclasses import dataclass
from typing import List, Dict, Optional

@dataclass
class 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 = f"EXPLAIN (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+(w+s+w+).*?ons+(w+)')
    
    # 匹配预估和实际行数
    # 例如: "(cost=0.43..8.45 rows=1 width=120) (actual time=0.025..0.028 rows=1 loops=1)"
    rows_pattern = re.compile(r'rows=(d+)?actual.?rows=(d+)s+loops=(d+)')
    
    # 匹配 Buffers
    # 例如: "Buffers: shared hit=4 read=2"
    buffers_pattern = re.compile(r'Buffers:s+shareds+hit=(d+)(?:s+read=(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_type=node_match.group(1),
                relation=node_match.group(2),
                estimated_rows=0, actual_rows=0, actual_time=0,
                shared_hit=0, shared_read=0, loops=1, 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"

# 传入倾斜数据（status=0 是少数，status=1 是多数）
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);

  @Override
  public 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();
  

  }

  @Override
  public Object plugin(Object target) {
  return Plugin.wrap(target, this);
  }

  @Override
  public void setProperties(Properties properties) {
  // 可在此处加载外部 Hint 规则配置文件
  }
  }

5.3 金仓 SPM（SQL Plan Management）执行计划绑定

如果 Hint 也救不了，或者你不想改代码，金仓提供了类似 Oracle 的 SPM（SQL 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 的 queryid
SELECT queryid, query, plan_id
FROM sys_stat_statements
WHERE query LIKE ‘%t_order%user_id%’;
– 假设查到的 queryid 是 ‘123456789’

– Step 4: 将该计划绑定为基线
CALL dbms_spm.load_plans_from_cursor(
sql_id => ‘123456789’,
fixed => ‘YES’ – fixed=YES 表示固定该计划，优化器不可更改
);

– Step 5: 验证基线是否生效
SELECT sql_handle, plan_name, origin, enabled, accepted, fixed
FROM 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个傻

🚫 坑1：EXPLAIN 不加 ANALYZE，被预估行数骗了

症状：看 EXPLAIN 输出，rows=1，以为很快。结果实际跑了 10 秒。
原因：EXPLAIN 只是优化器的“脑补”。如果统计信息过期，脑补的 rows=1，实际 rows=1000000。
解决：永远使用 EXPLAIN (ANALYZE, BUFFERS)，看 actual rows。

💡 金句：EXPLAIN 是渣男的承诺，EXPLAIN ANALYZE 才是他的银行流水。

🚫 坑2：MyBatis 的 {} 和 #{} 在金仓里的性能天壤之别

症状：用 {} 拼接的 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_plan

2 统计信息过期 执行计划预估行数与实际差100倍 配置定时 Job 执行 ANALYZE

3 Hint 不生效 加了 /*+ … */ 没用 安装 pg_hint_plan 或启用金仓 ksh 插件

4 Nested Loop 爆炸 执行计划里 loops=100000 检查驱动表是否选错，用 Hash Join 替代

5 内存参数太小 Buffers: shared read 极高 调大 shared_buffers 和 work_mem

6 排序溢出磁盘 出现 Disk: 5000kB 调大 work_mem，避免 Sort 节点写临时文件

八、金句总结

🔥 MySQL 的优化器是“差不多就行”，金仓的优化器是“差一点都不行”。
从 MySQL 迁到人大金仓，不是换个 JDBC URL 就完事了。你必须理解 CBO 的代价模型，理解 Custom Plan 与 Generic Plan 的博弈，理解 Buffer Pool 的命中逻辑。
迁移的本质，不是让新数据库去兼容你的烂 SQL，而是借这个机会，把以前 MySQL 帮你兜底的债，连本带利地还上。