索引下推

伴随2025-01-022025-01-02

什么是索引下推？(index condition pushdown)

索引下推是MySQL中的一种查询优化技术，主要用于提高查询效率。它允许MySQL在存储引擎层使用索引来过滤数据，而不是将所有数据从存储引擎传输到服务器层后再进行过滤。这样可以减少不必要的数据传输，从而提高查询性能。

前期准备

创建表

CREATE TABLE orders (
    id INT PRIMARY KEY,             -- 主键索引
    user_id INT,                     -- 用户ID
    status VARCHAR(10),               -- 订单状态
    created_at DATETIME,              -- 创建时间
    amount DECIMAL(10,2),             -- 订单金额
    INDEX idx_orders (user_id, status, created_at)  -- 联合索引
);

查询语句

1 2	SELECT * FROM orders WHERE user_id = 100 AND status = 'shipped' AND created_at > '2024-01-01';

SQL的执行逻辑

MySQL底层一共分为四层

连接层（Client Connectors）：负责客户端与 MySQL 的通信（如 TCP/IP、Socket 等），处理连接、认证、权限校验等。
服务层（Server Layer）：解析 SQL、优化执行计划、调用存储引擎接口。
存储引擎层（Storage Engine Layer）：负责数据的存储和检索，支持插件式引擎（如 InnoDB、MyISAM）。
文件系统层（File System）：存储数据文件（.ibd、.frm）、日志文件（redo log、binlog）等物理文件。

客户端
  │
  ↓ 建立连接、发送 SQL
连接层（认证、权限校验、连接池管理）
  │
  ↓ 传递 SQL
服务层（解析器 → 优化器 → 执行器）
  │
  ↓ 调用存储引擎接口
存储引擎层（InnoDB/MyISAM 读写数据）（回表）
  │
  ↓ 操作磁盘文件
文件系统层（.ibd/.frm 文件、日志文件）
  │
  ↑ 返回数据
存储引擎层 → 服务层 → 连接层 → 客户端

MySQL 的查询执行过程可以分为解析、优化、执行三个阶段。我们主要关注执行阶段，并结合索引下推进行分析。

1. 判断索引的使用

MySQL 需要决定使用哪个索引，这里 WHERE 条件涉及 (user_id, status, created_at) 这三个列，而 idx_orders 这个索引正好覆盖了 user_id、status 和 created_at，所以会使用该索引。

索引匹配规则（最左前缀匹配）：

user_id = 100 可用索引 ✅
status = ‘shipped’ 可用索引 ✅
created_at > ‘2024-01-01’ 也可用索引（但范围查询会影响索引使用）

因此，MySQL 选择 idx_orders 索引，并按以下步骤执行。

2. 具体执行步骤

步骤 1：索引扫描
- MySQL 先利用 B+树索引，找到 user_id = 100 的数据范围。
- 然后在这个范围内，再筛选 status = ‘shipped’ 的记录。
- 如果没有索引下推，此时 MySQL 只会基于 user_id 和 status 进行索引扫描，找到符合条件的行，但 created_at > ‘2024-01-01’ 这个条件会在回表后再筛选。
步骤 2：索引下推（如果启用）
- 如果 MySQL 启用了索引下推（ICP），它会在索引扫描阶段就应用 created_at > ‘2024-01-01’ 的过滤条件
- 只有满足 user_id = 100、status = ‘shipped’ 且 created_at > ‘2024-01-01’ 的行会进入下一步，避免了不必要的回表操作。
步骤 3：回表（如果需要）: 回表指的是如果索引列不完全包含所查询的字段，就需要回表，补全查询字段，然后返回
- 由于 SELECT * 需要查询所有列（而索引 idx_orders 只包含 (user_id, status, created_at)），所以 MySQL 需要通过主键回表查询完整的行数据。
- 但索引下推减少了不必要的回表，只对满足所有索引列筛选条件的行执行回表，从而提高了查询效率。
步骤 4：返回数据
- 读取回表数据后，MySQL 生成最终的结果集并返回给客户端。

`总结*`

索引下推发生的条件

使用了二级索引（非主键索引）
查询条件涉及索引列
查询的字段不完全包含在索引中（可能回表）
- 如果查询字段包含在索引中，是否使用索引下推执行效率都是一样的，因为不需要回表
索引列的筛选条件是范围查询 (>, <, BETWEEN) 或 LIKE ‘xxx%’
数据库版本大于等于5.7

如何判断是否使用索引下推

使用EXPLAIN查询分析计划，查看Extra列，如果包含Using index condition说明使用了索引下推

开关索引下推

-- 查询
SHOW VARIABLES LIKE 'optimizer_switch';

-- 开（全局）
SET GLOBAL optimizer_switch = 'index_condition_pushdown=on';

-- 关（全局）
SET GLOBAL optimizer_switch = 'index_condition_pushdown=off';