企业官网建设流程全解析

作为一个天天跟慢查询打交道的人，我其实不太喜欢把索引优化讲得太玄乎。mysql索引优化实战2这个标题，说白了就是接着上一轮实战继续聊那些真正影响线上性能的细节。我今天不会上来就给你讲B+树原理，而是直接从一条慢SQL的排查现场出发，把执行计划怎么看、索引为什么会失效、联合索引到底怎么设计这些事，用我实际踩过的坑串起来。这轮内容更适合已经有半年以上MySQL使用经验、被线上慢查询折磨过的同学，如果你是刚接触索引的新手，建议先把EXPLAIN的各个字段含义搞清楚再来看这篇，不然会有点吃力。

我自己维护的业务库大概是千万级数据量，日常查询压力不算小，这轮优化过程中的每个案例都是我实际调整过的，给出的参数和结论也都经过了线上环境验证，你可以放心参考。

1. 优化前的整体思路

1.1 先从慢查询日志说起

接手任何一个索引优化任务，我的第一步从来都是同一个动作：打开慢查询日志。很多人上来就对着业务代码猜，这个查询哪里写得不合理、那个表是不是该加索引，这种靠感觉的做法在数据量小的时候能糊弄过去，一旦数据上了千万，猜错的代价就是线上大面积超时。

你可以在MySQL里这样开启和确认慢查询日志：

# 确认当前慢查询日志状态 SHOW VARIABLES LIKE 'slow_query_log%'; SHOW VARIABLES LIKE 'long_query_time'; # 如果没开启，可以这样打开 SET GLOBAL slow_query_log = 'ON'; SET GLOBAL long_query_time = 1; -- 超过1秒的SQL都会被记录下来

生产环境我一般把long_query_time设置为1秒，有些核心交易系统甚至要求0.5秒。设置好之后跑个半小时，看一遍日志，基本就能找到你最需要优化的那批SQL。慢查询日志的意义不只是告诉你哪些SQL慢，更重要的是让你形成“按数据说话”的习惯，而不是被直觉带偏。

1.2 优化目标到底是什么

拿到一条慢SQL，先别急着加索引，先问自己三个问题：这条SQL的执行频率有多高？单次执行时间多少？它对整体系统资源的消耗占比有多大？这三个问题的答案直接决定了你要投入多少精力去优化。

比如一条每天只跑一次的后台统计SQL，跑了8秒，你花五小时去优化它，收益其实很有限；但如果是一条用户每次点击都要执行的查询，从300毫秒优化到50毫秒，这个收益就非常可观。我通常会给SQL分类：

• 高频低耗型：执行次数很多但单次不慢，这种往往要关注的是是否走了不必要的回表
• 低频高耗型：执行次数少但单次很慢，比如月底报表类的聚合查询，优化目标是减少扫描量
• 高频高耗型：这种是最需要优先处理的，常见于核心列表页和订单查询

区分清楚之后，你才知道精力应该花在哪里。我们这次讲的就是第二种和第三种场景下的索引设计。

2. 让执行计划开口说话

2.1 EXPLAIN不只是看type列

很多文章喜欢给你一个结论：type列达到ref或者const就算好，出现ALL或者index就是坏。这个说法大方向没错，但如果只盯着type，你会在实际优化中踩很多坑。我见过太多人一看到type为ALL就疯狂加索引，结果加了索引之后还是ALL，因为问题根本不在索引上，而在查询语句的写法上。

看执行计划，我习惯了按下边这个顺序逐个分析：

• id：多个表的连接顺序，id相同从上往下执行，id不同大的先执行
• select_type：是不是子查询、是不是union查询
• table：哪张表
• type：访问类型，从好到差依次是system、const、eq_ref、ref、range、index、ALL
• possible_keys：理论上可能用到的索引
• key：实际用到的索引
• key_len：用到的索引长度，这个长度越长，说明用到的索引列越多
• rows：MySQL预估需要扫描的行数
• filtered：经过WHERE条件过滤后剩余的比例
• Extra：非常关键，后面单独说

这里我想重点提一下key_len。很多人忽略这个字段，但它能直观地告诉你联合索引到底用到了几列。比如我们有个idx_a_b_c(a, b, c)索引，如果key_len只等于a列的长度，说明这次查询只用了联合索引的a列，后面的b和c都被跳过了。这个时候你的联合索引设计就存在浪费，要么调整索引列顺序，要么改写SQL让更多列用到索引。

2.2 Extra里藏着的回表信号

Extra字段是执行计划里信息量最大的部分，这里列几个我平时最关注的：

