MySQL中的回表查询、索引覆盖、索引下推

2024-08-23 02:36
文章标签 mysql 查询 覆盖 索引 database 下推 回表

本文主要是介绍MySQL中的回表查询、索引覆盖、索引下推，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

本文重点介绍索引中的常见概念：回表查询、索引覆盖、索引下推

image-20240822210201904

一、回表查询

我们首先理解：在InnoDB存储引擎中，根据索引的存储形式，又可以分为以下两种：

分类含义特点
聚集索引 (Clustered Index)将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据必须有，而且只有一个
二级索引 (Secondary Index)将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键可以存在多个

聚集索引选取规则

  • 如果存在主键，主键索引就是聚集索引。
  • 如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。
  • 如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引

聚集索引和二级索引的具体结构如下：

image-20240822194408392

  • 聚集索引的叶子节点下挂的是这一行的数据 。
  • 二级索引的叶子节点下挂的是该字段值对应的主键值

接下来，我们来分析一下，当我们执行如下的SQL语句时，具体的查找过程是什么样子的。

image-20240822194540592

具体过程如下:

  • ①. 由于是根据name字段进行查询，所以先根据name='Arm’到name字段的二级索引中进行匹配查找。但是在二级索引中只能查找到 Arm 对应的主键值 10。
  • ②. 由于查询返回的数据是*，所以此时，还需要根据主键值10，到聚集索引中查找10对应的记录，最终找到10对应的行row。
  • ③. 最终拿到这一行的数据，直接返回即可。

得到回表查询的概念：

回表查询： 这种先到二级索引中查找数据，找到主键值，然后再到聚集索引中根据主键值，获取数据的方式，就称之为回表查询。

以下两条SQL语句，那个执行效率高? 为什么?

-- A语句
-- 备注: id为主键，name字段创建的有索引；
select * from user where id = 10 ;

-- B语句
select * from user where name = 'Arm' ;

A 语句的执行性能要高于B 语句。

因为A语句直接走聚集索引，直接返回数据。 而B语句需要先查询name字段的二级索引，然后再查询聚集索引，也就是需要进行回表查询。

二、索引覆盖

索引覆盖（Index Covering）是指通过在索引中包含所有查询语句中所需的列，可以避免对表中的数据进行额外的访问，从而提高查询效率。(避免了回表操作)

例如，对于一个查询语句：

SELECT col1, col2, col3 FROM table WHERE col1 = x AND col2 = y

如果在table表中建立了一个索引，包含col1、col2和col3三列，那么MySQL可以通过索引定位到符合条件的数据，并在索引中提取col1、col2和col3列的值，无需对表中的数据进行额外的访问。这种方式就叫做索引覆盖。

索引覆盖能够显著提高查询效率，因此在建立索引时应尽量考虑包含查询语句中所需的所有列。

我们实际性进行测试：

-- 创建数据库
CREATE DATABASE IndexCoveringDemo;
USE IndexCoveringDemo;-- 创建表
CREATE TABLE employees (id INT PRIMARY KEY AUTO_INCREMENT,first_name VARCHAR(50),last_name VARCHAR(50),department VARCHAR(50),salary DECIMAL(10, 2),INDEX idx_name_department_salary (first_name, department, salary)
);

-- 插入随机数据
INSERT INTO employees (first_name, last_name, department, salary)
VALUES
('John', 'Doe', 'Engineering', 75000.00),
('Jane', 'Smith', 'Marketing', 55000.00),
('Alice', 'Johnson', 'Sales', 60000.00),
('Bob', 'Brown', 'Engineering', 80000.00),
('Charlie', 'Davis', 'HR', 50000.00),
('Emily', 'Wilson', 'Sales', 65000.00),
('David', 'Clark', 'Engineering', 70000.00),
('Frank', 'Moore', 'Marketing', 52000.00),
('Grace', 'Taylor', 'HR', 48000.00),
('Henry', 'Miller', 'Sales', 72000.00);

假设我们要查询Engineering部门中first_name和salary，我们可以利用之前创建的复合索引来进行索引覆盖查询。

EXPLAIN SELECT first_name, salary FROM employees WHERE department = 'Engineering';

image-20240822210807896

在这个查询中，first_name和salary两个字段都包含在索引idx_name_department_salary中，department字段是索引的一部分。这个查询将可以通过索引直接返回数据，而不需要访问实际的表数据。

如果我们查询的字段不完全包含在索引中，则MySQL将无法进行索引覆盖，需要访问表数据。

EXPLAIN SELECT first_name, last_name FROM employees  WHERE department = 'Engineering';

image-20240822210942194

在这个查询中，last_name字段不在索引idx_name_department_salary中，因此MySQL不能使用索引覆盖查询。

