从索引策略到查询性能飞跃：SQL优化实战全解析

接手过一个让人头疼的性能工单：业务方反馈某个查询在数据量涨到百万级之后，响应时间从几十毫秒直接飙升到十几秒，前端超时，接口报警刷屏。第一反应以为是数据库负载问题，结果一查，表结构里有索引，但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 ... \G

1.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`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; 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`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

业务方反馈的慢查询是：

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表跑EXPLAIN：

EXPLAIN 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);

再次执行EXPLAIN：

type: range key: idx_status_created rows: 182340 Extra: Using index condition; Using filesort

type从ALL变成range，rows从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 index

filesort消失了。因为整个索引遍历过程，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和id：

SELECT * 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 profile

EXPLAIN能看出执行计划，但一条查询具体耗费在哪里——是磁盘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优化做完之后，对照着自查一遍，能省掉不少在线事故。

我自己还有一个“土办法”：每条上线SQL，手工在草稿纸上写出它可能的执行路径——从哪个索引进入，经过哪些过滤，最终返回多少行。写不清楚的，说明对执行计划理解不透，这条SQL就不急着上线。

7. 关于优化效果能持续多长时间的一点个人体会

每次我把慢查询优化到几百毫秒，团队都会觉得大功告成。但数据量的增长、业务字段的扩展、并发请求的波动，都会让“今天很快”变成“明天又慢”。所以我在完成一轮优化后，总会在SQL里留下标准EXPLAIN基线，并且定时巡检慢查询日志。一旦发现某条曾经优化过的SQL重新出现在慢日志里，第一反应不是重新加索引，而是去看数据分布是否发生了变化、新业务是否引入了不同的过滤条件。

内存里的索引缓冲和磁盘I/O之间始终存在博弈，有时连sql_no_cache影响都能让压测结果相差数倍。我会告诉团队：SQL优化不是一锤子买卖，而是靠清晰的执行路径认知和持续的监控反馈，把性能维持在可预测的范围内。这是我做数据库性能工作这么久以来，最想分享的一点心得。