MYSQL 索引下推

MYSQL 索引下推（Index Condition）

背景介绍

官方文档链接

官方定义

Index Condition Pushdown (ICP) is an optimization for the case where MySQL retrieves rows from a table using an index. Without ICP, the storage engine traverses the index to locate rows in the base table and returns them to the MySQL server which evaluates the WHERE condition for the rows. With ICP enabled, and if parts of the WHERE condition can be evaluated by using only columns from the index, the MySQL server pushes this part of the WHERE condition down to the storage engine. The storage engine then evaluates the pushed index condition by using the index entry and only if this is satisfied is the row read from the table. ICP can reduce the number of times the storage engine must access the base table and the number of times the MySQL server must access the storage engine.

什么是索引下推

抓住第一句话的关键词，表述出来就是MYSQL使用索引进行回表的优化

索引下推有什么用

既然是一种优化肯定是为了提升Mysql的性能，具体的原理就是在Mysql存储引擎就使用索引中的列进行过滤，减少回表的次数

举个例子

一个基本的用户表，有id，姓名、性别，（name，sex）作为联合索引

假设我们需要查找姓刘的男性，会有一条这样的SQL

select * from user where name like '王%' and sex = '1'

没有索引下推的时候

​

当有了索引下推