MySQL--基础知识点--49--B+Tree索引/聚集索引_非聚集索引/联合索引

1. 为什么索引通常使用B+Tree数据结构，而不是用其他数据结构。

(1) 不使用二叉排序树的原因

二叉排序树整体结构受结点插入顺序的影响，最坏情况下，索引按递增或递减的顺序插入，会使整个二叉排序树成为一个单链表，查找过程成为与每个记录的索引进行对比，时间复杂度会变为O(n)。

(2) 不使用红黑树的原因
可以避免使用二叉排序树树的结构有可能是单链表问题，但使用红黑树树的深度仍然过深。红黑树每个结点只能放一个记录索引，导致记录数量非常大时，红黑树的深度过深。

(3) 不使用B-Tree的原因
可以解决红黑树树的深度过深问题，但B-Tree存在范围查找问题。对于范围查找中的每个记录的查找，都需要从根结点开始，需要耗费过多的资源。

(4) 部分存储引擎既支持hash索引也支持B+Tree索引，但当数据量过大时且频繁使用范围查找时，为什么仍然建议使用B+Tree索引？
理由见MySQL–基础知识点–14–索引 7.4什么是哈希索引

(5) 通常建议使用B+Tree的原因
B+Tree树中叶子结点存放记录（聚集索引）或记录所在物理地址(非聚集索引)，叶子结点之间有指针连接便于范围查找，不需要消耗过多的资源。

2. MyISAM

使用的是B+Tree索引且是非聚集索引

举例说明MyISAM存储引擎的查找过程如下

以Col1字段作为索引，查找Col1=49的记录



(1) 判断Col1是否是索引字段。如果是则跳到(3)；如果不是则跳到(2)。
(2) 逐行判断是否是所要查询的记录。
(3) 从一直加载在内存中的根中去查找。找到指针(i)。
(4) 根据指针(i)从磁盘加载相应的结点(ii)到内存。
(5) 从结点(ii)中找到指针(iii)。
(6) 根据指针(iii)从磁盘加载相应的结点(iv)到内存。
(7) 从结点(iv)中找到Col1=49对应的记录的物理地址。
(8) 将Col1=49对应的记录的物理地址的内容读出即为查询结果。

3. Innodb

使用的是B+Tree索引且是聚集索引

举例说明Innodb存储引擎的查找过程如下

以Col1字段作为索引，查找Col1=49的记录

(1) 判断Col1是否是索引字段。如果是则跳到(3)；如果不是则跳到(2)。
(2) 逐行判断是否是所要查询的记录。
(3) 从一直加载在内存中的根中去查找。找到指针(i)。
(4) 根据指针(i)从磁盘加载相应的结点(ii)到内存。
(5) 从结点(ii)中找到指针(iii)。
(6) 根据指针(iii)从磁盘加载相应的叶子结点(iv)到内存。
(7) 在叶子结点(iv)中找到Col1=49，并直接读出相应的记录即可。

3.1 聚集索引/非聚集索引

聚集索引：

• 逻辑上：B+Tree叶子结点包含了完整的数据记录，
• 物理上：聚集索引包括两个文件.frm(表结构的描述文件)、.idb(表中存储的数据记录和索引文件)
• 例如：Innodb存储引擎
  

非聚集索引：

• 逻辑上：B+Tree叶子结点包含的只是记录所在的磁盘地址。
• 物理上：非聚集索引包含三个文件.frm(表结构的描述文件)、.MYD(表中存储的数据记录文件)、.MYI(表中存储的索引文件)
• 例如：MyISAM存储引擎
  
  

3.2 为什么Innodb必须有主键？并且推荐使用整型的自增主键？

3.2.1 为什么Innodb必须有主键，而MyISAM的主键可有可无？

Innodb：

• 使用的是聚集索引，索引和数据记录是组织在一起的，放在同一个后缀为.idb的文件中。

MyISAM:

• 使用的是非聚集索引，索引和数据记录是分开存放的，索引放在后缀为.MYD文件中，数据记录放在.MYI文件中。

3.2.2 为什么推荐使用整型？

从两个角度考虑。
(1) 比较次数：

• 使用整形每次只比较1次，比较次数少；使用字符串，每次都需对字符串中的字符从左到右依次比较，比较次数多。
  (2) 空间占用：
• 使用整型占用空间比使用字符串占用空间少，使用整型用到的磁盘会更少。

3.2.3 B+Tree中叶子结点之间指针的作用是什么？

作用：为了更加快速的进行范围查找。

hash索引对于等值查询(即Col1=49)速度很快，直接计算出hash(49)从哈希表中查找相应记录的物理地址，读出记录即可。但对于范围查询由于hash索引没有B+Tree索引中叶子结点之间的指针，因此查询所耗费的资源很大。

同样，B-Tree索引同样没有类似于B+Tree中叶子节点之间的指针，导致对于范围查询中的每一个查询都需要从根结点开始，导致资源消耗很大。

因此表常使用的是B+Tree索引

3.2.4 为什么推荐使用自增？

原因：

• 每个叶子结点内部主键索引从左到右是递增趋势。
• 叶子结点之间主键索引从左到右也是递增趋势。

总之，总体来看整棵B+Tree树的主键索引从左到右是递增趋势。若不使用自增，后序插入的某个值可能插入前边已经排好序的某个叶子结点中，有可能造成叶子节点分裂以及其祖辈结点的分裂，造成资源的浪费。若使用自增，后序插入的值会依次在前边已经排好序的最后一个叶子结点中插入最后，或在最后一个叶子节点后又新建一个叶子结点插入所要插入的元素即可，避免了资源的浪费。因此推荐使用自增。

3.2.5 为什么非主键索引结构叶子节点存储的是主键值？

优点：

• 保持数据的一致性，若非主键索引结构叶子节点也存储数据，对于同一条记录来说可能会导致主键索引与多个非主键索引中的叶子结点的数据不一致，或者为了保持数据的一致性需要更多的资源开销。
• 节省存储空间。避免了数据的冗余，对于同一条记录只保存一份数据。

缺点：

• 需要进行回表操作，对于使用非主键索引查找一条记录，整个查找过程分两步进行：
  • (1) 在非主键索引结构中找到所查记录的主键值。
  • (2) 根据拿到的记录主键值在主键索引结构中找到相应的记录，读出即可。

3.2.6 联合索引的底层原理

与单个索引

结构：

• 联合索引与单个索引结构类似，使用的都是B+Tree。

存储：

• 只不过存储时单个索引存储的是一个索引，联合索引存储的是多个索引。

查询比较过程：

• 单个索引对于每个索引只需比较一次。
• 联合索引可能需要对其中所用到的字段依次进行比较，来找到相应的结果。

4. 索引优化

pass

[学习视频]
https://www.bilibili.com/video/BV1aE41117sk?p=1