做数据库性能优化的前两篇我们聊了SQL改写、索引设计这些“单点提速”手段。可做完这轮优化我在不少项目里发现一个很尴尬的情况慢SQL清单越拉越短数据库负载却还是压不住。有一次线上系统高峰期数据库端CPU才40%连接数却直接打满客户端报错全是“连接池超时”。当时我就意识到性能问题不能只盯着SQL本身还要看程序是怎么操作数据库的——这就是这一篇要聊的程序操作优化。如果说SQL优化是“把每条请求做得更高效”那程序操作优化就是“从源头控制有多少请求进来、请求在数据库上待多久、资源是否被白白占用”。它的价值不是让一条慢SQL变快而是让数据库压根不去执行那些本不该执行的SQL让事务别占着锁和连接不撒手让应用线程别在数据库连接上干等。这篇内容适合后端开发、架构师和DBA一起看尤其是那些用了ORM框架、写了不少业务接口、又经常和数据库性能问题纠缠的团队。下面按我实际排查问题时的思路从连接、事务、批量、ORM、缓存几个层面逐个拆。1. 先给性能问题归个类程序操作层到底管什么数据库性能优化里有个分层的思路我习惯把它分作三层最底层是SQL和索引解决单条语句怎么执行得更快中间层是架构解决读写分离、分片、容量规划的问题最上层就是程序操作解决应用与数据库之间的交互方式、资源使用方式是否合理。三层之间存在一个容易被忽略的联动即使每条SQL都被索引和优化器伺候得很快程序操作层的一个小失误比如循环里发起了100次查询、一个事务里锁了上万行数据、一个请求连接了3次数据库也能把整体性能拖垮。我在某电商系统的订单模块排查过一个问题订单列表接口看着只执行了一条查询但ORM懒加载导致每条订单都去查一次用户信息和收货地址一次请求实际触发了50多条SQL。数据库单条SQL都毫秒级完成接口却因为80%的时间耗在多次往返上而变得极慢。这种问题单靠索引是救不了的必须回到程序层来改。程序操作优化的核心其实就是管好四件事连接管理连接数是否合理、连接生命周期是否可控、有没有连接泄漏。事务控制事务粒度是否足够小、锁的范围是否被无谓放大、隔离级别是否合适。交互策略程序和数据库之间的往返次数是否被最小化、批处理是否被正确使用。数据访问模式ORM使用方式是否合理、缓存有没有在正确的位置发挥价值。后面几节就按这四条线展开。每一项都对应着我实际踩过、也帮别人擦过屁股的坑方法论不难难的是把习惯改过来。2. 连接池不是配得越大越好关键在池子的“周转率”2.1 一条数据库连接到底有多贵很多新手的理解是“连接池越大性能越好”于是把最大连接数从50调成500结果数据库更早被打爆。要理解连接为什么贵重得先算一笔账。以某主流关系型数据库为例每建立一条TCP连接服务端在握手阶段要做协议协商、权限校验然后为这条连接分配线程栈、解析上下文、各类缓存结构。内存开销通常在几百KB到数MB的量级。连接紧不紧张时感知不到一旦并发起来内存和线程数都会快速膨胀。再加上应用端每一次获取连接都有锁竞争和队列调度池子里的连接数量越多线程换手的成本也越高。更关键的是连接的创建速度。新连接从发起到真正可用在物理链路正常情况下也要几次网络往返。假设一条连接创建耗时5~20毫秒高峰期若需要每秒新建50条连接光是建连就会吃掉一部分服务端CPU而且是纯浪费——这些连接用完又要销毁。所以连接池的意义不是“多”而是“平滑复用”让连接像餐具一样循环使用而不是一次性纸杯。2.2 连接池参数怎么定我给一套可落地的思路连接池参数在不同框架里名字略有差异核心指标是一致的。我一般不会直接给死数字而是用一个估算公式作为起点再靠压测微调最大连接数 接口高峰期并发请求数 × (单请求中数据库占用时间 / 单请求总耗时)举个例子某接口高峰期QPS为500平均响应时间为200毫秒其中数据库实际执行占80毫秒。在200毫秒内每个请求把持连接的时间大约是80毫秒理论上同时需要的连接数为 500 × (80/1000) ≈ 40但考虑到请求可能有排队、偶发慢SQL、连接池内部等待一般再乘上1.5~2的冗余系数初始配置60~80是合理的。常见参数与调整经验我整理成了这张表参数设置建议说明最小空闲连接数与业务闲时流量匹配设置过大会让池子常年维持大量空闲连接浪费数据库资源最大连接数按上述公式计算后再留冗余不要轻易给到几百先压测看数据库和应用的承受能力连接获取超时1~3秒比较合适太短容易被瞬时流量击垮太长会让请求线程集体阻塞空闲连接存活时间5~10分钟较常见太短会导致高峰期频繁新建连接太长则维持无效连接连接最大存活时间建议4~8小时内轮回长时间复用同一连接可能被服务端清理、路由变化影响旧连接连接检测SQL高并发下慎重开启每次获取连接多一次心跳压力大时反而成为额外DB开销2.3 真正该重点盯的是连接泄漏和连接“被持有太久”参数再准程序写法有问题也白搭。团队最常见的问题是连接泄漏用框架时连接随事务自动释放大家没感觉一旦走了原生JDBC、或者ORM封装不当Connection取出来后没有在finally里关闭连接就永远还不回池子。我们排查过一次故障一个小功能上线后连接池从总数50被吃到满活跃连接才20剩下的30全部是泄漏的“僵尸连接”。这种问题单看监控图表现为连接数一条直线上升直到封顶线程全部阻塞在获取连接上。比泄漏更隐蔽的是短时间内长期持有连接。我见过一个反模式业务线程在事务内调用外部支付接口等了8秒超时这期间数据库连接、事务和行锁全部被这个线程占着。一个这样的线程就能把一个热门商品的库存行锁住后面的下单请求全部排队。这就是典型的“不该在事务里干的事被放进了事务里”。从操作层面我给团队定了三条红线获取连接的代码必须和释放连接处于同一层级的作用域里并且用try-with-resources或finally保证释放。高并发接口里严禁在持有连接或事务期间做远程调用、IO读写、线程等待。监控必须覆盖“等待连接数”和“连接持有时间分布”超过阈值就告警而不是等用户报障。3. 事务控制的核心短、小、快3.1 长事务和宽事务是性能的隐形杀手数据库事务在“程序操作”里的地位非常特殊——它直接决定了锁的持有范围和时间。很多性能事故最初看是又臭又长的慢SQL背后其实是长事务导致的锁等待和回滚段膨胀。长事务的危害有三个我逐个拆第一锁的范围被无限放大。一个事务里如果先更新了库存表锁住一批行然后在事务里继续查询几十张业务表、甚至调外部接口这些锁的持有时间就被人为延长。第二个更新同一批行的请求全都会阻塞在锁等待上外部表现就是高峰期“大量慢SQL、数据库线程堆积”其实SQL本身一点都不慢是在等锁。第二一大堆联表更新如果都在同一事务里一旦某条语句出错需要回滚回滚的代价很大。数据库要借助undo信息恢复到事务开始时的状态事务执行越长、修改的行越多回滚就越慢。我见过一个批量对账任务单事务更新了百万行中途遇到死锁报错回滚跑了二十多分钟才完成这期间CPU和IO都被拖住线上其他操作跟着遭殃。第三主从复制延迟被放大。对于基于日志复制的主从架构一个长事务在主库上执行多久从库就要重放多久主从延迟必然会拉大。偏偏长事务又通常发生在写入密集的大查询场景延迟会进一步造成读写不一致的串扰。3.2 事务粒度的判断这件事值得被放进事务吗对事务边界的判断我的建议是一句话事务里只保留必须保证原子性的数据读写其他一切统统移出去。读操作是最容易被误拉进事务的。只读查询比如查商品详情、查用户信息根本不需要包裹事务带Transactional反而会在高并发下多占用连接、延长事务上下文。核心设计原则是如果业务上不需要回滚那就不要开事务。再来看写事务的常规操作我列一个规范模板先做参数校验、权限判断、外部接口调用、写日志。然后开启事务。在事务内只做必要的数据库读写操作。尽快提交。提交后再去做消息推送、缓存更新、通知等非核心动作。这个流程看着简单实际上能干掉一半的事务持有时间问题。很多线上事故的根源恰恰是把步骤1和步骤5的行动全塞进了步骤2和步骤4之间。还有一些项目会把事务放在Service层方法里不小心形成事务嵌套。两层事务嵌套会让内层事务的提交依赖于外层锁持有的时间会被加长更危险的是有些人习惯在事务里加try-catch吞掉异常导致回滚失效数据就错乱了。我的经验是事务边界统一放在Service方法入口处Controller层和DAO层都不要开事务如果必须嵌套务必明确传播行为。3.3 隔离级别别瞎调要理解每种级别的锁代价隔离级别是事务优化的另一个变量。MySQL的默认级别是“可重复读”PostgreSQL、Oracle则默认“读已提交”。两者的关键差异在于可重复读在读操作时也会施加更长范围的锁尤其在有索引的查询场景里可能用间隙锁锁住一个区间后续插入的数据会被卡住读已提交的锁范围更小并发插入通常更顺畅。我们在某个秒杀系统里把一批只读报表查询的会话级别从可重复读调整成了读已提交数据库的锁等待量立刻有了明显下降。但注意这不是说读已提交一定更好——它允许同一事务内两次查询结果不一致如果业务强依赖可重复读语义比如先查余额再扣减期间希望余额不变化贸然调整就会出数据语义问题。任何隔离级别的调整都要业务团队确认过语义再由DBA统一评估变更。另外提醒一句事务里修改数据量越大、涉及的索引越多锁的粒度可能从行锁升级到表锁这是最需要警惕的。程序侧要控制单事务的更新行数比如批量任务拆成每1000条一个事务宁可多提交几次也别一个超级大事务把半张表锁死。4. 减少交互次数一次SQL能干的绝不放行10次4.1 往返成本比你想的贵得多程序操作优化里我见过收益最立竿见影的手段就是“批量化”。每一条SQL不管多快至少要走一遍网络往返、一轮解析和权限检查。如果一条业务逻辑被拆成100次数据库往返哪怕每次只有0.5毫秒光往返开销就要50毫秒再加上网络延迟和连接竞争整体耗时轻松飙升。有个我经常拿出来讲的反例某报表导出功能代码里写了个循环遍历1万个用户ID每次执行一次查询用户信息的SQL。单次查询虽然走索引只要0.3毫秒乘以1万就是3秒如果改成批量查询用IN条件一次取回1万个用户或者拆成每500个ID一批往返次数从1万降到20接口耗时能从秒级直接砍到两百毫秒以内。这种优化不需要动任何索引纯粹是交互策略的改变。4.2 批量插入的正确姿势批量插入是最常被写坏的操作。初学阶段很多人习惯在事务里循环单条插入每一条都是独立SQL数据库要反复处理日志刷盘、索引更新效率极低。正确的思路有两种一条SQL插入多行INSERT INTO t (a,b) VALUES (1,2),(3,4),(5,6)...这样解析和网络往返只有一次。注意行数不要无限膨胀一般500~2000行一批比较稳超过这个量级SQL包太大反而会加重网络和解析负担。使用数据库原生的导入工具或批量接口。比如从文件导入数据的场景用导入工具比逐行INSERT快很多尤其适合初始化数据、迁移数据场景。如果批量插入中还涉及主键生成强烈建议在应用层统一生成有序主键如基于时间戳的雪花ID衍生方式而不是让每行都回查一次自增主键。否则为了拿到ID又得多几次交互批量的红利就会被抵消。4.3 批量更新和千万级分页的耗时陷阱批量更新的套路是UPDATE ... CASE WHEN ... WHERE id IN (...)把多条update合并成一条。比如锁定一批订单状态原来循环每个订单执行一条UPDATE现在可以用一条语句按ID批量匹配更新。这样减少了往返次数也让锁持有时间更加集中。但要注意CASE WHEN语句如果更新行数特别多、WHERE条件又没有覆盖索引容易造成大范围扫描所以批量更新前一定要确认数据量级。分页的深翻页问题也属于程序交互策略。传统实现是LIMIT offset, size当offset走到几十万数据库要扫描并丢弃前面几十万行才能返回结果越往后越慢。优化方式基本就两种如果只是通过主键排序可以用“键集分页”记住上一页最后一条ID下一页用WHERE id 上次最后ID ORDER BY id LIMIT size如果用非主键排序先通过覆盖索引定位到目标记录的ID再回表取数据。这两种方式本质上是把“一次大范围扫描”变成“一段精准定位”翻页越深收益越大。4.4 存储过程与批处理什么时候才该把程序逻辑放进数据库聊到减少交互次数很多人会问存储过程值不值得用。我的态度很明确业务逻辑严禁塞进存储过程但数据密集型批处理可以适度使用。业务逻辑塞进存储过程的坏处很多版本管理困难、很难测试、不易水平扩展数据库端CPU还会被业务计算大量消耗。我更推荐的做法是——把复杂的多步骤数据加工、大量数据的转换搬运用数据库端的存储过程或批处理脚本完成因为这类任务本质就是数据处理放进数据库可以省去大量数据传输环节。比如某定时任务要把A表几百万行经过逻辑转换写入B表用程序逐行读取再写回既慢又占带宽直接在数据库内做一批SQL转换整体耗时能降一个量级。这里面有个取舍原则程序与数据库之间交互频繁、数据在应用端和数据库端来回倒腾的操作优先考虑批量化和数据库端处理而需要业务判断、外部依赖、异步编排的逻辑留在应用层做不要硬塞进数据库。5. ORM是效率神器也是性能事故高发区5.1 N1查询每个程序员都会遇到的坑用了ORM框架之后最常见的性能事故就是N1查询。典型场景是查出订单列表后业务代码遍历订单去访问每个关联的用户信息。ORM默认懒加载时并不会在查订单时带出用户数据而是在循环里第一次访问用户属性时才触发一条按ID查用户的SQL。如果一页50条订单就会额外发出50条查询加在一起就是51条——N1的名字就是这么来的。这个问题在高QPS接口里是致命的。我建议团队上线前做一遍代码Review专门找这种循环访问关系的写法。解决办法也很成熟查询时通过预加载或显式关联抓取把关联数据提前一次取出。如果业务只是需要部分字段直接在SQL里只查需要的列不要图省事把整个实体拖出来。在ORM的查询日志里开启慢查询统计看到类似“同一结构SQL反复执行几十次”的日志基本就可以判断是N1。5.2 ORM生成SQL的质量问题ORM自动生成的SQL在常规场景没问题但遇到复杂查询就会露怯。举三个我实际遇到的情况第一ORM的多对多关联自动拼接JOIN生成的SQL经常在无索引列上做关联扫描行数爆炸。此时老老实实改成手写精确的查询明确连接条件和索引利用效率会有质的提升。第二动态条件构造时ORM的“特性”会把所有条件用OR拼接到一起优化器无法有效利用索引甚至直接走全表扫描。这种情况的规避方法是要小心地构造查询确保属性条件在必要的地方与索引匹配。第三统计类查询。ORM生成的COUNT在部分实现里会自动加上一层COUNT(*)包住整张表如果表上百万行即使业务只关心少量条件也会把全表扫一遍。数据量上来后统计类接口我都会让技术团队改成手写SQL并自己把控执行计划。还有一类和ORM关系密切的写法问题是索引失效。程序里经常写WHERE DATE(create_time) 2024-01-01看着挺规整但实际上对create_time列用了函数索引直接失效。正确写法是WHERE create_time 2024-01-01 00:00:00 AND create_time 2024-01-02 00:00:00让时间范围条件落到索引可用区间。这类问题不是SQL本身慢是程序写法把索引废掉了。5.3 批量操作别走实体循环走Bulk接口ORM框架面对批量更新时如果代码写成“查出实体列表 → 循环修改实体属性 → 逐个提交”每一行的变更都会触发一次UPDATE SQL涉及N行就是N次UPDATE。正确的姿势是直接调用批量更新接口一次提交一条按ID批量更新的SQL既减少了交互次数也缩短了锁持有时间。同理批量删除也不要循环执行单条DELETE。如果一个业务需要删除某些条件下的1000条记录一条DELETE ... WHERE ... IN (...)就搞定。不过批量删除要考虑大事务影响超过合理行数时分成小批次提交。6. 应用层缓存为数据库挡住热点流量6.1 什么数据值得缓存什么数据不能缓存程序操作优化的另一个维度是——直接减少打到数据库的请求。应用层缓存是最有效的分流器但也要分清场景。适合缓存的数据有三类热点读数据比如秒杀页面的商品库存展示、首页配置、白名单列表这些数据读量极大、变化频率不高每次查库纯属浪费。重复计算型数据比如报表统计结果、排行榜本来要聚合几百万行才能算出来计算一次缓存一段时间即可。低频变化的配置型数据比如系统参数、字典项几乎不更新缓存后能显著降低数据库压力。不适合直接缓存的数据也有两类强一致性要求极高的数据比如各类扣减操作前的余额校验这类必须直接读库或加锁。频繁更新且每次更新都要求立刻生效的数据缓存命中率极低命中后还要处理复杂的同步问题不如不缓存。我见过一个团队把所有字典表数据都缓存了字典表更新后缓存却迟迟不清用户看到的新配置一直不生效最后查了半天才发现是缓存过期时间设成了8小时。缓存之前先明确一致性需求是基本前提。6.2 Cache Aside三件套穿透、击穿、雪崩的处理最常见也最稳妥的缓存策略是Cache Aside读请求先查缓存未命中再查数据库回填缓存写请求先更新数据库再删除或更新缓存。这个模式在大部分业务场景是对的但它逃不过三个经典问题。缓存穿透指查询一个根本不存在的数据每次请求都直接打到数据库。由于数据库查不到缓存无法回填所有请求都会穿透到库上。解决办法有两个对查不到的数据也缓存一个空值设置较短过期时间或者用布隆过滤器在缓存前拦掉一定范围的不存在请求。缓存击穿指某个热点key过期瞬间大量并发同时查这个key并回源数据库数据库瞬间承受集中压力。应对方法是用互斥锁控制回源——只允许一个请求去数据库加载其他请求等待或返回兜底值。缓存雪崩指大量key在同一时间过期导致数据库被密集访问打垮。最简单的处理是给缓存过期时间加一个随机偏移比如7~9小时之间随机过期避免“整齐划一”地失效。6.3 缓存一致性别过度设计但底线要守住缓存和数据库的一致性设计业内争论比较多我的保守建议是优先采用“先更新数据库再删除缓存”的策略因为删除缓存操作失败的概率比缓存与库不一致的概率更容易兜底。如果删除缓存失败可以引入延迟双删也就是在更新数据库后删除一次缓存短暂等待后再删除一次兜底第一次删除失败的场景。更严谨的做法是订阅数据库的变更日志做异步缓存刷新比如监听binlog事件把缓存刷新动作从请求路径中剥离出去业务逻辑更干净。不过要提醒缓存一致性方案很容易过度设计。很多业务场景根本不需要强一致允许秒级甚至分钟级延迟直接把过期时间设计成5秒就完事比任何双删、延迟双删都简单可靠。架构上少做一件事线上就少一类故障。7. 用监控和压测把程序操作层的问题“逼”出来7.1 线上监控看哪些指标程序操作优化做到位之后需要一套监控来持续验证而不是拍脑袋。我重点盯这些指标连接池活跃连接数、空闲连接数、等待获取连接的线程数这三个指标能提前暴露连接池打满的问题。数据库线程/会话数、锁等待次数、行锁等待时间用来发现事务边界和锁时长问题。单次请求的数据库往返次数这是程序操作层最直接的“健康度量”如果常规接口一次请求要发几十条SQL说明代码写得不收敛。慢SQL日志不只是看执行时间还要看等待时间占比和锁等待事件慢查询的原因往往在程序侧而不在SQL本身。7.2 压测时的几个“红线检查”新接口上线前我会用压测工具模拟流量重点验证三个点第一连接池是否能维持稳定压测过程中活跃连接数不应无限攀升等待获取连接的线程数应保持平稳。第二TPS在2倍峰值时打开慢查询日志观察是否出现大量“执行时间很短的慢查询”。如果有多半不是SQL问题而是锁等待或连接排队。第三压测结束后连接池活跃数应迅速回落到空闲水平如果长时间居高不下就要查是否存在连接泄漏或事务未正确提交。我在多个项目里验证过一件事程序操作层优化完成之后数据库连接数需求往往能下降30%~50%慢SQL数量也会因为减少无谓调用而同步下降。这些数据就是优化效果最好的证明。8. 常见问题速查与避坑清单我把这些年排查程序操作层问题的高频场景整理成了一张速查表遇到问题时可以直接对着找方向。现象大概率原因排查方法处理手段连接池被打满活跃连接不高连接泄漏检查栈和连接池监控看连接获取后是否关闭代码级检查补齐关闭逻辑重启后跟踪下降曲线线程大量阻塞在“拿连接”最大连接数偏小或持有时间过长看线程dump和连接等待耗时按公式重算连接数压缩事务与远程调用范围数据库CPU不高但接口很慢N1查询打开SQL日志统计请求内SQL条数预加载、批量查询替代循环查库高峰期出现集群慢SQL行锁等待查看锁等待事件和活动事务缩短事务时长缩小锁范围调整隔离级别批量插入极慢循环单条INSERT统计执行轮次改多值INSERT或分批批量插入深翻页查询越来越慢大OFFSET扫描查看执行计划的扫描行数键集分页或覆盖索引定位热点数据一过期数据库就崩缓存击穿/雪崩观察缓存过期时间和回源量加互斥锁回源过期时间加随机偏移报表接口全表扫描ORM生成低效SQL查看ORM生成的SQL执行计划改手写SQL并确认索引命中最后聊一个“看起来和性能没关系”的坑。有个项目的主键用字符串UUID数据量到千万级后写入越来越卡。单看程序没有任何问题——每条插入都很快但UUID的随机性导致索引页分裂和随机IO频发写入放大严重缓存命中率也低。后来把主键改成有序ID生成方案写入性能和索引维护成本显著改善。这件事提醒我程序操作优化不只是“少查几次、事务短一点”还包括应用层如何生成数据、如何设计键这些细节最终都会变成数据库的性能账单。程序操作优化的本质就是让应用对数据库的每一次访问都有的放矢不浪费资源也不制造无谓的等待。优化做得好的系统用户无感DBA无惊线上平静得像什么都没发生过而这恰恰是它最大的价值。