InnoDB 聚集索引和非聚集索引

区别

聚集索引包含所需的所有数据,比如mysql整张表就是一个聚集索引
比如索引的key就是主键,值就是这一行的所有字段,根据主键查询时可以直接查到所有字段

非聚集索引不包含所有数据,但会包含聚集索引的key
比如索引的key是两个自定义的字段,而值就是主键,根据这两个字段,只能直接查出主键是什么,所以这时如果想查询其它字段的值还会通过主键去聚集索引中再查询一次(即回表)。

覆盖索引减少回表

如果查询的条件或结果中,只使用了非聚集索引定义中的那几个字段的话,就不会回表二次查询。
比如索引是uid, username两个字段:
执行SELECT status WHERE uid = ?时会回表二次查询status是什么
但执行SELECT username WHERE uid = ?时就不会二次查询,因为username在索引定义中。

如何创建聚集索引

聚集索引只是存储方式,不是索引类型,所以在创建索引时无法指定是否是聚集索引。

默认情况下会根据主键创建聚集索引。
如果没有主键,会根据第一个唯一索引创建聚集索引。
如果也没有唯一索引,会使用row id做聚集索引。

延伸:MyISAM

MyISAM中即使是聚集索引节点中也不包含数据,而是数据在硬盘的物理地址。而非聚集索引同样存的也是物理地址,所以没有回表问题。这么来看MyISAM好像比InnoDB快。但是因为MyISAM的节点上没有存储数据,所以数据在磁盘上有可能不是连续顺序存储的(按插入时间写入),那么在按范围查询的时候是不是会增加磁盘读取次数。

B树和B+树的区别

B树中的元素不会出现重复的,元素有可能在中间节点也有可能在叶子节点。
B+树中所有元素都会出现在叶子节点,但也有可能同时出现在中间节点。B+树叶子节点间有额外的链表结构。
这两点会让B+树在按连续的范围查找时比B树更快。
另外B树在查找时不同的元素因为在不同层级的节点,所以不同的元素查询时间可能有差异,而B+树所有的元素都在叶子节点所以查询的时间更平均一些

B树每个元素都包含卫星数据。
B+树只有叶子节点中的元素包含卫星数据。
这会使在中间节点的页大小相同的情况下B+树能比B树存更多的节点,也就意味着B+树有可能比B树更矮一些(树的高度决定磁盘io次数,越矮次数越少)