【MySQL】索引 详解

一. 概念

索引是一种特殊的文件，包含着对数据表里所有记录的引用指针。可以对表中的一列或多列创建索引，并指定索引的类型，各类索引有各自的数据结构实现。

二. 作用

数据库中的表、数据、索引之间的关系，类似于书架上的图书、书籍内容和书籍目录的关系。索引所起的作用类似书籍目录，可用于快速定位、检索数据，对于提高数据库的性能有很大的帮助。

作用：提高查找效率
代价：

1. 占用更多空间：数据库索引需要消耗一定的额外空间， 数据量越大， 索引消耗的额外空间越多。
2. 拖慢增删改的效率：当进行增删改时， 往往需要同步调整索引结构

三. 使用场景

要考虑对数据库表的某列或某几列创建索引，需要考虑以下几点：

• 数据量较大，且经常对这些列进行条件查询。
• 该数据库表的插入操作，及对这些列的修改操作频率较低。
• 索引会占用额外的磁盘空间。

满足以上条件时，考虑对表中的这些字段创建索引，以提高查询效率。
反之，如果非条件查询列，或经常做插入、修改操作，或磁盘空间不足时，不考虑创建索引。

四. 操作

创建主键约束（PRIMARY KEY）、唯一约束（UNIQUE）、外键约束（FOREIGN KEY）时，会自动创建对应列的索引。

• 查看索引

show index from 表名;

示例：查看学生表已有的索引

show index from student;

• 创建索引
  对于非主键、非唯一约束、非外键的字段，可以创建普通索引

create index 索引名 on 表名(字段名);

示例：创建班级表中，name字段的索引

create index idx_classes_name on classes(name);

注意：
创建索引是一个非常低效的操作， 尤其是当表中已经有很多数据时， 不能贸然创建索引。

• 删除索引

drop index 索引名 on 表名;

示例：删除班级表中name字段的索引

drop index idx_classes_name on classes;

同理， 删除索引也是非常低效的， 容易把数据库搞崩。
所以，在创建表的时候，就应该把索引建好。

五. 索引背后的数据结构

1. 为什么顺序表按下标访问速度就快呢 ？为什么不能用 顺序表 存索引呢 ？

顺序表是在 内存 空间中连续存储， 所以支持随机访问，（访问任意地址上的数据速度都是极快的），这是因为内存的硬件结构所支持的。
虽然快， 但是索引是存储在硬盘上的。

2. 二叉搜索树为什么不适合做索引 ？
   二叉搜索树， 查找时间复杂度为 O（N）（最坏情况 -> 单分支树），
   那么就不让二叉搜索树变成单分支树 ：

• AVL树：平衡二叉搜索树，要求任意节点左右子树的高度差不超过 1
• 红黑树：要求比 AVL 树更宽松一点的平衡二叉树

它们都不太适合做索引：
二叉树最大的问题就是当元素多了， 高度就高了， 高度就对应着比较次数，对于数据库来说，每次比较意味着磁盘 IO 。

3. 为什么哈希表也不适合存储索引 ？

虽然哈希表查找速度很快，时间复杂度为 O（1），但是哈希表只能针对 “相等” 进行判定，不能对于 “大于”、 “小于” 以及范围查找进行判定，也不能进行模糊匹配（因为哈希表是根据 key 值找到 value 的）。

4. 堆更不适合：

• 是二叉树
• 堆只能找到最大值 / 最小值

5. 最适合做索引的还得是树形结构, 只不过不是二叉树。
   使用多叉搜索树，高度自然就下降了。

数据库中使用的这个多叉搜索树， 是很特殊的树称为 B+ 树。

B-树

（不读作 B 减 树， 这个 - 是连字符， 它就是 B 树）

B 树: 每个节点上都存储 N 个 key 值， N 个 key 值分成 N+1 个区间，每个区间对应一个子树

可以简单理解为下图这个例子：

查找过程：
和二叉搜索树类似， 先从根出发， 根据待比较元素， 确定一个区间。

