Mysql 回表查询和覆盖索引概念

最近在补Mysql基础时,发现很多判断查询语句是否优化时都通过回表查询等术语作为指标,看的很懵逼,就开始查找相关的文献,这时发现了一篇很通俗易懂的文章zhangdeTalk的博客 《MySQL 的覆盖索引与回表》

一、回表查询

因为InnoDB具有聚簇索引(主键索引)和二级索引(普通索引),且两种索引叶子节点存放的内容不一样,前者存储了所有的行数据,而后者仅仅存储主键的数据。

MyISAM的索引叶子节点存储得是行记录的指针

那么从普通索引是无法直接定位到行记录的,先扫描一遍普通索引获得主键值后在扫描聚簇索引,这就是所谓的回表查询,先定义主键值,在定位行记录。

二、覆盖索引

MySQL官网,类似的说法出现在explain查询计划优化章节,即explain的输出结果Extra字段为Using index时,能够触发索引覆盖。
在这里插入图片描述
**索引覆盖:**只需要在一棵索引树上就能获取SQL所需的所有列数据,无需回表,速度更快。

三、如何实现覆盖索引

常见的方法:将被查询字段,建立到联合索引中去

对于一个表

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

执行如下查询语句

select id,name from user where name='shenjian'; 

通过name二级索引,索引叶子存储了主键的id,所以通过这个索引树可以直接返回结果,无需回表,符合索引覆盖,效率高。

那么对于这样的语句

select id,name,sex from user where name='shenjian';

虽然id和name都可以通过name的二级索引获得,但是sex的值仍然需要回表查询,那么可以把单列索引升级为联合索引

alter table user drop key(name);
alter table user add key(name,sex);
四、哪些场景使用覆盖索引来优化SQL

1.全表count查询优化

select count(name) from user;

将name列添加索引

2.列查询回表优化

就是第三节中介绍的使用联合索引代替单列索引

3.分页查询
如果只有name是索引,执行下一条sql语句

select id,age,name from user order by age limit 100,2;

根据Extra发现使用的是Using filesort文件排序。
在这里插入图片描述
做法是可以建立联合索引key(age,name)

展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 深蓝海洋 设计师: CSDN官方博客
应支付0元
点击重新获取
扫码支付

支付成功即可阅读