MySQL 回表

MySQL 回表

五花马,千金裘,呼儿将出换美酒,与尔同销万古愁。

一、简述

回表,顾名思义就是回到表中,也就是先通过普通索引扫描出数据所在的行,再通过行主键ID 取出索引中未包含的数据。所以回表的产生也是需要一定条件的,如果一次索引查询就能获得所有的select 记录就不需要回表,如果select 所需获得列中有其他的非索引列,就会发生回表动作。即基于非主键索引的查询需要多扫描一棵索引树。

二、InnoDB 引擎有两大类索引

要弄明白回表,首先得了解 InnoDB 两大索引,即聚集索引 (clustered index)和普通索引(secondary index)。

聚集索引 (clustered index )

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

  • 如果表定义了主键,则Primary Key 就是聚集索引;
  • 如果表没有定义主键,则第一个非空唯一索引(Not NULL Unique )列是聚集索引;
  • 否则,InnoDB 会创建一个隐藏的row-id 作为聚集索引;

普通索引(secondary index )

普通索引也称为二级索引,除聚集索引外,所有的索引都是普通索引,即非聚集索引。

[En]

Ordinary index is also called secondary index, except for clustered index, all indexes are ordinary index, that is, non-clustered index.

InnoDB的普通索引叶子节点存储的是主键(聚簇索引)的值,而MyISAM的普通索引存储的是记录指针。

三、回表示例

数据准备

先创建一张表 t_back_to_table ,表中id 为主键索引即聚簇索引,drinker_id 为普通索引。

CREATE TABLE t_back_to_table (

id INT PRIMARY KEY,

drinker_id INT NOT NULL,

drinker_name VARCHAR ( 15 ) NOT NULL,

drinker_feature VARCHAR ( 15 ) NOT NULL,

INDEX ( drinker_id )

) ENGINE = INNODB;

再执行下面的 SQL 语句,插入四条测试数据。

INSERT INTO t_back_to_table ( id, drinker_id, drinker_name, drinker_feature )

VALUES

( 1, 2, '广西-玉林', '喝到天亮' ),

( 2, 1, '广西-河池', '白酒三斤半啤酒随便灌' ),

( 3, 3, '广西-贵港', '喝到晚上' ),

( 4, 4, '广西-柳州', '喝酒不吃饭' );

NO回表case

使用主键索引id ,查询出id 为3 的数据。

EXPLAIN SELECT * FROM t_back_to_table WHERE id = 3;

执行 EXPLAIN SELECT * FROM t_back_to_table WHERE id = 3,这条SQL 语句就不需要回表。

因为是根据主键的查询方式,则只需要搜索 ID 这棵B+树,树上的叶子节点存储了行记录,根据这个唯一的索引,MySQL 就能确定搜索的记录。

回表case

使用 drinker_id 这个索引来查询drinker_id = 3 的记录时就会涉及到回表。

SELECT * FROM t_back_to_table WHERE drinker_id = 3;

因为通过 drinker_id 这个普通索引查询方式,则需要先搜索drinker_id 索引树(该索引树上记录着主键ID的值),然后得到主键ID 的值为3 ,再到 ID 索引树搜索一次。这个过程虽然用了索引,但实际上底层进行了两次索引查询,这个过程就称为回表。

回表小结

  • 发现基于非主键索引的查询需要多扫描一棵索引树,先定位主键值,再定位行记录,性能低于扫描索引树。
    [En]

    it is found that queries based on non-primary key indexes need to scan one more index tree, first locate the primary key value, and then locate the row records, and its performance is lower than that of scanning the index tree.*

  • 应用中应尽可能多地使用主键查询。这里,表中只有四条数据。如果数据量很大,显然使用主键查询效率更高。
    [En]

    the primary key query should be used as much as possible in the application. Here there are only four pieces of data in the table. If the amount of data is large, it is obvious that using the primary key query is more efficient.*

  • 使用聚集索引(主键或第一个唯一索引)不会返回表,但普通索引将返回表。
    [En]

    using a clustered index (primary key or the first unique index) will not return to the table, but a normal index will.*

四、索引存储结构

