代码改变世界

知识点2 : MySQL优化:如何避免回表查询?_什么是索引覆盖?

  ly772186472  阅读(132)  评论(0编辑  收藏  举报

使用普通索引的情况下,并且包含了非索引字段的时候,会通过该普通索引获取到叶子节点的主键信息,拿到主键信息后再去聚合索引中找到对应的行信息,这个过程就叫做回表查询。

数据库表结构:

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

多查询了一个属性,为何检索过程完全不同?

什么是回表查询?

什么是索引覆盖?

如何实现索引覆盖?

哪些场景,可以利用索引覆盖来优化SQL?

一、什么是回表查询?

这先要从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是普通索引。

 

 

 

两个B+树索引分别如上图:

(1)id为PK,聚集索引,叶子节点存储行记录;

(2)name为KEY,普通索引,叶子节点存储PK值,即id;

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

通常情况下,需要扫码两遍索引树。

例如:

select * from t where name='lisi'; 

 

 

 二、什么是索引覆盖(Covering index)?

 

 

 

三、如何实现索引覆盖?

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

 

 

 

能够命中name索引,索引叶子节点存储了主键id,通过name的索引树即可获取id和name,无需回表,符合索引覆盖,效率较高。

画外音,Extra:Using index。

 

 

 

 

 

 

 

 

 

 

 

什么是覆盖索引?

 

 

 

 

 

 

 

 

总结

实践是检验原理的唯一标准。 通过此次实践,想必你已经充分了解并且体验到覆盖索引的概念及其意义了。其核心就是只从辅助索引要数据。那么, 普通索引(单字段)和联合索引,以及唯一索引都能实现覆盖索引的作用。

 

 

相关博文:
阅读排行:
· 分享4款.NET开源、免费、实用的商城系统
· 全程不用写代码,我用AI程序员写了一个飞机大战
· MongoDB 8.0这个新功能碉堡了,比商业数据库还牛
· 白话解读 Dapr 1.15:你的「微服务管家」又秀新绝活了
· 上周热点回顾(2.24-3.2)
点击右上角即可分享
微信分享提示