1. 一条 SQL 从客户端到执行器预编译省掉的到底是哪一段很多人对预编译的理解停留在能防注入这一层面试时能答出这个点实际调优时却说不清它到底省了什么开销。我见过的典型场景是一个接口 QPS 上不去开发把连接串加了几个和老老实实做压测待遇差别很大——前者省的是网络往返和重复的解析工作后者省的是重复的解析工作剩下的都得靠索引和 SQL 本身。要是这两件事混在一起谈最后很容易得出预编译没用或者预编译万能这种两头都不对立的结论。先把一条普通 SQL 的完整旅程摆出来。客户端通过连接把 SQL 文本发到服务器服务器先做词法和语法分析把一串字符变成解析树接着做语义检查确认表存在、字段存在、当前账号有没有权限然后优化器接手根据统计信息、索引分布、代价模型挑一条它认为最便宜的执行路径生成执行计划最后执行器拿着计划去调用存储引擎接口把行读出来经过过滤、排序、聚合把结果集回给客户端。这一整套流程里真正和数据有关的只有最后一步前面几步都是在理解和规划你要干什么。问题在于如果同一个 SQL 骨架被反复执行一万次前几步就被重复做了一万次。预编译做的事情就是把这套流程切成两段。第一段在准备好的时候完成SQL 文本发过去服务器做词法语法分析、语义检查、权限校验把结果留在服务器侧返回一个句柄或者语句 ID。第二段在执行的时候完成客户端只发参数服务器把参数填进已经分析好的结构里直接往优化和执行阶段走。省掉的正是把字符串变成解析树这部分重复劳动再加上参数不必拼进 SQL 文本传输上也更紧凑。我更愿意用一个合同模板来类比。没有预编译的情况下每次要签合同你都把整份合同从头起草一遍改掉甲方乙方、金额、日期再交给法务审。预编译则是先把模板定稿、法务审完后面每次只填空白栏。模板本身不用再审了法务的工作量就降下来了。但请注意一件事模板定稿不代表每次填完的内容都最优比如金额那栏填了一个极端值可能触发另一套审批流程——这正好对应执行计划可能因为参数不同而表现不一样的问题。理解了这一段后面几个问题就都好解释了。为什么有的框架开了预编译反而变慢因为它把一次性 SQL 也送进了 Prepare 流程多了一次网络往返和一次服务器侧的对象创建却没有第二次执行来摊薄成本。为什么明明开了预编译general_log 里还是Query而不是Prepare因为你开的只是客户端那个看起来像预编译的东西。这两个坑在后面会各占一节。1.1 解析开销到底占多少值得为它折腾吗这是个必须诚实回答的问题。解析本身很快一条简单的单表查询解析可能只占总耗时的百分之几取决于 SQL 复杂度、表数量和 JOIN 深度。SQL 越长、JOIN 越多、子查询嵌套越深解析的绝对耗时越大。所以在简单查询、超高 QPS和复杂查询、重复执行这两类场景下预编译的收益逻辑是不一样的前者靠的是省掉高频的固定开销后者靠的是省掉大块的分析开销。但解析开销从来不是预编译唯一的价值。更多时候它带来的是另一层收益网络层面的参数分离让每次交互的报文更小尤其是参数很多、字段很宽的场景这个差别能观察到。再就是权限检查、对象查找这些固定动作也被前置了。我做过的压测里简单点查在开启服务端预编译后提升通常是个位数百分比而一条十表 JOIN 的报表查询重复执行时提升会明显得多。所以如果有人拿着预编译没什么用的结论来找你先问他压的是什么语句。1.2 别急着下结论MySQL 在这件事上和 Oracle 的账不一样很多资料讲预编译时会顺带讲 Oracle 的硬解析、软解析那一套一次硬解析生成计划之后全是软解析复用计划绑定变量窥探还可能让计划突变。这套叙事直接搬到 MySQL 上会误导。MySQL 在预编译场景下的优化行为和 Oracle 那种计划缓存 参数窥探的机制不是一回事。按我自己的实测和查文档的经验MySQL 的预编译主要收益落在解析、权限校验这些前置环节执行计划这块的表现更像每次执行都会重新走优化流程只不过优化所用的统计信息可能来自 prepare 时刻。这个差异带来的实际后果有两个。好消息是你基本不用担心参数嗅探导致计划突然变差这类问题同一个 prepared statement 传不同参数优化器会按当前情况重新判断不太会出现一次坏计划锁死整条 SQL的极端情况。坏消息是你也不能指望靠预编译把优化器的工作量省下来这部分该花还是得花。想验证自己手上的版本到底是什么行为最直接的办法是对同一个 prepared statement 用不同参数跑几轮观察耗时和EXPLAIN结果的差异别只看理论。2. 两个层面上的预编译很多人第一步就理解偏了预编译这个词在 MySQL 生态里指代了至少两种不同的东西它们发生的位置、协议报文、性能表现全都不一样。搞混这两层后面所有的排查都会跑偏。第一层是 SQL 语法层面的 prepared statement你可以在 mysql 命令行里直接手写PREPARE stmt FROM SELECT * FROM t WHERE id ?;然后EXECUTE stmt USING id;用完DEALLOCATE PREPARE stmt;。这三个语句是 SQL 语言的一部分普通客户端就能用。它们对应的是一位会话级的对象生命周期跟着会话走。第二层是客户端/服务器协议层面的预编译。MySQL 的通信协议里有一组专门的命令字COM_STMT_PREPARE负责把语句文本送过去换取一个语句 IDCOM_STMT_EXECUTE负责按 ID 传参数执行COM_STMT_CLOSE负责释放。它走的是二进制协议参数和结果集都能用二进制格式传输比文本协议紧凑。你用的 JDBC、各个语言的驱动、连接池操作的都是这一层。2.1 SQL 层的 PREPARE 与协议层的 PREPARE 不是同一回事SQL 层的PREPARE语句本身要通过文本协议发到服务器服务器解析这条语句后再去准备里面那个 SQL 文本最终返回一个名字。整个过程至少两次往返。协议层的COM_STMT_PREPARE是一步到位驱动直接把 SQL 文本按协议格式发过去服务器返回语句 ID 和结果集元信息。这是驱动内部的行为你在 SQL 里看不到。对于日常开发你接触的基本都是协议层。只有做运维排查、写存储过程、或者需要手动验证某个语句能不能 prepare 时SQL 层才派上用场。我经常在排查这条语句到底能不能预编译的时候直接在命令行敲PREPARE出错信息比从驱动里报出来的要清楚得多。2.2 客户端预编译和服务端预编译驱动默认选了后者吗这是最容易踩的坑没有之一。以 JDBC 为例useServerPrepStmts默认是关闭的。默认状态下你调用PreparedStatement驱动做的事是在客户端把?替换成转义后的字面量拼出一条完整 SQL然后用COM_QUERY发出去。从服务器的角度看它收到的永远是一条条不同的完整 SQL压根没有 Prepare 这个动作。这叫客户端预编译或者更准确地说叫客户端参数替换。只有显式打开useServerPrepStmtstrue驱动才会真正调用COM_STMT_PREPARE。这个参数默认关闭是有原因的早期版本服务端预编译在某些场景下收益不明显反而增加了往返和服务器侧资源占用所以驱动选择保守。但如果你用的是批量或高频重复 SQL打开它通常有收益。对比项客户端预编译服务端预编译协议命令COM_QUERYCOM_STMT_PREPARE/COM_STMT_EXECUTE服务器侧解析每次都做Prepare 时做一次参数传输拼进 SQL 文本单独的二进制参数块防注入依赖驱动的转义实现参数与语句天然分离多一次往返否是首次 Prepare典型开关默认useServerPrepStmtstrue其他语言的驱动思路类似Python、Go、Node 各有各的默认值有的默认开启有的默认关闭还有的会根据语句类型动态决定。所以看到MySQL 支持预编译这句话第一反应应该是问是哪一层谁的默认值有没有被连接池改写。3. 占位符挡住注入靠的不是转义而是通道分离讲预编译防注入的资料很多但大多停在用了占位符就安全这个结论上。这个结论在大多数情况下成立但如果你不知道背后的机制很容易在边界场景翻车。拼接式写法的问题在于用户输入和 SQL 语法处在同一个通道里。SELECT * FROM users WHERE name input 当 input 是 OR 11时拼出来的字符串在词法分析阶段就被解释成了新的语法结构原本的字符串常量提前闭合后面的内容变成了条件表达式。这不是字符串处理失误而是数据被赋予了语法的身份。预编译的机制正好相反。SELECT * FROM users WHERE name ?在 Prepare 阶段就被完整解析语法结构固定死了?是一个明确的参数占位符。执行阶段传进来的值走的是参数通道服务器知道这是一个字符串类型的值它从头到尾不会去解析里面的引号和关键字。就算你传的是一整段带引号的 SQL它也只是一个超长的字符串常量匹配不到任何数据仅此而已。3.1 三个真正可能出问题的地方第一是标识符位置不能参数化。表名、列名、ORDER BY的字段、GROUP BY的字段这些是语法结构的一部分占位符放不进去。非要动态的话只能在应用层用白名单校验把允许的字段名映射一遍绝不能直接把用户输入拼进去。这是最容易被忽略的注入点因为很多人以为我用了 PreparedStatement 就万事大吉了。第二是某些数值位置的处理。比如LIMIT ?在 MySQL 里是支持的LIMIT ?, ?也可以但有的框架会因为参数类型推断问题报错需要显式指定整型。这类问题不会造成注入但会让你误以为预编译不可用而退回到拼接。第三是驱动层的转义实现。客户端预编译模式下防注入这一层是由驱动负责的它要做字符集相关的转义处理。如果连接字符集设置得和实际数据字符集不一致理论上存在绕过转义的可能。这也是我倾向于在安全敏感场景打开服务端预编译的原因把安全边界从应用层挪到数据库层少一层信任假设。3.2 明文存储过程里的 SQL 拼接还是老问题有一类场景特别迷惑人存储过程内部用CONCAT拼 SQL 然后PREPARE执行。这在动态表名、动态分区这类场景里很常见。看上去用了PREPARE实际上拼接发生在PREPARE之前参数已经通过CONCAT混进了 SQL 文本语法解析照样会被污染。真正安全的做法是把参数留到EXECUTE ... USING里传能用占位符的地方一个都不要省。4. 把预编译抓现行日志、状态变量与系统表理论讲完得能证明它在你的环境里真的发生了。我排查这类问题的顺序固定是先看日志确认协议命令再看状态变量确认量级最后查系统表确认当前有哪些 prepared statement 挂着。4.1 general_log 里的协议命令打开general_log之后服务器收到的每一条命令都会记下来。客户端用文本协议发 SQL 时你看到的是Query服务端预编译生效时你会看到Prepare和后续的Execute。这条判断极其有效不用猜驱动做了什么日志不会有假。要注意general_log对性能有影响只适合临时打开排查完记得关掉别留在一个高流量的生产实例上。4.2 Com_stmt 系列状态变量SHOW GLOBAL STATUS LIKE Com_stmt%能给你一张全景表。Com_stmt_prepare是 Prepare 的次数Com_stmt_execute是执行次数Com_stmt_close是关闭次数还有Com_stmt_reset、Com_stmt_fetch等。这几个数值的比值很说明问题如果com_stmt_prepare几乎等于com_stmt_execute说明每次执行都在重新 Prepare预编译缓存根本没起作用如果 execute 远大于 prepare说明复用是正常的。另一个必须看的变量是Prepared_stmt_count它反映当前服务器上活着的 prepared statement 总数。这个值持续增长不下降基本可以确定是连接池或者驱动的 PSCache 泄漏或者是某个连接忘了归还。4.3 performance_schema 里的明细performance_schema.prepared_statements_instances这张表在较新的版本里可用它会列出当前每个 prepared statement 归属的连接、语句文本、Prepare 次数、Execute 次数、最长执行耗时等。相比SHOW STATUS的全局计数这张表能定位到具体是哪个连接、哪条 SQL 在反复 Prepare。我第一次用它的时候一眼就发现了某段代码在循环里创建PreparedStatement且没有关闭Prepare 次数是 Execute 次数的整整两倍。4.4 手动敲一遍加深印象-- 准备 PREPARE s1 FROM SELECT id, name FROM users WHERE id ?; -- 执行两次参数不同 SET uid 1001; EXECUTE s1 USING uid; SET uid 1002; EXECUTE s1 USING uid; -- 查看当前会话残留的 prepared statement SELECT * FROM performance_schema.prepared_statements_instances; -- 释放 DEALLOCATE PREPARE s1;注意SQL 层的PREPARE作用域是当前会话换个连接就看不到了。做验证时别在 A 连接准备、B 连接查询。这套手动流程能帮你快速判断某个语句在语法上能不能 Prepare也能直观看到参数是怎么绑定进去的比盯着驱动日志猜要高效得多。5. 预编译的收益边界什么时候它是优化什么时候是负担前面说了它省什么现在说它不省什么以及什么时候是净亏。5.1 收益来源要拆开看别算成一笔糊涂账第一个来源是解析与准备工作的复用这部分在重复执行同一骨架时成立。第二个来源是传输效率参数用二进制编码结果集也可以走二进制格式宽表、多参数场景下报文体积会明显缩小。第三个来源是安全边界的下移这个不体现在性能数字上但在有安全审计要求的场景里很值钱。这三个来源的前提都是重复执行。如果一条 SQL 这辈子只执行一次比如某个定时任务里的汇总语句开服务端预编译只会让你多一次 Prepare 往返外加服务器侧一个对象创建和销毁的成本。我在做批处理脚本的时候遇到全是跑一次的 SQL通常会把这个开关关掉收益为零复杂度为正。5.2 批量写入场景的取舍更微妙JDBC 的批量插入有个rewriteBatchedStatements参数打开之后驱动会把一批INSERT INTO t VALUES (?)重写成一条多值INSERT INTO t VALUES (...), (...), (...)。这个重写带来的性能提升通常比预编译大得多因为它把 N 次网络往返压缩成了一次。但这里有个坑重写之后语句变成了文本协议下的整条 SQL和服务端预编译的路径会产生冲突在某些驱动版本上会出现两个功能互斥实际只生效一个的情况。我的建议是大批量插入场景优先保证rewriteBatchedStatements生效用日志确认改写后的是不是多值语句对于更新和查询类的重复执行再考虑服务端预编译。不要两个都开了就当万事大吉一定要看日志。场景特征建议理由同骨架 SQL 高频重复执行开启服务端预编译摊薄解析开销每条 SQL 只执行一次关闭服务端预编译多一次往返无收益大批量 INSERT优先开启批处理重写减少网络往返收益更大参数极多、字段极宽开启服务端预编译二进制传输更省带宽动态表名、动态排序无法预编译需应用层白名单校验6. 连接池环境下的预编译会话级对象带来的连锁反应单机、单连接的环境把预编译讲清楚并不难真正让线上出问题的是连接池。6.1 prepared statement 是会话私有的这个特性决定了服务器上看到的 prepared statement 数量大致等于活跃连接数 × 每个连接缓存的语句数。一个连接池配了 200 个连接每个连接缓存 50 条语句理论上限就是一万条。如果应用模块多、SQL 种类杂这个数字很容易继续膨胀。所以调整 PSCache 大小的时候不能只看单个连接要把连接数乘进去算总量再看max_prepared_stmt_count的默认上限16382还够不够。6.2 打满之后报什么错max_prepared_stmt_count达到上限之后新的 Prepare 请求会被拒绝报错信息里会出现 Cant create more than max_prepared_stmt_count statements 这样的字样。这个错误很有辨识度但排查时容易跑偏到是不是哪里没关资源上去。更常见的原因是 PSCache 配置过大加上连接数过多或者每个连接都缓存了大量只执行过一次的语句。调小缓存容量、或者把只执行一次的语句从缓存里排除通常就能解决。6.3 PSCache 要开但要算清楚以 JDBC 为例cachePrepStmts控制是否缓存 prepared statementprepStmtCacheSize是每个连接的缓存条目数prepStmtCacheSqlLimit是允许缓存的 SQL 长度上限超长的语句不会被缓存。这套参数要配套使用只开缓存不改容量意义不大。而且这两个参数只有在useServerPrepStmtstrue时才对服务端预编译有意义——如果还是客户端替换模式缓存的是驱动侧的语句对象收益逻辑完全不同这点必须分清楚。6.4 连接被回收或重启之后连接断开时它名下的 prepared statement 会被服务器一并清理掉这本身是好事不会泄漏。但如果你的应用自己维护了一个跨连接的语句映射连接重建之后旧 ID 就失效了继续用它执行会报未知的 prepared statement。这类问题在连接池做故障切换、数据库做重启时最容易冒出来而且往往不是必现排查起来很烦。我一般会在连接获取的回调里加一句状态重置或者直接依赖连接池自带的语句缓存失效逻辑不自己维护。7. 让预编译白干的几种写法7.1 IN 列表占位符数量得固定WHERE id IN (?, ?, ?)只能处理三个值。参数个数不固定的时候很多人的第一反应是在应用层拼出对应数量的?比如有八个参数就拼IN (?,?,?,?,?,?,?,?)。这么做本身没错但要注意两点一是拼出来的 SQL 骨架种类变多缓存命中率下降PSCache 很容易被塞满二是如果参数个数差异很大Prepare 的次数会成倍增长。我的处理方式是按参数个数分档比如 1、5、10、50、100 几个固定档位不足的位置补上不会命中的值或者干脆按 100 一组拆批次。这个做法听着粗糙但在实际系统里比动态拼 SQL 稳定得多。7.2 参数类型不匹配引发的隐式转换这是性能问题里最隐蔽的一类。表上user_id是varchar你传进去的是整型参数服务器在做比较时可能触发隐式类型转换索引就用不上了。预编译本身没问题问题出在参数类型上。表现是逻辑正确压测时慢EXPLAIN一看type从ref掉到了ALL。排查方式很简单把参数类型和列类型逐个对一遍特别注意那些在应用里被包装成字符串的数值字段。同理字符集不一致也会出这类问题。连接字符集和数据表字符集不同的时候比较操作上的转换同样会让索引失效。这类问题的特点是加了索引也没用很容易被误判成索引建错了。7.3 DDL 之后旧计划失效表结构变化、统计信息大幅变动之后之前准备好的语句可能需要重新准备。服务器会处理这类失效但你得知道有这个动作存在。它的一个副作用是在做在线 DDL 的窗口期应用侧可能集中出现一批 Prepare 和 Execute 的波动表现为短时间内的延迟抖动。如果你的监控里有分位数告警会看到 p99 在这个时间点翘一下这是正常的不用急着回滚。7.4 有些语句就是不能 prepare不是所有语句都能走预编译路径某些管理类、会话控制类语句在语法层面就不支持。与其背清单不如实测把语句拿到命令行里敲一遍PREPARE能过就说明语法层支持。如果报语法错误那就别指望驱动能帮你绕过去只能老老实实走文本协议。我在做 SQL 审核的时候会把这类语句单独标记出来配置层面排除掉免得开发在连接串上折腾半天。8. 我自己常用的一套配置与上线前自检配置层面我一般先在测试环境打开服务端预编译配合语句缓存跑一轮真实流量的压测再决定要不要带到生产。参数大致是这样一组useServerPrepStmtstrue打开服务端预编译cachePrepStmtstrue打开语句缓存prepStmtCacheSize按业务 SQL 种类定通常给到 250 到 500 之间prepStmtCacheSqlLimit覆盖到最长的那几条语句避免关键语句被静默排除在缓存之外。这几个数字没有标准答案取决于你的 SQL 种类数量和连接数算完总和再对照上限看。上线前的自检我固定做四件事。第一件在general_log里确认看到的确实是Prepare和Execute不是一堆Query。第二件看Com_stmt_prepare和Com_stmt_execute的比值确认缓存真的在复用。第三件看Prepared_stmt_count的走势确认它是平稳的而不是一路向上的。第四件拿一条典型慢查询对比开启前后的耗时用数据而不是感觉做判断。还有一个经验如果你在应用里用的是 ORM 或者数据库中间件它可能自己有一套语句缓存和参数处理逻辑连接串上的那几个参数不一定直接生效。遇到参数配了但日志没变化的情况先去确认框架有没有自己接管参数绑定别在连接串上反复试。最后分享一个我觉得挺实用的小技巧。压测的时候把Com_stmt_prepare和Com_stmt_execute两个计数一起打点画曲线正常情况下两条线应该明显分离execute 的斜率远大于 prepare。如果两条曲线几乎重合、斜率一致那基本可以判定预编译缓存没生效——这个信号比看单条 SQL 的耗时更早、更准而且不受业务流量波动影响。我第一次靠这个信号定位到一个连接池配置问题前后只花了几分钟。
MySQL预编译到底省了什么：从SQL解析到连接池的工程实践
1. 一条 SQL 从客户端到执行器预编译省掉的到底是哪一段很多人对预编译的理解停留在能防注入这一层面试时能答出这个点实际调优时却说不清它到底省了什么开销。我见过的典型场景是一个接口 QPS 上不去开发把连接串加了几个和老老实实做压测待遇差别很大——前者省的是网络往返和重复的解析工作后者省的是重复的解析工作剩下的都得靠索引和 SQL 本身。要是这两件事混在一起谈最后很容易得出预编译没用或者预编译万能这种两头都不对立的结论。先把一条普通 SQL 的完整旅程摆出来。客户端通过连接把 SQL 文本发到服务器服务器先做词法和语法分析把一串字符变成解析树接着做语义检查确认表存在、字段存在、当前账号有没有权限然后优化器接手根据统计信息、索引分布、代价模型挑一条它认为最便宜的执行路径生成执行计划最后执行器拿着计划去调用存储引擎接口把行读出来经过过滤、排序、聚合把结果集回给客户端。这一整套流程里真正和数据有关的只有最后一步前面几步都是在理解和规划你要干什么。问题在于如果同一个 SQL 骨架被反复执行一万次前几步就被重复做了一万次。预编译做的事情就是把这套流程切成两段。第一段在准备好的时候完成SQL 文本发过去服务器做词法语法分析、语义检查、权限校验把结果留在服务器侧返回一个句柄或者语句 ID。第二段在执行的时候完成客户端只发参数服务器把参数填进已经分析好的结构里直接往优化和执行阶段走。省掉的正是把字符串变成解析树这部分重复劳动再加上参数不必拼进 SQL 文本传输上也更紧凑。我更愿意用一个合同模板来类比。没有预编译的情况下每次要签合同你都把整份合同从头起草一遍改掉甲方乙方、金额、日期再交给法务审。预编译则是先把模板定稿、法务审完后面每次只填空白栏。模板本身不用再审了法务的工作量就降下来了。但请注意一件事模板定稿不代表每次填完的内容都最优比如金额那栏填了一个极端值可能触发另一套审批流程——这正好对应执行计划可能因为参数不同而表现不一样的问题。理解了这一段后面几个问题就都好解释了。为什么有的框架开了预编译反而变慢因为它把一次性 SQL 也送进了 Prepare 流程多了一次网络往返和一次服务器侧的对象创建却没有第二次执行来摊薄成本。为什么明明开了预编译general_log 里还是Query而不是Prepare因为你开的只是客户端那个看起来像预编译的东西。这两个坑在后面会各占一节。1.1 解析开销到底占多少值得为它折腾吗这是个必须诚实回答的问题。解析本身很快一条简单的单表查询解析可能只占总耗时的百分之几取决于 SQL 复杂度、表数量和 JOIN 深度。SQL 越长、JOIN 越多、子查询嵌套越深解析的绝对耗时越大。所以在简单查询、超高 QPS和复杂查询、重复执行这两类场景下预编译的收益逻辑是不一样的前者靠的是省掉高频的固定开销后者靠的是省掉大块的分析开销。但解析开销从来不是预编译唯一的价值。更多时候它带来的是另一层收益网络层面的参数分离让每次交互的报文更小尤其是参数很多、字段很宽的场景这个差别能观察到。再就是权限检查、对象查找这些固定动作也被前置了。我做过的压测里简单点查在开启服务端预编译后提升通常是个位数百分比而一条十表 JOIN 的报表查询重复执行时提升会明显得多。所以如果有人拿着预编译没什么用的结论来找你先问他压的是什么语句。1.2 别急着下结论MySQL 在这件事上和 Oracle 的账不一样很多资料讲预编译时会顺带讲 Oracle 的硬解析、软解析那一套一次硬解析生成计划之后全是软解析复用计划绑定变量窥探还可能让计划突变。这套叙事直接搬到 MySQL 上会误导。MySQL 在预编译场景下的优化行为和 Oracle 那种计划缓存 参数窥探的机制不是一回事。按我自己的实测和查文档的经验MySQL 的预编译主要收益落在解析、权限校验这些前置环节执行计划这块的表现更像每次执行都会重新走优化流程只不过优化所用的统计信息可能来自 prepare 时刻。这个差异带来的实际后果有两个。好消息是你基本不用担心参数嗅探导致计划突然变差这类问题同一个 prepared statement 传不同参数优化器会按当前情况重新判断不太会出现一次坏计划锁死整条 SQL的极端情况。坏消息是你也不能指望靠预编译把优化器的工作量省下来这部分该花还是得花。想验证自己手上的版本到底是什么行为最直接的办法是对同一个 prepared statement 用不同参数跑几轮观察耗时和EXPLAIN结果的差异别只看理论。2. 两个层面上的预编译很多人第一步就理解偏了预编译这个词在 MySQL 生态里指代了至少两种不同的东西它们发生的位置、协议报文、性能表现全都不一样。搞混这两层后面所有的排查都会跑偏。第一层是 SQL 语法层面的 prepared statement你可以在 mysql 命令行里直接手写PREPARE stmt FROM SELECT * FROM t WHERE id ?;然后EXECUTE stmt USING id;用完DEALLOCATE PREPARE stmt;。这三个语句是 SQL 语言的一部分普通客户端就能用。它们对应的是一位会话级的对象生命周期跟着会话走。第二层是客户端/服务器协议层面的预编译。MySQL 的通信协议里有一组专门的命令字COM_STMT_PREPARE负责把语句文本送过去换取一个语句 IDCOM_STMT_EXECUTE负责按 ID 传参数执行COM_STMT_CLOSE负责释放。它走的是二进制协议参数和结果集都能用二进制格式传输比文本协议紧凑。你用的 JDBC、各个语言的驱动、连接池操作的都是这一层。2.1 SQL 层的 PREPARE 与协议层的 PREPARE 不是同一回事SQL 层的PREPARE语句本身要通过文本协议发到服务器服务器解析这条语句后再去准备里面那个 SQL 文本最终返回一个名字。整个过程至少两次往返。协议层的COM_STMT_PREPARE是一步到位驱动直接把 SQL 文本按协议格式发过去服务器返回语句 ID 和结果集元信息。这是驱动内部的行为你在 SQL 里看不到。对于日常开发你接触的基本都是协议层。只有做运维排查、写存储过程、或者需要手动验证某个语句能不能 prepare 时SQL 层才派上用场。我经常在排查这条语句到底能不能预编译的时候直接在命令行敲PREPARE出错信息比从驱动里报出来的要清楚得多。2.2 客户端预编译和服务端预编译驱动默认选了后者吗这是最容易踩的坑没有之一。以 JDBC 为例useServerPrepStmts默认是关闭的。默认状态下你调用PreparedStatement驱动做的事是在客户端把?替换成转义后的字面量拼出一条完整 SQL然后用COM_QUERY发出去。从服务器的角度看它收到的永远是一条条不同的完整 SQL压根没有 Prepare 这个动作。这叫客户端预编译或者更准确地说叫客户端参数替换。只有显式打开useServerPrepStmtstrue驱动才会真正调用COM_STMT_PREPARE。这个参数默认关闭是有原因的早期版本服务端预编译在某些场景下收益不明显反而增加了往返和服务器侧资源占用所以驱动选择保守。但如果你用的是批量或高频重复 SQL打开它通常有收益。对比项客户端预编译服务端预编译协议命令COM_QUERYCOM_STMT_PREPARE/COM_STMT_EXECUTE服务器侧解析每次都做Prepare 时做一次参数传输拼进 SQL 文本单独的二进制参数块防注入依赖驱动的转义实现参数与语句天然分离多一次往返否是首次 Prepare典型开关默认useServerPrepStmtstrue其他语言的驱动思路类似Python、Go、Node 各有各的默认值有的默认开启有的默认关闭还有的会根据语句类型动态决定。所以看到MySQL 支持预编译这句话第一反应应该是问是哪一层谁的默认值有没有被连接池改写。3. 占位符挡住注入靠的不是转义而是通道分离讲预编译防注入的资料很多但大多停在用了占位符就安全这个结论上。这个结论在大多数情况下成立但如果你不知道背后的机制很容易在边界场景翻车。拼接式写法的问题在于用户输入和 SQL 语法处在同一个通道里。SELECT * FROM users WHERE name input 当 input 是 OR 11时拼出来的字符串在词法分析阶段就被解释成了新的语法结构原本的字符串常量提前闭合后面的内容变成了条件表达式。这不是字符串处理失误而是数据被赋予了语法的身份。预编译的机制正好相反。SELECT * FROM users WHERE name ?在 Prepare 阶段就被完整解析语法结构固定死了?是一个明确的参数占位符。执行阶段传进来的值走的是参数通道服务器知道这是一个字符串类型的值它从头到尾不会去解析里面的引号和关键字。就算你传的是一整段带引号的 SQL它也只是一个超长的字符串常量匹配不到任何数据仅此而已。3.1 三个真正可能出问题的地方第一是标识符位置不能参数化。表名、列名、ORDER BY的字段、GROUP BY的字段这些是语法结构的一部分占位符放不进去。非要动态的话只能在应用层用白名单校验把允许的字段名映射一遍绝不能直接把用户输入拼进去。这是最容易被忽略的注入点因为很多人以为我用了 PreparedStatement 就万事大吉了。第二是某些数值位置的处理。比如LIMIT ?在 MySQL 里是支持的LIMIT ?, ?也可以但有的框架会因为参数类型推断问题报错需要显式指定整型。这类问题不会造成注入但会让你误以为预编译不可用而退回到拼接。第三是驱动层的转义实现。客户端预编译模式下防注入这一层是由驱动负责的它要做字符集相关的转义处理。如果连接字符集设置得和实际数据字符集不一致理论上存在绕过转义的可能。这也是我倾向于在安全敏感场景打开服务端预编译的原因把安全边界从应用层挪到数据库层少一层信任假设。3.2 明文存储过程里的 SQL 拼接还是老问题有一类场景特别迷惑人存储过程内部用CONCAT拼 SQL 然后PREPARE执行。这在动态表名、动态分区这类场景里很常见。看上去用了PREPARE实际上拼接发生在PREPARE之前参数已经通过CONCAT混进了 SQL 文本语法解析照样会被污染。真正安全的做法是把参数留到EXECUTE ... USING里传能用占位符的地方一个都不要省。4. 把预编译抓现行日志、状态变量与系统表理论讲完得能证明它在你的环境里真的发生了。我排查这类问题的顺序固定是先看日志确认协议命令再看状态变量确认量级最后查系统表确认当前有哪些 prepared statement 挂着。4.1 general_log 里的协议命令打开general_log之后服务器收到的每一条命令都会记下来。客户端用文本协议发 SQL 时你看到的是Query服务端预编译生效时你会看到Prepare和后续的Execute。这条判断极其有效不用猜驱动做了什么日志不会有假。要注意general_log对性能有影响只适合临时打开排查完记得关掉别留在一个高流量的生产实例上。4.2 Com_stmt 系列状态变量SHOW GLOBAL STATUS LIKE Com_stmt%能给你一张全景表。Com_stmt_prepare是 Prepare 的次数Com_stmt_execute是执行次数Com_stmt_close是关闭次数还有Com_stmt_reset、Com_stmt_fetch等。这几个数值的比值很说明问题如果com_stmt_prepare几乎等于com_stmt_execute说明每次执行都在重新 Prepare预编译缓存根本没起作用如果 execute 远大于 prepare说明复用是正常的。另一个必须看的变量是Prepared_stmt_count它反映当前服务器上活着的 prepared statement 总数。这个值持续增长不下降基本可以确定是连接池或者驱动的 PSCache 泄漏或者是某个连接忘了归还。4.3 performance_schema 里的明细performance_schema.prepared_statements_instances这张表在较新的版本里可用它会列出当前每个 prepared statement 归属的连接、语句文本、Prepare 次数、Execute 次数、最长执行耗时等。相比SHOW STATUS的全局计数这张表能定位到具体是哪个连接、哪条 SQL 在反复 Prepare。我第一次用它的时候一眼就发现了某段代码在循环里创建PreparedStatement且没有关闭Prepare 次数是 Execute 次数的整整两倍。4.4 手动敲一遍加深印象-- 准备 PREPARE s1 FROM SELECT id, name FROM users WHERE id ?; -- 执行两次参数不同 SET uid 1001; EXECUTE s1 USING uid; SET uid 1002; EXECUTE s1 USING uid; -- 查看当前会话残留的 prepared statement SELECT * FROM performance_schema.prepared_statements_instances; -- 释放 DEALLOCATE PREPARE s1;注意SQL 层的PREPARE作用域是当前会话换个连接就看不到了。做验证时别在 A 连接准备、B 连接查询。这套手动流程能帮你快速判断某个语句在语法上能不能 Prepare也能直观看到参数是怎么绑定进去的比盯着驱动日志猜要高效得多。5. 预编译的收益边界什么时候它是优化什么时候是负担前面说了它省什么现在说它不省什么以及什么时候是净亏。5.1 收益来源要拆开看别算成一笔糊涂账第一个来源是解析与准备工作的复用这部分在重复执行同一骨架时成立。第二个来源是传输效率参数用二进制编码结果集也可以走二进制格式宽表、多参数场景下报文体积会明显缩小。第三个来源是安全边界的下移这个不体现在性能数字上但在有安全审计要求的场景里很值钱。这三个来源的前提都是重复执行。如果一条 SQL 这辈子只执行一次比如某个定时任务里的汇总语句开服务端预编译只会让你多一次 Prepare 往返外加服务器侧一个对象创建和销毁的成本。我在做批处理脚本的时候遇到全是跑一次的 SQL通常会把这个开关关掉收益为零复杂度为正。5.2 批量写入场景的取舍更微妙JDBC 的批量插入有个rewriteBatchedStatements参数打开之后驱动会把一批INSERT INTO t VALUES (?)重写成一条多值INSERT INTO t VALUES (...), (...), (...)。这个重写带来的性能提升通常比预编译大得多因为它把 N 次网络往返压缩成了一次。但这里有个坑重写之后语句变成了文本协议下的整条 SQL和服务端预编译的路径会产生冲突在某些驱动版本上会出现两个功能互斥实际只生效一个的情况。我的建议是大批量插入场景优先保证rewriteBatchedStatements生效用日志确认改写后的是不是多值语句对于更新和查询类的重复执行再考虑服务端预编译。不要两个都开了就当万事大吉一定要看日志。场景特征建议理由同骨架 SQL 高频重复执行开启服务端预编译摊薄解析开销每条 SQL 只执行一次关闭服务端预编译多一次往返无收益大批量 INSERT优先开启批处理重写减少网络往返收益更大参数极多、字段极宽开启服务端预编译二进制传输更省带宽动态表名、动态排序无法预编译需应用层白名单校验6. 连接池环境下的预编译会话级对象带来的连锁反应单机、单连接的环境把预编译讲清楚并不难真正让线上出问题的是连接池。6.1 prepared statement 是会话私有的这个特性决定了服务器上看到的 prepared statement 数量大致等于活跃连接数 × 每个连接缓存的语句数。一个连接池配了 200 个连接每个连接缓存 50 条语句理论上限就是一万条。如果应用模块多、SQL 种类杂这个数字很容易继续膨胀。所以调整 PSCache 大小的时候不能只看单个连接要把连接数乘进去算总量再看max_prepared_stmt_count的默认上限16382还够不够。6.2 打满之后报什么错max_prepared_stmt_count达到上限之后新的 Prepare 请求会被拒绝报错信息里会出现 Cant create more than max_prepared_stmt_count statements 这样的字样。这个错误很有辨识度但排查时容易跑偏到是不是哪里没关资源上去。更常见的原因是 PSCache 配置过大加上连接数过多或者每个连接都缓存了大量只执行过一次的语句。调小缓存容量、或者把只执行一次的语句从缓存里排除通常就能解决。6.3 PSCache 要开但要算清楚以 JDBC 为例cachePrepStmts控制是否缓存 prepared statementprepStmtCacheSize是每个连接的缓存条目数prepStmtCacheSqlLimit是允许缓存的 SQL 长度上限超长的语句不会被缓存。这套参数要配套使用只开缓存不改容量意义不大。而且这两个参数只有在useServerPrepStmtstrue时才对服务端预编译有意义——如果还是客户端替换模式缓存的是驱动侧的语句对象收益逻辑完全不同这点必须分清楚。6.4 连接被回收或重启之后连接断开时它名下的 prepared statement 会被服务器一并清理掉这本身是好事不会泄漏。但如果你的应用自己维护了一个跨连接的语句映射连接重建之后旧 ID 就失效了继续用它执行会报未知的 prepared statement。这类问题在连接池做故障切换、数据库做重启时最容易冒出来而且往往不是必现排查起来很烦。我一般会在连接获取的回调里加一句状态重置或者直接依赖连接池自带的语句缓存失效逻辑不自己维护。7. 让预编译白干的几种写法7.1 IN 列表占位符数量得固定WHERE id IN (?, ?, ?)只能处理三个值。参数个数不固定的时候很多人的第一反应是在应用层拼出对应数量的?比如有八个参数就拼IN (?,?,?,?,?,?,?,?)。这么做本身没错但要注意两点一是拼出来的 SQL 骨架种类变多缓存命中率下降PSCache 很容易被塞满二是如果参数个数差异很大Prepare 的次数会成倍增长。我的处理方式是按参数个数分档比如 1、5、10、50、100 几个固定档位不足的位置补上不会命中的值或者干脆按 100 一组拆批次。这个做法听着粗糙但在实际系统里比动态拼 SQL 稳定得多。7.2 参数类型不匹配引发的隐式转换这是性能问题里最隐蔽的一类。表上user_id是varchar你传进去的是整型参数服务器在做比较时可能触发隐式类型转换索引就用不上了。预编译本身没问题问题出在参数类型上。表现是逻辑正确压测时慢EXPLAIN一看type从ref掉到了ALL。排查方式很简单把参数类型和列类型逐个对一遍特别注意那些在应用里被包装成字符串的数值字段。同理字符集不一致也会出这类问题。连接字符集和数据表字符集不同的时候比较操作上的转换同样会让索引失效。这类问题的特点是加了索引也没用很容易被误判成索引建错了。7.3 DDL 之后旧计划失效表结构变化、统计信息大幅变动之后之前准备好的语句可能需要重新准备。服务器会处理这类失效但你得知道有这个动作存在。它的一个副作用是在做在线 DDL 的窗口期应用侧可能集中出现一批 Prepare 和 Execute 的波动表现为短时间内的延迟抖动。如果你的监控里有分位数告警会看到 p99 在这个时间点翘一下这是正常的不用急着回滚。7.4 有些语句就是不能 prepare不是所有语句都能走预编译路径某些管理类、会话控制类语句在语法层面就不支持。与其背清单不如实测把语句拿到命令行里敲一遍PREPARE能过就说明语法层支持。如果报语法错误那就别指望驱动能帮你绕过去只能老老实实走文本协议。我在做 SQL 审核的时候会把这类语句单独标记出来配置层面排除掉免得开发在连接串上折腾半天。8. 我自己常用的一套配置与上线前自检配置层面我一般先在测试环境打开服务端预编译配合语句缓存跑一轮真实流量的压测再决定要不要带到生产。参数大致是这样一组useServerPrepStmtstrue打开服务端预编译cachePrepStmtstrue打开语句缓存prepStmtCacheSize按业务 SQL 种类定通常给到 250 到 500 之间prepStmtCacheSqlLimit覆盖到最长的那几条语句避免关键语句被静默排除在缓存之外。这几个数字没有标准答案取决于你的 SQL 种类数量和连接数算完总和再对照上限看。上线前的自检我固定做四件事。第一件在general_log里确认看到的确实是Prepare和Execute不是一堆Query。第二件看Com_stmt_prepare和Com_stmt_execute的比值确认缓存真的在复用。第三件看Prepared_stmt_count的走势确认它是平稳的而不是一路向上的。第四件拿一条典型慢查询对比开启前后的耗时用数据而不是感觉做判断。还有一个经验如果你在应用里用的是 ORM 或者数据库中间件它可能自己有一套语句缓存和参数处理逻辑连接串上的那几个参数不一定直接生效。遇到参数配了但日志没变化的情况先去确认框架有没有自己接管参数绑定别在连接串上反复试。最后分享一个我觉得挺实用的小技巧。压测的时候把Com_stmt_prepare和Com_stmt_execute两个计数一起打点画曲线正常情况下两条线应该明显分离execute 的斜率远大于 prepare。如果两条曲线几乎重合、斜率一致那基本可以判定预编译缓存没生效——这个信号比看单条 SQL 的耗时更早、更准而且不受业务流量波动影响。我第一次靠这个信号定位到一个连接池配置问题前后只花了几分钟。