InnoDB 引擎的聚集索引和普通索引都是B+Tree 存储结构,只有叶子节点存储数据。

  • 新的B+树结构没有在所有的节点里存储记录数据,而是只在最下层的叶子节点存储,上层的所有非叶子节点只存放索引信息,这样的结构可以让单个节点存放更多索引值,增大Degree 的值,提高命中目标记录的几率。
  • 这种结构会在上层的非叶子节点存储一些冗余数据,但这种缺点是可以容忍的,因为冗余数据是索引数据,不会对内存造成沉重的负担。
    [En]

    this structure will store some redundant data in the upper non-leaf nodes, but such shortcomings can be tolerated, because the redundant data are index data and will not impose a heavy burden on memory.*

聚簇索引

id 是主键,所以是聚簇索引,其叶子节点存储的是对应行记录的数据。

聚簇索引存储结构

如果查询条件为主键(聚簇索引),则只需扫描一次B+树即可通过聚簇索引定位到要查找的行记录数据。

如:

SELECT * FROM t_back_to_table WHERE id = 1;

查找过程:

聚簇索引查找过程

普通索引

drinker_id 是普通索引(二级索引),非聚簇索引的叶子节点存储的是聚簇索引的值,即主键ID的值。

普通索引存储结构

MySQL 回表

如果查询条件为普通索引(非聚簇索引),需要扫描两次B+树。

  • 第一次扫描通过普通索引定位聚集索引的值。
    [En]

    the first scan locates the value of the clustered index through the ordinary index.*

  • 第二次扫描通过第一次扫描获得的簇索引值定位到要查找的行记录数据。
    [En]

    the value of the cluster index obtained by the second scan through the first scan is located to the row record data to be found.*

如:

SELECT * FROM t_back_to_table WHERE drinker_id = 1;

(1)第一步,先通过普通索引定位到主键值id=1;

(2 )第二步,回表查询,再通过定位到的主键值即聚集索引定位到行记录数据。

普通索引查找过程

MySQL 回表

五、如何防止回表

既然我们知道有退表这种事,我们就必须尽最大努力防范。防止表返回的最常见方法是索引覆盖,这会通过索引破坏索引。

[En]

Now that we know that there is such a thing as returning a watch, we must try our best to guard against it. The most common way to prevent table return is index overwriting, which defeats the index through the index.

索引覆盖

为什么可以使用索引打败索引防止回表呢?因为其只需要在一棵索引树上就能获取SQL所需的所有列数据,无需回表查询。

例如:SELECT * FROM t_back_to_table WHERE drinker_id = 1;

如何实现覆盖索引?

一种常见的方法是将查询的字段构建到联邦索引中。

[En]

A common method is to build the queried fields into the federated index.

解释性SQL的explain的输出结果Extra字段为Using index时表示触发了索引覆盖。

No覆盖索引case1

继续使用之前创建的 t_back_to_table 表,通过普通索引drinker_id 查询id 和 drinker_id 列。

EXPLAIN SELECT id, drinker_id FROM t_back_to_table WHERE drinker_id = 1;

explain分析:为什么没有创建覆盖索引Extra字段仍为Using index,因为drinker_id 是普通索引,使用到了drinker_id 索引,在上面有提到普通索引的叶子节点保存了聚簇索引的值,所以通过一次扫描B+树即可查询到相应的结果,这样就实现了隐形的覆盖索引,即没有人为的建立联合索引。(drinker_id 索引上包含了主键索引的值)

No覆盖索引case2

继续使用之前创建的 t_back_to_table 表,通过普通索引drinker_id 查询id 、drinker_id 和drinker_feature 三列数据。

EXPLAIN SELECT id, drinker_id, drinker_feature FROM t_back_to_table WHERE drinker_id = 1;

MySQL 回表

explain分析:drinker_id 是普通索引其叶子节点上仅包含主键索引的值,而drinker_feature 列并不在索引树上,所以通过drinker_id 索引在查询到id和drinker_id 的值后,需要根据主键id 进行回表查询,得到drinker_feature 的值。此时的Extra列的NULL表示进行了回表查询。

覆盖索引case

为了实现索引覆盖,需要建组合索引 idx_drinker_id_drinker_feature(drinker_id,drinker_feature)

#删除索引 drinker_id

