mysql回表查询uuid_数据库回表查询

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，再通过聚簇索引定位到行记录。这就是所谓的回表查询，先定位主键值，再定位行记录，它的性能较扫一遍索引树更低。

需要扫码两遍索引树：

1)、先通过普通索引定位到主键值id=5；

2)、在通过聚集索引定位到行记录；

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)，也可以避免回表。

内容是从网上总结:https://blog.csdn.net/qq_42000661/article/details/108536954