索引覆盖能够显著提升查询性能，尤其是在涉及大量数据时。通过合理设计复合索引，可以使查询仅通过索引就能返回所有所需的数据，从而减少磁盘I/O并加快查询速度。在设计索引时，需要权衡字段选择，确保常用查询尽可能通过索引覆盖来优化。

三、索引下推

索引下推（INDEX CONDITION PUSHDOWN，简称 ICP）是在 MySQL 5.6 针对扫描二级索引的一项优化改进。总的来说是通过把索引过滤条件下推到存储引擎，来减少 MySQL 存储引擎访问基表的次数以及 MySQL 服务层访问存储引擎的次数。ICP 适用于 MYISAM 和 INNODB，本篇的内容只基于 INNODB。

image_aYUPBYmp7a

  • MySQL 服务层：也就是 SERVER 层，用来解析 SQL 的语法、语义、生成查询计划、接管从 MySQL 存储引擎层上推的数据进行二次过滤等等。
  • MySQL 存储引擎层：按照 MySQL 服务层下发的请求，通过索引或者全表扫描等方式把数据上传到 MySQL 服务层。
  • MySQL 索引扫描：根据指定索引过滤条件，遍历索引找到索引键对应的主键值后回表过滤剩余过滤条件。
  • MySQL 索引过滤：通过索引扫描并且基于索引进行二次条件过滤后再回表。

img

  • 使用索引下推实现

img

索引下推的使用条件

  • ICP目标是减少全行记录读取，从而减少IO 操作，只能用于非聚簇索引。聚簇索引本身包含的表数据，也就不存在下推一说。
  • 只能用于range、 ref、 eq_ref、ref_or_null访问方法；
  • where 条件中是用 and 而非 or 的时候。
  • ICP适用于分区表。
  • ICP不支持基于虚拟列上建立的索引，比如说函数索引
  • ICP不支持引用子查询作为条件。
  • ICP不支持存储函数作为条件，因为存储引擎无法调用存储函数。

索引下推相关语句:

# 查看索引下推是否开启
select @@optimizer_switch
# 开启索引下推
set optimizer_switch="index_condition_pushdown=on";
# 关闭索引下推
set optimizer_switch="index_condition_pushdown=off";

我们来进行具体的测试：

CREATE DATABASE icp_demo;
USE icp_demo;CREATE TABLE employees (emp_id INT AUTO_INCREMENT PRIMARY KEY,emp_name VARCHAR(255),dept_id INT,salary DECIMAL(10, 2),hire_date DATE,INDEX idx_dept_salary (dept_id, salary)
) ENGINE=InnoDB;

DELIMITER $$
CREATE PROCEDURE populate_employees()
BEGINDECLARE i INT DEFAULT 1;WHILE i <= 100000 DOINSERT INTO employees (emp_name, dept_id, salary, hire_date) VALUES (CONCAT('Employee_', i), FLOOR(RAND() * 10), ROUND(RAND() * 100000, 2), CURDATE() - INTERVAL FLOOR(RAND() * 3650) DAY);SET i = i + 1;END WHILE;
END$$
DELIMITER ;

CALL populate_employees();

在执行查询之前，我们可以先查看索引下推是否开启。

SELECT @@optimizer_switch;

image-20240822205816785

SET optimizer_switch = 'index_condition_pushdown=on';

EXPLAIN SELECT emp_id, emp_name FROM employees WHERE dept_id = 5 AND salary BETWEEN 50000 AND 80000;

SET optimizer_switch = 'index_condition_pushdown=off';

EXPLAIN SELECT emp_id, emp_name FROM employees WHERE dept_id = 5 AND salary BETWEEN 50000 AND 80000;

image-20240822210015283

记得再次打开：

SET optimizer_switch = 'index_condition_pushdown=on';

https://mp.weixin.qq.com/s?__biz=MzkwOTczNzUxMQ==&mid=2247484267&idx=1&sn=be0d6295a3992d13d76dc4d6c5b34ba6&chksm=c1376823f640e135895626711aadd115d0d40ff77a3f40157130b4199e4487b894414b5795be#rd

请添加图片描述

这篇关于MySQL中的回表查询、索引覆盖、索引下推的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/1098106。

相关文章

SQL中的外键约束

SQL中的外键约束

外键约束用于表示两张表中的指标连接关系。外键约束的作用主要有以下三点： 1.确保子表中的某个字段（外键）只能引用父表中的有效记录2.主表中的列被删除时，子表中的关联列也会被删除3.主表中的列更新时，子表中的关联元素也会被更新 子表中的元素指向主表 以下是一个外键约束的实例展示
阅读更多...
基于MySQL Binlog的Elasticsearch数据同步实践

基于MySQL Binlog的Elasticsearch数据同步实践

