什么是回表
当需要查询的数据在索引树中不存在的时候,需要再次到聚集索引中去获取,这个过程就叫回表。
更简单点就是:先定位主键值,再定位行纪录。
那什么是聚集索引呢?
InnoDB
有两类索引:
- 聚集索引(Clustered index):常用的主键索引就是
- 普通索引/二级索引(Secondary index):非主键索引
这两种索引有什么不同呢?虽然它们使用的数据结构都是B+Tree
,但是区别在于叶子结点中存储内容不同:
- 聚集索引(主键索引)的叶子结点存储是一行完整的数据。
- 普通索引的叶子结点存储的则是主键值。
为什么会回表
示例:
# 通过主键索引来查询数据
select * from user id = 10;
# 通过普通索引来查询
select * from user where username="tony";
通过主键索引来查询的话,那么此时只需要搜索主键索引的B+Tree
的就可以找到数据。
通过非主键索引来查询数据,那么此时需要先搜索 username
这一列索引的 B+Tree
,搜索完成后得到主键的值,然后再去搜索主键索引的 B+Tree
,就可以获取到一行完整的数据。
从上可得知,第二种查询而言,一共搜索了两次,第一次是拿到主键值后再去搜索主键索引的B+Tree
。
结论:主键索引的叶子节点存储行纪录,普通索引的叶子节点存储主键值。
注意,并不是不用主键索引就一定会回表。如果查询的列本身就存在索引中,那么即使使用二级索引,一样也是不需要回表的。
怎么避免回表
使用聚集索引
具体根据应用场景来,但是能用主键索引就尽可能的用。
索引覆盖
即explain
的输出结果 Extra
字段为 Using index
时,能够触发索引覆盖。
create table user (
id int primary key,
name varchar(20),
sex varchar(5),
index(name)
)engine=innodb;
查询:select id,name from user where name='tony';
能够命中 name
索引,索引叶子节点存储了主键 id,通过 name 的索引树即可获取 id 和 name,无需回表,符合索引覆盖,效率比较高。(简单点:Extra:Using index
)
当然如果你被查询的字段,建立到联合索引里去,比如当前的index(name, sex)
,也是无需回表的。