2025 版 mysql索引使用技巧
说实话，这几年面试的时候被问得最多的问题，翻来覆去还是那几个：MySQL 索引为什么用 B+ 树？最左前缀原则你理解吗？联合索引怎么设计？哪些场景会导致索引失效？
很多同学背得滚瓜烂熟，但真到了线上环境，面对一张几百万行的表，where 后面挂了一长串条件，该怎么建索引却完全没头绪。这其实就是典型的"理论全会，实战全废"。
这篇文章我想从一线实际运维和优化的角度，把 MySQL 索引这几年的实践心得重新梳理一遍，重点回答那些网上讲得少、但实战最要命的问题：索引设计前要准备什么、联合索引到底怎么排字段顺序、排序和分组怎么蹭索引、哪些坑最容易踩。不管你是刚入门的学生，还是已经写了几年 SQL 的老手，这篇文章的目标只有一个——读完能直接拿去用，用起来能真正把慢查询干掉。
1. 内容整体设计与思路拆解
1.1 索引设计前先想清楚的三个问题
动手建索引之前，我强烈建议你先回答三个问题，而不是一上来就 CREATE INDEX。这三个问题你回答清楚了，索引方案基本就定了一大半。
第一个问题是这张表的查询模式是什么。说白了就是你要搞清楚 SQL 到底怎么写的。是等值查询多，还是范围查询多？有没有 ORDER BY 和 GROUP BY？有没有 JOIN？这些字段是单独出现还是经常一起出现在 where 条件里？我见过太多人建索引的时候完全不看业务 SQL，纯粹凭感觉给每个字段都来一个单列索引，结果查询优化器经常谁都不选，最后全表扫描。
第二个问题是这张表的更新频率有多高。索引的本质是用空间换时间，但很多人忽略了索引对写入的影响。每建一个索引，就等于给这张表多维护一棵 B+ 树，INSERT、UPDATE、DELETE 的时候都要同步维护。如果是一张高频写入的表，索引建多了，写入性能直接崩给你看。我印象很深的一次生产事故，就是给一张每秒写入几千次的日志表加了五个索引，结果从库同步延迟从几秒一路涨到几十分钟。
第三个问题是区分度够不够。区分度就是某个字段不同值的数量占总行数的比例。性别字段只有两个值，区分度极低，建索引基本没用，因为查询优化器会觉得还不如全表扫。而订单号这种字段几乎每条记录都不一样，区分度极高，建索引收益就非常大。这其实就是为什么我建议所有人在建索引前，先自己对目标字段的区分度有个大概判断。
1.2 为什么是 B+ 树而不是哈希或二叉树
这个问题很多文章都在解释，但我想换个角度，从"为什么实际建索引的时候要考虑这些特性"来理解。
如果你完全不了解底层原理，也能用索引，但遇到很多疑难问题会非常被动。比如你建了 (a,b) 联合索引，查 where b = 1，索引却不生效，你不懂 B+ 树的最左前缀原理，就只能靠背结论。
B+ 树的三个核心特性直接决定了索引怎么设计。第一，非叶子节点不存数据只存键值，加上页目录结构，一个 16KB 的页能装海量键值，三层 B+ 树就够支撑千万行数据，这意味着索引查找的 IO 次数极其稳定。第二，叶子节点通过双向链表串联，形成了天然的有序结构，这使得ORDER BY 和范围查询可以顺着链表顺序扫，不需要额外的排序操作。第三，B+ 树的插入和删除有分裂和合并机制，保证树的平衡性，这意味着索引维护成本是可控且可预测的。
哈希索引能比 B+ 树更快地等值查找，但它对范围查询无能为力，也不支持排序。二叉树在极端情况下会退化成链表，磁盘 IO 次数不可控。B+ 树把等值、范围、排序三种常见查询模式都照顾到了，这也是 MySQL 最终选择它的根本原因。
理解了这些，你就能明白一个非常核心的结论：索引设计的第一原则，是让查询条件尽可能命中最左前缀，同时利用索引天然的有序性来消除额外的排序和回表。后面的所有建索引技巧，本质上都是围绕这句话展开的。
2. 核心细节解析与实操要点
2.1 联合索引的字段顺序，到底怎么排
这个问题网上讨论特别多，但讲清楚的人真不多。我先直接给结论，再解释为什么。
联合索引 (a,b,c) 在 B+ 树里的排序规则是：先按 a 排，a 相同再按 b 排，b 相同再按 c 排。这就导致一个查询要命中这个索引，必须从 a 开始连续匹配，不能跳过中间的字段。这就是最左前缀原则。
基于这个机制，字段顺序的排列规则可以归纳为以下三条。
第一条，区分度高的字段放在最左边。因为联合索引在 B+ 树里是按顺序逐级排序的，最左边的字段决定了第一层分支的区分能力。区分度高的字段前置，能更快缩小扫描范围。比如用户ID和订单状态，通常用户ID前置。
第二条，等值条件放前面，范围条件放后面。为什么？因为联合索引一旦遇到范围条件（比如 >、<、BETWEEN），后续字段就无法用于索引定位了，只能向前扫描。等值条件不会中断索引的连续性，所以先等值后范围，能让前面多个等值字段都参与索引定位，效率最高。
第三条，需要排序的字段放在合适位置。联合索引的天然顺序可以替代 ORDER BY 的排序操作，但前提是你的排序字段必须符合索引的顺序方向。比如索引是 (a,b)，ORDER BY a,b 直接秒杀，ORDER BY b,a 就完全用不上。
我举个例子说明。假设订单表经常执行这样一条SQL：
SELECT * FROM orders WHERE user_id = 123 AND status = 1 AND created_at BETWEEN '2025-01-01' AND '2025-01-31' ORDER BY id;这个查询有三个等值/范围条件和排序需求。基于上面的原则，user_id 和 status 都是等值条件，created_at 是范围条件，所以联合索引应该设计成 (user_id, status, created_at)。至于 id，它本来就是主键，B+ 树的叶子节点上已经带着主键值，不需要专门为它加索引。
注意：把范围条件字段放在联合索引的最后，能最大程度利用索引定位，同时避免一个范围条件把后面所有字段都"废掉"的情况。你只要记住：等值条件随便排，范围条件尽量靠后即可。
2.2 覆盖索引：让查询不再回表
回表是 InnoDB 里一个非常关键的概念。InnoDB 的主键索引（聚簇索引）的叶子节点存的是整行数据，而二级索引的叶子节点只存索引字段和主键值。所以如果你用二级索引查，先找到主键值，再用主键去聚簇索引里找整行数据。这第二次查找就是回表。
回表本身不慢，但如果命中的行数特别多，比如几千上万行，那性能就非常难看了。而覆盖索引的思路是：让查询所需的字段全部包含在索引中，这样直接从二级索引里就能拿到所有数据，根本不需要回表。
举个例子：
SELECT order_no, user_id FROM orders WHERE user_id = 123;如果只建了一个 user_id 的单列索引，那么查出来的每一条记录都要回表去取 order_no。但如果建的是 (user_id, order_no) 联合索引，order_no 就在索引叶子节点上，查询完直接返回，回表彻底消失。
从执行计划上看，Extra 字段如果是 Using index，就说明走了覆盖索引。如果是 Using index condition，说明走了索引下推但没有完全覆盖。如果是 Using where，通常意味着回表后还有过滤。
这个技巧在实际优化中出镜率极高。我经常跟团队说，做查询优化的时候不要光盯着 where 条件，SELECT 后面的字段列表同样值得注意。把高频查询的返回字段一起纳入索引设计，往往能带来意想不到的收益。
2.3 单列索引和条件组合的博弈
很多新手会有个疑问：既然联合索引这么强大，那单列索引是不是完全没有存在价值？也不是。单列索引的价值在于灵活和低成本。
对于一张查询模式非常多样化的表，比如电商的商品表，用户可能按品牌查、按价格查、按分类查，也可能任意两个条件组合查。这种情况下，如果你把每种组合都建一个联合索引，索引数量会爆炸式增长，写放大严重。这时候更务实的方案是给高频独立条件建单列索引，让优化器自己去 index merge 或者选择最优路径。
MySQL 5.6 之后引入了 Index Merge 优化，优化器可以把多个单列索引的扫描结果做交集或并集合并。比如 where keyboard = 'xxx' OR type = 'yyy'，如果两个字段都有单列索引，优化器可能分别扫两个索引再合并。
但这里有个大坑：Index Merge 未必比一个联合索引快，因为它要扫描多个索引，还要做归并，开销并不小。所以对于高频组合查询（比如 where a = 1 AND b = 2），依然应该建联合索引 (a,b)，而不是依赖两个单列索引的合并。
我自己的经验判断是：单列索引适合"查询条件组合多但每种组合频率不高"的场景，联合索引适合"某一组条件经常固定一起出现"的场景。两者不冲突，关键看业务实际怎么查。
3. 实操过程与核心环节实现
3.1 用 EXPLAIN 验证索引是否真正生效
很多同学建完索引之后，最常问的问题是：怎么知道索引到底有没有生效？其实方法极其简单，就是看执行计划。
执行计划就是 SQL 语句执行前的"作战方案"，MySQL 优化器会告诉你它打算怎么查：走哪个索引、扫多少行、需不需要排序、会不会回表。在 SQL 前面加上 EXPLAIN 关键字就能看到。
EXPLAIN SELECT order_no, user_id FROM orders WHERE user_id = 123 AND status = 1;执行结果里，我让所有开发者必须学会看以下四个字段。
| 字段 | 含义 | 我重点关注什么 |
|---|---|---|
| type | 访问类型 | 从好到差依次是 system、const、eq_ref、ref、range、index、ALL，见到 ALL 就说明全表扫描，基本要优化 |
| key | 实际使用的索引 | 看是否用上了我们建的索引，如果为 NULL 说明没用上 |
| rows | 预估扫描的行数 | 数值越小越好，如果 rows 接近表总行数，说明索引基本没起作用 |
| Extra | 额外信息 | Using filesort 意味着要额外排序，Using temporary 意味着临时表，Using index 是覆盖索引，这些都要特别关注 |
比如 EXPLAIN 结果里出现 Using filesort，说明 ORDER BY 没走到索引，查询需要额外做一次排序操作。数据量小还好，数据量大时这个排序的代价可能比查询本身还高，这时候就该考虑把排序字段加入索引。
还有一种情况是 type 是 range，说明走了范围扫描，索引确实生效了，但如果返回行数特别多，回表代价也会很大。这时候需要结合 rows 字段判断，如果 rows 已经是几十万行，说明这个索引虽然生效了，区分度不够，整体性能仍然可能不理想。
提示：EXPLAIN 只是预估，不是实际执行结果。MySQL 8.0 提供了 EXPLAIN ANALYZE，会真正执行 SQL 并输出每个步骤的实际耗时，遇到预估和实际差异大的情况，果断用起来。
3.2 where a and b 组合条件的索引设计实操
回到热搜词里那个高频问题：where 条件是一个 AND b，应该怎么建索引？我用一张用户表来演示完整的实操过程。
假设表结构是：
CREATE TABLE users ( id INT PRIMARY KEY AUTO_INCREMENT, user_name VARCHAR(50), city VARCHAR(50), age INT, created_at DATETIME, KEY idx_city_age (city, age) ) ENGINE=InnoDB;这里我建了 (city, age) 联合索引，理由就是前面说的两条原则：city 和 age 都是等值条件，不是范围；city 的区分度虽然不算超高，但相对稳定，age 放后边做二次过滤。
现在来验证效果：
EXPLAIN SELECT * FROM users WHERE city = '杭州' AND age = 25;执行计划会显示 key 是 idx_city_age，type 是 ref，rows 大幅减少，说明索引生效。因为两个条件都是等值，联合索引能精确定位到一个很小的范围。
再看一个变种，如果条件变成：
EXPLAIN SELECT * FROM users WHERE age = 25 AND city = '杭州';注意，SQL 里条件顺序不影响索引。优化器会做条件重排，所以这个查询一样能命中 idx_city_age。很多新手以为 where 条件顺序和索引字段顺序必须一致，其实完全不是这么回事，优化器不是傻子，它会自动把条件调整到和索引匹配的姿势。
再来看一个反面教材。如果表上只有一个 age 单列索引，没有 city 索引，那么：
EXPLAIN SELECT * FROM users WHERE city = '杭州' AND age = 25;执行计划大概率走上 ALL 全表扫描或者 age 索引后回表再过滤。原因是单列索引无法同时利用两个条件精确定位，最后还是得看优化器的判断。这就是为什么 where 里经常一起出现的字段，要优先考虑联合索引而不是各建各的。
3.3 排序和分组场景怎么蹭索引
ORDER BY 是慢查询的重灾区，因为排序是一个非常昂贵的操作。当数据量大时，MySQL 会把数据加载到内存或磁盘上进行 filesort，代价极高。但如果你能让排序字段直接走索引，filesort 就直接消失了。
什么情况下 ORDER BY 能走索引？核心就一条：排序字段的顺序和方向，必须和索引列完全一致，不能跳列。
举个例子，索引是 (a,b)，下面这些情况走索引，不额外排序：
- ORDER BY a：用最左第一列排序，OK
- ORDER BY a, b：按索引顺序排，OK
- ORDER BY a DESC, b DESC：方向一致且从前往后，也可能走索引，优化器会倒序扫
这些情况不走索引，需要额外排序：
- ORDER BY b：跳过了 a，违反最左前缀
- ORDER BY b, a：顺序反了
- ORDER BY a DESC, b ASC：方向不一致
GROUP BY 和 ORDER BY 底层排序逻辑很像。GROUP BY 也是先分组再聚合，如果能走索引，优化器就直接按索引顺序扫描，天然分好组，性能提升非常明显。
不过要特别注意，GROUP BY 有时候会产生临时表，如果 Extra 显示 Using temporary，说明你要小心翼翼地优化了。比如索引是 (a,b)，执行 GROUP BY a, c，其中 c 不在索引里，MySQL 就需要临时表来分组，这个代价不小。
还有一个小细节：在 InnoDB 里，空字符串和 NULL 在索引中的行为不同，排序时 NULL 默认排在前面。如果你的业务排序要求 NULL 排最后，可能需要特殊处理，但这种场景一般不强求走索引，直接 filesort 反而更简单。
4. 常见问题与排查技巧实录
4.1 索引用不上？先看这五个经典场景
下面这五个场景，是实战中"索引明明建了但就是不生效"的五大元凶，挨个对照排查，基本能解决 90% 的问题。
第一个是对索引列做了运算或函数操作。比如：
SELECT * FROM users WHERE YEAR(created_at) = 2025;YEAR() 函数套在 created_at 上，索引直接失效，因为索引里存的是原始时间值，不是年份值，优化器没法做匹配。正确写法是范围条件：
SELECT * FROM users WHERE created_at >= '2025-01-01' AND created_at < '2026-01-01';第二个是隐式类型转换。比如手机号字段在表里是 varchar，你查询的时候写了数字：
SELECT * FROM users WHERE phone = 13800138000;MySQL 会把 phone 转成数字再比较，索引就用不上了。解决办法是查询时用字符串形式匹配：
SELECT * FROM users WHERE phone = '13800138000';第三个是LIKE 前缀模糊查询。最左前缀原则决定了 'abc%' 能走索引，但 '%abc' 和 '%abc%' 走不了。原因是 B+ 树按前缀有序，但无法从中间字符开始匹配。遇到这种需求，数据量大的话考虑全文索引或 Elasticsearch。
第四个是联合索引没遵守最左前缀。比如索引是 (a,b)，你直接查 where b = 1，索引肯定用不上。这没什么好说的，只能靠改查询或调整索引字段顺序来适配。
第五个是优化器判断走索引不如全表扫描。这是最容易被忽略的情况。当你要查的行数占总行数的比例很大时（一般超过 20%~30%），优化器会认为回表代价比全表扫描更大，索性放弃索引。比如字段区分度太低，或者范围条件太大，都会触发这个判断。
注意：还有隐式字符集不一致的问题。两张表 JOIN 时，如果两个表的关联字段字符集不同（比如 utf8mb4 和 latin1），MySQL 需要做转换，索引可能无法使用。建表时统一字符集，能避免很多隐蔽问题。
4.2 慢查询日志的打开姿势
线上排查慢查询，第一步永远是找到那些拖后腿的 SQL。MySQL 的慢查询日志就是干这个的。
从 MySQL 5.7 开始，慢查询日志默认关闭，需要手动设置。可以在配置文件里写，也可以动态设置：
SET GLOBAL slow_query_log = ON; SET GLOBAL long_query_time = 1; SET GLOBAL log_queries_not_using_indexes = ON;第一行开启慢查询日志，第二行把阈值设成 1 秒，超过 1 秒的 SQL 就会被记录，第三行把"没有用索引的查询"也记进日志。这三条命令对排查最有用，尤其是第三条，能帮你快速发现业务里那些完全没走索引的查询。
日志确定之后，用 mysqldumpslow 工具可以做汇总分析，按照执行次数、耗时排序，快速找到最高频的慢查询。我在实际工作中通常按"总耗时"排序，因为它等于 执行次数 × 平均耗时，总耗时高的 SQL 才是真正影响系统整体性能的元凶。单次虽然不慢，但被调用几千次，积少成多，一样会拖垮系统。
4.3 索引维护：碎片和冗余的清理
索引不是建完就一劳永逸的，它是需要维护的。两个最常见的维护场景是碎片清理和冗余索引排查。
先看碎片。B+ 树在频繁的 INSERT 和 DELETE 后，页会出现碎片，索引变得不紧凑，IO 效率下降。碎片严重时，即使索引逻辑没变，查询性能也可能明显退化。处理方式是执行 OPTIMIZE TABLE：
OPTIMIZE TABLE users;这个命令会重建表，整理数据和索引，把碎片收拢。但注意，它会锁表，大数据量下要挑业务低峰期执行。MySQL 5.7 之后的版本可以在线执行，但仍然会占用不少 IO 和临时空间，千万别在产品高峰期乱跑。
再看冗余索引。很多情况下，(a,b) 联合索引已经覆盖了 a 单列索引的功能，但如果你之前给 a 单独建过索引，那索引就冗余了。冗余索引浪费存储空间，还在每次写入时增加额外维护成本。排查方法也很简单：
SELECT * FROM sys.schema_redundant_indexes;MySQL 5.7 的系统库 sys 自带这个视图，直接列出所有冗余索引，照着删就完事了。删索引之前要仔细核对，确认没有其他 SQL 依赖那个单列索引。
5. 工具选型与优化思路延伸
5.1 执行计划和 EXPLAIN ANALYZE 的实战对比
传统 EXPLAIN 是预估，EXPLAIN ANALYZE 是实测，这两者的区别意味着什么？我举个例子你就懂了。
比如一个查询，EXPLAIN 预估扫描 100 行，实际执行可能扫了 10 万行，因为统计信息没更新，或者数据分布产生了偏移。如果你只看预估，会被表象误导，以为索引没问题，实际上查询已经慢得离谱了。
EXPLAIN ANALYZE 会真正执行 SQL，把每一步操作的实际耗时、实际行数都打印出来，格式是树状的：
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 123 AND status = 1;输出里每行都带着实际耗时和实际行数，你可以精确看到瓶颈在哪一步。是索引查找慢，还是回表慢，还是排序慢，一目了然。
有一个细节要注意：EXPLAIN ANALYZE 是真正执行了查询，所以会真实地消耗资源，也会返回数据。对于只读查询没问题，但对于修改操作（可以对 SELECT 之外的部分语句做 ANALYZE），一定要谨慎，别把线上的 UPDATE 真跑一遍。
5.2 索引下推和索引跳跃扫描
这两个特性了解的人相对少，但实战价值很高。
索引下推（Index Condition Pushdown，ICP）是 MySQL 5.6 引入的。简单说，以前处理 where 条件的时候，先把索引命中的记录都回表取出来，再一条条过滤。有了 ICP 之后，MySQL 会在索引层就先过滤掉不满足条件的记录，减少回表次数。执行计划里 Extra 出现 Using index condition 就说明 ICP 生效了。
索引跳跃扫描（Index Skip Scan）是 MySQL 8.0 引入的优化，适用于联合索引最左列区分度低、中间列或后续列查询频率高的场景。比如索引是 (gender, age)，查询 where age = 25，优化器可以"跳过"gender 直接扫描 age。但注意，这个特性有前提条件，不是所有查询都能用。我在实践中发现，它适用于最左列只有很少几个不同值的情况，如果最左列区分度高，跳跃扫描的无功扫描反而更慢。
这两个特性说明，MySQL 的索引优化能力一直在进化。但这不意味着你可以随便建索引，优化器再智能，也要有合适的索引可以选。
6. 场景速查表与索引设计决策总结
结合所有思路和实操，我把最常用的索引设计判断浓缩成一张速查表。平时写 SQL 或者 review 别人建的索引时，对照这张表过一遍，大方向基本不会跑偏。
| 业务场景 | 推荐的索引设计策略 | 设计背后的核心原因 |
|---|---|---|
| where a = 1 AND b = 2 | 联合索引 (a, b) | 等值条件连续匹配，精确定位到最小范围 |
| where a = 1 AND b > 10 | 联合索引 (a, b)，a 前置 | 等值条件参与索引定位，范围条件做边界扫描 |
| where a = 1 ORDER BY b | 联合索引 (a, b) | 索引天然有序，直接消除 filesort |
| SELECT a, b WHERE a = 1 | 联合索引 (a, b) 或覆盖索引 | 查询字段全部在索引中，不需要回表 |
| 多个独立条件随机组合 | 高频条件分别建单列索引 | 让优化器灵活选择或 index merge，避免索引爆炸 |
| 低区分度字段如性别、状态 | 不单独建索引 | 回表代价大于全表扫描，走索引反而更慢 |
| 高频率 UPDATE 的表 | 只保留必要索引，尽量合并 | 每个索引都是额外的 B+ 树维护成本，写放大严重 |
| like 'abc%' 查询 | 普通索引 | 前缀匹配符合 B+ 树有序性 |
| like '%abc%' 查询 | 全文索引或搜索引擎 | 中间匹配无法走 B+ 树索引，只能扫描 |
这张表背后的逻辑很集中：索引设计永远围绕减少扫描范围、消除回表、消除排序这三个目标展开。任何一个索引方案，你都可以拿这三个标准来检验它是不是最优解。
7. 写在最后：一次真实调优的回顾
我在实际工作中最常说的一句话是：先看执行计划，再谈索引设计。分享一个我印象很深的案例。一张订单表有 800 万行查询条件是 WHERE status = 1 AND created_at BETWEEN 某天 AND 某天，原始 SQL 跑了 2.3 秒。加了 (status, created_at) 联合索引后降到 40 毫秒，但执行计划仍然显示有回表。我又把索引改成 (status, created_at, order_no) 覆盖索引，彻底消除了回表，耗时稳定在 12 毫秒左右。
这个案例的核心其实不是联合索引和覆盖索引本身多神奇，而是每一步优化都建立在执行计划反馈的基础之上。加索引之前先看 EXPLAIN 的 type、rows、Extra 三个字段，加索引之后再对比一次，用数据说话，而不是凭感觉优化。
最后补一个小技巧：MySQL 8.0 的 EXPLAIN ANALYZE 比传统 EXPLAIN 更直观，可以直接看到实际耗时。遇到慢 SQL，先开慢查询日志拿到 SQL，跑一遍 EXPLAIN ANALYZE，锁定瓶颈，再动手改索引或 SQL，最后回归验证。这套流程我已经用了好几年，大概率你也会觉得好用。