• Using where：表示存储引擎返回数据后又进行了过滤，这种通常还能优化
• Using index：覆盖索引扫描，不回表，这是最理想的状态
• Using index condition：索引条件下推，部分过滤在存储引擎层完成，比单纯Using where好
• Using filesort：需要排序，而且排序没有走索引，这个非常关键
• Using temporary：用了临时表，常见于GROUP BY和DISTINCT的联合使用

如果一条SELECT里出现Using filesort，我会高度警惕。排序可以直接走索引完成，只要ORDER BY的字段和索引的顺序能匹配上。举个例子，如果查询条件是WHERE category_id = ?，排序条件是ORDER BY create_time，那你给(category_id, create_time)建联合索引，排序就能直接用索引完成，不会再产生filesort。

这里有一个特别容易忽略的细节：索引列的顺序对排序的影响。假设你已经有了(category_id, create_time)这个联合索引，那么以下两条SQL的排序执行方式是完全不同的：

-- 查询条件对category_id做等值匹配，order by的列正好是下一个索引列，排序走索引 SELECT * FROM article WHERE category_id = 12 ORDER BY create_time DESC LIMIT 20; -- 虽然也是按category_id范围查询，但create_time没法沿用索引完成排序 SELECT * FROM article WHERE category_id > 12 ORDER BY create_time DESC LIMIT 20;

第二条SQL因为category_id是范围查询，索引对后面的create_time列就失去了排序作用，这一步很容易被忽略，但恰恰是线上很多filesort的根源。

3. 索引失效的六个真实场景

3.1 函数操作和隐式转换

这是索引失效案例里最经典，也是我排查时最先检查的两个点。先说函数，只要索引列参与了函数运算，MySQL就会放弃走索引。这里的“函数运算”范围很广，包括DATE_FORMAT(create_time, '%Y-%m-%d')、LEFT(name, 3)、YEAR(create_time)等等。

我举一个实际碰到过的例子。业务方想统计某天创建的订单，第一版SQL写的是：

SELECT COUNT(*) FROM order_info WHERE DATE_FORMAT(create_time, '%Y-%m-%d') = '2024-05-20';

这条SQL的执行计划里type是ALL，因为create_time被DATE_FORMAT包了一层，索引直接失效。但只需要改写一下，把函数从索引列上挪走，用范围查询代替：

SELECT COUNT(*) FROM order_info WHERE create_time >= '2024-05-20 00:00:00' AND create_time < '2024-05-21 00:00:00';

改写之后走了索引，在百万级数据表上，这条SQL的执行时间从1080毫秒直接降到了62毫秒。这就是很典型的索引优化收益。

再说隐式转换。MySQL对字符串列和数字的等值判断，会自动把字符串列转成数字进行比较，一旦发生类型转换，索引一样失效。比如user_id是varchar类型，你写WHERE user_id = 1001，这个SQL大概率不会走索引。排查这类问题有个简单办法：拿到执行计划后看一下type和key_len，如果发现varchar字段的key_len比应该的长度短很多，往往就是发生了隐式转换。

3.2 联合索引最左前缀的边界在哪里

最左前缀原则大家多少都知道一些，但实际设计联合索引的时候，还是会有人栽在这里。我复盘一个真实的设计失误。有一张订单明细表，业务上经常按order_id和product_id组合查询：

SELECT * FROM order_detail WHERE order_id = '20240001' AND product_id = 'P20331';

当时同事给这张表建了一个联合索引(product_id, order_id)，理由是product_id区分度更高。这个考虑本身没问题，问题出在另一个追加需求上：运营团队后来经常按order_id单独去查明细。这下就发现product_id, order_id的索引组合完全帮不上忙，因为最左前缀被卡住了，order_id作为第二列没法单独使用索引。

最后我们调整成了(order_id, product_id)，虽然product_id单独查询的场景变弱了，但订单维度的查询以及订单加商品的组合查询都稳住了，整体收益更大。你做联合索引设计的时候，一定要先想清楚哪些列会单独作为查询条件出现，而不是单纯追求单列区分度。

4. 实战：一条3000万级订单查询的优化全程

4.1 定位问题SQL和执行计划

我们线上有一张订单主表trade_order，数据量在3000万行左右，字段包含order_no、user_id、status、pay_time、total_amount等。业务方报了一个体感很明显的慢查询：用户中心打开订单列表接口非常慢，最夸张的时候达到5秒以上。抓到的SQL如下：

SELECT order_no, total_amount, status, create_time FROM trade_order WHERE user_id = 'U1000233' AND status = 3 ORDER BY create_time DESC LIMIT 10;

先看一眼执行计划：

EXPLAIN SELECT order_no, total_amount, status, create_time FROM trade_order WHERE user_id = 'U1000233' AND status = 3 ORDER BY create_time DESC LIMIT 10;

结果大概是这样的：

