MySQL索引详解

一. 什么是MySQL索引

索引是对数据库表中一列或多列的值进行排序的一种数据结构，使用索引可快速访问数据库表中的特定信息。

通俗来说, 索引就相当于一本书的目录, 可以根据页码快速查找到指定的内容, 目的就是加快数据库的查询速度

1.1 索引的原理

• 索引是以"列"为维度建立的.通常一个个数据库表中会有很多列, 所以也会有不同的查询方式, 因此能建立不同的索引.

例如student表中, 有id列, 也有name列, 有的时候查询一个学生是按id查询, 也可能会用name查询

• 索引是存储在数据库服务器的硬盘上, 具体来说, 是存储在硬盘上的文件中的.

为什么不是存在内存中呢?

因为数据库中存储的数据量很大, 所以我们才会想去建立索引, 以便于在查询的时候快速锁定指定数据. 而基于这些庞大的数据量建立起来的索引本身也很大, 而且一般还会建立多个不同的索引.

这里就拿字典来举例子, 有按拼音来建立的索引, 有按笔画建立的, 有按偏旁建立的, 还有些是按生僻字来建立的, 这些就导致了字典的目录就能占个一百多页.

所以, 索引不可能全部存储在内存中.

• 存储原理: 空间换时间
• 数据库没有添加索引的时候, 默认是进行全文搜索.

注意: 建立索引后, 查询速度不一定会变快. 例如, 你在student建立了关于id的索引, 如果你按照name查询, 那么查询速度也不会变快.

1.2 索引优点&缺点

缺点:

• 需要消耗额外的空间(硬盘).
• 有可能拖慢数据库 增删改 的速度. 因为你进行 增删改 的时候, 不仅是要改变数据库中的数据, 还要更改维护索引文件.
• 维护索引需要消耗数据库资源.

优点

• 大大提高数据查询速度.
• 减少硬盘IO次数

综合来说: 索引适用于经常查询很少修改的业务

1.3 索引的使用

1.3.1 查看索引

show index from 表名;

例如: 查看学生表已有的索引

1. 学生表的结构:

show index from student;

当表中存在主键的时候, 内部就会自动的给该列创建索引

因为 主键不允许重复, 因此进行插入或者修改的时候, 就需要先查询, 看看插入/修改后的结果是否已经存在, 为了提高查询的速度, 数据库就自动的给主键这列创建了索引.

2. 如果该表的结构是这样的:

则他的索引为

使用unique约束某一列的时候, 也会为该列自动生成索引, 原因与上述相同

3. 如果该表的结构为这样

他的索引为

也就是说, 设置外键约束的时候, 也会自动生成一个索引.

因为外键也涉及到了自动查询.

例如,

我们在给学生表插入一条记录, 需要查询记录里class_id这个数是否于class表里的class_id中也存在, 这里用到的是class表的class_id(主键自动生成的索引)

在班级表中删除一条记录, 就需要查询记录中class_id这个数是否于student表中的class_id也存在. 这里用到的就是student表中外键自动生成的索引

⚠️综上: 若一个表中的字段被 主键/unique/外键 约束了, 都会自动给表建立索引

1.3.2 创建索引

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

例如,

create index name_index on student(name)

第三个索引就是我们自主创建的.

⚠️创建索引操作, 可能会十分的危险!!

如果这个表是空的或者是数据比较少, 创建索引没问题

但如果表中包含了很多数据, 创建索引会引起很大规模的硬盘IO操作!! 导致数据库被卡死.

所以, 在设计表的时候就应该先规划好, 哪些列需要有索引.

1.3.3 删除索引

drop index 索引名 on 表名;

例如:

drop index name_index on student;

当然, 删除索引只针对手动创建的索引, 而自动生成的索引是不能被删除的.

同样, 创建索引操作, 也可能会十分的危险, 也会引起很大规模的硬盘IO操作.

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

说起关于查询的数据结构, 我们不免会想到这两个: hash, 红黑树

但是, hash 不能进行范围查询, 也不能进行模糊匹配查询; 红黑树 虽然能够进行范围查询和模糊匹配, 但是会引起较多的硬盘IO.

在MySQL索引使用的数据结构主要有BTree索引和hash索引。

存储引擎不同, 使用的数据结构也不同.MySQL中有主要的两个存储引擎:MyISAM和InnoDB. 这篇文章是根据MySQL的InnoDB来介绍的.

InnoDB这个存储引擎使用的是B+Tree.

想搞懂B+树, 我们要先了解B树是怎么个事.

2.1 B树

B树的核心思路, 和二叉搜索树差不多, 其实就是由二叉树改造过来的.

MySQL的数据是存储在硬盘文件中的，查询处理数据时，需要先把硬盘中的数据加载到内存中，硬盘IO操作非常耗时，所以我们优化的重点就是尽量减少硬盘的IO操作。访问二叉树的每个节点都会发生一次IO，如果想要减少硬盘IO操作，就需要尽量降低树的高度。

