索引下推是一种减少回表查询,提高查询效率的特性。通过将部分带有索引的条件交由存储引擎层进行过滤操作,从而减少了 IO(本该由 Server 层做操作,交由 存储引擎层因此叫做 “下推” ) 。
接来下我们来举个例子:
假设有一个名为 students
的学生表,其中包含学生的姓名、年龄和成绩等信息。我们需要查询年龄在 18 到 25 岁之间,并且成绩在 80 分以上的学生。如果 age
和 score
字段上分别有索引,那么使用索引下推的查询可以如下所示:
SELECT name, age, score
FROM students
WHERE age BETWEEN 18 AND 25
AND score >= 80;
当 MySQL 没有使用索引下推,步骤如下:
1)通过索引 age
找到多条主键 ID 分别是(1、3、5、6)数据。
2)再通过主键 ID 分别进行回表查询出来[有四个主键 ID 需要回表 4 次]。
3)再将数据交由 Server 层,将满足 score
的数据过滤出来得到最终数据。
当 MySQL 使用了索引下推,步骤如下:
1)通过索引 age
找到多条主键 ID 分别是(1、3、5、6)数据。
2)由于 score
字段也拥有索引,存储引擎直接通过 score >= 80
来进行索引过滤,分别找到多条主键 ID 分别是(1、3、6)数据。
3)再通过主键 ID 分别进行回表查询出来得到最终结果[有三个主键 ID 需要回表 3 次]。
需要注意的是:
1)索引下推只能在 InnoDB
和 MyISAM
这两个存储引擎中生效。
2)如果查询中引用了子查询索引下推不能生效。
3)使用了存储函数索引下推也不能生效。
4)如果查询使用了 聚簇索引(主键)查询,索引下推也不会生效,因为其是对于非聚簇索引来进行减少回表次数。