那段时间我印象很深线上一个查询类数据服务的接口 P99 从 50 毫秒直接飙到 2 秒多流量并没有明显变化数据库 CPU 却一直在 90% 以上打转。排障的时候发现大家都在争论到底是 SQL 写得有问题还是缓存策略早就失效了。后来把问题拆开才意识到数据服务性能调优这件事从来不是要么优化 SQL要么优化缓存的二选一而是一条从 SQL 到缓存的全链路诊断过程。这篇文章就围绕这条链路展开先讲怎么定位瓶颈再讲慢 SQL 的执行计划怎么读接着是缓存层那些比 SQL 更隐蔽的坑最后聊怎么把调优动作沉淀进日常迭代里。主要面向做后端开发、维护数据服务的同学也适合准备面试时想拿实战案例说话的人。1. 先分清故障层SQL慢还是缓存拖累了服务1.1 用三层拆解法定位真正的瓶颈很多人一接到性能告警第一反应是打开慢 SQL 日志或者直接看代码。这其实顺序反了。正确的第一步是先搞清楚请求的时间到底消耗在哪一层。我习惯把一条数据服务的请求链路拆成三层网络传输层、应用逻辑层、数据存储层。数据存储层又可以继续拆成数据库和缓存两个支线。定位时用RT 分层来观察而不是凭感觉猜。具体做法是先看整体监控里的 QPS 和 TP99再把同一个接口的耗时按网关注入、应用内方法调用、数据库查询、缓存读写做拆分。如果你没有接入链路追踪系统最朴素的办法是在应用日志里打时间戳或者在数据库慢日志里看查询耗时再对比缓存中间件的响应时间。举个我们当时的数据接口整体耗时 2200ms其中应用内纯 Java 代码逻辑只有 120msRedis 读写平均 2msMySQL 查询平均却占了 1900ms。这就是很明显的数据库侧瓶颈。反过来如果数据库耗时 30msRedis 平均耗时 400ms那你优化 SQL 根本没用问题在缓存的 key 设计或 IO 阻塞上。这种拆解法听起来很简单但实际排障时容易乱因为大家经常被表象带偏。比如数据库 CPU 高第一反应确实会想到 SQL 问题但也可能是缓存大批量过期后所有请求同时穿透到数据库把数据库打爆了。所以看监控时不要只看数据库指标还要看缓存命中率曲线。如果 Redis 的 keyspace_misses 在故障时间点同时飙升那瓶颈大概率在缓存侧SQL 只是背了锅。1.2 没有基线数据所有调优都是空谈每次排障结束后团队里常犯的毛病是内存里调了一个参数觉得快了就直接上线。其实在数据服务性能调优里最忌讳的就是没有对照组。我建议每个核心数据服务都维护一份性能基线至少包括这几个指标每天固定时间段的接口平均耗时、TP99、数据库 QPS、慢查询数量、缓存命中率。不需要多复杂的平台用脚本定期采集就行。举个采集基线数据的例子比如统计 MySQL 慢查询的数量变化可以定期抓 slow loggrep -c Query_time:.* $(mysql -N -e show variables like slow_query_log_file | awk {print $2})Redis 的命中率可以直接从 INFO 里算redis-cli info stats | grep -E keyspace_hits|keyspace_misses拿到这两组数再结合应用监控你就能建立一个简单的优化前 vs 优化后对比坐标系。更重要的是我建议一次调优只改一个变量。比如你今天既改了 SQL 索引又调了缓存过期时间还动了连接池大小那最后性能提升到底归功于哪一步你根本说不清后续遇到问题也无法复用经验。这是很多团队调优反复折腾却无法沉淀方法论的核心原因。2. 从慢SQL日志和执行计划里挖出真凶2.1 慢SQL日志正确读法Rows_examined 比执行时间更值钱慢 SQL 日志大家都会开但很多人只盯着 Query_time。我会先教团队看两个字段Rows_examined 和 Rows_sent。Rows_examined 是这条 SQL 扫描过的行数Rows_sent 是最终返回的行数。如果 Rows_examined 是 500 万Rows_sent 只有 20说明存储引擎翻遍了 500 万行最后只给你留下 20 条这种 SQL 不管执行时间当前看起来是否超过阈值都迟早会出事。另一个很实用的排查工具是 SHOW FULL PROCESSLIST。慢日志是事后看的但线上数据库 CPU 正在飙升时你需要马上知道当前哪几条 SQL 在火上浇油。执行SHOW FULL PROCESSLIST; -- 重点关注 Time 字段大的会话以及 State 字段是否为 Sending data / Sorting result看到长时间 Sending data 的查询基本可以判断它在做大量的行读取和过滤下一步就是抓出来做 EXPLAIN。MySQL 8.0 及以上版本可以直接用 EXPLAIN ANALYZE 拿到一条 SQL 每个阶段实际消耗的时间这比传统 EXPLAIN 只看预估 rows 要直观得多。团队里很多人第一次用的时候都不太适应因为传统 EXPLAIN 只给估算值而 ANALYZE 会真实执行一遍并把每一步的具体时间打出来。注意它是会真实执行 SQL 的所以在大表上要小心使用可以在测试环境或者事务里配合回滚来用。2.2 三种最常见的 SQL 性能事故索引失效、深分页、隐式转换在实际业务里能打垮数据服务的 SQL 事故基本就那几类这里展开说一下索引失效最典型的场景是查询条件里对索引列做了函数运算。举个例子某订单表的 createdAt 列建了索引但查询写的是SELECT * FROM orders WHERE DATE(created_at) 2025-01-15;DATE() 函数套在索引列上优化器就没法走索引只能全表扫描。正确写法是改成范围查询SELECT * FROM orders WHERE created_at 2025-01-15 00:00:00 AND created_at 2025-01-16 00:00:00;这个改动的原理就是保持索引列本身干净让 B 树的二分查找能够生效。深分页是 LIMIT 写法里最坑的一种。业务端做分页经常用 LIMIT 1000000, 20这句话在 MySQL 里要先扫描出前 100 万行再把它们全部丢进临时结果集最后只拿第 1000001 到 1000020 行返回。越往后翻页扫描量越大。更合理的做法是用延迟关联先走覆盖索引取最小主键集合再回表拿完整数据SELECT t.id, b.title, a.content FROM ( SELECT id FROM orders WHERE user_id ? AND status 1 ORDER BY create_time DESC LIMIT 1000000, 20 ) t JOIN orders a ON a.id t.id JOIN products b ON b.id a.product_id;这个写法的关键在于子查询里只查主键 id排序也只需要用索引彻底避开回表和大字段的传输。如果业务上允许游标分页也就是记录上一页最后一条数据的 id再用 WHERE id last_id 取下一页性能还会更好只是不能随意跳页码了。隐式转换是很多慢 SQL 的隐形推手。比如手机号字段在库里是 varchar 类型查询条件却传了数字SELECT * FROM users WHERE phone 13812345678;MySQL 会在比较时把字符串和数字都转成浮点数导致索引列被隐式函数包裹查询走不了索引。这种问题往往靠肉眼很难发现需要盯执行计划里的 type 是不是从 ref 变成了 ALL。我在实际业务里还会遇到一类去重查询的问题。热搜词里SQL 语句去重出现频率非常高很多人习惯用 SELECT DISTINCT但如果去重的字段本身没有索引MySQL 会产生临时表。假设你有一张 800 万行的用户标签表想统计所有出现过的标签SELECT DISTINCT tag_name FROM user_tags;如果 tag_name 没有索引这条 SQL 会把全表数据捞进内存做排序去重内存不够还要落盘临时表堪称性能杀手。可以先给 tag_name 建一个二级索引让索引本身就保证有序DISTINCT 就能顺着索引顺序直接取一遍避免临时表排序。或者把单列 DISTINCT 改成 GROUP BY在慢日志里的表现往往也好一些因为优化器对 GROUP BY 的分组下推处理更成熟。2.3 一次 SQL 改写的前后对比关注执行计划里的 type 和 Extra很多刚入门的朋友会把 SQL 优化当成背模板什么不要用 SELECT *、WHERE 条件放最左侧这类口诀背了一堆但一遇到线上问题还是不会分析。我提供一个标准动作写任何一条 SQL 都养成执行 EXPLAIN 的习惯并且只看几个关键列。列名重点关注含义typeconst / eq_ref / ref / range / index / ALL访问类型ALL 是全表扫描range 及以下基本需要优化key实际选中的索引NULL 说明没用到索引rows预估扫描行数越大越危险结合真实行数判断ExtraUsing filesort / Using temporary / Using indexfilesort 表示排序没用上索引temporary 表示用了临时表Using index 是覆盖索引的加分项我之前处理过一个真实案例。业务需求是根据用户 ID 查最近 20 条带商品信息的订单列表初始 SQL 长这样SELECT o.id, o.order_no, p.title, o.pay_amount FROM orders o JOIN products p ON o.product_id p.id WHERE o.user_id 10086 AND o.status 1 ORDER BY o.create_time DESC LIMIT 20;这条 SQL 看着挺正常但 EXPLAIN 的结果显示 orders 表走的是索引扫描 typeref预估 rows 有 3 万多Extra 里还有 Using filesort。原因在于 order by create_time 这个排序字段不在 user_id 和 status 组合成的联合索引里MySQL 需要先取到 3 万多条数据再额外做文件排序。后来我把联合索引改成 (user_id, status, create_time)同一个 EXPLAIN 的 Extra 里不再出现 Using filesortrows 降到 6000 多接口从 890ms 掉到了 55ms。整个优化过程中没有改任何业务代码只是让索引的结构刚好覆盖了 where 过滤和 order by 排序两个需求。这类组合索引设计的思维比单纯背一句给 WHERE 字段加索引价值大得多。因为一条 SQL 的执行链路是先通过索引定位到满足条件的行再把需要排序的字段按索引顺序取出最后回表拿完整数据。你的索引字段顺序设计得越贴近查询模式存储引擎的每一步就越省力。3. 缓存层的问题比SQL更隐蔽3.1 缓存命中率上不去先怀疑 key 设计和数据粒度优化完 SQL 之后很多数据服务的性能瓶劲会转移到缓存层。缓存问题隐蔽在它不是直接报错的而是让你感觉接口变慢但又找不到原因。最常见的一个坑是缓存命中率长期低于预期。怎么量化命中率用 Redis 自带的指标就可以redis-cli INFO stats | grep keyspace # keyspace_hits: 89123 # keyspace_misses: 8827命中率 keyspace_hits / (keyspace_hits keyspace_misses)。读多写少、访问相对均衡的业务命中率长期低于 90% 就要警惕了。我从实际项目里复盘过命中率上不去的原因通常不是缓存容量不够而是 key 设计不合理。一种典型问题是缓存粒度太粗。比如把整个首页接口返回的 JSON 包当作一个 key 来缓存但业务里这个 JSON 中只有顶部轮播图部分频繁变化导致运营每次改内容都要失效整个缓存用户请求把整包数据重新回源一次数据库。这种场景应该把动态区块和静态区块拆成两个缓存 key动态部分短过期时间静态部分长过期时间命中率立刻能提上来。另一种典型问题是缓存 value 太大。有些同学为了方便把数据库一行所有的列都塞进 Redis里面可能有个大字段存的是几千字的描述文本。结果就是每次取缓存都发生大流量传输Redis 的带宽先被打满延迟自然就上去了。合理做法是只缓存业务真正高频使用的字段大字段仍然走数据库或者单独做压缩存储。如果访问热度非常集中也就是少数几个 key 占据绝大多数流量光有 Redis 还不够。我会在应用进程内再放一层本地缓存用 Caffeine 或者 Guava Cache 做 L1Redis 做 L2。这样热点数据在大部分情况下直接命中进程内存连网络 IO 都省了。但要注意本地缓存和 Redis 之间的过期时间必须错开不然会变成所有节点同时回源 Redis 甚至数据库。另外也要提醒一句像 MyBatis 这种 ORM 框架自带的二级缓存如果业务里已经接入了 Redis 作为统一缓存一般不建议再开启 ORM 二级缓存。因为两级缓存的失效时机不同数据一致性验证的成本远大于它带来的那点性能收益。3.2 缓存穿透、击穿、雪崩三个完全不同的故障场景这三兄弟经常被混在一起说但它们的成因和应对策略完全不一样我用实际场景逐个拆开缓存穿透指的是请求查询的数据压根不存在于任何一层。比如用户 A 假装用一个不存在的订单 ID 疯狂请求每次缓存都查不到请求直接打到数据库。这就是缓存没拦住流量。应对穿透最稳妥的办法是两层方案一层是参数合法性校验把明显不存在的 ID 挡在入口处另一层是缓存空值也就是虽然数据库查不到结果也把空结果以短过期时间比如 60 秒存进缓存让后续同样请求命中缓存。如果接口对外的 key 空间很大还可以用布隆过滤器在缓存之前做一次快速判断会省下大量 Redis 访问。缓存击穿指的是某一个热点 key 在过期的瞬间发生高并发访问所有请求同时回源数据库。比如某个爆款商品的详情数据平时几万 QPS 都在读缓存凌晨缓存过期的那一刻请求像洪水一样涌向数据库。处理思路有两个第一是逻辑过期即缓存的 value 里存一个过期时间应用层发现逻辑过期后只有一个线程能拿到分布式锁去数据库刷新缓存其他线程先返回旧值。第二是互斥锁用 Redis 的 SETNX 保证同一时刻只有一个请求回源数据库。我给你一个互斥锁的伪代码业务里可以直接参考String data redis.get(key); if (data null) { boolean locked redis.setIfAbsent(lock: key, 1, Duration.ofMillis(500)); if (locked) { try { data db.query(...); redis.set(key, data, Duration.ofMinutes(30)); } finally { redis.del(lock: key); } } else { // 其他请求先休眠一小会儿再读一次缓存 Thread.sleep(50); data redis.get(key); } }这段代码的精髓在于拿到锁的线程负责回源更新缓存没拿到锁的线程不直接压到数据库而是等锁释放后再读缓存。50ms 的休眠时间是经验值太短会导致大量线程立刻重试太长会增加接口耗时可以根据实际业务压测调整。缓存雪崩是指大量 key 在同一时间段内集中过期导致大部分缓存同时失效请求全部打到数据库。这个问题的经典解法是在设置 TTL 时加随机抖动比如本来都是 30 分钟过期改成 30 分钟加一个 0 到 300 秒的随机值避免所有 key 手拉手一起消失。另外也可以让缓存分为多套过期周期或者做多级缓存降级。雪崩一旦发生单靠 TTL 调整往往来不及所以一定要有预案数据库连接池限流、接口降级开关、优先保证核心链路可用。这里我要特别强调一点缓存这三个问题和前面说的慢 SQL 不是孤立的两件事。很多时候线上事故是慢 SQL 优化做到一半你发现有缓存兜底就降低了警惕结果缓存一出问题所有 SQL 问题加倍放大。缓存治理的本质是把访问压力在中间件层面做缓冲但绝不能替不健康的数据访问辩护。3.3 缓存一致性没有银弹只有最终一致缓存和数据库的双写一致性问题是数据服务性能调优里绕不过去的坎。先说一个现实结论没有一套方案既能保证强一致又有高性能。如果你的业务真的要强一致最稳妥的办法就是不读缓存直接查数据库。而绝大多数读多写少场景我们追求的是最终一致也就是允许在极短时间窗口内读到旧数据。主流的方案是 Cache Aside 模式也就是先更新数据库再删除缓存。更新数据库后缓存被删掉下一次读请求就会回源数据库并重新加载缓存。这个方案之所以比先更新缓存强是因为它避免了并发写时缓存里留下旧值的问题。删除缓存后即使有并发读读到的也只是短暂的空窗期数据最终还会被重新加载成最新值最终一致性能保证。在并发要求更高的场景下可以在删除缓存前做一次延迟双删。也就是更新数据库后先删除缓存睡眠几百毫秒再删一次缓存。这多出来的一次删除是为了清掉那些在第一次删除前读到了旧值、并且正在回写缓存的并发请求。但这套方案有个显而易见的缺点睡眠等待是耗时操作不适合放在同步调用链路里实际项目里我一般会用消息队列异步做第二次删除。现在很多团队会引入基于 binlog 的异步刷新方案。也就是让应用程序把更新的动作同步到消息队列后台消费者解析数据变更后再去刷新缓存。这样做的好处是业务代码完全不用关心缓存操作可以做到应用和缓存解耦。但要注意它的成本需要搭建 binlog 监听组件同时刷新缓存是异步的会有更明显的时间窗口。比较各家方案时我会参考几个关键维度方案一致性强度实现复杂度适用场景先更 DB 再删缓存最终一致窗口短低大多数业务延迟双删最终一致窗口更短中并发写较多需要缩短不一致窗口binlog 订阅刷新最终一致窗口较长高团队有中间件能力希望应用无感知强一致读 DB强一致低对一致性要求极高、读并发不高的场景一致性调优这件事我踩过的坑是一开始总想做到极致为了一秒内可能出现的一次不一致投入了大量复杂度。后来我把关注点改成了让旧数据在页面上的存续时间控制在可接受的范围内比如运营后台秒杀库存信息允许有 500ms 的延迟可见但支付状态绝对不能有延迟。不同数据对一致性的敏感度不一样缓存策略不能一刀切。4. 把性能调优落进平常的迭代里4.1 连接池、批量操作和应用侧浪费SQL 和缓存都正常的情况下数据服务性能还有一个容易被忽略的缺口应用侧的资源浪费。其中最典型的就是数据库连接池配置不合理。很多项目总想着把连接池调大好像连接数越多性能越好。其实每个连接都对应数据库端的一个线程连接数过大时线程切换开销会拖垮数据库连接排队反而严重。如果你用的是 HikariCP可以按这个思路设置初始参数spring.datasource.hikari.maximumPoolSize20 spring.datasource.hikari.minimumIdle5 spring.datasource.hikari.connectionTimeout3000 spring.datasource.hikari.maxLifetime1800000maximumPoolSize 的经验公式是核心并发数 ×单连接处理一个请求的耗时 网络等待时间再除以单请求目标 RT。比如你预期峰值并发 200单次数据库操作平均 10ms一个连接每秒能处理 100 个请求那 20 个连接就够用了。连接不是越多越好够用且留有余量才是健康状态。还有一个很典型的浪费是 N1 查询。用 ORM 时很多人会写循环里逐条查数据库for (Product product : productList) { ProductDetail detail productDetailMapper.selectByProductId(product.getId()); }假设 productList 有 50 个元素这就是 50 次数据库往返。正确做法是先查出所有商品 id再用 IN 查询一次性取回ListProductDetail details productDetailMapper.selectByProductIds(idList);数据库的网络往返被极大压缩性能提升是非常明显的。还有一层隐藏损耗在事务边界。长事务会长期持有数据库锁导致其他普通查询全部排队等待。我在项目里见过一个接口把远程调用也放在事务里执行远程服务慢两秒数据库事务就开两秒后面所有这个表的写操作全部堵住。事务的范围只应该包住真正需要原子性的写操作查询、远程调用尽量放在事务外。4.2 监控和告警把调优成果固定成防线一个数据服务的性能调优做到最后如果只停留在某次上线那后续很快会退化。我把长期稳定运行的秘诀总结成四个字持续观测。团队里应该建立起一套基础监控和告警体系维度不需要多但每条都要真实有效。我常用的监控指标和阈值大致如下监控目标推荐阈值或动作说明接口 TP99超过 500ms 告警数据服务常见目标根据业务调整慢 SQL 平均时长超过 200ms 触发记录超过阈值的 SQL 自动进慢日志分析缓存命中率低于 90% 关注、低于 85% 告警命中率骤降往往意味着缓存策略变化或热 key 失效连接池活跃连接数超过最大值的 80% 告警避免连接耗尽后才被动处理数据库 CPU连续 5 分钟超过 80% 告警和慢 SQL、缓存穿透联动分析这些阈值不是拍脑袋定的我是结合常见业务压测结果和数据库经验值整理的。实际项目里要根据你服务的 QPS 基线和可用性目标调整建好后也不要一劳永逸每次大版本迭代后回看一轮。告警配置好之后调优经验的沉淀也很重要。我习惯每次线上性能事故后写一份简短复盘格式包括故障时间、触发的监控项、定位链路、根因、改动点、验证方式。下次再遇到类似问题直接翻历史复盘比重新排障省太多时间。4.3 一道SQL面试题怎么答才能体现实战能力热搜词里SQL 面试题出现频率很高但大多数人准备面试时喜欢背八股比如索引有哪些结构、SQL 优化有哪些手段。真到了面试官问一句线上数据库 CPU 突然 100%你怎么排查很多人就只会说加索引、开慢查询日志回答得非常空。我建议把整条调优链路串成一个标准问答。面试官问数据库 CPU 100% 怎么办你的回答至少要包含四层第一步观察监控确认 CPU 升高和接口 QPS 变化有没有相关性第二步抓 SHOW FULL PROCESSLIST 和慢查询日志定位具体是哪几条查询占用了大量执行时间第三步用 EXPLAIN 分析执行计划看是索引失效、深分页还是大量小查询堆积第四步检查缓存命中率曲线自主判断是不是缓存穿透或雪崩导致请求打到数据库。这样回答面试官能从中看到你有全局观而且每一步都有真实操作支撑。如果面试官进一步问SQL 优化有哪些手段你可以分两个层次来说。业务层是缓存、读写分离、连表粒度控制、数据库表结构设计单条 SQL 层面才是索引设计、执行计划分析、深分页改写、避免隐式转换。不用把每一个细节都背出来只要让面试官看到你已经形成了一套从宏观到微观的方法论这个回答就已经赢过大多数背模板的候选人了。数据服务性能调优这条路我实践下来的核心体会只有两个第一调优前先建立基线调优中一次只改一个变量第二SQL 和缓存永远是一体的缓存方案设计得再好SQL 底子不健康也只是延迟了问题爆发的时间。最后分享一个工作习惯我每周会固定抽出半小时把线上慢 SQL 日志和缓存命中率拉出来过一遍不用等到故障发生才去救火。这种持续的巡检带来的收益比临时抱佛脚的调优大得多。