共计 2235 个字符,预计需要花费 6 分钟才能阅读完成。
select id,name where name=’shenjian’
select id,name__,sex _where name=’shenjian’_
多查问了一个属性,为何检索过程齐全不同?
什么是回表查问?
什么是索引笼罩?
如何实现索引笼罩?
哪些场景,能够利用索引笼罩来优化 SQL?
这些,这是明天要分享的内容。
画外音:本文试验基于 MySQL5.6-InnoDB。
一、什么是回表查问?
这先要从 InnoDB 的索引实现说起,InnoDB 有两大类索引:
- 汇集索引 (clustered index)
- 一般索引 (secondary index)
**InnoDB 汇集索引和一般索引有什么差别?
**
InnoDB 汇集索引 的叶子节点存储行记录,因而,InnoDB 必须要有,且只有一个汇集索引:
(1)如果表定义了 PK,则 PK 就是汇集索引;
(2)如果表没有定义 PK,则第一个 not NULL unique 列是汇集索引;
(3)否则,InnoDB 会创立一个暗藏的 row-id 作为汇集索引;
画外音:所以 PK 查问十分快,间接定位行记录。
InnoDB 一般索引 的叶子节点存储主键值。
画外音:留神,不是存储行记录头指针,MyISAM 的索引叶子节点存储记录指针。
举个栗子,无妨设有表:
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
《2020 最新 Java 根底精讲视频教程和学习路线!》
两个 B + 树索引别离如上图:
(1)id 为 PK,汇集索引,叶子节点存储行记录;
(2)name 为 KEY,一般索引,叶子节点存储 PK 值,即 id;
既然从一般索引无奈间接定位行记录,那 一般索引的查问过程是怎么样的呢?
通常状况下,须要扫码两遍索引树。
例如:
select * from t where name=’lisi’;
是如何执行的呢?
如 粉红色 门路,须要扫码两遍索引树:
(1)先通过一般索引定位到主键值 id=5;
(2)在通过汇集索引定位到行记录;
这就是所谓的 回表查问,先定位主键值,再定位行记录,它的性能较扫一遍索引树更低。
二、什么是索引笼罩 (Covering index)?
额,楼主并没有在 MySQL 的官网找到这个概念。
画外音:治学严谨吧?
借用一下 SQL-Server 官网的说法。
MySQL 官网,相似的说法呈现在 explain 查问打算优化章节,即 explain 的输入后果 Extra 字段为 Using index 时,可能触发索引笼罩。
不论是 SQL-Server 官网,还是 MySQL 官网,都表白了:只须要在一棵索引树上就能获取 SQL 所需的所有列数据,无需回表,速度更快。
三、如何实现索引笼罩?
常见的办法是:将被查问的字段,建设到联结索引里去。
仍是《迅猛定位低效 SQL?》中的例子:
create table user (
id int primary key,
name varchar(20),
sex varchar(5),
index(name)
)engine=innodb;
第一个 SQL 语句:
select id,name from user where name=’shenjian’;
可能命中 name 索引,索引叶子节点存储了主键 id,通过 name 的索引树即可获取 id 和 name,无需回表,合乎索引笼罩,效率较高。
画外音,Extra:Using index。
第二个 SQL 语句:
select id,name__,sex _from user where name=’shenjian’;_
可能命中 name 索引,索引叶子节点存储了主键 id,但 sex 字段必须回表查问能力获取到,不合乎索引笼罩,须要再次通过 id 值扫码汇集索引获取 sex 字段,效率会升高。
画外音,Extra:Using index condition。
如果把 (name) 单列索引降级为联结索引 (name, sex) 就不同了。
create table user (
id int primary key,
name varchar(20),
sex varchar(5),
index(name, sex)
)engine=innodb;
能够看到:
select id,name … where name=’shenjian’;
select id,name,__sex _… where name=’shenjian’;_
都可能命中索引笼罩,无需回表。
画外音,Extra:Using index。
四、哪些场景能够利用索引笼罩来优化 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),也能够防止回表。
InnoDB 汇集索引一般索引 , 回表 , 索引笼罩,心愿这 1 分钟大家有播种。
链接:https://juejin.cn/post/690408…