做SQL优化这些年，我最大的体会就是：索引这东西，谁都会建，但真正能用好的人不多。很多人一遇到慢查询，第一反应是"加个索引"，结果加了之后发现查询还是慢，甚至更慢了；去面试的时候被问"为什么InnoDB要用B+树"、"联合索引最左前缀到底是怎么回事"，又讲不清楚。这篇文章我就把自己在实际工作中对 MySQL 索引的理解、踩过的坑、以及性能调优的经验完整梳理一遍，不绕弯子，直接把最关键的原理和实操讲透。
这篇文章适合三类人：刚入行想系统搞懂索引原理的新人，写完 SQL 总被领导说"慢"的开发者，以及准备 MySQL 面试、想真正搞懂索引底层而不是背八股的同学。看完之后你至少能解决三个问题：索引为什么能加速查询、索引在什么场景下会失效、以及面对一条慢 SQL 时如何设计出合理的索引。
1. 索引的本质：为什么一棵B+树能让查询快几个数量级
1.1 索引到底解决了什么问题
先想一个朴素的问题：如果没有索引，MySQL 执行一条WHERE查询是怎么做的？答案是全表扫描——从表的第一行开始，一行一行地比对字段值，直到找到所有满足条件的记录。假设表里有 1000 万行数据，平均要找 500 万行才能命中，这个成本是线性增长的，数据量越大越慢。
索引的作用，就是把这种"线性查找"变成"树形查找"。这就好比查字典：没有索引等于从第一页翻到最后一页，有索引等于先查拼音或者偏旁部首定位到大概位置，再翻过去精确定位。对于 B+树这种数据结构，1000 万条记录只需要大概 3 层就能定位到具体的数据页，每次查找的磁盘 IO 次数就是树的层数，几乎是个常数。
1.2 为什么偏偏是B+树，哈希、二叉树、红黑树不行？
很多人背过结论"MySQL 用 B+树"，但对为什么不用其他结构一头雾水。这里我把几个候选结构的对比讲清楚，理解了这一点，面试问原理你就能自己推出来。
哈希索引：按哈希值直接定位，单条等值查询确实快，O(1) 的复杂度。但哈希表的致命缺陷是无法支持范围查询，WHERE age > 20 AND age < 30这种 SQL 直接歇菜。而且哈希值是无序的，排序也没法走索引。所以哈希索引只适合 Memory 引擎和 InnoDB 的"自适应哈希索引"这种特定场景，不能作为通用存储结构。
二叉树 / 红黑树：虽然能支持范围查询，但树的高度太高。二叉树在最坏情况下退化成链表，红黑树虽然是平衡的，但一个 1000 万节点的红黑树高度约是 23 层。这里有个关键点：InnoDB 是磁盘存储引擎，树的每一层对应一次磁盘 IO，树越高，查询次数越多。23 次磁盘 IO 和 3 次磁盘 IO 的差距，在机械硬盘时代是数量级的差别。
B树： B 树把二叉树变成了多叉树，每个节点可以存多个键值，树的高度大大降低，这是进步。但 B 树的每个节点都存数据，导致单个节点存不了多少索引键，而且 B 树的叶子节点之间没有指针连接，做范围查询时还得回到父节点去寻找相邻的叶子节点，效率没有 B+树高。
最后看 B+树的设计，简直是为磁盘存储量身定做的：
- 非叶子节点只存索引键，不存数据，所以一页（默认 16KB）能放下非常多的键，树的高度控制得很低。一个 3 层的 B+树能存上千万条记录。
- 所有数据都存在叶子节点上，并且叶子节点之间用双向链表串起来，范围查询和排序走链表的顺序访问，效率极高。
- 每一次节点访问就是一次磁盘 IO，而节点的大小正好对应一页的 16KB，和磁盘 IO 的最小单位对齐。
这个"为什么用 B+树"是索引问题里最值得掰开揉碎讲的，因为它决定了你对后续所有索引行为的理解深度。
1.3 InnoDB和MyISAM的索引存储差异
同样是索引，不同存储引擎在实现上有本质区别。MyISAM 的索引和数据是分开存的，索引文件（.MYI）里叶子节点存的是指向数据行的物理地址，这叫做非聚簇索引。InnoDB 的聚簇索引（主键索引）的叶子节点直接保存整行的数据，数据和索引是一体的。
这个差异直接导致了一个重要结论：InnoDB 表必须有主键。没有显式主键，InnoDB 也会找一个非空的唯一键，再找不到就隐式生成一个 6 字节的 rowid 当作主键。这也是为什么业界一直强调"InnoDB 表要建自增主键"——因为聚簇索引的叶子节点按主键值有序排列，自增可以保证插入走末尾，减少页分裂和碎片。
2. 索引的分类：主键索引、二级索引、复合索引到底怎么区分
2.1 按功能划分的四大索引类型
先看最实用的分类维度——按照索引的功能和约束来分，这也是建表时经常碰到的。
| 索引类型 | 特点 | 典型应用场景 |
|---|---|---|
| 主键索引（PRIMARY KEY） | 唯一且非空，一个表最多一个 | 每张表的核心标识字段，如用户表 id |
| 唯一索引（UNIQUE） | 索引列的值必须唯一，允许 NULL | 手机号、身份证号、订单号等业务唯一字段 |
| 普通索引（INDEX） | 没有任何唯一性约束，只是加速查询 | 频繁出现在 WHERE、JOIN、ORDER BY 中的字段 |
| 全文索引（FULLTEXT） | 用于全文检索，对文本内容分词匹配 | 文章内容、商品描述等大文本字段的模糊搜索 |
主键索引和唯一索引的区别经常有人搞混：主键索引本质上是"非空的唯一索引"，而且一个表只能有一个主键，但可以有多个唯一索引。唯一索引允许 NULL，但需要注意，MySQL 中唯一索引对 NULL 做了特殊处理——多个 NULL 值可以共存，因为 NULL 不等于任何值，包括它自己。
2.2 聚簇索引与非聚簇索引：回表问题的根源
前面提到了聚簇索引和非聚簇索引，这里把概念补完整。
聚簇索引（clustered index）：在 InnoDB 里特指主键索引，叶子节点存的是完整的数据行。因为数据行和索引存在一起，所以表的数据物理顺序是按主键排序的。
非聚簇索引（secondary index，也叫二级索引）：叶子节点不存完整数据，只存索引列的值 + 主键值。
这里有个非常重要的推论：通过二级索引查数据，需要先查二级索引找到主键值，再用主键值去聚簇索引里查找整行数据。这一步就叫回表。
举个例子。表结构是(id PRIMARY KEY, name, age)，你在name字段上建了普通索引。执行SELECT * FROM t WHERE name='张三'时，MySQL 的完整动作是：
- 在 name 索引树中搜索，找到 '张三' 对应的主键值 id；
- 拿着 id 去主键索引（聚簇索引）中搜索，找到完整的数据行返回。
这就多了一次 B+树搜索。如果查询只涉及name和id两个字段，就可以直接利用索引里存的值返回，不用回表，这就是后面要说的覆盖索引优化。
2.3 联合索引与最左前缀原则（高频面试点）
联合索引也叫复合索引，是指在一个索引中包含多个列，比如INDEX idx_name_age (name, age)。
为什么需要联合索引？因为一个查询如果同时用两个条件，单独建两个单列索引的效果很不理想。MySQL 8.0 之前虽然有 index merge 优化，但更多时候是让优化器从多个单列索引里挑一个用，另一列回表过滤；而联合索引一棵树同时照顾了两个条件，效率完全不同。
联合索引的核心规则是最左前缀原则：idx_name_age在查找时，会先按 name 字段排序，name 相同的再按 age 排序。所以：
WHERE name = '张三'—— 走索引，因为 name 是最左列；WHERE name = '张三' AND age = 20—— 走索引，两个条件都用上了；WHERE age = 20——无法走索引，因为跳过了最左列 name。
最左前缀原则对于一个从业者来说不仅是理论，更是设计的直接依据：联合索引的字段顺序，决定了这个索引能不能被你的 SQL 用上。如果你把age放前面、name放后面，那对WHERE name = ?这条高频 SQL 来说，索引就白建了。
那排序到底该遵从什么顺序？两个最实用的参考规则：一是把等值查询的字段放在前面，二是按照区分度从高到低排。等等，这里有冲突怎么办？比如 name 区分度高、age 区分度低，但查询时 name 是等值条件、age 是范围条件，这种情况下建议优先把等值条件字段放前面。这个规则后面在"索引设计经验"里细化。
3. 索引操作全解：创建、查看、删除与实操避坑
3.1 创建索引的五种正确姿势
实际建索引的 SQL 写法有好几种，我按常用程度列一下，并说清各自的适用场景。
-- 方式1：建表时直接定义索引（适合新表） CREATE TABLE user ( id BIGINT PRIMARY KEY AUTO_INCREMENT, phone VARCHAR(20), name VARCHAR(50), age INT, KEY idx_phone (phone), UNIQUE KEY uk_phone (phone), INDEX idx_name_age (name, age) ); -- 方式2：ALTER TABLE 添加索引（适合已有表加索引） ALTER TABLE user ADD INDEX idx_age (age); ALTER TABLE user ADD UNIQUE INDEX uk_phone (phone); -- 方式3：CREATE INDEX 独立建索引（最直观，推荐日常使用） CREATE INDEX idx_name ON user (name); CREATE UNIQUE INDEX uk_phone ON user (phone); CREATE INDEX idx_name_age ON user (name, age); -- 方式4：前缀索引（针对长字符串字段） -- 只对字符串的前 N 个字符建索引，大幅减少索引体积 CREATE INDEX idx_content_prefix ON article (content(20)); -- 方式5：降序索引（MySQL 8.0+ 支持，应对倒序排序场景） CREATE INDEX idx_age_desc ON user (age DESC);这里我特别想提醒一个实操上的点：在大表上建索引要格外小心。MySQL 8.0 支持了在线 DDL（ALGORITHM=INPLACE），建索引期间不会阻塞正常读写，但也不是完全没有代价。选择业务低峰期操作、观察主从延迟、建完立即用 EXPLAIN 验证是否命中，这三步缺一不可。我之前遇到过建一个大索引导致主从延迟到分钟级的案例，后来都是先在从库上建好、再切换主从，安全很多。
3.2 复合索引的创建原则：一个索引顶三个索引
为什么说复合索引设计得好，一个索引可以顶三个？因为最左前缀原则的存在，INDEX(a, b, c)能同时覆盖(a)、(a,b)、(a,b,c)三种查询条件组合。这意味着在设计联合索引时，能用"一个复合索引 + 合适字段顺序"解决的问题，就绝不为每个单列单独建索引，否则既浪费空间，又拖慢增删改的速度。
我总结一个复合索引字段顺序的决策流程，你可以直接照着做：
- 先看等值查询：所有
=条件字段，按区分度从高到低排列； - 再看范围查询：范围字段（
>、<、BETWEEN、LIKE）放在等值字段之后，因为范围条件之后的其他列无法用于定位，只能做过滤； - 最后看排序字段：如果 SQL 里要 ORDER BY 某个字段，而这个字段不在查询条件里，可以把它加在索引末尾，让索引直接提供排序结果，避免 filesort。
这个决策流程里排最后一条的话，ORDER BY 的使用要具体情况具体分析，通常是在它的出现恰好落在最左前缀的末尾时，能达到覆盖排序的效果。我自己建索引的习惯是先模拟跑一遍 EXPLAIN，看 key、rows、Extra 三列，再决定字段要不要调整。EXPLAIN 不会骗人，它是索引设计里最重要的反馈工具。
3.3 查看和删除索引：别建了一堆没用的
-- 查看表上的所有索引 SHOW INDEX FROM user; -- 查看建表语句（同时也包含索引信息） SHOW CREATE TABLE user; -- 删除索引 DROP INDEX idx_name ON user; -- 或者（等价） ALTER TABLE user DROP INDEX idx_name;SHOW INDEX FROM user的结果里，Cardinality 这一列比较重要，它表示索引中不同值的基数估算。Cardinality 除以表的行数可以粗略地判断索引的区分度。如果区分度太低（比如性别字段），这个索引基本没什么用，查询优化器大概率不会选它。如果你发现一个有索引的字段执行计划里还是 full table scan，先看这个索引的 Cardinality，很可能是统计信息过期或者本身区分度太差。
另外分享一个我常用的检查脚本：把数据库中所有索引查出来，看看有没有重复索引。重复索引的症状是：idx_name和idx_name_age都存在，其中 idx_name 就是冗余的，因为 idx_name_age 的最左前缀已经覆盖了 name 单列查询。这种重复索引除了浪费空间，还会让 INSERT、UPDATE 的维护成本翻倍，清理掉对性能有明显正面效果。
4. 索引失效的十大场景：每一个都是真实踩过的坑
4.1 索引失效场景全清单
这里把我在实际排查 SQL 慢查询时最常见的索引失效原因系统整理一遍。每一条都是真金白银的教训，建议收藏。
| 序号 | 失效场景 | 错误示例 | 正确写法 |
|---|---|---|---|
| 1 | 联合索引违反最左前缀 | WHERE age = 20而索引是 (name, age) | 优先保证最左列 name 在条件中 |
| 2 | 对索引列使用函数或计算 | WHERE LEFT(name, 1) = '张' | 改写为WHERE name LIKE '张%' |
| 3 | 隐式类型转换 | 索引列是字符串，条件传了数字 | 保持类型一致，或显式 CAST |
| 4 | LIKE 以通配符开头 | WHERE name LIKE '%张%' | 能换成'张%'就换，否则考虑全文索引 |
| 5 | OR 条件中有非索引列 | WHERE age = 20 OR status = 1 | 用 UNION 拆成两个查询 |
| 6 | 范围查询右边列失效 | 索引 (age, name) 且WHERE age > 18 AND name = '张' | 把等值列放前面，范围列放后面 |
| 7 | NOT IN / NOT EXISTS | WHERE status NOT IN (1,2) | 改写为 LEFT JOIN 或 EXISTS 的肯定形式 |
| 8 | IS NULL / IS NOT NULL | 索引列IS NOT NULL造成全扫 | 给字段设默认值，避免 NULL 判断 |
| 9 | 排序与索引顺序不一致 | ORDER BY age DESC 而索引是 (age ASC) | 建降序索引，或让排序方向一致 |
| 10 | 优化器判定全表扫更便宜 | 返回行数超过表行数的 20% 左右 | 优化 SQL 逻辑，缩小结果集 |
4.2 逐个拆解失效原因与规避方案
函数和计算导致的失效是初学者最容易踩的坑。WHERE DATE_FORMAT(create_time, '%Y-%m-%d') = '2024-01-01'看着没毛病，但函数包裹了索引列之后，B+树里存的是原始值，根本无法按函数处理后的结果做二分查找，索引自然就废了。正确写法是把范围条件转换到列本身：WHERE create_time >= '2024-01-01 00:00:00' AND create_time < '2024-01-02 00:00:00'。MySQL 8.0 虽然引入了函数索引（CREATE INDEX idx ON t ((DATE(create_time)))），但那是建在函数表达式上，不是对已有列搞函数转换。
隐式类型转换是我见过线上事故率最高的坑。表里 phone 列是 VARCHAR，但代码里传参写成了数字，WHERE phone = 13800138000，MySQL 会把字符串列转成数字去比较，索引直接失效。排查这种问题有个小技巧：执行EXPLAIN SELECT * FROM user WHERE phone = 13800138000，看 type 列，如果是 ALL 或 rows 特别大，优先怀疑类型不一致。把 SQL 改成WHERE phone = '13800138000'，索引立刻就能用上。
OR 条件失效很多人不理解，觉得两边都有索引为什么不用。MySQL 的处理逻辑是：OR 要返回结果集的并集，如果其中一个条件没法走索引，就必须全表扫才能得到完整结果。所以要么保证 OR 两侧都有索引且能命中，要么改写为 UNION：
-- 改写前（status 无索引，导致整个条件全表扫） SELECT * FROM t WHERE name = '张' OR status = 1; -- 改写后（两个查询各走各的索引） SELECT * FROM t WHERE name = '张' UNION SELECT * FROM t WHERE status = 1;范围查询右侧列失效的逻辑稍微绕一点。联合索引(age, name)，SQL 是WHERE age > 18 AND name = '张三'。因为 age 是范围条件，B+树定位到 age > 18 这个区间后，在这个区间内 name 是无序的，无法用二分继续定位，只能逐条过滤。所以范围字段之后的列，在索引里"有等于无"，这就是"最左前缀 + 范围列终结"原则。
4.3 优化器的心思：为什么有时候有索引却不用
最后一个场景最气人：明明字段有索引，EXPLAIN 一看还是全表扫。但这其实是优化器的成本决策，不一定是索引坏了。比如你要查WHERE sex = '男'，如果表里 90% 都是男性，走索引找完 90% 的行，再回表读取，成本反而比全表扫高。优化器一估算，直接全表扫完事。
这种情况怎么破？有几个思路：一是查一下SHOW INDEX里的 Cardinality 是不是统计信息过期，执行ANALYZE TABLE t刷新统计；二是看 SQL 里是否真的能缩小结果集，比如加时间范围；三是用FORCE INDEX强制走索引试试，但这个要谨慎，只在充分验证后使用。大多数时候，问题不在索引而在 SQL 本身需要优化。
5. 索引优化实战：覆盖索引、索引下推与MRR的底层提速逻辑
5.1 覆盖索引：一条SQL免掉回表，查询速度直接起飞
覆盖索引的定义很简单：查询的列和 WHERE 条件都能在二级索引里找到，不需要回表。因为二级索引的叶子节点存了索引列和主键值，只要查询涉及这些列，返回结果可以直接从索引里取。
举个例子。表t(id, name, age)，有索引idx_name (name)。执行：
SELECT id, name FROM t WHERE name = '张三';这个查询需要的就是 name（索引列本身）和 id（叶子节点存了主键值），所以执行计划里 Extra 会显示Using index，表示不需要回表，代价非常小。但如果SELECT * FROM t WHERE name = '张三'，要取 age 字段，索引树里没有，就必须回表。
实际优化时，我经常用覆盖索引来"降维打击"慢查询：把高频查询涉及的字段塞进联合索引里，让整个查询全程走索引，不回表。比如一个订单表，高频查询是按用户查订单号和金额，那就建(user_id, order_no, amount)联合索引，查询直接全部覆盖，线上性能提升非常明显。
5.2 索引下推（ICP）：减少回表次数的隐形优化
索引下推（Index Condition Pushdown）很多人不理解，其实一句话就能讲明白：本来要回表之后才能用 WHERE 条件过滤的行，现在在索引遍历的时候就直接过滤掉了。
看例子：联合索引(name, age)，SQL 是SELECT * FROM t WHERE name LIKE '张%' AND age = 20。
MySQL 5.6 之前（没有 ICP），执行流程是：先在索引里找到所有 name 以 '张' 开头的记录的主键，然后逐个回表，回表之后再用 age = 20 过滤。如果 name 前缀匹配了 1 万行，就要回表 1 万次，再过滤。
有 ICP 之后：在遍历二级索引时，发现索引里同时有 age 字段，直接判断 age 是否等于 20，不满足的就跳过，只有满足的才回表。这样回表次数从 1 万次降到几十次。
对用户来说，ICP 是 MySQL 默认开启的优化，不需要手动干预。但你知道了这个原理，就能理解为什么联合索引把过滤字段加全、比建完索引再拿所有行去回表过滤要高效得多。
5.3 MRR优化：把随机IO变成顺序IO
MRR（Multi-Range Read）的核心思想是：回表的时候不要一条主键一次随机 IO，而是把一批主键排序后，顺序去聚簇索引里读数据。磁盘顺序读比随机读快几个数量级，所以 MRR 对大量回表的场景优化效果很可观。
MRR 由 MySQL 自动判断是否启用，受mrr=on和mrr_cost_based=on控制。当二级索引返回的主键值本身比较分散时，MRR 收益很大；如果二级索引扫描本身就是有序的，MRR 反而会带来额外排序开销，优化器会根据成本决定是否启用。理解 MRR 的价值在于：它和覆盖索引是两套互补的思路，覆盖索引是干脆避免回表，MRR 是让回表变快。
5.4 索引设计黄金法则：来自真实案例的总结
写到最后，把我在多个线上项目里总结出的一套索引设计规则整理出来。这不是面试八股，是能直接落地的经验。
- 单表索引数量控制在 5 个以内（不含主键），太多会导致写放大严重；
- 区分度低于 20% 的字段不要单独建索引，性别、状态这种只能作为联合索引的一部分；
- 长字符串用前缀索引，但要测出合适的前缀长度，比如区分度最接近完整列的长度；
- 频繁更新的字段慎建索引，每一次 UPDATE 都要同步维护索引树；
- 无法避免 IS NULL 判断时，给字段设置默认值，让索引列"非 NULL"化；
- 索引设计必须结合真实 SQL，凭空设计索引不如不设计，用慢查询日志 + EXPLAIN 驱动索引调整；
- 每建一个索引，都要问自己：这个索引能不能覆盖/消除现有的某个索引，避免冗余。
6. 高频踩坑实录与索引面试题速查
6.1 我亲身经历过的三个索引事故
分享三个真实踩坑经历。第一个是把索引建错了方向，一个统计报表 SQL 按时间倒序查询，索引是升序的，结果每次查询都触发 filesort，几百万行数据排序直接卡死。后来在 MySQL 8.0 里改成降序索引，秒级出结果。当时没意识到 ORDER BY DESC 和 ASC 对索引的利用完全不一样，这是最常见的性能陷阱之一。
第二个是联合索引字段顺序拍脑袋定的，上线后发现最核心的查询走了全表扫。原因很简单，SQL 里用的是 B 字段等值 + A 字段范围，而我们把 A 放前面、B 放后面，范围列左侧的字段没法用。最后调整索引顺序为(B, A)，问题立刻消失。字段顺序不是写的时候顺手排的，而是要对着真实 SQL 的需求排。
第三个是全表数据量只有几万行，但 WHERE 字段建了索引却始终不走。后来发现是表的字符集是 utf8mb4，代码里传参连接字符串时被转成了 latin1，导致隐式类型转换 + 字符集不一致双重失效。排查了很久才发现。连接参数和表字符集不一致也会导致索引失效，这个点特别隐蔽，分享给各位避坑。
6.2 面试官最爱问的索引问题（附回答要点）
把面试里最高频的索引问题整理成速查表，供快速回顾。
| 问题 | 回答要点 |
|---|---|
| 为什么 InnoDB 用 B+树不用哈希/红黑树 | 磁盘 IO 次数，树高、范围查询、页大小对齐 |
| 聚簇索引和二级索引的区别 | 叶子节点是否存整行数据，二级索引要回表 |
| 什么是最左前缀原则 | 联合索引按定义顺序匹配，跳过最左列会失效 |
| 覆盖索引怎么避免回表 | 查询列和条件都包含在索引里，Extra 显示 Using index |
| 什么是回表和索引下推 | 回表是二次查找；ICP 是索引层提前过滤，减少回表 |
| 索引怎么设计才算合理 | 区分度 + 等值在前 + 范围在后 + 避免冗余 |
| 大表加索引要注意什么 | 在线 DDL、低峰期、从库先行、验证执行计划 |
关于索引失效场景，再补充两个容易被忽略的：一是ORDER BY字符串字段时，如果没有与索引列顺序一致，会产生 filesort；二是JOIN时连接字段两边字符集不一致，也会让连接条件里的索引失效。面试时能答出这两个细节，通常能让面试官觉得你是真在实战中调过优的。
最后再分享一个小技巧：遇到一条慢 SQL，我从来不先猜原因，第一件事就是EXPLAIN SELECT ...，先看 type 是否到ref或range，再盯rows估算扫描行数和Extra里有没有Using filesort或Using temporary。这三板斧能定位掉 80% 的索引问题。剩下的 20%，多半是统计信息过期、数据分布剧烈变化或者并发锁竞争，需要结合SHOW PROFILE和实际业务数据继续分析。索引是 MySQL 性能优化的核心杠杆，掌握好它，你写出的 SQL 会进入另一个层次。
