MySQL：索引（1）原理与底层结构

磁盘由大小相同且同轴的圆形盘片组成，磁盘可以转动（各磁盘必须同步转动）。在磁盘的一侧有磁头支架，磁头支架固定了一组磁头，每个磁头负责存取一个磁盘的内容。磁头不能转动，但是可以沿磁盘半径方向运动（实际是斜切向运动），每个磁头同一时刻也必须是同轴的，即从正上方向下看，所有磁头任何时候都是重叠的（不过目前已经有多磁头独立技术，可不受此限制）

盘片被划分成一系列同心环，圆心是盘片中心，每个同心环叫做一个磁道，所有半径相同的磁道组成一个柱面。磁道被沿半径线划分成一个个小的段，每个段叫做一个扇区，每个扇区是磁盘的最小存储单元。

当需要从磁盘读取数据时，系统会将数据逻辑地址传给磁盘，磁盘的控制电路按照寻址逻辑将逻辑地址翻译成物理地址，即确定要读的数据在哪个磁道，哪个扇区。

为了读取这个扇区的数据，需要将磁头放到这个扇区上方，为了实现这一点，磁头需要移动对准相应磁道，这个过程叫做寻道，所耗费时间叫做寻道时间，然后磁盘旋转将目标扇区旋转到磁头下，这个过程耗费的时间叫做旋转时间。寻道时间与旋转时间就是我们操作硬盘的主要时间消耗。

（2）预读机制

        由于存储介质特性，磁盘本身存取就比主存慢，再加上机械运动耗费，磁盘存取速度往往是主存的几百万分之一，因此要提高效率，必须减少磁盘I/O。
        为了达到这个目的，磁盘往往也不是严格按需读取，而是每次都会预读，即使只需要一个字节，磁盘也会从这个位置开始，顺序向后再读取一定长度的数据放入内存。
        这样做的理论依据是计算机科学中著名的局部性原理：
        当一个数据被用到时，其附近的数据也通常会马上被使用，程序运行期间所需要的数据通常比较集中。
        由于磁盘顺序读取的效率很高（无需寻道时间，只需很少的旋转时间），因此对于具有局部性的程序来说，预读可以提高I/O效率。

        预读的长度一般为页（page）的整数倍。innodb 默认一次读取 16k 。
        页是存储器的逻辑块，os往往将主存和磁盘存储区分割为连续的大小相等的块，每个存储块称为一页（许多 os 的页大小一般为4k），主存和磁盘以页为单位交换数据。
        当程序要读取的数据不在主存中时，会触发缺页异常，系统会向磁盘发出读盘信号，磁盘会找到数据的起始位置并向后连续读取一页或几页载入内存中，然后异常返回，程序继续运行。

索引正是通过将列数据进行排序后存储，从而保证磁盘中顺序存储的数据都为按照一定顺序排列的，从而实现查找的加速。

二、索引的数据结构

在 Mysql 中，索引是在存储引擎层而不是服务器层实现的。所以，并没有统一的索引标准；不同存储引擎的索引的数据结构也不相同。

1、数组

数组是用连续的内存空间来存储数据，并且支持随机访问。

有序数组可以使用二分查找法，其时间复杂度为 O(log n)，无论是等值查询还是范围查询，都非常高效。

但数组有两个重要限制：

数组的空间大小固定，如果要扩容只能采用复制数组的方式。
插入、删除时间复杂度为 O(n)。

这意味着，如果使用数组作为索引，如果要保证数组有序，其更新操作代价高昂。

2、全文索引

MyISAM 存储引擎支持全文索引，用于查找文本中的关键词，而不是直接比较是否相等。查找条件使用 MATCH AGAINST，而不是普通的 WHERE。

全文索引一般使用倒排索引实现，它记录着关键词到其所在文档的映射。InnoDB 存储引擎在 MySQL 5.6.4 版本中也开始支持全文索引。

3、Hash索引

哈希表是一种以键 - 值（key-value）对形式存储数据的结构，我们只要输入待查找的值即 key，就可以找到其对应的值即 Value。

哈希表使用哈希函数组织数据，以支持快速插入和搜索的数据结构。哈希表的本质是一个数组，其思路是：使用 Hash 函数将 Key 转换为数组下标，利用数组的随机访问特性，使得我们能在 O(1) 的时间代价内完成检索。

hash = hashfunc(key)
index = hash % array_size

哈希索引基于哈希表实现，只适用于等值查询。对于每一行数据，哈希索引都会将所有的索引列计算一个哈希码（hashcode），哈希码是一个较小的值。哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。

在 Mysql 中，只有 Memory 存储引擎显示支持哈希索引。