DROP INDEX drinker_id ON t_back_to_table;

#建立组合索引

CREATE INDEX idx_drinker_id_drinker_feature on t_back_to_table(drinker_id,drinker_feature);

继续使用之前创建的 t_back_to_table 表,通过覆盖索引idx_drinker_id_drinker_feature 查询 id 、drinker_id 和drinker_feature 三列数据。

EXPLAIN SELECT id, drinker_id, drinker_feature FROM t_back_to_table WHERE drinker_id = 1;

MySQL 回表

explain分析:此时字段drinker_id 和drinker_feature 是组合索引idx_drinker_id_drinker_feature ,查询的字段id、drinker_id 和drinker_feature 的值刚刚都在索引树上,只需扫描一次组合索引B+树即可,这就是实现了索引覆盖,此时的Extra字段为Using index表示使用了索引覆盖。

六、索引覆盖优化SQL 场景

适合使用索引覆盖来优化SQL的场景如全表count查询、列查询回表和分页查询等。

全表count查询优化

#首先删除 t_back_to_table 表中的组合索引

DROP INDEX idx_drinker_id_drinker_feature ON t_back_to_table;

EXPLAIN SELECT COUNT(drinker_id) FROM t_back_to_table

explain分析:此时的Extra字段为Null 表示没有使用索引覆盖。

使用索引覆盖优化,创建drinker_id 字段索引。

#创建 drinker_id 字段索引

CREATE INDEX idx_drinker_id on t_back_to_table(drinker_id);

EXPLAIN SELECT COUNT(drinker_id) FROM t_back_to_table

MySQL 回表

explain分析:此时的Extra字段为Using index表示使用了索引覆盖。

列查询回表优化

前面描述使用索引覆盖的示例是列查询回表优化。

[En]

The previous example that describes the use of index overrides is column query back table optimization.

例如:

SELECT id, drinker_id, drinker_feature FROM t_back_to_table WHERE drinker_id = 1;

使用索引覆盖:建组合索引 idx_drinker_id_drinker_feature on t_back_to_table(drinker_id,drinker_feature)即可。

分页查询优化

#首先删除 t_back_to_table 表中的索引 idx_drinker_id

DROP INDEX idx_drinker_id ON t_back_to_table;

EXPLAIN SELECT id, drinker_id, drinker_name, drinker_feature FROM t_back_to_table ORDER BY drinker_id limit 200, 10;

MySQL 回表

explain分析:因为drinker_id 字段不是索引,所以在分页查询需要进行回表查询,此时Extra为U sing filesort 文件排序,查询性能低下。

使用索引覆盖:建组合索引idx_drinker_id_drinker_name_drinker_feature

#建立组合索引 idx_drinker_id_drinker_name_drinker_feature (drinker_id,drinker_name,drinker_feature)

CREATE INDEX idx_drinker_id_drinker_name_drinker_feature on t_back_to_table(drinker_id,drinker_name,drinker_feature);

再次根据 drinker_id 分页查询:

EXPLAIN SELECT id, drinker_id, drinker_name, drinker_feature FROM t_back_to_table ORDER BY drinker_id limit 200, 10;

MySQL 回表

explain分析:此时的Extra字段为Using index表示使用了索引覆盖。

五花马

千金裘

呼儿将出换美酒

与尔同销万古愁

Original: https://www.cnblogs.com/taojietaoge/p/16167188.html
Author: 涛姐涛哥
Title: MySQL 回表

原创文章受到原创版权保护。转载请注明出处:https://www.johngo689.com/505458/

转载文章受原作者版权保护。转载请注明原作者出处!

(0)

