1. 先给程序操作优化划个边界别什么都往里面装数据库性能优化这个系列写到第三篇前两篇我分别聊了架构层面的水平拆分、垂直拆分以及数据库实例本身的参数调优、索引设计。有读者留言问前两篇讲的东西我都照着做了慢查询也抓了索引也加了但业务高峰期该卡还是卡怎么办我的回答是你大概率没看程序是怎么操作数据库的。所谓程序操作优化指的是不改表结构、不改索引、不调数据库参数单纯从应用代码访问数据库的方式上找问题、做优化。换句话说数据库已经把能做的都做到位了接下来要看你的代码有没有好好用它。这个环节经常被忽略因为它的优化点散落在业务代码里不像加个索引那么立竿见影但它往往是性能问题真正的藏身之处。程序操作优化涵盖的面很广从连接池怎么配、SQL怎么写、事务怎么开、批量操作怎么做到并发场景下怎么避免锁冲突都属于这个范畴。这篇文章不会讲怎么改数据库配置也不会讲怎么调服务器参数我只讲一件事程序侧怎么做才能让数据库跑得更顺。适合那些已经做完基础优化、但性能仍不达标的团队参考也适合准备做系统性能整改的开发同学当一份查漏补缺的清单。2. 连接池配置一个参数设错数据库直接假死2.1 连接数不是越大越好连接池也不是装饰品先说一个我实际遇到过的案例。某业务系统在做压测的时候数据库CPU使用率不到30%但接口平均响应时间从50ms一路涨到800ms最终大量请求超时。查了半天发现应用侧的数据库连接池最大连接数被某个同事从50改成了500。原因是他觉得连接多一点并发能力就强一点。这就是最典型的连接池误用。连接不是免费的每一条数据库连接都要占用数据库端的进程/线程资源、内存资源和锁资源。MySQL里连接数上限默认是151你一个连接池要500个连接光排队建立连接就能把数据库拖垮。而且当连接数过大时数据库的上下文切换消耗会急剧上升响应时间不降反升。连接池存在的意义不是无限提供连接而是复用连接减少建连开销。建连是个重操作TCP握手、鉴权、会话初始化动辄几十毫秒。如果每次请求都新建连接响应慢不说数据库还得不停地fork线程来处理新连接。2.2 连接池参数的推荐配置与调整逻辑连接池的核心参数无非这几个最小连接数、最大连接数、连接空闲超时、获取连接超时。我整理了一份常见的初始配置参考参数推荐初始值说明最小连接数5~10保证低峰期也有可用连接避免突发请求全部走建连流程最大连接数数据库上限的50%~70%给其他工具、管理端留余量别把数据库撑满连接空闲超时30~60秒太短会导致频繁建连太长会占用空闲资源获取连接超时3~5秒超过这个时间直接快速失败别让请求无限等下去启动时初始化多少连接、高峰期允许扩展到多少连接这个配比要结合QPS和数据库的负载能力来定。我的经验是最大连接数要小于数据库实例允许的最大连接数并且需要预留20%~30%的余量。至于单条SQL的执行时长建议通过连接池的连接最大存活时间参数定期回收连接防止数据库端主动断开后应用还在使用僵死连接。2.3 最容易踩的坑获取连接后不释放程序操作层面的连接泄露是个老生常谈的问题但直到今天还在发生。有的团队用了连接池以为就万事大吉了结果在异常分支里忘了归还连接或者在使用try-with-resources之前的老代码里finally块漏写了close。连接池能复用的是归还的连接你不归还池里的连接越用越少最后全部请求阻塞在获取连接这一步。排查连接泄露有一个简单实用的方法在连接池监控里查看活跃连接数。如果压测结束后活跃连接数迟迟降不下来多半就是泄露了。另外建议使用连接池自带的泄露检测能力比如HikariCP的leakDetectionThreshold参数设置为5000ms就能在连接被占用超过5秒时输出告警日志定位到具体代码栈。3. 批量操作的正确姿势从一条一条来到攒一批一起上3.1 为什么逐条INSERT会那么慢先看一段常见代码for (User user : userList) { jdbcTemplate.update(INSERT INTO t_user (name, age) VALUES (?, ?), user.getName(), user.getAge()); }如果这段代码处理的是1000条数据那么向数据库发起了1000次INSERT。每一次INSERT都要经历一次网络往返、一次SQL解析、一次事务日志写入。这里有个被很多人忽略的点即使你在同一个事务里执行这一千条INSERT每一条仍然是独立的SQL执行数据库对每一条都要做完整的处理流程。实测下来逐条插入1000条数据在本地MySQL上大约需要1.2~1.8秒如果用批量方式把1000条组装成一条多值INSERT耗时能降到100~200毫秒。差距接近10倍。3.2 多值INSERT、分批提交与事务边界批量插入最常用的写法是多值INSERTINSERT INTO t_user (name, age) VALUES (张三, 18), (李四, 19), (王五, 20), ...一条SQL带几百上千个value是MySQL支持的每个value会生成一行数据。但凡事有度我见过有人把5万条数据一次性拼成一条SQL直接把数据库的max_allowed_packet打爆。根据实际压测每批500~1000条是比较稳的量级超过2000条后SQL解析和网络传输的耗时就开始明显上升。再说事务边界。批量操作应该在每个批次结束后提交事务而不是等所有批次完成后再统一提交。假设你有10万条数据要插入分100批每批1000条。如果每一批单独提交单个事务只涉及1000条数据事务日志小、锁持有时间短。如果10万条放一个事务里虽然中途失败可以全部回滚但事务日志巨大锁范围长期占用对数据库的冲击不小。3.3 批量UPDATE和批量DELETE的特殊风险批量UPDATE比批量INSERT更需要注意。逐条UPDATE的问题和INSERT类似但不建议直接把多条UPDATE拼成一条SQL因为UPDATE没法像INSERT那样做多值优化。正确的做法是使用CASE WHEN改写UPDATE t_user SET age CASE id WHEN 1 THEN 18 WHEN 2 THEN 20 WHEN 3 THEN 22 END WHERE id IN (1, 2, 3)这种方式一条SQL完成多个行的更新只需要一次SQL解析、一次表扫描效率远高于逐条UPDATE。不过要注意控制IN列表里的id数量我建议不超过1000个否则SQL太长解析时间和网络传输时间都会增加。批量DELETE有个风险点一次删除大量数据会持有大量行锁影响并发的读写同时会生成大量binlog主从复制可能因此产生延迟。安全做法是分批删除每批500~1000条加LIMIT条件并适当sleep一段时间。DELETE FROM t_log WHERE create_time 2024-01-01 LIMIT 1000;每执行一次检查受影响行数如果等于1000说明可能还有存量继续执行下一批。如果小于1000说明删完了循环结束。4. 事务边界设计短事务是银弹长事务是事故现场4.1 一个攒一批再提交的事务引发的连锁反应有个经典翻车案例可以说明事务边界的重要性。某订单系统的定时任务每5分钟执行一次每次读取500条待处理订单然后逐条处理并更新状态。为了保证原子性开发人员把整个任务包在一个大事务里任务跑完才提交。任务正常跑完需要20秒也就是说事务持有这些订单行锁的时间是20秒。问题在于这些订单状态更新锁住的不仅仅是本批次的500条数据——数据库的行锁在MVCC机制下会影响相同主键的并发更新。前端用户查看订单列表不受影响但用户A在手机端修改地址恰好改的是这批订单里的一条就会一直等待直到事务提交。高峰期多个定时任务叠加锁等待越积越多最后数据库的活跃连接数暴涨系统整体响应变慢。这个案例暴露了两个问题事务太大、执行时间太长。事务的基本原则是能短则短干了什么就立刻提交没事别占着连接不放手。4.2 事务中的查询、远程调用和顺手操作很多人意识不到当事务开启之后事务内的所有SELECT不加锁读也是要读快照的长事务会让快照版本堆积导致undo log膨胀查询也可能变慢。更常见的是有人在事务内做了本不该做的事。我见过最离谱的情况是在事务里调用了外部HTTP接口等待第三方返回结果。一个HTTP请求少说需要几百毫秒这期间数据库连接被占用、事务迟迟不提交、相关行的锁一直没释放。一旦第三方接口超时整个事务回滚数据库连接被长时间占用。这种顺手操作最坑人。事务边界设计的几条硬性要求事务内只做数据库操作不做远程调用、不做文件读写、不发送消息事务内不要包含大批量查询查询时间越长事务持锁越久事务要有明确的结束点该提交就提交不要等GC帮你close一个业务操作拆多个事务比如扣库存一个事务、更新订单状态一个事务、记账一个事务而不是从头到尾一个大事务4.3 自动提交与隐式事务的坑MySQL默认autocommit1每一条SQL都是独立事务自动提交。很多人觉得反正自动提交就不用显式开事务了。但程序操作优化恰恰建议把autocommit关掉显式控制事务。原因很简单当多条SQL需要保证一致性时自动提交会导致一部分成功、一部分失败数据不一致。当单条SQL不涉及事务时也许没问题但在批量操作场景下配合自动提交的逐条INSERT会每条都刷新一次事务日志性能极差。更隐蔽的是ORM框架的坑。Hibernate的open-in-view视图模式会在整个请求周期内保持数据库事务和会话打开包括渲染模板的时间。这么做只是为了让视图层可以懒加载数据代价是事务持有时间长、连接占用时间长、锁范围不可控。我建议项目里关掉open-in-view在Service层手动控制事务边界。5. SQL写法的程序层改造同一个查询换种写法性能差十倍5.1 从能查到到查得快ORM生成SQL的隐性问题程序操作优化绕不开SQL改写。用ORM框架MyBatis、Hibernate的人经常只看Mapper里的方法名不看生成的SQL是什么。但ORM生成的SQL往往不是最优的。一个典型问题是N1查询。比如查询用户列表时要先查所有用户然后循环遍历用户再去查每个用户的订单信息。10个用户就要执行11次SQL。数据量上来、并发一高这种写法会直接拖垮数据库。解决方案是改成JOIN一次性查出数据或者在ORM里使用批量查询IN代替循环单查。// 错误示范循环查询 ListUser users userMapper.findAll(); for (User user : users) { ListOrder orders orderMapper.findByUserId(user.getId()); ... } // 正确做法一次查询或IN批量查询 ListOrder orders orderMapper.findByUserIds(userIds);IN查询里要注意IN列表的长度。IN后面跟的ID数量越大SQL解析越慢而且MySQL对IN列表的优化在某些版本有限制。建议拆分成每批500~1000个ID。5.2 深翻页与OFFSET陷阱分页查询是程序操作优化里绕不开的坑。LIMIT 100000, 20这种写法在数据量达到几十万行时会让数据库扫描前面10万行然后丢弃只把最后20行返回给应用。这种深翻页操作随着页码增大越来越慢。解决深翻页的思路有三种。一是基于游标的分页用上一页最后一条记录的ID作为下一页的查询条件SELECT * FROM t_order WHERE id #{lastOrderId} ORDER BY id LIMIT 20;这种方式走主键索引翻到第10000页也不会变慢。二是延迟关联先查出ID再关联原表获取完整数据SELECT o.* FROM t_order o INNER JOIN (SELECT id FROM t_order ORDER BY id LIMIT 100000, 20) t ON o.id t.id;三是限制最大翻页深度产品层面直接禁止用户翻到5000页以后搜索引擎也只展示前几十页。这三种方法我都用过实操中最推荐的还是基于游标的分页没有多余语法索引利用率最高。5.3 循环中操作数据库性能杀手No.1有一个非常常见的反模式我在代码评审里见了无数次在for循环里调用数据库操作。不管是SELECT还是UPDATE只要循环里有数据库访问就要警觉。哪怕单次查询只要1毫秒循环1000次就是1秒而且数据库连接被单线程独占其他请求全部排队。怎么根治思路只有一个把循环内的数据访问提到循环外。先收集所有需要的数据ID一次SQL查出在内存里做关联或者用批量UPDATE代替循环子查询。循环内的UPDATE尤其坑人。比如订单状态流转每个订单根据自身状态计算下一个状态开发人员很容易写出循环更新的代码。但状态流转本身是可以抽象成批量的先查出所有符合条件的订单在内存中算出它们各自的新状态再拼成CASE WHEN批量UPDATE。循环单更新和批量更新性能差距在数据量达到万级以上时是数量级的差异。6. 并发控制与锁冲突程序侧能做的比想象中多6.1 锁竞争的本质为什么并发越高性能下降越夸张数据库并发操作的性能瓶颈很多时候不是CPU、不是磁盘I/O而是锁冲突。多个事务同时操作同一行数据时后到的事务必须等待前面的事务释放锁。等待的人越多系统吞吐量就越低。程序操作优化要解决的问题就是减少锁冲突、缩短锁持有时间。上一节讲的事务边界是缩短锁持有时间的关键手段这一节讲的是改变操作顺序和粒度。先看一个死锁案例的日志Transaction A持有行1的锁等待行2的锁 Transaction B持有行2的锁等待行1的锁两个事务互相等待谁也无法继续执行数据库的死锁检测机制会杀死其中一个事务让它回滚。这种死锁在程序操作层面最常见的成因是两个事务以不同的顺序操作多张表或行。6.2 统一操作顺序最简单有效的死锁规避手段规避死锁有一个百试不爽的土办法规定一个全局统一的业务对象操作顺序。比如涉及用户账户和订单表更新的业务所有代码都先更新用户账户、再更新订单表事务A和事务B就不会出现互相持有对方资源的情况。如果一次性操作多行还要保证行之间的顺序一致性。比如批量转账要么都按账户ID从小到大排序要么都按固定的业务主键排序。只要不同事务对同一组行的访问顺序一致死锁的概率会降到极低。6.3 悲观锁与乐观锁的选择程序操作层面控制并发还有两个常用手段悲观锁SELECT...FOR UPDATE和乐观锁版本号/CAS。我在实际项目中的选型经验是读多写少的场景优先乐观锁。版本号字段加一次UPDATE冲突时重试即可不会阻塞读请求。写多读少、且冲突概率高的场景悲观锁更可靠。FOR UPDATE会锁住行后续读会被阻塞但保证了严格串行化。库存扣减、金额变动这类强一致场景推荐行级锁或乐观锁重试而不是表锁。锁粒度能小则小能锁行不锁表能锁记录不锁间隙。InnoDB的间隙锁在某些隔离级别下会自动启用可能会锁住一个范围内的所有间隙这比行锁更可怕。如果业务允许可以考虑将隔离级别降为READ COMMITTED来避免间隙锁带来的锁冲突。6.4 避免一个常见的锁表操作很多人不知道表结构变更DDL在MySQL 5.6及之前的版本会锁全表即使是最新的MySQL 8.0在线DDL在某些阶段也会持有元数据锁。如果程序里有动态建表、动态加字段的逻辑且与业务高峰期重叠很容易引发大面积锁等待。我见过一个坑为了记录业务日志程序每天都动态创建一个新表建完再往里插数据。DDL执行期间所有涉及同库的查询全部被阻塞因为元数据锁是全局性的。后来改成了预创建表结构、按日期分表问题消失。这个案例的教训是程序里不要频繁做DDL操作表结构变动必须走审核流程并安排在低峰期执行。7. 优化效果的验证方法从感觉变快了到数据证明变快了7.1 先量化再动手压测和慢查询日志程序操作优化做了一堆改动之后怎么证明真的有效靠感觉是不行的必须有数据支撑。推荐的做法是优化前后各做一轮压测对比同一并发级别下的吞吐量TPS/QPS、响应时间P99/P95、数据库活跃连接数和锁等待次数。光靠压测还不够。慢查询日志是发现程序操作问题最直接的来源。开启慢查询日志后把执行时间超过100毫秒的SQL全部捞出来分析你会发现大量耗时SQL的根源不是索引而是程序层面的设计问题循环查询、深翻页、大事务、锁等待。7.2 程序侧监控指标做程序操作优化需要在应用侧埋几个关键监控指标数据库连接池活跃连接数观察波动情况。如果频繁打满要么最大连接数不够要么存在连接泄露。事务平均耗时、最长耗时超过1秒的事务需要重点关注查查它到底做了什么操作。锁等待次数通过SHOW STATUS LIKE Innodb_row_lock%查询当前行锁的等待次数和等待时长。SQL执行次数分布哪条SQL被调用得最多、最频繁它就是性能优化的优先对象。7.3 一套我自己常用的优化前后对比模板实际操作时我会做一个简单的对照表每次优化前后各填一份指标优化前优化后变化TPS每秒事务数3201050提升228%P99响应时间800ms180ms下降77%数据库活跃连接数峰值18045释放大量连接锁等待次数/小时230045下降98%慢查询数/小时853下降96%有了这张表才能确认每次改动到底是真有效还是心理安慰。没有数据支撑的优化很容易陷入改来改去不知道哪里对了哪里错了的泥潭。就我个人的经验来说程序操作优化最迷人的一点是它不需要增加任何硬件资源不需要改任何数据库结构完全靠代码层面的调整就能拿到成倍的性能收益。连接池配好、批量操作做起来、事务边界划清楚、SQL改写做到位、并发访问理明白这一套流程走下来大多数系统的性能问题都能解决七八成。剩下的一部分才需要去考虑读写分离、分库分表、引入缓存中间件这些更重的方案。希望这篇能帮你少走一些弯路。