前一阵子接手了一个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 那一列是 ALL，rows 估算值到了百万级，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 变成了 ref，rows 降到了几百，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 循环里逐个查用户信息，产生了 N+1 次查询。
优化的思路比较简单粗暴：把关联查询提前到 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 log，1000 条数据就是 1000 次磁盘同步。实测下来，插入 1000 条数据耗时约 12 秒，平均每条 12 毫秒，这在本地网络环境下算非常慢了。
第一步改造是改成 MyBatis 的批量插入，也就是在 mapper XML 里写一个多值 INSERT，一条 SQL 包含多条记录：
<insert id="batchInsert"> INSERT INTO order_info (biz_id, product_id, amount, status, create_time) VALUES <foreach collection="list" item="item" 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 上开启一个参数：rewriteBatchedStatements=true。不加这个参数时，即使你在 Java 端使用 connection 的 batch API，MySQL 驱动也只会把多条 insert 语句逐条发送给服务器，性能提升有限。
我的操作是在项目的数据源配置里修改 JDBC URL：
jdbc:mysql://your-host:3306/your-db?useUnicode=true&characterEncoding=utf8&rewriteBatchedStatements=true&useSSL=false同时，在 MyBatis 中开启批量执行器：
SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH);两者配合的情况下，驱动程序会把一个 batch 里的所有 insert 重写为一条多值 INSERT 语句再发给数据库。我做了三组对比实验，数据如下：
| 策略 | 1000条耗时 | 事务次数 | 说明 |
|---|---|---|---|
| 循环单条 insert | 12秒 | 1000 | 每条一次网络往返+独立事务 |
| foreach 多值 INSERT，500条/批 | 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 id="batchUpdateStatus"> 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 优化器认为需要转换后才能比较，直接放弃索引走了全表扫描。
这个问题的规避办法很简单：查询参数显式使用jdbcType=VARCHAR，或者保证 Java 参数类型和数据库字段类型一致。我在代码规范里加了一条：所有 varchar 类型的字段查询，都必须显式标注 jdbcType，不允许依赖数据库的隐式转换。这之后类似的问题就没再出现过。
另外，排查这些问题时我养成了一个习惯：每次改写 SQL，先用EXPLAIN ANALYZE或者EXPLAIN FORMAT=JSON看看执行计划，确认索引确实被用上了再上生产。刚开始改 select 的时候，我以为加完索引就万事大吉，结果有一次执行计划显示优化器选择了另一个区分度更低的索引，导致性能不升反降，后来通过FORCE INDEX强制指定索引才解决。现在我对每条关键查询都要看执行计划，不看就不放心。
写在最后的几点体会
这次优化做了大概两周时间，最终的结果是：数据库 CPU 使用率从 80% 以上降到 20% 左右，慢查询数量基本清零，接口 TP99 稳定在几百毫秒以内，批量导入 1000 条数据从 12 秒降到 1.2 秒。没有加缓存中间件，没有换数据库，纯粹靠 SQL 改写和 Java 代码调用方式调整就拿到了十倍以上的性能提升。
我个人最大的体会是，做数据优化一定要先量化再动手，优化之前把慢 SQL 和监控基线准备好，每完成一步改动就用数据验证效果，而不是凭感觉。很多性能问题的根源不在数据库，而在于应用层怎么写 SQL、怎么管理事务。insert、update、select 这三类操作的优化是所有后端开发者都会遇到的场景，掌握批量化、索引化、事务最小化这几个基本原则，大部分性能问题都能迎刃而解。如果你也在处理类似的问题，建议先打开慢查询日志看看最耗时的三类 SQL 分别长什么样，然后照着这篇文章里的思路逐个击破，应该会有非常直接的回报。
