MySQL索引深度解析：从原理到优化

前言

        对经历过面试的开发者来说，数据库索引是高频考点，在面试中频繁出现，考验我们对数据库知识的掌握。在实际开发里，索引的合理运用能显著提升 SQL 性能，优化数据库响应速度，为用户带来流畅体验。

        索引是用于快速查询和检索的数据结构，本质上是排序好的数据集合，作用类似书的目录。就像查字典时，有目录就能快速定位到所需内容，索引能大幅提高数据查询效率。

        索引底层数据结构多样，常见的有 B 树、B + 树、Hash 和红黑树。在 MySQL 中，Innodb 和 MyIsam 都采用 B + 树作为索引结构。不同数据结构各有特点与适用场景，下面我们就来深入了解，探索索引奥秘，掌握高效数据库查询的关键。

一、索引介绍

         索引是一种特殊的数据结构，用于快速查询和检索数据。其本质可以看成是一种排序好的数据结构，是加速数据检索的“捷径”，避免全表扫描。

        想象一下我们需要查找电话簿，如果只有姓名和电话号码的无序列表，查找效率极低；但如果按姓名排序并建立索引，查找速度将大大提升。

索引的优缺点

优点：

        使用索引可以大大加快数据的检索速度（大大减少检索的数据量）, 减少 IO 次数，这也是创建索引的最主要的原因。
        通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

缺点：

        创建索引和维护索引需要耗费许多时间。当对表中的数据进行增删改的时候，如果数据有索引，那么索引也需要动态的修改，会降低 SQL 执行效率。
        索引需要使用物理文件存储，也会耗费一定空间。

nnoDB引擎使用的索引的底层数据结构为B+树，在分析B+树前，先对B树进行分析。B树（Balanced Tree），全称为多路平衡查找树，一棵B树可以表示如下。

B树的特点可以概括如下。

1. 所有数据分布在整个B树中；
2. 任何一条数据会出现且仅会出现在一个节点上；
3. 搜索可能在非叶子节点上就结束。

现在开始分析B+树，一棵B+树可以表示如下。

B+树相较于B树，其优势如下。

1. B+树的扫表能力更强。做全表扫描时，只需要在叶子节点顺序的访问，不需要遍历树；
2. 磁盘IO能力更强。深度可以比B树更低；
3. 排序能力更强。基于叶子节点上的双向指针实现；
4. 效率更稳定。IO次数是稳定的。

但是，使用索引一定能提高查询性能吗?

大多数情况下，索引查询都是比全表扫描要快的。但是如果数据库的数据量不大，那么使用索引也不一定能够带来很大提升。        

二、MySQL 索引的底层数据结构

        MySQL主要使用B+树作为索引的数据结构。相比于B树、哈希表、红黑树等其他数据结构，B+树在磁盘I/O密集型场景下具有显著优势：

• B+树的叶子节点存储数据（或数据指针），非叶子节点只存储索引键值和指向子节点的指针，这使得B+树能够在有限的内存中存储更多的索引信息，减少磁盘访问次数。
• B+树的叶子节点之间通过指针连接成有序链表，这使得B+树能够高效地处理范围查询。

而哈希表虽然查找速度快(O(1))，但不支持范围查询和排序，红黑树虽然自平衡，但在磁盘I/O场景下效率不如B+树。

        在 MySQL 中，MyISAM 引擎和 InnoDB 引擎都是使用 B+Tree 作为索引结构，但是，两者的实现方式不太一样。

MyISAM 非聚簇索引：MyISAM 引擎将索引和数据分开存储。在其 B+Tree 索引结构里，叶子节点的data域存储的是数据记录的物理地址。当进行索引检索时，先通过 B+Tree 搜索算法在索引中找到指定Key，获取该Key对应叶子节点data域中的地址，再依据此地址到数据文件中读取相应的数据记录。这种索引和数据分离的存储方式就是 “非聚簇索引”。

InnoDB 聚簇索引与辅助索引：InnoDB 引擎的数据文件本身就是索引文件。表数据文件按照 B+Tree 结构组织，形成主索引，且该索引以数据表的主键作为key，叶子节点的data域保存完整的数据记录，这就是 “聚簇索引”。除主索引外的其他索引为辅助索引，辅助索引叶子节点的data域存储的是相应记录的主键值，而非数据地址。在使用辅助索引查询时，先从辅助索引找到主键值，再通过主索引获取完整数据。

三、索引分类

MySQL索引类型丰富，根据存储方式和应用场景可以分为以下几类：

1. 根据存储方式:

• 聚簇索引 (Clustered Index): InnoDB引擎的主键索引，数据行物理存储顺序与索引顺序一致。优点是查询速度快，范围查询效率高；缺点是更新数据开销大，因为数据和索引存储在一起。
• 非聚簇索引 (Non-Clustered Index): 除主键索引外的其他索引，索引存储与数据存储分离。叶子节点存储的是主键值，需要通过主键值再回表查询数据（回表操作）。优点是更新开销小；缺点是可能需要回表，查询速度相对较慢。

2. 根据应用场景:

• 主键索引 (Primary Key Index): 唯一且非空，InnoDB引擎自动创建聚簇索引。
• 唯一索引 (Unique Index): 确保列值唯一，允许NULL值。
• 普通索引 (Index): 允许重复值。
• 全文索引 (Fulltext Index): 用于全文检索，适合文本类型数据。
• 空间索引 (Spatial Index): 用于地理位置数据检索。
• 联合索引 (Composite Index): 多个列组合成一个索引，优化多列查询条件。
• 前缀索引 (Prefix Index): 只索引字符串的前缀部分，减少索引大小，提高效率，但可能导致部分查询无法利用索引。

四、索引的优缺点

优点:

• 显著提高查询速度，减少磁盘I/O。
• 确保数据唯一性（唯一索引）。
• 优化排序和分组操作（联合索引）。

缺点:

• 增加存储空间开销。
• 更新数据时需要维护索引，增加写操作开销。
• 索引设计不当可能导致查询效率下降（例如索引覆盖不足）。