• 在确定区间时不是也需要比较多次嘛 ？ 和二叉搜索树相比有什么优势 ？

二叉搜索树，每个节点上面只有一个值， 只比较一次，比较次数和高度相关。
但是 B-树， 高度降低了， 只不过每个节点比较了多次， 但是相比于比较次数来说，IO 次数才是关键的， 磁盘是以节点为单位进行 IO 的，每次 IO 加载一个节点， 但是 B-树每个节点中好几个值， B-树高度低很多， IO 次数更少， 效率更高。

B+树

（这个确实就读作 B加 树）

B+ 树: 也是一棵 N 叉搜索树，每个节点上包含多个 key 值， 每个节点若有 N 个 key, 分成 N 个区间 （而不是 N+1 个）

关键点：

1. 父节点的值都会在子节点中体现。
2. 非叶子节点中的每个值最终都会在叶子节点中体现出来。
3. 父节点中的值会作为子节点中的最大值/最小值 （上图以最小值为例）。
4. 最下面的叶子节点，用链表进行按顺序连接。
5. 非叶子节点只存储 key 值本身即可，叶子节点是完整的数据集合， 只在叶子节点存储数据表的每一行数据。

B+ 树就是为了数据库索引量身打造的：

1. 使用 B+ 树进行查找时，整体的 IO 次数比较少。
2. 所有查询最终都会落到叶子节点， 每次查询的 IO 次数都差不多， 查询次数稳定，效率就比较稳定。
3. 叶子节点用链表相连， 非常适合范围查找，例如查找 （ 30 <= x <= 85） 的值。
4. 所有的数据存储（载荷）都是放到叶子节点， 非叶子节点中只保存 key 即可，因此非叶子节点整体占用的空间较小， 甚至可以缓存到内存中，一旦能放到内存中了，磁盘 IO 几乎就没有了。

聚簇索引与非聚簇索引

聚簇索引：

1. 将索引与数据放到一块了（指叶子节点），找到索引也就找到数据了。
   所以一个表中只能有一个聚簇索引。（因为只有一份数据。）
2. 聚簇索引将索引值相同的数据放一块。
3. 使用二级索引时， 先通过二级索引找到主键索引，再通过主键索引中找数据。（这个过程称为 “回表”）
   比如使用 id 建一个索引， 再使用 name 建一个索引， 那么 name 这个索引中叶子节点最终存储的是 id, 然后再通过 id 找到对应的 数据。
   为什么辅助索引不直接存储数据的位置？
   因为这样就算数据的位置变了，二级索引也不用变。因为对应的主键索引并没有变。
4. 聚簇索引中， 数据的物理存放顺序与索引的顺序完全相同，索引相邻，那么对应的数据在磁盘上的位置一定也相邻。所以数据地址都是挨着的。
   意味着：如果说， 你插入的这条数据对应的索引不是递增的， 那么这个索引就要插入到中间位置，也就是说对应的数据也要插入中间位置， 也就是说需要挪动数据，才能将这条数据及其索引插入进去。更改了索引结构，大大降低了 更新/插入数据的效率。
   所以说：一般以自增主键作为聚簇索引，这样每次插入数据都是添加到末尾， 不用挪动数据。

非聚簇索引：

1. 叶子节点中存储的时数据的位置， 而不是数据本身。
2. 通过主键索引和二级索引都能直接找到数据， 两者几乎没有区别， 只是一个存储主键， 另一个存储辅助键。
3. 数据存放的地址是凌乱的。所以更新/插入数据的速度一般比聚簇索引快，因为只要开辟一块空间把数据放进去就行了，然后让索引中记录位置就行了，不用考虑数据存放的位置。

两者最重要的区别就是：

1. 聚簇索引的数据与索引放到一起。（指叶子节点。）
2. 聚簇索引中数据存放的物理顺序与索引的排序完全相同。

好啦！ 以上就是对 MySQL 索引的讲解， 希望能帮到你 ！
评论区欢迎指正 ！