接手过一个让人头疼的性能工单业务方反馈某个查询在数据量涨到百万级之后响应时间从几十毫秒直接飙升到十几秒前端超时接口报警刷屏。第一反应以为是数据库负载问题结果一查表结构里有索引但SQL写法绕过了索引整条查询走了全表扫描。那次排查让我把索引策略和SQL写法这两件事彻底串起来了——索引不是建了就完事SQL怎么写、索引怎么设计、优化器怎么选三者咬合在一起才能把性能真正提上去。这篇文章就围绕“从索引策略到查询性能飞跃”这条主线完整拆解一次SQL优化的实操过程。内容覆盖执行计划解读、索引设计原理、复合索引与覆盖索引的取舍、SQL改写技巧、慢查询排查链路以及一整套可以直接复用的避坑清单。适合后端开发、数据分析师、DBA 以及所有被慢查询折磨过的人照着做基本能覆盖日常90%以上的SQL性能问题。1. 先从执行计划说起慢查询到底慢在哪很多同学一上来就想着“加索引”但索引加在哪里、加什么类型、怎么验证效果全凭感觉。这种做法的结果就是索引建了一堆查询还是慢写入反而被拖垮。我在处理性能问题时的第一步永远是看执行计划搞清楚查询到底是怎么走的慢在哪个环节。1.1 EXPLAIN是性能分析的入口MySQL里最常用的工具就是EXPLAIN它能把优化器选择的执行路径完整展示出来。拿到一条慢SQL第一时间执行EXPLAIN SELECT u.id, u.nickname, o.order_no, o.amount FROM orders o JOIN users u ON o.user_id u.id WHERE o.status 1 AND o.created_at 2024-01-01 ORDER BY o.created_at DESC LIMIT 20;重点关注几个字段type、key、rows、Extra。type访问类型从好到差大致是system const eq_ref ref range index ALL。如果看到ALL意味着全表扫描数据量一大必炸。key实际用到的索引。如果为NULL说明没走任何索引。rows优化器预估扫描的行数。这个数字越大查询成本越高。Extra包含很多重要信号比如Using filesort代表文件排序Using temporary代表临时表Using index代表覆盖索引Using where代表存储引擎层返回后还要过滤。我还习惯在EXPLAIN后面加一个\G竖排输出字段多的时候看起来更清楚。EXPLAIN SELECT ... \G1.2 type字段是性能的第一风向标我在评估一条查询能不能上线时第一眼看type。如果是ALL或index且表数据量超过十万基本可以直接打回。举个实际例子某次排查一个报表查询执行计划显示两张千万级大表做连接驱动表type是ALL被驱动表type是ref整体耗时18秒。后来把驱动表的过滤条件加上索引type从ALL变成range耗时就降到了300毫秒。type为range时还有个容易被忽略的点rows预估值虽然不高但实际扫描的页可能很多。这时候要看filtered字段它表示存储引擎返回的数据中经过WHERE条件过滤后剩余的比例。如果filtered很低说明索引虽然缩小了范围但大量数据仍然要回表过滤这时候覆盖索引的价值就体现出来了。我一直建议团队里的开发同学养成一个习惯所有涉及线上SQL的评审都附上EXPLAIN结果截图type低于range的一律不许上生产。这不是苛刻而是吃过太多亏之后总结出来的硬规矩。2. 索引策略设计为什么索引建了还是慢很多人有个误解只要WHERE条件里有索引列查询就一定快。真实情况远没那么简单索引是否生效、生效到什么程度取决于索引的结构设计、字段选择、查询写法三者是否匹配。2.1 B树索引是怎么加速查询的MySQL的InnoDB引擎默认使用B树索引。B树的特点是数据只存放在叶子节点非叶子节点只存索引键值每个叶子节点通过指针相连形成有序链表。这种结构让范围查询非常高效——找到起点之后沿着链表顺序读取就行不需要每次都从根节点重新遍历。举个例子假设有一张订单表按照user_id建了索引ALTER TABLE orders ADD INDEX idx_user_id (user_id);执行SELECT * FROM orders WHERE user_id 12345时过程是这样的从B树的根节点开始逐层比较键值定位到user_id 12345所在的叶子节点。顺着叶子节点的链表向后扫描直到遇到第一个不等于12345的记录。每取到一条索引记录通过记录中的主键回表取出一整行数据。注意最后一步回表。如果user_id 12345的订单有1000条就需要回表1000次。如果这1000行数据在磁盘上分散在不同页面每次回表都是一次随机I/O。这就是为什么有时候明明走了索引查询依然不快的原因。我在实战中解决这个问题的方法很简单用覆盖索引让索引本身包含查询需要的所有列省掉回表。2.2 复合索引设计顺序和列的选择是核心单列索引能解决的问题有限大多数真实查询的过滤条件是多个维度组合的。这时候需要复合索引也就是多个字段组合成一个索引。复合索引遵循“最左前缀原则”。比如建立一个复合索引ALTER TABLE orders ADD INDEX idx_status_created (status, created_at);那么这个索引可以匹配status单独过滤、status created_at组合过滤但无法匹配只带着created_at的查询。原因在于B树的节点排序规则先按第一个字段排序再按第二个字段排序第三个依次类推。设计复合索引时我遵循的核心原则是“等值条件排前面范围条件排后面”。举个例子业务查询是SELECT * FROM orders WHERE status 1 AND created_at BETWEEN 2024-01-01 AND 2024-01-31 AND channel app;status和channel都是等值条件created_at是范围条件。那么索引设计应该是ALTER TABLE orders ADD INDEX idx_status_channel_created (status, channel, created_at);为什么把范围条件放在最后因为B树一旦遇到范围查找范围内的数据在索引中可能分布在多个相邻节点后面的字段就没法继续参与索引定位了。等值条件放在前面能最大程度缩小检索范围范围条件放到后面至少前面的等值条件已经被精确约束住了。2.3 覆盖索引与回表的取舍覆盖索引指的是索引本身包含了查询需要的所有列查询过程中不需要回表就能拿到结果。它是消除随机I/O最直接的手段。举一个我经历过的例子。某个积分明细查询表里有500万行查询条件是对member_id过滤然后按created_at排序分页SELECT id, member_id, points, created_at FROM points_detail WHERE member_id 888888 ORDER BY created_at DESC LIMIT 20;原始索引只有idx_member_id (member_id)。执行计划显示type是ref但Extra里有Using filesort并且每条记录都要回表取created_at、points整体耗时约1.8秒。调整方案是把索引改成覆盖索引ALTER TABLE points_detail ADD INDEX idx_member_created (member_id, created_at, points, id);修改后执行计划Extra变成了Using index; Using filesort虽然filesort还在但数据量范围从几十万缩小到了几千条排序开销骤降查询耗时降到40毫秒。这里说明一下为什么Using filesort还会出现ORDER BY created_at的顺序跟索引中的排序方向不完全匹配或者排序字段没在索引最前面的列上位。最理想的情况是索引顺序恰好满足排序方向连filesort都省掉如果做不到也要尽量让参与排序的数据量足够小。2.4 索引维护成本别让索引本身成为负资产索引不是免费的。每建一个索引写入数据时就要多维护一棵B树多了存储空间开销和写入开销。我在实际项目中见过一张表建了12个索引结果写入性能下降了将近50%。一个实用的建议是用SHOW INDEX FROM table_name查看索引使用情况同时开启performance_schema统计未使用索引SELECT OBJECT_NAME, INDEX_NAME, COUNT_STAR, COUNT_READ, COUNT_WRITE FROM performance_schema.table_io_waits_summary_by_index_usage WHERE OBJECT_SCHEMA your_db ORDER BY COUNT_READ ASC;对COUNT_READ长期为0的索引基本可以判定为冗余索引考虑删除。线上环境做索引变更前务必在测试环境跑一遍压测验证读写性能的影响。3. SQL优化实战从一条慢查询到性能飞跃的完整过程讲完了原理进入正题。这里用一个虚构的电商订单场景“某商城订单库”来做完整复盘。订单表和用户表演示如下字段做了简化CREATE TABLE users ( id int(11) NOT NULL AUTO_INCREMENT, nickname varchar(32) DEFAULT NULL, phone varchar(20) DEFAULT NULL, reg_time datetime DEFAULT NULL, PRIMARY KEY (id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; CREATE TABLE orders ( id int(11) NOT NULL AUTO_INCREMENT, order_no varchar(32) DEFAULT NULL, user_id int(11) DEFAULT NULL, status tinyint(4) DEFAULT NULL, amount decimal(10,2) DEFAULT NULL, created_at datetime DEFAULT NULL, PRIMARY KEY (id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;业务方反馈的慢查询是SELECT u.nickname, o.order_no, o.amount FROM orders o JOIN users u ON o.user_id u.id WHERE o.status 1 AND o.created_at 2024-01-01 AND o.created_at 2024-02-01 ORDER BY o.created_at DESC LIMIT 20;数据量订单表约800万行用户表约200万行。查询目的是获取某月份待支付订单的最近20条并展示用户昵称。第一次执行耗时12.8秒。3.1 性能瓶颈定位第一步单独对orders表跑EXPLAINEXPLAIN SELECT * FROM orders WHERE status 1 AND created_at 2024-01-01 AND created_at 2024-02-01 ORDER BY created_at DESC LIMIT 20 \G输出关键信息possible_keys: NULL type: ALL rows: 4824100 Extra: Using where; Using filesort问题一目了然没有可用索引全表扫描预估需要扫480万行还要做文件排序。第二步查看orders表现有索引SHOW INDEX FROM orders;只有主键索引没有任何普通索引。这种情况慢是必然的谈不上优化器选择问题。3.2 索引设计方案根据“等值条件在前、范围条件在后”的原则第一个候选索引是(status, created_at)。等值条件status 1放前面范围条件created_at放后面。ORDER BY字段正好是created_at跟索引顺序一致有机会消除filesort。建索引ALTER TABLE orders ADD INDEX idx_status_created (status, created_at);再次执行EXPLAINtype: range key: idx_status_created rows: 182340 Extra: Using index condition; Using filesorttype从ALL变成rangerows从480万降到18万。但Extra还是有Using filesort这说明了什么说明虽然排序字段在复合索引上但范围条件下索引的B树遍历顺序不能完美覆盖“按created_at倒序”的需求优化器还是选择了额外的排序。执行耗时从12.8秒降到2.3秒有改善但还远远不够。3.3 覆盖索引进一步压榨性能当前查询需要的列是o.order_no、o.amount、o.created_at如果再把这些列加进索引就能避免回表。调整索引ALTER TABLE orders DROP INDEX idx_status_created, ADD INDEX idx_status_created_covering (status, created_at, order_no, amount);这里我把order_no、amount也放进了索引。有人会问为什么不加user_id因为user_id在JOIN阶段用于匹配已经充当了关联条件而索引的主要任务是先缩小status created_at范围。把order_no、amount加进去是为了让这一步查询完全在索引里完成不走回表。执行计划变成type: range key: idx_status_created_covering rows: 182340 Extra: Using where; Using index; Using filesort多了Using index说明这一步已经通过覆盖索引拿到了所有必要数据。耗时从2.3秒降到1.1秒。3.4 消除filesort让排序字段做索引的排头兵Using filesort还在说明排序开销依然存在。在某些高频查询场景下可以从检索和排序角度重新设计索引。ORDER BY的字段是created_at DESC范围筛选字段也是created_at如果索引只有这一列范围扫描后的数据天然是有序的就能省掉排序。但业务还有status 1这个过滤条件。只建created_at索引会导致status的过滤靠回表后的where完成数据量太大时也不划算。如何取舍我当时的做法是既然status 1的值只有少数几个而created_at在1月份内只有30天的范围把status换成普通等值过滤让索引以created_at为第一列排头ALTER TABLE orders ADD INDEX idx_created_status (created_at, status, order_no, amount);此时SQL改写为SELECT u.nickname, o.order_no, o.amount FROM orders o JOIN users u ON o.user_id u.id WHERE o.created_at 2024-01-01 AND o.created_at 2024-02-01 AND o.status 1 ORDER BY o.created_at DESC LIMIT 20;执行计划中Extra变成Using where; Using indexfilesort消失了。因为整个索引遍历过程created_at先按物理顺序定位到1月份区间内的第一个值然后向后扫描每个叶子节点上created_at天然是从小到大的。ORDER BYDESC相当于倒序读取索引节点不需要额外排序。最终耗时降到420毫秒相比最初的12.8秒性能提升了30倍。3.5 JOIN的优化对驱动表讲点“道理”主查询提速后大头解决了但还有一个细节JOIN执行时谁做驱动表在orders表扫出20条后需要关联users表取nickname。MySQL的优化器倾向于用小表驱动大表但这里需要考虑一个核心问题如果users.id是主键每次按主键逐行查找20次开销很小但如果users表的数据量过大且id不是主键查找成本就会放大。实际场景里users.id是主键走eq_ref这已经是最优路径了。可以进一步微调的是加一个STRAIGHT_JOIN关键字强制指定驱动顺序SELECT u.nickname, o.order_no, o.amount FROM orders o STRAIGHT_JOIN users u ON u.id o.user_id WHERE o.created_at 2024-01-01 AND o.created_at 2024-02-01 AND o.status 1 ORDER BY o.created_at DESC LIMIT 20;不过这里的收益可以忽略不计MySQL自动选择已经足够好。我不建议随手加STRAIGHT_JOIN除非在EXPLAIN中明确看到驱动表选择错误再动这个手。4. SQL写法层面的陷阱避开那些让索引失效的写法跑通上面的案例之后很多读者会以为“不就是加索引嘛”。但我在真实工单里百分之三四十的慢查询表上明明有合适的索引却因为SQL写法的问题让索引失效。这一章把最常见的坑全部列出来。4.1 隐式类型转换索引列被函数加工最经典的写法是SELECT * FROM users WHERE phone 13800138000;phone字段如果是varchar类型等号右边是整数MySQL会把phone列隐式转换为数字再比较等于对索引列套了函数索引直接失效。还有一类是日期字段的处理SELECT * FROM orders WHERE DATE(created_at) 2024-01-15;对created_at套了DATE()函数索引列被表达式包裹B树无法利用有序键值定位。正确的写法是范围查询SELECT * FROM orders WHERE created_at 2024-01-15 00:00:00 AND created_at 2024-01-16 00:00:00;有人觉得这么写麻烦我就用一句话提醒他们“索引列单独放在比较运算符左边右边随便怎么折腾。”4.2 前导模糊匹配%开头就是全表扫描SELECT * FROM users WHERE nickname LIKE %张三%;这个查询条件无法利用B树索引因为模糊匹配的开头是通配符优化器无法确定该从哪个索引节点开始扫描。改成LIKE 张三%就能走索引。如果业务确实需要中间匹配建议引入全文索引或搜索引擎而不是硬扛LIKE。4.3 OR条件拆解每个分支都得走索引SELECT * FROM orders WHERE status 1 OR channel app;即使status和channel各有单列索引MySQL对OR的处理通常是将两边分别走索引后合并再回表取数据。如果两边数据范围都不小合并和回表就会很慢。更麻烦的是如果某一边没有索引整条查询就会退化成全表扫描。碰到多OR条件我通常先评估能否拆成两个查询用UNION ALL或者改成IN条件SELECT * FROM orders WHERE status IN (1, 2);从优化器视角来看IN的等价性更好能够在一个索引范围内完成扫描。4.4 排序和分组没有索引就是灾难ORDER BY和GROUP BY不走索引时MySQL会生成临时表或文件排序。对百万级数据量来说文件排序的开销很可能超过全表扫本身。一个常用优化手段是让索引顺序与分组排序字段一致。比如SELECT status, COUNT(*) FROM orders GROUP BY status;如果status有索引分组统计可以顺着索引扫描直接完成。如果GROUP BY status后面还带着ORDER BY created_at则需要考虑是否能通过复合索引同时覆盖两个字段的顺序需求。4.5 分页过深limit偏移量是性能杀手分页查询越到后面越慢是大家最容易忽视的问题SELECT * FROM orders WHERE status 1 ORDER BY created_at DESC LIMIT 100000, 20;LIMIT 100000,20 意味着MySQL需要读取和丢弃前100000条记录然后再取20条。即使全程走索引这部分倒腾也需要大量I/O。实战优化手段有两个延迟关联、基于游标的分页。延迟关联的核心是先在索引里完成排序和偏移再用主键回表取整行SELECT * FROM orders JOIN ( SELECT id FROM orders WHERE status 1 ORDER BY created_at DESC LIMIT 100000, 20 ) AS tmp ON orders.id tmp.id;游标分页更高效适合客户端传入上一页最后一条的created_at和idSELECT * FROM orders WHERE status 1 AND (created_at 2024-01-20 10:00:00 OR (created_at 2024-01-20 10:00:00 AND id 50000)) ORDER BY created_at DESC, id DESC LIMIT 20;这里带上id是为了处理同一时刻多条数据时的稳定性排序唯一且不丢数据。5. 真实的排查链路慢查询日志、explain与show profile组合拳很多开发者在线上遇到慢SQL时只会说“数据库好慢”然后开始盲目加索引。实际上一套成熟的排查链路应该用工具把问题固定下来再做针对性优化。5.1 慢查询日志与pt-query-digest首先确认慢查询日志已经开启SHOW VARIABLES LIKE slow_query_log; SHOW VARIABLES LIKE long_query_time;long_query_time默认是10秒线上建议设成1秒抓更细的问题。开启后SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1;慢日志是文本文件行数一多人工分析效率就低。我常用pt-query-digest做汇总分析。它会按“查询指纹”去重统计每个模板的执行次数、平均耗时、总耗时占比一屏就能看出哪类查询是主要矛盾。某次优化中通过pt-query-digest发现某条模板只占了全部查询的3%却贡献了67%的耗时立刻定位到了罪魁祸首。5.2 拿到全生命周期拆解show profileEXPLAIN能看出执行计划但一条查询具体耗费在哪里——是磁盘I/O、排序还是网络传输show profile能给出更细的拆解。-- 开启 SET profiling 1; -- 执行目标SQL SELECT ...; -- 查看所有查询的profile列表 SHOW PROFILES; -- 查看指定Query_ID的详细耗时分布 SHOW PROFILE CPU, BLOCK IO, SWAPS FOR QUERY 10;输出里能看到Sending data、Sorting result、Creating sort index等阶段耗时。如果Sorting result占比高就说明问题出在filesort上如果Sending data偏高可能是数据量大或I/O瓶颈。5.3 对比试验改前改后的量化验证优化方案上线前我习惯先在同一台测试数据库上分别记录旧SQL和新SQL在同样数据量下的执行时间、扫描行数、耗时分布。执行前先RESET QUERY CACHE或FLUSH STATUS避免缓存带来的假象。压测脚本可以用简单的shell循环for i in $(seq 1 20); do mysql -h127.0.0.1 -uuser -p*** your_db -e SELECT ... | grep rows in set done取稳定中位数而不是单次最优值。数据库负载波动不可避免多跑几次取中位数更靠谱。6. 常见问题速查表上线前把这些问题过一遍把日常踩过的坑整理成下面的速查表SQL优化做完之后对照着自查一遍能省掉不少在线事故。问题现象可能原因解决方案检查项type为ALL无条件过滤或索引缺失确认WHERE条件列是否有合适索引检查过滤条件是不是可空列有索引但key为NULL隐式类型转换、函数套列检查索引列是否单独处于比较符号左侧Extra出现Using filesort排序字段不在索引中或顺序不匹配设计索引时把排序字段纳入索引方向要一致Extra出现Using temporaryGROUP BY或DISTINCT字段无索引给分组字段加索引避免临时表分页越深越慢LIMIT偏移量大扫描大量无用行延迟关联或改用游标分页JOIN性能差驱动表选择错误或被驱动表无索引小表驱动大表被驱动表连接列建索引索引数量膨胀写入变慢冗余索引、低效索引过多用performance_schema统计使用次数清理长期未命中的索引字符集不一致导致隐式转换表或列的字符集不同JOIN时无法利用索引统一表字符集比如全部为utf8mb4我自己还有一个“土办法”每条上线SQL手工在草稿纸上写出它可能的执行路径——从哪个索引进入经过哪些过滤最终返回多少行。写不清楚的说明对执行计划理解不透这条SQL就不急着上线。7. 关于优化效果能持续多长时间的一点个人体会每次我把慢查询优化到几百毫秒团队都会觉得大功告成。但数据量的增长、业务字段的扩展、并发请求的波动都会让“今天很快”变成“明天又慢”。所以我在完成一轮优化后总会在SQL里留下标准EXPLAIN基线并且定时巡检慢查询日志。一旦发现某条曾经优化过的SQL重新出现在慢日志里第一反应不是重新加索引而是去看数据分布是否发生了变化、新业务是否引入了不同的过滤条件。内存里的索引缓冲和磁盘I/O之间始终存在博弈有时连sql_no_cache影响都能让压测结果相差数倍。我会告诉团队SQL优化不是一锤子买卖而是靠清晰的执行路径认知和持续的监控反馈把性能维持在可预测的范围内。这是我做数据库性能工作这么久以来最想分享的一点心得。