数据库性能优化实战：从慢查询到缓存，接口耗时从1.2s降至80ms

你有没有遇到过这种情况接口压测时TPS上不去数据库CPU直接拉满开发同学的第一反应是“加缓存”“加机器”结果第二天线上还是被打回原形我做了多年后端越来越觉得数据库性能优化不是零散的“调几个参数”而是一整套后端思维。所谓后端思维核心是站在整个数据链路上看问题接口慢慢在哪一层数据访问层占了多大比重索引建得合不合理连接池配置是不是在拖后腿这篇内容不搞教科书式的堆概念我会从实际项目出发聊聊数据库性能优化时真正要做的几件事包括慢查询定位、索引设计、SQL改写、连接池管理、缓存与读写分离最后用一个完整案例复盘从1.2秒优化到80毫秒的过程。适合正在做后端开发、系统设计或者被数据库性能问题折腾过的朋友参考。1. 后端视角下的数据库性能问题先从根上建立认知1.1 大多数性能问题其实发生在数据访问层在我接触过的项目里一个典型的前后端分离系统请求链路大致是浏览器到Nginx再到后端服务最后落到数据库。浏览器渲染慢Nginx配置不合理后端代码有锁竞争这些都可能让接口变慢但真正占大头、最经常被忽视的是数据库这一层。我和很多后端同事排查问题时有同感接口平均耗时1秒以上其中70%甚至90%都消耗在SQL执行或等待数据库连接上。比如一个订单列表接口业务代码本身可能就是取参数、调Mapper、拼Vo再返回这部分本地操作撑死几毫秒。但如果Mapper里那条查询没有走索引全表扫描了几十万行单条SQL就能跑到几百毫秒。用户点击一次查询数据库要忙活这么久后端再快也没用。所以在开始优化前我习惯先在脑子里画一条链路把数据库访问单独拎出来看。不要一上来就怀疑代码逻辑很多时候问题根本不在代码而在SQL和索引。1.2 优化的三个层次SQL、系统参数、架构数据库性能优化不是只有一条路我一般把它拆成三个层次。第一层是SQL与索引层。这一层收益最高成本最低。一条慢SQL通过增加索引、改写查询方式从1秒降到几十毫秒完全可能。第二层是MySQL实例参数与硬件层例如调整innodb_buffer_pool_size、连接数上限、磁盘类型这一层需要结合机器配置和业务场景效果也很明显但影响面大。第三层是架构层比如引入Redis缓存、做主从读写分离、分库分表这一层改动最大通常要涉及系统设计层面的重构。很多团队的误区是刚出现接口变慢就直接上缓存结果缓存命中率很低甚至因为数据一致性问题引入新bug。我建议的优化顺序是先看SQL和索引再做参数调整最后动架构。低垂的果实先摘完再决定要不要砍树重建。理解了这三个层次后面所有动作都会更有章法。1.3 性能优化没有银弹但有一个完整流程数据库性能优化最忌“拍脑袋”。我自己的标准流程是四步先找问题再分析原因然后动手优化最后验证效果。听起来简单但很多人会跳过“分析原因”直接上临时方案。用一句话概括就是“测量-优化-验证”的闭环。第一步测量开启慢查询日志抓出慢SQL第二步分析用EXPLAIN看执行计划判断是全表扫描还是走索引扫描了多少行第三步优化针对根因做调整可能是加索引可能是改SQL也可能是调连接池第四步验证用压测或者线上监控对比优化前后的耗时、TPS、CPU。整个流程不能少任何一步尤其是验证很多人优化完没压测就上线结果反而引入更严重的问题。这一套流程看起来笨但在真实项目中往往最有效。后面几章我会按这个顺序拆开讲。2. 慢查询定位别凭感觉猜用数据说话2.1 开启慢查询日志让数据库自己“汇报”找数据库性能问题我从来不会靠猜而是先让MySQL自己把慢的SQL记录出来。慢查询日志就是干这个的。通常我会在测试环境先开启线上环境根据团队接受程度开启因为日志本身也会带来少量IO开销。常见的配置如下slow_query_log 1 slow_query_log_file /var/log/mysql/mysql-slow.log long_query_time 1 log_queries_not_using_indexes 1long_query_time表示超过多少秒算慢查询这里设为1秒。生产环境如果压力大可以设为0.5秒甚至更低看业务情况。log_queries_not_using_indexes会额外记录那些没有走索引的SQL这类SQL哪怕很快也值得关注因为数据量涨上去后随时可能变成慢SQL。开启之后不要干等直接跑几轮业务操作然后查看日志mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log这条命令按时间排序取前10条最慢的SQL分析起来非常方便。慢查询日志是第一步能帮我把问题范围从“整个系统”缩小到“几条SQL”。2.2 用EXPLAIN读执行计划看懂索引扫描的代价拿到慢SQL后下一步永远是EXPLAIN。这是分析执行计划的利器相当于把MySQL的执行思路摊开给你看。EXPLAIN SELECT * FROM order WHERE user_id 123 AND create_time 2024-01-01 ORDER BY create_time DESC LIMIT 20;执行后的列有很多我重点看四个type、key、rows、Extra。type表示访问类型从上到下常见的有system、const、eq_ref、ref、range、index、ALL。其中ALL是全表扫描性能最差index是扫描整个索引树也好不到哪去range是范围扫描ref是普通非唯一索引等值匹配const和eq_ref是与主键或唯一索引相关的高效访问。上面这条查询如果没有联合索引type大概率是ALLrows会显示扫描几万行如果建好了联合索引type可能变成rangerows会降到几十行。key字段显示的则是实际用到的索引名如果为NULL说明没走索引。Extra字段也很有信息量。比如看到“Using temporary”表示用了临时表常见于GROUP BY或ORDER BY处理不当“Using filesort”表示额外排序通常需要优化索引“Using index”则表示覆盖索引是理想情况。用EXPLAIN分析一次慢SQL基本就能确定问题出在索引还是SQL写法。2.3 从压测和监控数据倒推根因慢查询日志能告诉我们SQL有多慢但有时候需要压测才能看出问题的严重性尤其是接口级别的性能。我常用JMeter或者简单的脚本做并发压测然后配合MySQL自身的监控命令去看实时状态。常用的几个命令是SHOW PROCESSLIST; SHOW GLOBAL STATUS LIKE Threads_connected; SHOW GLOBAL STATUS LIKE Innodb_buffer_pool_read_requests;SHOW PROCESSLIST能看到当前正在执行的SQL如果压测时发现大量查询长时间处于Sending data状态基本可以断定数据库在卖力地全表扫描。Threads_connected过高则说明连接数压力大可能是连接池配置不合理也可能是锁等待阻塞了请求。监控的意义在于“从现象倒推根因”。比如TPS上不去同时看到大量连接堆积说明瓶颈可能在连接池或锁如果DB CPU打满慢日志里全是同一类查询那就是SQL的问题。这个步骤做扎实了后面优化才有明确方向。3. 索引设计实战好索引是设计出来的不是堆出来的3.1 过滤性、覆盖索引与回表索引是数据库优化最核心的武器但很多人对索引的理解停留在“给查询字段加索引”这一步。我见过不少工程师把所有WHERE条件字段全部索引一遍结果索引建了不少查询该慢还是慢。原因往往在于没有理解索引的过滤性和覆盖效果。先说过滤性。索引的作用是快速缩小范围如果一个字段的取值只有“是”和“否”两种那它对过滤的贡献就很有限。比如有个字段status表示订单状态值为1和2分布均匀即便建了索引MySQL可能也认为直接扫全表更划算。反过来像user_id这样的字段一个用户在大量订单里只占一小部分过滤性就很好。设计索引时要优先选择过滤性高的列。再说覆盖索引和回表。InnoDB的主键索引叶子节点存的是整行数据普通索引叶子节点存的是主键值。如果查询需要的数据列都包含在索引里MySQL可以直接用索引返回结果不需要再回表查整行Extra里会显示Using index这就是覆盖索引。例如我们有个订单表经常要查“某个用户最近的下单金额”那么联合索引(user_id, amount)就能覆盖这个查询避免回表。回表次数越多IO消耗越大所以能用覆盖索引解决的问题就不要轻易SELECT *。3.2 最左前缀原则在联合索引中的应用联合索引用得合不合理往往决定了SQL快不快。MySQL遵循最左前缀原则例如建立联合索引(a, b, c)查询条件只要包含a、或包含a和b、或同时包含a、b、c总之从最左列开始连续使用就能用上这个索引。如果条件里跳过a直接从b开始索引就用不上。实际项目中我最常用到的场景是按用户加时间查询。比如订单表查询条件通常是user_id create_time status那联合索引可以建(user_id, create_time)状态字段如果区分度不高可以加在最后。原因很简单user_id等值过滤create_time做范围排序这正好符合最左前缀和排序需求。相反如果建(create_time, user_id)那查询条件里如果没有create_time索引就失效了。设计联合索引时还要考虑“排序”需求。SQL里带ORDER BY create_time DESC时如果索引已经包含create_time并且前面有user_id等值条件那排序可以直接用索引完成不用filesort。很多慢查询的根源就是忽略了这一点。3.3 索引失效的常见写法与规避索引建了不代表一定用得上。这里列几个我踩过坑的典型场景。第一在索引列上做函数运算。比如WHERE DATE(create_time) 2024-01-01这样写会让索引失效正确写法是create_time 2024-01-01 AND create_time 2024-01-02用范围查询代替函数计算。第二隐式类型转换。比如字段是varchar查询条件却传了数字MySQL会做隐式转换导致索引失效。解决办法是保证参数类型跟字段类型一致。第三LIKE前导模糊查询。WHERE name LIKE %张%这个“%”在最前面索引无法高效匹配只能全表扫。业务如果一定要这种搜索建议走全文索引或者搜索引擎。LIKE 张%这种前缀匹配还是可以走索引的。第四OR条件连接两个不同字段如果其中一个没有索引整体索引就可能失效。优化思路是把OR改成UNION ALL或者确保每个条件都能走索引。需要说明的是这些“失效规则”在复杂场景下不是非黑即白MySQL优化器可能根据不同版本和数据分布调整计划。所以关键在于养成习惯每写完一条重要的查询都EXPLAIN一眼。4. SQL写法上的共性问题与改写思路4.1 SELECT * 与不必要的字段消耗很多序列化框架让我们习惯了直接用实体映射于是SQL动不动就SELECT *。这样写最大的问题不是“多查了几个字段”而是破坏了覆盖索引的意义。明明一个普通索引就能覆盖结果因为带了无关字段不得不回表读整行。另外字段越多传输到应用层的网络IO就越大内存占用也越高特别是在字段多、行数大的列表查询里。我实际改SQL时会先把实际需要的字段列出来。比如订单列表页只需要订单号、金额、状态、创建时间那就只查这四个字段如果它们恰好被索引覆盖性能会明显提升。这条属于最基础、性价比最高的SQL改写。4.2 分页深翻页的优化延迟关联分页查询在后端开发里非常常见数据量一大就能看到问题。LIMIT 100000, 20意味着MySQL要扫描前100020条然后丢掉前100000条只返回最后20条。扫描的行数跟偏移量成正比翻到越后面越慢。解决思路是“延迟关联”先通过索引定位到目标行的主键再用主键去关联获取所需字段。比如原SQL是SELECT * FROM order WHERE status 1 ORDER BY create_time DESC LIMIT 100000, 20;改写为SELECT o.* FROM ( SELECT id FROM order WHERE status 1 ORDER BY create_time DESC LIMIT 100000, 20 ) t JOIN order o ON o.id t.id;子查询只查主键id索引覆盖下扫描成本小很多然后再用主键回表取20条完整数据。我实测过当偏移量达到几十万行时这种优化往往能把查询时间降低一个数量级。分页深翻页还可以用“基于游标”的方式即带上上一页最后一条记录的id作为查询条件适合对页码跳转要求不高的场景。4.3 避免在索引列上做函数运算SQL写法里最容易忽略的坑就是在查询条件里包一层函数。除了前面说的DATE函数还有常见的LEFT(name, 2)、YEAR(create_time)、TIMESTAMPDIFF等。一旦索引列被函数包裹MySQL就无法直接比较索引树中的值只能退化为全表扫描。这类问题的改法通常是“把函数换成边界条件”。例如要查近30天的数据不要写DATE_SUB(NOW(), INTERVAL 30 DAY)而是直接计算好起始时间再用create_time ? AND create_time ?。计算逻辑放到应用层数据库只做区间扫描性能自然不一样。4.4 小表驱动大表与JOIN的评估JOIN写法的好坏直接影响执行效率。MySQL优化器会自己决定驱动表和被驱动表但SQL写法往往会影响它的判断。一条原则是小表驱动大表。对于IN和EXISTS如果外层是小表用IN外层是大表用EXISTS。JOIN时尽量让结果集小的表作为驱动表。实际项目里我碰到最多的两个问题一是滥用LEFT JOIN明明只需要内连接数据却LEFT JOIN了一张大表导致MySQL扫描大量无关行二是在JOIN的ON条件上做隐式类型转换索引失效。建议先确认业务语义能用INNER JOIN就用INNER JOINLEFT JOIN会保留左表所有行对大表查询影响很大。JOIN优化没有唯一标准我习惯在写完JOIN后必看执行计划确认驱动表是否符合预期。如果发现优化器选错了驱动表可以通过STRAIGHT_JOIN或者调整条件来干预但一般情况下优化器比人靠谱别急着强行干预。5. 连接池与数据库连接的资源管理5.1 连接池参数不是越大越好后端服务访问数据库基本都要通过连接池。连接池的作用是复用连接避免每次请求都建立新连接。但连接池参数有个很常见的误区很多同学认为maximumPoolSize越大越好结果把数据库拖垮。连接不是免费的每个连接都会占用数据库内存和线程资源。连接开得太多数据库要花大量时间在上下文切换上反而降低吞吐。以Spring Boot默认的HikariCP为例我通常不会让最大连接数超过50具体值取决于业务和机器配置。一个比较推荐的做法是把连接数控制在机器CPU核心数的2到3倍再根据压测逐步调整。HikariCP的典型配置spring: datasource: hikari: maximum-pool-size: 20 minimum-idle: 5 connection-timeout: 30000 idle-timeout: 600000minimum-idle太小会导致高峰期频繁创建连接太大又浪费资源。我一般设置为5到10最大连接数则根据压测结果动态调整。判断连接池是否足够一看Threads_connected二看获取连接的超时率如果出现Connection is not available的异常说明池大小不够了。5.2 连接泄漏与事务边界连接池最隐蔽的坑是连接泄漏。一次请求中如果代码里开启事务后忘了提交或释放连接连接就一直被占用连接池会慢慢耗尽。更常见的是Transactional被放在大方法上一个方法内部做了很多无关的查询和远程调用事务迟迟不提交数据库连接长时间不归还。我在排查线上问题时遇到过接口不慢但连接池被打满所有请求都卡在获取连接上。后来定位到是一个定时任务方法把好几百条数据处理放在一个事务里耗时几十秒期间连接一直被占着。解决办法很简单把大事务拆小只让真正的写入操作进入事务查询不需要加事务的就不要加Transactional。另一个经验是让连接池自己暴露监控指标。HikariCP可以通过Actuator、Micrometer上报当前活跃连接数、等待获取连接数等指标。一旦发现活跃连接长期接近上限就要警惕是否有连接泄漏。排查方式很简单压测时用jstack抓线程栈查看哪个方法里的连接被长时间占用。5.3 数据库连接数的估算方法连接池到底设置多大不能拍脑袋。这里分享一个我常用的估算公式连接池大小 ≈ 最大并发请求数 × 平均单次请求的数据库耗时 / 期望的请求响应耗时。举个例子假设系统最大并发100个请求每个请求平均占用数据库连接10毫秒我们希望接口平均响应时间在100毫秒以内。那么需要的连接数大约是100 × 10 / 100 10个。这里的含义是100个并发请求同时进来如果每个请求耗10毫秒要让它们在100毫秒内完成至少需要10个连接交替干活。真实场景中耗时和并发都不是恒定不变的所以公式只能给出起点最终还是靠压测验证。但至少有了这个估算就不会把连接池随意设成1000。数据库连接数的配置最高指导原则永远是“够用且留有余量”而不是“多多益善”。6. 缓存与读写分离性能放大的两板斧6.1 缓存设计Redis缓存什么、怎么失效当SQL和索引优化做到位数据库还是扛不住大量读请求时就该上缓存了。最常见的组合是Redis。但缓存不是无脑缓存一切我见过太多缓存失效导致数据不一致的案例。缓存适合两类数据一是读多写少的热点数据比如商品详情、用户基本信息二是计算成本高、可容忍短暂不一致的数据比如报表统计。不推荐缓存的数据是写频繁、实时性要求极高的业务数据比如库存、余额这类数据直接用数据库管理更安全和简单。缓存设计的关键是“怎么失效”。我常用的策略是Cache Aside Pattern读的时候先读缓存缓存没有就读数据库然后回填缓存写的时候先更新数据库再删除缓存。为什么是“删缓存”而不是“更新缓存”因为删除缓存可以让下次读时重建避免并发写时缓存被覆盖成旧值。另外要特别注意过期时间不能设为永久否则数据更新后缓存迟迟不刷新会出问题。合理设置过期时间也是防止缓存雪崩的手段可以在基础过期时间上再加一个随机值。还要提防缓存穿透、击穿、雪崩。缓存穿透指查询一个不存在的key每次都打到数据库我的做法是缓存空值并设置短过期时间缓存击穿指某个热点key过期瞬间大量请求同时打到数据库可以用分布式锁或者提前预热缓存雪崩指大量key同时过期解决方法是过期时间随机化。6.2 读写分离的实施前提与风险读操作远多于写操作时读写分离通常是一步性价比很高的演进。它的思路很简单主库负责写入从库负责读取把读取压力分散到多台机器的从库上。但实施前要回答一个问题业务能否容忍主从同步延迟MySQL主从复制延迟在正常负载下可能只有几十毫秒但一旦主库有大批量写入或从库硬件较弱延迟可能飙升到秒级。如果刚写完订单立刻要从从库读取订单详情可能会读到null。这个场景就不适合无脑读写分离除非应用层有补偿策略或者强制路由到主库。我在具体项目里做读写分离会给数据源做路由写操作和强一致读走主库普通读走从库。Spring Boot里常用AbstractRoutingDataSource实现动态数据源切换或者直接引入ShardingSphere这类中间件。如果团队不想引入太重的东西也可以在代码里做一层简单的路由但要注意事务内必须保持单一数据源不能一个事务里既有主库又有从库。读写分离不是银弹。从库越多同步链路越复杂一致性风险越高。我的建议是先通过SQL优化和缓存把整体压力降下来如果读压力还是高再考虑读写分离。并且上线前一定要压测主从同步延迟确认延迟在可接受范围内。6.3 从“加缓存”到“减查询”的思维转变做性能优化久了我发现一个很有意思的规律高手往往在思考“怎么让数据库少干活”而不是“怎么让数据库干得更快”。加缓存、加从库本质上是把压力往外推但更高明的做法是直接减少不必要的数据访问。比如列表接口前端只展示20条数据结果后端查了整表再在内存里过滤这就是让数据库干了无用功。正确的做法是把过滤条件下推到SQL里让数据库只返回需要的行。再比如详情接口一个页面要调用五六次接口每次接口都查一遍数据库更合理的做法是提供一个批量聚合接口一次查询满足多个展示诉求。“减查询”听起来简单做起来需要后端有全局视野能够站在整个业务链路上判断哪些数据访问是重复的、哪些是可以合并的。我通常会在做性能评审时问团队三个问题这接口可以少查一次数据库吗这数据一定要实时查吗这查询能不能用更小的数据集完成这三个问题问完基本就能找到很多不必要的查询。7. 一次接口优化的完整复盘从1.2秒到80毫秒7.1 现象与压测数据说一个我自己经历过的案例。一个基于Spring Boot MySQL的交易记录查询接口分页显示用户账单前端报表页需要实时查询。功能很简单但在压测阶段发现平均响应时间1.2秒TPS低得吓人。数据库CPU在压测时持续跑到90%以上前端用户体验完全无法接受。当时接口大概长这样根据用户ID、时间段、状态三个条件分页查询然后再关联用户表和业务类型表。乍一看条件并不复杂问题出在哪按第2章的思路来排查。7.2 定位过程慢SQL、执行计划、瓶颈分析我先把慢查询日志打开压测一轮抓出来一条核心SQL。EXPLAIN一看type是ALL也就是全表扫描rows显示扫描了接近50万行。更加顺手的是这条SQL用了SELECT *并且分页偏移量已经到了20万行之后属于典型的深翻页。定位到根因三步走第一核心过滤条件user_id和create_time没有建联合索引导致全表扫描第二SELECT *取了大量不需要的字段破坏了覆盖索引的可能性第三LIMIT偏移量过大在取数和排序上浪费了大量时间。这一个接口就踩了前面提到的三个坑。7.3 优化动作索引、SQL改写、缓存优化分三步落地。第一步建联合索引把user_id、create_time和status组合成一个复合索引利用最左前缀原则覆盖查询条件ALTER TABLE transaction_record ADD INDEX idx_user_create_status (user_id, create_time, status);第二步改写SQL去掉多余的字段把原来SELECT *改成只查需要展示的字段。分页部分改成延迟关联先用索引覆盖子查询查出主键ID再JOIN取详情避免前20万条数据的浪费。第三步引入缓存。账单页的汇总金额和最近30天数据属于高热度数据我把它放到Redis设置5分钟随机过期时间。查询前先读缓存命中就直接返回没有再走优化后的SQL。同时给不存在的查询key缓存一个空值避免压测时因参数随机导致缓存穿透。优化前后对比非常明显指标优化前优化后平均响应时间1.2秒80毫秒扫描行数约50万行约200行EXPLAIN typeALLrangeTPS约30约3007.4 可复用的数据库性能优化Checklist经历了这次优化之后我整理了一份日常工作里经常对照的检查清单分享出来查询条件里的字段是否建了合适的联合索引是否满足最左前缀原则SQL是否SELECT *能否只查必要字段利用上覆盖索引分页查询是否深度翻页能否改成游标分页或延迟关联索引列上是否有函数运算或隐式类型转换连接池大小是否经过压测验证是否有连接泄漏风险高频读接口是否适合加缓存缓存过期策略和穿透保护是否完善业务是否容忍主从延迟能不能通过读写分离分散读压力这份清单不全面但每次遇到数据库性能问题我都会按这个顺序过一遍大多数问题都能在头几条找到答案。最后说一点个人体会。数据库性能优化不像写业务代码能立刻看到“功能完成”它更需要耐心和系统性思考。很多人上来就想用缓存和架构调整解决一切但真正的高手都会先把SQL、索引、执行计划这些基本功做扎实。我至今还保留着每个慢SQL都EXPLAIN的习惯因为这个习惯已经在线上帮我省下了无数次半夜加班的代价。希望这篇文章的思路和案例能让你在下次面对数据库慢查询时不再凭感觉下手而是有计划地一步步逼近根因。