数据库-3.6 密集索引和稀疏索引的区别

密集索引和稀疏索引的区别：
1，密集索引文件中的每个搜索码值都对应一个索引值。
大家可以理解为：叶子节点保存的不仅仅是键值，还保存了位于同一行记录里的其他列的信息，由于密集索引决定了表的物理排列顺序，一个表只能有一个物理排列顺序，所以一个表只能创建一个密集索引。
2，稀疏索引文件只为索引码的某些值建立索引项。
大家可以理解为：叶子节点仅保存了键位信息，以及该行数据的地址。有的稀疏索引是仅保存了键位信息及其主键。那么定位到叶子节点之后，仍然需要通过这个地址或者主键信息进一步定位到数据。

上面都是对于一般数据库的情况。我们来对Mysql进行分析。
MySQL主要有两种存储引擎，一种是MyISAM，一种是InnoDB。前者不管是主键索引，唯一键索引，或者普通索引，其索引均属于稀疏索引。而InnoDB必须有且仅有一个密集索引，这个密集索引的选取规则如下：
（1）若一个主键被定义，该主键则作为密集索引；
（2）若没有主键被定义，改表的第一个唯一非空索引则作为密集索引；
（3）若不满足以上条件，InnoDB内部会生成一个隐藏主键（密集索引），这个隐藏的主键是一个6字节的列，该列的值会随着数据的插入而自增，也就是说，我们的InnoDB必须有一个主键，而该主键就必须作为唯一的密集索引而存在。那为什么它一定要有主键索引呢？看第4点；
（4）非主键索引存储相关键位和其对应的主键值，包含两次查找。
非主键索引，即稀疏索引的叶子节点并不存储行数据的物理地址，而是存储该行的主键值，所以非主键索引包含了两次查找，一次是查找次级索引之身，然后再查找主键，

根据上图，我们知道InnoDB使用的是密集索引，将主键组织到一颗B+树中，而行数据就存储在叶子节点上，因为InnoDB的主键索引和对应的数据是保存在同一个文件当中的，所以呢，检索的时候，在加载叶子节点的主键进入内存的同时，也加载了对应的数据，即若使用“where id =14“这样的条件查询主键，则按照B+树的检索算法即可查找到对应的叶子节点，并获得对应的行数据。
若对稀疏索引进行条件筛选，则需要经历两个步骤，第一步，在稀疏索引的B+树中检索该键，比如检索“Ellison”，就定位到主键信息了，获取主键信息了还要经过第二步，第二步就是使用主键“where id =14”在密集索引的B+树中再执行一次检索操作，最终再到达叶子节点，获取整行的数据。
而MyISAM呢？它使用的均为稀疏索引，稀疏索引的两颗B+树，看上去没有什么不同，节点的结构完全一致，只是存储的内容不一样而已。主键索引B+树的节点存储了主键，辅助键索引B+树，存储了辅助键。表数据存储在独立的地方，即索引和它的表数据是分开存储的。这两颗B+树的叶子节点都使用一个地址指向真正的数据，像右图的映射关系。对于表数据来说，这两个键没有任何差别，由于索引树是独立的，通过辅助键检索，无需访问主键的索引树。

查看数据库文件存放位置：

mysql> show global variables like "%datadir%";

参考：https://blog.csdn.net/qq_34056251/article/details/79551324