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

2024-08-23 02:36

本文主要是介绍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_namesalary,我们可以利用之前创建的复合索引来进行索引覆盖查询。

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

image-20240822210807896

在这个查询中,first_namesalary两个字段都包含在索引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

相关文章

Springboot中分析SQL性能的两种方式详解

《Springboot中分析SQL性能的两种方式详解》文章介绍了SQL性能分析的两种方式:MyBatis-Plus性能分析插件和p6spy框架,MyBatis-Plus插件配置简单,适用于开发和测试环... 目录SQL性能分析的两种方式:功能介绍实现方式:实现步骤:SQL性能分析的两种方式:功能介绍记录

使用 sql-research-assistant进行 SQL 数据库研究的实战指南(代码实现演示)

《使用sql-research-assistant进行SQL数据库研究的实战指南(代码实现演示)》本文介绍了sql-research-assistant工具,该工具基于LangChain框架,集... 目录技术背景介绍核心原理解析代码实现演示安装和配置项目集成LangSmith 配置(可选)启动服务应用场景

oracle DBMS_SQL.PARSE的使用方法和示例

《oracleDBMS_SQL.PARSE的使用方法和示例》DBMS_SQL是Oracle数据库中的一个强大包,用于动态构建和执行SQL语句,DBMS_SQL.PARSE过程解析SQL语句或PL/S... 目录语法示例注意事项DBMS_SQL 是 oracle 数据库中的一个强大包,它允许动态地构建和执行

SQL 中多表查询的常见连接方式详解

《SQL中多表查询的常见连接方式详解》本文介绍SQL中多表查询的常见连接方式,包括内连接(INNERJOIN)、左连接(LEFTJOIN)、右连接(RIGHTJOIN)、全外连接(FULLOUTER... 目录一、连接类型图表(ASCII 形式)二、前置代码(创建示例表)三、连接方式代码示例1. 内连接(I

在MySQL执行UPDATE语句时遇到的错误1175的解决方案

《在MySQL执行UPDATE语句时遇到的错误1175的解决方案》MySQL安全更新模式(SafeUpdateMode)限制了UPDATE和DELETE操作,要求使用WHERE子句时必须基于主键或索引... mysql 中遇到的 Error Code: 1175 是由于启用了 安全更新模式(Safe Upd

轻松上手MYSQL之JSON函数实现高效数据查询与操作

《轻松上手MYSQL之JSON函数实现高效数据查询与操作》:本文主要介绍轻松上手MYSQL之JSON函数实现高效数据查询与操作的相关资料,MySQL提供了多个JSON函数,用于处理和查询JSON数... 目录一、jsON_EXTRACT 提取指定数据二、JSON_UNQUOTE 取消双引号三、JSON_KE

MySql死锁怎么排查的方法实现

《MySql死锁怎么排查的方法实现》本文主要介绍了MySql死锁怎么排查的方法实现,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面随着小编来一起学习学习吧... 目录前言一、死锁排查方法1. 查看死锁日志方法 1:启用死锁日志输出方法 2:检查 mysql 错误

MySQL数据库函数之JSON_EXTRACT示例代码

《MySQL数据库函数之JSON_EXTRACT示例代码》:本文主要介绍MySQL数据库函数之JSON_EXTRACT的相关资料,JSON_EXTRACT()函数用于从JSON文档中提取值,支持对... 目录前言基本语法路径表达式示例示例 1: 提取简单值示例 2: 提取嵌套值示例 3: 提取数组中的值注意

MySQL修改密码的四种实现方式

《MySQL修改密码的四种实现方式》文章主要介绍了如何使用命令行工具修改MySQL密码,包括使用`setpassword`命令和`mysqladmin`命令,此外,还详细描述了忘记密码时的处理方法,包... 目录mysql修改密码四种方式一、set password命令二、使用mysqladmin三、修改u

查询SQL Server数据库服务器IP地址的多种有效方法

《查询SQLServer数据库服务器IP地址的多种有效方法》作为数据库管理员或开发人员,了解如何查询SQLServer数据库服务器的IP地址是一项重要技能,本文将介绍几种简单而有效的方法,帮助你轻松... 目录使用T-SQL查询方法1:使用系统函数方法2:使用系统视图使用SQL Server Configu