当前位置：首页 > 数据库 > Mysql > 详细页面

MySQL回表查询与索引覆盖

时间：2023-10-30来源：系统城装机大师作者：佚名

1、两类索引

为了更好地阐释这个问题，我们还是从索引来介绍吧。

InnoDB 索引分为两大类，一类是聚集索引(Clustered Index)，一类是非聚集索引(Secondary Index)

1.1 聚集索引（聚簇索引）

InnoDB聚集索引的叶子节点存储行记录，因此InnoDB必须要有且只有一个聚集索引。

如果表定义了PK（Primary Key，主键），那么PK就是聚集索引。
如果表没有定义PK，则第一个NOT NULL UNIQUE的列就是聚集索引。
否则InnoDB会另外创建一个隐藏的ROWID作为聚集索引。

这种机制使得基于PK的查询速度非常快，因为直接定位的行记录。

1.2 非聚集索引（普通索引、非聚簇索引、二级索引）

普通索引也叫二级索引，除聚簇索引外的索引，即非聚簇索引。

InnoDB的普通索引叶子节点存储的是主键（聚簇索引）的值，而MyISAM的普通索引存储的是记录指针。

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

A：减少了出现行移动或者数据页分裂时二级索引的维护工作（当数据需要更新的时候，二级索引不需要修改，只需要修改聚簇索引，一个表只能有一个聚簇索引，其他的都是二级索引，这样只需要修改聚簇索引就可以了，不需要重新构建二级索引）

在使用非聚集索引时，为了取到具体数据，则需要通过PK回到聚集索引里去查询数据。这就叫回表查询，扫描了2次索引树，所以效率相对较低。

2、应用示例

一例胜千言，show me you code！

2.1 建表操作

1
2
3
4
5
6
7
8 mysql> create table user(
-> id int(10) auto_increment,
-> name varchar(30),
-> sex tinyint(4),
-> type varchar(8),
-> primary key (id),
-> index idx_name (name)
-> )engine=innodb charset=utf8mb4;

id 字段是聚簇索引，name 字段是普通索引（二级索引）

2.2 填充数据

1
2
3
4
5
6
7
8
9 mysql> select * from user;
+----+--------+------+------+
| id | name | sex | type |
+----+--------+------+------+
| 1 | sj | m | A |
| 3 | zs | m | A |
| 5 | ls | m | A |
| 9 | ww | f | B |
+----+-----+-----+-----+