那次线上告警来得相当突然。某个订单系统的数据接入接口平均耗时从80毫秒一路涨到接近两秒数据库CPU直接冲到90%慢SQL日志里密密麻麻全是insert和select偶尔夹杂几条update也是秒级返回。我接手排查之后发现问题本身并不玄学核心就是三句话insert是一条一条插update是一条一条改select走了深分页扫了整张表。整个过程涉及Java代码层面、SQL写法层面和数据库结构层面的多重调整正好覆盖insert、update、select三类高频操作。这篇文章就把这次优化经历完整拆开讲把思路、代码、参数、坑一次性说完适合正在做Java后端性能优化的同学也适合想把手头接口吞吐再往上抬一抬的老手。慢插入某个批量导入功能在循环里执行单条INSERT一万条数据就要调用一万次数据库。慢更新状态流转任务逐条UPDATE频繁提交小事务锁竞争严重。慢查询列表接口使用深度分页LIMIT 100000, 20直接让 MySQL 扫描十几万行。排查思路不复杂先看数据库侧的表现再回应用侧翻代码最后才动手改。1. 乱象初现慢SQL告警与三层定位思路1.1 现象从监控指标到代码入口当时的告警信息很直观接口P99耗时从220ms上升到1800ms数据库连接池活跃连接数打到上限大量请求在等待获取连接。打开MySQL慢查询日志之后三类SQL原形毕露。第一类是插入语句的堆积。一行一行插入订单明细耗时主要不在SQL本身而在每次执行都独立提交事务redo log刷盘、网络往返、SQL解析全部重复一万次。第二类是UPDATE语句集中在同一个业务表上明显是某个状态机在批量推进工单状态。第三类是SELECT典型的列表分页查询翻到第100页之后就开始大量扫描。定位到具体方法也不难通过打印调用栈、结合 arthas trace 观察热点方法的耗时分布基本把范围锁定在三个入口导入任务、状态流转任务、列表查询接口。这里我的建议是不要一上来就改代码先把数据摸清楚。确认是哪条SQL在拖垮数据库确认它的执行计划是不是出了大问题确认调用频率到底有多高然后再决定优化方向。1.2 优化策略先榨SQL再动代码这一次我给自己定了三条优化原则。第一SQL能合并的绝不循环执行。数据库最怕的不是单条SQL复杂而是应用侧用几千次简单SQL把连接池榨干。能批量拼接的批量拼接能一次更新的多次合并为一次。第二索引设计必须和执行计划对齐。不是说有索引就行而是要确认查询真的走到了索引回表次数被控制住排序字段也在索引覆盖范围内。第三事务粒度要重新设计。很多人把“事务”默认等同于“整个方法”结果一个批量任务开启了一个超长事务行锁一直不释放后面的更新全部排队。优化的核心之一就是把大事务拆小把持锁时间降下来。下面三个章节分别展开 insert、update、select 的优化细节每一段都会有代码示例和前后对比。2. 批量insert优化从逐条插入到批量提交2.1 单条insert为什么成为全链路瓶颈先看最初的写法典型的逐条插入public void insertOrders(ListOrderItem orderList) { for (OrderItem item : orderList) { jdbcTemplate.update( INSERT INTO order_record(order_no, item_id, amount, create_time) VALUES(?, ?, ?, ?), item.getOrderNo(), item.getItemId(), item.getAmount(), item.getCreateTime() ); } }这段代码表面看不出大问题但如果orderList有一万条那就等于向MySQL发送了一万次INSERT语句。每次INSERT都要经历客户端拼接SQL、网络传输、MySQL解析SQL、打开事务、执行写入、提交事务、刷redo log、网络返回。一整套往返走下来单条耗时可能只要1ms但一万次累加就会变成不可忽视的延迟。我实测过一组数据在相同配置的MySQL实例上逐条插入10000条耗时大约是23秒改成批量提交后耗时能压到1.5秒以内性能提升超过10倍。优化空间完全来自减少网络往返和减少事务提交次数。改造思路也很明确把“每条数据独立提交”改成“攒一批一起提交”。2.2 使用JdbcTemplate批量更新接口Spring的JdbcTemplate提供了batchUpdate方法正好用来做批处理。改后代码如下public void insertOrders(ListOrderItem orderList) { jdbcTemplate.batchUpdate( INSERT INTO order_record(order_no, item_id, amount, create_time) VALUES(?, ?, ?, ?), new BatchPreparedStatementSetter() { Override public void setValues(PreparedStatement ps, int i) throws SQLException { OrderItem item orderList.get(i); ps.setString(1, item.getOrderNo()); ps.setString(2, item.getItemId()); ps.setBigDecimal(3, item.getAmount()); ps.setTimestamp(4, item.getCreateTime()); } Override public int getBatchSize() { return orderList.size(); } } ); }batchUpdate方法内部会使用PreparedStatement的addBatch和executeBatch机制将多条INSERT打包发送给数据库执行。对MySQL来说JDBC驱动还会把多个INSERT组合成一条多VALUES的INSERT语句减少了解析次数。如果在MyBatis环境下同样可以开启批量执行器SqlSession sqlSession sqlSessionFactory.openSession(ExecutorType.BATCH); try { OrderRecordMapper mapper sqlSession.getMapper(OrderRecordMapper.class); for (OrderItem item : orderList) { mapper.insert(item); } sqlSession.commit(); } finally { sqlSession.close(); }这里要注意一个容易被忽略的点ExecutorType.BATCH不等于自动批量提交它只是让多次update操作复用同一个PreparedStatement并累积执行最终必须显式commit才会真正落库。2.3 分批大小与rewriteBatchedStatements参数批量插入不是一次性把十万条全塞进去就完事。一次batchUpdate的数量太多会带来几个问题SQL文本过长超过max_allowed_packet限制、事务持有过多行锁、内存中对象列表过大。实际中我一般把单批大小控制在1000条左右分批循环提交public void insertOrdersInBatch(ListOrderItem orderList) { int batchSize 1000; for (int from 0; from orderList.size(); from batchSize) { int to Math.min(from batchSize, orderList.size()); ListOrderItem subList orderList.subList(from, to); jdbcTemplate.batchUpdate(SQL, new BatchPreparedStatementSetter() { // 省略实现 }); } }还有一个MySQL特有的大坑JDBC驱动参数rewriteBatchedStatements。默认情况下MySQL的Connector/J驱动执行addBatch并不会真的将多条语句打包发送而是仍然逐条执行看起来用了batch性能却没提升。必须在JDBC URL上显式开启jdbc:mysql://localhost:3306/order_db?rewriteBatchedStatementstrueuseServerPrepStmtstrue开启这个参数后驱动才会把一批INSERT改写成INSERT INTO ... VALUES (...),(...),(...)这种形式真正批量发送给服务端。这个参数是这次优化中性价比最高的一个动作不花一分钱改动一行配置性能直接翻倍。验证连接参数是否生效可以通过MySQL通用日志或者观察网络包数量来确定。最简单的办法是打开MySQL的general_log看同一时间点收到的INSERT语句是不是多VALUES形式。注意rewriteBatchedStatementstrue对UPDATE语句也有影响它会把批量UPDATE重写成多条数据一起执行但前提是每一条的SQL结构必须完全一致。如果业务代码里每条UPDATE的动态条件不同就不会被重写。2.4 优化前后对比与事务边界设计优化前后的数据很直观我还是用一万条订单明细做基准方案耗时说明循环单条INSERT约23秒网络往返9900次事务提交10000次batchUpdate(1000/批)约1.8秒网络往返10次事务提交10次开启rewriteBatchedStatements后约1.1秒INSERT语句重写为多VALUES减少服务端解析这里需要额外说明事务边界。如果是真正的一次性批量任务比如凌晨跑批允许一个事务包含全部数据这样出了问题可以整体回滚。但如果是线上实时接口一个事务里插入一万行、持有一万行的行锁和间隙锁对高并发写入是很危险的容易造成锁等待甚至死锁。我的习惯是实时接口单个事务控制在1000行以内跑批任务控制在5000行以内。超过这个量就要考虑拆分为多个事务每批次提交后单独记录进度失败时从上次断点继续。这也引出一个更深层的问题批量插入往往伴随着批量更新下面进入update场景。3. update优化减少行锁持有时间与消息积压3.1 循环update为什么撑不住这个订单系统里有一个状态流转任务每天定时把一批待处理的订单状态从“已支付”推进到“已处理”。原始代码写法如下public void processOrders(ListLong orderIds) { for (Long orderId : orderIds) { jdbcTemplate.update( UPDATE order_record SET status 3, update_time NOW() WHERE id ?, orderId ); } }这样的写法缺点和逐条INSERT一样多次网络往返、多次事务提交。但在高并发场景下它还有一个更致命的问题——锁的粒度和持有时间。每次UPDATE都会对目标行加行锁事务提交时才释放。如果代码在for循环里每执行一条UPDATE就自动提交一次理论上每次持锁时间很短。但实际上很多团队会把整个循环包在一个大事务里比如用Transactional注解了processOrders方法那就会等到全部订单更新完后才统一提交。假设4000个订单分布在连续的主键范围内前者更新完不提交后面同范围的更新全部排队。这次优化中我先把事务边界从“整个方法”改为“每500条一个事务”再把语句本身改成可批量执行的写法。3.2 CASE WHEN批量更新与临时表更新批量更新最简单的落地方式是使用CASE WHEN语法一条SQL更新多行UPDATE order_record SET status CASE id WHEN 1001 THEN 3 WHEN 1002 THEN 3 WHEN 1003 THEN 4 END, update_time NOW() WHERE id IN (1001, 1002, 1003);对应的Java代码通过字符串拼接生成动态SQL。这里我建议使用MyBatis的foreach标签或者自己构建SQL语句注意控制单批数量。这种写法最大的优势是减少SQL执行次数。原本1000条订单需要执行1000次UPDATE现在只需要1次UPDATE网络往返减少了999次MySQL解析也只做一次。同时因为所有行都在一条SQL里更新InnoDB对目标行的锁虽然会全部加上但锁等待窗口比多次独立提交小得多因为提交次数减少了。不过CASE WHEN批量更新并不是银弹它有两个明显缺陷。第一如果一次更新的行数过多生成的SQL文本会非常长可能超过max_allowed_packet限制同时一条语句占用大量内存。我实测单条UPDATE超过2000行时SQL文本已经比较可观解析和锁等待时间也会上升。第二CASE WHEN方式无法针对不同行设置不同的WHERE条件。它本质上是先通过WHERE id IN锁定候选行再用CASE字段做行级赋值适用于按主键更新但不适用于复合业务条件的更新。如果更新条件比较复杂另一种更稳妥的方案是使用临时表CREATE TEMPORARY TABLE tmp_order_update ( id BIGINT PRIMARY KEY, status INT ); INSERT INTO tmp_order_update(id, status) VALUES (1001, 3), (1002, 3), (1003, 4); UPDATE order_record o INNER JOIN tmp_order_update t ON o.id t.id SET o.status t.status, o.update_time NOW();临时表方案需要额外的一次INSERT但好处是更新逻辑更清晰且能配合复杂的JOIN条件使用。跑批场景下我倾向于用临时表实时接口场景下用CASE WHEN因为临时表的创建和销毁本身有成本。3.3 版本号控制与分批防重批量更新还需要考虑并发场景下的数据覆盖问题。比如状态流转任务在批量更新订单状态同时用户在前端手动取消订单如果两个操作没有版本控制就可能出现任务把已取消的订单重新覆盖为“已处理”。最简单的解法是乐观锁。每次更新都带上当前状态作为条件UPDATE order_record SET status 3, update_time NOW() WHERE id ? AND status 2在批量更新场景中可以改为UPDATE order_record SET status 3, update_time NOW() WHERE id IN (...) AND status 2如果返回的影响行数小于期望数量说明部分订单状态已经被其他请求修改需要回读数据找出冲突项。实现时可以按影响行数做校验int affected jdbcTemplate.update(sql, args); if (affected expectCount) { log.warn(批量更新存在冲突期望更新{}条实际更新{}条, expectCount, affected); // 触发冲突处理流程 }另外还有一点必须提醒不要把状态判断只放在SQL的WHERE条件里。如果应用侧代码先执行SELECT判断状态再由UPDATE语句执行更新两个操作之间可能存在时间差并发下依然会丢更新。正确的做法是把状态条件直接写进UPDATE语句数据库的行锁会保证条件判断和更新操作原子发生。3.4 update优化后的实测效果我拿1000个订单的状态流转做了对比测试测试条件是相同表结构、相同数据量、相同MySQL配置方案耗时说明循环单条UPDATE每次自动提交约6.2秒1000次网络往返1000次事务提交循环单条UPDATE一个大事务约5.5秒1000次网络往返1次事务提交但锁持有时间过长CASE WHEN批量更新1000条一批约0.4秒1次SQL1次事务提交锁持有时间大幅缩短临时表JOIN更新1000条一批约0.6秒多一次临时表写入但条件更灵活这个结果说明网络往返次数对UPDATE性能的影响是决定性的。CASE WHEN方案把1000次往返压缩到1次收益非常直接。踩过的坑也要提一句。当时我直接在原有Transactional方法里改成CASE WHEN批量更新结果方法内部分了三批每批1000条但因为方法外层的JdbcTemplate默认自动提交第一批更新完就自动commit了中间某批失败后无法整体回滚。后来我改成了编程式事务手动控制每批提交时机才能保证正确处理。4. select优化慢查询的索引重建与分页改造4.1 通过EXPLAIN定位慢查询的索引问题列表查询接口是另一处重灾区。页面上的订单列表支持按订单状态、创建时间、渠道来源等条件筛选同时按创建时间倒序分页。最初查询慢得离谱第100页之后的请求经常超过3秒。看EXPLAIN结果EXPLAIN SELECT id, order_no, amount, status, create_time FROM order_record WHERE status 2 ORDER BY create_time DESC LIMIT 100000, 20;执行计划里type等于ALLrows估算十几万Extra字段显示Using filesort。这说明MySQL根本没有用到任何索引约束而是全表扫描后做了文件排序再把前100020条结果取出来丢掉前100000条。这类问题的核心原因通常是索引缺失或者索引顺序设计不合理。状态字段的区分度很低即使建了(status)单列索引MySQL优化器也可能因为估算扫描行数占比过高而放弃索引。真正需要的是一个复合索引ALTER TABLE order_record ADD INDEX idx_status_create_time (status, create_time);这个索引能同时满足WHERE条件过滤和ORDER BY排序MySQL可以直接按索引顺序读取数据避免filesort。我加完索引后再看执行计划type变成refrows从十几万降到几百Extra里不再出现Using filesort。注意不是所有查询都适合建复合索引。区分度很低的字段放在最前面可能导致大量行落入同一索引块反而增加回表成本。实际设计时要结合业务筛选频率来决定字段顺序。4.2 覆盖索引与延迟关联索引优化后前几十页的查询速度有了显著提升但深度分页还是不够理想。原因在于即便用上了索引LIMIT 100000, 20仍然需要扫描前100000行索引数据然后逐一回表获取完整行记录最后丢弃99980行这是巨大的无效开销。第一个优化手段是覆盖索引让查询列全部落在索引里避免回表。比如上面的查询只查id、order_no、amount、status、create_time五个字段如果索引是(status, create_time, amount, order_no)那么MySQL可以从索引直接返回所有列不再访问聚簇索引。但全字段覆盖在实际业务中很难做到因为SELECT经常涉及十多个字段全部塞进索引会让索引体积过大。更通用的方案是延迟关联。延迟关联的思路是先通过索引快速定位到所需的主键ID集合然后再用主键去关联原表获取完整数据SELECT o.id, o.order_no, o.amount, o.status, o.create_time FROM ( SELECT id FROM order_record WHERE status 2 ORDER BY create_time DESC LIMIT 100000, 20 ) t INNER JOIN order_record o ON t.id o.id ORDER BY o.create_time DESC;子查询部分只返回主键id索引扫描和排序都发生在聚簇索引之外减少了回表带来的随机IO。外层再用主键INNER JOIN获取完整行因为主键是聚簇索引回表效率极高。实测数据LIMIT 200000, 20场景下原始查询耗时2.1秒延迟关联后耗时0.15秒提升超过10倍。4.3 深分页的终极形态游标分页延迟关联能解决一部分问题但本质上LIMIT大偏移量的少量结果依然需要扫描大量索引数据。如果列表是滚动加载模式比如App里的下拉加载最优解是游标分页也叫keyset分页。游标分页的核心思路是App在请求时带上上一页最后一条记录的排序字段值下一页查询直接基于这个游标定位SELECT id, order_no, amount, status, create_time FROM order_record WHERE status 2 AND create_time 2024-05-20 10:00:00 ORDER BY create_time DESC LIMIT 20;因为这个游标条件天然符合索引有序性MySQL可以直接定位到目标位置前100000条数据完全不扫描。查询耗时稳定在10ms以内和翻页深度无关。Java代码侧需要做的改造是把页数参数替换为游标参数。前端不再传pageNum而是传lastCreateTime和lastId后端通过这两个字段拼条件。public ListOrderRecord queryNextPage(String status, Timestamp lastCreateTime, Long lastId, int pageSize) { if (lastCreateTime null) { // 第一页 return mapper.selectFirstPage(status, pageSize); } return mapper.selectNextPage(status, lastCreateTime, lastId, pageSize); }注意一点如果create_time在业务上可能出现重复值游标要采用复合条件避免漏掉或者重复数据。常用写法是create_time ? OR (create_time ? AND id ?)同时用id作为第二排序键兜底。这个方案并不适合所有页面。比如跳页场景下用户必须能直接跳到第100页游标分页并不适用。对于那种情况我建议仍然使用延迟关联或者直接限制最大可查页数业务侧剪裁需求。4.4 select优化的最终结果选取三个典型场景做验证数据量是80万行订单记录查询场景优化前耗时优化后耗时核心手段第10页每页20条320ms25ms复合索引避免filesort第500页每页20条1600ms180ms延迟关联减少回表滚动加载第100次1500ms8ms游标分页消除深翻页这四个数字背后其实是同一类问题的不同解法让MySQL少干活少扫描少回表少排序。还有一点值得单独说。加索引不是越多越好索引越多写入时需要同步更新的索引结构越多insert和update的开销也越大。这次优化过程中我清理了三个已经不用的单列索引把写入性能又拉回了几个百分点。索引设计没有标准答案但原则上能用复合索引覆盖多种查询场景就不要堆一堆冗余单列索引。5. 优化工具箱与排查技巧速查5.1 常用性能分析工具组合如果你也准备排查类似的Java数据库性能问题我建议先准备好以下工具链排查效率会高很多数据库侧开启慢查询日志设置long_query_time为1秒定期分析慢日志输出。执行计划分析使用EXPLAIN查看SQL的扫描行数、索引使用情况、Extra字段内容。调用链追踪通过arthas的trace命令定位方法热点确认耗时是花在数据库调用还是业务逻辑。连接池监控观察活跃连接数、等待获取连接数、连接创建销毁频率。连接池被打满往往意味着SQL执行时间过长。这些工具不需要一次性全部上但至少要有一个慢查询日志否则排查无从下手。工具选型方面MySQL自带的mysqldumpslow可以快速聚合统计慢日志中Top N SQLEXPLAIN加上 formatjson 还能看到更详细的cost信息都是免费且可靠的手段。5.2 SQL执行计划阅读要点很多同学看EXPLAIN只看有没有用到索引这个习惯要改。我列了一下排查时的高频关注点关注点解析方法优化方向type字段ALL表示全表扫描ref表示普通索引匹配eq_ref表示主键或唯一索引匹配目标是range以上尽量避免ALLrows字段预估扫描行数和实际耗时强相关越小越好Extra中的Using filesort表示排序无法利用索引建立能同时满足排序条件的索引Extra中的Using temporary表示查询创建了临时表调整GROUP BY或DISTINCT相关字段key_len字段索引使用长度对比说明哪些索引部分被用到我一般判断SQL是否要优化的标准很简单EXPLAIN里rows超过十万并且type不是range级别就直接列进优化清单。如果一条查询每秒钟执行很多次这个阈值还要更严。5.3 避坑清单与个人心得这次优化踩过的坑值得集中梳理一下第一批量操作并不是越大越好。我一开始把批大小调到5000结果一个批次持锁时间太长产生了大量锁等待后来调回1000才稳定。批大小的选择要结合行平均大小和数据库max_allowed_packet来估算实在不好估算就先用1000作为基线压力测试之后再做调整。第二事务内不要穿插远程调用。优化过程中我顺手清理了一次循环里查缓存、调外部接口、再更新数据库的写法。远程调用把事务生命周期拉得非常长行锁长时间无法释放。正确做法是先完成所有外部调用再进入数据库写入链路。第三改完SQL一定要重新看执行计划。有一次给表加了索引但SQL里的条件写法是WHERE status 2字段类型是INTMySQL发生了隐式类型转换索引完全没法用。这类坑非常隐蔽排查半天最后发现只是引号和类型的问题。第四线上变更要先备份和执行计划对比。数据库索引变更或者大批量UPDATE在非高峰期操作先EXPLAIN再执行。如果UPDATE影响行数非常大建议分批执行每批1000到5000条批次之间加一点间隔避免和正常业务产生锁冲突。第五性能测试必须包含读和写两条链路。单纯的select优化看起来效果很好但如果新增了复合索引insert和update的写入性能可能下降5%到10%。这次在select优化后我就顺手做了写入链路的回归测试确认批量insert和update没有劣化才放行。最后分享一个我个人的小习惯。每次优化完一类SQL我会把修改前后的执行计划截图保存下来连同慢SQL日志、压测数据一起记录到项目文档里。下次再做性能优化或者有人问“这个接口怎么突然变快了”直接翻记录就能说清楚来龙去脉。这个习惯救了我很多次尤其是接手老项目时历史优化的上下文就是最快的排查线索。回到这次的经历本身原地不动的话数据库连接池迟早被打满动手之后同样的业务量下系统轻松了不少。技术上的收获其实不在于某个SQL写得有多花哨而在于理解了数据库底层的执行方式和锁的行为后续遇到类似问题心里会更有底。