那么如何降低树的高度呢?

假设, 树的每个节点中,包含一个key和两个指向左右子树的指针. 数值key占8个字节, 一个指针占4字节. 则一个节点占16字节.

在MySQL的innodb引擎的一次IO操作会读取一页的数据量(默认一页大小为16kb), 而这一次IO操作在二叉树上只能读到16个字节(一个节点)的有效数据, 空间利用率极低. 为了提高空间利用率, 我们可以在一个节点处存储多个元素, 在每个结点尽可能多的存储数据. 根据我们的假设, 一次IO能读取16kb, 一次IO读取一个节点, 那么这个节点可以存储1000个索引(16kb / 16b), 这样就将二叉树改造成了多叉树

这种数据结构, 我们就称为是B数, B树是一种多叉平衡搜索树.

B树的主要特点有:

• B树的节点中存储着多个元素, 每个内节点有多个分叉.
• 在所有的节点中都存储数据
• 父节点当中的元素不会出现在子节点中.
• 所有的叶子节点都位于同一层, 叶子节点具有相同的深度, 叶子节点之间没有指针连接.

B树结构大致如下:

假如我们要查询key = 26的数据data, 根据上图, 我们可知查询路径为: 硬盘块1 --> 硬盘块3 --> 硬盘块7

第一次硬盘IO: 将硬盘1加载到内存中, 在内存中从头遍历比较, 15 < 26 < 48, 走中子树, 从硬盘中寻址到硬盘块3.

第二次硬盘IO: 将硬盘3加载到内存中, 在内存中从头遍历比较, 25 < 26 < 31, 走中子树, 到硬盘中寻址硬盘块7.

第三次硬盘IO: 将硬盘块7加载到内存中, 在内存中从头遍历比较, 26 = 26, 找到key = 26的位置, 取出对应的数据data, 查询结束.

相比于二叉搜索树, 在整个查找过程中, 虽然数据比较的次数没有明显减少, 但是对于硬盘IO的次数会大大减少. 并且, 我们是在内存中进行的数据比较, 所以比较消耗的时间可以忽略不记.

虽然B树已经很理想了, 但是还有可以优化的地方:

• B树不支持范围查询的快速查找. 例如: 仍然根据上图, 我们想要查询10到35之间的数据, 查找到10之后, 需要回到根节点重新遍历查找, 需要从根节点进行多次遍历, 查询效率有待提高.
• B树的复杂度很不稳定. 与 key 在树中的位置有关, 最好为O(1)

2.2 B+树

B+树是B树的改造版, 他与B树的不同点有:

• 所有的data在叶子节点出现, 内部节点不再存储data, 只存储key
• 叶子节点之间使用双向指针连接, 最底层的叶子节点形成了一个双向有序链表, 方便进行范围查询.

B+树的大致结构

由于B+树将所有的索引项都放在了叶子节点上, 所以每次查询数据的时候, 都需要检索到叶子节点, 那么每次检索的硬盘IO次数与树的高度产生了直接的关系, 并且查询的时间复杂度更为稳定.

但是由于内节点不再存放data, 那每个内节点可以放入更多的key, 一次硬盘IO操作能够读取更多的key, 能索引的范围更大更精确, 所以相对于B树来说, B+树的树高理论情况下是比B树要矮的, 进而可以减少相应的硬盘IO操作.

假如我们要查询key为7对应的数据data, 则查询路径为: 硬盘块1 -> 硬盘块2 -> 硬盘块4

第一次硬盘IO: 将硬盘1加载到内存中, 在内存中从头遍历比较, 7 < 15, 走左子树, 从硬盘中寻址到硬盘块2.

第二次硬盘IO: 将硬盘3加载到内存中, 在内存中从头遍历比较, 7 = 7, 走右子树, 到硬盘中寻址硬盘块4.

第三次硬盘IO: 将硬盘4加载到内存中, 在内存中从头遍历比较,7 = 7, 找到key = 7的位置, 取出对应的数据data, 查询结束.

假如我们想要查找8和26之间的数据, 查找路径为: 磁盘块1->磁盘块2->磁盘块5->磁盘块6

前三次硬盘IO: 首先查找到键值为8对应的数据 (定位到硬盘块5) , 然后缓存到结果集中. 这一步和前面等值查询流程一样, 发生了三次磁盘IO.

继续查询, 查找到8之后, 底层的所有叶子节点是一个有序列表, 我们从硬盘块5中的键值8开始向后遍历筛选出所有符合条件的数据.

第四次硬盘IO: 根据硬盘块5的后继指针到硬盘中寻址定位到硬盘块6, 将硬盘块6加载到内存中, 在内存中从头遍历比较, 直到找到key = 26后停下, 将9到26这些key对应的数据data缓存到结果集中.

由于后面不会再有<=26的数据, 不需要再向后查找, 查询结束, 将结果集返回给用户。

综上, B+树可以保证精确查询和范围查询的快速查找.

MySQL的innodb存储引擎底层就是B+树.