1.什么是回表查询
前提:本次测试使用的是mysql 5.6版本。
1. 1 mysql的存储引擎
mysql的存储引擎分类比较多,比较常用的是MyISAM和InnoDb两种,具体各种存储 引擎就不详说了。
1. 2 InnoDb存储引擎
InnoDb存储引擎又有两大类索引
聚集索引(clustered index)
普通索引(secondary index)
1. 3 聚集索引和普通索引的区别
InnoDb的聚簇索引的叶子节点存储行记录,因此InnoDb必须要有聚簇索引且仅有一个聚簇索引。
聚簇索引也是有条件的,那就是必须是有序的,这里又要注意一点,有序不是连续,如:1,3,5,7是有序的,但不是连续的,依然可以构成聚簇索引的。如果是通过uuid生成的主键是不能构成聚簇索引的。
InnoDb叶子节点存储行记录,是一条完整的数据,MyISAM叶子节点存储记录指针。
InnoDB 聚集索引 的叶子节点存储行记录,因此,InnoDB必须要有且只有一个聚集索引
- A、如果定义了PK,那PK就是聚簇索引。
- B、如果没有定义PK,那NOT NULL UNIQUE列就是聚簇索引
- C、否则 InnoDb会创建一个隐藏的row-id列做为聚簇索引
画外音:所以PK查询非常快,直接定位行记录。
InnoDB 普通索引 的叶子节点存储主键值。
画外音:注意,不是存储行记录头指针,MyISAM的索引叶子节点存储记录指针。
大概了解了上述概念后,那什么是回表呢?假设有一张表 t(id PK, name KEY, sex, flag),表中有 4条记录。 (id是聚集索引,name是普通索引。)
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`sex` varchar(5),
`flag` varchar(5),
PRIMARY KEY (`id`) USING BTREE)
ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTE
SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
2.两种不同的索引存储上述数据的区别
聚簇索引存储(存储行记录) id为PK,聚集索引,叶子节点存储行记录
非聚簇索引 (存储 PK) name为KEY,普通索引,叶子节点存储PK值,即id
假设我们执行下面的查询语句,mysql是如何执行的?
select * from user where name='ls';
总结:粉红色路径就就是查询的执行的过程,先通过普通索引name定位到主键值id=5,再通过聚簇索引定位到行记录。这就是所谓的回表查询,先定位主键值,再定位行记录,它的性能较扫一遍索引树更低。
需要扫码两遍索引树:
- 先通过普通索引定位到主键值id=5;
- 在通过聚集索引定位到行记录;
3、所以我们就要通过建立复合索引覆盖被查询的字段;
这样就在一棵索引树上就能获取SQL所需的所有列数据,无需回表,速度更快。
没有使用覆盖索引情况 ( 注意是单值索引)
create table user (
id int primary key,
name varchar(20),
sex varchar(5),
index(name)
)engine=innodb;
查询语句
explain select id, name, sex from `user` where name = "ls"
explain分析结果
分析:
keys这列的值是name,说明命中索引name,但是sex这一列的值要回表才能查询到
使用覆盖索引情况 (注意是复合索引)
create table user (
id int primary key,
name varchar(20),
sex varchar(5),
index(name, sex)
)engine=innodb;
查询语句
explain select id, name, sex from `user` where name = "ls"
explain分析结果
分析:
都能够命中索引覆盖,无需回表。
4、哪些场景可以利用索引覆盖来优化SQL? 怎样去避免回表查询数据?
场景1:全表count查询优化
原表为:
user(PK id, name, sex);
直接:
select count(name) from user;
不能利用索引覆盖。
添加索引:
alter table user add key(name);
就能够利用索引覆盖提效。
场景2:列查询回表优化
select id,name,sex … where name=‘shenjian’;
这个例子不再赘述,将单列索引(name)升级为联合索引(name, sex),即可避免回表。
场景3:分页查询
select id,name,sex … order by name limit 500,100;
将单列索引 (name)升级为联合索引 (name, sex),也可以避免回表。