聚簇索引和非聚簇索引

原创于 2025-02-16 23:14:57 发布 · 1k 阅读

CC 4.0 BY-SA版权

文章标签：

26 篇文章

订阅专栏

一、什么是聚集索引，二级索引（非聚集索引）

分类	含义	特点
聚集索引	将数据存储和索引放到了一块，索引结构的叶子节点保存了整行数据	每张表必须有，且只有一个
非聚集索引	将数据和索引分开存储，索引结构的叶子节点关联的是对应的主键	可以存在多个

聚集索引选取规则：

将上表中的主键索引（聚集索引）拿取出来就是：

所有的key（灰色部分）对应的都是表中的主键

叶子节点保存的是整行的数据（row），也就意味着，绿色部分保存的是表中那一整行的数据，我们通过主键就可以直接查询这一行数据

如果给表中的name属性，添加了一个普通索引（二级索引），那么这个索引的数据结构如下：

所有的key（灰色部分）对应的就是name的值

到了叶子节点上，对应存储的就不是整行数据，而是他们所对应的主键的值

回表查询，也称（二次查询），当我们执行以下sql时：

select * from user where name = 'Arm';

要通过名称，来查询所有字段，name字段上添加了二级索引（非聚集索引），但是这个二级索引上并没有保存这一行所有字段的信息，所以会先通过二级索引查询到叶子结点上的主键的值，再通过主键，使用主键索引（聚集索引）查询整行row数据

这种先使用二级索引查询到聚集索引的主键，再通过主键查询整行数据，使用了两次索引进行查询的SQL，就称为回表查询

覆盖索引是指查询使用了索引，并且需要返回的列，在该索引中已经能全部找到

如图：

第一条SQL，由于是通过主键id去查询的，所以走的肯定是聚集索引，在叶子节点中就保存了所有信息，所以要查询*，所需要的字段是已经有了的，所以是覆盖索引

第二条SQL，通过name查询id和name，首先name作为索引中的key值，肯定是有的，而name创建的二级索引（非聚集索引）的叶子节点上保存的正好是主键id，那么这条SQL需要的所有列，这个索引中都有，也是覆盖索引

第三条SQL，通过name查询id、name、gender字段，其中gender字段既不是索引的key，也不是叶子节点上的主键；则需要通过查询到的id，再使用一次主键的索引（回表查询），才能查询到整行row，才能获取到这个gender值

很显然，能一次性查出来走覆盖索引的，肯定比走回表查询（走两次索引）的，性能要好很多

要怎么尽量避免回表查询呢？

1、尽量不使用select *，用什么字段查什么字段

2、对于经常查询的字段，考虑建立联合索引（多个字段组合当索引的key，如果查这几个字段，就可以使用覆盖索引了）

附一个题目：MySQL超大分页怎么处理呢？

tb_sku表有1千万数据，分页查询tb_sku表中limit 9000000, 10的所有字段

也就是查询第9000000到9000010条数据，如果直接使用

## 11秒
select * from tb_sku limit 9000000, 10;

耗时11秒，因为在进行分页查询时，如果执行limit 9000000,10 ，此时需要MySQL排序前9000010条记录，仅仅返回第9000000到9000010条数据，其他记录丢弃，排序的代价就很大

那么要怎么解决呢？

优化思路：一般分页查询时，通过创建覆盖索引能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化

如下：

## 7秒左右
select * 
from tb_sku t,
  (select id from tb_sku order by id limit 9000000, 10) a
where t.id = a.id;

如果表tb_sku非常大（如包含数百万或更多记录），推荐使用SQL 2。它通过子查询减少了主查询的扫描范围，避免了直接跳过大量数据，从而提高了查询效率。即使表中有主键索引，SQL 2的性能也通常优于SQL 1。

如果表较小，或者分页查询的偏移量较小（如LIMIT 100, 10），SQL 1的性能也完全可以接受。