mysql 命令行回显查询结果 mysql查询回表

什么是回表查询？

这要先从InnoDB的索引实现说起，InnoDB有两大类索引：

• 聚簇索引(clustered index)
• 普通索引(secondary index)

InnoDB的聚簇索引和普通索引有什么区别？

InnoDB的聚簇索引的叶子节点存储的行记录，因此InnoDB必要且仅有一个聚簇索引；

1. 如果表定义了PK,则PK就是聚簇索引
2. 如果表没有定义PK,则第一个not null unique列是聚簇索引
3. 否则，InnoDB会创建一个隐藏的row-id作为聚簇索引

ps：所以PK查询速度快，直接定位到行记录

InnoDB的普通索引的叶子节点存储主键值。

举个例子，假设有表：

t(id PK, name key ,sex，flag )

id是聚簇索引，name是普通索引

表中有四条数据：

1, shenjian, m, A

3, zhangsan, m, A

5, lisi, m, A

9, wangwu, f, B

两个B+ tree索引分别如上图：

1. id为PK,聚簇索引，叶子节点存储行记录
2. name为普通索引，叶子节点存储PK值

既然从普通索引无法直接定位到行记录，那么普通索引的查询过程是怎么样的呢？

通常情况下，需要扫描两边索引树。

例如：

select * from t where name = 'lisi'

如上图粉色路径，需要扫描两边索引树:

1. 先通过普通索引定位到id=5；
2. 在通过聚簇索引定位到行记录

这个过程就是回表，先定位主键位置，再定位行记录，它的性能比较扫一遍索引树更低。

什么是索引覆盖?

Mysql官网表示用explain的输出结果Extra字段为Using index时，能过触发索引覆盖。

Mysql官网表明：只需要在一棵索引树上就能获取sql所需的所有类数据，无需回表，速度更快。

那么如何实现索引覆盖呢？

常见的方法就是将要查询的字段，建立联合索引。

举个栗子：

create table user (
    id int primary key,
    name varchar(20),
    sex varchar(5),
    index(name)
)engine=innodb;

第一个sql

能够命中name索引，索引叶子节点存储了id，通过name的索引树就可以获取name和id，不需要回表，符合索引覆盖。第二个sql

能够命中name索引，索引叶子节点存储了id，但是sex需要回表查询才能获取到，不符合覆盖索引。

如果把(name)单列索引升级为联合索引(name, sex)就不同了。

create table user_two (
    id int primary key,
    name varchar(20),
    sex varchar(5),
    index(name, sex)
)engine=innodb;

可以看到都能够命中索引覆盖，不需要回表。

哪些场景可以利用索引覆盖来优化sql？

场景1：全表count查询优化

原表中 user(id PK,name KEY,sex)

直接count sex的话，不能利用索引覆盖

添加索引：

能够利用索引覆盖进行优化。

场景2：列查询回表优化

select id,name,sex  where name='lisi';

将单列索引(name)升级为联合索引(name,sex),避免回表查询。

场景3：分页查询

select id,name,sex  order by name limit 50,100;

将单列索引(name)升级为联合索引(name,sex),避免回表查询。