mysql主键索引和聚集索引（mysql主键索引和聚集索引的关系）



我们先了解下InnoDB引擎表的一些关键特征：InnoDB引擎表是基于B+树的索引组织表(IOT)。

每个表都需要有一个聚集索引(clustered index)；

所有的行记录都存储在B+tree的叶子节点(leaf pages of the tree)；

基于聚集索引的增、删、改、查的效率相对是最高的；

如果我们定义了主键(PRIMARY KEY)，那么InnoDB会选择其作为聚集索引；

如果没有显式定义主键，则InnoDB会选择第一个不包含有NULL值的唯一索引作为主键索引；

如果也没有这样的唯一索引，则InnoDB会选择内置6字节长的ROWID作为隐含的聚集索引(ROWID随着行记录的写入而主键递增，这个ROWID不像ORACLE的ROWID那样可引用，是隐含的)。

综上总结，如果InnoDB表的数据写入顺序能和B+树索引的叶子节点顺序一致的话，这时候存取效率是最高的，也就是下面这几种情况的存取效率最高：

使用自增列(INT/BIGINT类型)做主键，这时候写入顺序是自增的，和B+数叶子节点分裂顺序一致；

如果该表不指定自增列做主键，同时也没有可以被选为主键的唯一索引(上面的条件)，这时候InnoDB会选择内置的ROWID作为主键，写入顺序和ROWID增长顺序一致；

如果一个InnoDB表又没有显示主键，又有可以被选择为主键的唯一索引，但该唯一索引可能不是递增关系时(例如字符串、UUID、多字段联合唯一索引的情况)，该表的存取效率就会比较差。

聚集索索 和 非聚集索引

每个InnoDB表都有一个称为聚集索引的特殊索引，其中存储了行的数据。通常，聚集索引与主键同义。

mysql的innodb表，就是索引组织表，表中的所有数据行都放在索引上，这就约定了数据是严格按照顺序存放的，所以不管插入的先后顺序，它在那个物理上的那个位置与插入的先后顺序无关。

聚集索引：叶子节点存的是整行数据，直接通过这个聚集索引的键值找到某行

聚集索引：数据的物理存放顺序与索引顺序是一致的，即：只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的。

聚集索引：数据行和相邻的键值紧凑地存储在一起，因为无法同时把数据行存放在两个不同的地方，所以一个表只能有一个聚集索引。

非聚集索引，叶子节点存的是字段的值，通过这个非聚集索引的键值找到对应的聚集索引字段的值，再通过聚集索引键值找到表的某行，类似oracle通过键值找到rowid，再通过rowid找到行

mysql的innodb表，其聚集索引相当于整张表，而整张表也是聚集索引。默认通过主键聚集数据，如果没有定义主键，则选择第一个非空的唯一索引，如果没有非空唯一索引，则选择rowid来作为聚集索引

mysql的innodb表，因为整张表也是聚集索引，select出来的结果是顺序排序的，比如主键字段的数据插入顺序可以是5、3、4、2、1,查询时不带order by得出的结果也是按1、2、3、4、5排序

通俗理解

聚集索引 ：类似新华字典正文内容本身就是一种按照一定规则排列的目录

非聚集索引：这种目录纯粹是目录，正文纯粹是正文的排序方式

每个表只能有一个聚集索引，因为目录只能按照一种方法进行排序 。

================

基于主键索引和普通索引(二级索引)的查询有什么区别？

主键索引的叶子节点存的是整行数据。在 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，就相对麻烦了，需要逻辑上挪动后面的数据，空出位置。

版权声明：

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。
如若内容造成侵权、违法违规、事实不符，请将相关资料发送至xkadmin@xkablog.com进行投诉反馈，一经查实，立即处理！

转载请注明出处，原文链接：https://www.xkablog.com/sqlbc/19052.html