聚集索引与非聚集索引

若有侵权 请联系
聚集（clustered）索引，也叫聚簇索引
定义：数据行的物理顺序与列值（一般是主键的那一列）的逻辑顺序相同，一个表中只能拥有一个聚集索引
通俗点讲
聚簇索引：将数据存储与索引放到了一块，找到索引也就找到了数据
非聚簇索引：将数据存储于索引分开结构，索引结构的叶子节点指向了数据的对应行，myisam通过key_buffer把索引先缓存到内存中，当需要访问数据时（通过索引访问数据），在内存中直接搜索索引，然后通过索引找到磁盘相应数据，这也就是为什么索引不在key buffer命中时，速度慢的原因

澄清一个概念：innodb中，在聚簇索引之上创建的索引称之为辅助索引，辅助索引访问数据总是需要二次查找，非聚簇索引都是辅助索引，像复合索引、前缀索引、唯一索引，辅助索引叶子节点存储的不再是行的物理位置，而是主键值。

图源
innodb聚簇索引和myisam非聚簇索引

问题一：为什么InnoDB表必须有主键，并且推荐使用整型的自增主键？没有主键怎么办?

必须有主键，因为数据文件本质是聚集索引文件

若未指定innoDB或默认指定隐式主键

整形自增主键，使得innoDB的数据文件在物理结构上数据连续，搭配磁盘IO的预读，性能更好，并且在检索时比对索引值效率高，减少树的高度和分裂次数

问题二：为什么非主键索引结构叶子节点存储的是主键值？

节省空间，行数据只存储一份，在主键形成的聚集索引文件中。

提高主键复用，保证主键索引一致性

基于主键索引和普通索引（二级索引）的查询有什么区别？ 主键索引的叶子节点存的是整行数据。在 InnoDB
里，主键索引也被称为聚集索引（clustered index）。 非主键索引的叶子节点内容是主键的值。在 InnoDB
里，非主键索引也被称为二级索引（secondary index）。

如果语句是 select * from T where ID=500，即 主键查询方式，则只需要搜索 ID 这棵 B+树 ； 如果语句是
select * from T where k=5， 即 普通索引查询方式，则需要先搜索 k 索引树，得到 ID的值为 500，再到 ID
索引树搜索一次。这个过程称为回表。B+树为了维护索引有序性，在插入新值的时候需要做必要的维护。以上面为例， 如果插入新的行 ID 值为
700，则只只需要在 R5 的记录后面插入一个新记录。 如果新插入的 ID值为 400，就相对麻烦了，需要逻辑上挪动后面的数据，空出位置

主键约束比唯一索引约束严格，当没有设定主键时，非空唯一索引自动称为主键。对于主键和唯一索引的一些区别主要如下：
1.主键不允许空值，唯一索引允许空值
2.主键只允许一个，唯一索引允许多个
3.主键产生唯一的聚集索引，唯一索引产生唯一的非聚集索引
注：聚集索引确定表中数据的物理顺序，所以是主键是唯一的（聚集就是整理数据的意思）
如果键是PRI，则列是主键或多列主键中的列之一。
如果键是UNI，则该列是唯一索引的第一列。(唯一索引允许多个空值，但可以通过检查Null字段来判断该列是否允许空。)
如果键为MUL，则该列是非唯一索引的第一列，其中允许在列中多次出现给定值。