MySQL查询优化的三种处理阶段(Index Key、Index Filter和Table Filter)详解

目录

• 1. Ind编程ex Key
• 2. Index Filter
• 3. Table Filter
• 三类过滤物理过程
• 索引下推重要点
• 示例
• 索引下推
• 传统索引扫描
• 有索引下推的查询流程
• 优势
• 使用注意
• 如何验证索引下推
• 小结

在 mysql 中，索引主要用于优化查询。在 MySQL 查询优化中涉及到三种处理阶段：Index Key、Index Filter 和 Table Filter。

它们描述的是数据库在使用索引时，查询条件的匹配和过滤发生的过程，这个分类的背景通常出现在讨论索引下推（Index Condition Pushdown, ICP）时。

以下是对这三种索引处理阶段的详细说明：

1. Index Key

定义：索引键 (Index Key) 是指索引的关键列，即索引中存储的字段值。索引键是用来帮助定位数据行的，查询通常首先使用索引键来筛选匹配最基础条件的记录。

使用场景：

当查询条件和索引的定义一致时，例如：

SELECT * FROM employees WHERE employee_id = 123;

如果 employee_id 有索引，数据库可以通过索引键快速找到符合条件的记录。

特性：

• 这是索引的基本功能，通过索引键直接定位满足条件的记录。
• 索引键检索的是精确匹配或者范围扫描的记录。

查询执行流程：

通过索引键快速定位对应的记录集合。

2. Index Filter

定义：索引过滤 (Index Filter) 是对索引存储的数据进行进一步编程客栈过滤，用于实现更复杂的查询条件，而无需先通过索引定位所有数据然后回表。索引过滤是在存储引擎层完成的，是索引下推优化的关键部分。

使用场景：

查询条件涉及多个字段，但不是全部字段都能通过索引键直接定位。例如：

SELECT * FROM employees WHERE employee_id > 100 AND salary < 50000;

假设有索引 (employee_id, salary)：

• 数据库通过 employee_id > 100 定位部分范围的记录；
• 然后在存储层通过 salary < 50000 进一步过滤索引中的记录，而不是直接将所有匹配 employee_id > 100 的记录返回到 Server 层。

特性：

• 索引过滤是对索引本身存储的数据进行字段值筛选，而不是直接访问表。
• 索引下推优化后，在存储引擎层完成这部分过滤，提高了查询效率。

查询执行流程：

• 基于索引键定位候选记录。
• 在存储层进一步筛选索引中的记录，减少上层（Server Layer）需要处理的数据量。

3. Table Filter

定义：表过滤 (Table Filter) 是指数据库通过回表查询数据后，再对返回的表中数据进行过滤。这通常是针对查询条件中涉及的非索引列，或者索引本身无法过滤的情况。

使用场景：

查询条件涉及非索引字段，例如：

SELECT * FROM employees WHERE employee_id > 100 AND department = 'Engineering';

假设只有索引 (employee_id)：

• 数据库通过索引范围查询 employee_id > 100；
• 获取记录后，需要回表读取 department 列，并在 Server 层过滤 department = 'Engineering' 的条件。

特性：

• 表过滤发生在 Server 层（服务层），需要通过索引定位记录后，回表查询原始记录再进行过滤。
• 如果查询条件中非索引列过多，或者数据量较大，表过滤会带来性能开销。

查询执行流程：

• 基于索引键定位候选记录。
• 回表查询原始www.devze.com数据。
• 在 Server 层对数据进行过滤，符合条件的记录才会返回给用户。

三类过滤物理过程

Index Key 初始阶段，通过索引键快速定位候选记录。

Index Filter 在存储引擎层上对候选记录进行进一步过滤，减少需要回表的记录数。

Table Filter 如果查询涉及非索引列或更复杂的过滤条件，需要回表查询，并在服务器层最终过滤。

索引下推重要点

MySQL 5.6 之前，一旦记录在索引 Key 查找到，所有复杂条件的过滤都在 Server 层完成（包括非下推的 Index Filter 和 Table Filter）php。

MySQL 5.6 开始支持索引下推 (ICP)，将部分过滤逻辑 (Index Filter) 下推到存储引擎层，并在回表查询之前完成过滤，显著减少了回表次数和 Server 层的压力。

