本文主要是介绍MySQL ICP（索引条件推送）和延迟加载，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

什么是ICP（Index Condition Pushdown）

考虑下面的查询：

SELECT * FROM people
  WHERE zipcode='95054'
  AND lastname LIKE '%etrunia%'
  AND address LIKE '%Main Street%';

假设表中有(zipcode, lastname, address)这个索引，但，对于BTree（或者B+Tree，下文统称BTree），它还是无法使用到lastname、address来进行快速检索，这是底层存储结构的限制，B+Tree只能匹配最左前缀，对于这种非前缀的模糊查询，它无能为力。

所以，在MySQL的早期版本中（5.6之前），它简单地选择只使用索引进行zipcode的等值匹配，存储引擎取出下一个匹配的索引元组，然后利用该元组取出整行数据，然后，服务器层再使用WHERE对这个整行数据进行过滤。这...无形中增加了MySQL服务器层与存储引擎层的数据交换，以及IO的读取次数。

考虑InnoDB工作的情景：

1. InnoDB找到下一个匹配的索引元组，如(95054, 432)，95054是zipcode，432是主键
2. InnoDB二次查找聚集索引，查找到主键是432的行，返回给MySQL服务器层
3. 服务器对返回的整行应用WHERE条件进行匹配，如果匹配，添加到结果集中

对于那些索引本身就能覆盖WHERE条件中所有列的情况，上面的工作步骤无疑是有一些脱裤子放屁的嫌疑了，我们明明可以在索引中就完成WHERE匹配，这样第2步中的查找就没了，第3步中的服务器进行WHERE匹配也没了。

ICP技术在索引能够覆盖WHERE条件的情况下，将条件下放到存储引擎层，在存储引擎层面提前过滤掉那些不需要的行。

相关文档：MySQL5.6 Reference Manual / 8.2.1.5 Index Condition Pushdown Optimization

究其原因就是在使用索引进行查询时，存储引擎没有足够的权力决定一个行（或者说索引项）是否被过滤掉，所有的权力都在MySQL服务器层的手中，它来应用WHERE条件。但服务器层又没有存储引擎了解谁该被过滤掉。下面看一个更扯的问题

当索引不能覆盖查询列时

存储引擎层没有权利过滤行（索引项）的另一个问题：

MySQL5.6版本之前的另一个限制是，虽然索引覆盖了WHERE条件中的所有列，但如果索引不能覆盖SELECT子句中的所有查询列时，尽管WHERE条件为假，该列还是要被存储引擎完整的拿出来，传到服务器层进行WHERE条件过滤。

我们使用Docker来测试，尽量找一个低版本的MySQL

docker pull mysql:5.5
docker run --name mysql55 -e MYSQL_ROOT_PASSWORD=root -d
mysql:5.5
docker exec -it mysql55 /bin/bash
mysql -uroot -proot

执行如下SQL：

CREATE DATABASE test;
USE test;
create table product(
    id int primary key auto_increment,
    name varchar(30) not null,
    actor varchar(30) not null,
    title varchar(30) not null,
    key idx_actor_title(actor, title)
);
insert into product SELECT 1, 'NAME1', 'SEAN CARREY', 'M APOLLO X';
insert into product SELECT 2, 'NAME2', 'JOE', 'OOXXXXASDF';
insert into product SELECT 3, 'NAME3', 'SEAN CARREY', 'OOOXXX';

查看创建好的表

下面来查询：

EXPLAIN SELECT * FROM product
WHERE actor='SEAN CARREY'
AND title='OOOXXX'\G;

结果中的Extra: Using where说明了服务器层将在存储引擎层返回行以后应用WHERE过滤条件。

这是就是因为如上面所说，SELECT * 中返回的列包含索引中无法覆盖到的列，所以该行被取出，传递给服务器层进行WHERE校验，这增加了服务器层到存储引擎层的传输开销。上面的加粗字体是问题的关键，既然MySQL这个憨批分析不出问题所在，选择了一条更加脱裤子放屁的执行路径，那么我们帮它选好该怎么执行好了，这就是延迟关联技术。

延迟关联技术

EXPLAIN SELECT * FROM product
JOIN(
    SELECT id FROM product
    WHERE actor='SEAN CARREY'
    AND title='OOOXXX'
) AS t1 ON (t1.id=product.id)\G;

上面的代码的关键部分在于内查询，(actor, title)这个索引在InnoDB存储引擎中完全可以覆盖(actor, title, id)这三个列，这样，服务器便知道它无需对每一行再次进行WHERE校验，索引完全可以完成工作，这样存储引擎就无需将那些不需要的整行传递给服务器了。然后到了外层，外层再对所有内层取出的ID进行匹配查询，这里也可以用IN。

这个问题究其根本也是由于MySQL不能把查询字段下放到存储引擎层面而带来的，所以只要有了ICP，它也就被解决了。

之所以再次记录ICP相关的笔记，是因为昨天尝试再看《高性能MySQL》时，在延迟关联技术这遇到了一些看着似懂非懂的地方，今早我才发现，原来我一直没把ICP优化的实际目的搞清楚。上面了解了ICP的目的，下面很容易就将延迟关联技术的作用搞懂了。

参考

• 高性能MySQL - 施瓦茨 (Baron Schwartz) / 扎伊采夫 (Peter Zaitsev) / 特卡琴科 (Vadim Tkachenko)
• MySQL5.6 Reference Manual / 8.2.1.5 Index Condition Pushdown Optimization

这篇关于MySQL ICP（索引条件推送）和延迟加载的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！