数据库性能优化做到第三篇，聊点真正让DB同学“血压升高”的东西——程序操作优化。前两篇如果讲的是硬件选型、参数调优这些服务器侧的活儿，那这篇就完全是“人和代码”的战争了。
我见过太多业务系统，硬件配置拉满、MySQL参数抄了一堆大厂模板，结果一压测CPU照样飙到99%。原因很简单：SQL写得烂、请求发得太猛、事务开得太长、连接池配置拍脑袋——这些全是在程序侧埋雷。数据库再强，也架不住应用层不懂节制地薅。
这篇把经验拆成几个实操块：查询优化、写入优化、连接池与会话管理、事务与锁优化，外加一个排查速查表。内容不追求面面俱到，只讲我实测过、踩过坑、能直接落地的招。
1. 先从三层模型看优化方向
程序操作优化，第一件事不是改代码，而是想清楚优化的范围到底在哪。
1.1 三层优化模型
我自己习惯把程序操作优化拆成三个层面，逐个排查：
第一层是SQL与数据访问层。包括SQL写法、索引使用、表结构设计对查询的影响、批量操作的拆分方式等。这一层最常见的问题是索引失效、深分页、隐式转换这种“慢性病”，优化收益最立竿见影。
第二层是连接与会话层。包括连接池大小、获取连接超时、空闲回收、并发请求的排队策略等。很多系统不是数据库撑不住，是连接池被打满了，请求全在池子外面排队。
第三层是应用架构与事务层。包括事务边界、锁粒度、隔离级别、分布式场景下的重试与幂等等。这一层问题最隐蔽，通常表现为偶发的锁等待超时、死锁，线上还不容易复现。
很多朋友一来就调数据库参数，max_connections从200调到2000，以为能解决一切。实际上连接数调高之后，线程切换成本和锁竞争反而更严重。我建议先按三层模型自查，把确定性问题处理完再谈参数调优。
1.2 优化前先建基线，不然改了也白改
没有基线就没有发言权。我接手一个系统的时候，第一件事是开慢查询日志，统计高峰期的TPS、QPS、平均延迟和锁等待次数，至少要观察一周。没有这一步，你优化完了都不知道自己有没有变快。
常用的基线指标就四个：QPS、TPS、平均响应时间、慢查询数量。慢查询的阈值建议先定在1秒，跑一段时间再收窄到500毫秒甚至200毫秒。有些系统平时没有慢查询，一到大促就出问题，说明SQL对数据量很敏感，这种尤其需要关注执行计划的变化。
有一个经验可以分享：不要只盯MySQL自带的慢日志。如果有条件，用Percona Toolkit里的pt-query-digest做聚合分析，把慢SQL按“总耗时”和“平均耗时”两个维度排序，能找到真正的“大头”。有的SQL虽然平均只有几十毫秒，但一分钟跑几千次，积少成多一样能把数据库打死。
2. 查询优化：先搞清楚SQL到底怎么跑的
在我接触过的系统里，90%的性能问题都能在SQL和索引层面找到答案。不要急着上缓存，先看执行计划。
2.1 索引失效的坑，你大概率踩过
索引失效的典型场景就那么几种，几乎每个开发都踩过：
在索引列上做函数操作，比如WHERE DATE(create_time) = '2025-01-01'。这种写法会导致索引失效，全表扫描。正确做法是WHERE create_time >= '2025-01-01 00:00:00' AND create_time < '2025-01-02 00:00:00'。
隐式类型转换也是重灾区。比如user_id字段是varchar类型，但你的SQL写成WHERE user_id = 123456，MySQL会先把字段转成数字再比较，索引照样失效。这种问题在排查时特别隐蔽，执行计划里明明显示了possible_keys，但实际一个都没用上。
前模糊匹配LIKE '%keyword%'无法走索引，属于常识，但很多人不知道怎么优化。说实话，前模糊匹配本身就不适合关系型数据库硬扛，要么换搜索引擎，要么配合全文索引。如果数据量小，直接扫就扫了；数据量大，别硬撑。
还有一个容易忽略的：联合索引的最左前缀原则。索引是(a, b, c)，但查询条件是WHERE b = 1 AND c = 2，这个索引就用不上。我看到不少开发随手建索引，哪儿红了加哪儿，结果索引建了一大堆，实际利用率和区分度都很差，写入反而变慢了。
2.2 深分页的优化思路要记住
分页查询是必考题目。早期数据量小，LIMIT 100000, 20没什么感觉；数据量到百万级之后，这种写法会越来越慢，因为MySQL要扫描前面100000行才能拿到第20行。
优化的思路就两个：要么让排序尽量走索引，要么先定位主键再回表。举个例子，一条常规的深分页SQL可以这样优化：
-- 优化前，MySQL要扫描并丢弃前100000行 SELECT * FROM orders ORDER BY id DESC LIMIT 100000, 20; -- 优化后，先取主键范围再join原表 SELECT o.* FROM orders o INNER JOIN ( SELECT id FROM orders ORDER BY id DESC LIMIT 100000, 20 ) t ON o.id = t.id;子查询里只取了主键id，走覆盖索引扫描，速度非常快。再用主键去join原表拿完整数据，回表20行就够了。这条SQL在千万级数据量下，性能差距是数量级的。
除此之外还有基于游标的分页：把LIMIT offset换成的WHERE id > last_max_id。比如上一页的最后一个id是999990，下一页就写WHERE id > 999990 ORDER BY id ASC LIMIT 20。这种方案最适合“下拉加载更多”的场景，因为下一页永远只取20条，不会越翻越慢。
2.3 聊聊SELECT *和回表那些事
SELECT *的问题不在于“取出来的字段多”，而在于它容易打断覆盖索引。覆盖索引的意思是查询需要的所有字段都在索引里，可以直接从索引返回，不需要回表。一旦SELECT *，很可能索引里没包含所有字段，MySQL只能老老实实回表，IO就上来了。
如果你的业务只需要id、name、status三个字段，联合索引也刚好覆盖这三个字段，那查询性能会非常好看。所以我会习惯性审查业务SQL，把不需要的字段去掉。这里有一个细节：不要为了覆盖而把太多字段塞进索引，索引太宽会影响写入性能和占用空间，属于捡了芝麻丢西瓜。
还有一个关于OR的条件，WHERE a = 1 OR b = 2这种写法即使a和b都有单列索引，优化器也未必会用，很可能合并索引或者干脆全表扫。能改写成UNION ALL就改写，不能改写就看看是否值得改造成两个查询再合并。实际经验是，OR条件在MySQL里优化很不稳定，性能压力大的时候要么改SQL要么改表结构。
关于查询优化最后补一句：所有优化必须以执行计划为准。不要凭感觉说“我这条SQL肯定走索引了”，请用EXPLAIN看type、rows、Extra三列，type至少要达到ref级别，rows要跟实际返回行数接近，Extra不要出现Using filesort和Using temporary。出现这两个，基本可以断定排序或去重没有用好索引。
3. 写入优化：从单条INSERT到批量操作的演变
查询优化聊得差不多了，写入优化的痛点同样多。很多系统读多写少，但写入侧的坑一旦踩中，比查询更致命。
3.1 批量插入的尺寸应该怎么定
新手最容易犯的错就是循环单条INSERT。一万条数据for循环插一万次，每次都要走一次网络往返、一次SQL解析、一次事务提交。不慢才怪。
优化方案很简单，多行VALUES一条SQL搞定：
INSERT INTO orders (id, user_id, amount, status) VALUES (1, 101, 99.00, 1), (2, 102, 129.00, 1), (3, 103, 59.00, 2);但是“批量”不是越大越好。我见过有人一次批量插5万条，直接把max_allowed_packet打爆，或者把binlog写得巨大，主从同步延迟到天上去。
批量插入的size怎么定？没有统一答案，取决于单行数据大小、网络带宽、MySQL的max_allowed_packet限制。我常用的参考公式是：批次大小 = 1MB / 单行平均大小，再取个安全系数。假设单行1KB，1MB大概能放1000行，保险起见一批500行。如果单行只有100字节，一批5000到10000行也没啥问题。
注意不同数据库驱动对批量操作的支持不一样。JDBC可以通过rewriteBatchedStatements=true让executeBatch()真正走多行插入，否则驱动还会一条条发给MySQL，批了个寂寞。我在早期踩过这个坑，当时看着代码明明用了batch，结果数据库端收到的还是一条条单插，性能提升几乎没有。这个参数务必要确认。
Python这边可以用pymysql的executemany()，配合cursor.executemany(sql, args_list)来实现类似效果。但要注意，executemany本质上还是逐条发送，需要数据库驱动支持多行合并才会真正变成一条多值INSERT。
3.2 大批量更新与删除的正确姿势
批量UPDATE和DELETE更危险。直接UPDATE big_table SET status = 1 WHERE status = 0，看起来挺干净，实际上可能锁住几百万行，导致主从延迟巨大，甚至把从库拖垮。MySQL 5.6以后的binlog格式默认是ROW，一个大事务提交后，从库要逐行应用binlog，那酸爽谁用谁知道。
我的做法是分批次小步提交，比如每次只更新1000条：
UPDATE big_table SET status = 1 WHERE status = 0 ORDER BY id LIMIT 1000;写个循环，一次执行1000条并且提交，直到影响行数为0。这种操作方式的好处是单次锁范围小、不会阻塞其他请求太久、主从延迟也可控。缺点是需要写循环逻辑，不能一条SQL走天下。但线上稳定比省事重要。
DELETE同理。特别是清理历史数据的场景，千万千万不要一把梭。按id范围分批删除，每批加个小sleep，给主从同步一点缓冲时间。我在清理几亿条流水时，就是这么一批一批删的，删几个小时，线上业务毫无感知。
3.3 事务日志与写放大
批量写入还有个底层问题容易被忽略：redo log和binlog。每次写入，不仅有数据页的修改，还有redo log的记录以及binlog的生成与同步。大量高频小事务，日志刷盘频率非常高，磁盘IO压力巨大。
MySQL的“组提交”（group commit）机制能把多个事务的binlog刷盘合并成一次，但这需要并发事务达到一定量才有效。单线程串行提交，组提交帮不上忙。所以，提高写入并发度有时候比单纯减小日志量更有效。
如果你发现数据库频繁切换“日志等待”状态，或者磁盘IO不高但commit耗时很高，可以检查innodb_flush_log_at_trx_commit参数。设为1是最安全的，每次提交都刷盘；设为2只有操作系统崩溃才丢数据，性能会好一些。这个取舍取决于业务对数据丢失的容忍度，金融类就别想了，老老实实用1。我在一些报表系统上会用2，实测写入性能能提升三四倍，丢了那几秒钟数据根本没人发现。
4. 连接池与会话管理：别让数据库栽在聊天上
连接池是程序操作优化里最容易忽略、影响面却最大的一环。我排障这么多年，发现很多系统的性能瓶颈根本不在数据库，而是连接池配置不合理。
4.1 连接池的核心参数要理解
先明确一个概念：数据库连接是“重型资源”，每条连接都要占用内存、线程、甚至一份排序和临时表空间。连接数不是越多越好，太多反而会引发上下文切换和锁竞争。
以Java的HikariCP为例，我经常用下面这套配置作为起点：
| 参数 | 值 | 说明 |
|---|---|---|
| maximumPoolSize | 20 | 最大连接数，别拍脑袋设200 |
| minimumIdle | 5 | 最小空闲连接，低于这个会补充 |
| connectionTimeout | 3000 | 获取连接超时（毫秒），超时直接报错 |
| idleTimeout | 600000 | 空闲连接回收时间（毫秒） |
| maxLifetime | 1800000 | 连接最大存活时间，略小于数据库wait_timeout |
最大连接数是多少合适？有个粗糙的参考公式：核心数 × 2 + 磁盘数。一个8核32G的数据库，连接池给20到30个连接通常就够用了。如果20个连接都不够，首先要怀疑的不是连接池太小，而是SQL太慢，每个请求占用连接的时间太长。
连接池大小和线程池大小的关系也要匹配。线程池50个线程，连接池却只有10个，那40个线程全部阻塞在等待连接上，纯浪费。我自己倾向于把线程池大小和连接池大小调成一致，最多留一点余量。
4.2 连接池耗尽怎么排查
连接池耗尽的典型现象：系统响应变慢、日志里频繁出现Connection is not available, request timed out或者wait millis 3000。
排查思路分两步。第一步，看是不是有慢SQL持有连接不释放。连到MySQL执行SHOW PROCESSLIST，查看长时间处于Sleep或Query状态的连接，有些连接被借出去之后业务忘了归还，或者被异常分支卡住没释放，这些就是罪魁祸首。
第二步，看连接池监控。HikariCP支持Metrics指标，观察active、idle、pending三个数。如果pending常在零点以上，说明获取连接的请求在排队，要嘛加连接池，要嘛优化SQL。如果active一直打满，而pending也在涨，问题大概率在业务代码而不是连接池本身。
还有一个低级但常见的坑：业务代码里把连接写在try外面，或者忘记在finally里close。一次两次看不出什么，连接池被借空是时间问题。我在代码审查时特别注意这点，连接必须在finally或者try-with-resources里关闭，没有任何例外。
4.3 并发控制：乐观锁与悲观锁的取舍
程序操作优化还有一个命题，叫“怎么控制并发访问”。很多业务喜欢用悲观锁：SELECT ... FOR UPDATE锁一行，改完再提交。这种思路不是不行，而是容易把并发度做死。
比如库存扣减，每个用户进来先锁库存行，再计算再更新，整个事务变长，锁等待概率直线上升。如果一个商品原本有10个人同时抢，悲观锁只能让他们排队购买，吞吐量非常难看。
换成乐观锁，用版本号或者CAS的思路：
UPDATE inventory SET stock = stock - 1, version = version + 1 WHERE id = 123 AND version = 5;如果UPDATE影响行数为1，说明没有并发冲突，直接成功。影响行数为0，说明版本号被其他人改了，业务层重新查数据重试即可。这种方式的并发度比悲观锁高得多，代价是需要处理重试逻辑。
这里有个实战细节：重试一定要控制次数和退避时间，不能无限重试疯狂打数据库。我用得比较多的是最多重试3次，指数退避，第一次延迟100毫秒、第二次200毫秒、第三次400毫秒。超过次数就放弃，给用户提示稍后再试。
乐观锁适合读多写少、冲突不频繁的场景。像抢购这种高冲突场景，乐观锁的重试会反复失败，不如改造成队列或Redis原子操作。在数据库层面硬扛高并发写，本身就是吃力不讨好的事情。
5. 事务、锁与并发冲突：死锁不是玄学
死锁这个问题，本质上是锁的获取顺序不一致导致的。很多人觉得死锁是运气问题，其实它是逻辑问题，完全可以规避。
5.1 事务圈越小越好
我始终坚持一个原则：事务里只放必要操作。不要把远程调用、外部HTTP请求、消息发送等放进事务里。一个事务占用数据库锁的时间越短，别人等待的时间就越短，死锁概率也越低。
举个例子，下单操作里的扣库存、生成订单、更新用户积分，这三个放一个事务没问题。但你要在事务里调支付接口、发短信验证码，那就是给自己找麻烦。网络延迟不可控，一个接口卡3秒，事务就持有锁3秒，并发一高，锁等待超时铺天盖地。
正确做法：事务内只做数据库操作，远程调用放事务外，失败后用补偿机制处理。如果担心分布式事务，就引入消息队列配合对账，而不是靠数据库长事务硬撑。
5.2 间隙锁与next-key锁，是低频坑但是大坑
InnoDB默认隔离级别是可重复读（RR），这个级别下，命中范围查询的UPDATE和DELETE会加间隙锁或next-key锁。间隙锁是行锁和行锁之间的空隙组成的锁，它可以阻止其他事务向这个间隙插入数据。
间隙锁最经典的坑是：事务A按某个条件更新一批数据，事务B想往这个条件范围内插入数据，直接被阻塞。而这锁可能锁的范围比想象的大得多，因为范围条件没有命中索引时，会锁住全表的所有间隙。大量插入操作排队等待，系统瞬间瘫痪。
解决方案有几个：一是把隔离级别降到读已提交（RC），InnoDB在RC下会放弃间隙锁，只保留行锁。RC配合binlog_row_image=MINIMAL，在大多数业务场景下足够安全且并发更好。二是确保UPDATE和DELETE的条件能走索引，走不上索引就意味着全表扫描加全表间隙锁，这是致命打击。
我的默认推荐是RC。除非有特定需求需要RR的幻读保护，否则RC是性能和一致性之间比较均衡的选项。很多厂商的默认配置就是RC，不是没有原因的。
5.3 一个典型的死锁复盘
说一个我亲身处理的死锁案例。订单服务和库存服务，各自一个事务，都先更新订单表再更新库存表，但两者的顺序恰好相反。
事务A：UPDATE orders ...; UPDATE inventory ...; 事务B：UPDATE inventory ...; UPDATE orders ...;
A锁了订单表的某行，去等库存表；B锁了库存表的某行，去等订单表。两边互不相让，数据库死锁检测器介入，回滚其中一个。
这类死锁解决起来很简单：所有事务统一按同一个顺序操作资源。比如约定先锁库存再锁订单，事务A和B都按这个顺序执行，死锁自然消失。
如果不想改代码顺序，另一个办法是减小锁的粒度。库存表按商品ID分片后，不同商品之间的锁没有竞争，也能降低死锁概率。
排查死锁，MySQL提供了现成工具：SHOW ENGINE INNODB STATUS，里面会打印出最近一次死锁的现场，包括持有锁的会话、等待锁的SQL、锁的type。排障时先看这个，定位到具体事务和SQL，再分析加锁顺序是否一致。不要瞎猜。
6. 常见问题排查速查表：先定位再动手
最后分享一个排障速查表，我在处理线上问题的时候会按这张表过一遍，效率非常高。
| 现象 | 排查方法 | 解决方向 |
|---|---|---|
| 接口偶尔超时，CPU不高 | 看连接池监控，是否active打满 | 调大连接池或检查SQL慢查询 |
| QPS没涨但CPU飙升 | 慢查询日志定位慢SQL，EXPLAIN分析 | 索引优化、SQL改写、减少全表扫描 |
锁等待超时Lock wait timeout | SHOW ENGINE INNODB STATUS看锁等待现场 | 缩短事务长度、收敛锁范围、降隔离级别 |
| 主从延迟飙升 | 看从库SQL线程是否有大事务 | 分批提交、抑制大事务、避免一条SQL改几百万行 |
连接数打满Too many connections | 查processlist，是否大量Sleep连接 | 代码修复连接泄漏、调小连接池上限、kill空闲连接 |
| 死锁 | 查看死锁日志，对比两个事务的加锁顺序 | 统一加锁顺序、缩小锁粒度、降隔离级别 |
排障流程上，我的习惯是“先看日志，再看监控，最后才改配置”。日志能告诉你是什么操作引发的，监控能告诉你影响面有多大。改配置永远放在最后，因为配置改动影响全局，轻易动不得。
慢查询日志的具体开启方式也顺手留一个：
SET GLOBAL slow_query_log = ON; SET GLOBAL long_query_time = 1; SET GLOBAL log_queries_not_using_indexes = ON;注意，log_queries_not_using_indexes会记录所有不走索引的SQL，在开发环境开一下没问题，线上开的话日志量可能爆炸，建议只在排查期临时开启，用完就关。
再补充一个工具组合。pt-query-digest做慢SQL聚合，sys.schema_unused_indexes查无用索引，performance_schema看IO和锁等待。这三个工具能覆盖90%的排查场景。工具不贪多，用好一个是一个。
还有一个小技巧，排查SQL性能时，可以用EXPLAIN ANALYZE（MySQL 8.0+）拿到每个步骤的实际执行时间，比单纯EXPLAIN估算准确得多。看执行计划时重点看最耗时的那一步，很多时候问题就藏在那个Using filesort或者Full table scan上。
程序操作优化这块，我个人最大的体会是：优化的核心不是会背多少技巧，而是建立一套“观察执行计划-定位瓶颈-小步验证-回归对比”的习惯。慢SQL优化完，务必对比优化前后的执行计划和实际耗时，数据摆出来才算数。那些凭感觉说“优化了”的做法，在线上往往是给自己挖坑。
把程序操作层面的坑填平之后，再去碰数据库参数调优，你会发现很多参数问题其实都是SQL和事务层面的问题伪装出来的。这也是我坚持“先程序后参数”的原因。希望这篇对你有用，至少能帮你少踩几个我当年踩过的坑。
