做后端这几年，有个感受特别明显：一说数据库性能差，大家的直觉反应就是看索引、调参数、加机器，但很多时候真正把数据库拖垮的，恰恰是程序里那些不起眼的操作习惯——循环里发查询、事务包裹了远程调用、连接池配得过大或过小、分页翻到几万页还在用大偏移。
这是数据库性能优化系列的第三篇，重点聚焦“程序操作优化”。前两篇如果聊的是数据库自身的调优和SQL语句的写法，那这篇要解决的问题就是：应用程序是怎么跟数据库交互的，哪些操作模式会让数据库白白扛着不该扛的负载。适用对象很明确：后端开发、DBA、技术负责人，尤其是被“数据库CPU飙高但SQL却很简单”这类问题困扰过的人。看完你会拿到一套可以直接抄的检查清单和优化套路。
1. 先弄明白：程序操作优化到底优化什么
1.1 数据库性能问题的责任边界
一条查询从发起端到数据库，要经过应用服务器、网络、数据库连接、SQL解析、执行计划生成、存储引擎扫描。绝大多数“数据库慢”的现场，最终指标都指向数据库本身物理资源偏高，但根因却藏在程序侧的交互模式里。
举个例子。线上遇到过MySQL CPU持续90%以上，一条SQL单看很简单，走主键，每次几毫秒。查了慢日志也找不到耗时大头。后来排查发现是应用启动了一个定时任务，每5秒循环跑几千次单价查询，每次循环都新建数据库连接。CPU全耗在连接建立和鉴权上，真正的SQL执行反而占比很小。这种情况，改程序操作方式的收益远大于调任何数据库参数。
责任边界一定要划清楚：数据库配置调优是改善“体力”，程序操作优化是改善“招式”。很多DBA被开发吐槽“数据库不行”，其实是被程序糟糕的访问模式背了锅。所以做性能优化，第一站永远是程序侧的操作审计。
1.2 从四个抓手入手
程序操作优化的覆盖面很广，但核心逃不出四个维度：
- 连接管理：连接怎么拿、怎么放、池子配多大，直接决定数据库端会话数量和资源开销。
- SQL执行模式：批量还是循环、分页怎么翻、N+1查询有没有，决定数据库扫描和传输的数据量。
- 事务设计：事务边界多大、锁持有多久、锁竞争怎么避免，决定并发场景下的吞吐上限。
- 数据访问策略：缓存怎么用、读写怎么分离、冷热数据如何处理，决定数据库到底要扛多少流量。
这四块对应了后续每一章的讲解。先有一个整体框架，再逐个补齐细节，逻辑才顺。
2. 连接管理：最容易出问题的第一关
2.1 连接池参数怎么定，才能既不拥塞又不浪费
程序操作数据库，最基础的就是连接管理。连接池配得不好，后果分两种极端：配小了应用层拿不到连接报错；配大了数据库被大量空闲连接拖累，内存和线程开销都上去了。很多团队直接用默认配置一跑就是一两年，这是不行的。
以Java生态常用的HikariCP为例，一组让我实测后觉得比较合理的起步参数是这样的：
spring: datasource: hikari: # 连接池最大连接数 maximum-pool-size: 20 # 最小空闲连接数 minimum-idle: 5 # 连接超时时间（毫秒），建议不要用默认的30秒 connection-timeout: 3000 # 空闲连接存活时间（毫秒） idle-timeout: 600000 # 连接最大存活时间（毫秒） max-lifetime: 1800000这里有几个容易被误解的点。
最大连接数不是越大越好。连接数过多时，数据库端每个连接背后都有独立的线程栈和内存，而且CPU上下文切换成本会剧增。业界常用一个估算公式：连接数 ≈ (核心数 × 2) + 有效磁盘数，这是《高性能MySQL》里提到的思路。不过说实话，更实用的做法是根据QPS和单次事务耗时来算。
举例：系统平均QPS为2000，其中写入和读取事务平均耗时T=20ms。那么并发事务量大约等于 QPS × T = 2000 × 0.02 = 40。这时候连接池上限配到50左右就够了，配到200反而有害。
提示：不要迷信任何固定公式。连接池大小应当根据压测结果持续调整。利用监控看数据库端最大活跃会话数，把这个值加上10%~20%的余量，就是比较合理的池子大小。
2.2 连接泄漏，线上事故的第一大隐形杀手
连接池参数配对了，还会遇到一个更隐蔽的问题——连接泄漏。程序从连接池拿了连接，用完不归还，久而久之池子里的连接全部被“借走”，新请求只能排队等待，轻则接口变慢，重则全线超时。
判断有没有泄漏，先看两个指标：
- 连接池监控里active连接数持续维持在maximum-pool-size，说明连接拿出去回不来。
- 数据库端
show processlist里出现大量Sleep状态的会话，且State长时间不变。
最常见的泄漏场景是这几类：
- 异常分支里catch到错误但没走close释放连接。
- 使用了ORM框架，但事务切面没配好，导致连接在事务提交前一直被占用。
- 手动获取了
Connection，却忘了用try-with-resources或finally释放。
排查连接泄漏，我在实际项目里用过一套很高效的方式。第一步，把connection-timeout调短到3秒，让泄漏暴露得更快——拿不到连接的请求会立刻失败，而不是无限等待。第二步，在HikariCP里打开泄漏检测：
spring: datasource: hikari: leak-detection-threshold: 60000超过60秒未归还的连接会被日志打印出来，包含获取连接时的调用堆栈，顺着堆栈就能定位到具体代码位置。这套组合拳帮我定位过不止一次线上故障，查出来都是某个Service方法里手动开了连接却漏了关闭。
3. SQL执行模式：批量、分页与N+1的三座大山
3.1 N+1查询，ORM框架的甜蜜陷阱
只要用了ORM框架，几乎都会踩N+1查询的坑。表现是业务逻辑看着清清楚楚：先查了一条主记录，再在循环里逐条查详情。假如主记录有1000条，数据库就要吃掉1001条查询，传输和解析成本天然放大了1000倍。
举一个典型的例子。用MyBatis-Plus查用户列表，再循环查每个用户的订单数：
// 错误示范：循环里发查询 List<User> users = userMapper.selectList(null); for (User user : users) { OrderCount count = orderMapper.countByUserId(user.getId()); // 业务处理... }这1000个用户就会产生1000条count查询。在并发量不高的时候看不出来，一上量数据库就撑不住了。
正确的做法分三种，看场景选择：
- 尽量用SQL层解决：一次性join查出结果，例如
SELECT u.id, COUNT(o.id) FROM user u LEFT JOIN orders o ON ... GROUP BY u.id，直接把聚合工作交给数据库。 - 用ORM的批量查询能力：先查出用户列表，拿到所有ID，再用
WHERE user_id IN (...)一次性查出订单数据，在内存里聚合拼接。 - 配置批量加载BatchSize：在MyBatis里设置
default-batch-size，让框架在检测到循环查询时自动合并成批量查询。
判断项目里有没有N+1，最土但最有效的方法是在开发环境打印SQL日志，跑一个列表页，数一下打了多少条SQL。正常情况下一个列表页最多三五条SQL，如果和列表行数成正比，那多半中招了。
3.2 深分页，越翻越慢的元凶
深分页是另一个高频问题。经典的LIMIT offset, size写法，在offset很小的时候没事，一旦offset到了几十万，数据库依然要把前面所有行都扫一遍再丢弃，耗时自然线性上涨。
我现在遇到深分页，一般优先用两种方案。
一种是延迟关联。先只查主键，再做join回表取完整数据：
-- 优化前：深翻页扫描大量行 SELECT * FROM orders ORDER BY id DESC LIMIT 500000, 20; -- 优化后：先查主键，再回表 SELECT * FROM orders INNER JOIN ( SELECT id FROM orders ORDER BY id DESC LIMIT 500000, 20 ) AS tmp ON orders.id = tmp.id;原理很简单：内层查询只扫描主键索引，数据量小，回表次数只有20次，性能提升非常明显。
另一种是键集分页，也就是基于唯一键游标翻页。适合App列表这类实时性强的场景：
-- 基于上次请求返回的最大id继续往下翻 SELECT * FROM orders WHERE id > 1000000 ORDER BY id ASC LIMIT 20;这种方案不管翻多深，耗时都稳定。唯一的坑是排序字段要稳定，避免用order by create_time这种可能有重复值的字段做游标，否则会出现漏数据。
3.3 批量写入，数据量越大收益越明显
程序循环单条写入，能耗大，事务开销重复，网络往返多。我见过一个导入Excel的接口，1万条数据循环insert耗时好几分钟，改成批量插入后不到10秒就完成了。
MySQL批量写入要特别注意几个点：
- JDBC连接串上要加
rewriteBatchedStatements=true，否则addBatch()发到数据库依然是逐条执行。这是我踩过最深的坑，加与不加性能差一个数量级。
// JDBC URL示例 jdbc:mysql://localhost:3306/demo?rewriteBatchedStatements=true批量大小要控制。一次batch塞太多，SQL包体积会超过
max_allowed_packet限制，反而不稳定。按我的经验，单批500~1000条比较稳，可以按数据行大小灵活调整。批量更新用
CASE WHEN拼装，把多条更新合并成一条SQL：
UPDATE products SET stock = CASE id WHEN 1001 THEN 10 WHEN 1002 THEN 5 WHEN 1003 THEN 8 END WHERE id IN (1001, 1002, 1003);实测下来1万条更新，这种方式比循环单条快20倍以上。注意CASE WHEN方式拼出来的SQL包体不要太大，超过几十KB就拆批。
3.4 只有必要字段，不贪心
“SELECT *”看着省事，代价是多余字段的网络传输、ORM映射和临时内存消耗。尤其在宽表场景，例如一个表三四十个字段，但接口只用其中五六个，SELECT *的话浪费就非常明显。
有人会觉得这是小问题，其实在高QPS场景下，每条查询多传1KB，每秒1000次查询就多出1MB流量。同时，ORM框架把一整行数据映射成对象，也比只映射几个字段消耗更多CPU。问题积累到量级上就不再是小事。
正确姿势是始终显式列出所需字段，只在极少数连字段都不确定的动态场景才用SELECT *，而且这类接口一定要做好限流和缓存。
4. 事务设计与锁竞争：并发性能的分水岭
4.1 短事务是真理，长事务要拆分
事务的边界直接决定了锁的持有时间。程序里常见的不合理事务包括：在事务里调用第三方HTTP接口、在事务里做文件读写、在事务里循环几十次查询。锁被这些操作白白占用，其他事务只能排队。
我总结了一个短事务的“白名单”标准：一个事务里只包含必须要保证原子性的写操作和最少量的读操作。
举一个线上例子，有个下单接口，事务里做了：查库存、扣库存、调营销接口发券、写订单表。营销接口响应偶尔达到3秒，整个事务就僵在那里，库存这行记录的锁被持有3秒，大量并发下单全部堆积。后来把发券从事务里拆出去，改成下单成功后异步发券，用消息队列削峰，接口的P99从800ms直接降到120ms。
4.2 锁竞争和死锁，怎么定位怎么解
并发操作同一张表、同一行记录，非常容易引发锁竞争。InnoDB默认行锁，但注意：更新条件用不到索引时，行锁会升级成表锁，代价巨大。
排查锁问题，我的常规动作是先看数据库状态：
SHOW ENGINE INNODB STATUS\G重点关注输出里的LATEST DETECTED DEADLOCK段落，里面会明确列出死锁涉及的两条SQL和持有锁的key。实际项目中我遇到过最典型的死锁场景是两个事务交叉更新两行数据：
事务A：UPDATE account SET balance=balance-100 WHERE id=1;然后UPDATE account SET balance=balance+100 WHERE id=2;事务B：UPDATE account SET balance=balance-50 WHERE id=2;然后UPDATE account SET balance=balance+50 WHERE id=1;
两条更新路径相反，死锁就发生了。解决方案是约定全局统一更新顺序，例如所有操作都按主键升序排列再更新，本质上把交叉路径变成单行路径。
锁等待超时参数也别忽视。数据库默认innodb_lock_wait_timeout是50秒，一个请求等锁超过这个时间才会报错。实际业务里让用户等50秒毫无意义，建议调到3~5秒，让快速失败的请求尽早暴露问题，也让系统避免被锁等待拖死。
4.3 先写数据库还是先发消息
标题里有个热搜词“先写数据库 先写MQ”，这在程序操作优化里确实是高频决策点。
先来结论：任何业务操作，数据库持久化永远是主数据源，消息是下游通知和异步消费的来源。主流方案是“先写数据库，成功后再发消息”。原因很简单，消息队列是最终一致的不保证环境，一旦先发消息，而数据库写入失败，下游已经消费了消息去执行操作，数据就错乱了。
但“先写数据库再发MQ”也有坑：数据库写成功了，消息发送失败怎么办？下游没收到通知，状态更新就会丢失。这时需要引入补偿机制。实操里我建议用本地消息表或事务消息。
本地消息表的做法是：在业务数据库里建一张message_outbox表，和业务数据在同一个本地事务里写入，然后由后台任务把消息扫描发送到MQ，发送成功后再标记为已发送。这样消息不会丢，也不会引入分布式事务，实现简单可控。
RocketMQ自带事务消息，原理类似：发送half消息 → 执行本地事务 → 提交确认或回滚。如果你的团队还没用消息队列中间件，本地消息表是最轻量可靠的选择。
5. 数据访问策略：让数据库少干活
5.1 索引生效的那些细节，程序侧必须懂
程序操作优化里有一个很反直觉的现象：表结构里明明建了索引，程序一跑却发现索引压根没生效。问题基本出在SQL写法上。
最容易踩的坑我列几个：
在索引列上做函数运算。
-- 索引列上加函数，索引失效 SELECT * FROM orders WHERE YEAR(create_time) = 2025; -- 改成范围查询，索引正常走 SELECT * FROM orders WHERE create_time >= '2025-01-01' AND create_time < '2026-01-01';隐式类型转换。字段是varchar类型，参数传了数字，或者反过来，MySQL会做隐式转换，导致索引列上隐式加了一次CAST，索引就失效了。
-- user_code是varchar类型，传数字会导致索引失效 SELECT * FROM users WHERE user_code = 10086;模糊查询的写法。
LIKE '%abc'和LIKE '%abc%'无法走索引，LIKE 'abc%'可以走前缀索引。真需要全文模糊搜索，应该用全文索引或搜引擎，而不是在数据库里硬扫。
程序侧写SQL时养成一个习惯：写完看一眼执行计划，EXPLAIN输出里type字段不是ALL才安心。开发阶段多花30秒，线上能少熬夜。
5.2 缓存不是银弹，但用对位置就是加速器
程序操作优化的另一大块是缓存策略。数据库查询再快，一层网络和一次索引扫描也比不上内存命中。好的缓存设计能让数据库QPS降到原来的十分之一甚至更低。
缓存的位置要有优先级。本地缓存 > 分布式缓存 > 数据库。读多写少的配置数据、枚举数据，直接本地缓存（Caffeine/Guava Cache）解决；跨服务共享的热点数据用Redis；冷门数据或一致性要求极高的数据才落库查询。
缓存层要重点防三个问题：
- 穿透：查询不存在的数据，每次都打到数据库。解决：缓存空值，或者在缓存前用布隆过滤器拦截不存在的key。
- 击穿：热点key在过期瞬间涌入大量请求，全部打到数据库。解决：互斥锁只让一个请求回源，其余线程等待后读缓存。
- 雪崩：大量key同时过期，数据库一瞬间被压垮。解决：过期时间加随机数，让过期请求均匀分布。
缓存和数据库的一致性，我踩过几次坑后形成了固定套路：先更新数据库，再删缓存，而不是先更新缓存。因为更新缓存容易产生并发不一致，删除缓存后下次请求会重新加载。极端场景下再配合延迟双删兜底，绝大多数业务都够用了。
5.3 读写分离与冷热数据，要从程序规划开始
数据库服务端可以做主从复制，但程序侧如果不配合，读写分离就是空谈。程序操作优化的一个实践是：明确哪类查询走只读从库，哪类必须走主库。
强一致场景，比如支付结果查询、对账、订单状态确认，必须走主库；报表类、列表类、搜索类的弱一致场景，路由到从库。这里要注意一个小坑：主从延迟。不要在主库写入后立即从从库读刚写入的数据，那会读到旧数据，引发认知冲突。解决方式是在写入后短时间内强制走主库，或者等一个安全的延迟窗口。
冷热数据的处理也属于程序侧决策。历史订单、日志流水这类数据越来越大会拖累查询和备份性能，程序上可以在写入时就按时间维度分流，例如orders_2025这种按月分表，或者定期把两年前的数据归档到冷存储。设计上多走一步，后面维护轻松十年。
6. 常见问题与排查技巧实录
6.1 四个高频故障的排查思路
实际工作中，程序操作优化相关的问题有很强的共性，我把高频故障和排查路径整理成一张速查表：
| 现象 | 大概率原因 | 排查入手点 |
|---|---|---|
| 数据库CPU高但SQL简单 | 连接数过多、循环查询、无缓存 | 查看processlist连接数和Sleep会话，检查代码循环和缓存策略 |
| 接口偶发超时，数据库连接获取失败 | 连接池耗尽或连接泄漏 | 连接池监控看active连接数，打开leak-detection-threshold |
| 并发更新同一行数据互相阻塞 | 长事务持锁时间过长 | SHOW ENGINE INNODB STATUS看锁等待，优化事务边界 |
| 批量导入耗时极长 | 循环单条插入、未开启批量重写 | 确认rewriteBatchedStatements=true，改批量提交，检察max_allowed_packet |
6.2 一套通用的压测排查流程
如果线上已经出了性能问题，我一般不会直接在数据库上瞎调参数，而是按下面这套流程走：
第一步，先看监控。应用监控里的接口耗时、数据库监控里的连接数、活跃会话数、慢查询数量，先确定问题发生的层级。
第二步，开慢查询日志。MySQL侧把long_query_time临时调到1秒，收集一两小时，用pt-query-digest分析慢SQL的聚合分布，看哪些SQL是高频慢查。
第三步，抓现场。在问题发生时执行SHOW PROCESSLIST，观察会话状态。State为Locked说明在等锁；大量Sleep说明连接未释放；大量Sending data说明磁盘扫描严重。
第四步，回到程序代码里定位交互模式。检查循环、连接池参数、事务边界、批量操作。这四步走下来，80%的问题都能定到根因。
6.3 团队如何从制度上根治操作优化问题
个人能力再强，不如团队规范防患于未然。我把自己团队里运转见效的几个规矩分享出来。
Code Review阶段引入性能红线清单。必查项包括：循环内是否发了SQL、事务里是否有远程调用、连接是否一定会在finally关闭、分页是否用了延迟关联或键集分页。
压测环境接入基础链路监控。每个迭代上线前跑一轮准生产环境压测，把数据库指标和接口P99指标对比，上升超过30%就不允许上线。
线上问题复盘后，把根因整理进“程序操作反模式”文档，新人入职第一周先读这份文档。这里面记录的坑，都是真金白银的线上事故换来的，比任何教程都有效。
我自己实际带项目的感受是，程序操作优化的价值经常被低估，因为它的收益不像加索引那样能立刻看到执行计划的变化，而是体现在系统整体吞吐和稳定性的长线优势上。平时多花半小时审视代码里的数据库交互方式，省下来的是未来无数个本可以避免的加班夜。最后再分享一个坚持了很久的小习惯：每写完一个涉及数据库的接口，都顺手看一眼SQL日志和连接池状态，确认无异常再提交代码。这个动作简单，但积累下来的数据会告诉你程序的每一步操作到底让数据库付出了多少代价。
