数据库性能优化做到第三篇聊点真正让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 123456MySQL会先把字段转成数字再比较索引照样失效。这种问题在排查时特别隐蔽执行计划里明明显示了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 / 单行平均大小再取个安全系数。假设单行1KB1MB大概能放1000行保险起见一批500行。如果单行只有100字节一批5000到10000行也没啥问题。注意不同数据库驱动对批量操作的支持不一样。JDBC可以通过rewriteBatchedStatementstrue让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为例我经常用下面这套配置作为起点参数值说明maximumPoolSize20最大连接数别拍脑袋设200minimumIdle5最小空闲连接低于这个会补充connectionTimeout3000获取连接超时毫秒超时直接报错idleTimeout600000空闲连接回收时间毫秒maxLifetime1800000连接最大存活时间略小于数据库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想往这个条件范围内插入数据直接被阻塞。而这锁可能锁的范围比想象的大得多因为范围条件没有命中索引时会锁住全表的所有间隙。大量插入操作排队等待系统瞬间瘫痪。解决方案有几个一是把隔离级别降到读已提交RCInnoDB在RC下会放弃间隙锁只保留行锁。RC配合binlog_row_imageMINIMAL在大多数业务场景下足够安全且并发更好。二是确保UPDATE和DELETE的条件能走索引走不上索引就意味着全表扫描加全表间隙锁这是致命打击。我的默认推荐是RC。除非有特定需求需要RR的幻读保护否则RC是性能和一致性之间比较均衡的选项。很多厂商的默认配置就是RC不是没有原因的。5.3 一个典型的死锁复盘说一个我亲身处理的死锁案例。订单服务和库存服务各自一个事务都先更新订单表再更新库存表但两者的顺序恰好相反。事务AUPDATE orders ...; UPDATE inventory ...; 事务BUPDATE inventory ...; UPDATE orders ...;A锁了订单表的某行去等库存表B锁了库存表的某行去等订单表。两边互不相让数据库死锁检测器介入回滚其中一个。这类死锁解决起来很简单所有事务统一按同一个顺序操作资源。比如约定先锁库存再锁订单事务A和B都按这个顺序执行死锁自然消失。如果不想改代码顺序另一个办法是减小锁的粒度。库存表按商品ID分片后不同商品之间的锁没有竞争也能降低死锁概率。排查死锁MySQL提供了现成工具SHOW ENGINE INNODB STATUS里面会打印出最近一次死锁的现场包括持有锁的会话、等待锁的SQL、锁的type。排障时先看这个定位到具体事务和SQL再分析加锁顺序是否一致。不要瞎猜。6. 常见问题排查速查表先定位再动手最后分享一个排障速查表我在处理线上问题的时候会按这张表过一遍效率非常高。现象排查方法解决方向接口偶尔超时CPU不高看连接池监控是否active打满调大连接池或检查SQL慢查询QPS没涨但CPU飙升慢查询日志定位慢SQLEXPLAIN分析索引优化、SQL改写、减少全表扫描锁等待超时Lock wait timeoutSHOW 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 ANALYZEMySQL 8.0拿到每个步骤的实际执行时间比单纯EXPLAIN估算准确得多。看执行计划时重点看最耗时的那一步很多时候问题就藏在那个Using filesort或者Full table scan上。程序操作优化这块我个人最大的体会是优化的核心不是会背多少技巧而是建立一套“观察执行计划-定位瓶颈-小步验证-回归对比”的习惯。慢SQL优化完务必对比优化前后的执行计划和实际耗时数据摆出来才算数。那些凭感觉说“优化了”的做法在线上往往是给自己挖坑。把程序操作层面的坑填平之后再去碰数据库参数调优你会发现很多参数问题其实都是SQL和事务层面的问题伪装出来的。这也是我坚持“先程序后参数”的原因。希望这篇对你有用至少能帮你少踩几个我当年踩过的坑。