面试常见问题（必知必会）之数据库索引（二）不同存储引擎的索引实现（InnoDB or MyISAM）

在介绍不同存储引擎的索引实现之前，先说一下面试中可能问道的常识性问题：存储引擎是形容数据库的，还是形容数据库表的？（形容数据库表的~）

 

MyISAM存储引擎的索引实现

• 索引文件和数据文件是分离的（非聚类）

  

  从上图我们可以看出，数据库的数据默认是保存在mysql目录下的data文件夹，然后我们看到的表是在对应的data下的test文件夹中。通过名字，可以看出MyISAM存储引擎的表有三文件，分别是表结构、表数据、表索引。

  下图呢，就是MyISAM的一个底层结果。那如果我要查找一个数据，例如（select * from t where col1=30;）。首先，我们娿先判断该字段有没有索引，如果有就进入上面提到的MYI（索引文件），进行快速定位（定位过程可参考"数据库索引（一）最后"），（就是先找到30所在的子节点指针（15和56之间的指针），然后找到子节点继续查找30所在的下一层子节点指针（20和49之间指针），然后最后查找索引30对应的data值）找到了30所对应的data值（30所在行的磁盘空间地址）。然后拿到这个值直接进入上面提到的MYD（数据文件）中进行定位磁盘空间地址获取数据。（数据查找过程）

 
 

InnoDB存储引擎索引实现

与上面的MyISAM相比，InnoDB表就将表数据和表索引放到了一个文件中。该表数据文件本身就是按B+Tree组织的一个索引结构文件。也就是下面这张图所示的结构：

与MyISAM不同的是，它的叶子节点不是存储的该索引所在行的磁盘空间地址，而知直接存储了该索引所在行的数据。

说到这儿，就顺便提一下面试中有可能问道的（聚集索引、聚簇索引、稀疏索引等）

• 聚集索引：叶节点包含了完整的数据记录（如上图InnoDB主键索引所示）
• 非聚集索引：索引和数据是分开存储的（如上上上图MyISAM主键索引所示）
• 聚簇索引：其实就是聚集索引（叫法不同）
• 稀疏索引：其实就是非聚集索引
   
   

非主键索引

上面我们说的是主键索引，那么非主键索引又是怎么保存的？

要将上图表中的col3创建索引，结构图如下。通过下图我们会发现它和主键索引的区别是，主键索引索引值对应的data是整行数据。而非主键索引对应的data是主键值。

 
 

联合索引

联合索引就是几列组合起来当做索引

索引是从小到大排序存储的，联合索引的排序规则是最左前缀原理：

例如，上图是一个联合索引图。它是由name、age、position三列联合起来的。要对索引排序，就需要先根据name字段进行排序，比如根节点。如果第一个name字段相同，那就接着根据第二个age字段进行排序，比如第二行最左边节点。那如果前两个字段都相同，那就是比较第三个字段了，比如第二行最右边节点。
 
 

引入面试题：

1. 为什么建议InnoDB表必须建主键，并且推荐使用整型的自增主键？

   A：因为InnoDB表的底层存储就是通过B+Tree来进行组织存储的。如果我们不设置主键，数据库就会从我们表中第一列开始，依次寻找直到寻找到一个一整列数据都是唯一的来进行构建B+Tree进而进行存储。如果都没有找到唯一的一列，数据库会在后台创建一个唯一的隐藏列来进行B+Tree维护组织进行存储。所以建议必须建主键。

   先说为什么使用整型，第一是因为我们在利用索引查找的时候，因为它每个节点、每个索引都是从左到右递增的，所以我们是通过比较大小来查找的。如果是整型它会比UUID比较快很多。第二是因为整型在索引存储时比UUID更节省很多空间。

   因为B+Tree保存是递增保存，使用自增主键会减少过多的性能损耗。（具体可参考《数据库索引（三）》）

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

   A：一致性和节省存储空间