在MySQL这条进阶路上，索引就是那个"一懂全懂、一卡全卡"的知识节点。前期写SQL可能没太大感觉，等数据量一上来、线上查询变慢，你回头看执行计划时才发现，当初建表时随手写的几个索引到底有多重要。这篇文章想系统性地把索引这条线拉通：从数据结构出发，讲到复合索引、最左前缀、索引失效、覆盖索引，再到实际设计索引的具体套路和排查思路。不管你是刚能熟练写增删改查的开发，还是已经在负责表结构设计的同学，这篇都值得边看边在自己库里试一遍。
我平时做性能和调优相关的工作，索引相关的坑踩了不少。有一类问题反复出现：线上一个查询跑几十秒，DBA过来一看，要么是索引压根没建，要么是建了但因为写法不对导致索引失效。说到底，索引不是建了就完事，理解它工作的底层逻辑，你才知道每个索引该怎么建、SQL该怎么写。这篇我就按自己的理解和实操经验来说清楚，尽量不说废话。
1. 为什么索引是MySQL性能的核心
先聊点"为什么"。MySQL本质上就是一个存储和检索数据的系统，而检索速度的快慢，直接决定了业务接口的响应时间。在没有索引的情况下，InnoDB要找到一行数据，只能做全表扫描——也就是把整张表的数据页从磁盘搬出来，逐行比对。假设一张表有500万行，平均每行200字节，那就是约1GB的数据量。哪怕InnoDB有缓冲池帮忙缓存热点页，第一次查询的磁盘IO也够喝一壶了。
索引的本质，是拿额外的存储空间和维护开销，换取查询时的磁盘IO次数大幅下降。这个交易在很多场景下是划算的。比如有个简单的等值查询：
SELECT * FROM user WHERE phone = '13800138000';如果phone列上没有索引，MySQL会扫描主键索引的叶子节点，逐行比对phone字段值。这里的叶子节点存储的是整行数据，每页能放的行数是有限的。假设每页16KB、每行约200字节，那么一页大概能放80行，500万行就需要6万多页。就算一次IO能读一页，你也要做6万多次逻辑读。如果加了phone的普通索引，情况就变成了：先通过辅助索引定位到主键值，再回表查一次。辅助索引的叶子节点只存索引列和主键值，假设phone字段占11字节、主键占8字节，加上其他开销，一页能放的行数多得多，树的高度可能只有3层。也就是说，你只需要3次左右的磁盘IO就能定位到目标记录。这个差距，就是索引带来的核心收益。
MySQL的索引结构是B+树，不是二叉树，也不是哈希表。这个选择背后有几个非常实际的考量。二叉树的树高和数据量成对数关系，看起来还行，但实际存储时每个节点只有一个键值，当数据量到千万级别时，树高会达到20多层；而InnoDB每次从磁盘读数据是按页读的，一次IO对应一个节点，20多层意味着最坏情况下要20多次磁盘IO，这在机械硬盘时代是不可接受的。哈希表做等值查询确实快，O(1)复杂度，但它天生无法支持范围查询和排序。你执行一个WHERE age BETWEEN 20 AND 30，哈希表只能逐个枚举，没有任何加速手段。B+树的兄弟叶子节点之间用双向链表串联，范围查询和排序就变得非常顺手。数据量千万级时B+树通常也就3到4层，根节点和中间层节点因为常被访问，几乎都能被缓冲池缓存，真正每次都走磁盘IO的只有最后一层叶子节点。这个设计使得绝大多数查询在IO层面都极其可控。MySQL最终选择B+树作为索引结构，本质上是从磁盘IO的特性出发做的工程决策——尽量减少随机IO，让顺序IO和缓存命中率发挥最大作用。
2. 索引的底层结构：InnoDB到底在玩什么
2.1 B+树为什么长这样
B+树和B树的区别，很多人背过但没有真正理解。B树在每个节点上都存数据，而B+树只在叶子节点存数据，非叶子节点只存索引键值。这个差异直接影响了两件事。第一，非叶子节点能容纳更多的键值，树更矮，IO次数更少；第二，叶子节点通过链表相连，方便范围扫描。用个生活化的类比，B树像一本每页都有完整目录的书，B+树像图书馆的索引卡柜，目录卡只告诉你在哪一排书架，具体书的位置要到最后一层卡片才写清楚，所有的卡片又用一根绳子串起来，从第一张顺到最后一张就能按顺序走完。
InnoDB的主键索引即聚簇索引，叶子节点直接存储整行数据。辅助索引的叶子节点存储的是索引列值和主键值。这是InnoDB最核心的设计，也是后边很多优化技巧的源头。比如覆盖索引就是让查询所需的列全部在辅助索引里能找到，省掉回表那一次IO；又比如主键为什么建议用自增整数而不是UUID，就是因为辅助索引叶子节点存的是主键值，主键越长，辅助索引越大，IO开销越高；UUID还是无序的，插入时会导致页分裂，产生大量碎片。
2.2 聚簇索引和辅助索引的区别
这张表最好记清楚：
| 对比项 | 聚簇索引（主键索引） | 辅助索引（二级索引） |
|---|---|---|
| 叶子节点存储内容 | 整行数据 | 索引列值 + 主键值 |
| 每张表数量 | 只有1个 | 可以有多个 |
| 回表需求 | 不需要 | 需要（除非覆盖索引） |
| 数据物理排序 | 按主键排序 | 按索引列排序，主键值做辅助 |
关键点在于，所有辅助索引的叶子节点都带主键值。也就是说，你给一张表建了5个索引，等于多存了5份主键值的冗余数据。这也是索引数量不宜过多的原因之一——写放大很严重。插入一条数据，不仅要更新主键索引的B+树，还要同步更新所有辅助索引的B+树。所以MySQL里有一个常见建议：单表索引数控制在5个以内，不是没有道理的。你加了索引查询变快了，但在高并发写入场景下，每个索引的维护都是额外开销。
2.3 页分裂和索引碎片
数据插入B+树时，如果某个叶子页已经满了，就必须申请新页并将一半数据搬过去，这个过程叫页分裂。页分裂本身是正常的，但如果分裂频繁，会造成数据页的物理存储不连续，产生碎片。碎片率高了以后，即使逻辑上相邻的数据，物理上也隔得很远，顺序扫描的性能会显著下降。典型场景就是主键用UUID或业务随机字符串。UUID是无序的，每次插入都可能落在B+树中间的某个位置，触发页分裂。而自增主键的插入永远追加在末尾，极少触发分裂。所以在设计表结构时，我通常会建议尽量用自增整型做主键，除非有分库分表的全局唯一ID需求，再用雪花算法这类有序的分布式ID方案。
3. 复合索引与最左前缀：面试必问，实战也必用
3.1 复合索引的底层逻辑
复合索引是指在一个索引中包含多个列，比如idx_user_age_name (age, name)。它的排序规则是先按第一个列排序，第一个列相同的再按第二个列排序，依此类推。这个"先按谁排"的顺序，决定了索引的适用范围。
在实际建索引之前，先理解一个核心原则：复合索引的设计要尽量让查询条件里的列能"用上"索引的顺序，且不要跳跃。MySQL的优化器在做索引选择时，会从复合索引的最左列开始匹配，一直匹配到范围查询或等值查询结束。如果查询条件里没有包含最左列，那这个复合索引基本发挥不了作用，优化器大概率会放弃它，选择全表扫描或者其它索引。
3.2 最左前缀原则的具体表现
到底什么写法能命中索引，什么写法不能？我用一张表说清楚。假设有一张订单表：
CREATE TABLE `order_info` ( `id` bigint NOT NULL AUTO_INCREMENT, `user_id` bigint NOT NULL, `status` tinyint NOT NULL DEFAULT '0', `order_time` datetime NOT NULL, `amount` decimal(10,2) NOT NULL, PRIMARY KEY (`id`), KEY `idx_user_time` (`user_id`, `order_time`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;现在有这些查询：
-- 能命中，user_id是最左列 SELECT * FROM order_info WHERE user_id = 1001; -- 能命中，user_id等值匹配，order_time范围匹配 SELECT * FROM order_info WHERE user_id = 1001 AND order_time > '2024-01-01'; -- 不能命中，缺少user_id SELECT * FROM order_info WHERE order_time > '2024-01-01';最后一条为什么不能命中？因为复合索引的最左列是user_id，如果查询条件里没有user_id，那么索引B+树中order_time列的排序是建立在user_id相同的前提下的，直接按order_time范围查找时，优化器没法利用索引的有序性，只能全表扫描。这是最左前缀原则最容易踩的坑。
3.3 覆盖索引：让查询连回表都省掉
覆盖索引值得单独拿出来说，因为它的收益太直观了。一条SQL如果查询的列都包含在辅助索引的叶子节点中，那么查询就不需要回表，直接遍历辅助索引就能拿到全部结果。
还是拿上面的订单表举例：
-- user_id、status、order_time这三列都在idx_user_status_time里 -- 不需要回表，Extra会显示Using index SELECT user_id, status, order_time FROM order_info WHERE user_id = 1001 AND status = 1;为了达到覆盖索引的效果，我在设计索引时会有意识地把查询中高频出现的列塞进索引里。但这里有一个代价：索引列越多，占用空间越大，写入维护成本越高。所以覆盖索引不是无脑堆列，而是针对高频查询做精细化设计。有一个常见的做法是，把一个查询里反复出现的列做成联合索引，即使它们原本不是经常一起出现在WHERE条件里，只要能让这条高频查询省掉回表，就值得。
3.4 索引下推：MySQL 5.6之后的隐形优化
索引下推（ICP，Index Condition Pushdown）是一个容易被忽略但实际影响很大的优化。在没有ICP的情况下，InnoDB通过辅助索引找到记录后，需要回表，然后在服务层对WHERE条件里的其它列做过滤。有了ICP之后，存储引擎会在使用索引遍历时，直接对索引中包含的列做条件过滤，减少回表次数。
举个例子：
SELECT * FROM user WHERE name = '张三' AND age > 20;假设有复合索引(name, age)，在MySQL 5.6之前，存储引擎通过name定位到所有张三的记录，先回表再过滤age > 20的记录。在5.6及之后，存储引擎在索引遍历时就会判断age是否符合条件，不符合的直接跳过，不需要回表。数据量大的时候，这个优化能把IO减少一大截。理解ICP的好处在于，你会更倾向于设计让过滤条件尽可能落在索引列上的复合索引，让下推机制发挥最大效果。
4. 索引失效的典型场景：这些坑我基本都踩过
4.1 索引失效清单
索引失效是个高频面试题，但很多答案只列了现象，没解释原因。我根据实际排查经验，把常见的失效场景整理成下表，并说明为什么失效：
| 场景 | 示例 | 失效原因 |
|---|---|---|
| 对索引列使用函数 | WHERE YEAR(create_time) = 2024 | 函数改变了列值，B+树的有序性失效 |
| 对索引列做隐式类型转换 | WHERE phone = 13800138000 | phone是varchar，数字会被转成字符串，索引失效 |
| 模糊查询前置通配符 | WHERE name LIKE '%张三%' | 通配符在前，无法利用B+树的按前缀匹配特性 |
| 联合索引不满足最左前缀 | WHERE order_time > '2024-01-01'（缺user_id） | B+树先按最左列排序 |
| 使用OR连接非索引列 | WHERE user_id = 1 OR status = 2 | OR会拆成多个条件，需要多个索引做合并，可能退化 |
| 查询条件里的列做了运算 | WHERE age + 1 = 30 | 运算改变了列的原始值 |
| NOT IN、NOT LIKE、!= | WHERE status != 1 | 不等于无法匹配B+树的等值/范围查找 |
| 范围查询后的列无法继续走索引 | WHERE user_id = 1 AND create_time > '2024-01-01' AND status = 1（status在范围后） | 范围查询后索引有序性被破坏 |
这里面有几点需要单独解释。隐式类型转换是特别隐蔽的坑，因为MySQL有时能自动转换，有时不能。比如phone = 13800138000，MySQL会把字符串列和数字比较时，将字符串转换成数字再做比较。比较值全部发生转换后，索引列本身无法直接匹配，优化器只能放弃索引。解决方式很简单：应用层传参时保持类型一致，或者SQL里写成字符串形式。
OR有一个特殊情况：如果OR连接的多个条件列都各自有索引，MySQL可以用索引合并（Index Merge）来优化，不一定全表扫描。但索引合并本身成本不低，还需要额外的排序去重开销，性能通常不如直接设计一个复合索引来得干净。所以遇到OR，优先考虑改写SQL或合并索引。
4.2 为什么范围查询后的列会失效
这是理解索引失效最容易混淆的地方，我想拆开说透。复合索引（a, b, c）的B+树排序规则是：先按a排序，a相同的按b排序，b相同的按c排序。当你执行WHERE a = 1 AND b > 2 AND c = 3时，MySQL能利用索引找到a=1的所有记录，然后在其中找到b>2的记录。但这些b>2的记录里，c列并不一定是有序的。原因在于c的排序是在b相等的前提下才成立的，而b > 2是一个范围，在这个范围内b值不等，c的有序性就无从谈起了。所以c条件只能在索引范围内做过滤，无法继续用B+树跳跃查找，优化器通常就不再把这个条件作为索引访问的定位条件了。
这给设计索引提供了一个非常重要的思路：在复合索引中，把等值条件的列放在前面，范围条件的列放在后面。等值条件可以帮助索引精确定位，范围条件只需要一个就够。如果所有条件都是等值，顺序主要看区分度和查询频率；如果有范围条件，范围条件后的其它列设计索引时基本不用考虑了，它们只能作为普通过滤条件存在。
4.3 一个实际的失效排查
我之前接手过一个项目，线上有个慢查询总是超时。当时的SQL简化后类似：
SELECT * FROM pay_record WHERE pay_time >= '2024-03-01' AND merchant_id = 888 AND amount > 100;表结构里已经有一个索引idx_pay_time，按pay_time建的。结果执行计划显示全表扫描。为什么？因为数据表里有上亿条记录，pay_time范围内命中的记录数可能占到全表的30%以上，优化器一算回表成本，觉得还不如全表扫描来得快。这就是一个容易被误判为索引失效的典型案例——索引其实可以用，但选择性太低，优化器主动放弃。
后面我把索引改成idx_merchant_pay_time (merchant_id, pay_time)，查询条件里merchant_id本来就是高区分度的列，就这么一个小改动，查询从6秒降到了0.1秒。这里我想强调的是：判断索引有效性不能只看"能不能走索引"，还得看优化器算出来的成本。高区分度列放前面，等于帮优化器缩小了检索范围。
5. 索引设计实战：从where条件反推索引
5.1 where a and b到底怎么建索引
这个热搜词出现的频率极高，算是MySQL索引设计中最经典的问题。WHERE a = ? AND b = ?这种情况，到底建单列索引还是复合索引？
我的结论是：优先建复合索引。原因有三点。第一，复合索引可以直接通过最左前缀同时利用a和b两个列，单列索引只能利用一个，另一个需要回表过滤或索引合并。第二，复合索引天然支持覆盖索引，可以把SELECT需要的列塞进索引里。第三，单列索引两个都要维护，写入时索引更新的开销翻倍。
具体建索引时有一个微调原则：区分度高的列放前面。如果a的区分度极低，比如只有0和1两个值，b的区分度很高，那么(a, b)和(b, a)效果差距会很大。因为如果a区分度低，即使先按a定位，命中数据量依然很大，B+树在第二层过滤的效果会打折扣；而先按b定位，很快就能收敛到少量记录，a再作为过滤条件就很轻松。
假设一张表有100万行，a字段有2个不同值，b字段有10万个不同值：
(b, a)：先按b定位，每个b值大约对应10行，再按a过滤，成本极低；(a, b)：先按a定位，命中50万行，再按b继续查找，虽然B+树也能处理，但第一层就暴露了50万行的范围，IO和CPU成本明显高。
所以实操时，我会先跑几条SQL看区分度：
SELECT COUNT(DISTINCT a) / COUNT(*) AS a_cardinality, COUNT(DISTINCT b) / COUNT(*) AS b_cardinality FROM my_table;区分度接近1的放前面。这里的逻辑其实和前面提到的B+树排序规则完全一致：越能快速收敛的列，越应该排在索引的前面。
5.2 排序查询与索引：避免filesort
MySQL中排序如果可以用索引，就直接按B+树的顺序读取数据，Extra显示Using index。如果不能，就需要在内存或磁盘上做排序操作，也就是filesort。filesort在小数据量时无所谓，但数据量一大就会产生临时文件和额外IO。
比如有一张订单表，有个高频查询：
SELECT id, user_id, amount FROM order_info WHERE user_id = 1001 ORDER BY order_time DESC LIMIT 20;如果索引是(user_id, order_time)，那么user_id等值定位后，order_time天然有序，排序操作直接省掉，MySQL只需要从索引里倒序取20条记录。这就是"索引本身就帮你排好序"的好处。如果把ORDER BY的列换成amount，索引必须回表后重新排序。
所以设计索引时不能只看WHERE条件，ORDER BY也是很重要的索引驱动因素。我的设计顺序是：等值条件列放前面，ORDER BY列紧跟其后，范围条件列再往后。这样可以最大化利用索引的有序性。
5.3 主键索引和唯一索引的区别
热搜词里有一个问得很细的问题：主键索引和唯一索引有什么区别？日常很多人混着用，但两者的语义和实现并不相同。
| 对比项 | 主键索引 | 唯一索引 |
|---|---|---|
| 每张表数量 | 只能有一个 | 可以有多个 |
| 是否允许NULL | 不允许 | 允许，且允许多个NULL |
| 是否作为聚簇索引 | InnoDB中默认是 | 只能是辅助索引 |
| 用途 | 行唯一标识 | 业务唯一约束 |
这里有一个值得注意的点：在InnoDB中，如果表没有显式主键，第一个非空唯一索引会被当作聚簇索引使用。这在有些场景下会带来隐藏问题。如果你的唯一索引列是无序字符串（比如身份证号），它被当成聚簇索引后，插入时会产生大量的页分裂和碎片。所以即便业务上有唯一约束的列，我还是建议单独建自增主键，唯一约束用辅助唯一索引实现，这样物理存储的连续性有保障。
另外，唯一索引在查询执行计划上和普通索引没有本质区别，但写入时多一步唯一性检查。高并发写入场景下，如果业务上能接受短暂的不一致，可以考虑用普通索引替代唯一索引来提升写入性能。不过这个取舍需要结合具体业务，不能拍脑袋。
6. 索引选择的代价与常见问题排查
6.1 索引不是越多越好
索引数量这个话题，在生产环境里特别容易出问题。很多开发同学的做法是，来了一个新查询就加一个索引，半年后一张表上挂了十几个索引。查询确实快了，但写入变慢、磁盘占用变大、缓冲池命中率下降，最终引发更大的性能问题。
索引的代价要从三个维度看。第一是写入代价：每插入一条记录，所有索引都要更新。假设一张表有5个索引，插入一条记录，等于维护5棵B+树的插入操作；如果赶上页分裂，这个开销会进一步放大。第二是存储代价：辅助索引的叶子节点存索引列和主键，5个索引就是5份冗余数据。第三是优化器代价：索引多了以后，MySQL优化器在选择执行计划时要评估更多候选路径，评估成本也会上升。更关键的是，优化器的选择有时候并不总是最优的。如果统计信息不准确，或者数据分布出现倾斜，优化器可能会选到一条很差的路径。我们就遇到过一张表明明有索引，但执行计划里选了全表扫描的情况，排查到最后发现是优化器基于过时的统计信息做了错误判断。
所以索引设计要遵循一个原则：用最少的索引覆盖最多的查询模式。一张表里高频的查询如果有三四种，尽量让这些查询都能共用一到两个复合索引，而不是每种查询各建一个。这是索引设计中最容易被忽视的取舍。
6.2 常见问题速查与排查套路
我把平时排查索引问题时最常用的几个动作整理成清单，直接照着做就行。
第一，先看执行计划。执行EXPLAIN SELECT ...，关注type、key、rows、Extra四列。type从好到坏依次是system、const、eq_ref、ref、range、index、ALL。如果type是ALL，说明没走索引；如果是index，说明走了索引但扫的是全索引；Extra中出现Using filesort或Using temporary，说明排序或分组没有利用到索引。
第二，确认索引是否真的被用上。有时候key列显示用了索引，但rows依然很大，说明索引选择性差。这时应该检查索引列是否有区分度，或者是不是查询条件本身写得不合理。
第三，复核索引列有没有被函数、运算、类型转换"污染"。这是最常见的失效原因。排查方法很直接：把WHERE条件里的函数去掉，或者改成对常量做运算，比如WHERE create_time > DATE_SUB(NOW(), INTERVAL 7 DAY)，而不是WHERE DATE(create_time) = CURDATE()。
第四，分析是慢在回表还是慢在扫描。如果explain的Extra显示Using index condition，说明走了索引下推，但还有回表；如果Extra显示Using index，说明覆盖索引。线上查询追求的理想状态是Using index。
第五，统计信息不准的问题。MySQL 8.0里可以手动执行ANALYZE TABLE来更新统计信息，或者考虑调大innodb_stats_persistent_sample_pages参数。这一招在处理优化器选错索引时非常有效。
6.3 一张SQL的索引优化前后对比
最后放一个完整的优化案例，把上面的思路串起来。假设有一张商品订单表，数据量约2000万行，原SQL：
SELECT order_id, buyer_name, amount, create_time FROM trade_order WHERE status = 1 AND buyer_id = 9527 AND create_time > '2024-06-01' ORDER BY create_time DESC LIMIT 50;原索引是idx_status_create_time (status, create_time)。执行计划的type是ref，rows约80万行，查询耗时3.2秒。问题很明显：status区分度低，它的选择性只有几个值，索引第一列几乎起不到过滤作用。
我把索引改成idx_buyer_create (buyer_id, status, create_time)，buyer_id区分度很高，等值匹配后直接定位到几百行，create_time再用来排序。改完以后执行计划type为ref，rows显示496，查询耗时降到0.08秒。这个案例在之前的线上排查中也遇到过类似的场景，区别只在于表名不同。
这里的经验是：高区分度列做索引的前导列，永远优先于"看起来业务上更重要"的列。等你把Explain用熟练了，你会发现大部分慢查询的根源就是索引列顺序设计得有问题。
7. 我的一些实操体会
索引优化的水很深，但核心逻辑并不复杂：理解B+树的排序规则，顺着它的性子设计索引，SQL写法上不去破坏索引的有序性。我建议你在自己的测试库里面多练几次Explain，把一个复合索引拆成不同顺序，观察rows的变化，这种感觉会比看多少篇文章都来得实在。另外，线上环境加索引前最好在低峰期操作，用pt-online-schema-change这类工具做在线变更，避免大表锁表时间过长。这一步在真正处理生产环境问题时，能帮你避开很多不必要的麻烦。
