索引（Index）完整详解

一、索引本质与作用

索引是数据库管理系统中用于快速查找数据的数据结构，类似于书籍的目录。通过索引，MySQL 可以避免全表逐行扫描，直接定位到目标数据的位置，从而大幅提升查询、排序、连表操作的效率。

核心作用：

• 极大减少需要扫描的行数，加速 WHERE 条件过滤
• 优化 ORDER BY 和 GROUP BY 操作，避免额外的文件排序
• 提升多表 JOIN 的关联速度

---

二、索引的默认规则

规则	说明
主键字段	建表时若设置了 PRIMARY KEY，MySQL 会自动生成主键索引（聚簇索引），无需手动创建
普通字段	默认没有索引，必须手动创建
字符串字段	默认没有索引，必须手动创建
联合字段	默认没有索引，必须手动创建联合索引
外键字段	MySQL 中不会自动为外键列创建索引，需要手动添加

---

三、索引底层数据结构

MySQL 支持多种索引数据结构，最常用的是 B+Tree，同时也支持 Hash（主要用于 MEMORY 引擎）。建索引时写的 BTREE 关键字，实际底层实现是 B+Tree。

1. B-Tree（平衡多路搜索树）

• 所有节点（包括非叶子节点和叶子节点）都存储键值和数据指针。
• 查询性能稳定，但非叶子节点存储数据会占用空间，导致树的高度相对较高，磁盘 I/O 次数增加。

2. B+Tree（MySQL 默认索引结构）⭐重点详解

B+Tree 是 B-Tree 的变体，被 InnoDB 和 MyISAM 作为默认索引实现，也是 MySQL 中最重要、最常用的索引结构。

结构特点：

• 非叶子节点只存储索引键值，不存储真实数据或数据指针，因此一个节点可以容纳更多的键，从而降低树的高度（通常 2~4 层）。
• 所有叶子节点存储完整的数据信息（对于聚集索引是整行数据；对于非聚集索引是指针或主键值）。
• 叶子节点之间用双向链表串联，形成有序序列，非常适合范围查询和排序操作。

为什么 B+Tree 最适合做数据库索引？

优势	说明
树矮	非叶子节点只存键值，分叉多，高度低，磁盘 I/O 次数少（每次 I/O 读取一个节点）
范围查询快	叶子节点链表结构，找到起始值后即可顺序扫描，无需反复回溯父节点
全表扫描高效	直接遍历叶子节点链表即可完成全表扫描，速度远快于 B-Tree
稳定性高	所有查询都要走到叶子节点，查询性能稳定（不像 Hash 那样最好 O(1) 最差全扫描）

举例： 一个高度为 3 的 B+Tree，假设每个节点可存储 1000 个键值，则叶子节点总数可达 10 亿，足以支撑大部分表。

3. Hash 索引

• 仅适用于精确查询（= 或 IN），对于范围查询（>、<、BETWEEN）无效。
• 采用哈希函数计算键值对应的存储位置，理想情况下时间复杂度 O(1)，查询非常快。
• 不支持排序（哈希表无序）。
• 主要用于 MEMORY 存储引擎，InnoDB 虽然支持自适应哈希索引（AHI），但不可手动创建，由引擎自动优化。

---

四、索引分类（按功能）

1. 主键索引（Primary Key）

• 一张表只能有一个主键索引。
• 字段值必须 非空且唯一（NOT NULL + UNIQUE）。
• InnoDB 中主键索引就是聚簇索引，叶子节点直接存储整行数据，查询效率最高。

2. 唯一索引（Unique Key）

• 字段值不能重复，但允许为 NULL（NULL 可以多次出现，视引擎而定）。
• 常用于手机号、身份证号、邮箱等需要保证唯一性的业务字段。

3. 普通索引（Index）

• 最通用的索引，没有唯一性限制，允许重复、允许 NULL。
• 仅用于加速查询，不保证数据完整性。

4. 联合索引（Composite Index / Compound Index）

• 在多个字段上共同创建的一个索引，例如 INDEX idx_name_age (name, age)。
• 存储规则： 先按第一个字段排序，第一个字段相同再按第二个字段排序，依次类推，类似于字典序（ORDER BY col1, col2, col3）。

核心规则：最左前缀匹配原则（Leftmost Prefix Principle）

• 查询条件必须从联合索引的最左侧第一个字段开始，不能跳过中间的字段。
• 如果跳过了左侧任意字段，该字段及其右侧的所有字段索引都会失效。
• 如果联合索引中某个字段使用了范围查询（>、<、BETWEEN、LIKE 'xxx%'），则其右侧的字段索引也会失效。

联合索引的好处：
一个联合索引可以覆盖多种查询场景（例如 (a,b,c) 可以覆盖 a、a,b、a,b,c 三种查询），减少单独建多个索引的空间和维护开销。

---

五、InnoDB 与 MyISAM 索引实现深度对比

核心判断依据：索引和数据是否分离（聚集 / 非聚集）。
回表操作 只取决于索引结构中是否直接存储了完整数据，与底层是否使用 B+Tree 无关。