但哈希算法有个 Hash 冲突问题，也就是说多个不同的 key 最后得到的 index 相同。通常情况下，我们常用的解决办法是链地址法。链地址法就是将哈希冲突数据存放在链表中。就比如 JDK1.8 之前 HashMap 就是通过链地址法来解决哈希冲突的。不过，JDK1.8 以后HashMap为了减少链表过长的时候搜索时间过长引入了红黑树。

除了Hash冲突外，索引不支持顺序和范围查询(Hash 索引不支持顺序和范围查询是它最大的缺点：假如我们要对表中的数据进行排序或者进行范围查询，那 Hash 索引就不行了。

SELECT * FROM tb1 WHERE id < 500;

在这种范围查询中，直接遍历比 500 小的叶子节点就够了。而 Hash 索引是根据 hash 算法来定位的，只能把 1 - 499 的数据，每个都进行一次 hash 计算。这就是 Hash 最大的缺点了。

因为种种限制，所以哈希索引只适用于特定的场合。而一旦使用哈希索引，则它带来的性能提升会非常显著。

4、B 树索引

通常我们所说的索引是指B-Tree索引，它是目前关系型数据库中查找数据最为常用和有效的索引，大多数存储引擎都支持这种索引。使用B-Tree这个术语，是因为 MySQL 在CREATE TABLE或其它语句中使用了这个关键字，但实际上不同的存储引擎可能使用不同的数据结构，比如 InnoDB 就是使用的B+Tree。

B+Tree中的 B 是指balance，意为平衡。需要注意的是，B+树索引并不能找到一个给定键值的具体行，它找到的只是被查找数据行所在的页，接着数据库会把页读入到内存，再在内存中进行查找，最后得到要查找的数据。

以下内容节选自《一文搞懂MySQL索引所有知识点》

（1）二叉树查询

我们知道，对于有序数组，使用二分法来查找数据可以将时间复杂度降低到log(N)，即与树的高度。

在这里插入图片描述

二叉树特点：每个节点最多有2个分叉，左子树和右子树数据顺序左小右大。

这个特点就是为了保证每次查找都可以这折半而减少IO次数，但是二叉树就很考验第一个根节点的取值，因为很容易在这个特点下出现我们并发想发生的情况“树不分叉了”，这就很难受很不稳定。

在这里插入图片描述

显然这种情况不稳定的我们再选择设计上必然会避免这种情况的。

平衡二叉树是采用二分法思维，平衡二叉查找树除了具备二叉树的特点，最主要的特征是树的左右两个子树的层级最多相差1。在插入删除数据时通过左旋/右旋操作保持二叉树的平衡，不会出现左子树很高、右子树很矮的情况。

使用平衡二叉查找树查询的性能接近于二分查找法，时间复杂度是 O(log2n)。查询id=6，只需要两次IO。

在这里插入图片描述

就这个特点来看，可能各位会觉得这就很好，可以达到二叉树的理想的情况了。然而依然存在一些问题。

随着数据库中数据的增加，索引本身大小随之增加，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。这样的话，索引查找过程中就要产生磁盘 I/O 消耗，相对于内存存取，I/O 存取的消耗要高几个数量级。可以想象一下一棵几百万节点的二叉树的深度是多少？如果将这么大深度的一颗二叉树放磁盘上，每读取一个节点，需要一次磁盘的 I/O 读取，整个查找的耗时显然是不能够接受的。

一种行之有效的解决方法是减少树的深度，将二叉树变为 N 叉树（多路搜索树），于是我们的B树就出现了。

（2）B树查询

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

假如key为bigint=8字节，每个节点有两个指针，每个指针为4个字节，一个节点占用的空间16个字节（8+4*2=16）。

因为在MySQL的InnoDB存储引擎一次IO会读取的一页（默认一页16K）的数据量，而二叉树一次IO有效数据量只有16字节，空间利用率极低。为了最大化利用一次IO空间，一个简单的想法是在每个节点存储多个元素，在每个节点尽可能多的存储数据。每个节点可以存储1000个索引（16k/16=1000），这样就将二叉树改造成了多叉树，通过增加树的叉树，将树从高瘦变为矮胖。构建1百万条数据，树的高度只需要2层就可以（1000*1000=1百万），也就是说只需要2次磁盘IO就可以查询到数据。磁盘IO次数变少了，查询数据的效率也就提高了。

这种数据结构我们称为B树，B树是一种多叉平衡查找树，如下图主要特点：
B树的节点中存储着多个元素，每个内节点有多个分叉。
节点中的元素包含键值和数据，节点中的键值从大到小排列。也就是说，在所有的节点都储存数据。
父节点当中的元素不会出现在子节点中。
所有的叶子结点都位于同一层，叶节点具有相同的深度，叶节点之间没有指针连接。

在这里插入图片描述

假如我们查询值等于10的数据。查询路径磁盘块1->磁盘块2->磁盘块5。

第一次磁盘IO：将磁盘块1加载到内存中，在内存中从头遍历比较，10<15，走左路，到磁盘寻址磁盘块2。
第二次磁盘IO：将磁盘块2加载到内存中，在内存中从头遍历比较，7<10，到磁盘中寻址定位到磁盘块5。
第三次磁盘IO：将磁盘块5加载到内存中，在内存中从头遍历比较，10=10，找到10，取出data，如果data存储的行记录，取出data，查询结束。如果存储的是磁盘地址，还需要根据磁盘地址到磁盘中取出数据，查询终止。

过程如图：在这里插入图片描述

相比二叉平衡查找树，在整个查找过程中，虽然数据的比较次数并没有明显减少，但是磁盘IO次数会大大减少。同时，由于我们的比较是在内存中进行的，比较的耗时可以忽略不计。B树的高度一般2至3层就能满足大部分的应用场景，所以使用B树构建索引可以很好的提升查询的效率。

（3）B+树查询

B+树在B树的基础上又做了一定调整，主要包括2点：

在这里插入图片描述

只有叶子节点才会存储数据，非叶子节点至存储键值。

同一个数据块，在使用B树时，需要存储索引信息+数据信息，而使用B+树时，只需要存储索引信息，由于索引与数据小，整个B+树将会比B树更矮，即磁盘寻道时间更短。

叶子节点之间使用双向指针连接，最底层的叶子节点形成了一个双向有序链表。

双向指针使得可以快速的实现范围查找。

假如我们查询值等于9的数据。查询路径磁盘块1->磁盘块2->磁盘块6。

第一次磁盘IO：将磁盘块1加载到内存中，在内存中从头遍历比较，9<15，走左路，到磁盘寻址磁盘块2。
第二次磁盘IO：将磁盘块2加载到内存中，在内存中从头遍历比较，7<9<12，到磁盘中寻址定位到磁盘块6。
第三次磁盘IO：将磁盘块6加载到内存中，在内存中从头遍历比较，在第三个索引中找到9，取出data，如果data存储的行记录，取出data，查询结束。如果存储的是磁盘地址，还需要根据磁盘地址到磁盘中取出数据，查询终止。（这里需要区分的是在InnoDB中Data存储的为行数据，而MyIsam中存储的是磁盘地址。）

在这里插入图片描述

假如我们想要查找9和26之间的数据。查找路径是磁盘块1->磁盘块2->磁盘块6->磁盘块7。

首先查找值等于9的数据，将值等于9的数据缓存到结果集。这一步和前面等值查询流程一样，发生了三次磁盘IO。
查找到15之后，底层的叶子节点是一个有序列表，我们从磁盘块6，键值9开始向后遍历筛选所有符合筛选条件的数据。
第四次磁盘IO：根据磁盘6后继指针到磁盘中寻址定位到磁盘块7，将磁盘7加载到内存中，在内存中从头遍历比较，9<25<26，9<26<=26，将data缓存到结果集。
主键具备唯一性（后面不会有<=26的数据），不需再向后查找，查询终止。将结果集返回给用户。

在这里插入图片描述

可以看到B+树可以保证等值和范围查询的快速查找，MySQL的索引就采用了B+树的数据结构。

（4）B+树的层高与存放记录数

InnoDB存储引擎最小储存单元是页，一页大小就是16k。B+树叶子存的是数据，内部节点存的是键值+指针。索引组织表通过非叶子节点的二分查找法以及指针确定数据在哪个页中，进而再去数据页中找到需要的数据。

假设一颗高度为2的B+树，第一层为根节点，第二层为叶子节点。

先看根节点，若主键为int类型，长度为4字节，而指针大小在InnoDB源码中设置为6字节，所以就是 8+6=14 字节，那么一页能存储的数据为16k/14B =16*1024B/14B = 1170条。

再假设一行记录的数据大小为1k，那么单个叶子节点可以存的记录数 =16k/1k =16。

因此，一棵高度为2的B+树，能存放1170 * 16=18720条这样的数据记录。同理一棵高度为3的B+树，能存放1170 *1170 *16 =21902400，大概可以存放两千万左右的记录。B+树高度一般为1-3层，如果B+到了4层，查询的时候会多查磁盘的次数，SQL就会变慢。

jchen104

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL：索引（1）原理与底层结构

索引是数据库为了提高查找效率的一种数据结构，对数据库表中一列或多列的值进行排序，使用索引可快速访问数据库表中的特定信息。索引的作用就相当于书的目录，能加快数据库的查询速度。如果没有目录，那我们就只能一页一页的去找我们需要查的那个字，速度很慢。如果有目录了，我们只需要先去目录里查找字的位置，然后直接翻到那一页就行了。索引对于良好的性能非常关键，在数据量小且负载较低时，不恰当的索引对于性能的影响可能还不明显；但随着数据量逐渐增大，性能则会急剧下降。因此，索引优化应该是查询性能优化的最有效手段。.........
复制链接

扫一扫