首页 > 数据库 >MySQL- 索引下推

MySQL- 索引下推

时间:2024-08-13 22:27:21浏览次数:14  
标签:salary 下推 查询 回表 索引 MySQL

索引下推(Index Condition Pushdown,简称 ICP)是 MySQL 5.6 引入的一项优化技术,它通过将部分查询条件“下推”到索引扫描阶段,从而减少不必要的行访问和回表操作,提高查询性能。

1. 索引下推的概念

在传统的索引扫描过程中,MySQL 会首先通过索引找到符合索引条件的记录,然后回表(即访问实际的表数据行)读取所需的列,最后再应用其他过滤条件(非索引条件)来判断这条记录是否符合查询要求。这意味着即使某些记录最终被过滤掉了,MySQL 也必须先回表读取它们的全部数据,这会导致额外的 I/O 操作和性能开销。

索引下推优化的思想是:在索引扫描阶段,将部分查询条件直接应用于索引记录,从而减少回表操作。只有在索引中满足所有条件的记录才会被回表读取其完整数据。

2. 索引下推的工作原理

索引下推的工作原理可以通过以下步骤来理解:

  1. 索引扫描

    • MySQL 在索引中扫描符合索引条件的记录。
  2. 索引条件过滤

    • 在扫描索引记录时,MySQL 会将可以应用于索引的查询条件“下推”到索引扫描阶段。如果索引中的记录不符合这些条件,MySQL 会直接跳过该记录,不进行回表操作。
  3. 回表操作

    • 只有那些在索引中同时满足索引条件和下推条件的记录,MySQL 才会回表读取完整的数据行。
  4. 剩余条件过滤

    • 回表读取的数据行会进一步应用其他查询条件进行过滤,以确保最终返回的结果集是准确的。

3. 索引下推的示例

假设我们有一个表 employees,表结构如下:

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department_id INT,
    salary DECIMAL(10, 2),
    INDEX idx_lastname_salary(last_name, salary)
);

现在,我们有一个查询:

SELECT * FROM employees WHERE last_name LIKE 'S%' AND salary > 50000;
  • 索引 idx_lastname_salary 包含 last_namesalary 两个列。
  • 查询条件中的 last_name LIKE 'S%' 可以用索引来加速查找。
  • 查询条件中的 salary > 50000 也是 idx_lastname_salary 索引的一部分,但在传统情况下,它不会在索引扫描阶段应用,而是在回表之后再进行过滤。

没有索引下推的执行过程

  1. MySQL 使用 last_name LIKE 'S%' 在索引中找到所有符合条件的记录。
  2. 对于每一个符合条件的记录,MySQL 都会回表读取 salary 列的值。
  3. 回表后的数据行会被检查 salary > 50000 这个条件,不满足的记录会被过滤掉。

启用索引下推后的执行过程

  1. MySQL 使用 last_name LIKE 'S%' 在索引中找到符合条件的记录。
  2. 在索引扫描过程中,MySQL 直接在索引中检查 salary > 50000 这个条件,只有满足条件的记录才会进行回表操作。
  3. 由于很多不符合 salary > 50000 的记录在索引扫描阶段就被过滤掉,回表操作大幅减少,查询性能提升。

4. 索引下推的好处

  • 减少回表操作:通过将更多的条件在索引扫描阶段应用,索引下推减少了不必要的回表操作,减少了 I/O 开销。
  • 提高查询性能:由于减少了数据行的访问次数,索引下推可以显著提高查询的整体性能。
  • 特别适合组合索引:在使用复合索引(多个列的联合索引)时,索引下推的优化效果尤为明显。

5. 索引下推的适用条件

索引下推优化的适用条件包括:

  • 查询中包含的条件是可以在索引中评估的。例如,如果索引包含的列可以满足查询中的部分条件,这些条件就可以被下推到索引扫描阶段。
  • 查询使用了复合索引,且索引中的多个列参与了查询条件的判断。

6. 如何查看索引下推是否生效

我们可以使用 EXPLAIN 语句来查看索引下推是否在查询中生效。

EXPLAIN SELECT * FROM employees WHERE last_name LIKE 'S%' AND salary > 50000;

EXPLAIN 输出的 Extra 列中,如果出现 Using index condition,这意味着 MySQL 在该查询中使用了索引下推优化。

7. 适用和不适用场景

适用场景

  • 使用组合索引且查询中涉及索引中的多个列时,索引下推可以有效减少回表操作。
  • 查询条件比较复杂,且可以在索引中进行部分判断的情况下,索引下推能够提高效率。

不适用场景

  • 如果查询中涉及的条件无法在索引中评估(如涉及计算或函数运算),则无法使用索引下推。
  • 如果查询中的条件涉及的列不在索引中,也无法使用索引下推。

8. 示例数据和执行计划

假设表中有如下数据:

INSERT INTO employees VALUES 
(1, 'Smith', 'John', 10, 60000),
(2, 'Smith', 'Alice', 10, 40000),
(3, 'Brown', 'Charlie', 20, 55000),
(4, 'Davis', 'David', 30, 45000);

