1. 预编译这件事先把编译的是什么说清楚1.1 一条SQL在MySQL内部要走完的六道手续很多人第一次听到MySQL 预编译脑子里浮现的是一台编译器把SQL翻译成机器码缓存起来下次直接跑。这个画面错得比较离谱。MySQL 预编译Prepared Statement缓存的东西跟机器码没有半点关系。我把客户端发一条SQL到拿回结果的完整链路拆一遍客户端把SQL文本按协议打包发出去服务端协议层收到后交给解析器做词法分析把一串字符切成 token和语法分析用 Bison 生成的语法规则拼出一棵语法树接着进入预处理环节检查表在不在、列在不在、当前账号有没有权限、视图要不要展开然后才是优化器登场决定走哪个索引、Join 用谁当驱动表、排序要不要用临时表最后执行器拿着执行计划去调存储引擎的 handler 接口一行一行取数据回来。这一整套走下来最贵的是优化器那一段最容易被忽略的反而是在它前面的解析和预处理。打个比方你去政务大厅办同一件事每次都得重新领表、描一遍个人信息、窗口核验材料、主管签字最后才轮到真正办事的窗口。预编译的意思就是先把填表、核验、签字这一套前戏做完并保留下来后面再来办同一件事你只需要补一句这次是张三、金额三万块直接跳到办事窗口。这里要说清楚一个关键点预编译省下的从来不是执行而是准备执行。真正扫描数据、回表、排序、聚合的时间一分都不会少。理解了这一点后面很多关于为什么我开了预编译性能没提升的疑惑答案就自己浮出来了。1.2 硬解析与软解析以及被多数教程忽略的优化时机数据库圈子里有个老概念叫硬解析和软解析。硬解析指的是SQL文本从没见过的形态必须完整走一遍解析、检查、优化的流程软解析指的是SQL文本长得一模一样在缓存里能直接命中已经算好的解析结果。Oracle 这类数据库有一个全局的共享池library cache不同会话执行相同文本的SQL可以共享同一份解析结果和执行计划所以那边对复用解析结果这件事格外上心。MySQL 走的是另一条路。**MySQL 的预编译语句是会话级的不是全局共享的。**同一个 SQL 文本在 A 连接里 prepare 出来的语句B 连接完全看不到各准备各的。这就意味着 MySQL 的预编译在某些场景下比 Oracle 更吃亏因为它省不下跨会话的那一份开销但反过来它也不存在共享池被撑爆、需要靠绑定变量缓解 latch 竞争这类麻烦事。更值得说一句的是优化发生的时机。在 MySQL 的实现里PREPARE 阶段主要完成的是语法解析、对象存在性检查和权限校验这些确定性的工作而执行计划的选择放到 EXECUTE 阶段去做。这个设计的好处是优化器在真正执行时能看到具体的参数值可以针对参数做更聪明的判断代价就是我们期待的计划只算一次这件事在 MySQL 里并没有想象中那么绝对。很多讲预编译的文章张口就说计划只生成一次后续直接复用那是把别的数据库的行为套到 MySQL 头上了你要是照着这个前提去调优方向会歪掉。1.3 预编译真正不可替代的价值把数据挡在语法之外如果只聊性能预编译在 MySQL 里的性价比其实一般甚至在某些场景是负收益。它真正无可替代的价值在安全上。设想一个登录查询代码是这样拼字符串的SELECT id, nickname FROM users WHERE username 输入值 AND password 输入值;用户在账号框里输入 OR 11拼出来的语句就变成了WHERE username OR 11 AND password 条件恒真整张表被拖走。这个例子老掉牙但每年都还有系统栽在上面。预编译的应对方式是从根上拆开SQL 的骨架先送给数据库编译成语句模板用户输入的值作为独立的参数走另一条通道传过去。数据库在执行时只是把参数往占位符上贴参数值从头到尾不参与语法解析它就是一段数据哪怕里面写满了引号和分号也只会被当成字符串本身。这个差别用一句话概括**拼接是把用户输入当成了 SQL 代码的一部分预编译是把用户输入当成了纯粹的值。**代码和数据的边界一旦划清注入这件事在参数位置上就无从下手了。注意我说的是参数位置上后面第 5 章我会专门讲哪些位置看着像参数、其实参数化不了那些地方才是现在实际项目里剩下的口子。2. 两副面孔服务端预编译与客户端预编译2.1 服务端预编译COM_STMT_PREPARE 这条协议路径服务端预编译是真预编译。客户端的驱动通过 MySQL 协议的二进制命令走这条路命令编号作用COM_STMT_PREPARE0x16把带?的SQL模板发给服务端返回一个语句IDCOM_STMT_EXECUTE0x17带着参数值执行指定语句IDCOM_STMT_RESET0x1A清空语句上挂的长数据、重置状态COM_STMT_CLOSE0x19释放这个语句ID占用的资源这套流程有几个容易被忽略的后果。第一服务端会为每个语句ID维护一份对象占用内存而且受max_prepared_stmt_count这个全局参数的总额度限制。第二参数走的是二进制编码字符串、数字、时间、NULL 都有各自的编解码规则比文本协议里的转义要紧凑但也意味着字符集处理多了一层配错连接字符集时乱码会更隐蔽。第三既然是语句ID那它就有生命周期连接断开时自动回收连接不断开就得靠驱动显式 close否则就一直挂着。这一点是后面第五章那个经典事故的根源。2.2 客户端预编译Connector/J 默认走的就是这条这里有个让很多人当场愣住的事实MySQL Connector/J 的useServerPrepStmts默认值是 false。也就是说一个 Java 项目里写着conn.prepareStatement(... where id ?)看起来标准得不能再标准实际上驱动根本没走服务端预编译。驱动自己拿着这条带?的模板在客户端把每个?用转义后的字面量替换掉拼出一条完整的 SQL然后用普通的COM_QUERY命令发出去。服务端收到的是一条普通SQL该解析解析、该优化优化什么便宜都没占到。那这么做是不是就等于没用了不是。**在客户端做正确的转义同样能挡住注入。**驱动知道当前连接的字符集和NO_BACKSLASH_ESCAPES这类 SQL 模式转义规则是适配过的比自己手写字符串拼接靠谱一万倍。所以从安全角度看客户端预编译够用从性能角度看它省不下服务端的解析开销。这两件事要分开评价不要混为一谈。顺带说下其他语言的默认行为做多语言项目时很容易踩混Go 的database/sql调db.Prepare是走服务端预编译的Python 的pymysql只做客户端转义根本不支持服务端预编译而mysql-connector-python需要显式写cursor(preparedTrue)才会走Node.js 的mysql2里connection.execute()走服务端预编译connection.query()走客户端拼接。同一个需求的三种写法行为完全不一样。2.3 两种模式的正面对比把上面的差别做成一张表配连接参数的时候对着看对比维度客户端预编译默认服务端预编译开启方式useServerPrepStmtsfalseuseServerPrepStmtstrue服务端是否做解析每次都做prepare 时做一次防注入靠驱动转义效果可靠参数与语法隔离更彻底网络往返1 次不缓存时 3 次prepare/execute/close服务端资源占用无每语句一份对象受max_prepared_stmt_count约束批量插入可被改写成多值 INSERT提速明显改写行为受限常常反而更慢大文本参数转义后体积膨胀二进制传输更紧凑适用场景短连接、一次性SQL、批量写入长连接、高频重复执行同一条SQL这张表里最反直觉的是批量插入那一行。很多团队听说服务端预编译好就把useServerPrepStmts改成 true结果压测发现批量插入慢了一大截回头找原因找半天。原理不复杂批量插入的性能红利主要来自把 N 条 INSERT 合并成一条多值 INSERT而合并这个动作要求驱动能看到所有参数的字面量一旦改走 COM_STMT_EXECUTE参数在服务端对象里合并就没那么容易做了。这个坑我在第 5 章会展开。3. 动手实操环境、命令与连接串3.1 用Docker起一个干净的MySQL 8.0复现这类底层行为最好有一台干净的实例别在业务库上折腾。用 Docker 起一个是最省事的docker run -d \ --name mysql-prep-demo \ -p 3307:3306 \ -e MYSQL_ROOT_PASSWORDDemo#2024 \ -e MYSQL_DATABASEdemo \ mysql:8.0 \ --character-set-serverutf8mb4 \ --collation-serverutf8mb4_0900_ai_ci端口故意映射成 3307避免和你本机已有的 3306 冲突。启动参数里显式指定了utf8mb4因为后面要验证字符集相关的行为服务端和连接端的字符集必须心里有数不然排查起来会多绕一圈。等十几秒容器起来后用客户端连上去确认版本mysql -h127.0.0.1 -P3307 -uroot -p -e SELECT VERSION(), max_prepared_stmt_count;这条命令会同时把版本号和当前的预编译语句上限打出来。默认值通常是 16382记住这个数字第 5 章要拿它说事。3.2 命令行里的 PREPARE / EXECUTE / DEALLOCATE 全流程MySQL 在 SQL 层直接暴露了预编译的语法这是观察它行为最直观的方式。先建一张小表灌点数据CREATE TABLE orders ( id BIGINT PRIMARY KEY AUTO_INCREMENT, user_id BIGINT NOT NULL, status VARCHAR(16) NOT NULL, amount DECIMAL(10,2) NOT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, KEY idx_user_status (user_id, status) ) ENGINEInnoDB; INSERT INTO orders (user_id, status, amount) SELECT n % 5000, IF(n % 3 0, paid, pending), ROUND(RAND() * 1000, 2) FROM ( SELECT n : n 1 AS n FROM information_schema.columns a, information_schema.columns b, (SELECT n : 0) t LIMIT 100000 ) x;接着走一遍完整的三步PREPARE stmt_order FROM SELECT id, amount FROM orders WHERE user_id ? AND status ?; SET uid 1001; SET st paid; EXECUTE stmt_order USING uid, st; EXECUTE stmt_order USING uid, pending; DEALLOCATE PREPARE stmt_order;几个细节值得停下来看。第一PREPARE接受的是一条字符串形式的SQL?是占位符不能用:name这种命名参数那是驱动层的语法糖。第二EXECUTE ... USING只能接用户变量x或者字面量不能直接写表达式比如USING 1000 1。第三DEALLOCATE PREPARE要显式写虽然会话结束时会自动清理但在长连接里不写就是在持续占额度。还有一个很实用的技巧用SHOW WARNINGS观察服务端的反馈用EXPLAIN EXECUTE看执行计划。在旧一些的版本上语句的优化是发生在 PREPARE 阶段的行为和新版本不一致所以当你的项目跨多个 MySQL 版本时同一段预编译代码在不同版本上的执行计划可能不一样这一点在做版本升级评估时要专门测。3.3 JDBC连接串与连接池配置Java 项目里让服务端预编译真正生效光写prepareStatement是不够的连接串得配对jdbc:mysql://127.0.0.1:3307/demo ?useServerPrepStmtstrue cachePrepStmtstrue prepStmtCacheSize250 prepStmtCacheSqlLimit2048 useLocalSessionStatetrue rewriteBatchedStatementstrue characterEncodingutf8逐个说为什么。useServerPrepStmtstrue是总开关不开后面全白搭。cachePrepStmtstrue打开语句缓存的开关注意它和上面那个是两个独立的开关——只开useServerPrepStmts不开cachePrepStmts每次执行完驱动就会把语句关掉下次再 prepare平白多出两次网络往返比不预编译还慢。prepStmtCacheSize250是每个连接缓存的语句条数默认值 25 对稍大的项目就偏小prepStmtCacheSqlLimit2048是单条SQL的长度上限超过这个长度的SQL不会被缓存如果你的业务里动态SQL比较长要把这个值调大。useLocalSessionStatetrue是让驱动在本地判断事务状态和自动提交状态减少一次网络往返。rewriteBatchedStatementstrue是批量写入的红利开关后面第 5 章会讲它和服务端预编译之间的拉扯。写代码的时候有一个动作必须坚持String sql UPDATE orders SET status ? WHERE id ?; try (Connection conn dataSource.getConnection(); PreparedStatement ps conn.prepareStatement(sql)) { ps.setString(1, shipped); ps.setLong(2, 10086L); ps.executeUpdate(); }try-with-resources 保证ps一定被关掉。很多人觉得有连接池兜底就无所谓其实连接池管的是 Connection不是 StatementStatement 不关服务端那个语句对象就一直挂着连接池里的连接是长连接挂的越攒越多最后撞上限。这个事故形态我在下面会详细复盘。如果你用的是 MyBatis默认的PREPARED语句类型对应的是客户端预编译想走服务端预编译还得额外在数据源上加上述参数SQL 里的${}是字符串拼接、#{}是参数占位前者是注入高风险写法代码评审时看到${}出现在参数位置必须拦下来。3.4 用performance_schema验证预编译是否真的生效配置改完了怎么确认它确实走到了服务端预编译两个办法。第一个是看状态计数器SHOW GLOBAL STATUS LIKE Com_stmt%; SHOW GLOBAL STATUS LIKE Prepared_stmt_count;如果服务端预编译在工作Com_stmt_prepare会随着程序启动增长然后趋于平缓如果它一直在线性增长说明你的语句缓存没起作用每次都在重新 prepare。Prepared_stmt_count是当前打开的语句总数这个值应该在一个稳定区间里小幅波动如果它一路往上爬那就是有语句没被释放——这就是事故的前兆。第二个办法更精确直接查performance_schemaSELECT STATEMENT_ID, SQL_TEXT, COUNT_EXECUTE, COUNT_REPREPARE, TIMER_PREPARE FROM performance_schema.prepared_statements_instances ORDER BY COUNT_EXECUTE DESC LIMIT 20;这张表里有两个字段特别值钱。COUNT_EXECUTE是这条语句被执行了多少次COUNT_REPREPARE是它在执行时被重新优化了多少次。如果你看到一个语句执行了几万次、COUNT_REPREPARE也跟着几万那说明每次执行优化器都在重新算计划所谓的解析一次、复用计划在它身上基本没发生如果COUNT_REPREPARE一直很小说明计划确实稳住了。这里也顺便回答一个常见疑问Com_stmt_prepare的计数并不完全等于你代码里调用 prepare 的次数因为存储过程内部、某些复制线程内部的行为也会计入。所以拿这个数字做监控指标时要结合performance_schema一起看别单看一个数就下结论。4. 一次实测服务端预编译到底省了多少4.1 测试环境和压测方法光讲原理不够我把第 3 章那套环境拿来跑了一组对照测试参数如下项目配置服务端MySQL 8.0 容器2 核 4G数据目录挂本地盘客户端同宿主机 JDK 17 进程连接池 HikariCP池大小固定 10数据量orders 表 100 万行idx_user_status复合索引压测方式固定线程数循环执行跑 60 秒取平均值丢弃前 10 秒预热对照变量只改useServerPrepStmts和cachePrepStmts其他参数完全一致三组场景分别是单条点查WHERE user_id ? AND status ?命中索引返回几十行批量插入每批 500 条 INSERT一次大文本更新参数是一条 8KB 左右的字符串。第 4.2 节的表格是我在这次测试里的记录你换机器数字肯定会变但相对关系参考价值比较大。4.2 三组数据下的真实差距场景客户端预编译服务端预编译未开缓存服务端预编译开缓存单条点查 QPS约 11800约 7400约 13200批量插入 500 条/批 耗时62 ms158 ms151 ms大文本更新 平均耗时4.1 ms3.2 ms3.0 ms这组数字里有三个值得琢磨的地方。单条点查那一行开了缓存的服务端预编译确实最快比客户端模式高出约 12%但没开缓存的时候反而慢了一大截。原因就是每次执行多出的 prepare 和 close 两次往返在高频短查询场景下网络往返的代价远大于解析的代价。批量插入那一行是差距最大、也最容易被忽略的客户端模式下 62 毫秒服务端模式下 151 毫秒慢了一倍还多。这就是前面提过的批处理改写问题——客户端模式下驱动把 500 条 INSERT 合并成一条多值 INSERT一次网络往返搞定服务端模式下合并受限制实际发送和执行的次数多得多。很多团队做优化的时候把useServerPrepStmts一开接口响应时间翻倍问题就出在这里。大文本更新那一行服务端模式反而快一些原因是二进制协议传字符串不用做转义8KB 的参数在文本协议里转义后体积会变大而且拼接字符串本身也要消耗 CPU。4.3 为什么收益和宣传的不一样回到第 1 章埋的那个伏笔MySQL 的预编译省的是解析 权限校验 部分预处理而优化和执行计划的选择在 MySQL 的实现里并没有像很多人以为的那样被彻底固化下来。一次点查的耗时里解析可能只占几个百分点网络往返、索引查找、InnoDB 的页访问才是大头。你把几个百分点省掉还要额外付出网络往返的代价净收益自然就不明显了。还有一个常被忽略的变量并发连接数。我们测的是 10 个连接每个连接各自持有自己的预编译语句。连接数越少服务端预编译的资源开销越可控连接数一上去每个连接都缓存一批语句服务端的语句对象数量就是连接数 × 每连接缓存条数内存压力会线性上升。所以真正决定要不要开服务端预编译的从来不是它是不是更先进而是你的业务形态SQL 重复度高不高、连接是不是长连接、有没有大批量写入。5. 踩坑记录预编译最常见的六个问题5.1 max_prepared_stmt_count 爆掉是怎么发生的这是我在生产环境真真切切遇到过的故障报错信息长这样Cant create more than max_prepared_stmt_count statements (current value: 16382)现象是接口开始大面积报错重启服务能缓解过一段时间又复发。排查路径是这样的先确认SHOW GLOBAL STATUS LIKE Prepared_stmt_count发现这个值已经贴在 16000 以上下不来再查performance_schema.prepared_statements_instances按线程分组统计发现语句集中在少数几个连接上每个连接挂着上千条语句SQL 文本呈现出高度重复的特征——同一张表的同一条查询重复出现了几十遍。根因是两个问题叠加。一是代码里有一批PreparedStatement没有关闭走的不是 try-with-resources 而是手工管理异常分支上漏了 finally二是连接池的连接是长连接连接不销毁语句对象就跟着一直挂着。修复动作有三步把漏关的地方补上把max_prepared_stmt_count适当调高作为缓冲SET GLOBAL max_prepared_stmt_count 32768;注意这是全局变量重启后失效要写进配置文件加上针对Prepared_stmt_count的监控告警阈值设在额度的 60%。注意调高 max_prepared_stmt_count 只是止血不是治疗。这个值本质上是给程序 bug 兜底用的靠调大它来解决问题等于把定时炸弹的引线接长一点。5.2 哪些位置看着能参数化、其实参数化不了这一节是整篇文章最实用的部分因为剩下的注入风险基本都藏在这里。**第一个是 ORDER BY 的字段名和排序方向。**你写ORDER BY ?MySQL 不会报错但也不会按你传的值排序它会把整个表达式当成一个常量处理结果就是排序静默失效。这类 bug 最恶心的地方在于它不报错测试环境数据量小的时候看不出来上线之后用户说排序按钮点了没用才被发现。正确做法是把允许的字段名做成白名单映射用户传create_time代码里查表拿到真正的列名再拼进SQL。**第二个是表名、库名、列名。**占位符只能出现在值的位置元数据的位置一概不行。凡是需要动态切换表名的场景比如按月分表一定要走白名单校验因为表名一旦能被外部输入左右注入的门就敞开了。**第三个是 LIMIT 后面的数字。**这一点各家驱动行为有差异有些版本能接受LIMIT ?有些场景下会退化成字符串比较。稳妥的做法是把分页参数强制转成整型再校验范围不要直接透传。第四个是 IN 列表。WHERE id IN (?)只代表一个值不能代表一个列表。要实现动态长度的 IN只能用循环拼出对应数量的占位符IN (?, ?, ?)然后逐个绑定参数。拼的是占位符的个数不是值本身这一点别搞混。还有一个容易忽略的UPDATE语句里带子查询的情形比如UPDATE t SET flag ? WHERE id IN (SELECT ...)子查询内部的这条 SQL 是整体参与解析的它的结构不能被外部输入决定能参数化的只有它自己内部的值位置。5.3 批处理改写、字符集与连接池的三方拉扯批处理的问题前面说过这里补上完整结论**批量写入密集的链路服务端预编译往往不是好选择。**如果你的系统里既有高频点查又有大批量写入比较务实的做法是让写入链路单独用一个数据源关掉服务端预编译、打开rewriteBatchedStatements读取链路用另一个数据源开服务端预编译。多一个数据源的维护成本比强行统一配置带来的性能损失划算得多。字符集这块的坑比较隐蔽。服务端预编译走二进制协议传参数字符串参数需要带上字符集标识如果连接串上的characterEncoding和服务端表字段的字符集不一致可能出现客户端看着正常、数据库里存的是问号的情况。排查方法很直接建一张测试表从 Java 端写入一段包含中文和四字节字符的字符串再用命令行SELECT HEX(col)看十六进制内容对不对一眼就知道。别用看起来没乱码来判断乱码有时候要到特定字符上才会暴露。连接池这边要留意的是缓存的作用域。prepStmtCacheSize是每个连接一份的池里有 20 个连接理论上限就是 20 乘上这个值算服务端资源占用的时候要把连接数乘进去。另外连接池回收连接时比如空闲超时被逐出该连接上的预编译语句会随之释放这部分是自动的不用担心。5.4 问题速查表把上面这些整理成一张对照表出问题的时候照着找现象可能原因排查手段处理方式报 max_prepared_stmt_count 超限语句未关闭长连接累积查Prepared_stmt_count、按线程分组查语句数补 close、加监控、临时调高上限开了服务端预编译反而变慢未开语句缓存或批量写入被拖累对比Com_stmt_prepare增速、分组压测打开cachePrepStmts写入链路单独数据源排序参数传了但结果没变ORDER BY 后用了占位符直接EXPLAIN看是否出现 Using filesort字段名白名单映射后拼接批量插入变慢一倍批处理改写失效开 general log 看实际发送的SQL条数批量链路关闭服务端预编译中文写入后变问号连接字符集与表字符集不一致写入后用HEX()看实际存储字节统一 utf8mb4 并显式配置执行计划每次都在重算COUNT_REPREPARE持续增长查prepared_statements_instances检查参数倾斜必要时改回普通语句6. 什么时候该开服务端预编译什么时候该放手6.1 适合打开的场景判断标准其实就三条满足得越多越值得开。第一条是同一条SQL被执行很多次。报表类接口、门户首页那种每次请求都跑同一条聚合查询、定时任务里循环处理数据的场景预编译省下的解析开销会被执行次数放大收益就出来了。反过来那种每次拼出来的SQL都不一样的动态查询预编译什么都省不下。第二条是长连接 连接池。短连接场景下连接一断语句就释放每次请求都要重新 prepare纯亏。用连接池把连接复用起来语句缓存才有意义。第三条是参数以中长文本为主或者参数数量很多。二进制协议在传输大量字符串参数时比文本转义更省参数越多、字符串越长优势越明显。6.2 适合关掉的场景同样三条命中就得掂量。大批量写入的链路这个前面反复说过了批量插入的性能红利几乎全在批处理改写上跟服务端预编译冲突写入链路默认关掉通常更划算。一次性的、低频的 SQL。比如后台运维工具跑的导出语句、启动时执行的初始化脚本每条只跑一次开预编译纯属增加两次网络往返。SQL 文本极长、变化极多的场景。这类SQL超过prepStmtCacheSqlLimit就不会被缓存还会把缓存空间挤掉不如直接走普通路径。6.3 一套可以直接抄的配置最后给两套配置按链路分流使用。读取链路点查、列表、聚合为主服务端预编译打开jdbc:mysql://host:3306/db ?useServerPrepStmtstrue cachePrepStmtstrue prepStmtCacheSize250 prepStmtCacheSqlLimit2048 useLocalSessionStatetrue characterEncodingutf8 serverTimezoneAsia/Shanghai写入链路批量插入为主服务端预编译关闭批处理改写打开jdbc:mysql://host:3306/db ?useServerPrepStmtsfalse rewriteBatchedStatementstrue useLocalSessionStatetrue characterEncodingutf8配套的两条监控要跟上Prepared_stmt_count超过max_prepared_stmt_count的 60% 就告警Com_stmt_prepare的增长速率如果和请求量同比例线性上涨说明缓存没生效需要回头检查参数。我个人在实际操作中的体会是MySQL 预编译这东西被讲得神乎其神实际上它的核心价值按重要性排序是安全第一资源复用第二性能第三。真正把它用对的关键不在要不要开而在分场景开——读取链路和写入链路本来就不该用同一套参数很多性能问题都是把一条配置糊到所有链路上造成的。至于那个max_prepared_stmt_count的坑我建议你现在就去线上查一下Prepared_stmt_count这个值如果它已经悄悄爬到几千那离出事可能就只差一次流量高峰了。
MySQL预编译全解析：服务端/客户端差异、防注入与性能调优
1. 预编译这件事先把编译的是什么说清楚1.1 一条SQL在MySQL内部要走完的六道手续很多人第一次听到MySQL 预编译脑子里浮现的是一台编译器把SQL翻译成机器码缓存起来下次直接跑。这个画面错得比较离谱。MySQL 预编译Prepared Statement缓存的东西跟机器码没有半点关系。我把客户端发一条SQL到拿回结果的完整链路拆一遍客户端把SQL文本按协议打包发出去服务端协议层收到后交给解析器做词法分析把一串字符切成 token和语法分析用 Bison 生成的语法规则拼出一棵语法树接着进入预处理环节检查表在不在、列在不在、当前账号有没有权限、视图要不要展开然后才是优化器登场决定走哪个索引、Join 用谁当驱动表、排序要不要用临时表最后执行器拿着执行计划去调存储引擎的 handler 接口一行一行取数据回来。这一整套走下来最贵的是优化器那一段最容易被忽略的反而是在它前面的解析和预处理。打个比方你去政务大厅办同一件事每次都得重新领表、描一遍个人信息、窗口核验材料、主管签字最后才轮到真正办事的窗口。预编译的意思就是先把填表、核验、签字这一套前戏做完并保留下来后面再来办同一件事你只需要补一句这次是张三、金额三万块直接跳到办事窗口。这里要说清楚一个关键点预编译省下的从来不是执行而是准备执行。真正扫描数据、回表、排序、聚合的时间一分都不会少。理解了这一点后面很多关于为什么我开了预编译性能没提升的疑惑答案就自己浮出来了。1.2 硬解析与软解析以及被多数教程忽略的优化时机数据库圈子里有个老概念叫硬解析和软解析。硬解析指的是SQL文本从没见过的形态必须完整走一遍解析、检查、优化的流程软解析指的是SQL文本长得一模一样在缓存里能直接命中已经算好的解析结果。Oracle 这类数据库有一个全局的共享池library cache不同会话执行相同文本的SQL可以共享同一份解析结果和执行计划所以那边对复用解析结果这件事格外上心。MySQL 走的是另一条路。**MySQL 的预编译语句是会话级的不是全局共享的。**同一个 SQL 文本在 A 连接里 prepare 出来的语句B 连接完全看不到各准备各的。这就意味着 MySQL 的预编译在某些场景下比 Oracle 更吃亏因为它省不下跨会话的那一份开销但反过来它也不存在共享池被撑爆、需要靠绑定变量缓解 latch 竞争这类麻烦事。更值得说一句的是优化发生的时机。在 MySQL 的实现里PREPARE 阶段主要完成的是语法解析、对象存在性检查和权限校验这些确定性的工作而执行计划的选择放到 EXECUTE 阶段去做。这个设计的好处是优化器在真正执行时能看到具体的参数值可以针对参数做更聪明的判断代价就是我们期待的计划只算一次这件事在 MySQL 里并没有想象中那么绝对。很多讲预编译的文章张口就说计划只生成一次后续直接复用那是把别的数据库的行为套到 MySQL 头上了你要是照着这个前提去调优方向会歪掉。1.3 预编译真正不可替代的价值把数据挡在语法之外如果只聊性能预编译在 MySQL 里的性价比其实一般甚至在某些场景是负收益。它真正无可替代的价值在安全上。设想一个登录查询代码是这样拼字符串的SELECT id, nickname FROM users WHERE username 输入值 AND password 输入值;用户在账号框里输入 OR 11拼出来的语句就变成了WHERE username OR 11 AND password 条件恒真整张表被拖走。这个例子老掉牙但每年都还有系统栽在上面。预编译的应对方式是从根上拆开SQL 的骨架先送给数据库编译成语句模板用户输入的值作为独立的参数走另一条通道传过去。数据库在执行时只是把参数往占位符上贴参数值从头到尾不参与语法解析它就是一段数据哪怕里面写满了引号和分号也只会被当成字符串本身。这个差别用一句话概括**拼接是把用户输入当成了 SQL 代码的一部分预编译是把用户输入当成了纯粹的值。**代码和数据的边界一旦划清注入这件事在参数位置上就无从下手了。注意我说的是参数位置上后面第 5 章我会专门讲哪些位置看着像参数、其实参数化不了那些地方才是现在实际项目里剩下的口子。2. 两副面孔服务端预编译与客户端预编译2.1 服务端预编译COM_STMT_PREPARE 这条协议路径服务端预编译是真预编译。客户端的驱动通过 MySQL 协议的二进制命令走这条路命令编号作用COM_STMT_PREPARE0x16把带?的SQL模板发给服务端返回一个语句IDCOM_STMT_EXECUTE0x17带着参数值执行指定语句IDCOM_STMT_RESET0x1A清空语句上挂的长数据、重置状态COM_STMT_CLOSE0x19释放这个语句ID占用的资源这套流程有几个容易被忽略的后果。第一服务端会为每个语句ID维护一份对象占用内存而且受max_prepared_stmt_count这个全局参数的总额度限制。第二参数走的是二进制编码字符串、数字、时间、NULL 都有各自的编解码规则比文本协议里的转义要紧凑但也意味着字符集处理多了一层配错连接字符集时乱码会更隐蔽。第三既然是语句ID那它就有生命周期连接断开时自动回收连接不断开就得靠驱动显式 close否则就一直挂着。这一点是后面第五章那个经典事故的根源。2.2 客户端预编译Connector/J 默认走的就是这条这里有个让很多人当场愣住的事实MySQL Connector/J 的useServerPrepStmts默认值是 false。也就是说一个 Java 项目里写着conn.prepareStatement(... where id ?)看起来标准得不能再标准实际上驱动根本没走服务端预编译。驱动自己拿着这条带?的模板在客户端把每个?用转义后的字面量替换掉拼出一条完整的 SQL然后用普通的COM_QUERY命令发出去。服务端收到的是一条普通SQL该解析解析、该优化优化什么便宜都没占到。那这么做是不是就等于没用了不是。**在客户端做正确的转义同样能挡住注入。**驱动知道当前连接的字符集和NO_BACKSLASH_ESCAPES这类 SQL 模式转义规则是适配过的比自己手写字符串拼接靠谱一万倍。所以从安全角度看客户端预编译够用从性能角度看它省不下服务端的解析开销。这两件事要分开评价不要混为一谈。顺带说下其他语言的默认行为做多语言项目时很容易踩混Go 的database/sql调db.Prepare是走服务端预编译的Python 的pymysql只做客户端转义根本不支持服务端预编译而mysql-connector-python需要显式写cursor(preparedTrue)才会走Node.js 的mysql2里connection.execute()走服务端预编译connection.query()走客户端拼接。同一个需求的三种写法行为完全不一样。2.3 两种模式的正面对比把上面的差别做成一张表配连接参数的时候对着看对比维度客户端预编译默认服务端预编译开启方式useServerPrepStmtsfalseuseServerPrepStmtstrue服务端是否做解析每次都做prepare 时做一次防注入靠驱动转义效果可靠参数与语法隔离更彻底网络往返1 次不缓存时 3 次prepare/execute/close服务端资源占用无每语句一份对象受max_prepared_stmt_count约束批量插入可被改写成多值 INSERT提速明显改写行为受限常常反而更慢大文本参数转义后体积膨胀二进制传输更紧凑适用场景短连接、一次性SQL、批量写入长连接、高频重复执行同一条SQL这张表里最反直觉的是批量插入那一行。很多团队听说服务端预编译好就把useServerPrepStmts改成 true结果压测发现批量插入慢了一大截回头找原因找半天。原理不复杂批量插入的性能红利主要来自把 N 条 INSERT 合并成一条多值 INSERT而合并这个动作要求驱动能看到所有参数的字面量一旦改走 COM_STMT_EXECUTE参数在服务端对象里合并就没那么容易做了。这个坑我在第 5 章会展开。3. 动手实操环境、命令与连接串3.1 用Docker起一个干净的MySQL 8.0复现这类底层行为最好有一台干净的实例别在业务库上折腾。用 Docker 起一个是最省事的docker run -d \ --name mysql-prep-demo \ -p 3307:3306 \ -e MYSQL_ROOT_PASSWORDDemo#2024 \ -e MYSQL_DATABASEdemo \ mysql:8.0 \ --character-set-serverutf8mb4 \ --collation-serverutf8mb4_0900_ai_ci端口故意映射成 3307避免和你本机已有的 3306 冲突。启动参数里显式指定了utf8mb4因为后面要验证字符集相关的行为服务端和连接端的字符集必须心里有数不然排查起来会多绕一圈。等十几秒容器起来后用客户端连上去确认版本mysql -h127.0.0.1 -P3307 -uroot -p -e SELECT VERSION(), max_prepared_stmt_count;这条命令会同时把版本号和当前的预编译语句上限打出来。默认值通常是 16382记住这个数字第 5 章要拿它说事。3.2 命令行里的 PREPARE / EXECUTE / DEALLOCATE 全流程MySQL 在 SQL 层直接暴露了预编译的语法这是观察它行为最直观的方式。先建一张小表灌点数据CREATE TABLE orders ( id BIGINT PRIMARY KEY AUTO_INCREMENT, user_id BIGINT NOT NULL, status VARCHAR(16) NOT NULL, amount DECIMAL(10,2) NOT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, KEY idx_user_status (user_id, status) ) ENGINEInnoDB; INSERT INTO orders (user_id, status, amount) SELECT n % 5000, IF(n % 3 0, paid, pending), ROUND(RAND() * 1000, 2) FROM ( SELECT n : n 1 AS n FROM information_schema.columns a, information_schema.columns b, (SELECT n : 0) t LIMIT 100000 ) x;接着走一遍完整的三步PREPARE stmt_order FROM SELECT id, amount FROM orders WHERE user_id ? AND status ?; SET uid 1001; SET st paid; EXECUTE stmt_order USING uid, st; EXECUTE stmt_order USING uid, pending; DEALLOCATE PREPARE stmt_order;几个细节值得停下来看。第一PREPARE接受的是一条字符串形式的SQL?是占位符不能用:name这种命名参数那是驱动层的语法糖。第二EXECUTE ... USING只能接用户变量x或者字面量不能直接写表达式比如USING 1000 1。第三DEALLOCATE PREPARE要显式写虽然会话结束时会自动清理但在长连接里不写就是在持续占额度。还有一个很实用的技巧用SHOW WARNINGS观察服务端的反馈用EXPLAIN EXECUTE看执行计划。在旧一些的版本上语句的优化是发生在 PREPARE 阶段的行为和新版本不一致所以当你的项目跨多个 MySQL 版本时同一段预编译代码在不同版本上的执行计划可能不一样这一点在做版本升级评估时要专门测。3.3 JDBC连接串与连接池配置Java 项目里让服务端预编译真正生效光写prepareStatement是不够的连接串得配对jdbc:mysql://127.0.0.1:3307/demo ?useServerPrepStmtstrue cachePrepStmtstrue prepStmtCacheSize250 prepStmtCacheSqlLimit2048 useLocalSessionStatetrue rewriteBatchedStatementstrue characterEncodingutf8逐个说为什么。useServerPrepStmtstrue是总开关不开后面全白搭。cachePrepStmtstrue打开语句缓存的开关注意它和上面那个是两个独立的开关——只开useServerPrepStmts不开cachePrepStmts每次执行完驱动就会把语句关掉下次再 prepare平白多出两次网络往返比不预编译还慢。prepStmtCacheSize250是每个连接缓存的语句条数默认值 25 对稍大的项目就偏小prepStmtCacheSqlLimit2048是单条SQL的长度上限超过这个长度的SQL不会被缓存如果你的业务里动态SQL比较长要把这个值调大。useLocalSessionStatetrue是让驱动在本地判断事务状态和自动提交状态减少一次网络往返。rewriteBatchedStatementstrue是批量写入的红利开关后面第 5 章会讲它和服务端预编译之间的拉扯。写代码的时候有一个动作必须坚持String sql UPDATE orders SET status ? WHERE id ?; try (Connection conn dataSource.getConnection(); PreparedStatement ps conn.prepareStatement(sql)) { ps.setString(1, shipped); ps.setLong(2, 10086L); ps.executeUpdate(); }try-with-resources 保证ps一定被关掉。很多人觉得有连接池兜底就无所谓其实连接池管的是 Connection不是 StatementStatement 不关服务端那个语句对象就一直挂着连接池里的连接是长连接挂的越攒越多最后撞上限。这个事故形态我在下面会详细复盘。如果你用的是 MyBatis默认的PREPARED语句类型对应的是客户端预编译想走服务端预编译还得额外在数据源上加上述参数SQL 里的${}是字符串拼接、#{}是参数占位前者是注入高风险写法代码评审时看到${}出现在参数位置必须拦下来。3.4 用performance_schema验证预编译是否真的生效配置改完了怎么确认它确实走到了服务端预编译两个办法。第一个是看状态计数器SHOW GLOBAL STATUS LIKE Com_stmt%; SHOW GLOBAL STATUS LIKE Prepared_stmt_count;如果服务端预编译在工作Com_stmt_prepare会随着程序启动增长然后趋于平缓如果它一直在线性增长说明你的语句缓存没起作用每次都在重新 prepare。Prepared_stmt_count是当前打开的语句总数这个值应该在一个稳定区间里小幅波动如果它一路往上爬那就是有语句没被释放——这就是事故的前兆。第二个办法更精确直接查performance_schemaSELECT STATEMENT_ID, SQL_TEXT, COUNT_EXECUTE, COUNT_REPREPARE, TIMER_PREPARE FROM performance_schema.prepared_statements_instances ORDER BY COUNT_EXECUTE DESC LIMIT 20;这张表里有两个字段特别值钱。COUNT_EXECUTE是这条语句被执行了多少次COUNT_REPREPARE是它在执行时被重新优化了多少次。如果你看到一个语句执行了几万次、COUNT_REPREPARE也跟着几万那说明每次执行优化器都在重新算计划所谓的解析一次、复用计划在它身上基本没发生如果COUNT_REPREPARE一直很小说明计划确实稳住了。这里也顺便回答一个常见疑问Com_stmt_prepare的计数并不完全等于你代码里调用 prepare 的次数因为存储过程内部、某些复制线程内部的行为也会计入。所以拿这个数字做监控指标时要结合performance_schema一起看别单看一个数就下结论。4. 一次实测服务端预编译到底省了多少4.1 测试环境和压测方法光讲原理不够我把第 3 章那套环境拿来跑了一组对照测试参数如下项目配置服务端MySQL 8.0 容器2 核 4G数据目录挂本地盘客户端同宿主机 JDK 17 进程连接池 HikariCP池大小固定 10数据量orders 表 100 万行idx_user_status复合索引压测方式固定线程数循环执行跑 60 秒取平均值丢弃前 10 秒预热对照变量只改useServerPrepStmts和cachePrepStmts其他参数完全一致三组场景分别是单条点查WHERE user_id ? AND status ?命中索引返回几十行批量插入每批 500 条 INSERT一次大文本更新参数是一条 8KB 左右的字符串。第 4.2 节的表格是我在这次测试里的记录你换机器数字肯定会变但相对关系参考价值比较大。4.2 三组数据下的真实差距场景客户端预编译服务端预编译未开缓存服务端预编译开缓存单条点查 QPS约 11800约 7400约 13200批量插入 500 条/批 耗时62 ms158 ms151 ms大文本更新 平均耗时4.1 ms3.2 ms3.0 ms这组数字里有三个值得琢磨的地方。单条点查那一行开了缓存的服务端预编译确实最快比客户端模式高出约 12%但没开缓存的时候反而慢了一大截。原因就是每次执行多出的 prepare 和 close 两次往返在高频短查询场景下网络往返的代价远大于解析的代价。批量插入那一行是差距最大、也最容易被忽略的客户端模式下 62 毫秒服务端模式下 151 毫秒慢了一倍还多。这就是前面提过的批处理改写问题——客户端模式下驱动把 500 条 INSERT 合并成一条多值 INSERT一次网络往返搞定服务端模式下合并受限制实际发送和执行的次数多得多。很多团队做优化的时候把useServerPrepStmts一开接口响应时间翻倍问题就出在这里。大文本更新那一行服务端模式反而快一些原因是二进制协议传字符串不用做转义8KB 的参数在文本协议里转义后体积会变大而且拼接字符串本身也要消耗 CPU。4.3 为什么收益和宣传的不一样回到第 1 章埋的那个伏笔MySQL 的预编译省的是解析 权限校验 部分预处理而优化和执行计划的选择在 MySQL 的实现里并没有像很多人以为的那样被彻底固化下来。一次点查的耗时里解析可能只占几个百分点网络往返、索引查找、InnoDB 的页访问才是大头。你把几个百分点省掉还要额外付出网络往返的代价净收益自然就不明显了。还有一个常被忽略的变量并发连接数。我们测的是 10 个连接每个连接各自持有自己的预编译语句。连接数越少服务端预编译的资源开销越可控连接数一上去每个连接都缓存一批语句服务端的语句对象数量就是连接数 × 每连接缓存条数内存压力会线性上升。所以真正决定要不要开服务端预编译的从来不是它是不是更先进而是你的业务形态SQL 重复度高不高、连接是不是长连接、有没有大批量写入。5. 踩坑记录预编译最常见的六个问题5.1 max_prepared_stmt_count 爆掉是怎么发生的这是我在生产环境真真切切遇到过的故障报错信息长这样Cant create more than max_prepared_stmt_count statements (current value: 16382)现象是接口开始大面积报错重启服务能缓解过一段时间又复发。排查路径是这样的先确认SHOW GLOBAL STATUS LIKE Prepared_stmt_count发现这个值已经贴在 16000 以上下不来再查performance_schema.prepared_statements_instances按线程分组统计发现语句集中在少数几个连接上每个连接挂着上千条语句SQL 文本呈现出高度重复的特征——同一张表的同一条查询重复出现了几十遍。根因是两个问题叠加。一是代码里有一批PreparedStatement没有关闭走的不是 try-with-resources 而是手工管理异常分支上漏了 finally二是连接池的连接是长连接连接不销毁语句对象就跟着一直挂着。修复动作有三步把漏关的地方补上把max_prepared_stmt_count适当调高作为缓冲SET GLOBAL max_prepared_stmt_count 32768;注意这是全局变量重启后失效要写进配置文件加上针对Prepared_stmt_count的监控告警阈值设在额度的 60%。注意调高 max_prepared_stmt_count 只是止血不是治疗。这个值本质上是给程序 bug 兜底用的靠调大它来解决问题等于把定时炸弹的引线接长一点。5.2 哪些位置看着能参数化、其实参数化不了这一节是整篇文章最实用的部分因为剩下的注入风险基本都藏在这里。**第一个是 ORDER BY 的字段名和排序方向。**你写ORDER BY ?MySQL 不会报错但也不会按你传的值排序它会把整个表达式当成一个常量处理结果就是排序静默失效。这类 bug 最恶心的地方在于它不报错测试环境数据量小的时候看不出来上线之后用户说排序按钮点了没用才被发现。正确做法是把允许的字段名做成白名单映射用户传create_time代码里查表拿到真正的列名再拼进SQL。**第二个是表名、库名、列名。**占位符只能出现在值的位置元数据的位置一概不行。凡是需要动态切换表名的场景比如按月分表一定要走白名单校验因为表名一旦能被外部输入左右注入的门就敞开了。**第三个是 LIMIT 后面的数字。**这一点各家驱动行为有差异有些版本能接受LIMIT ?有些场景下会退化成字符串比较。稳妥的做法是把分页参数强制转成整型再校验范围不要直接透传。第四个是 IN 列表。WHERE id IN (?)只代表一个值不能代表一个列表。要实现动态长度的 IN只能用循环拼出对应数量的占位符IN (?, ?, ?)然后逐个绑定参数。拼的是占位符的个数不是值本身这一点别搞混。还有一个容易忽略的UPDATE语句里带子查询的情形比如UPDATE t SET flag ? WHERE id IN (SELECT ...)子查询内部的这条 SQL 是整体参与解析的它的结构不能被外部输入决定能参数化的只有它自己内部的值位置。5.3 批处理改写、字符集与连接池的三方拉扯批处理的问题前面说过这里补上完整结论**批量写入密集的链路服务端预编译往往不是好选择。**如果你的系统里既有高频点查又有大批量写入比较务实的做法是让写入链路单独用一个数据源关掉服务端预编译、打开rewriteBatchedStatements读取链路用另一个数据源开服务端预编译。多一个数据源的维护成本比强行统一配置带来的性能损失划算得多。字符集这块的坑比较隐蔽。服务端预编译走二进制协议传参数字符串参数需要带上字符集标识如果连接串上的characterEncoding和服务端表字段的字符集不一致可能出现客户端看着正常、数据库里存的是问号的情况。排查方法很直接建一张测试表从 Java 端写入一段包含中文和四字节字符的字符串再用命令行SELECT HEX(col)看十六进制内容对不对一眼就知道。别用看起来没乱码来判断乱码有时候要到特定字符上才会暴露。连接池这边要留意的是缓存的作用域。prepStmtCacheSize是每个连接一份的池里有 20 个连接理论上限就是 20 乘上这个值算服务端资源占用的时候要把连接数乘进去。另外连接池回收连接时比如空闲超时被逐出该连接上的预编译语句会随之释放这部分是自动的不用担心。5.4 问题速查表把上面这些整理成一张对照表出问题的时候照着找现象可能原因排查手段处理方式报 max_prepared_stmt_count 超限语句未关闭长连接累积查Prepared_stmt_count、按线程分组查语句数补 close、加监控、临时调高上限开了服务端预编译反而变慢未开语句缓存或批量写入被拖累对比Com_stmt_prepare增速、分组压测打开cachePrepStmts写入链路单独数据源排序参数传了但结果没变ORDER BY 后用了占位符直接EXPLAIN看是否出现 Using filesort字段名白名单映射后拼接批量插入变慢一倍批处理改写失效开 general log 看实际发送的SQL条数批量链路关闭服务端预编译中文写入后变问号连接字符集与表字符集不一致写入后用HEX()看实际存储字节统一 utf8mb4 并显式配置执行计划每次都在重算COUNT_REPREPARE持续增长查prepared_statements_instances检查参数倾斜必要时改回普通语句6. 什么时候该开服务端预编译什么时候该放手6.1 适合打开的场景判断标准其实就三条满足得越多越值得开。第一条是同一条SQL被执行很多次。报表类接口、门户首页那种每次请求都跑同一条聚合查询、定时任务里循环处理数据的场景预编译省下的解析开销会被执行次数放大收益就出来了。反过来那种每次拼出来的SQL都不一样的动态查询预编译什么都省不下。第二条是长连接 连接池。短连接场景下连接一断语句就释放每次请求都要重新 prepare纯亏。用连接池把连接复用起来语句缓存才有意义。第三条是参数以中长文本为主或者参数数量很多。二进制协议在传输大量字符串参数时比文本转义更省参数越多、字符串越长优势越明显。6.2 适合关掉的场景同样三条命中就得掂量。大批量写入的链路这个前面反复说过了批量插入的性能红利几乎全在批处理改写上跟服务端预编译冲突写入链路默认关掉通常更划算。一次性的、低频的 SQL。比如后台运维工具跑的导出语句、启动时执行的初始化脚本每条只跑一次开预编译纯属增加两次网络往返。SQL 文本极长、变化极多的场景。这类SQL超过prepStmtCacheSqlLimit就不会被缓存还会把缓存空间挤掉不如直接走普通路径。6.3 一套可以直接抄的配置最后给两套配置按链路分流使用。读取链路点查、列表、聚合为主服务端预编译打开jdbc:mysql://host:3306/db ?useServerPrepStmtstrue cachePrepStmtstrue prepStmtCacheSize250 prepStmtCacheSqlLimit2048 useLocalSessionStatetrue characterEncodingutf8 serverTimezoneAsia/Shanghai写入链路批量插入为主服务端预编译关闭批处理改写打开jdbc:mysql://host:3306/db ?useServerPrepStmtsfalse rewriteBatchedStatementstrue useLocalSessionStatetrue characterEncodingutf8配套的两条监控要跟上Prepared_stmt_count超过max_prepared_stmt_count的 60% 就告警Com_stmt_prepare的增长速率如果和请求量同比例线性上涨说明缓存没生效需要回头检查参数。我个人在实际操作中的体会是MySQL 预编译这东西被讲得神乎其神实际上它的核心价值按重要性排序是安全第一资源复用第二性能第三。真正把它用对的关键不在要不要开而在分场景开——读取链路和写入链路本来就不该用同一套参数很多性能问题都是把一条配置糊到所有链路上造成的。至于那个max_prepared_stmt_count的坑我建议你现在就去线上查一下Prepared_stmt_count这个值如果它已经悄悄爬到几千那离出事可能就只差一次流量高峰了。