示例

假设有一个包含索引 (employee_id, salary) 的表，查询如下：

SELECT * FROM employees WHERE employee_id > 100 AND salary < 50000 AND department = 'Engineering';

• Index Key: 索引通过 employee_id > 100 进行范围扫描，获取候选记录。
• Index Filter（索引下推实现）: 在存储层进一步通过 salary < 50000 过滤出满足条件的记录，减少回表的次数。
• Table Filter: 回表查询后，对 department = 'Engineering' 的条件进行过滤，最终返回结果。

索引下推

索引下推（Index Condition Pushdown, ICP）是数据库查询优化的一种技术。它主要用于提升数据库查询性能，尤其是顺序扫描大表或使用索引进行过滤时。索引下推在 MySQL 5.6 引入，是针对索引的查询优化。

简单解释

索引下推的核心思想是把一部分查询条件“下推”到存储层的索引扫描过程，而无需每次都把数据从存储层读到服务层做判断。这样可以减少需要访问的数据行数，从而优化查询速度。

传统索引扫描

在没有索引下推时，当查询涉及多个筛选条件（WHERE 子句）时，数据库先通过索引查找到满足部分条件的记录，但并不会马上应用所有的条件过滤。它会将索引匹配到的记录获取到服务层（Server Layer）后再检查剩余的条件是否符合，然后决定结果是否返回给用户。

这种做法在数据量大或涉及复杂条件时，可能会导致服务层不得不处理大量不必要的数据记录，从而性能不佳。

有索引下推的查询流程

索引下推允许直接在存储引擎层应用更多的筛选条件，而不需要将所有的筛选工作都依赖上层来完成。存储层在扫描索引时，直接应用部分条件来过滤记录，减少向服务层返回的记录数量。

举例说明：

假如有一个表 products，带有索引 (category_id, price)，查询语句如下：

SELECT * FROM products WHERE category_id = 10 AND price < 100;

没有索引下推：

• 存储层通过索引 (category_id, price) 找到所有 category_id = 10 的记录。
• 然后将这些记录返回给服务层。
• 服务层对这些记录再进行过滤，看 price < 100 的记录是否符合条件。
• 在这个过程中，可能会发送大量数据到服务层处理，增大系统开销。

有索引下推：

• 存储层通过索引 (category_id, price)，不仅用于定位 category_id = 10 的记录，还直接在存储层检查 price < 100 条件。
• 只有完全满足条件的记录才会返回给服务层。
• 服务层需要处理的www.devze.com数据量显著减少，查询效率提升。

优势

• 降低IO开销：因为存储层生成的满足条件的记录更少，处理的数据量减少了。
• 更快的查询速度：减少服务层进行二次筛选的压力。
• 无需修改查询语句：索引下推是存储引擎的优化机制，无需用户对 SQL 语句进行额外调整。

使用注意

• 是否能够启用索引下推，取决于存储引擎以及索引类型。
• 在 MySQL 中，只有 InnoDB 存储引擎支持索引下推。
• 索引下推并不总是显著提升查询性能，其实际效果依赖于查询复杂度、数据分布、索引选择等因素。

如何验证索引下推

你可以通过 EXPLAIN 命令检查查询计划，如果查询使用了索引下推，会看到关键字 Using index condition，例如：

EXPLAIN SELECT * FROM products WHERE category_id = 10 AND price < 100;

输出可能包括：

Extra: Using index condition

如果没有 "Using index condition"，则说明没有启用索引下推。

总之，索引下推是数据库引擎的一项重要优化技术，它通过让存储层承担更多的筛选工作，显著提升了查询性能，特别是在使用复合索引的场景中。

小结

索引下推利用了 Index Filter 在存储层完成过滤的能力，减少了回表次数和 Server 层处理数据的压力，从而优化了查询性能。在实际使用索引时，通过合理的覆盖索引设计，可进一步减少回表，提高效率。

到此这篇关于MySQL查询优化的三种处理阶段(Index Key、Index Filter和Table Filter)详解的文章就介绍到这了,更多相关MySQL查询优化内容请搜索编程客栈(www.devze.com)以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程客栈(www.devze.com)！