从MySQL5.6开始，在索引方面有了一些改进，比如Indexconditionpushdown(ICP)，严格来说是优化器层面的改进。简单理解的话，优化器会把对索引条件的处理尽可能从服务器层下推到存储引擎层。举个例子，有一张表，其复合索引idx_cols包含(c1,c2,...,cn)n列，如果有where条件对c1进行范围扫描，则剩下的c2,...,cn是n-1个索引，不能用来抽取和过滤数据，ICP就是为了优化这个事情。下面在MySQL5.6的环境下做一个简单的测试。我们创建一个带有主键和复合索引的表emp来说明。createtableemp(empnosmallint(5)unsignednotnullauto_increment,enamevarchar(30)notnull,deptnosmallint(5)unsignednotnull,jobvarchar(30)notnull,primarykey(empno),keyidx_emp_info(deptno,ename))engine=InnoDBcharset=utf8;当然我也随机插入了几条数据，有道理。insertintoempvalues(1,'张三',1,'CEO'),(2,'lisi',2,'CFO'),(3,'wangwu',3,'CTO'),(4,'jeanron100',3,'工程师');ICP控制在数据库参数中有一个优化器参数optimizer_switch进行统一管理，我想这是MySQL优化器离我们最近的时候了。您可以使用以下方法查看。显示像“optimizer_switch”这样的变量；当然，5.6之前的版本是看不到索引条件下推这几个字的。5.6版本看到的结果如下：#mysqladminvar|grepoptimizer_switchoptimizer_switch|index_merge=on，index_merge_union=on，index_merge_sort_union=on，index_merge_intersection=on，engine_condition_pushdown=on，index_condition_pushdown=on，mrr=on，mrr_oncost_basedblock_nested_loop=on，batched_key_access=off，materialization=on，semijoin=on，loosescan=on，firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on下面我们用两个语句来对比说明，通过执行计划来对比。setoptimizer_switch="index_condition_pushdown=off">explainselect*frommempwheredeptnobetween1and100andename='jeanron100';+----+------------+------+------+----------------+------+--------+------+------+------------+|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|+----+--------------+--------+------+----------------+------+--------+-----+-----+-------------+|1|SIMPLE|emp|ALL|idx_emp_info|NULL|NULL|NULL|4|Usingwhere|+----+------------+--------+-----+--------------+------+----------+------+------+------------+如果启用，查看是否启用了ICP。setoptimizer_switch="index_condition_pushdown=on";>explainselect*frommempwheredeptnobetween10and3000andename='jeanron100';+----+------------+--------+-------+----------------+------------+--------+------+------+--------------------+|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|+----+------------+--------+------+--------------+------------+--------+-----+------+----------------------+|1|SIMPLE|emp|range|idx_emp_info|idx_emp_info|94|NULL|1|Usingindexcondition|+----+-------------+--------+--------+------------+-------------+------------+------+------+-------------------+1rowinset(0.00sec)如果仔细观察，会发现这两个语句还是有区别的，就是范围扫描的范围不同。如果还是用原来的语句，结果还是有限的。>explainselect*fromempwheredeptnobetween1and300andename='jeanron100';+----+------------+--------+------+----------------+------+--------+------+------+------------+|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|+----+------------+-------+-------+----------------+-----+--------+-----+------+------------+|1|SIMPLE|emp|ALL|idx_emp_info|NULL|NULL|NULL|4|Usingwhere|+----+-----------+--------+------+------------+-----+--------+------+------+------------+1rowinset(0.00sec)这个地方是值得深思。
