MySQL索引下推的实现示例-编程学习网

1. 索引下推的概念

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

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

2. 索引下推的工作原理

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

索引扫描：MySQL 在索引中扫描符合索引条件的记录。
索引条件过滤：在扫描索引记录时，MySQL 会将可以应用于索引的查询条件“下推”到索引扫描阶段。如果索引中的记录不符合这些条件，MySQL 会直接跳过该记录，不进行回表操作。
回表操作：只有那些在索引中同时满足索引条件和下推条件的记录，MySQL 才会回表读取完整的数据行。
剩余条件过滤：回表读取的数据行会进一步应用其他查询条件进行过滤，以确保最终返回的结果集是准确的。

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_name 和 salary 两个列。
查询条件中的 last_name LIKE 'S%' 可以用索引来加速查找。
查询条件中的 salary > 50000 也是 idx_lastname_salary 索引的一部分，但在传统情况下，它不会在索引扫描阶段应用，而是在回表之后再进行过滤。

没有索引下推的执行过程：

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

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

MySQL 使用 last_name LIKE 'S%' 在索引中找到符合条件的记录。
在索引扫描过程中，MySQL 直接在索引中检查 salary > 50000 这个条件，只有满足条件的记录才会进行回表操作。
由于很多不符合 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 语句来查看索引下推是否生效，并结合查询模式和索引设计来充分利用这一优化技术。

到此这篇关于MySQL索引下推的实现示例的文章就介绍到这了,更多相关MySQL索引下推内容请搜索编程网(www.lsjlt.com)以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程客栈(www.lsjlt.com)！

阅读原文内容投诉

免责声明：

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的，并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据，供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

历年真题答案解析
备考技巧名师总结
高频考点精准押题

资料下载
历年真题

2024上半年软考中级软件测评师考试基础知识真题
193.9 KB下载数265
2024上半年软考中级软件设计师考试基础知识真题
191.63 KB下载数245
2023下半年-系统集成项目管理工程师-真题考点汇总（完整版）
143.91 KB下载数1148
2023年下半年系统集成项目管理工程师第一、二、三批次真题考点整理(考友回忆版)
183.71 KB下载数642
2023年上半年软考中级《系统集成项目管理工程师》-基础知识-考试真题及答案
644.84 KB下载数2756

2024年上半年信息系统项目管理师第二批次真题及答案解析（完整版）
难度 813人已做
查看
【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析
难度 354人已做
查看
【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析
难度 318人已做
查看
2024年上半年软考高项第一、二批次真题考点汇总（完整版）
难度 435人已做
查看
2024年上半年系统架构设计师考试综合知识真题
难度 224人已做
查看

发现更多好内容

猜你喜欢

AI推送时光机