一文详解MySQL索引(六张图彻底搞懂)_Mysql

一、什么是索引？为什么需要索引？

查字典时，你会逐页翻找某个汉字吗？显然不会。我们通常会先查目录，通过拼音或部首定位到汉字所在的页码——这个目录就是一种索引。它通过额外的空间存储（目录页），换取了查询速度的大幅提升，这正是"空间换时间"的经典思想。

数据库中的索引本质相同：它是一种能快速定位数据的数据结构，核心作用是加速sql查询。没有索引的查询就像逐页翻字典，需要扫描整张表（全表扫描），在数据量庞大时效率极低；而有了索引，数据库能直接定位到目标数据所在的位置，避免无效扫描。

二、索引该用哪种数据结构？

mysql索引设计的出发点在于，要能按区间高效地范围查找，还要尽少在磁盘 i/o 操作中做查询。

1. 哈希表

哈希表通过键值对存储，查询单个数据的时间复杂度是o(1)，看似高效，但有致命缺陷：

无法支持范围查询（如age > 30）
不能按顺序返回结果（无法满足order by需求）
哈希冲突会导致性能波动

因此，哈希表仅适合精准匹配的场景（如字典查询），无法作为数据库的主力索引结构。

2. 跳表

跳表是一种基于链表的 “分层索引结构”，核心思路是给基础链表增加多级索引，实现类似 “二分查找” 的高效查询：

结构特点：底层是有序链表，上层索引层按固定间隔（如每 2 个节点）抽取节点形成，最高层索引指向链表首尾，通过索引层快速定位范围，再下沉到底层链表精确查找。
优势：支持范围查询（天然有序），插入 / 删除无需旋转（只需调整索引层指针），实现复杂度低于平衡树。
缺陷：随着数据量递增，索引层数会同步增加（百万级数据可能需要 10 + 层索引）。数据库索引需持久化到磁盘，每一层索引的访问都对应一次磁盘 io—— 多层索引会导致 io 次数激增，反而比二叉树更低效。此外，跳表的索引层占用大量额外空间，数据量越大空间开销越突出。

跳表更适合内存数据库（如 redis 的 sorted set），磁盘数据库中因 io 开销问题极少采用。

3. 二叉排序树

二叉排序树（左子树 < 根节点 < 右子树）支持范围查询和排序，但存在严重缺陷：

顺序插入时会退化为链表（如插入1、2、3、4），查询时间复杂度从o(logn)暴跌至o(n)
树高过高导致磁盘io频繁（数据库索引需持久化到磁盘，树高直接影响io次数）

4. 平衡二叉树

平衡二叉树（如avl树）通过旋转操作维持平衡（左右子树高度差≤1），解决了退化问题，但新问题出现：

为保持"绝对平衡"，插入/删除时需频繁旋转，导致大量磁盘io
仍为二叉结构（每个节点最多2个子树），数据量过大时树高依然很高（百万级数据树高约20）

5. 红黑树

红黑树是一种"近似平衡"的二叉树，通过变色和有限旋转维持平衡，不追求绝对高度差：

减少了旋转次数，降低了插入/删除的io开销
但本质仍是二叉树，数据量庞大时树高问题依然存在（千万级数据树高约30）

红黑树更适合内存中的小规模数据（如java hashmap中链表转红黑树的阈值为8），而非磁盘存储的数据库索引。

6. b树

b树是多路平衡排序树（一个节点可包含多个子树），显著降低了树高：

每个节点存储多个键值对（索引+数据），减少io次数
查询效率不稳定，如有的数据在二层有的数据在最后一层
不方便范围查询：b 树能高效的通过等值查询 90 这个值，但不方便查询出一个期间内 3 ~ 10 区间内所有数的结果。因为当 b 树做范围查询时需要使用中序遍历，那么父节点和子节点也就需要不断的来回切换涉及了多个节点会给磁盘 i/o 带来很多负担。

7. b+树

