MySQL 的覆盖索引与回表的使用方法
作者:张德Talk 发布时间:2024-01-20 22:40:37
两大类索引
使用的存储引擎:MySQL5.7 InnoDB
聚簇索引
* 如果表设置了主键,则主键就是聚簇索引
* 如果表没有主键,则会默认第一个NOT NULL,且唯一(UNIQUE)的列作为聚簇索引
* 以上都没有,则会默认创建一个隐藏的row_id作为聚簇索引
InnoDB的聚簇索引的叶子节点存储的是行记录(其实是页结构,一个页包含多行数据),InnoDB必须要有至少一个聚簇索引。
由此可见,使用聚簇索引查询会很快,因为可以直接定位到行记录。
普通索引
普通索引也叫二级索引,除聚簇索引外的索引,即非聚簇索引。
InnoDB的普通索引叶子节点存储的是主键(聚簇索引)的值,而MyISAM的普通索引存储的是记录指针。
示例
建表
mysql> create table user(
-> id int(10) auto_increment,
-> name varchar(30),
-> age tinyint(4),
-> primary key (id),
-> index idx_age (age)
-> )engine=innodb charset=utf8mb4;
id 字段是聚簇索引,age 字段是普通索引(二级索引)
填充数据
insert into user(name,age) values('张三',30);
insert into user(name,age) values('李四',20);
insert into user(name,age) values('王五',40);
insert into user(name,age) values('刘八',10);
mysql> select * from user;
+----+--------+------+
| id | name | age |
+----+--------+------+
| 1 | 张三 | 30 |
| 2 | 李四 | 20 |
| 3 | 王五 | 40 |
| 4 | 刘八 | 10 |
+----+--------+------+
索引存储结构
id 是主键,所以是聚簇索引,其叶子节点存储的是对应行记录的数据
聚簇索引(ClusteredIndex)
age 是普通索引(二级索引),非聚簇索引,其叶子节点存储的是聚簇索引的的值
普通索引(secondaryIndex)
如果查询条件为主键(聚簇索引),则只需扫描一次B+树即可通过聚簇索引定位到要查找的行记录数据。
如:select * from user where id = 1;
聚簇索引查找过程
如果查询条件为普通索引(非聚簇索引),需要扫描两次B+树,第一次扫描通过普通索引定位到聚簇索引的值,然后第二次扫描通过聚簇索引的值定位到要查找的行记录数据。
如:select * from user where age = 30;
1. 先通过普通索引 age=30 定位到主键值 id=1
2. 再通过聚集索引 id=1 定位到行记录数据
普通索引查找过程第一步
普通索引查找过程第二步
回表查询
先通过普通索引的值定位聚簇索引值,再通过聚簇索引的值定位行记录数据,需要扫描两次索引B+树,它的性能较扫一遍索引树更低。
索引覆盖
只需要在一棵索引树上就能获取SQL所需的所有列数据,无需回表,速度更快。
例如:select id,age from user where age = 10;
如何实现覆盖索引
常见的方法是:将被查询的字段,建立到联合索引里去。
1、如实现:select id,age from user where age = 10;
explain分析:因为age是普通索引,使用到了age索引,通过一次扫描B+树即可查询到相应的结果,这样就实现了覆盖索引
2、实现:select id,age,name from user where age = 10;
explain分析:age是普通索引,但name列不在索引树上,所以通过age索引在查询到id和age的值后,需要进行回表再查询name的值。此时的Extra列的NULL表示进行了回表查询
为了实现索引覆盖,需要建组合索引idx_age_name(age,name)
drop index idx_age on user;
create index idx_age_name on user(`age`,`name`);
explain分析:此时字段age和name是组合索引idx_age_name,查询的字段id、age、name的值刚刚都在索引树上,只需扫描一次组合索引B+树即可,这就是实现了索引覆盖,此时的Extra字段为Using index表示使用了索引覆盖。
哪些场景适合使用索引覆盖来优化SQL
全表count查询优化
mysql> create table user(
-> id int(10) auto_increment,
-> name varchar(30),
-> age tinyint(4),
-> primary key (id),
-> )engine=innodb charset=utf8mb4;
例如:select count(age) from user;
使用索引覆盖优化:创建age字段索引
create index idx_age on user(age);
列查询回表优化
前文在描述索引覆盖使用的例子就是
例如:select id,age,name from user where age = 10;
使用索引覆盖:建组合索引idx_age_name(age,name)即可
分页查询
例如:select id,age,name from user order by age limit 100,2;
因为name字段不是索引,所以在分页查询需要进行回表查询,此时Extra为Using filesort文件排序,查询性能低下。
使用索引覆盖:建组合索引idx_age_name(age,name)
来源:https://segmentfault.com/a/1190000021718016
猜你喜欢
- 本文实例为大家分享了Python实现学生成绩管理系统的具体代码,供大家参考,具体内容如下基本功能:输入并存储学生的信息:通过输入学生的学号、
- 前言 大家好,好男人就是我,我就是好男人,我就是-0nise。在各大漏洞举报平台,我们时常会
- 天天敲代码的朋友,有没有想过代码也可以变得很酷炫又浪漫?今天就教大家用Python模拟出绽放的烟花,工作之余也可以随时让程序为自己放一场烟花
- 关于 PHP 的文件操作,我们也将是通过一系列的文章来进行学习。今天我们先学习的是一个很少人使用过,甚至很多人根本不知道的扩展,它与我们日常
- 前言笔者最近正在学习Pandas数据分析,将自己的学习笔记做成一套系列文章。本节主要记录Pandas中数据的合并(concat和append
- 前言kettle是一款免费开源的、可视化的、国际上比较流行的、功能强大的ETL必备工具,在ETL这一方面做的还不错,下面介绍一下基于win1
- Flask-Admin是Flask框架的一个扩展,用它能够快速创建Web管理界面,它实现了比如用户、文件的增删改查等常用的管理功能;如果对它
- 表单验证做网站程序多多少少都会碰到,其中emai地址的合法性验证也算是一个典型的例子,网页表单的验证我们一般是先在客户端使用javascri
- 学习Python的人都知道数组是最常用的的数据类型,为了保证程序的正确性,需要调试程序。因此,需要在程序中控制台中打印数组的全部元素,如果数
- PDO::inTransactionPDO::inTransaction — 检查是否在一个事务内(PHP 5 >= 5.3.3, B
- 1 数据准备1.1 新建数据表CREATE TABLE `player` ( `id` bigint(20) NOT NULL
- 引言在刚入门python时,模块化编程、模块、类库等术语常常并不容易理清。尤其是Modules(模块)和Packages(包),在impor
- 在对于时间准确度的把握上,为了使操作的更加细化,很多人习惯把时间精确到秒。但在实际程序操作中,虽然秒数方便我们的查阅,但是计算机并不能直接的
- 什么是分页技术 分页,是一种将所有数据分段展示给用户的技术.用户每次看到的不是全部数据,而是其中的一部分,如果在其中没有找到自习自
- 1 前言很多程序都要求用户输入某种信息,程序一般将信息存储在列表和字典等数据结构中。用户关闭程序时,就需要将信息进行保存,一种简单的方式是使
- 我们在使用ASP 内置的ADO组件进行数据库编程时,通常是在脚本的开头打开一个连接,并在脚本的最后关闭它,但是就较大脚本而言,在多
- 首先我们供给一台主机要进行的步骤就是对其主机端口的扫描,查看其中开放的端口。我们首先创建一个TCP的全连接的扫描器。我们使用socket来创
- 常见到网上博文有错误理论,包括身边很多朋友都曾认同“可访问性(Accessibility)是为残障人士准备”的观点。其实在互联网技术领域远不
- 最近使用Python调用百度的REST API实现语音识别,但是百度要求音频文件的压缩方式只能是pcm(不压缩)、wav、opus、spee
- Git 远程仓库Git 并不像 SVN 那样有个中心服务器。目前我们使用到的 Git 命令都是在本地执行,如果你想通过 Git 分享你的代码