对比维度	MyISAM	InnoDB
文件结构	索引文件 .MYI + 数据文件 .MYD 分离	索引和数据合并存储在 .ibd 文件中
主键索引	非聚集：叶子节点存储数据行的物理地址指针	聚集：叶子节点直接存储完整行数据
二级索引	非聚集，叶子节点也存储物理指针	叶子节点存储主键值（而非指针）
回表需求	任何索引查询都需要回表：先查 .MYI 得指针，再到 .MYD 取数据	主键索引不回表；二级索引需要回表（用主键值再查主键索引）
事务/锁/外键	不支持事务、不支持外键、表级锁	支持事务、行级锁、外键、崩溃恢复

为什么 InnoDB 二级索引存主键值而不是指针？

• 当表发生行迁移、页分裂时，数据行的物理位置会改变。如果二级索引存物理指针，则需要同步更新所有二级索引，代价巨大。而存主键值（逻辑指针），无论数据行如何移动，只要主键值不变，二级索引就无需修改，回表时通过主键值去聚簇索引重新定位即可。

---

六、索引的优缺点

优点：

• 极大减少查询扫描行数，提升查询速度（尤其大数据量）。
• 优化 ORDER BY 和 GROUP BY，避免额外排序。
• 提升多表 JOIN 的效率。

缺点：

• 占用磁盘空间：每个索引都是一棵独立的 B+Tree，占据额外存储。
• 降低写入性能：对表进行 INSERT、UPDATE、DELETE 时，MySQL 必须同步更新所有相关的索引结构，索引越多，写入越慢。
• 冗余索引会造成严重负担：无用的或重复的索引不仅浪费空间，还会拖累 DML 操作效率。

---

七、索引失效全场景（常见原因）

失效场景	说明与示例
违反最左前缀	联合索引 (a,b,c)，查询条件只用 b 或只用 c，索引失效
对索引列做函数/计算	WHERE LEFT(name,3) = '张' 或 WHERE age + 1 = 30，破坏有序性
联合索引范围查询后置	(a,b,c) 中 a 使用范围查询（>，<，BETWEEN），则 b、c 失效
使用不等于	!=、<>、NOT IN、NOT EXISTS 通常导致索引失效（优化器认为全表扫描代价更小）
IS NULL / IS NOT NULL	多数情况下索引失效，尤其是 IS NOT NULL
模糊查询通配符在开头	'%字符' 或 '%字符%' 无法走索引；只有 '字符%' 前缀匹配可用
字符串不加引号（隐式类型转换）	phone = 13800000000（phone 是 VARCHAR），MySQL 会转换为 CAST(phone AS INT)，导致索引失效
OR 连接非索引字段	WHERE id = 1 OR name = '张三'，若 name 无索引，整个查询走全表扫描
查询结果占比过大	当优化器估算需要读取的行数超过全表的 20~30% 时，可能主动放弃索引，改用全表扫描
字段区分度太低	如性别（只有男/女）、状态（0/1）等重复率极高的字段，建索引无意义，优化器也不会使用

---

八、索引设计与使用最佳实践

1. 优先给哪些字段建索引？
   频繁出现在 WHERE、JOIN 关联列、ORDER BY、GROUP BY 后面的字段。

2. 联合索引字段排序原则
   将等值查询的字段放在左侧，范围查询的字段放在右侧，这样能最大程度利用最左前缀，避免范围查询导致右侧失效。

3. 严禁使用 SELECT *
   尽量只查询必要的字段，便于使用覆盖索引，直接从索引中拿到所有数据，避免回表。

4. 控制联合索引字段数量
   建议不超过 3~4 个字段。字段过多会导致索引体积大、层级增加、维护成本高。

5. 不在索引列上做任何加工
   禁止对索引列使用函数、计算或类型转换，将运算移到常量端，例如 WHERE age = 30 - 1 优于 WHERE age + 1 = 30。

6. 模糊查询优化
   尽量使用前缀匹配（LIKE '字符%'）；若必须前后模糊，可依赖覆盖索引或引入 ElasticSearch 等搜索引擎。

7. 定期清理冗余索引
   删除重复、长期未使用、或可被其他联合索引完全覆盖的索引。

8. 小表不建索引
   数据量很小的表（例如几百行），全表扫描速度已经足够快，建索引反而增加开销。

9. 充分利用覆盖索引
   当查询的所有字段都包含在某个索引中时，MySQL 可以只扫描索引就返回结果，不回表，这是最高效的查询方式。

10. 理解 Explain 输出
    通过 EXPLAIN 分析执行计划，关注 key（实际使用的索引）、key_len（索引使用长度）、Extra（是否 Using index 覆盖索引）、type（ref、range、const 等）字段，针对性优化。

---

总结： 索引是 MySQL 性能优化的核心武器，但要“用好”而非“滥用”。理解 B+Tree 结构、聚集与非聚集索引的本质、最左前缀原则、回表与覆盖索引的概念，才能真正设计出高效、合理的索引方案。