b+树就完美解决了上述问题：

非叶子节点只存索引：单个节点可容纳更多索引，树高显著降低（百万级数据树高通常≤3）
数据只在叶子节点存储：所有叶子节点通过双向链表连接，范围查询只需遍历链表，无需回溯
磁盘io友好：树高低+顺序io，大幅减少磁盘访问次数

因此，mysql等主流数据库均采用b+树作为索引的底层数据结构。

三、b+树是如何存索引的

mysql的索引按存储方式可分为两类，核心区别在于"索引是否与数据存放在一起"：

1. 聚簇索引

特点：索引与数据存储在一起，叶子节点包含完整的行数据
典型案例：主键索引（innodb表必有的索引）
优势：查询主键时无需额外操作，直接获取数据
注意点：主键应设计为自增字段。若主键无序（如uuid），插入时会导致b+树频繁"页分裂"，严重影响性能

2. 非聚簇索引（二级索引）

特点：索引与数据分离，叶子节点存储索引值+主键id
典型案例：唯一索引、普通索引、前缀索引等
查询流程：需经过"回表"操作——先通过二级索引找到主键id，再通过主键索引查询完整数据

查二级索引一定会回表吗？

不一定，如果只查id，那二级索引的叶子结点就有id；如果查询字段均可在二级索引中找到，也无需回表，这就是覆盖索引。

四、联合索引与最左前缀匹配法则

联合索引是针对多个字段创建的索引（如(name, age, gender)），其b+树按字段顺序排序（先按name，再按age，最后按gender）。使用时需遵循最左前缀匹配法则：

必须从左到右匹配：
- 有效：where name='张三'、where name='张三' and age=20
- 无效：where age=20（跳过了最左的name）、where name='张三' and gender='男'（跳过了中间的age）
字段顺序不影响有效性：
- where age=20 and name='张三' 会被mysql优化器调整为name='张三' and age=20，仍能使用索引
范围查询会中断匹配：
- where name='张三' and age>20 and gender='男' 中，age>20是范围查询，后续的gender无法使用索引

为什么不从最左开始查，就无法匹配呢

比如有一个 user 表，我们给 name 和 age 建立了一个联合索引 (name, age)。

alter table user add index comidx_name_phone (name,age);

联合索引在 b+ 树中是复合的数据结构，按照从左到右的顺序依次建立搜索树 (name 在左边，age 在右边)。

注意，name 是有序的，age 是无序的。当 name 相等的时候，age 才有序。

五、索引失效的场景

理解索引失效的原因，本质是理解b+树的查询逻辑：

模糊查询前缀含通配符：
- 有效：name like '张%'（前缀明确，可匹配索引）
- 无效：name like '%张'（前缀模糊，无法定位索引位置）
索引列参与运算或函数：
- 无效：where year(birthday) = 1990、where age + 1 = 30（索引存储原始值，运算后无法匹配）
隐式类型转换：
- 无效：where phone = 13800138000（若phone是字符串类型，会触发cast(phone as unsigned)，等价于函数操作）
or条件包含非索引列：
- 无效：where name='张三' or address='北京'（address无索引时，无法同时走索引和全表扫描，直接退化为全表扫描）

六、索引设计的最佳实践

索引并非越多越好，需在查询性能与写入性能间平衡：

适合建索引的场景：
- 数据量大且查询频繁的表
- where、group by、order by涉及的字段
- 区分度高的字段（如身份证号，而非性别）
不适合建索引的场景：
- 增删改频繁的列（索引会增加写入开销）
- 数据量极小的表（全表扫描可能更快）
- 区分度低的字段（如性别、状态），整个b+树一边男一边女，加索引也提高不了效率
实用技巧：
- 优先使用联合索引，提高覆盖索引概率，避免回表
- 长字符串用前缀索引（如name(10)），减少空间占用
- 控制单表索引数量（建议≤5个）
- 避免select *，减少回表操作