搜索引擎优化知识完全手册(IndexConditionPushdown称为索引下推优化技术有哪些注意事项？)

　　搜索引擎优化知识完全手册(IndexConditionPushdown称为索引下推优化技术有哪些注意事项？)

　　网上查了一下，很多相关的文章都把Index Condition Pushdown称为索引下推优化。我觉得把指标条件下推比较合适，因为这个优化技术的关键操作和指标有关。条件由 MySQL 服务器传递给存储引擎，从而减少 IO 的数量。MySQL服务器到存储引擎宕机，通过索引列相关的查询条件，这样更容易理解索引条件下的优化。

　　适用条件需要扫描整个表。例如：范围、引用、eq_ref、ref_or_null。适用于 InnoDB 引擎和 MyISAM 引擎的查询。（5.6 版本不适用于分区表查询，5.7 版本可用于分区表查询）。InnDB 引擎只适用于二级索引，因为InnDB 的聚集索引会将整行数据读入InnDB 的缓冲区。这样一来，下推索引的主要目的就是为了减少IO的数量，没有意义。因为数据已经在内存中了，不需要再读取了。不能下推引用子查询的条件。调用存储过程的条件无法下推，存储引擎也无法调用位于MySQL服务器中的存储过程。不能按下触发条件。工作过程

　　既然是优化，我们就需要了解我们优化了什么，以及它原本是如何工作的，所以分两部分来描述工作过程。

　　下一行是在下推优化查询过程中获取的，不使用索引。首先读取索引信息，然后根据索引读取整行数据。然后使用where条件判断当前数据是否满足条件，是否满足返回数据。使用索引条件下推优化的查询过程，获取下一行的索引信息。检查索引中存储的列信息是否符合索引条件，如果符合，则读取整行数据，如果不符合，则跳过读取下一行。使用剩余的判断条件判断该行数据是否满足要求，满足则返回数据。解释分析

　　使用explan进行分析时，如果使用索引条件下推，Extra会显示Using index condition。不是Using index是因为不能确定在索引条件下推送的数据是符合要求的数据，需要通过其他查询条件来判断。

　　图1：未使用ICP技术（工艺用数字符号标注，如①②③等）

　　

　　流程说明：

　　①：MySQL Server 发出读取数据的命令。这是在执行器中执行下面的代码段，通过函数指针和句柄接口调用存储引擎的索引读取或全表读取。这里发生的是索引读取。

　　if (in_first_read){

in_first_read= false;

//设定合适的读取函数，如设定索引读函数/全表扫描函数

error= (*qep_tab->read_first_record)(qep_tab);

}else

error= info->read_record(info);复制代码

　　②、③：进入存储引擎，读取索引树，在索引树上搜索，从表记录中读取满足条件的（搜索后满足红色的）（步骤④，一般为IO），从存储引擎返回 ⑤识别结果。

　　在这里，不仅是在索引行中读取索引（通常在内存中，这是快速的。步骤③），还有步骤④，通常带有IO。

　　⑥：将存储引擎找到的多个元组返回给MySQL Server，MySQL Server在⑦中得到更多的元组。

　　⑦--⑧：从⑦到⑧根据WHERE子句的条件进行过滤，得到满足条件的元组。注意，在MySQL Server层，获取的元组越多，然后过滤，最终结果是满足条件的元组数量越少。

　　图2：采用ICP技术（工艺用数字符号标注，如①②③等）工艺说明：

　　①：MySQL Server发出读取数据的命令，过程同图1。

　　②、③：进入存储引擎，读取索引树，在索引树上搜索，读出满足下推条件的表记录（搜索后满足红色的）（步骤④，一般为IO），从存储引擎返回⑤标识的结果。

　　这里不仅是在索引行中读取的索引（通常在内存中，速度较快。步骤③），还有本阶段根据下推条件的判断。如果不满足条件，则不读取该表。对于索引树中的数据，直接在索引树上判断下一个索引项，直到条件满足才执行步骤④。这样，与没有ICP的方法相比，IO量减少了。

　　⑥：将存储引擎中找到的几个元组返回给MySQL Server，MySQL Server得到⑦中的几个元组。所以，对比没有ICP的图，返回给MySQL Server层的是少量满足条件的元组。另外，图中组件的层级关系不再赘述。

　　例子

　　假设有一个 people 表，其中收录 name、address、first_name 字段

　　索引是 (name, address, first_name)

　　然后我们执行以下查询

　　SELECT * FROM person WHERE `name` = "1" AND `address` LIKE "%222" and 复制代码

　　如果不使用索引条件下推优化，MySQL只能根据索引查询所有name=1的行，然后依次比较是否满足所有条件。

　　使用索引条件下推优化技术时，可以通过索引中存储的数据来判断当前索引对应的数据是否满足条件，只有满足条件的数据才会对整行进行查询数据的。在查看执行计划时，发现额外的列中有Using index condition信息，说明使用了索引下推。

　　

　　配置

　　默认情况下启用索引下推优化。可以通过以下脚本控制开关

　　SET optimizer_switch = 'index_condition_pushdown=off';

SET optimizer_switch = 'index_condition_pushdown=on';复制代码

　　思考

　　索引下推优化技术实际上是充分利用了索引中的数据，在查询整行数据之前尽量过滤掉无效数据。

　　因为需要存储引擎来判断索引中的数据和条件，所以这项技术是基于存储引擎的，只能使用特定的引擎。而判断条件需要是存储引擎层面可以执行的操作，比如调用存储过程的条件，因为存储引擎没有调用存储过程的能力。