1. 查询慢的本质性能问题不是调SQL而是调整数据访问路径先讲个真实的例子。去年年中我们线上有个订单查询接口平时P99延迟稳定在80毫秒左右突然某天开始飙到1.2秒监控告警响了一整夜。团队第一反应是加索引结果DBA加了两个索引上去延迟反而更差了——因为写入变慢导致主从延迟加大业务侧读不到最新数据又来投诉。后来我把执行计划拉出来一看发现那条SQL压根没走我们新加的索引优化器选了一条更差的路径。这个案例说明一个事儿查询性能优化本质上是在和数据访问路径较劲不是简单地加索引或者改SQL。你要先搞清楚数据是从磁盘怎么一层层走到应用层内存里的在哪一步卡住了然后才能谈优化。我做了十几年后端经手过的慢查询没有一千也有八百总结下来查询性能的核心公式就一个单次查询的响应时间 ≈ 数据扫描量 / 吞吐率 网络往返次数 × 单次RTT翻译成人话要么你让数据库少看数据要么让它看得更快要么就让应用少问几次。所有优化手段万变不离这三件事。这也解释了为什么网上那些优化三板斧经常失效——有人一上来就教你加索引但是没告诉你如果你的查询条件本身是范围匹配或者你的数据分布是极度倾斜的索引可能根本帮不上忙。你得分清自己卡在哪一层。我在实战中习惯把查询性能问题分成四个层级每一层的优化手段完全不同层级问题特征典型手段优化收益SQL写法层单条SQL扫描行数过大、关联顺序差改写SQL、调整JOIN顺序、改写谓词通常能提升5~20倍索引结构层走了索引但回表太多、索引失效复合索引设计、覆盖索引、索引下推提升10~100倍架构访问层热点数据集中、重复查询过多缓存、读写分离、垂直拆分提升100~1000倍数据分布层数据量级到了千万级以上、分页深翻水平分片、冷热分离、汇总表让查询量级降维我在接下来的内容里会按照排查问题的真实顺序来展开——先讲怎么定位瓶颈再讲索引怎么设计然后是SQL改写技巧最后是架构层面的兜底方案。整篇文章前后逻辑是按一套完整的实战链路串起来的不是散装技巧的拼凑。2. 第一件事永远是看执行计划定位瓶颈的完整套路很多人一接到慢查询第一反应就是打开编辑器开始改SQL。这是最大的误区。SQL只是表象执行计划才是真相。你对数据库说我要什么数据库通过优化器决定怎么拿真正决定性能的是后者。我见过太多这样的场景一条SQL肉眼看起来写得挺合理条件、索引都有但执行计划里显示它做了全表扫描因为隐式类型转换让索引失效了。如果你不看执行计划光靠脑子想想破头也找不出原因。2.1 执行计划里最值得盯的五个信号以MySQL为例执行计划用EXPLAIN就能拿到但我发现很多人拿到之后不知道该看什么扫一眼 type 列是ALL就开始加索引这不行。我一般按优先级关注这五样东西type列从好到差依次是 system const eq_ref ref range index ALL。如果是ALL或者index意味着全表扫描或全索引扫描这是第一个优化目标。key列看实际用到的索引是哪棵不是possible_keys。有时候优化器明明可选索引A和B最后选了个最差的你要知道为什么。rows列这是优化器预估的扫描行数不是精确值但量级基本靠谱。如果预估扫描100万行但返回100行说明筛选力度不够索引设计有很大问题。Extra列这里藏着各种告警。看到Using filesort要小心看到Using temporary更要警惕说明查询在排序或分组时建了临时表。filtered列MySQL 5.7表示存储引擎返回的数据中有多少比例真正满足查询条件。这个值如果很低比如5%说明索引虽然帮你精确到了某个范围但范围内绝大部分数据都是要被丢掉的。我给一个判断口诀你排查的时候按这个思路走基本不会乱先看type是不是全表扫再看key用没用上再看rows扫了多少行最后看Extra有没有额外的排序和临时表负担。2.2 我排查慢查询的固定流程下面是我的固定排查套路你可以直接抄作业。这个流程我在多个项目里验证过能覆盖80%以上的慢查询场景。从慢查询日志里捞目标SQLmysqldumpslow或者直接查performance_schema都行按平均耗时和出现频次排序优先处理频次高×耗时长的这种对整体延迟影响最大。把SQL复制出来加EXPLAIN跑一遍记录执行计划的快照。用EXPLAIN ANALYZEMySQL 8.0.18实际执行一次拿到每步操作的真实耗时和扫描行数和预估做对比。如果发现预估和实际偏差很大执行ANALYZE TABLE更新统计信息再看执行计划有没有变化——很多时候优化器走错路是因为统计信息过期了。确认瓶颈是扫描行数太多还是关联效率低然后进入对应的优化环节。注意EXPLAIN不执行查询EXPLAIN ANALYZE会真正执行查询。在生产库上跑EXPLAIN ANALYZE之前先确认这条SQL本身的耗时不会拖垮主库谨慎起见可以先在从库或者压测环境上跑。2.3 优化器选错索引的两个经典原因看执行计划的过程中你会频繁遇到明明有索引但优化器不用的情况。我总结下来最常出现的原因就两个。第一个是数据分布倾斜。比如订单表有个状态字段95%的订单都是已完成状态你查WHERE status pending走状态索引没问题但如果你查WHERE status completed优化器一算走索引要扫500万行再加回表不如直接全表扫1000万行来得快于是它放弃索引了。这是合理的不是bug。第二个是统计信息过期。优化器是靠表的统计信息来估算行数的如果统计信息长期没更新它估算的行数和实际情况差了几十倍选路就会跑偏。解决办法很简单——不是强行FORCE INDEX而是先更新统计信息再看。我见过不少人在这种场景下硬加索引或者改写SQL结果都是白忙活。还有一个容易忽略的点MySQL 8.0之前IN列表里的参数数量会影响优化器对索引的选择。列表元素过多时优化器可能放弃使用索引转而去扫全表。如果你在业务代码里拼了一个几百上千项的IN别急着怪数据库先考虑改写成临时表关联这在后面的SQL改写章节会详细讲。3. 索引优化收益最大也最容易翻车的环节索引优化是查询性能优化里收益最直观的环节——一条SQL从2秒降到10毫秒往往就是一棵合适的索引的事。但这也是翻车率最高的环节。索引不是越多越好不是每个条件都加一个单列索引就叫优化那叫碰运气。3.1 复合索引设计顺序就是一切最核心的一条规则复合索引的列顺序遵循等值条件优先、范围条件靠后、排序列兜底。举例说明。假设我们有个用户订单查询页筛选条件是用户ID等值订单状态等值下单时间范围范围按下单时间倒序一个合格的复合索引应该是(user_id, status, order_time)三个字段的先后顺序恰好对应了条件的类型。为什么因为B树索引本质上是一个有序的多级查找结构前面几列精确匹配时能最大程度地缩小扫描范围范围条件放在后面则可以让树在最后一层做有序的范围扫描。我打个比方。这就像图书馆查书先按索书号找到对应的书架再在书架上按分类标签找到对应排最后在这排里从头到尾扫一遍找出版年份范围内的书。如果你一上来就说我要找2019年到2021年出版的书那就得整个图书馆挨个书架走一遍了。如果你建的是(status, order_time, user_id)在查询里user_id用不上索引定位优化器只能拿status和order_time定位然后对命中的每一行再比对user_id。如果某个状态的订单特别多这个筛选差距就是几何级的。3.2 覆盖索引从到磁盘搬数据变成在索引里直接拿答案覆盖索引是我在所有优化手段里最偏爱的一个因为它的收益几乎是白捡的。普通索引查找的过程是先在索引B树里找到主键值这个过程叫索引定位然后拿着主键回到聚簇索引里找完整行数据这个过程叫回表。回表意味着多一次随机磁盘I/O这在机械硬盘时代是致命的即使现在SSD时代随机I/O的代价依然远高于顺序I/O。覆盖索引的思路是让索引里的字段本身就包含你查询需要的所有列这样查完索引直接返回结果连回表的动作都省了。举个具体例子。假设订单表有几十个字段你的列表页只需要查order_id, order_no, amount, created_at这几个字段条件是user_id ?且created_at ?。这时候索引(user_id, created_at, order_id, order_no, amount)就能让这条查询完全在索引页里完成不需要回表。数据量大时这个优化能从扫100万行并回表100万次变成扫100万行索引页但零回表速度差异经常是几十倍的。但这里有个隐形代价要提醒你宽索引会拖累写入性能也会占用更多存储空间。每棵二级索引都是一棵独立的B树你加一棵索引每次INSERT就要多维护一棵树。所以覆盖索引只针对高频查询不要把所有查询都试图用覆盖索引解决。我个人的经验是单表索引总数控制在5~7个以内比较安全再多就要权衡写入代价了。3.3 索引失效的经典场景对照表我把工作中踩过的索引失效场景整理成了一张表你在排查时可以对着看。失效场景原因正确做法WHERE user_id 1 100对索引列做了运算改写为user_id 99WHERE DATE(created_at) 2024-01-01对索引列用了函数改写为范围条件created_at 2024-01-01 AND created_at 2024-01-02WHERE mobile 13812345678mobile是varchar隐式类型转换传入参数也写成字符串SQL和参数类型保持一致WHERE name LIKE %张%前模糊匹配导致索引失效改用全文索引或者配合搜索引擎处理WHERE a 1 OR b 2a、b各有单列索引OR条件无法合并索引改为UNION ALL两个等值查询或建复合索引覆盖两个条件WHERE title a AND content b单靠一个索引无法用多个单列索引做AND过滤改建成复合索引(title, content)或依赖索引合并WHERE user_id IN (1,2,3,...,1000)大IN列表导致优化器放弃索引改临时表JOIN或分段IN后合并结果这张表你在实际排查时直接对照基本能覆盖绝大多数明明有索引却不生效的情况。但要注意这是结果不是原因——你要理解为什么这些写法会让优化器放弃索引而不是机械地背结论。核心逻辑就一句话B树索引依赖列的有序性定位一旦你破坏了列的有序性函数、运算、类型转换定位逻辑就失效了。3.4 索引优化的实测收益一个具体数字去年有个报表查询逻辑很简单查某商家最近30天的订单汇总。原始SQL大概长这样SELECT merchant_id, COUNT(*), SUM(amount) FROM orders WHERE merchant_id 10086 AND created_at 2024-06-01 AND created_at 2024-07-01;表里总共1800万行这条查询跑了2.3秒。执行计划显示type: ALL全表扫描。我当时做了两个调整第一建复合索引(merchant_id, created_at)第二把查询改成覆盖索引形态加上(merchant_id, created_at, amount)让SUM(amount)不用回表。改完之后这条SQL的执行计划从全表扫描变成type: ref扫描行数从1800万降到20万耗时降到60毫秒。当然这种查一条SQL就建一个索引的做法在生产环境要特别小心。你的索引设计要面向整个业务场景而不是单条SQL。同一个orders表上可能同时有十几个不同的查询模式你给这条SQL建了(merchant_id, created_at)可能另一条SQL需要(user_id, status, created_at)两条索引之间会有冗余。所以我在做索引优化时会先把这个表上所有高频查询全部拉出来归纳出几种查询模式然后统一设计索引集合而不是一看到慢SQL就加一个索引。4. SQL改写从写法上砍掉无用功执行计划看完、索引也建到位了如果性能还是不达标那就得回头审视SQL写法本身。很多慢查询的问题不是数据拿不过来而是拿了一堆不需要的数据又做了一堆无谓的计算。4.1 分页深翻页LIMIT的隐形陷阱分页大概是所有业务系统里最容易被忽视的性能杀手。LIMIT 1000000, 20这个写法数据库其实是先扫出第0到第1000020行然后丢掉前100万行只返回最后20行。越往后翻页扫描量越大延迟越高。解决办法有两个主流方案方案一延迟关联。先只查主键再回原表取完整行-- 优化前深分页时巨慢 SELECT * FROM orders ORDER BY created_at DESC LIMIT 1000000, 20; -- 优化后 SELECT o.* FROM orders o INNER JOIN ( SELECT id FROM orders ORDER BY created_at DESC LIMIT 1000000, 20 ) t ON o.id t.id;这个方案在id是主键、排序字段有索引的情况下效果立竿见影。因为子查询里只扫描和排序主键延迟关联后再回表拿20行完整数据而不是对100万行都做回表。方案二游标分页Keyset Pagination。用上一页最后一条记录的排序字段值作为下一页的起点彻底避开OFFSETSELECT * FROM orders WHERE created_at 2024-06-30 23:59:59 -- 上一页最后一条的created_at ORDER BY created_at DESC LIMIT 20;这个方案在数据量千万级以上、分页深度很大时是唯一的正道。它把每次查询的扫描量从深翻页的累计量压缩到固定的一页量。代价是从随机翻页变成了只能前后翻页需要产品侧配合。4.2 关联查询和子查询的取舍很多开发者在写关联查询时有个习惯把所有关联条件全部放在WHERE里让优化器自己判断关联顺序。这在数据量小的时候没问题数据量一上来就失控了——优化器的打算不一定是你的打算。我在这类场景下的经验法则是驱动表外层循环的表选数据量小的那个。MySQL的JOIN本质上是嵌套循环外层表是驱动表内层表每一条都要在外层表里找匹配。如果驱动表有1000条被驱动表有10万条理想情况是先扫1000条再对每条去索引里查总查询次数是1000次索引查找反过来就是10万次完全不是一个量级。能用IN解决的场景尽量少用NOT IN和NOT EXISTS。NOT IN在子查询结果集较大时容易导致优化器全表扫描而NOT EXISTS的执行逻辑是逐行判断很多时候也没好到哪去。如果确实要排除一批数据用LEFT JOIN ... IS NULL往往更可控。我举个例子查最近半年没下过单的用户-- 写法一NOT IN子查询结果集大时容易踩坑 SELECT * FROM users WHERE id NOT IN ( SELECT DISTINCT user_id FROM orders WHERE created_at 2024-01-01 ); -- 写法二LEFT JOIN IS NULL推荐 SELECT u.* FROM users u LEFT JOIN ( SELECT DISTINCT user_id FROM orders WHERE created_at 2024-01-01 ) o ON u.id o.user_id WHERE o.user_id IS NULL;写法二在大部分情况下能让优化器更容易做出正确的执行计划尤其是当曾下单用户表比全量用户表小得多的时候。当然在MySQL 8.0里优化器对NOT IN的处理已经改进很多但我的建议是——能明确写清楚执行意图的情况下不要依赖优化器的智能。执行计划这层兜底你写SQL时就应该把它当做一个必要条件而不是事后补救。4.3 少做计算把计算从SQL里搬出去SQL里的表达式计算是隐形杀手。比如SELECT * FROM orders WHERE TIMESTAMPDIFF(DAY, created_at, NOW()) 7;这条SQL对created_at做了函数运算索引列被函数包裹后无法使用索引每次查询都会全表扫描一遍。改写方式极简单SELECT * FROM orders WHERE created_at NOW() - INTERVAL 7 DAY;区别在哪前者是对每一行的created_at做函数计算后再比较后者是对查询条件的常量做一次运算created_at本身不受影响索引可以正常遍历。凡是你能提前算好的值就不要让数据库在每一行上重复算。同样的道理也适用于日期格式化、字符串拼接、金额计算。我见过有人把金额四舍五入的逻辑写在SQL里导致查询全表扫了一遍——其实写个SELECT ROUND(amount,2) FROM ...没问题但如果你把这个放在WHERE条件里那就是灾难。5. 架构兜底当单条SQL已经无可优化时前面讲的所有手段前提都是单条SQL还有优化空间。但真实业务里经常遇到这种情况SQL已经写得很精炼、索引也建到位了、执行计划已经贴着最优走了响应时间依然是几百毫秒甚至秒级。这时候你得认清楚一件事——问题不在SQL本身而在数据访问架构。5.1 南北流量分离让查询和写入不再互相打架我接手过的一个电商项目订单表日均写入量在30万行左右同时有大量的实时统计查询在跑。典型的矛盾是写事务需要行锁、需要维护索引统计查询需要扫大量行、读很多页。两拨流量挤在同一个实例上谁都不痛快——写的等锁读的被写堵。这类问题的常规解法是读写分离主库负责写从库负责读应用层通过中间件按路由规则把流量拆开。这套方案本身不复杂但有几个细节要做到位才不至于引入新的坑主从延迟要监控到位。业务如果要求读自己刚写入的数据就不能简单地把读流量全丢给从库。我的做法是读请求带上最后写入时间戳如果时间戳距离当前时间小于主从延迟阈值就走主库读否则走从库读。从库数量按读流量增长弹性扩容。每增加一台从库读能力就线性增加但主库的复制压力也会增加所以一般单主库挂2~4台从库比较合理再多要评估半同步复制等方案的收益和代价。读写分离不能替代索引优化。从库上的慢查询和主库一样会拖垮实例你该做的索引优化一条都不能少只是把压力分摊到了不同的机器上。5.2 缓存把重复计算从数据库里抹掉缓存是性价比最高的架构优化手段没有之一。我发现很多团队不是没上缓存而是缓存没设计对——要么缓存粒度过大导致频繁失效要么缓存穿透把数据库打崩。我的一个核心原则缓存的是可重算的数据不是永久的真相。订单金额、商品库存这种强一致要求的数据缓存要极其谨慎但列表页的热门商品、用户画像、行政区划、配置项等缓存可以大胆做。另一个关键设计是缓存粒度。接口粒度的缓存最容易做也最容易出问题——上游数据一变整个缓存都失效流量尖峰瞬间全部打到数据库。我一般会做两级先做技术缓存直接缓存数据库查询结果再做业务缓存在服务层对聚合结果做缓存。前者命中率高后者更贴近业务语义结合使用能覆盖绝大多数场景。缓存穿透是个老生常谈但值得强调的坑。热门商品ID的查询如果商品不存在你缓存里没这个key每次请求都会打到数据库。这时候有两个手段一是对空结果也做短暂缓存比如30秒二是用布隆过滤器在缓存层前置拦截。我一般选前者简单直接后者适合在数据量极大时才需要。5.3 数据量级到天花板后的拆分思路如果单表数据量到了三四千万行以上索引再优化也有天花板——B树再厉害也架不住一个索引要跨越几十GB的存储空间内存放不下每次查找都可能伴随磁盘I/O。这时候的答案不是你继续优化一条SQL而是重新思考数据怎么组织。我的经验是按先垂直拆分再水平拆分的顺序推进。垂直拆分最容易理解把一张宽表拆成多张窄表把不常用的大字段比如商品描述、订单扩展信息拆到独立表里让主表的行宽变窄每页能容纳更多行索引效率自然更高。水平拆分才是解决数据量太大的根本手段。按用户ID哈希分片、按时间分片、按地区分片各有适用场景。订单类数据我通常选按用户ID分片因为订单查询大多以用户为入口流水类数据按时间分片因为这类数据天然有时间属性归档和查询都方便。但水平拆分有一个绕不开的代价跨分片的关联查询和聚合查询会变得极其复杂。所以在做水平拆分之前一定要先确认业务的核心查询模式是否能被分片键覆盖。如果90%的查询都能带分片键那拆分可行如果大量查询需要跨分片折磨的就不是数据库而是你的应用代码了。6. 优化上线后的验证与回归别让优化变成事故文章写到这我特别想强调一个环节也是很多团队容易忽略的环节——优化上线之后你怎么知道这个优化是真的有效而不是把问题转移到了别的地方我以前就干过一件蠢事。为了优化一条统计SQL把一个高频查询改成了物化视图MySQL里对应的是生成列加索引结果查询是快了但物化视图的刷新逻辑和业务数据产生了不一致第二天报表数据对不上被业务方追着问了一天。这个问题的根源就在于我做完优化之后没有做回归验证直接按SQL变快了来评判忽略了数据一致性这个隐性指标。所以我现在的做法是任何查询优化上线前后都要做三个维度的对照验证第一个维度是响应时间。这个最直观但不要只看平均值要看P95和P99。平均值很容易被少数慢请求拉平你看平均值觉得500毫秒优化到了200毫秒不错但P99可能还是2秒——说明有少部分查询依然很慢这往往和索引失效、缓存淘汰、数据倾斜有关。第二个维度是扫描行数和吞吐量。这个要回到执行计划里看优化前后的rows预估和实际扫描量有没有量级的变化。如果响应时间降了但扫描行数没变说明你只是改善了I/O调度或者偶然的缓存命中真正的瓶颈还没解决换个时间点可能又慢回去。第三个维度是资源消耗。优化后的查询如果从扫全表变成了走索引通常意味着CPU和I/O都会下降但如果你用了复杂的临时表关联、覆盖索引字段过多写入侧的负担可能反而上升。所以一定要看优化前后整个数据库实例的QPS、CPU、磁盘I/O趋势确认没有把压力从读侧转移到写侧。提示线上回归验证要选在流量低峰期做灰度对比。先把新SQL部署给10%的流量观察15分钟确认P99、慢查询数、锁等待三个指标没有恶化再逐步放量到50%、100%。不要一上来全量替换数据一致性出问题时全量替换意味着全量故障。我在实践里还养成了一个习惯每次做完查询优化都会在文档里记录优化前后的执行计划、扫描行数、P99延迟、样本SQL以及上线后的监控截图。这个习惯看起来费时间但半年后再回头看价值非常大——它能帮你快速定位是不是之前的某个优化导致现在的某个问题也不用对着线上Redis缓存瞎猜。看到这里你可能也发现了整个优化的链路千头万绪但核心逻辑永远是一样的先定位钱花在哪再决定怎么省。执行计划告诉你钱花在哪索引和SQL改写告诉你省钱的技巧缓存和拆分告诉你省钱的路线。把这四件事串起来一条慢查询从发现到解决通常不会超过一两个小时。最后再分享一个我长期坚持的细节我优化完任何一条SQL都会顺手把原始版本和执行计划存到团队的慢查询知识库里标注为什么慢、怎么改的、收益多少。这不是什么高深的技术但每次新人来拿起这本册子就能少踩我当年踩过的坑——可能这才是实战两个字最值钱的地方吧。