密集索引和稀疏索引的区别:
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