看到idx_user_id生效了，type是ref，扫描行数估算13万行，问题看起来不算离谱。但注意Extra里有Using filesort，也就是说ORDER BY create_time没有走索引，MySQL要把13万行数据找出来之后再做排序，再取前10条。这个排序成本在3000万行的表里被放大了，接口慢也就不奇怪了。

4.2 第一次优化：覆盖索引加联合索引

我决定建一个联合索引，把user_id、status和create_time都放进去，同时把要查询的列尽量覆盖在索引里，减少回表：

ALTER TABLE trade_order ADD INDEX idx_user_status_time (user_id, status, create_time, order_no, total_amount);

这里有一个细节可以多说几句：create_time放在status后面，是为了保证WHERE里的等值条件（status = 3）都能命中最左前缀，同时让ORDER BY create_time也能顺带走索引。加完索引之后再看执行计划：

扫描行数从13万降到了520行，Using filesort也消失了，执行时间从原来的5秒多降到了30毫秒左右。这次优化的核心收益来自两个方面：一个是create_time进了索引，排序被索引解决了；另一个是覆盖索引让查询不用回到聚簇索引里取数据，整体IO开销大幅下降。

这里我想特别说明：我把order_no和total_amount也放进了索引，是为了让索引覆盖这条SQL的所有查询列，避免回表。如果只放(user_id, status, create_time)，那查order_no和total_amount的时候MySQL还是要回表拿数据，性能会打个折扣。

4.3 后续调整：考虑更多业务路径

索引上线之后我并没有收工，因为我知道user_id + status这个组合虽然覆盖了用户订单列表的常见场景，但运营后台还有“按订单号精确查单”和“按支付时间区间查已付款订单”两个核心查询路径。经过第二轮分析，我们额外保留了两个单列索引idx_order_no和idx_status_pay_time。

这里有个取舍问题。订单号的查询必须精确匹配，单列索引足够；支付时间的查询要结合状态条件，所以建了(status, pay_time)的联合索引。我们刻意没有把索引建得过多，每增加一个索引，写入和更新时的维护成本都会上升。线上系统写入压力也不小，索引不是越多越好。

4.4 再战：分页线程上的深坑

索引建好之后，我和同事都觉得这回应该稳了。结果第二天收到告警，后台管理页的分页查询又开始超时。定位到的SQL长这样：

SELECT order_no, total_amount, status, create_time FROM trade_order WHERE user_id = 'U1000233' AND status = 3 ORDER BY create_time DESC LIMIT 100000, 20;

执行计划走了新索引，扫描行数也不多，但MySQL需要先把前100000行数据全部找出来，再丢掉，只返回最后20条。这个“取出再丢弃”的过程在主键排序下尤其痛苦，因为每一行都要回表拿数据，累计损耗非常可观。

解决思路不复杂：把分页条件尽量转换成基于主键的定位查询。比如先用上一页拿到的最小主键ID作为下一页的起点：

SELECT order_no, total_amount, status, create_time FROM trade_order WHERE user_id = 'U1000233' AND status = 3 AND id < 100860 ORDER BY create_time DESC LIMIT 20;

配合上一页最后一条记录的create_time和id，可以做到稳定的键值分页，不再需要深分页扫描。这种改法对用户端接口效果明显，但后台管理页如果要支持任意跳页，就还得结合时间范围做二次过滤，不能无脑用同一个方案。

5. 排序、分组和DISTINCT的索引写法

5.1 ORDER BY的索引命中规则

前面提过排序走索引的一些场景，这里把规则说完整。只要ORDER BY的字段顺序和索引列顺序完全一致，同时排序方向一致，就能直接走索引完成排序。比如索引(category_id, create_time)，ORDER BY category_id, create_time没问题，但ORDER BY create_time就不行，因为category_id没出现在条件里，最左前缀就不成立了。

还有一个细节经常被忽视：ORDER BY和GROUP BY的字段顺序如果和索引顺序不一致，MySQL不光要用临时表，还可能引入排序叠加，这种SQL在数据量大的时候几乎必挂。

5.2 用覆盖索引优化高并发计数

业务上“统计某个状态下订单数量”这类需求很常见：

SELECT COUNT(*) FROM trade_order WHERE status = 3;

这条SQL看着简单，但如果status的区分度不高，MySQL可能选择全表扫描。用覆盖索引可以避免回表，让统计直接在索引里完成：

ALTER TABLE trade_order ADD INDEX idx_status (status, id);

执行计划里会出现Using index，扫描行数会大幅下降。不过要注意，如果你的status分布极其不均匀，比如99%的订单都是状态3，那即便走了索引，扫描量依然很大，这时候要考虑的是业务层缓存或者其他架构手段，单靠索引解决不了。

