8.2 优化SQL语句

8.2.1 优化 SELECT 语句

8.2.1.6 索引条件下推优化

索引条件下推（ICP）是指MySQL使用索引从表中检索数据行的一种优化。如果没有ICP，存储引擎将遍历索引以定位基表中的行，并将这些行返回给MySQL服务器，然后该服务器依照WHERE条件对这些行进行过滤。启用ICP后，如果部分WHERE条件仅仅通过使用索引列就可以进行过滤，那么，MySQL服务器会将这部分WHERE条件向下推送到存储引擎。然后，存储引擎通过使用索引条目来评估下推的索引条件，只有在满足该条件时，才从表中读取数据行。ICP可以减少存储引擎必须访问基表的次数和MySQL服务器必须访问存储引擎的次数。

索引条件下推适用性取决于以下条件：

• 当需要访问表的所有行时，ICP可用于range、ref、eq_ref和ref_or_null访问方法。

• ICP可以用于InnoDB和MyISAM表，包括分区InnoDB和MyISAM表。

• 对于InnoDB表，ICP只能用于辅助索引。ICP的目标是减少所有行读取的次数，从而减少I/O操作。对于InnoDB集群索引，完整的记录已经被读入InnoDB缓冲区，在这种情况下使用ICP并不会减少I/O。

• 在虚拟生成的列上创建辅助索引不支持ICP。InnoDB支持对虚拟生成的列进行辅助索引。

• 引用子查询的条件不能下推。

• 引用存储函数的条件不能下推。存储引擎无法调用存储函数。

• 触发条件不能下推。（有关触发条件的信息，请参见8.2.2.3节，“使用EXISTS策略优化子查询”。）

要了解此优化的工作原理，请首先考虑在不使用索引条件下推时索引扫描是如何进行的：

1. 获取下一行，首先通过读取索引元组，然后使用索引元组来定位和读取表中的整行数据。

2. 然后使用此表上的WHERE条件部分对读取的数据进行测试，然后根据测试结果接受或拒绝行。

使用索引条件下推，扫描过程如下：

1. 得到下一行的索引元组，而不是表中的整行数据。

2. 使用此表上的WHERE条件部分对读取的数据进行测试，并且该测试只是使用了索引列进行检查。如果不满足条件，则转到下一行的索引元组。

3. 如果满足该条件，则使用索引元组来定位并读取表中的整行数据。

4. 测试应用于此表的WHERE条件部分，然后根据测试结果接受或拒绝行。

在使用索引条件下推时，EXPLAIN输出中在Extra列中显示使用了索引条件（Using index condition）。如果没有使用索引条件下推，必须读取表中的整行，它不显示使用索引（Using index）。

假设有一个表（people）保存了有关人员及其地址的信息，并且有一个定义为INDEX(zipcode, lastname, firstname)(邮编、姓氏、名)的索引。如果我们知道一个人的邮政编码（zipcode），但不确定他的姓氏，我们可以这样搜索：

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

MySQL可以使用索引扫描来定位 zipcode='95054'的基表（people）的行位置 。而第二个条件（lastname LIKE '%etrunia%'）不能用于限制必须扫描的行数，所以如果没有索引条件下推，这个查询必须检索整个表的所有行来查找符合zipcode='95054'的所有人。

但是，如果使用索引条件下推，MySQL在读取表中完整行之前先检查 lastname LIKE '%etrunia%'部分。这样可以避免读取匹配了zipcode条件的索引元组而不能匹配lastname条件对应的整行。

索引条件下推默认是启用的。它可以通过设置optimizer_switch系统变量index_condition_pushdown标志来控制：

SET optimizer_switch = 'index_condition_pushdown=off';
SET optimizer_switch = 'index_condition_pushdown=on';