前一阵子接手了一个Java服务的性能优化任务数据量上来之后接口响应从几百毫秒一路飙到三秒开外数据库CPU经常打满。排查慢查询日志之后发现核心问题就三类select 查询走了全表扫描、insert 是循环单条插入、update 在业务循环里一条一条提交。这应该是很多后端项目都会遇到的典型场景所以我把这次 insert、update、select 的优化过程完整记录下来从问题定位、SQL 改写、代码层配合到最终的效果对比都有给同样在做数据层优化的朋友一个可以照着操作的参考。1. 项目背景与问题定位1.1 优化之前的业务表现这次负责的模拟项目X是一个典型的业务中台服务核心表是订单流水表和操作日志表日增数据在百万级别。业务高峰集中在每天的几个固定时段接口包括列表分页查询、批量导入、状态流转更新。优化前的主要表现是列表查询接口在翻到比较深的页数时响应明显变慢导入上千条数据时接口直接超时状态更新频繁出现锁等待报错数据库监控显示 CPU 使用率长期在百分之七八十以上。最开始大家都怀疑是数据库机器配置不够甚至有人提出直接升级实例规格。但我看了一圈慢查询日志之后发现真正的瓶颈不是硬件而是应用层生成的 SQL 质量太差。慢查询日志里大量出现单条 insert一次导入一千条数据就是一千条 INSERT 语句每条都是独立的网络往返和事务提交select 则大量使用 select * 加上没有命中索引的查询条件update 更夸张在 for 循环里一条一条执行每条语句单独提交事务行锁频繁碰撞。定位问题我用了一个比较笨但很有效的方法把慢查询日志按执行次数和耗时排序然后把排名靠前的 SQL 拿出来看执行计划找到它们共同的模式。这一步做完之后优化方向就很清晰了就是标题里的三类操作insert、update、select。1.2 优化目标的量化拆解问题定位之后我没有急着改代码而是先和团队定了一个可量化的目标。因为如果不量化优化的过程中很容易陷入“好像变快了”的错觉。我把优化目标拆成了三个指标select列表接口的 TP99 响应时间从 2500 毫秒降到 800 毫秒以内深分页查询不再出现线性退化。insert批量导入 1000 条数据的耗时从 12 秒降到 2 秒以内并且不再因为单条失败导致整个导入中断。update状态更新接口在高峰期的锁等待次数降为零平均事务执行时间缩短到 100 毫秒以内。除此之外还有一个硬约束不能改表结构之外的业务逻辑。也就是说所有优化尽量在 SQL 写法和 Java 代码的调用方式上完成表结构如果需要加索引可以加但业务语义不能动。这样做的目的是控制改动风险避免优化过程中引入新的问题。2. select 查询优化的几个关键动作2.1 从执行计划里挖出全表扫描的元凶select 优化是所有数据优化里最优先的因为读操作的频率通常远高于写操作一个慢查询被放大到所有用户身上影响面非常广。我处理的第一条慢 SQL 是订单列表查询原来的写法大致是这样的SELECT * FROM order_info WHERE biz_id 10086 AND status IN (1, 2, 3) ORDER BY create_time DESC LIMIT 100000, 20拿 EXPLAIN 一看type 那一列是 ALLrows 估算值到了百万级Extra 里还有 Using filesort。也就是说这个查询虽然带了查询条件但因为缺少合适的索引数据库只能把整张表扫一遍过滤出符合条件的几千条数据之后再做一次内存排序最后才取偏移量之后的 20 条。这一套操作下来慢是必然的。我当时的第一反应是加一个联合索引把查询条件里的 biz_id、status 和排序的 create_time 都放进去。但这里有个细节要注意status 这种区分度很低的字段放进联合索引在某些场景下未必能带来预期收益因为 IN 查询在索引里的表现可能会退化成范围扫描。真正起决定作用的是 biz_id 和 create_time 的组合索引。最后的索引设计是(biz_id, create_time, status)这样查询条件可以快速定位到指定的 biz_id然后利用索引的有序性直接按 create_time 倒序取数filesort 就消失了。改完之后再看执行计划type 变成了 refrows 降到了几百Extra 里的 Using filesort 也消失。这一条 SQL 的耗时从 2.3 秒降到了 80 毫秒左右效果非常直观。2.2 深分页问题的处理套路上面的 SQL 还有一个问题LIMIT 100000, 20这种深分页写法即使加了索引数据库也要先扫描并丢弃前 10 万条记录才能拿到目标数据。随着页码不断加深耗时会越来越久。在处理这个问题时我没有采用业界常用的“禁用深分页”方案因为产品需求上确实存在跳页查询的场景直接砍掉功能不现实。我的做法是经典的游标分页当用户从第一页开始翻时前端把上一页最后一条记录的排序字段值传回来SQL 改写为基于游标的定位查询。比如这里用自增主键 id 来定位SELECT * FROM order_info WHERE biz_id 10086 AND status IN (1, 2, 3) AND id 101000 ORDER BY id DESC LIMIT 20注意id 作为游标的前提是排序字段和 id 的单调性一致或者直接按 id 排序。如果业务排序字段是 create_time而 create_time 存在大量相同值就会出现跨页重复或漏数据的问题。这种情况下我一般会设计一个复合游标把(create_time, id)组合作为排序和定位条件SQL 写成WHERE (create_time ?) OR (create_time ? AND id ?)这样既能保证排序稳定又能利用索引快速定位。改完之后不管翻到第几页响应时间都保持稳定在几百毫秒以内不再随页码增长而劣化。2.3 select 后置过滤和代码层的配合除了 SQL 本身的改写Java 代码层也有一处值得优化的地方原来的代码会把一次要展示的数据全量查询到内存里然后在 Java 侧做各种 map、filter 和聚合操作。比如查列表的同时还要关联用户表取用户姓名原来的实现是一个 for 循环里逐个查用户信息产生了 N1 次查询。优化的思路比较简单粗暴把关联查询提前到 SQL 层用一次 JOIN 把需要的字段全部取出来如果担心 JOIN 影响性能也可以分两次查询一次查主表数据一次按 id 集合批量查关联表然后内存中做匹配。这两种方式的性能都比循环查库好一个数量级。不过需要留意的是JOIN 时驱动表的选择很关键MySQL 优化器通常会选择小表驱动大表但有些情况下优化器也会选错这时可以用 STRAIGHT_JOIN 强制指定驱动表或者干脆拆成两次查询实测往往更稳定。另外select 优化里还有一个经常被忽略的点不要无脑 select *。原来代码里大量使用 select *把一张 30 多个字段的表全查出来但前端真正用到的只有五六个字段。多余的字段不仅浪费网络带宽还会让 Java 侧的转换逻辑变慢。改成显式列出需要的字段之后单次查询的传输数据量少了大概七成。3. insert 批量写入优化的详细过程3.1 从循环单条插入到批量提交insert 优化是这个项目里收益最明显的一块。原代码是典型的循环插入for (OrderDto dto : dtoList) { orderMapper.insert(dto); }这段代码的逻辑本身没有问题但它带来的代价是巨大的每插入一条数据就要执行一次网络往返、一次 SQL 解析、一次事务提交。MySQL 默认的 autocommit 模式下每次 insert 都是一次独立事务需要刷一次 binlog 和 redo log1000 条数据就是 1000 次磁盘同步。实测下来插入 1000 条数据耗时约 12 秒平均每条 12 毫秒这在本地网络环境下算非常慢了。第一步改造是改成 MyBatis 的批量插入也就是在 mapper XML 里写一个多值 INSERT一条 SQL 包含多条记录insert idbatchInsert INSERT INTO order_info (biz_id, product_id, amount, status, create_time) VALUES foreach collectionlist itemitem separator, (#{item.bizId}, #{item.productId}, #{item.amount}, #{item.status}, #{item.createTime}) /foreach /insert然后在 Java 代码里分批调用比如每批 500 条int batchSize 500; for (int i 0; i dtoList.size(); i batchSize) { int end Math.min(i batchSize, dtoList.size()); orderMapper.batchInsert(dtoList.subList(i, end)); }这一步改造后1000 条数据的插入耗时从 12 秒降到了 3 秒左右。为什么不是想象中的 1000 倍提升因为虽然减少了网络往返和事务提交次数但每条 SQL 的字符串拼接、参数绑定、执行计划的生成仍然需要时间。真正的杀手锏是 JDBC 驱动层面的参数。3.2 rewriteBatchedStatements 参数的实测效果很多人不知道JDBC 的 PreparedStatement 批量执行要想真正合并成一条多值 INSERT必须在连接 URL 上开启一个参数rewriteBatchedStatementstrue。不加这个参数时即使你在 Java 端使用 connection 的 batch APIMySQL 驱动也只会把多条 insert 语句逐条发送给服务器性能提升有限。我的操作是在项目的数据源配置里修改 JDBC URLjdbc:mysql://your-host:3306/your-db?useUnicodetruecharacterEncodingutf8rewriteBatchedStatementstrueuseSSLfalse同时在 MyBatis 中开启批量执行器SqlSession sqlSession sqlSessionFactory.openSession(ExecutorType.BATCH);两者配合的情况下驱动程序会把一个 batch 里的所有 insert 重写为一条多值 INSERT 语句再发给数据库。我做了三组对比实验数据如下策略1000条耗时事务次数说明循环单条 insert12秒1000每条一次网络往返独立事务foreach 多值 INSERT500条/批3.1秒2减少网络往返和事务提交foreach 多值 INSERT BATCH 模式1.2秒2驱动层再次合并优化这个结果说明同样的业务逻辑仅仅调整写法和驱动参数性能差距可以达到十倍。建议任何使用 MySQL Java 的项目都检查一下 JDBC URL 有没有带上 rewriteBatchedStatements 参数这一步是无本万利的优化。3.3 批量插入的分批策略和边界前面提到每批 500 条这个数字不是拍脑袋定的。批次的粒度需要综合考虑两个因素一是单条 SQL 的大小MySQL 默认的 max_allowed_packet 是 64M如果一条多值 INSERT 超过这个上限数据库会直接报错二是事务的长度一批数据就是一个事务批次越大事务执行时间越长持有的锁和产生的 undo 日志也越多主从复制延迟也会加大。我实际测试过不同的 batchSize从 100、500、1000、2000 各跑了几轮结果 500 到 1000 之间的收益最大超过 1000 之后耗时反而略有上升而且数据库的临时内存占用明显增加。最终线上环境我采用了 500 条一批同时把整个导入任务放到一个独立的事务里失败时可以整体回滚避免出现“插了一半、数据状态对不上”的问题。另外批量插入还有个容易踩的坑如果数据里有重复的主键或唯一键只要有一条冲突整批 INSERT 都会失败。这种情况需要在业务层先做好数据去重和幂等控制。我在这里选择的做法是在导入前先做一次查询把已存在的主键过滤掉保证每批数据都是干净的。虽然多了一次查询但相比整个批次因为一条脏数据回滚重来成本低得多。4. update 更新优化的核心思路4.1 循环更新改造成批量更新update 的优化和 insert 类似问题核心同样出在循环。原代码在业务流转时对同一个订单的多个子状态依次更新每条 update 独立提交for (String itemId : itemIds) { orderMapper.updateStatus(itemId, newStatus); }这种写法在低并发时看不出问题但一旦多个请求同时更新同一批订单行锁等待就出现了。最直接的表现就是数据库监控里出现大量的 lock wait timeout业务上表现为接口偶发超时。我的改造方案是把循环中的多个 update 合并成一条语句利用 CASE WHEN 语法一次更新多条记录update idbatchUpdateStatus UPDATE order_info SET status CASE id WHEN 1 THEN COMPLETED WHEN 2 THEN COMPLETED WHEN 3 THEN FAILED END WHERE id IN (1, 2, 3) /update这条 SQL 只执行一次只占有一个短事务整体耗时从原来每个订单 3 到 5 毫秒、累加到大批订单几百毫秒降到了一次执行 20 毫秒以内。更新的行数越多性能差距越大。4.2 避免更新没有变化的字段在排查 update 相关慢 SQL 时我还发现一个隐蔽的问题业务代码里有很多无意义的更新。比如用户登录后把用户的 last_login_time 更新为当前时间哪怕这个时间在同一个事务里已经更新过一次后续的操作仍然会再执行一次 update。MySQL 的更新操作即使最终值没有变化也会产生 undo 日志、binlog 日志和行锁代价一点都不会少。优化方法是两方面的一是业务逻辑层做判断只有字段值确实变化时才执行 update二是 SQL 写法上利用 WHERE 条件做过滤。比如UPDATE user_info SET last_login_time NOW() WHERE id ? AND (last_login_time IS NULL OR last_login_time NOW() - INTERVAL 60 SECOND)这样在 60 秒内的重复更新会被直接跳过减少不必要的写操作。类似的问题还有更新操作带了版本号校验但业务上并不需要乐观锁每次都强行把 version 字段加一导致大量无意义的行锁竞争后来我把这些场景的 version 字段去掉才彻底解决。4.3 并发更新时的索引和死锁细节update 优化还有一个特别需要注意的细节WHERE 条件必须能命中索引否则数据库会在更新前对全表加锁造成极其严重的问题。我遇到过一个案例某张表的 update 语句 WHERE 条件用的是业务单号而这个字段上没有索引结果每次更新都触发全表锁整个批处理任务直接卡死。给业务单号加上索引之后锁的范围瞬间缩小到目标行问题随之消失。另一个坑是批量更新时的死锁。CASE WHEN 批量更新执行时MySQL 会对 WHERE IN 里的目标行加锁但加锁的顺序和 SQL 执行计划有关。如果两个事务同时执行UPDATE ... WHERE id IN (1,2,3)和UPDATE ... WHERE id IN (3,2,1)而执行的顺序又不一致就有可能出现互相等待的死锁。规避方法很简单在代码层对 id 集合排序后再构造 SQL保证所有线程按照同样的顺序更新记录。我在批量 update 的工具方法里直接加了一行list.sort(Comparator.comparingInt(x - x))之后死锁就再也没出现过。5. 代码层配合与事务边界的调整5.1 事务粒度的正确姿势SQL 优化可以做得很极致但如果 Java 代码里的事务边界摆得不对再好的 SQL 也白搭。这个项目里原来很多 service 方法被一个大事务包住里面有查询、校验、更新日志、调用外部接口整个事务动不动就是几百毫秒甚至几秒。事务越大持有的连接越久数据库连接池就容易被耗尽行锁持有的时间也越长直接的后果就是并法能力急剧下降。我在优化时遵从一个原则事务只覆盖真正需要原子性的写操作。读操作一律放到事务外面外部接口调用绝对不能在事务内执行。原代码里有几个方法是在事务里调用别的微服务接口的我全部调整为先查数据和校验再开启事务执行写操作最后提交事务后再调用外部接口。这样调整之后事务的平均时长从几百毫秒降到几十毫秒连接池的占用率立刻回落。具体操作上我主要利用了 Spring 的事务传播机制把大事务拆成多个独立的小事务同时避免一个业务方法被多个事务注解嵌套包裹。之前代码里一个入口方法加了Transactional调用的内部方法又加了Transactional默认的传播行为是 REQUIRED结果内部方法的注解完全没起作用所有操作还是挤在入口的大事务里。改成部分内部方法使用REQUIRES_NEW或者在入口去掉事务注解、让真正需要原子性的子方法各自开启事务情况才好转。5.2 分批读写与连接池参数调优除了事务边界分批读写也是代码层优化的一个重点。原来 select 查询一次把所有数据加载到内存再分页展示数据量到了几十万条以后内存直接溢出。我改成了流式查询使用 MyBatis 的游标模式逐条处理或者按主键 id 分段查询每段取 5000 条处理完再取下一段有效控制内存占用。连接池这边也有两处值得检查一是连接池的最大连接数很多项目默认配置是 20但数据库的整体并发只有几十个连接一旦接口吞吐上来连接池很快排满新请求全部等待。我按照数据库能承受的连接数重新设定了 maximumPoolSize而不是无脑调大。二是连接的最大生命周期MySQL 默认的 wait_timeout 是 8 小时长时间不用的连接会被数据库主动断开连接池里的连接如果不做保活检查下次访问就会报连接失效。我给 HikariCP 配置了连接测试和较短的 maxLifetime这个问题就消失了。5.3 监控指标的建立和基线记录数据优化最怕改完之后没有反馈机制无法判断是否真的变好了。我在这次优化过程中做了一个很简单的监控面板核心指标就四个数据库 CPU 使用率、慢查询数量、连接池活跃连接数、主要接口的 TP99 响应时间。优化前跑了一天作为基线之后每完成一个改动就对比基线的数据变化。这样做的意义在于每次改动的影响是可控和可追踪的。比如我加上联合索引后慢查询数量从每天几千条降到了几十条数据库 CPU 从 80% 降到 40%这就能直接证明索引方向是对的。再比如批量 insert 上线后导入接口的 TP99 从 12 秒降到 1.5 秒binlog 写入速率反而没有明显增加说明改写是成功的。没有这些数据单纯凭直觉说“快了好多”是无法说服团队长期维护这些优化的。6. 常见问题与排查技巧实录6.1 批量操作中的重复数据和唯一键冲突批量 insert 上线后生产环境很快就遇到了第一个问题导入的数据里存在重复的业务单号唯一键冲突导致整批回滚。虽然我在代码层做了去重但去重逻辑只考虑了单批数据内部的重复没有考虑和历史数据库里已有数据的重复结果线上还是报错了。排查的过程比较曲折。刚开始只看到异常是 Duplicate entry业务方反馈说导入失败了但不知道具体是哪一行。我把导入程序做了改造在批量 insert 出现异常时先用二分法定位出具体冲突的那几条数据然后打印出来给业务方确认这才定位到问题。现在我在批量导入框架里会默认开启一个“冲突检测”模式先把待插入的数据按唯一键去数据库里查一遍剔除已存在的再执行插入。虽然多了一次查询但在数据量不是特别大的场景下这个代价是完全可以接受的。6.2 update 优化后出现的主从延迟另一个印象深刻的问题是在 update 和 insert 优化上线后出现了明显的主从延迟。原因是优化后单位时间内写入的数据量变大、事务变少但单个事务产生的 binlog 大小也变大了。从库在复制大批量事务时需要执行更长的时间导致从库落后主库几百毫秒甚至几秒。最直接的解决方案是调整从库的并行复制参数同时把批量任务安排在业务低峰期执行。另外我调整了批量操作的事务粒度把一个 10000 条的大事务拆成多个 1000 条的小事务让主库的提交频率更高、单事务耗时更短从库也能更快地追赶。这个调整也再次印证了前面说的批次大小不是越大越好需要在写入性能和复制延迟之间找平衡。6.3 一次诡异的索引失效排查排查过程中还有一次很典型的索引失效问题。某个查询的 WHERE 条件里是biz_id 123456但 biz_id 字段在表里的类型是 varchar而 Java 代码传入的参数是 Long 类型MyBatis 在拼 SQL 时会把参数处理成字符串还是数字取决于 jdbcType 的设置。当时没有显式指定 jdbcType结果传入的类型引发了隐式类型转换MySQL 优化器认为需要转换后才能比较直接放弃索引走了全表扫描。这个问题的规避办法很简单查询参数显式使用jdbcTypeVARCHAR或者保证 Java 参数类型和数据库字段类型一致。我在代码规范里加了一条所有 varchar 类型的字段查询都必须显式标注 jdbcType不允许依赖数据库的隐式转换。这之后类似的问题就没再出现过。另外排查这些问题时我养成了一个习惯每次改写 SQL先用EXPLAIN ANALYZE或者EXPLAIN FORMATJSON看看执行计划确认索引确实被用上了再上生产。刚开始改 select 的时候我以为加完索引就万事大吉结果有一次执行计划显示优化器选择了另一个区分度更低的索引导致性能不升反降后来通过FORCE INDEX强制指定索引才解决。现在我对每条关键查询都要看执行计划不看就不放心。写在最后的几点体会这次优化做了大概两周时间最终的结果是数据库 CPU 使用率从 80% 以上降到 20% 左右慢查询数量基本清零接口 TP99 稳定在几百毫秒以内批量导入 1000 条数据从 12 秒降到 1.2 秒。没有加缓存中间件没有换数据库纯粹靠 SQL 改写和 Java 代码调用方式调整就拿到了十倍以上的性能提升。我个人最大的体会是做数据优化一定要先量化再动手优化之前把慢 SQL 和监控基线准备好每完成一步改动就用数据验证效果而不是凭感觉。很多性能问题的根源不在数据库而在于应用层怎么写 SQL、怎么管理事务。insert、update、select 这三类操作的优化是所有后端开发者都会遇到的场景掌握批量化、索引化、事务最小化这几个基本原则大部分性能问题都能迎刃而解。如果你也在处理类似的问题建议先打开慢查询日志看看最耗时的三类 SQL 分别长什么样然后照着这篇文章里的思路逐个击破应该会有非常直接的回报。