【Mysql】- 索引下推学习分析

newzhong
37 阅读4分钟

1. 原理分析

1. 索引下推 (Index Condition Pushdown, ICP) 的原理

索引下推是 MySQL 5.6 版本引入的一个优化措施，旨在提高查询效率。在没有 ICP 之前，存储引擎在查询时，首先会使用索引检索数据，然后将符合索引条件的数据返回给 MySQL 服务器层，服务器层再根据其他条件对数据进行过滤。

引入 ICP 后，如果某个索引包含了查询中所有的 WHERE 条件，MySQL 服务器层会将部分或全部的过滤条件下推到存储引擎层。这样，存储引擎在查询时可以直接利用这些条件进行过滤，只有完全符合条件的数据才会被返回给服务器层，从而减少了数据传输和服务器层的过滤开销。

2. 索引下推的优点

  • 减少数据传输：因为存储引擎在查询时已经根据条件进行了过滤，所以只有符合条件的数据才会被返回，减少了数据传输量。
  • 降低服务器层负载：存储引擎层处理了部分过滤逻辑，减轻了服务器层的计算负担。
  • 提高查询效率：由于减少了数据传输和服务器层的过滤开销，整体查询效率得到提升。

3. 索引下推的使用场景

  • 查询包含多个条件：当查询语句的 WHERE 条件中包含多个条件，并且这些条件都可以在索引中找到时，ICP 会非常有效。
  • 覆盖索引查询：当查询使用覆盖索引时，即查询的所有列都包含在索引中，ICP 可以显著提高查询效率。

4. 索引下推的限制

  • 只适用于 InnoDB 和 MyISAM 存储引擎：其他存储引擎不支持 ICP。
  • 不适用于全文索引：全文索引不支持 ICP。
  • 不适用于某些类型的查询：例如，子查询和不等于（!=）操作符在某些情况下无法使用 ICP。

5. 如何启用或禁用索引下推

索引下推在 MySQL 5.6 及以上版本中默认是启用的。如果需要禁用，可以通过设置系统变量 optimizer_switch 来实现：

SET optimizer_switch = 'index_condition_pushdown=off';

要重新启用，可以将值设置为 on：

SET optimizer_switch = 'index_condition_pushdown=on';

在实际应用中，索引下推通常能够显著提升查询性能，特别是在处理大量数据和复杂查询时。因此，建议在大多数情况下保持 ICP 启用状态。

2. 举例

2.1 示例场景

假设我们有一个名为 employees 的表，结构如下：

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    department VARCHAR(50),
    salary INT,
    INDEX(department, salary) -- 复合索引
);

现在，我们执行以下查询：

SELECT * FROM employees WHERE department = 'Engineering' AND salary > 5000;

假设我们的表中存在以下数据：

idnamedepartmentsalary
1AliceEngineering4000
2BobEngineering6000
3CharlieHR5500

2.2 没有索引下推 (ICP) 的情况

  1. 存储引擎首先使用复合索引 department, salary 定位到 department = 'Engineering' 的记录。
  2. 存储引擎将所有匹配 department = 'Engineering' 的记录（包括 salary 低于 5000 的记录）返回给 MySQL 服务器层。
  3. MySQL 服务器层进一步过滤出 salary > 5000 的记录。

2.3 使用索引下推 (ICP) 的情况

  1. 存储引擎使用复合索引 department, salary 定位到 department = 'Engineering' 的记录。
  2. 存储引擎在索引内部进一步过滤，只返回 salary > 5000 的记录给 MySQL 服务器层。
  3. MySQL 服务器层直接使用存储引擎返回的结果，无需进一步过滤。

2.4流程图

没有索引下推 (ICP) 的情况:
+------------------+     +------------------+     +------------------+
|                  |     |                  |     |                  |
|  存储引擎层       | --> |  MySQL 服务器层   | --> |  MySQL 服务器层   |
|                  |     |                  |     |                  |
|  1. 使用索引定位  |     |  2. 接收所有记录 |     |  3. 进一步过滤   |
|                  |     |                  |     |                  |
+------------------+     +------------------+     +------------------+
使用索引下推 (ICP) 的情况:
+------------------+     +------------------+
|                  |     |                  |
|  存储引擎层       | --> |  MySQL 服务器层   |
|                  |     |                  |
|  1. 使用索引定位  |     |  2. 直接使用结果  |
|                  |     |                  |
+------------------+     +------------------+

通过这个例子和流程图，我们可以看到索引下推 (ICP) 如何减少存储引擎和 MySQL 服务器层之间的数据传输，从而提高查询效率。

avatar
文章
阅读
粉丝