企业官网建设流程全解析

上周帮同事排查一条线上慢查询，单表三千万行，查询条件就两个字段，联合索引也建了，结果跑了 2.8 秒才返回。打开执行计划一看，type是ALL，索引根本没进去。这种情况我见过太多次了。

很多人对 MySQL 索引优化的理解停留在"给 where 条件加个索引"，但真正上手练习就会发现，索引优化第一步不是加索引，而是学会看懂一条 SQL 到底是怎么执行的，以及优化器为什么做了这个选择。这篇文章我用自己的练习过程来拆解：从造表、造数据，到 explain 逐字段分析，再到六个典型索引失效场景的复现与修复，最后串一条完整的线上排查链路。内容偏实战，适合已经会写基础 SQL、想系统提升索引优化能力的后端开发和 DBA 阅读。

1. 先别急着建索引：慢查询到底慢在哪

1.1 一条慢查询的真实场景还原

假设现在有个订单查询接口，我接到的问题是"按用户查最近订单很慢"。表结构简化后长这样：

CREATE TABLE `order_info` ( `id` bigint NOT NULL AUTO_INCREMENT, `user_id` bigint NOT NULL, `order_no` varchar(32) NOT NULL, `amount` decimal(10,2) NOT NULL, `status` tinyint NOT NULL DEFAULT '0', `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `idx_user_id` (`user_id`) ) ENGINE=InnoDB;

业务 SQL 是这样的：

SELECT order_no, amount, status, create_time FROM order_info WHERE user_id = 10234567 AND status IN (1, 2, 5) ORDER BY create_time DESC LIMIT 20;

user_id上有索引idx_user_id，但 explain 的结果里type = ALL，rows 估算三千万，全表扫描。

为什么有索引却不走？核心原因在于：MySQL 优化器会估算各条执行路径的代价，当它认为"走二级索引回表 + 排序"的成本比"直接全表扫"更高时，就会放弃索引。user_id = 10234567能过滤掉大部分行，但status IN条件其实过滤能力很弱，加上ORDER BY create_time还需要回表后排序，优化器最终选择了全表扫描。

这里有个反直觉的点：即使user_id的选择性很好，优化器也可能不走索引。因为二级索引里只有user_id和主键，而我们需要order_no、amount、status、create_time，必须回聚簇索引取完整行，回表一多，成本就上去了。

1.2 先排除索引之外的干扰因素

在上手加索引之前，我通常会按下面的顺序排查，避免在错误方向上浪费时间：

1. 打开slow_query_log，确认这条 SQL 是不是稳定慢，而不是偶发的网络抖动或锁等待。
2. 用SHOW ENGINE INNODB STATUS看看有没有长时间未提交的事务持有行锁，否则慢的根源可能是锁而不是索引。
3. 确认统计信息是否过期：ANALYZE TABLE order_info;之后再跑一次 explain，很多时候只是统计信息不准导致优化器选错。
4. 最后才是分析执行计划、调整索引。

提示：第四步之前的三步经常被人跳过。我在实践中遇到过多次"加索引没效果"的假象，最后发现是另一个会话长期占着锁，索引压根没机会派上用场。先排除等待类问题，再谈扫描类问题，顺序不能反。

1.3 索引不是越多越好：先建立成本意识

同一个表上，每个索引都意味着额外的存储空间、写入时的维护成本，以及优化器在选择路径时面临的复杂度。生产环境更常见的问题不是"没索引"，而是"索引太多，优化器选错"。

我有一次接手过一张表，上面挂了 11 个索引，其中三个完全重复，结果每次 Insert 都要同步维护一大堆二级索引，写入性能被拖得很惨。删掉冗余索引之后，写入延迟直接降了三分之一。

所以在练习索引优化的时候，每加一个索引之前都问自己三个问题：

• 这个索引覆盖了哪些高频查询？
• 是否已有索引能通过调整字段顺序达到同样效果？
• 写入压力和存储成本能不能承受？

带着这种成本意识再开始造表，后面的练习才有意义。

2. 造一张百万行测试表，把索引优化变成可复现的练习

2.1 表结构设计：模拟真实业务字段

练习用的表不能太简单，字段最好覆盖常见的查询条件：数字、字符串、状态值、日期。我用一张简化的会员订单表：

CREATE TABLE `member_order` ( `id` bigint NOT NULL AUTO_INCREMENT, `member_id` bigint NOT NULL, `order_no` varchar(32) NOT NULL, `status` tinyint NOT NULL DEFAULT '0', `amount` decimal(10,2) NOT NULL DEFAULT '0.00', `channel` varchar(16) DEFAULT NULL, `remark` varchar(255) DEFAULT NULL, `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE KEY `uk_order_no` (`order_no`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

字段设计有几个刻意安排：

• order_no是唯一键，用来演示等值查询和隐式类型转换。
• member_id是高频查询字段，用来演示普通二级索引。
• status和channel是低选择性字段，用来演示"建了索引也可能不走"的情况。
• create_time用来演示范围查询和排序。

2.2 用存储过程批量造数据

测试环境用 MySQL 8.0，插入 100 万行数据只需要一个存储过程：

DROP PROCEDURE IF EXISTS insert_member_order; DELIMITER $$ CREATE PROCEDURE insert_member_order() BEGIN DECLARE i INT DEFAULT 1; SET AUTOCOMMIT = 0; WHILE i <= 1000000 DO INSERT INTO member_order (member_id, order_no, status, amount, channel, create_time) VALUES ( FLOOR(RAND() * 10000) + 1, CONCAT('NO', LPAD(i, 10, '0')), FLOOR(RAND() * 8), ROUND(RAND() * 1000, 2), ELT(FLOOR(RAND() * 3) + 1, 'app', 'web', 'h5'), NOW() - INTERVAL FLOOR(RAND() * 365) DAY ); IF i % 5000 = 0 THEN COMMIT; END IF; SET i = i + 1; END WHILE; COMMIT; END$$ DELIMITER ; CALL insert_member_order();

存储过程里我刻意让member_id的分布只有 10000 个用户，平均每个用户 100 条订单。这样模拟了真实系统的"长尾分布"。

2.3 数据倾斜：让练习更接近真实世界

如果每个用户只有几条订单，加索引效果会特别明显，练起来不够有挑战。我建议在造数完成后单独制造几个"大用户"：

UPDATE member_order SET member_id = 88888 WHERE id % 100 = 0;

这样用户 88888 大约有 1 万条订单，是普通用户的 100 倍。后面练习时用这个热点用户跑查询，很容易复现"索引在某些数据分布下失效"的现象。索引优化练习里最容易出的问题，就是用均匀分布的数据得出乐观结论，真实业务的数据分布几乎一定倾斜。

3. Explain输出逐字段拆解：哪些数字是骗人的，哪些才是关键

3.1 type字段：从ALL到const的代价阶梯

执行EXPLAIN SELECT * FROM member_order WHERE member_id = 123;，返回结果的type字段决定了访问类型。我整理了常见取值，按性能从差到好排：

练习时最需要警惕ALL和index。index虽然名字里带"index"，但它是"遍历了整棵索引树"，数据量一大一样慢。真正称得上"用好了索引"的，至少要到range级别。

3.2 rows、filtered 与真实扫描量

rows是优化器估算需要读取的行数，filtered是经过条件过滤后剩余行数的百分比。这两个数字经常骗人：

• rows依赖统计信息，不精确。统计信息过旧时，误差可能到 10 倍以上。
• filtered在单表查询里意义有限，在多表 JOIN 时更能反映优化器的判断。
• 我会把这两个值相乘，得到优化器眼里"最终可能返回的行数"，再对比实际数据量。

我在练习时有个习惯：explain 看完之后，用SELECT COUNT(*)实际跑一遍，对比 explain 里的估算值。比如 explain 说 rows = 3200，实际 count 只有 45，那这个执行计划不能全信，先ANALYZE TABLE member_order;刷新统计信息再说。

3.3 Extra列：Using index到底意味着什么

Extra列里出现频率最高的几个值，我总结成三档：

• Using index：最优档，查询所需列都在索引树上，不需要回表。
• Using index condition：用到索引下推了，部分过滤在索引层完成，但仍可能回表。
• Using where; Using filesort：基本等于说"索引没完全走通，排序还得额外做"。

Using filesort并不代表一定在磁盘上排序，内存排序也叫filesort。但它的出现意味着排序没有利用索引顺序，需要额外付出排序成本。ORDER BY create_time DESC在大数据量下出现Using filesort，基本就要考虑通过调整索引来消除。

3.4 一个常见误区：key不为空不代表索引用好了

再看一种执行计划：

type: ref key: idx_member_status rows: 520000 Extra: Using index condition

key不为空，但 rows 显示 52 万行。原因往往是联合索引只用了最左列，后面的字段没参与过滤。这种"用了一半的索引"比不用更麻烦，它给了你优化的错觉。

所以在练习时，我每次看执行计划都是三个列一起看：type、key、rows。只盯key是最容易自欺欺人的做法。理想状态应该是type尽量靠前、key对应正确索引、rows接近实际返回行数，三者同时满足才算真正用好了。

4. 索引失效现场：六个典型场景的实测与修复

这一章是全文最核心的部分，我逐个场景给出复现 SQL、执行计划表现和修复方案。这些场景在实际工作中反复出现，值得一个接一个亲手做一遍。

4.1 对索引列使用函数

常见写法：

SELECT * FROM member_order WHERE DATE(create_time) = '2024-05-01';

explain 结果：type = ALL，索引idx_create_time没被使用。原因很直白：优化器无法对DATE(create_time)做范围匹配，因为函数改变了列在索引里的原始顺序。

修复方案有两种：

-- 方案一：范围写法，对索引友好 SELECT * FROM member_order WHERE create_time >= '2024-05-01 00:00:00' AND create_time < '2024-05-02 00:00:00'; -- 方案二：函数索引（MySQL 8.0.13+ 可用） ALTER TABLE member_order ADD INDEX idx_create_date ((DATE(create_time)));

我优先用方案一。函数索引虽然解决问题，但会让优化器的选择空间变复杂，版本兼容性也有限。能用原列范围写清楚的条件，就不要引入额外机制。

4.2 隐式类型转换

表里order_no是 varchar，如果查询写成数字：

SELECT * FROM member_order WHERE order_no = 1234567890;

MySQL 会把字符串列转换成数字再比较，索引列上发生了隐式转换，uk_order_no失效，执行计划变成ALL。

识别方法：explain 里key是空的，但Extra出现Using where，说明条件是在回表之后过滤的。修复很粗暴，SQL 里给值加上引号：

SELECT * FROM member_order WHERE order_no = '1234567890';

4.3 联合索引的最左前缀

联合索引(member_id, status, create_time)，下面三条 SQL 的索引用法完全不同：

-- 用得上，member_id 是最左列 WHERE member_id = 5 AND status = 1; -- 用得上，但只能用到 member_id 和 status WHERE member_id = 5 AND status = 1 AND create_time > '2024-01-01'; -- 用不上，直接跳过了最左列 WHERE status = 1 AND create_time > '2024-01-01';

第三条是练习里最常见的错误认知：以为"索引里有的字段都写上就完事"。其实联合索引的字段顺序就是匹配顺序，跳过了最左列，整个索引都失效。

修复思路是调整索引字段顺序：最常作为等值条件的字段排前面，范围字段放最后。比如上面的第三条查询，如果业务上高频按status和create_time查，就应该单独建(status, create_time)索引，而不是指望(member_id, status, create_time)覆盖。

4.4 LIKE前缀模糊查询

SELECT * FROM member_order WHERE order_no LIKE '%202405%';

前缀模糊查询用不了普通 B+ 树索引的有序性，uk_order_no直接失效。注意LIKE '2024%'是可以走索引的，只有%在前面的写法对索引不友好。

业务上如果非要前缀模糊搜索，我一般分两个方向处理：

• 改成前缀匹配LIKE '202405%'，配合order_no索引使用。
• 如果场景是真正的全文检索，建议引入专职的全文检索引擎，而不是在 MySQL 里死磕 B+ 树。

4.5 OR条件串联

SELECT * FROM member_order WHERE member_id = 5 OR order_no = 'NO0000000001';

MySQL 对 OR 的处理常常是"两边条件都要访问"，如果其中一个条件没有有效索引，就可能退化成全表扫描。虽然member_id有索引，order_no也有唯一索引，但优化器在 OR 场景下未必能同时利用两棵索引树。

保险的做法是把 OR 改写为 UNION：

SELECT * FROM member_order WHERE member_id = 5 UNION SELECT * FROM member_order WHERE order_no = 'NO0000000001';

不过改写前先看执行计划，因为 MySQL 8.0 对 OR 的优化能力比老版本强很多，不是所有 OR 都必需改写。

4.6 范围条件后面的字段失效

联合索引(member_id, create_time, status)，执行：

SELECT * FROM member_order WHERE member_id = 5 AND create_time > '2024-01-01' AND status = 1;

效果：member_id和create_time用到了索引，status只能回表后再过滤。因为 B+ 树索引在create_time上定位到一个范围区间，这个区间内部的status是无序的，无法继续匹配。

修复方案是把等值条件提前，范围条件放最后：

ALTER TABLE member_order ADD INDEX idx_cover (member_id, status, create_time);

这个例子也呼应了最左前缀原则——字段顺序不是拍脑袋定的，而是根据查询模式反复调出来的。

4.7 六个失效场景的对照表

我把上面的场景整理成一张速查表，方便练习时对照：

5. 覆盖索引、回表与排序：少读一次数据页的收益有多大

5.1 回表：一次查询为什么要读两棵树

InnoDB 的聚簇索引叶子节点存了整行数据，二级索引叶子节点只存索引列和主键。通过二级索引查到主键后，还要拿着主键回聚簇索引里找完整行，这个动作叫回表。

回表不是每次都很致命，但数据量大、回表次数多的时候，随机 IO 会成为瓶颈。我在 100 万行的表里做了简单测试：查某个 member 的 5000 条订单，普通回表方案耗时约 180ms，覆盖索引方案约 60ms，差距在 3 倍左右。对高频执行的小查询，这个差距会被放大，因为每个请求都在消耗额外 IO。

5.2 覆盖索引的实战写法

原 SQL：

SELECT order_no, amount, status FROM member_order WHERE member_id = 123 AND status IN (1, 2) ORDER BY create_time DESC LIMIT 10;

现有索引(member_id, status, create_time)，执行计划里Extra是Using index condition，说明走了索引下推但还需要回表取order_no和amount。

这时建一个覆盖索引：

ALTER TABLE member_order ADD INDEX idx_member_status_create ( member_id, status, create_time, order_no, amount );

覆盖索引的字段顺序要和查询条件的匹配顺序一致，create_time放在status后面同时解决了排序问题，order_no和amount放在最后是为了让 SELECT 列直接从索引树取，不再回表。

5.3 排序优化：ORDER BY 和索引顺序的一致性

ORDER BY create_time DESC如果索引顺序是(member_id, status, create_time)，那么当member_id和status都等值确定后，create_time在这个局部范围内是有序的，MySQL 可以避免 filesort。反过来，如果索引是(member_id, create_time, status)而 SQL 里 ORDER BY 是status, create_time，排序就会走文件排序。

判断方法还是看 Extra 有没有Using filesort。出现的时候检查三点：

• WHERE 等值条件是否已经"消耗"掉了联合索引的最左列。
• ORDER BY 字段顺序是否和索引剩余字段顺序一致。
• 升降序是否一致。MySQL 8.0 支持降序索引，但默认升序索引对DESC排序帮助有限。

5.4 前缀索引与索引选择性

遇到remark这种长字符串字段要建索引时，不要直接建全字段索引，而是提取前 N 个字符做前缀索引：

ALTER TABLE member_order ADD INDEX idx_remark_prefix (remark(20));

多长的前缀合适？用选择性来计算：

SELECT COUNT(DISTINCT LEFT(remark, 10)) / COUNT(*) AS sel10, COUNT(DISTINCT LEFT(remark, 20)) / COUNT(*) AS sel20 FROM member_order;

选择性越高越好，一般建议达到 0.7 以上，同时前缀长度不宜过长，否则占空间大、写入也慢。

前缀索引的缺点也要清楚：不能再用于覆盖索引，也不能用于 ORDER BY 和 GROUP BY。所以前缀索引适合的场景是"等值查询 + 回表可接受"的长字段检索。

6. 从一条线上慢查询到索引重建：完整排查链路复盘

前面的练习是零散的场景复现，这一节我把它们串起来，复盘一条线上慢查询从发现到修复的完整过程。

6.1 现象：接口超时，数据库 CPU 偏高

某天订单查询接口 P99 延迟从 120ms 涨到 2.5s，数据库 CPU 长时间在 70% 以上。我先打开slow_query_log，捞出耗时最高的几条 SQL，发现都是同一类查询：

SELECT id, order_no, amount, status, create_time, remark FROM member_order WHERE member_id = ? AND create_time >= ? AND create_time < ? ORDER BY create_time DESC LIMIT 20;

参数不同，但耗时普遍超过 1.5s。热点用户尤其明显。

6.2 排查链路：从慢日志到执行计划

我按顺序执行了下面几步：

1. 确认规律：耗时和member_id的分布强相关，热点用户明显更慢。
2. SHOW INDEX FROM member_order;，发现表上只有主键、uk_order_no和单列索引idx_member_id。
3. 对热点用户跑 explain：

EXPLAIN SELECT id, order_no, amount, status, create_time, remark FROM member_order WHERE member_id = 88888 AND create_time >= '2024-01-01' AND create_time < '2024-03-01' ORDER BY create_time DESC LIMIT 20;

执行计划显示type = ref，key = idx_member_id，rows = 13000，Extra里有Using filesort。

问题一下清楚了：idx_member_id只用了member_id等值过滤，查出 1.3 万行，再回表读取完整行，然后做文件排序取 20 条。热点用户订单多，这个路径特别吃亏。

6.3 根因定位：索引设计没跟上查询模式

这里有三个可优化点：

• 回表太多。二级索引叶子节点只有member_id和主键，1.3 万行都要回表拿order_no、amount、create_time、remark等字段。
• 文件排序。idx_member_id不包含create_time，同一用户下的订单在索引里是无序的，排序必须额外做。
• 不能覆盖查询列。SELECT 里有remark，即使加了create_time也覆盖不了所有列，所以最终方案要考虑回表成本。

最合理的修复是直接改成联合索引：

ALTER TABLE member_order DROP INDEX idx_member_id, ADD INDEX idx_member_time (member_id, create_time);

注意我把旧的单列索引删了，因为新索引的最左列就是member_id，单列索引完全冗余。这也是前面强调过的"索引不是越多越好"的实战版本：既解决当前问题，又清理重复索引。

6.4 验证与上线

重建索引后，再跑同样的 SQL：

type: range key: idx_member_time rows: 420 Extra: Using index condition

rows 从 13000 降到 420，实际查询耗时从 1.8s 降到 80ms 左右，接口 P99 回落到 200ms 以内。

上线前我额外做了件很多人忽略的事：在测试环境用不同倾斜度的数据模拟热点用户，反复跑 SQL，确认新索引在最坏数据分布下不会退化。步骤不复杂，但能避免"本地正常、上线翻车"。

6.5 上线后的监控和反馈闭环

索引改动上线后，我持续观察三个指标：

• slow_query_log里同类 SQL 是否消失。
• 数据库 CPU 是否回落。
• 索引使用情况：通过performance_schema.table_io_waits_summary_by_index_usage或 sys 库的statement_analysis，看新索引是否真的被高频使用。

如果新索引建了一周都没被使用过，那大概率是查询 SQL 和索引设计脱节了，需要重新回到 explain 阶段排查。

7. 练习之后的体会：索引优化是取舍，不是堆叠

练习做到这里，我发现索引优化真正的难点不是记住 B+ 树结构，也不是背会 explain 每个字段的含义，而是在具体业务场景里做取舍。

idx_member_time解决了订单查询，但也意味着每次插入、更新都要维护这个联合索引。如果这张表的写入量是 10 万 TPS，索引项的增加会实打实反映在写入延迟上。所以我在设计索引时始终保留一个习惯：列出这张表最核心的五到十条 SQL，用它们来反推索引设计，而不是看到 where 条件就加索引。

另外一个体会是练习环境尽量贴近生产：数据量要够大，数据分布要倾斜，SQL 要带真实的排序和分页。用一千行数据练索引优化，得出的结论经常会误导人。我见过有人拿着 500 行测试表说"加了索引就是快"，到了生产环境被打脸——差异就在于数据量和分布完全不同。

如果你也想系统练一遍，我建议按这个顺序走：先把 explain 的type、rows、Extra三个列看熟，然后在一张百万行表上把第四章的六个失效场景全部复现并修复，最后再用第五章的覆盖索引思路优化几条慢查询。等这个过程跑完，再去应对线上慢 SQL 和面试里的索引题，会顺手很多。