mysql中的回表查询与索引覆盖

Wesley13
• 阅读 693

了解一下MySQL中的回表查询与索引覆盖。

回表查询

要说回表查询,先要从InnoDB的索引实现说起。InnoDB有两大类索引,一类是聚集索引(Clustered Index),一类是普通索引(Secondary Index)。

InnoDB的聚集索引

InnoDB聚集索引的叶子节点存储行记录,因此InnoDB必须要有且只有一个聚集索引。

1.如果表定义了PK(Primary Key,主键),那么PK就是聚集索引。

2.如果表没有定义PK,则第一个NOT NULL UNIQUE的列就是聚集索引。

3.否则InnoDB会另外创建一个隐藏的ROWID作为聚集索引。

这种机制使得基于PK的查询速度非常快,因为直接定位的行记录。

InnoDB的普通索引

InnoDB普通索引的叶子节点存储主键值(MyISAM则是存储的行记录头指针)。

什么是回表查询

假设有个t表(id PK, name KEY, sex, flag),这里的id是聚集索引,name则是普通索引。

表中有四条记录:

id

name

sex

flag

1

sj

m

A

3

zs

m

A

5

ls

m

A

9

ww

f

B

聚集索引的B+树索引(id是PK,叶子节点存储行记录):

mysql中的回表查询与索引覆盖

普通索引的B+树索引(name是KEY,叶子节点存储PK值,即id):

mysql中的回表查询与索引覆盖

普通索引因为无法直接定位行记录,其查询过程在通常情况下是需要扫描两遍索引树的。

select * from t where name = 'lisi';

这里的执行过程是这样的:

mysql中的回表查询与索引覆盖

粉红色的路径需要扫描两遍索引树,第一遍先通过普通索引定位到主键值id=5,然后第二遍再通过聚集索引定位到具体行记录。这就是所谓的回表查询,即先定位主键值,再根据主键值定位行记录,性能相对于只扫描一遍聚集索引树的性能要低一些。

索引覆盖

索引覆盖是一种避免回表查询的优化策略。具体的做法就是将要查询的数据作为索引列建立普通索引(可以是单列索引,也可以一个索引语句定义所有要查询的列,即联合索引),这样的话就可以直接返回索引中的的数据,不需要再通过聚集索引去定位行记录,避免了回表的情况发生。

覆盖索引的定义与注意事项

如果一个索引覆盖(包含)了所有需要查询的字段的值,这个索引就是覆盖索引。因为索引中已经包含了要查询的字段的值,因此查询的时候直接返回索引中的字段值就可以了,不需要再到表中查询,避免了对主键索引的二次查询,也就提高了查询的效率。

要注意的是,不是所有类型的索引都可以成为覆盖索引的。因为覆盖索引必须要存储索引的列值,而哈希索引、空间索引和全文索引等都不存储索引列值,索引MySQL只能使用B-Tree索引做覆盖索引。

另外,当发起一个被索引覆盖的查询(索引覆盖查询)时,在explain(执行计划)的Extra列可以看到【Using Index】的信息。

覆盖索引的优点

1.索引条目通常远小于数据行的大小,因为覆盖索引只需要读取索引,极大地减少了数据的访问量。

2.索引是按照列值顺序存储的,对于IO密集的范围查找会比随机从磁盘读取每一行数据的IO小很多。

3.一些存储引擎比如MyISAM在内存中只缓存索引,数据则依赖操作系统来缓存,因此要访问数据的话需要一次系统调用,使用覆盖索引则避免了这一点。

4.由于InnoDB的聚簇索引,覆盖索引对InnoDB引擎下的数据库表特别有用。因为InnoDB的二级索引在叶子节点中保存了行的主键值,如果二级索引能够覆盖查询,就避免了对主键索引的二次查询。

"时刻保持谦虚,高情商是软实力。"

点赞
收藏
评论区
推荐文章
blmius blmius
3年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录问题用navicat导入数据时,报错:原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等,这样可以更容易地在不同的环境中使用MySQL。全局s
待兔 待兔
3个月前
手写Java HashMap源码
HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程22
Wesley13 Wesley13
3年前
MySQL千万级别优化·中
MySQL千万级别的查询优化手段·中单列索引(假设在v\_record表中存在id列的索引)1、WHERE条件使用​EXPLAINSELECT\FROMv\_recordWHEREid2​结论:利用索引进行回表查询2、SELECT字段使用
Stella981 Stella981
3年前
KVM调整cpu和内存
一.修改kvm虚拟机的配置1、virsheditcentos7找到“memory”和“vcpu”标签,将<namecentos7</name<uuid2220a6d1a36a4fbb8523e078b3dfe795</uuid
Easter79 Easter79
3年前
Twitter的分布式自增ID算法snowflake (Java版)
概述分布式系统中,有一些需要使用全局唯一ID的场景,这种时候为了防止ID冲突可以使用36位的UUID,但是UUID有一些缺点,首先他相对比较长,另外UUID一般是无序的。有些时候我们希望能使用一种简单一些的ID,并且希望ID能够按照时间有序生成。而twitter的snowflake解决了这种需求,最初Twitter把存储系统从MySQL迁移
Wesley13 Wesley13
3年前
mysql设置时区
mysql设置时区mysql\_query("SETtime\_zone'8:00'")ordie('时区设置失败,请联系管理员!');中国在东8区所以加8方法二:selectcount(user\_id)asdevice,CONVERT\_TZ(FROM\_UNIXTIME(reg\_time),'08:00','0
Wesley13 Wesley13
3年前
mysql5.6 分页查询优化
mysql5.6分页查询优化场景:表结构:主键(非自增)contentCode(varchar),过滤条件列为updateTime(timeStamp),已经为timestamp建立索引。搜索sql为:SELECTFROMmy_hello_tableWHEREupdat
Wesley13 Wesley13
3年前
Mysql索引最佳实践笔记0524
mysql5.7innodb默认存储引擎一、关于索引二、最佳实践三、避坑实践一、关于索引1.索引的作用提高查询效率数据分组、排序避免回表查询优化聚集查询用于多表join关联查询利用唯一性约束、保证数据唯一性innodb行锁实现索引的“
Stella981 Stella981
3年前
ELK学习笔记之ElasticSearch的索引详解
0x00ElasticSearch的索引和MySQL的索引方式对比Elasticsearch是通过Lucene的倒排索引技术实现比关系型数据库更快的过滤。特别是它对多条件的过滤支持非常好,比如年龄在18和30之间,性别为女性这样的组合查询。倒排索引很多地方都有介绍,但是其比关系型
为什么mysql不推荐使用雪花ID作为主键
作者:毛辰飞背景在mysql中设计表的时候,mysql官方推荐不要使用uuid或者不连续不重复的雪花id(long形且唯一),而是推荐连续自增的主键id,官方的推荐是auto_increment,那么为什么不建议采用uuid,使用uuid究