mysql主键和聚簇索引_MySQL 聚簇索引一定是主键吗

数据库表的索引从数据存储方式上可以分为聚簇索引和非聚簇索引(又叫二级索引)两种。

use myTest;

drop table if exists t1;

CREATE TABLE `t1` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`a` int(20) DEFAULT NULL,

`b` int(20) DEFAULT NULL,

`c` int(20) DEFAULT NULL,

`d` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,

PRIMARY KEY (`id`),

KEY `idx_a_b` (`a`,`b`),

KEY `idx_c` (`c`)

) ENGINE=InnoDB CHARSET=utf8mb4 ;

- 什么是聚簇索引

先说说聚簇索引，聚簇索引有两个明显特征：

索引必须为唯一索引

叶子节点处储存的是整行数据(这也是为何用主键(聚簇索引)进行查询时，查询速度会较快的原因(减少回表查询))

这也是为何在新建数据表时，会推荐建立自增id作为主键，同时id自增确保业务层面上的无意义。==确保主键ID业务上无意义很重要，因为它确保你再已生成记录的主键ID，是不会被update的==。如果是随机主键或者频繁更新主键的话,就会存在数据页频繁断裂，B+树不饱和的情况(原因：聚簇索引是按顺序进行排序的)。而如果设置主键是自增，那么每一次都是在聚集索引的最后增加，当一页写满，就会自动开辟一个新页，不会有聚集索引树分裂这一步，效率会比随机主键高很多。这也是很多建表规范要求主键自增的原因。

- 什么是非聚簇索引

非聚簇索引有两个明显特征：

索引值必须为可不唯一

叶子节点处储存的索引行跟主键(key+pk cols)

辅助索引的叶子节点，存储主键值，而不是整行数据，这样的好处有：

1.减少存储数据，降低了辅助索引的树所占的存储空间

2.聚簇索引列不变的情况下，非聚簇索引列改变不影响辅助索引的原本结构

- 聚簇索引一定是主键吗

有人会疑虑似乎聚簇索引一定会是主键，那如果数据表不建立主键的话是否就没有聚簇索引了？

在 InnoDB 中，聚集索引不一定是主键，但是主键一定是聚集索引：原因是如果没有定义主键，聚集索引可能是第一个不允许为 null 的唯一索引，如果也没有这样的唯一索引，InnoDB 会选择内置 6 字节长的 ROWID 作为隐含的聚集索引。

InnoDB 的数据是按照主键顺序存放的，而聚集索引就是按照每张表的主键构造一颗 B+ 树，它的叶子节点存放的是整行数据。

每张 InnoDB 表都有一个聚集索引，但是不一定有主键。

- 使用非聚簇索引查询数据时，一定要回表查询吗？

回表查询是由于非聚簇索引的B+Tree叶子节点只记录了，PK cols与key。这就造成当查询字段非主键跟索引字段时，索引树无法直接提供所有数据列，从而要进行一次回表查询，也就是拿着主键ID再进行一次查询操作。

例如t1表

select * from t1 where c = 1; #sql_1

select id,c from t1 where c = 1; #sql_2

当查询被非聚簇索引涵盖列时，则不需要走回表操作。即sql_2不需要走回表查询，sql_1需要。

本作品采用《CC 协议》，转载必须注明作者和本文链接

大人中