MySQL面试题--最全面-索引

在InnoDB存储引擎中，我们只需要根据主键值对聚簇索引进行一次查找就能找到对应的记录，而在MyISAM中却需要进行一次回表操作，意味着MyISAM中建立的索引相当于全部都是二级索引。
InnoDB的数据文件本身就是索引文件，而MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。
- MyISAM的表在磁盘上存储在以下文件中： *.sdi（描述表结构）、*.MYD（数据），*.MYI（索引）
- InnoDB的表在磁盘上存储在以下文件中： .ibd（表结构、索引和数据都存在一起）
InnoDB的非聚簇索引data域存储相应记录主键的值，而MyISAM索引记录的是地址。换句话说，InnoDB的所有非聚簇索引都引用主键作为data域。
MyISAM的回表操作是十分快速的，因为是拿着地址偏移量直接到文件中取数据的，反观InnoDB是通过获取主键之后再去聚簇索引里找记录，虽然说也不慢，但还是比不上直接用地址去访问。
InnoDB要求表必须有主键（ MyISAM可以没有）。如果没有显式指定，则MySQL系统会自动选择一个可以非空且唯一标识数据记录的列作为主键。如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整型。

InnoDB的辅助索引data域存储相应记录主键的值而不是地址。
InnoDB的数据文件本身就是主索引文件。
MyISAM的索引和数据是分开存储的。

3.一个表中如果没有创建索引，那么还会创建B+树吗？

会

如果我们在创建表时没有创建索引，那么系统会自动为我们生成一个B+树。

如果在创建表的时候指定了主键，比如ID，那么B+树就会以该主键去创建它的叶子节点索引。

如果没有指定主键，那么系统会默认为我们生成一个B+树，使用隐式的row_ID作为主键。这个row ID是系统自动生成的，只供MySQL的内部使用。

4.说一下B+树索引实现原理（数据结构）

B+树索引实现原理主要基于B+树这种数据结构，它是为磁盘或其他直接访问辅助设备而设计的一种平衡的多路查找树。在数据库和文件系统中，B+树被用作索引结构，以加快数据的查找速度。

以下是B+树索引实现原理的详细说明：

数据结构特点：
- 多路平衡：B+树允许每个节点有多个子节点，且所有叶子节点都位于同一层，这有助于保持树的平衡，从而确保查询性能的稳定。
- 节点结构：非叶子节点（内部节点）存储键值和指向子节点的指针，但不存储实际的数据记录。叶子节点存储键值、数据记录的指针以及指向下一个叶子节点的指针。这种设计使得范围查询更加高效。
- 磁盘友好：由于B+树的节点大小与磁盘块大小相近，因此可以充分利用磁盘的I/O操作，减少磁盘访问次数。
索引构建：
- 当向B+树中插入新数据时，会按照键值的大小在树中找到合适的位置进行插入。如果需要，会进行节点的分裂以保持树的平衡。
- 删除数据时，同样需要维护树的平衡，可能涉及节点的合并操作。
查询操作：
- 从根节点开始，根据键值在内部节点中进行查找，沿着指针指向的子节点继续查找，直到到达叶子节点。
- 在叶子节点中，找到与查询键值匹配的记录，或者确定该键值不存在于树中。
- 由于叶子节点之间通过指针相连，范围查询可以通过遍历叶子节点来实现，非常高效。
更新操作：
- 当数据记录发生更新时，B+树索引需要同步更新。如果更新涉及键值的变化，可能需要在树中进行相应的插入或删除操作，以保持索引的一致性。
优化：
- 为了进一步提高性能，B+树索引还可以采用一些优化技术，如预取技术、缓存机制等，以减少磁盘I/O次数，提高查询速度。

案例演示：

假设有一个表index_demo，表中有2个INT类型的列，1个CHAR(1)类型的列，c1列为主键：

 CREATE TABLE index_demo(c1 INT,c2 INT,c3 CHAR(1),PRIMARY KEY(c1)) ;

index_demo表的简化的行格式示意图如下：

我们只在示意图里展示记录的这几个部分：

record_type：表示记录的类型， 0是普通记录、 2是最小记录、 3 是最大记录、1是B+树非叶子节点记录。
next_record：表示下一条记录的相对位置，我们用箭头来表明下一条记录。
各个列的值：这里只记录在 index_demo 表中的三个列，分别是 c1 、 c2 和 c3 。
其他信息：除了上述3种信息以外的所有信息，包括其他隐藏列的值以及记录的额外信息。

将其他信息项暂时去掉并把它竖起来的效果就是这样：

把一些记录放到页里的示意图就是（这里一页就是一个磁盘块，代表一次IO）：

MySQL InnoDB的默认的页大小是16KB，因此数据存储在磁盘中，可能会占用多个数据页。如果各个页中的记录没有规律，我们就不得不依次遍历所有的数据页。如果我们想快速的定位到需要查找的记录在哪些数据页中，我们可以这样做：

下一个数据页中用户记录的主键值必须大于上一个页中用户记录的主键值
给所有的页建立目录项

以页28为例，它对应目录项2 ，这个目录项中包含着该页的页号28以及该页中用户记录的最小主键值 5。我们只需要把几个目录项在物理存储器上连续存储（比如：数组），就可以实现根据主键值快速查找某条记录的功能了。比如：查找主键值为 20 的记录，具体查找过程分两步：

先从目录项中根据二分法快速确定出主键值为20的记录在目录项3中（因为 12 ≤ 20 < 209 ），对应页9。
再到页9中根据二分法快速定位到主键值为 20 的用户记录。

至此，针对数据页做的简易目录就搞定了。这个目录有一个别名，称为索引 。

InnoDB中的索引方案

