这篇文章主要介绍了MySQL 的覆盖索引与回表的使用方法,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面随着小编来一起学习学习吧

两大类索引

使用的存储引擎: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)

以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持爱安网。

最新资讯
华为云郑叶来:云业务受禁令的影响最小

华为云郑叶来:云业务受

从产业角度看,华为云受到的影响应该是最小的。
台积电3nm制程投片信心爆棚,首波苹果全包

台积电3nm制程投片信

台积电近日已释出3nm量产目标,2022年下半单月产能跃升
穆迪:美国对微信采取的措施不会影响腾讯评级

穆迪:美国对微信采取的

美国对微信采取的措施不会影响腾讯评级。
第二届“科学探索奖”揭晓:50位青年科学家均分腾讯1.5亿元奖金

第二届“科学探索奖”

第二届“科学探索奖”获奖名单正式揭晓,50位青年科学家
百度关联公司北京成立全资子,经营范围含汽车租赁等

百度关联公司北京成立

天眼查App显示,9月23日,阿波罗智通(北京)科技有限公司成立
首张黑洞动态照片发布,再次验证广义相对论

首张黑洞动态照片发布

9 月 23 日,事件视界望远镜(Event Horizon Telescope,EHT
最新文章
linux centos7安装mysql8的教程

linux centos7安装mys

这篇文章主要介绍了linux centos7安装mysql8的教程,本
Mysql高性能优化技能总结

Mysql高性能优化技能

这篇文章主要介绍了Mysql高性能优化技能总结的相关资
mysql 8.0.19 安装配置方法图文教程

mysql 8.0.19 安装配

这篇文章主要为大家详细介绍了mysql 8.0.19 安装配置
MySQL 的覆盖索引与回表的使用方法

MySQL 的覆盖索引与回

这篇文章主要介绍了MySQL 的覆盖索引与回表的使用方法
Windows10下mysql 8.0.19 winx64安装教程及修改初始密码

Windows10下mysql 8.0

这篇文章主要为大家详细介绍了Windows10下mysql 8.0.1
Workbench连接不上阿里云服务器Ubuntu的Mysql解决方法(已测)

Workbench连接不上阿

这两天为了解决workbench连接不上阿里云服务器的问题,