0%

MySQL索引下推(ICP)简单理解及例子

索引下推(Index Condition Pushdown, 简称ICP)是MySQL 5.6 版本的新特性,它能减少回表查询次数,提升检索效率。

MySQL体系结构

要明白索引下推,首先要了解MySQL的体系结构:

上图来自MySQL官方文档
通常把MySQL从上至下分为以下几层:

  • MySQL服务层:包括NoSQL和SQL接口、查询解析器、优化器、缓存和Buffer等组件。
  • 存储引擎层:各种插件式的表格存储引擎,实现事务、索引等各种存储引擎相关的特性。
  • 文件系统层: 读写物理文件。

MySQL服务层负责SQL语法解析、触发器、视图、内置函数、binlog、生成执行计划等,并调用存储引擎层去执行数据的存储和检索。“索引下推”的“下”其实就是指将部分上层(服务层)负责的事情,交给了下层(存储引擎)去处理。

索引下推案例

假设用户表数据和结构如下:

id age birthday name
1 18 01-01 User1
2 19 03-01 User2
3 20 03-01 User3
4 21 03-01 User4
5 22 05-01 User5
6 18 06-01 User6
7 24 01-01 User7

创建一个联合索引(age, birthday),并查询出年龄>20,且生日为03-01的用户:

1
select * from user where age>20 and birthday="03-01"

由于age字段使用了范围查询,根据最左前缀原则,这种情况只能使用age字段进行范围查询,索引中的birthday字段无法使用。使用explain查看执行计划:

1
2
3
4
5
+------+-------------+-------+-------+---------------+--------------+---------+------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+-------+---------------+--------------+---------+------+------+-----------------------+
| 1 | SIMPLE | user | range | age_birthday | age_birthday | 4 | NULL | 3 | Using index condition |
+------+-------------+-------+-------+---------------+--------------+---------+------+------+-----------------------+

可以看到虽然使用了age_birthday索引,但是索引长度key_len只有4,说明只有联合索引只有age字段生效了(因为age字段是int类型,占用4个字节)。最后Extra列的Using index condition表示这个查询使用了索引下推优化。

为在没有索引下推的情况下,执行步骤如下:

  1. 存储引擎根据索引查找出age>20的用户id,分别是:4,5,7
  2. 存储引擎到表格中取出id in (4,5,7)的3条记录,返回给服务层
  3. 服务层过滤掉不符合birthday="03-01"条件的记录,最后返回查询结果为id=4的1行记录。

如果开启了索引下推优化,执行步骤如下:

  1. 存储引擎根据索引查找出age>20的用户id,并使用索引中的birthday字段过滤掉不符合birthday="03-01"条件的记录,最后得到id=4;
  2. 存储引擎到表格中取出id=4的1条记录,返回给服务层;
  3. 服务层过滤掉不符合birthday="03-01"条件的记录,最后返回查询结果为id=4的1行记录。

启用索引下推后,把where条件由MySQL服务层放到了存储引擎层去执行,带来的好处就是存储引擎根据id到表格中读取数据的次数变少了。在上面这个例子中,没有索引下推时需要多回表查询2次。并且回表查询很可能是离散IO,在某些情况下,对数据库性能会有较大提升。