我们新分配一个编号为30的页来专门存储目录项记录，页10、28、9、20专门存储用户记录：

目录项记录和普通的用户记录的不同点：

目录项记录的 record_type 值是1，而普通用户记录的 record_type 值是0。
目录项记录只有主键值和页的编号两个列，而普通的用户记录的列是用户自己定义的，包含很多列，另外还有InnoDB自己添加的隐藏列。

现在查找主键值为 20 的记录，具体查找过程分两步：

先到页30中通过二分法快速定位到对应目录项，因为 12 ≤ 20 < 209 ，就是页9。
再到页9中根据二分法快速定位到主键值为 20 的用户记录。

更复杂的情况如下：

我们生成了一个存储更高级目录项的页33 ，这个页中的两条记录分别代表页30和页32，如果用户记录的主键值在 [1, 320) 之间，则到页30中查找更详细的目录项记录，如果主键值不小于320 的话，就到页32中查找更详细的目录项记录。这个数据结构，它的名称是 B+树。

5.聚簇索引与非聚簇索引b+树实现有什么区别？

聚簇索引

特点：

索引和数据保存在同一个B+树中
页内的记录是按照主键的大小顺序排成一个单向链表 。
页和页之间也是根据页中记录的主键的大小顺序排成一个双向链表 。
非叶子节点存储的是记录的主键+页号。
叶子节点存储的是完整的用户记录。

优点：

数据访问更快，因为索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快。
聚簇索引对于主键的排序查找和范围查找速度非常快。
按照聚簇索引排列顺序，查询显示一定范围数据的时候，由于数据都是紧密相连，数据库可以从更少的数据块中提取数据，节省了大量的IO操作 。

缺点：

插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键。
更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新。

限制：

只有InnoDB引擎支持聚簇索引，MyISAM不支持聚簇索引。
由于数据的物理存储排序方式只能有一种，所以每个MySQL的表只能有一个聚簇索引。
如果没有为表定义主键，InnoDB会选择非空的唯一索引列代替。如果没有这样的列，InnoDB会隐式的定义一个主键作为聚簇索引。
为了充分利用聚簇索引的聚簇特性，InnoDB中表的主键应选择有序的id，不建议使用无序的id，比如UUID、MD5、HASH、字符串作为主键，无法保证数据的顺序增长。

非聚簇索引

（二级索引、辅助索引）

聚簇索引，只能在搜索条件是主键值时才发挥作用，因为B+树中的数据都是按照主键进行排序的，如果我们想以别的列作为搜索条件，那么需要创建非聚簇索引。

例如，以c2列作为搜索条件，那么需要使用c2列创建一棵B+树，如下所示：

这个B+树与聚簇索引有几处不同：

页内的记录是按照从c2列的大小顺序排成一个单向链表 。
页和页之间也是根据页中记录的c2列的大小顺序排成一个双向链表 。
非叶子节点存储的是记录的c2列+页号。
叶子节点存储的并不是完整的用户记录，而只是c2列+主键这两个列的值。

一张表可以有多个非聚簇索引：

6.说一下B+树中聚簇索引的查找（匹配）逻辑

在B+树中，聚簇索引的查找逻辑如下：

从根节点开始查找：与B+树中的其他查找操作一样，聚簇索引的查找也是从B+树的根节点开始的。
根据主键定位到叶子节点：在B+树中，非叶子节点存储了键值和指向子节点的指针。查找过程中，会根据主键的值在非叶子节点中进行比较，并沿着指针指向的子节点继续查找，直到到达叶子节点。
在叶子节点中查找数据记录：叶子节点存储了主键、数据记录的指针以及指向下一个叶子节点的指针。当查找到达叶子节点时，会根据主键的值在叶子节点中进行匹配。如果找到了匹配的主键，那么就可以通过该叶子节点中存储的数据记录指针定位到实际的数据记录。
范围查询优化：由于聚簇索引将数据记录与主键物理地存储在一起，因此在进行范围查询时，可以通过遍历叶子节点中的指针来高效地获取满足条件的数据记录。这种顺序访问的特性使得聚簇索引在范围查询方面具有优势。

需要注意的是，聚簇索引的查找性能与B+树的平衡状态有关。如果B+树的高度过高或者节点分裂过多，可能会导致查找性能下降。因此，在实际应用中，需要根据数据的分布和查询需求来合理地构建和维护聚簇索引。

7.说一下B+树中非聚簇索引的查找（匹配）逻辑

B+树中非聚簇索引的查找（匹配）逻辑主要涉及到两个步骤：首先在B+树中定位到相应的叶子节点，然后再根据叶子节点中存储的信息去访问实际的数据记录。以下是详细的查找逻辑：

定位叶子节点：
- 从B+树的根节点开始，根据非聚簇索引的键值（通常是某个列的值或者多个列的组合）在B+树中进行查找。
- 在查找过程中，会沿着B+树的内部节点（非叶子节点）向下遍历，根据键值的大小比较来确定进入哪个子树。
- 最终，会定位到包含目标键值的叶子节点。这个过程类似于二分查找，每次比较都会缩小查找范围，直到找到匹配的叶子节点或者确定不存在匹配的键值。
访问数据记录：
- 当定位到叶子节点后，需要根据叶子节点中存储的信息去访问实际的数据记录。
- 与聚簇索引不同，非聚簇索引的叶子节点并不直接存储数据记录本身，而是存储了指向数据记录的指针或者主键值。
- 如果是指针，则可以直接根据指针去访问存储数据记录的物理位置；如果是主键值，则需要根据主键值去聚簇索引中查找对应的数据记录。
- 在访问数据记录时，可能还需要进行额外的磁盘I/O操作，因为数据记录可能并不与叶子节点连续存储在一起。