MySQL(四)--索引的数据结构及底层原理

MySQL(四)–索引的数据结构及底层原理

一、索引用什么数据结构，为什么用B+？

索引是存在于索引文件中，是存在于磁盘中的。因为索引通常是很大的，因此无法一次将全部索引加载到内存中，因此每次只能从磁盘中读取一个磁盘页的数据到内存中。

但“平衡二叉树（也就是红黑树）”指的是逻辑结构上的平衡二叉树，其物理实现是数组。然后由于逻辑上相近的节点在物理结构上可能会差很远。因此，每次读取的磁盘页的数据有许多是用不上的，所以查找过程中要进行许多次的磁盘读取操作。所以，平衡二叉树是不适合作为索引结构的。

局部性原理与磁盘预读：

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

• 当一个数据被用到时，其附近的数据也通常会马上被利用
• 程序运行期间所需要的数据通常比较集中，由于磁盘顺序读取的效率很高（不需要寻道时间，只需要很少的旋转时间），因此，对于具有局部性的程序来说，预读可以提高I/O效率。

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

下面说一下具体的主存存取原理：

从抽象角度看，主存是一系列的存储单元组成的矩阵，每个存储单元存储固定大小的数据。每个存储单元有唯一的地址，现代主存的编址规则比较复杂，这里将其简化成一个二维地址：通过一个行地址和一个列地址可以唯一定位到一个存储单元。上图展示了一个4 x 4的主存模型。

主存的存取过程如下：

当系统需要读取主存时，则将地址信号放到地址总线上传给主存，主存读到地址信号后，解析信号并定位到指定存储单元，然后将此存储单元数据放到数据总线上，供其它部件读取。

写主存的过程类似，系统将要写入单元地址和数据分别放在地址总线和数据总线上，主存读取两个总线的内容，做相应的写操作。

这里可以看出，主存存取的时间仅与存取次数呈线性关系，因为不存在机械操作，两次存取的数据的“距离”不会对时间有任何影响，例如，先取A0再取A1和先取A0再取D3的时间消耗是一样的。

磁盘存取原理：

索引一般以文件形式存储在磁盘上，索引检索需要磁盘I/O操作。与主存不同，磁盘I/O存在机械运动耗费，因此磁盘I/O的时间消耗是巨大的。

下图是磁盘的整体结构示意图：

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

盘片被划分成一系列同心环，圆心是盘片中心，每个同心环叫做一个磁道，所有半径相同的磁道组成一个柱面。磁道被沿半径线划分成一个个小的段，每个段叫做一个扇区，每个扇区是磁盘的最小存储单元。为了简单起见，我们下面假设磁盘只有一个盘片和一个磁头。

当需要从磁盘读取数据时，系统会将数据逻辑地址传给磁盘，磁盘的控制电路按照寻址逻辑将逻辑地址翻译成物理地址，即确定要读的数据在哪个磁道，哪个扇区。为了读取这个扇区的数据，需要将磁头放到这个扇区上方，为了实现这一点，磁头需要移动对准相应磁道，这个过程叫做寻道，所耗费时间叫做寻道时间，然后磁盘旋转将目标扇区旋转到磁头下，这个过程耗费的时间叫做旋转时间。

红黑树：深度多，由于逻辑上很近的节点（父子）物理上可能很远，无法利用局部性。所以，红黑树（平衡二叉树）是不能用作索引结构。

B树：B树的每个节点可以存储多个关键字，它将节点大小设置为磁盘页的大小，充分利用了磁盘预读的功能，每次读取磁盘页时就会读取一整个节点。也正是因为每个节点存储着非常多个关键字，树的深度就会非常小，进而要执行的磁盘读取操作数就会非常小，更多的是在内存中对读取进来的数据进行加查找。B树的查询，主要发生在内存中。B树更适合作为索引。

B+树：==>MySQL中也是使用B+树作为索引。

• B树：有序数组+平衡多叉树
• B+树：有序数组链表+平衡多叉树

B+树的关键字全部存放在叶子节点中，非叶子节点用来做索引，而叶子节点中有一个指针指向下一个叶子节点。目的是为了提高区间访问的性能。

B+树还有一个最大的好处：方便扫库。B树必须用中序遍历的方法按序扫库，而B+树直接从叶子节点挨个扫一遍就完了，B+树支持“范围查询”非常方便，而B树不支持。这就是数据库选用B+树的最主要原因。

引用一段话：

数据库索引采用B+树的主要原因：是B树在提高了磁盘IO性能的同时并没有解决元素遍历的效率低下的问题。正是为了解决这个问题，B+树应运而生。B+树只要遍历叶子节点就可以实现整棵树的遍历，而B树则比较麻烦。

二、关于索引失效：

索引失效：不是这个索引被删除了，而是在某些情况下，DBMS不会检索索引列表了，执行速度和没有这个索引时的速度是一样的。

索引失效时针对某条sql语句的，某个查询条件，而不是针对索引本身。

什么时候会失效？

①对索引字段判断是否为null时，语句为is null或is not null
②判断索引列是否不等于某个值时
③对索引列进行运算。这里运算包括：+，-，*，/等运算，也包括用函数
④like "%_"百分号在前

写详细一点：

①对于创建的多列索引（复合索引），不是使用的第一部分就不会使用索引
②对于使用like查询，查询若是’%aaa’不会使用索引，而’aaa%'会使用到索引
③若条件中有or，有条件没有使用索引，即使其中有条件带索引也不会使用。换言之，就要求使用的所有字段，都必须单独使用时能使用索引
④不同类型字段比较不会用到索引，比如：若列类型是字符串，那么一定要在条件中使用引号引用起来，否则不使用索引。
⑤正则表达式不使用索引。

索引失效的原因：

• ①随着表的增长，where条件出来的数据太多，大于15%，使得索引失效（会导致计算走索引花费>走全表）
• ②统计信息失败（需要重新搜集统计信息）
• ③索引本身失效（需要重建索引）

感谢并参考：

https://blog.csdn.net/qq_40241957/article/details/86426027

https://blog.csdn.net/kennyrose/article/details/7532032