1. 索引的本质：先搞清楚 InnoDB 到底是怎么找数据的
1.1 没有索引的年代：全表扫描到底有多慢
很多同学刚开始学 MySQL 的时候，对索引的印象就是“查询变快了”，但到底为什么快、快在哪个环节，其实没细想过。我们先回到最朴素的问题：如果一张表没有主键、没有索引，MySQL 要执行一条SELECT * FROM t WHERE name = '张三'，它会怎么找？
它只能从表的第一行数据开始，一行一行往后面扫，把每一行的name字段取出来跟'张三'做比较，直到把整张表扫完。这个操作在 InnoDB 里叫全表扫描，底层就是遍历聚簇索引（后面会讲到）的叶子节点链表。假设表里有 500 万行，平均每行 200 字节，那光是把数据页从磁盘读到内存，就要读差不多 1GB 的量。哪怕 MySQL 做了缓冲池的预读优化，第一次查询的延迟也会很难看，而且这个成本会随着表行数线性增长。
打个比方：你想在一本 500 页的词典里找“索引”这个词，如果词典没有目录、没有页码，你只能从第 1 页开始逐页翻，翻到第 300 页才找到。而索引就是那套带页码的目录，你通过目录直接定位到目标页码，再翻过去看正文，效率天差地别。
这就是索引要解决的核心问题：把线性查找变成树形查找，把扫描行数从“全表行数”降到“树的深度级别”。InnoDB 的 B+树一般只有 3 到 4 层，意味着哪怕表里有几千万行，定位一条记录也只需要走 3 到 4 次磁盘 I/O。这个差距，就是索引价值的全部来源。
1.2 B+树、聚簇索引与二级索引：回表为什么能回出性能问题
InnoDB 的索引底层是 B+树，这点大家应该都听过。但有几个关键细节，是理解后续所有索引优化问题的基础。
第一个细节：InnoDB 的表本身就是一棵 B+树。这张表的 B+树按照主键顺序组织，叶子节点存的是完整的一行数据。在主键上建立的索引，就是聚簇索引，它决定了数据在磁盘上的物理排列。换句话说，你建表时如果没有显式声明主键，InnoDB 也会悄悄帮你选一个唯一的非空列作为聚簇索引；如果没有这样的列，它会生成一个隐藏的ROWID来当聚簇索引。
第二个细节：你在其他列上建的所有索引，都属于二级索引。二级索引的 B+树叶子节点不存整行数据，只存索引列的值加上主键值。所以当你用二级索引查询时，大体上分两步：
- 先在二级索引的 B+树里找到匹配的记录，拿到主键值。
- 再用主键值去聚簇索引的 B+树里查一遍，取回完整的行数据。
这第 2 步，就叫回表。
回表本身不是问题，问题在于回表的次数。如果一条 SQL 通过二级索引筛出了 1 万条记录，而查询需要的是全部字段，那就要回表 1 万次。哪怕每次回表都是走主键 B+树查找，这个累计成本也非常可观。这也是为什么很多慢查询并不是没走索引，而是走了索引之后回表回得太狠。
理解了这两个细节，再看后面要讲的覆盖索引、索引下推，就会觉得非常顺理成章。覆盖索引的思路就是：让二级索引的叶子节点直接覆盖查询需要的所有字段，省掉回表；索引下推的思路就是：在二级索引扫描阶段，先把能过滤的条件过滤掉，减少回表次数。
关于 B+树为什么不用红黑树或者哈希索引，这里简单提一句：红黑树是二叉树，数据量一大树就很高，磁盘 I/O 次数多；哈希索引虽然单条等值查询接近 O(1)，但做不了范围查询，也做不了排序。B+树节点可以存很多键值，树矮、扇出大，而且叶子节点有序链表天然适合范围扫描，所以 InnoDB 选它做默认索引结构。
2. 设计索引前必须想清楚的问题
2.1 哪些列适合建索引：区分度是核心指标
聊完了索引的原理，我们来谈设计。很多人拿到建表需求，在WHERE条件里出现的字段随手就加个索引，这不叫设计，这叫碰运气。判断一个字段适不适合建索引，第一个要看的就是区分度，也就是这一列有多少种不同的值。
区分度的计算方式很简单：COUNT(DISTINCT column) / COUNT(*)。这个值越接近 1，说明这一列的值越分散，索引筛选效果就越好。比如订单表里的order_no订单号，每条记录唯一，区分度就是 1，非常适合建索引。而status状态字段，如果你只有“待支付、已支付、已退款、已关闭”四种取值，在几千万行的表里，每个状态平均对应几百万行，这时候你单独在status上建索引，实际效果是很差的。
为什么？因为优化器在决定走哪个索引之前，会先估算扫描行数。如果它认为走索引需要访问的行数占全表比例太高（一般是超过 20% 到 30%），它会直接放弃索引，选择全表扫描，因为全表扫描的顺序读比索引的离散读更快。这个判断依据，就是 MySQL 基于表统计信息算出来的。
所以在实际工作里，我建索引前会先跑一条统计 SQL，看看候选字段的区分度：
SELECT COUNT(DISTINCT status) AS status_distinct, COUNT(DISTINCT pay_type) AS pay_type_distinct, COUNT(DISTINCT order_no) AS order_no_distinct, COUNT(*) AS total FROM trade_order;如果某个字段的COUNT(DISTINCT)除以COUNT(*)长期低于 10% 甚至 5%，那它单独建索引的价值就很低，更好的做法是把它放到联合索引里，跟高区分度字段组合使用。
另外一个要考虑的点是更新频率。索引不是免费的，每次对该列做INSERT、UPDATE、DELETE，InnoDB 都要同步维护对应的 B+树结构，更新越频繁的列，索引维护成本越高。如果一个字段频繁被更新，但查询场景又很少，那这个索引就是在给写入操作拖后腿。我见过不少业务表，为了某一个低频报表需求，给十几个字段都加了索引，结果日常写入慢得一塌糊涂，得不偿失。
2.2 联合索引与最左前缀：顺序比数量更重要
联合索引（也叫复合索引）是另一个高频考点，也是日常开发中最容易用错的地方。很多人知道联合索引遵循“最左前缀”原则，但不知道这个原则的本质是什么。
联合索引在 B+树里的存储规则是：先按照联合索引的第一个字段排序，第一个字段相同的情况下，再按第二个字段排序，依此类推。这个排序规则决定了你查询条件里必须有第一个字段，索引才能生效；如果查询条件直接跳过第一个字段，只用了第二个字段，那么 B+树的排列方式对这次查询毫无帮助，就像一本先按“姓氏笔画”排再按“名字笔画”排的通讯录，你只知道人家名字而不知道姓氏，翻起来就很痛苦。
举个例子，假设在(status, pay_type, create_time)上建了联合索引，那么这棵 B+树会先把所有status=1的数据聚到一起，在status=1这部分里再按pay_type排序，最后在status=1且pay_type=2这组数据里按create_time排序。
所以这个索引能支持的查询组合是：
WHERE status = 1WHERE status = 1 AND pay_type = 2WHERE status = 1 AND pay_type = 2 AND create_time > '2024-01-01'WHERE status = 1 ORDER BY create_time
但如果直接查WHERE pay_type = 2，这个联合索引是用不上的，因为 B+树最左边一层根本没按pay_type排序，优化器没法快速定位。
有一点容易遗漏：范围查询会让右侧的索引列失效。同样是(status, pay_type, create_time)联合索引，WHERE status = 1 AND pay_type > 2 AND create_time > '2024-01-01'这条 SQL 里，create_time的等值过滤条件是用不上索引的，因为pay_type一旦是范围判断，B+树里在pay_type相同条件下再排的create_time顺序就没法继续用于筛选了。所以在设计联合索引时，通常把等值条件的列放前面，范围条件的列放后面。
还有一点很容易被忽视：联合索引可以用来优化排序。比如ORDER BY create_time DESC这个排序操作，如果没有合适的索引，MySQL 会把查出来的数据放到临时文件里做 filesort，也就是额外排序。而如果你建的联合索引顺序正好覆盖了排序条件，B+树天然就是有序的，优化器可以直接顺序读取，连排序都省了。我在实际优化慢查询时，经常靠这一步把几秒钟的查询压到几十毫秒。
3. 索引失效的几种典型场景，踩过的人都懂
3.1 你以为走了索引，其实在悄悄全表扫描
索引失效是面试高频题，也是线上事故高发区。很多 SQL 刚写完时跑得很快，等数据量涨上去突然就慢了，一查执行计划，发现索引压根没走。这里把我踩过的坑整理一遍，基本覆盖了绝大多数失效场景。
第一种是对索引列做了函数或运算。比如：
SELECT * FROM trade_order WHERE DATE(create_time) = '2024-06-01';这张表在create_time上建了索引，但DATE()函数把每一行的create_time都先处理一遍再做比较，B+树里存储的是原始值，没有经过DATE()处理，自然没法直接定位，只能全表扫描。正确的写法是改成范围条件：
SELECT * FROM trade_order WHERE create_time >= '2024-06-01 00:00:00' AND create_time < '2024-06-02 00:00:00';第二种是隐式类型转换。最常见的是字符串列跟数字比较。比如order_no是VARCHAR类型，但你写WHERE order_no = 123456，MySQL 会自动把order_no转成数字再比较，这本质上也是对索引列做了函数操作，索引照样失效。除了改写 SQL 匹配字段类型，也可以用EXPLAIN观察有没有发生类型转换。
第三种是前导模糊查询。LIKE '%关键字'这种写法，因为 B+树是按最左前缀排序的，你连第一个字符都不知道，索引自然无从定位。但LIKE '关键字%'是可以走索引的，因为它有确定的前缀。
第四种是OR 条件里存在非索引列。WHERE status = 1 OR remark = '加急'，如果remark上没有索引，优化器没法对两个条件分别走索引再合并，往往只能全表扫描。解决办法是把 OR 改写为UNION ALL，或者给remark也加上合适的索引。
第五种是联合索引不满足最左前缀。前面已经详细解释过，这里不再重复。但有一个容易被忽略的变体：联合索引中间跳过了一列，比如(a, b, c)联合索引，查询条件只写WHERE b = 1 AND c = 2，因为没带a，整个索引用不上；只写WHERE a = 1 AND c = 2，则只能用上a的部分，c的过滤是在索引扫描之后完成的。
第六种是对索引列做了隐式字符集转换。两个表关联时，如果关联字段的字符集不一样，比如一个表是utf8mb4，另一个表是utf8，MySQL 为了比较，会把两边都转成兼容性更好的utf8mb4，对索引列做了一次隐式转换，索引就失效了。这个坑在联表查询里很隐蔽，不容易发现，需要检查两个表的字符集设置是否一致。
3.2 EXPLAIN：让优化器告诉你它到底怎么走的
排查索引问题，第一步永远是看执行计划，也就是EXPLAIN。很多新手不会看，或者只会看key字段是不是 NULL，这远远不够。我一般重点看这几列：
type：访问类型，从好到差大致是system > const > eq_ref > ref > range > index > ALL。如果看到ALL，说明全表扫描；index表示走了索引但扫描的是整个索引树，也不一定快；range是范围扫描，通常可以接受；ref和eq_ref是等值关联的常见类型，是健康的。key：实际使用的索引名。如果key为 NULL，说明没有走索引。rows：优化器估算的需要扫描的行数，这个值越小越好，但也只是个估算值。Extra：里面有很多重要信息。看到Using filesort说明查询里有排序操作没走索引；看到Using temporary说明可能用了临时表，要警惕；看到Using index说明用了覆盖索引；看到Using index condition说明走了索引下推。
举个例子，一条 SQL 加上EXPLAIN之后，输出可能是这样的：
EXPLAIN SELECT order_id, amount FROM trade_order WHERE status = 1 ORDER BY create_time DESC LIMIT 10;如果结果里type是ALL，Extra里有Using filesort，那基本可以断定：status的区分度太差，优化器放弃索引，同时排序也吃不上索引。这种时候要考虑的就不是“为什么索引失效”这么简单，而是整个查询方案需要重新设计。
EXPLAIN不要只在本地小数据量上跑。小表里优化器可能觉得全表扫描也无所谓，等上了生产环境，数据量大了，执行计划可能完全不一样。更稳妥的做法是在操作的库上用真实数据量验证，或者用EXPLAIN ANALYZE（MySQL 8.0 支持）看实际执行时间和扫描行数。
4. 实战复盘：一次订单慢查询的索引调优全过程
4.1 从慢日志到执行计划，定位问题只需要三步
前面讲了理论，接下来用一条真实场景的 SQL 串一遍完整的调优流程。我先描述一下业务背景：一张trade_order订单表，大概有 800 万行数据，字段包含order_id（主键）、order_no、user_id、status（1 待支付、2 已支付、3 已退款、4 已关闭）、pay_type（1 微信、2 支付宝、3 银行卡）、create_time、amount等。某个后台管理页面需要查询“所有已支付且用支付宝支付的订单，按创建时间倒序，取最近 20 条”。
最初的 SQL 是这样的：
SELECT order_id, order_no, user_id, amount, create_time FROM trade_order WHERE status = 2 AND pay_type = 2 ORDER BY create_time DESC LIMIT 20;上线初期这张表只有几十万行，跑得还挺快。等数据量到几百万行之后，页面开始卡顿，查询基本要 2 到 3 秒。我去查慢查询日志，发现这条 SQL 排在最前面，于是开始排查。
第一步，看执行计划：
EXPLAIN SELECT order_id, order_no, user_id, amount, create_time FROM trade_order WHERE status = 2 AND pay_type = 2 ORDER BY create_time DESC LIMIT 20;结果里type是ALL，key是 NULL，rows估算大概 300 多万行，Extra里有Using filesort。也就是说，这条查询在 800 万行的表上做了全表扫描，还额外做了一次排序，不慢才怪。
第二步，分析原因。表里原本有个idx_status单列索引，但status = 2的记录可能占全表的 40% 左右，优化器一估算，走索引扫描 300 万行再回表，比全表扫描还慢，于是干脆放弃索引。而pay_type没有索引，排序字段create_time也没有被任何索引覆盖，所以 filesort 也躲不掉。
第三步，决定方案。既然status区分度差、pay_type区分度也一般，但两者组合起来区分度会好很多，同时create_time还需要参与排序，所以合理的方案是建一个联合索引：
ALTER TABLE trade_order ADD INDEX idx_status_pay_type_create_time (status, pay_type, create_time);注意我把create_time放在最后面。因为status和pay_type是等值条件，放在联合索引前两位，可以让 B+树先按这两个字段精确定位到一批记录，这批记录内部已经按create_time排序，直接顺序取 20 条就行，连排序都省了。
建完索引后，再跑一次EXPLAIN，type变成了range，key是idx_status_pay_type_create_time，rows估算只有几十行，Extra里的Using filesort消失了。实际查询时间从 2 秒多降到了 20 毫秒以内。这一步就是联合索引同时优化过滤和排序的典型收益。
4.2 覆盖索引与索引下推：回表次数还能再省
上面那条 SQL 虽然已经很快了，但仔细看SELECT的字段，里面有order_no、user_id、amount这些不在联合索引里的列。也就是说，虽然定位到记录很快，但每一条命中的记录，存储引擎都要回到聚簇索引里取一次完整的行，再把需要的字段捞出来。
如果这个查询的频率非常高，回表成本依然不能忽视。怎么优化？两个思路。
第一个思路是覆盖索引。如果查询经常只需要固定的几个字段，可以考虑把字段都塞进索引里，让二级索引的叶子节点直接覆盖查询需求，省掉回表。比如：
ALTER TABLE trade_order ADD INDEX idx_status_pay_type_create_time_cover (status, pay_type, create_time, order_no, user_id, amount);当然这样做会让索引变得很大，写入成本也更高，不能滥用，只适合高频、固定字段、数据量大的查询场景。在EXPLAIN里，如果Extra列出现Using index，就说明这条查询走了覆盖索引，没有回表。
第二个思路是索引下推（Index Condition Pushdown，ICP），这是 MySQL 5.6 引入的特性，默认开启。它的作用是：在二级索引扫描阶段，把WHERE条件里凡是能用索引列判断的条件，直接下推到存储引擎层过滤掉，减少回表次数。
举个例子，联合索引是(status, pay_type, create_time)，查询条件是WHERE status = 2 AND pay_type = 2 AND create_time > '2024-01-01'。如果没有 ICP，存储引擎会先根据status = 2 AND pay_type = 2定位到一批二级索引记录，全部回表取完整行，再在 Server 层过滤create_time。有了 ICP，create_time > '2024-01-01'这个条件会在扫描二级索引时直接过滤，只有满足条件的记录才回表，回表次数大幅减少。EXPLAIN的Extra列会显示Using index condition，看到这个就说明 ICP 正常工作了。
这里想强调一个理念：索引优化不是建完就结束了，建完之后一定要用EXPLAIN确认执行计划是不是按预期走的，最好再对比优化前后的耗时和rows估算值。很多时候，你以为建了索引就有用，实际因为字段顺序不对、范围条件位置不对、查询条件写法不对，索引根本没被用上，白建了。
5. MySQL 8.0 里那些被低估的索引新特性
5.1 不可见索引：删不掉的历史包袱怎么处理
我们继续说“新”这个字。MySQL 8.0 在索引方面有几个非常实用的新特性，能解决不少老版本里的头疼问题。
第一个是不可见索引。它的作用是让优化器“看不见”某个索引，但索引本身还保留着，仍然会随数据更新而维护。这个特性最典型的场景是：你想删掉一个可能还有用的旧索引，但担心删了之后线上查询突然变慢，甚至引发事故。老版本里只能先确认、提工单、半夜上线删除，非常被动。有了不可见索引，你可以先把索引设为不可见，观察一段时间，确认没有查询受影响，再真正 DROP；如果发现有问题，立刻恢复可见，相当于一个带保险的索引下线方案。
语法很简单：
-- 让索引对优化器不可见 ALTER TABLE trade_order ALTER INDEX idx_status INVISIBLE; -- 恢复可见 ALTER TABLE trade_order ALTER INDEX idx_status VISIBLE;有一点要注意：不可见索引在优化器眼里是“不存在”的，但数据写入时仍然要维护它，所以它帮你承担了排查风险，却没有帮你节省任何写入开销。它是个安全开关，不是性能优化工具。
5.2 函数索引、降序索引与索引跳跃扫描
第二个是函数索引。前面提到对索引列使用函数会导致索引失效，MySQL 8.0 直接给出了标准解法：你可以在表达式上建索引。这个特性在 8.0.13 之后还支持JSON列上提取字段做索引，对于日志类、配置类表非常有用。
-- 在 DATE(create_time) 上建立函数索引 ALTER TABLE trade_order ADD INDEX idx_create_date ((DATE(create_time)));建完这个索引之后，WHERE DATE(create_time) = '2024-06-01'就可以走索引了。这个特性本质上是 MySQL 在后台帮你创建了一个虚拟列，然后在这个虚拟列上建索引，所以你不需要自己额外维护一列。
第三个是降序索引。老版本 MySQL 的 B+树索引理论上只支持升序存储，你写ORDER BY create_time DESC，优化器通常需要做反向扫描，如果同时有升序和降序的排序需求，情况会更麻烦。MySQL 8.0 真正支持了降序存储的索引。比如一个联合索引要同时支持ORDER BY a ASC, b DESC，你就可以写成：
ALTER TABLE trade_order ADD INDEX idx_status_pay_type_create_time_desc (status, pay_type, create_time DESC);这样 B+树里create_time直接从大到小排，查询倒序时就可以正向扫描索引，省掉 filesort。
第四个是索引跳跃扫描（Index Skip Scan）。这个特性在 MySQL 8.0 里让某些不满足最左前缀的查询也可以用上联合索引了。它的原理是针对联合索引的第一个字段区分度很低的情况，优化器自动枚举第一个字段的各个不同值，然后分别用剩余字段去索引里查，最后合并结果。比如联合索引(status, pay_type, create_time)，查询条件只有WHERE pay_type = 2，如果status的取值只有四种，优化器会模拟扫描四个status值，分别走(status=1, pay_type=2)、(status=2, pay_type=2)等组合。
但要说清楚的是，Index Skip Scan 只有在第一个字段区分度很低时才划算，如果第一个字段有几百上千个不同值，枚举成本比全表扫描还高，优化器依然会放弃。它不是万能药，正确姿势仍然是按照最左前缀去设计联合索引，只是多了一条兜底路径。
6. 高频问题与避坑速查
6.1 一张表看懂索引失效与优化方向
我把日常开发和线上排查中最常碰到的问题整理成了一张速查表，按照“场景 → 现象 → 原因 → 处理建议”的格式罗列，方便大家直接对照排查。
| 场景 | 现象 | 根本原因 | 处理建议 |
|---|---|---|---|
| 对索引列使用函数 | 执行计划key=NULL | 索引存储的是原始值，函数处理后无法定位 | 改写为范围条件，或 MySQL 8.0 使用函数索引 |
| 字符串列与数字比较 | 索引失效 | 隐式类型转换对索引列做了函数处理 | 统一字段类型，或在 SQL 里把值写成字符串 |
LIKE '%关键字' | 索引失效 | B+树依赖最左前缀，起点未知 | 改前缀匹配，或考虑全文索引 |
| OR 条件含非索引列 | 全表扫描 | 无法对两个条件分别走索引再合并 | 改写为UNION ALL，或补齐索引 |
| 联合索引未遵守最左前缀 | 部分或全部索引失效 | B+树排序规则导致后续列无法定位 | 调整查询条件，或重新设计索引列顺序 |
| 联合索引中间列是范围查询 | 右侧列失效 | 范围右侧的排序无法继续筛选 | 把等值列放前，范围列放后 |
| 关联字段字符集不一致 | 联表查询变慢 | 隐式字符集转换使索引失效 | 统一两表字段字符集 |
ORDER BY字段没有索引覆盖 | Extra出现Using filesort | 排序需要额外排序步骤 | 建包含排序字段的联合索引 |
| 单列索引区分度太低 | 索引被优化器放弃 | 估算扫描行数占比过高 | 改为联合索引，或业务层面缩小范围 |
这张表是我每次做索引优化的基础检查清单。遇到慢查询先别急着加索引，拿执行计划跟这张表比对一下，往往能直接命中问题点。
6.2 几条从实践中攒下来的心得
最后说几条比较碎但很实用的经验。
第一，建立联合索引时，把区分度最高的列放最前面通常是对的，但也不是绝对。如果某个等值列本身就放在查询条件里，而且过滤后数据量会大幅缩小，哪怕区分度不是最高，放在前面也能让 B+树的定位更精准，还能避免 Index Skip Scan 的尴尬情况。重点还是要结合真实业务的查询模式去判断，多跑几次 EXPLAIN 对比。
第二，不要迷信覆盖索引。覆盖索引省掉了回表，确实很诱人，但你把大量字段塞进索引之后，索引体积膨胀，缓冲池里能放下的索引页就会减少，反而可能导致更多磁盘 I/O。一定要权衡查询频率和索引成本，只对高频查询做覆盖索引。
第三，查看SHOW INDEX FROM table里的 Cardinality 字段。这个值代表索引中不同值的估算数量，如果它跟表行数差很远，说明索引的区分度可能有问题，或者表统计信息太久没更新，可以执行ANALYZE TABLE刷新一下。统计信息不准，优化器就容易做出错误判断。
第四，考虑页面端口，这个细节很容易被忽略：Order by 的排序方向如果和索引的存储方向完全相反，MySQL 需要反向扫描索引，性能虽然也不错，但如果你经常需要混排（一部分升序、一部分降序），一定要想到 8.0 的降序索引，这个问题我在低版本 MySQL 上踩过，优化器硬是走 filesort，怎么调都不对。
第五，删除索引要谨慎。以前我们排查一个 MySQL 5.7 的慢查询，发现一个冗余索引占了很大空间，于是 DROP 掉，结果第二天线上一个报表查询突然超时。原因是我们忽略了一个情况：那个索引虽然看起来冗余，但在某种特定查询里，优化器就是用它的顺序避免 filesort。现在遇到类似场景，我会先把它设置为 INVISIBLE，观察一个完整的业务周期，确认无影响后再删。
第六，关于主键索引的设计。InnoDB 的聚簇索引叶子节点存整行数据，主键是 B+树的排序键。自增主键能让新记录顺序插入，减少页分裂；而业务订单号、UUID 这类随机字符串做主键，数据写入时会导致页分裂和碎片，写入性能差很多。除非你有非常明确的业务理由，否则建议用自增整数作为主键。
索引是个值得反复打磨的东西。我个人的习惯是，每写完一条复杂 SQL，都会习惯性跑一下EXPLAIN，确认它是ref还是range，有没有出现Using filesort，这些细节看多了，对优化器的理解会越来越准，踩坑概率也会明显降低。