一、为什么要做 随着马蜂窝的逐渐发展，我们的业务数据越来越多，单纯使用 MySQL 已经不能满足我们的数据查询需求，例如对于商品、订单等数据的多维度检索。 使用 Elasticsearch 存储业务数据可以很好的解决我们业务中的搜索需求。而数据进行异构存储后，随之而来的就是数据同步的问题。 二、现有方法及问题 对于数据同步，我们目前的解决方案是建立数据中间表。把需要检索的业务数据，统一放到一张M
阅读更多...
如何去写一手好SQL

如何去写一手好SQL

MySQL性能 最大数据量 抛开数据量和并发数，谈性能都是耍流氓。MySQL没有限制单表最大记录数，它取决于操作系统对文件大小的限制。 《阿里巴巴Java开发手册》提出单表行数超过500万行或者单表容量超过2GB，才推荐分库分表。性能由综合因素决定，抛开业务复杂度，影响程度依次是硬件配置、MySQL配置、数据表设计、索引优化。500万这个值仅供参考，并非铁律。 博主曾经操作过超过4亿行数据
阅读更多...
性能分析之MySQL索引实战案例

性能分析之MySQL索引实战案例

文章目录 一、前言二、准备三、MySQL索引优化四、MySQL 索引知识回顾五、总结 一、前言 在上一讲性能工具之 JProfiler 简单登录案例分析实战中已经发现SQL没有建立索引问题，本文将一起从代码层去分析为什么没有建立索引？ 开源ERP项目地址：https://gitee.com/jishenghua/JSH_ERP 二、准备 打开IDEA找到登录请求资源路径位置
阅读更多...
MySQL数据库宕机，启动不起来，教你一招搞定！

MySQL数据库宕机，启动不起来，教你一招搞定！

作者介绍：老苏，10余年DBA工作运维经验，擅长Oracle、MySQL、PG、Mongodb数据库运维（如安装迁移，性能优化、故障应急处理等）公众号：老苏畅谈运维欢迎关注本人公众号，更多精彩与您分享。 MySQL数据库宕机，数据页损坏问题，启动不起来，该如何排查和解决，本文将为你说明具体的排查过程。 查看MySQL error日志 查看 MySQL error日志，排查哪个表（表空间
阅读更多...
活用c4d官方开发文档查询代码

活用c4d官方开发文档查询代码

当你问AI助手比如豆包，如何用python禁止掉xpresso标签时候，它会提示到 这时候要用到两个东西。https://developers.maxon.net/论坛搜索和开发文档 比如这里我就在官方找到正确的id描述 然后我就把参数标签换过来
阅读更多...
MySQL高性能优化规范

MySQL高性能优化规范

前言：      笔者最近上班途中突然想丰富下自己的数据库优化技能。于是在查阅了多篇文章后，总结出了这篇！ 数据库命令规范 所有数据库对象名称必须使用小写字母并用下划线分割 所有数据库对象名称禁止使用mysql保留关键字（如果表名中包含关键字查询时，需要将其用单引号括起来） 数据库对象的命名要能做到见名识意，并且最后不要超过32个字符 临时库表必须以tmp_为前缀并以日期为后缀，备份
阅读更多...
[MySQL表的增删改查-进阶]

[MySQL表的增删改查-进阶]

🌈个人主页：努力学编程’ ⛅个人推荐： c语言从初阶到进阶 JavaEE详解 数据结构 ⚡学好数据结构，刷题刻不容缓：点击一起刷题 🌙心灵鸡汤：总有人要赢，为什么不能是我呢 💻💻💻数据库约束 🔭🔭🔭约束类型 not null: 指示某列不能存储 NULL 值unique: 保证某列的每行必须有唯一的值default: 规定没有给列赋值时的默认值.primary key:
阅读更多...
MySQL-CRUD入门1

MySQL-CRUD入门1

文章目录 认识配置文件client节点mysql节点mysqld节点 数据的添加(Create)添加一行数据添加多行数据两种添加数据的效率对比 数据的查询(Retrieve)全列查询指定列查询查询中带有表达式关于字面量关于as重命名 临时表引入distinct去重order by 排序关于NULL 认识配置文件 在我们的MySQL服务安装好了之后, 会有一个配置文件, 也就
阅读更多...
最大流=最小割=最小点权覆盖集=sum-最大点权独立集

最大流=最小割=最小点权覆盖集=sum-最大点权独立集

二分图最小点覆盖和最大独立集都可以转化为最大匹配求解。 在这个基础上，把每个点赋予一个非负的权值，这两个问题就转化为：二分图最小点权覆盖和二分图最大点权独立集。   二分图最小点权覆盖     从x或者y集合中选取一些点，使这些点覆盖所有的边，并且选出来的点的权值尽可能小。 建模：     原二分图中的边(u,v)替换为容量为INF的有向边(u,v)，设立源点s和汇点t
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2