执行查询:

EXPLAIN SELECT * FROM employees WHERE last_name LIKE 'S%' AND salary > 50000;

EXPLAIN 输出中,我们可能会看到类似以下的结果:

id | select_type | table     | type  | possible_keys   | key               | key_len | ref  | rows | Extra
-----------------------------------------------------------------------------------------------------------
1  | SIMPLE      | employees | range | idx_lastname_salary | idx_lastname_salary | 102    | NULL |  2   | Using index condition; Using where

Extra 列中显示 Using index condition,表示 MySQL 使用了索引下推来优化这个查询。

9. 总结

索引下推(ICP)是 MySQL 5.6 引入的一个重要优化技术,它通过将部分查询条件“下推”到索引扫描阶段来减少回表操作,从而提高查询性能。索引下推特别适合使用复合索引的场景,通过有效地减少不必要的 I/O 操作,能够显著提升查询的执行效率。在实际应用中,可以通过 EXPLAIN 语句来查看索引下推是否生效,并结合查询模式和索引设计来充分利用这一优化技术。

标签:salary,下推,查询,回表,索引,MySQL
From: https://blog.csdn.net/weixin_43844521/article/details/141175496

相关文章

  • MySQL使用教程笔记(远程访问)
    远程连接MySQL数据库_mysql远程访问数据库-CSDN博客如何远程连接mysql数据库服务器_mysql如何连接远程数据库服务器_如何连接远程数据库mysql-腾讯云开发者社区-腾讯云(tencent.com)如何使用C#连接远程MySQL服务器?_webrtc_weixin_0010034-MySQL数据库(csdn.net)c#中mysq......
  • 最全MySQL面试题和答案(四)
    1.数据库结构优化一个好的数据库设计方案对于数据库的性能往往会起到事半功倍的效果。优化设计需要考虑数据冗余、查询和更新的速度、字段的数据类型是否合理等多方面的因素。将字段很多的表分解成多个表概述:对于字段较多的表,如果有些字段的使用频率很低,可以将这些字段分离出来......
  • MySQL学习[4] ——MySQL锁
    四、MySQL锁4.1MySQL有哪些锁?4.1.1全局锁全局锁就是**对整个数据库实例加锁,主要用于全库逻辑备份**等场景。flushtableswithreadlock#加全局锁unlocktables#解锁加上全局(读)锁后,整个数据库都是只读状态。若数据库的数据较多,导致整个处理流程较慢,数据库......
  • MySQL8.0 Clone Plugin 实现解析浅析
    MySQL8.0ClonePlugin实现解析浅析从8.0.17版本开始官方实现了clone的功能,允许用户通过简单的SQL命令把远端/本地的数据库实例拷贝到其他实例后快速拉起一个新的实例。该功能由一些列的WL组成:Clonelocalreplica(WL#9209):实现了数据本地Clone。Cloneremotereplica......
  • MySQL数据库——数据库的数据类型(一)
    四、数据类型1.数据类型分类分类数据类型说明数值类型BIT(M)位类型。指定位数,默认值1,范围1-64TINYINT[UNSIGNED]带符号的范围-128127,无符号范围0255.默认有符号BOOL使用0和1表示真和假SMALLINT[UNSIGNED]带符号是-2^15次方到2^15-1,无符号是2^16-1IN......
  • 【JAVA_Mysql】JAVA连接Mysql基本步骤
    ......
  • MySQL:复杂查询(二)——联合查询02
    本篇博客接上篇,上篇已讲联合查询部分知识:MySQL:复杂查询(一)——聚合函数&分组查询&联合查询01-CSDN博客目录1、联合查询1.1外连接1.1.1右外连接RIGHTJOIN1.1.2左外连接LEFTJOIN1.2自连接1.3子查询1.3.1单行子查询1.3.2多行子查询[NOT]IN1.3.3 多列......
  • 尚硅谷MYSQL(第七章)
    从函数定义的角度出发,我们可以将函数分成内置函数和自定义函数。在SQL语言中,同样也包括了内置函数和自定义函数。内置函数是系统内置的通用函数,而自定义函数是我们根据自己的需要编写的里面有因子的话因子相同 随机数相同四舍五入中......
  • 数据库系统 第13节 索引
    索引(Indexing)是一种数据结构技术,广泛应用于数据库和文件系统中,用于提高数据检索的速度。在没有索引的情况下,数据库必须从头到尾扫描整个表来找到所需的数据行,这被称为全表扫描(FullTableScan),对于大型数据库来说效率极低。索引的基本原理索引类似于书籍的目录。当你需要查......
  • 《优化 SQL 索引策略:提升大规模数据检索效率的关键》
    在当今数字化时代,数据量呈爆炸式增长,企业和组织面临着处理大规模数据的巨大挑战。对于数据库管理员和开发者来说,如何在大规模数据环境中优化SQL中的索引策略,以减少数据检索时间,成为了至关重要的任务。索引是数据库中用于加速数据检索的重要结构。然而,在大规模数据场景下,......