MySQL 索引
为什么需要索引
根据上面索引的定义,可以知道索引其实是一种数据结构,主要用于提高表中的查询效率,除此之外,索引还是数据库随机高速读取和对记录进行有效排序的基础。
不使用索引情况下数据的读取
除了像 Redis 这样的内存型数据库外,大部分的关系型数据库如 MySQL 等的数据都是直接存储在磁盘上的,而对于从磁盘查找数据来说,需要经历寻道, 寻址, 数据传输三个阶段。
- 寻道:驱动器驱动磁头前后移动到对应的磁道,一般为 5 ~ 14 ms
- 寻址:磁盘旋转到指定扇区的过程,寻址时间与磁盘转速有关,对于一个 7200 转的磁盘来说,意味着一分钟转 7200 圈,每秒可以转 120 圈,在寻址时,最好情况下磁头正好在正确扇区不需要再次寻址,最差情况下需要转一圈才能到正确扇区,所以寻址的平均时间为 1/120/2 = 4.17ms1/120/2=4.17m s
- 数据传输:数据传输阶段的耗时主要包括两部分,一是磁头从磁盘读取到数据并存储到磁盘缓存所需要的时间,二是从磁盘缓存中读取数据到对应控制器所需的时间;数据传输耗时主要与硬件性能有关,但一般为零点几毫秒。
所以直接从磁盘读取数据的 IO 耗时一般在 10ms 左右,为了避免频繁的磁盘 IO,所以操作系统在读取数据时会以页为单位,一次读取目标数据以及和目标数据相邻的一页大小(4K或8K)的数据并放在缓存中,这样下次再读取相邻的数据时就可以直接从缓存中返回了。
在不使用索引的情况下,如果要查询最后一条数据,就需要从头遍历到尾,
这种情况下,数据库需要读取所有的片才能得到目标数据,大量时间会浪费在磁盘 IO 上,为此,我们需要一种数据结构去记录数据项和磁盘中页的关系,这样在查询某条记录时就可以直接定位到某一页,这样只需要进行一次磁盘IO便可以得到目标数据,可以大大优化查询效率,这种数据结构便是索引。
Mysql 默认读入的是16KB 也就是4页哦!
为什么是 B+ 树
要实现上面的功能,首先可以采用 Hash Table 的方式,将索引键 Hash 之后存储哈希值和键对应的行指针,这样一来,在使用哈希索引查询的时候就可以直接计算出要查询记录的哈希值,然后查询此哈希值对应的行指针,由于每一行所需要的存储空间是固定的,所以得到行指针就相当于定位到了记录对应的页,这时每次查询只需要进行一次磁盘 IO, 可以大大优化查询效率,但哈希索引存在一些问题:
- 哈希冲突: 只要使用 Hash Table 的数据结构,哈希冲突就是不可避免的,MySQL 中解决冲突的方式是拉链法,即一旦发生冲突就把新的记录以链表的方式链接到原来的记录之后,这样每次查询都需要先遍历这个链表得到一个行指针,再根据行指针查询记录,得到记录后再与要查询的记录作比较,如果得到的不是要查询的记录,要回去取链表中的下一个行指针,再去查询比较,直到得到期望的数据,因此使用哈希索引后的磁盘IO次数取决于冲突的发生率,在存在大量冲突时,哈希索引的查询效率会急速下降。
- 哈希索引只支持等值查询:由于哈希索引是根据哈希键计算出哈希值,所以它只能在进行等值查询(如
IN
,=
,<=>
)时才能起到优化效率的效果,在进行非等值操作(如!=
,>
,<
,<>
)时起不到任何作用。 - 组合索引:在使用组合索引时,哈希索引的做法是将所有索引键合并后再做哈希,这就导致对多个字段做组合索引后,再查询其中某一个字段时无法利用索引。
- 无法根据索引进行有效排序,哈希之后的的值已经丢失了原来的索引键的大小信息,所以无法根据索引进行高效排序
除了使用 Hash Table, 另一个思路是使用排序树,以排序树的结构组织页后,可以将原来查询 O(n)的复杂度降低到 lg n 而 o(n)的复杂度就意味着每次查询需要进行 n 次磁盘IO,使用排序树后虽然不能像哈希表一样达到 O(1) 的复杂度,但相比不使用索引可以大大减少磁盘 IO 的次数 。
MySQL 中默认使用 B+ 树构建索引,之所以使用 B+ 树而不是 B 树或二叉排序树的原因在于:
- 要选取的树结构必须是稳定的,如果采用二叉排序树,在插入有序序列后,二叉树就会退化为链表,起不到好的优化效果
- 根据排序树查询其实是在进行树的深度遍历,而每遍历一层树节点都是一次磁盘IO,所以具体的IO次数取决于树的高度,这就要求树要尽可能矮,也就要求能一个根节点能持有多个子节点。
B+ 树就满足上面的要求,首先 B+ 树是一棵多路平衡二叉树,其次由于磁盘IO以固定大小的页为单位,所以每次进行磁盘IO能够查询出的数据量是有限制的,这同样意味着树的一个父节点能够持有的子节点数量是有限的,而 B+ 树的数据只存储在叶子节点,中间节点只存储指针,这使得每个中间节点能持有更多的子节点,相比 B 树,B+ 树的高度更低,且每次查询都必须遍历到叶子节点,使得 B+ 树的查询稳定性更高。
虽然上面说 B+ 树的叶子节点存储数据,但具体到 MySQL 对索引的实现上,叶子节点存储的依然不是真正的数据,存储的只是指向真实数据的指针,当然聚簇索引除外,聚簇索引存储数据的顺序和索引顺序是一致的,一张表也只能建立一个聚簇索引,一