大家都在看

  • Springboot 注解失效问题 @Async, @Transactional, @Subsribe

    问题描述:在一个Service的某个方法上使用了guava eventbus @subscribe注解,最开始发现没有收到消息,debug发现是该service的该方法没有注册到e…

    数据库 2023年6月11日
    0136
  • 6_SSM

    一. ssm整合 1.1 项目创建 1.1.1 创建Maven项目 创建一个普通的Maven-web项目,名称为ssm 记得勾选上Create from archetype然后选择…

    数据库 2023年6月11日
    0105
  • 2021长安杯wp

    案件背景 2021年4月25日,上午8点左右,警方接到被害人金某报案,声称自己被敲诈数万元;经询问,昨日金某被嫌疑人诱导果聊,下载了某果聊软件,导致自己的通讯录和果聊视频被嫌疑人获…

    数据库 2023年6月11日
    0148
  • Docker 部署前后端项目

    Docker 部署前后端项目 平生不会相思,才会相思,便害相思。 简介:都是被逼的,从零开始一个Docker 部署九个微服务和三个前端项目。其中,这些服务需要用到Nacos、MyS…

    数据库 2023年6月14日
    0133
  • lvs负载均衡

    Lvs 一.Lvs简介 二. 体系结构 三. Lvs管理工具 1. ipvs 2. ipvsadm 四.lvs工作模式及原理 1.NAT模式 2. DR模式 3.TUN模式(隧道模…

    数据库 2023年6月14日
    0152
  • String 对象

    String对象及底层区别 String 对象 两种方式 方式一:直接使用双引号得到字符串对象 代码: //方式一:直接使用双引号得到字符串对象 String name = &qu…

    数据库 2023年6月16日
    0126
  • MySQL实战45讲 3

    03 | 事务隔离:为什么你改了我还看不见? 事务 Transaction TRX 事务就是 要保证一组数据库操作,要么全部成功,要么全部失败。 MySQL 原生的 MyISAM …

    数据库 2023年5月24日
    0152
  • IDEA中tomcat插件版本7中文乱码问题

    tomcat插件版本7中文乱码问题 IDEA中tomcat插件版本7中文乱码问题 问题描述: 因为idea中tomcat插件版本只到7,他的默认解码方式为:ISO-8859-1,又…

    数据库 2023年6月16日
    0104
  • CentOS 7 RabbitMQ集群部署

    基于的RabbitMQ版本:3.8.5 Status of node rabbit@node236 … Runtime OS PID: 13975 OS: Linux Upti…

    数据库 2023年6月9日
    0157
  • MySQL——基础查询与条件查询

    基础查询 /* 语法: select 查询列表 from 表名; 类似于:System.out.println(打印东西); 1、查询列表可以是:表中的字段、常量值、表达式、函数 …

    数据库 2023年5月24日
    0163
  • 重写Feign编码器

    有个spring cloud 架构的项目需要调用php小组的api接口,但php提供的接口入参大部分是下划线命名,而Java这边的实体类是按照驼峰编写,如果使用Fegin调用会导致…

    数据库 2023年6月6日
    0154
  • Java数据结构和算法

    一、数据结构 数据结构是计算机存储、组织数据的方式,指相互之间存在一种或多种特定关系的数据元素的集合。 通常情况下,精心选择的数据结构可以带来更高的运行或者存储效率。数据结构往往同…

    数据库 2023年6月11日
    0147
  • 老毛桃WinPE以ISO镜像模式安装CentOS7

    准备: 一台新台式机(2TB磁盘),将用于安装centos 7,注意安装前请使用工具将磁盘分区先删除。 一个u盘,用户制作老毛桃winpe启动盘。 从www.laomaotao.o…

    数据库 2023年6月14日
    0141
  • CSS进阶内容——布局技巧和细节修饰

    CSS进阶内容——布局技巧和细节修饰 我们在之前的文章中已经掌握了CSS的大部分内容,但仍有一些内容我们没有涉略,这篇文章就是为了补充前面没有涉及的内容,为我们的知识做出补充并且介…

    数据库 2023年6月14日
    0141
  • JMeter接口自动化发包与示例

    JMeter接口自动化发包与示例 近期需要完成对于接口的测试,于是了解并简单做了个测试示例,看了看这款江湖上声名远播的强大的软件-Jmeter靠不靠谱。官网:https://jme…

    数据库 2023年6月6日
    0116
  • DECIMAL 数据处理原理浅析

    注:本文分析内容基于 MySQL 8.0 版本 文章开始前先复习一下官方文档关于 DECIMAL 类型的一些介绍: The declaration syntax for a DEC…

    数据库 2023年5月24日
    095
亲爱的 Coder【最近整理,可免费获取】👉 最新必读书单  | 👏 面试题下载  | 🌎 免费的AI知识星球