Mysql高频面试题（一）

建立索引的目的是加快对表中记录的查找或排序。
付出的代价：一是增加了数据库的存储空间，二是在插入和修改数据时要花费较多的时间(因为索引也要随之变动)。

1. Mysql如何实现的索引机制？

MySQL中索引分三类：B+树索引、Hash索引、全文索引

2. InnoDB索引与MyISAM索引实现的区别是什么？

MyISAM索引文件和数据文件是分离的，使用B+树实现，主键索引与辅助索引实现一致，索引文件仅保存记录所在行的指针（物理位置），通过这些地址来读取页，进而读取被索引的行。

1. InnoDB的数据文件本身就是索引文件，而MyISAM索引文件和数据文件是分离的 ，索引文件仅保存数据记录的地址。

• MyISAM的表在磁盘上存储在以下文件中： *.sdi（描述表结构）、*.MYD（数据），*.MYI（索引）
  
• InnoDB的表在磁盘上存储在以下文件中： .ibd（表结构、索引和数据都存在一起）
  

2. InnoDB的二级索引的叶子节点存放的是Key字段加主键值。因此，通过二级索引查询首先查到的是主键值，然后InnoDB再根据查到的主键值通过主键索引找到相应的数据块。MyISAM的索引方式都是非聚簇的。MyISAM索引记录的是地址 。
   

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

会创建B+树。
InnoDB要求表必须有主键 （ MyISAM可以没有 ）。如果没有显式指定，则MySQL系统会自动选择一个可以非空且唯一标识数据记录的列作为主键。如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整型。

• 如果有主键会创建聚簇索引
• 如果没有主键会生成rowid作为隐式主键

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

假设有一个表index_demo，表中有2个INT类型的列，1个CHAR(1)类型的列，c1列为主键：
CREATE TABLE index_demo(c1 INT, c2 INT, c3 CHAR(1), PRIMARY KEY(c1)) ;
1
2

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 的记录，具体查找过程分两步：

1. 先从目录项中根据二分法快速确定出主键值为20的记录在目录项3中（因为 12 ≤ 20 < 209 ），对应页9。
2. 再到页9中根据二分法快速定位到主键值为 20 的用户记录。
   至此，针对数据页做的简易目录就搞定了。这个目录有一个别名，称为索引 。

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

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

• 目录项记录 的 record_type 值是1，而 普通用户记录 的 record_type 值是0。
• 目录项记录只有主键值和页的编号两个列，而普通的用户记录的列是用户自己定义的，包含很多列，另外还有InnoDB自己添加的隐藏列。
  现在查找主键值为 20 的记录，具体查找过程分两步：

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

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

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

• 聚簇索引

定义：聚簇索引是一种对磁盘上实际数据重新组织以按指定的一个或多个列的值排序。
特点：

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

优点：

• 数据访问更快 ，因为索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快。
• 聚簇索引对于主键的排序查找和范围查找速度非常快。
• 按照聚簇索引排列顺序，查询显示一定范围数据的时候，由于数据都是紧密相连，数据库可以从更少的数据块中提取数据，节省了大量的IO操作 。
• 在聚簇索引下，数据在物理上按顺序排在数据页上，重复值也排在一起，因而在那些包含范围检查(between、<、<=、>、>=)或使用group by或order by的查询时，一旦找到具有范围中第一个键值的行，具有后续索引值的行保证物理上毗连在一起而不必进一步搜索，避免了大范围扫描，可以大大提高查询速度。
  可以作为聚簇索引的候选列：
  1、主键列，该列在where子句中使用并且插入是随机的。
  2、按范围存取的列，如pri_order > 100 and pri_order < 200。
  3、在group by或order by中使用的列。
  4、不经常修改的列。
  5、在连接操作中使用的列。

缺点：

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

限制：

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

• 非聚簇索引（二级索引、辅助索引）

聚簇索引，只能在搜索条件是主键值时才发挥作用，因为B+树中的数据都是按照主键进行排序的，如果我们想以别的列作为搜索条件，那么需要创建非聚簇索引。
定义：非聚簇索引，叶级页指向表中的记录，记录的物理顺序与逻辑顺序没有必然的联系。非聚簇索引则更像书的标准索引表，索引表中的顺序通常与实际的页码顺序是不一致的。
特点：一个表可以多个非聚簇索引。
非聚簇索引常被用在以下情况：
1、某列常用于集合函数(如Sum,…)。
2、某列常用于join，order by，group by。
3、查寻出的数据不超过表中数据量的20%。

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

非聚簇索引的B+树与聚簇索引的B+树有几处不同：

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

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

例如：根据主键c1列的值查找c1=100的记录，查找过程如下：

1. 根据根页面33定位到页30（因为1 ≤ 100 < 320）
2. 同理最终在页9定位到具体的记录。
3. 由于聚簇索引的叶子节点存储的是完整的用户记录，所以B+树中聚簇索引能直接查找到完整的用户记录。
   

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

例如：根据c2列的值查找c2=4的记录，查找过程如下：

1. 根据根页面44定位到页42（因为2 ≤ 4 < 9）
2. 由于c2列没有唯一性约束，所以c2=4的记录可能分布在多个数据页中，又因为 2 ≤ 4 ≤ 4，所以确定实际存储用户记录的页在页34和页35中。
3. 在页34和页35中定位到具体的记录。
4. 但是这个B+树的叶子节点只存储了c2和c1（主键）两个列，所以我们必须再根据主键值去聚簇索引中再查找一遍完整的用户记录。