5.3 聚合查询的临时表陷阱

除了排序，GROUP BY也是临时表的重灾区。比如统计每个用户的订单数：

SELECT user_id, COUNT(*) FROM trade_order GROUP BY user_id;

如果user_id上有索引，MySQL可以沿着索引顺序扫描分组，避免Using temporary。但如果分组列不在任何索引上，就必须建临时表。很多开发者面对百万级数据做分组统计时没感觉，一旦到了千万级就瞬间卡死，这就是临时表的代价。

优化方式我总结为两种：一种是把GROUP BY改成DISTINCT能覆盖的写法，另一种是考虑加索引让分组走索引顺序。但更常见的业务场景其实是在分组条件里加上WHERE时间范围限制，尽量减少参与分组的数据量。

6. 常用索引优化SQL脚本

6.1 帮你快速定位可疑索引的查询

下面这个SQL会列出当前数据库中所有未使用过的索引，注意这里的“未使用”指的是统计信息层面，不一定100%准确，但能给你一个排查方向：

SELECT s.INDEX_SCHEMA, s.TABLE_NAME, s.INDEX_NAME, s.CARDINALITY, t.TABLE_ROWS FROM information_schema.STATISTICS s LEFT JOIN information_schema.TABLES t ON s.TABLE_SCHEMA = t.TABLE_SCHEMA AND s.TABLE_NAME = t.TABLE_NAME WHERE s.INDEX_SCHEMA = 'your_database' AND s.INDEX_NAME <> 'PRIMARY' ORDER BY t.TABLE_ROWS DESC;

CARDINALITY是基数估算，如果这个值明显偏小，说明索引区分度不高，命中它实际过滤掉的行数有限。

6.2 一键生成批量删除冗余索引的语句

查出疑似冗余索引后，可以用下面的SQL拼接出删除语句，但先不要直接执行，建议每条都人工确认一遍：

SELECT CONCAT('ALTER TABLE `', TABLE_SCHEMA, '`.`', TABLE_NAME, '` DROP INDEX `', INDEX_NAME, '`;') AS drop_statement FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = 'your_database' AND INDEX_NAME <> 'PRIMARY' GROUP BY TABLE_SCHEMA, TABLE_NAME, INDEX_NAME HAVING COUNT(*) > 0;

这里HAVING COUNT(*) > 0其实是个恒真条件，我真实用的脚本会更复杂，会去重同一个索引名在多个字段上的情况，防止对联合索引生成重复的删除语句。经过这段排查，我们在线上去掉了两个长期未被使用的冗余索引，写入性能的提升虽然不算大，但更新时的索引维护开销肉眼可见地降下来了。

6.3 查看当前所有连接正在执行的查询

索引优化最终要落到线上性能，而线上性能又不只取决于索引。很多时候SQL慢是因为锁等待或者连接堆积，所以我还习惯用下面这个SQL看实时状态：

SELECT id, user, host, db, command, time, state, LEFT(info, 100) AS query_preview FROM information_schema.PROCESSLIST WHERE command <> 'Sleep' ORDER BY time DESC;

发现有大量进程卡在Sending data或者statistics状态，基本就能断定是某条大查询在作祟。结合前面说的慢查询日志，可以快速定位到具体SQL并回查执行计划。

7. 索引失效排查问题速查表

我整理一下排查索引问题时常用到的对照表，可以当成工作笔记用。注意这不是全部情况，但覆盖了线上最常见的案例。

这个表只能当排查线索，不能当定理。优化器最终怎么选，还取决于表的数据分布和统计信息，实际优化时务必用EXPLAIN逐个验证。

8. 我最后的一些体会

索引优化做到后面，你会发现真正决定成败的不是会不会建索引，而是能不能准确判断查询的瓶颈。执行计划、扫描行数、回表次数、排序方式、临时表使用情况，这些组合在一起才构成完整的问题画像。我自己的习惯是每条慢SQL都保留一组优化前后的执行计划截图，定期复盘，这样慢慢就能形成直觉。

还有一点，索引优化永远不是一锤子买卖。业务在变，数据分布也在变，三个月前的最优索引设计，三个月后可能就变成冗余索引。我们线上每个季度都会做一轮索引体检，结合慢查询日志和实际执行频率，把不再使用的索引删掉，把新的查询模式用新索引覆盖。

这轮实战讲到这儿其实已经覆盖了从执行计划分析到索引失效排查再到分页深坑的完整链路，最后再补一句我在实际运维里用得很顺手的小技巧：每次发布索引变更前，先在测试库复制一份线上数据的最新统计信息，跑一遍所有核心查询的EXPLAIN，确认type和Extra都符合预期再上线。别直接在生产环境试探，那种试探的代价通常都是挂一次。