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的用户：

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

+------+-------------+-------+-------+---------------+--------------+---------+------+------+-----------------------+| 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>20的用户id，分别是：4,5,7
存储引擎到表格中取出id in (4,5,7)的3条记录，返回给服务层
服务层过滤掉不符合birthday="03-01"条件的记录，最后返回查询结果为id=4的1行记录。

如果开启了索引下推优化，执行步骤如下：

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

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

前言

MySQL体系结构

索引下推案例

总结

更多相关文章

随机推荐