最近处理了一个线上查询卡顿的问题，订单列表接口偶尔会慢到让人怀疑数据库是不是在“摸鱼”，看一眼慢查询日志，单条SQL最离谱的时候跑了3秒多。这种问题排查到最后，几乎都会落在索引上。MySQL索引优化是一个说起来人人都懂、做起来却很容易翻车的话题，这篇就把我在实战里反复用到的5个提速技巧完整梳理一遍，你对照自己的表结构和慢查询，基本上就能直接照着改。
这篇内容适合正在被慢查询折磨的后端开发、DBA新人，也适合那些每天都在写SQL但没有系统研究过执行计划的同学。我会把“为什么要这样建索引”“为什么查询会快”这种底层逻辑讲清楚，同时给出可以直接抄作业的SQL和建索引语句。只要你能看懂SELECT语句，跟着操作一遍，就能感受到查询从几百毫秒掉到几十毫秒、甚至几毫秒的差别。
1. 动手之前，先把“慢在哪”定位清楚
优化索引本身不难，难的是找到那个真正拖垮性能的SQL。很多同事一上来就对着整个库的所有索引一顿重构，结果可能优化了一个根本不慢的查询，真正的慢查询还在角落里默默跑着。所以第一步不是改索引，而是把目标锁定。
1.1 慢查询日志：先找出真正要优化的SQL
MySQL自带的慢查询日志就是定位问题的第一把钥匙。默认情况下它是关闭的，你可以先查看当前配置：
SHOW VARIABLES LIKE 'slow_query_log%'; SHOW VARIABLES LIKE 'long_query_time';如果没有开启，可以在MySQL命令行里临时打开（重启后失效，正式环境需要修改配置文件）：
SET GLOBAL slow_query_log = 'ON'; SET GLOBAL long_query_time = 1;long_query_time的单位是秒，设置为1就表示执行时间超过1秒的SQL会被记录到慢查询日志里。我习惯在分析阶段把这个阈值先设成0.5秒，抓一批相对慢的SQL出来，再根据实际执行频率和耗时综合判断。日志文件路径可以用下面的命令查到：
SHOW VARIABLES LIKE 'slow_query_log_file';日志里记录的每一条慢SQL都带有执行时间、锁等待时间、返回行数、扫描行数等信息。注意看扫描行数和返回行数的对比，如果扫描了50万行只返回20条，那十有八九是索引没有用好。
1.2 Explain：一条SQL的执行路线图
拿到慢SQL之后，下一步就是给这条SQL做体检，Explain命令就是执行计划的展示工具：
EXPLAIN SELECT order_id, user_id, amount, status FROM t_order WHERE user_id = 12345 ORDER BY create_time DESC LIMIT 20;执行计划里重点看几个字段：
type：访问类型，从好到差依次是system、const、eq_ref、ref、range、index、ALL。出现ALL就是全表扫描，这是最需要警惕的信号。key：实际用到的索引。如果显示NULL，说明这条SQL没有用到任何索引。rows：预估扫描的行数，这个数字越大，成本越高。Extra：这里经常会出现Using filesort、Using temporary、Using index等提示，每一种都对应不同的优化空间。Using filesort表示排序没有用到索引，需要额外的排序操作；Using index说明索引覆盖，直接读索引就拿到了所有数据。
我做完Explain之后，通常只关心三个问题：有没有走索引？走的是哪个索引？扫描了多少行？把这三个问题回答清楚，优化方向基本就出来了。
2. 技巧一：覆盖索引，让查询不碰数据行
你可能遇到过这种情况：明明WHERE条件里的字段已经建了索引，查询却还是不够快。这时候，问题很可能出在SELECT后面的字段上。
2.1 覆盖索引为什么能快这么多
MySQL的InnoDB引擎里，主键索引（聚簇索引）的叶子节点存的是整行数据，而普通索引（二级索引）的叶子节点存的是索引字段值加主键值。
普通情况下，用二级索引查数据需要两步：先扫二级索引找到主键值，再拿着主键值去聚簇索引里回表查整行数据。回表意味着一次随机I/O，数据量大时这个额外开销非常可观。
覆盖索引就是让查询需要的所有字段都能在二级索引里直接取到，不需要回表。拿你手机里的通讯录举例：你要找某个人的电话号码，如果通讯录直接按“姓名-电话”建目录，翻目录就能看到电话，不用再打开每个人的详情页。这个“目录本身已经包含答案”的思路，就是覆盖索引。
2.2 覆盖索引的实战设计方法
假设有一个订单表：
CREATE TABLE t_order ( id BIGINT PRIMARY KEY AUTO_INCREMENT, user_id INT NOT NULL, order_id VARCHAR(64) NOT NULL, amount DECIMAL(10,2), status TINYINT, create_time DATETIME, KEY idx_user_id (user_id) );如果执行这条SQL：
SELECT user_id, create_time FROM t_order WHERE user_id = 123;虽然user_id字段有索引，但create_time不在索引中，MySQL需要回表读取整行数据再返回。优化方式是把查询需要的字段一起放到索引中：
ALTER TABLE t_order ADD KEY idx_user_create (user_id, create_time);再次执行Explain，你会发现Extra字段变成了Using index，这说明查询已经从“索引扫描+回表”变成“纯索引扫描”，速度自然快了一大截。我在实际优化中，遇到高频查询且返回字段固定的场景，都会优先考虑覆盖索引。但要注意，不要为了覆盖而把所有字段都塞进索引，索引越长，插入和更新的代价越高。
3. 技巧二：联合索引与最左前缀，顺序决定成败
单字段索引谁都会建，但实际业务里一个查询往往涉及多个条件，这时候联合索引就派上了用场。联合索引最大的坑在于字段顺序，顺序建反了，索引效率会大打折扣。
3.1 联合索引的字段顺序怎么排
联合索引(a, b, c)本质上相当于建立了三个索引：(a)、(a, b)、(a, b, c)。MySQL会按照定义索引时的字段顺序，从左到右匹配查询条件。
先看一个实际场景，订单查询经常按user_id和status过滤：
SELECT * FROM t_order WHERE user_id = 12345 AND status = 1 ORDER BY create_time DESC;我建议把索引建成：
ALTER TABLE t_order ADD KEY idx_user_status_create (user_id, status, create_time);这里有个通用的设计原则：先把等值查询的字段放前面，再把用于排序的字段放后面。因为等值条件下的字段可以精确定位，而排序字段放入索引可以避免文件排序。至于哪个等值字段放更前面，可以比较一下字段的区分度，区分度高的优先放前面。
区分度可以用这个SQL大致估算：
SELECT COUNT(DISTINCT user_id) / COUNT(*) FROM t_order;如果user_id的区分度是0.9，而status只有0.01，那user_id一定放在status前面，因为先按高区分度字段过滤能更快地缩小数据范围。
3.2 最左前缀的边界与常见误区
最左前缀原则意味着，如果查询条件跳过了第一个字段，索引就用不上了。比如上面的联合索引(user_id, status, create_time)，下面的查询就无法使用这个索引：
SELECT * FROM t_order WHERE status = 1;因为索引的第一列是user_id，查询条件里没有user_id，MySQL没法从联合索引最左边开始匹配。这一点很多新人会踩坑，以为建了联合索引就万事大吉。
还有一种很容易被忽略的边界情况：范围查询会中断后面的匹配。比如：
WHERE user_id = 123 AND create_time > '2024-01-01' AND status = 1;如果索引是(user_id, create_time, status)，create_time用了范围查询，那么status条件就无法继续利用索引匹配，只能作为普通过滤条件了。所以设计联合索引时，尽量把范围查询的字段放在最后面，或者根据实际查询频率做取舍。
4. 技巧三：索引下推，模糊查询也能跑得快
MySQL 5.6引入的索引下推（Index Condition Pushdown，ICP）是一个容易被忽略但非常实用的特性。它解决的是联合索引下“后置字段”的过滤效率问题，对LIKE前缀模糊查询尤其有效。
4.1 索引下推做了什么
在没有ICP的旧版本里，如果联合索引是(user_id, status)，执行查询：
SELECT * FROM t_order WHERE user_id = 12345 AND status LIKE 'PAID%';MySQL只能通过user_id = 12345定位到一批索引记录，拿到主键后回表读取每一行，再在Server层判断status LIKE 'PAID%'是否成立。明明索引里就有status字段，却要把整行数据捞出来再过滤，白白浪费很多回表操作。
启用ICP之后，存储引擎会在索引内部先做完status LIKE 'PAID%'的过滤，只把匹配成功的索引记录送回Server层，再执行回表。回表次数少了，I/O自然少了。
可以这样理解：以前是厨师把所有菜都端上桌，由食客自己挑想吃的；现在厨师在后厨就先把不新鲜的菜扔掉了，端上桌的都是符合要求的。
4.2 怎么确认ICP生效
确认ICP是否生效很简单，执行Explain后看Extra字段里有没有Using index condition。如果有，说明条件条件下推到存储引擎了，索引旧数据已经先经过一层过滤。
我实测过一个物流订单查询，(sender_id, express_no)联合索引，本来LIKE查询要回表几千行，ICP生效后回表只有几十行，查询时间从200多毫秒降到了30毫秒左右。
需要说明的是，ICP默认是开启的，但你可以在配置文件里确认：
optimizer_switch = 'index_condition_pushdown=on'日常工作中，只要在联合索引查询中使用了前缀匹配的模糊查找，我都会习惯性地看一眼Explain里有没有Using index condition。如果没有，先检查MySQL版本是不是低于5.6，再检查联合索引的字段顺序是否合理。
5. 技巧四：前缀索引，长字段的优雅解法
不是所有字段都适合直接建索引。比如用户邮箱、身份证号、商品描述这类长度动辄几十上百的字符串，如果整个字段加索引，索引会占用大量空间，B+树的层高也会增加，插入更新变慢，查询性能反而受影响。这时候就该用前缀索引了。
5.1 长字符串字段为什么不适合全列索引
InnoDB默认的单索引长度限制是3072字节，虽然很多字段类型可以撑满这个上限，但完全没有必要。索引本质上是在B+树里维护一份有序的副本，字段越长，这个副本越庞大，同样大小的数据页能存放的索引项就越少，树的高度就越高。树每高一层，查询就多一次磁盘I/O。
举个例子，一张用户表有200万行，邮箱字段平均长度18个字符，如果全列索引，每个索引项不止要存邮箱原值，还要存主键值，一页能放的索引项可能只有几百个；如果只取前8个字符做前缀索引，同样的页能放的索引项数量就上去了，查询效率差别非常明显。
5.2 前缀长度怎么选，用数据说话
前缀索引的关键是选择合适的前缀长度，太长没意义，太短区分度不够。选长度这件事，不要拍脑袋，用区分度计算来决定。
先计算完整列的选择性：
SELECT COUNT(DISTINCT email) / COUNT(*) AS full_selectivity FROM t_user;然后逐步测试不同前缀长度的选择性：
SELECT COUNT(DISTINCT LEFT(email, 5)) / COUNT(*) AS len5, COUNT(DISTINCT LEFT(email, 8)) / COUNT(*) AS len8, COUNT(DISTINCT LEFT(email, 10)) / COUNT(*) AS len10 FROM t_user;选择的原则很简单：长度在区分度接近完整列的情况下取最小值。比如完整列选择性是0.97，len8是0.96，len10是0.969，那就选8。这样既保证了查询过滤效率，又控制了索引体积。
使用前缀索引时有一个注意点，它不能用于ORDER BY和GROUP BY操作，因为前缀索引只保存了字段的一部分内容，无法完整排序或分组。遇到必须排序的查询，建议再单独评估是否需要覆盖索引或全列索引。
6. 技巧五：别让索引白白失效，几个致命写法
建了索引不假，但SQL写法不对，索引照样用不上。很多时候查询慢不是因为没索引，而是索引被“废”了。
6.1 最常见的索引失效动作
函数操作是最典型的失效场景。对索引字段使用函数后，索引就会失效。比如：
SELECT * FROM t_order WHERE DATE(create_time) = '2024-05-20';这会导致create_time上的索引无法使用，因为索引存储的是原始时间戳，不是DATE()函数处理后的值。正确的写法是范围条件：
SELECT * FROM t_order WHERE create_time >= '2024-05-20 00:00:00' AND create_time < '2024-05-21 00:00:00';隐式类型转换也很坑。比如user_id字段是VARCHAR类型，查询时却传了数字：
SELECT * FROM t_user WHERE user_id = 12345;MySQL会先把字段值转换成数字再比较，相当于对索引列做了隐式函数操作，索引自然失效。解决方法是传入值写成字符串形式：
SELECT * FROM t_user WHERE user_id = '12345';LIKE查询里，前置通配符也会导致索引失效：
SELECT * FROM t_order WHERE order_id LIKE '%20240520%';%开头的模糊匹配没办法利用B+树的有序结构进行前缀查找，只能全索引扫描或者全表扫描。只有order_id LIKE '20240520%'这种后缀通配，才能走索引。
OR条件也需要警惕。如果OR后面的字段没有索引，整个查询都走不了索引：
SELECT * FROM t_order WHERE user_id = 12345 OR status = 1;即使user_id有索引，status没有索引，MySQL也可能选择全表扫描。这种情况下，可以把OR拆成两个查询，再用UNION合并：
SELECT * FROM t_order WHERE user_id = 12345 UNION SELECT * FROM t_order WHERE status = 1;6.2 排序与分组优化：Filesort的解决方案
ORDER BY导致Using filesort也是常见问题。比如查询按create_time排序，但没有合适的索引，MySQL就需要把中间结果集加载到内存或磁盘上排序，这就是文件排序。
优化思路是把排序字段加入到联合索引中。比如查询：
SELECT * FROM t_order WHERE user_id = 12345 ORDER BY create_time DESC LIMIT 20;建立联合索引(user_id, create_time)之后，数据先按user_id匹配，在同一user_id下天然按create_time有序，排序操作直接取消，Explain里的Using filesort就消失了。
GROUP BY也是同样的原理，因为分组本身需要排序。比如按status分组统计：
SELECT status, COUNT(*) FROM t_order GROUP BY status;如果status上有合适的索引，分组可以高效完成，否则Using temporary就会出现在Extra字段里。
7. 排查实录：一次从3秒到30毫秒的完整优化
光讲理论容易飘，下面分享一个我在实际项目中处理的完整优化案例，复盘整个排查和改动过程。
7.1 现场情况与定位
某订单管理后台的卖家订单列表接口，用户翻页查询时经常超过2秒，高峰期甚至到4秒。合作方反馈很强烈。我先打开慢查询日志，抓到一条核心SQL：
SELECT id, order_id, user_id, amount, status, create_time FROM t_order WHERE seller_id = 20240520 ORDER BY create_time DESC LIMIT 20;先看表结构，t_order表当时只有主键和user_id上的普通索引，seller_id上没有索引。执行Explain确认：
type: ALL key: NULL rows: 486512 Extra: Using filesort全表扫描48万行，还要文件排序，难怪会慢。
7.2 优化步骤与结果对比
第一步，加联合索引。查询条件是seller_id等值匹配，排序条件是create_time，所以按前面的原则建立联合索引：
ALTER TABLE t_order ADD KEY idx_seller_create (seller_id, create_time);再次Explain：
type: ref key: idx_seller_create rows: 156 Extra: NULL扫描行数从48万降到156，文件排序消失，查询时间从3.2秒降到了58毫秒。
第二步，考虑到列表接口每次返回的字段很固定，进一步把amount、status也塞进索引，做成覆盖索引：
ALTER TABLE t_order ADD KEY idx_seller_create_amount (seller_id, create_time, amount, status);这次Explain的Extra变成了Using index，查询只需要扫描索引页，不需要回表，线上实测稳定在30毫秒左右。这一步相当于把所有常用字段直接“塞进目录”，连翻详情页都省了。
同时我还检查了一个问题：order_id字段是VARCHAR类型，但传入参数偶尔会被写成数字类型。排查了一下调用链，把查询条件统一改成字符串传参，避免了隐式类型转换。
这个案例里最有价值的一点是，优化过程并不复杂，也没有用任何“黑科技”，就是老老实实看执行计划、分析扫描行数、按照联合索引设计原则来建索引。效果却非常显著。这也说明大部分慢查询的根源并不神秘，就是索引没建对或者没建全。
8. 问题速查表与我的几条实操习惯
最后把日常工作中最容易遇到的索引问题整理成一张速查表，方便你排查时对号入座。
8.1 常见问题速查表
| 症状表现 | 可能原因 | 解决办法 |
|---|---|---|
| 明明有索引却不走，type是ALL | SQL写法导致索引失效，如函数、隐式转换 | 改SQL写法，让列保持原始类型参与比较 |
| Extra出现Using filesort | ORDER BY字段不在索引中 | 把排序字段加入联合索引 |
| 联合索引查询慢 | 字段顺序不合理，范围字段放中间 | 等值字段前置，范围字段放最后 |
| LIKE后置%很慢 | 前置通配符导致索引失效 | 改成前缀模糊，或使用全文索引 |
| select多列查询仍回表 | 查询列不全在索引中 | 按高频查询设计覆盖索引 |
| 长字符串加索引后写入变慢 | 索引过大导致B+树膨胀 | 改用前缀索引 |
| OR条件查询全表扫描 | 某个OR字段没有索引 | 拆分为UNION，或给所有OR字段加索引 |
| 统计报表GROUP BY慢 | 分组字段没有合适的索引 | 按分组和聚合需求建立联合索引 |
| count(*)特别慢 | 条件无法利用索引 | 优化WHERE条件，添加合适的过滤索引 |
8.2 我踩过几次坑之后养成的习惯
第一，新索引上线前，一定用Explain验证一遍，不要建完索引就以为万事大吉。索引确实建了，但SQL写法不对导致索引没用上的情况我遇到过太多次。
第二，定期检查重复索引。有些表上会有idx_user_id(user_id)和idx_user_create(user_id, create_time)，前者很多时候就是多余的，因为联合索引最左前缀已经能覆盖user_id单字段查询。重复索引既浪费空间，还拖慢写入。
第三，别忽视统计信息的时效性。MySQL的优化器会根据统计信息决定是否走索引，如果统计信息过期，可能会出现明明有索引却不走的情况。遇到这类问题，先执行ANALYZE TABLE t_order刷新统计信息，再看看执行计划是否变化。
第四，大表加索引要谨慎。千万级以上的表，ALTER TABLE加索引会锁表或触发比较长的DDL过程，尽量在业务低峰期操作，或者使用在线DDL工具。小表随便加，大表务必提前规划。
索引优化这件事，做得越久越会发现，它不完全是技术活，更是“思路活”。只要你把执行计划看明白了，理解了B+树、回表、覆盖索引、最左前缀这些核心概念，大多数慢查询都能在半小时内定位原因。这套方法我在很多个项目里反复验证过，从3秒到30毫秒的案例也远不止上面这一个，照着这个思路去查，你的SQL大概率也能快上100倍。
