mysql索引下推|极客笔记

mysql索引下推

在 MySQL 中，索引是一种用于加快数据检索速度的数据结构。在查询中使用索引可以帮助数据库系统快速定位到需要的数据即行，从而提高检索的效率。在 MySQL 5.6 版本以后，引入了索引下推（Index Condition Pushdown）的优化特性，能够进一步提升查询性能。

索引下推的概念

索引下推是指在查询语句中进行索引优化，将原本需要在数据行中再次进行过滤的操作推到索引层级执行，从而减少了不必要的数据访问和提高了查询效率。简单来说，就是将过滤条件推到索引层，减少了对不满足条件的数据行的访问，减少了数据I/O。

在 MySQL 的执行计划中，索引下推主要通过索引条件推导（Index Condition Pushdown）和索引表扫描（Index Access Extended）两种方式来实现。当查询语句中有逻辑条件过滤时，索引下推会直接在索引层级进行过滤，只查询满足条件的索引记录。

索引下推的优势

减少数据访问：通过将过滤条件推到索引层级执行，减少了对不满足条件的数据行的访问，减少了数据I/O，提高了查询效率。
减少逻辑I/O：减少了要检查的行数，减少了逻辑 I/O，从而加快查询速度。
优化内存使用：减少了不必要的数据加载到内存中，节省了内存资源。

索引下推的使用场景

索引下推可以在以下场景中发挥作用：

当查询涉及多个条件时，可以更好地利用多列索引来进行过滤。
当查询中包含一些不等式（比如大于、小于等）条件时，可以更好地利用索引来进行范围查询。

示例代码

为了演示索引下推的效果，我们创建一个示例表 users，包含 id、name 和 age 三个字段，分别为主键、姓名和年龄。我们在 age 字段上创建一个索引。

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT
);

CREATE INDEX idx_age ON users(age);

接着插入一些数据：

INSERT INTO users (id, name, age) VALUES (1, 'Alice', 25);
INSERT INTO users (id, name, age) VALUES (2, 'Bob', 30);
INSERT INTO users (id, name, age) VALUES (3, 'Charlie', 35);

然后运行以下查询语句：

EXPLAIN SELECT * FROM users WHERE age > 28 AND name = 'Bob';

这条查询语句会进行索引下推优化，将过滤条件推到索引层级执行，只查询符合条件的记录。执行结果中会看到 type 字段为 index，表示索引扫描。

运行结果

在运行上述查询语句后，可以看到类似以下的执行计划：

id   select_type  table  type  possible_keys  key     key_len  ref    rows  Extra
1    SIMPLE       users  index idx_age        idx_age 4        NULL   2     Using where; Using index

这里可以看到 type 为 index，表示索引扫描，说明数据库对查询进行了索引下推优化，减少了数据访问。