大约在两年前，我写了一篇关于mysql索引的文章。最近有同学在文章的评论中对文章的内容提出质疑，质疑主要集中在联合索引的使用方式上。在那篇文章中，我说明联合索引是将各个索引字段做字符串连接后作为key，使用时将整体做前缀匹配。 而这名同学在这个页面
大约在两年前，我写了一篇关于mysql索引的文章。最近有同学在文章的评论中对文章的内容提出质疑，质疑主要集中在联合索引的使用方式上。在那篇文章中，我说明联合索引是将各个索引字段做字符串连接后作为key，使用时将整体做前缀匹配。
而这名同学在这个页面找到了如下一句话：index condition pushdown is usually useful with multi-column indexes: the first component(s) is what index access is done for, the subsequent have columns that we read and check conditions on。从而认为联合索引的使用方式与文中不符。
实际上，这个页面所讲述的是在mariadb 5.3.3（mysql是在5.6）开始引入的一种叫做index condition pushdown（以下简称icp）的查询优化方式。由于本身不是一个层面的东西，前文中说的是index access，而这里是query optimization，所以并不构成对前文正确性的影响。在写前文时，mysql还没有icp，所以文中没有涉及相关内容，但考虑到新版本的mariadb或mysql中icp的启用确实影响了一些查询行为的外在表现。所以决定写这篇文章详细讲述一下icp的原理以及对索引使用方式的优化。
实验先从一个简单的实验开始直观认识icp的作用。
安装数据库首先需要安装一个支持icp的mariadb或mysql数据库。我使用的是mariadb 5.5.34，如果是使用mysql则需要5.6版本以上。
mac环境下可以通过brew安装：
brew install mairadb
其它环境下的安装请参考mariadb官网关于下载安装的文档。
导入示例数据与前文一样，我们使用employees sample database，作为示例数据库。完整示例数据库的下载地址为：https://launchpad.net/test-db/employees-db-1/1.0.6/+download/employees_db-full-1.0.6.tar.bz2。
将下载的压缩包解压后，会看到一系列的文件，其中employees.sql就是导入数据的命令文件。执行
mysql -h[host] -u[user] -p 就可以完成建库、建表和load数据等一系列操作。此时数据库中会多一个叫做employees的数据库。库中的表如下：
mariadb [employees]> show tables;+---------------------+| tables_in_employees |+---------------------+| departments || dept_emp || dept_manager || employees || salaries || titles |+---------------------+6 rows in set (0.00 sec)
我们将使用employees表做实验。
建立联合索引employees表包含雇员的基本信息，表结构如下：
mariadb [employees]> desc employees.employees;+------------+---------------+------+-----+---------+-------+| field | type | null | key | default | extra |+------------+---------------+------+-----+---------+-------+| emp_no | int(11) | no | pri | null | || birth_date | date | no | | null | || first_name | varchar(14) | no | | null | || last_name | varchar(16) | no | | null | || gender | enum('m','f') | no | | null | || hire_date | date | no | | null | |+------------+---------------+------+-----+---------+-------+6 rows in set (0.01 sec)
这个表默认只有一个主索引，因为icp只能作用于二级索引，所以我们建立一个二级索引：
alter table employees.employees add index first_name_last_name (first_name, last_name);
这样就建立了一个first_name和last_name的联合索引。
查询为了明确看到查询性能，我们启用profiling并关闭query cache：
set profiling = 1;set query_cache_type = 0;set global query_cache_size = 0;
然后我们看下面这个查询：
mariadb [employees]> select * from employees where first_name='mary' and last_name like '%man';+--------+------------+------------+-----------+--------+------------+| emp_no | birth_date | first_name | last_name | gender | hire_date |+--------+------------+------------+-----------+--------+------------+| 254642 | 1959-01-17 | mary | botman | m | 1989-11-24 || 471495 | 1960-09-24 | mary | dymetman | m | 1988-06-09 || 211941 | 1962-08-11 | mary | hofman | m | 1993-12-30 || 217707 | 1962-09-05 | mary | lichtman | f | 1987-11-20 || 486361 | 1957-10-15 | mary | oberman | m | 1988-09-06 || 457469 | 1959-07-15 | mary | weedman | m | 1996-11-21 |+--------+------------+------------+-----------+--------+------------+
根据mysql索引的前缀匹配原则，两者对索引的使用是一致的，即只有first_name采用索引，last_name由于使用了模糊前缀，没法使用索引进行匹配。我将查询联系执行三次，结果如下：
+----------+------------+---------------------------------------------------------------------------+| query_id | duration | query |+----------+------------+---------------------------------------------------------------------------+| 38 | 0.00084400 | select * from employees where first_name='mary' and last_name like '%man' || 39 | 0.00071800 | select * from employees where first_name='mary' and last_name like '%man' || 40 | 0.00089600 | select * from employees where first_name='mary' and last_name like '%man' |+----------+------------+---------------------------------------------------------------------------+
然后我们关闭icp：
set optimizer_switch='index_condition_pushdown=off';
在运行三次相同的查询，结果如下：
+----------+------------+---------------------------------------------------------------------------+| query_id | duration | query |+----------+------------+---------------------------------------------------------------------------+| 42 | 0.00264400 | select * from employees where first_name='mary' and last_name like '%man' || 43 | 0.01418900 | select * from employees where first_name='mary' and last_name like '%man' || 44 | 0.00234200 | select * from employees where first_name='mary' and last_name like '%man' |+----------+------------+---------------------------------------------------------------------------+
有意思的事情发生了，关闭icp后，同样的查询，耗时是之前的三倍以上。下面我们用explain看看两者有什么区别：
mariadb [employees]> explain select * from employees where first_name='mary' and last_name like '%man';+------+-------------+-----------+------+----------------------+----------------------+---------+-------+------+-----------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | extra |+------+-------------+-----------+------+----------------------+----------------------+---------+-------+------+-----------------------+| 1 | simple | employees | ref | first_name_last_name | first_name_last_name | 44 | const | 224 | using index condition |+------+-------------+-----------+------+----------------------+----------------------+---------+-------+------+-----------------------+1 row in set (0.00 sec)
mariadb [employees]> explain select * from employees where first_name='mary' and last_name like '%man';+------+-------------+-----------+------+----------------------+----------------------+---------+-------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | extra |+------+-------------+-----------+------+----------------------+----------------------+---------+-------+------+-------------+| 1 | simple | employees | ref | first_name_last_name | first_name_last_name | 44 | const | 224 | using where |+------+-------------+-----------+------+----------------------+----------------------+---------+-------+------+-------------+1 row in set (0.00 sec)
前者是开启icp，后者是关闭icp。可以看到区别在于extra，开启icp时，用的是using index condition；关闭icp时，是using where。
其中using index condition就是icp提高查询性能的关键。下一节说明icp提高查询性能的原理。
原理icp的原理简单说来就是将可以利用索引筛选的where条件在存储引擎一侧进行筛选，而不是将所有index access的结果取出放在server端进行where筛选。
以上面的查询为例，在没有icp时，首先通过索引前缀从存储引擎中读出224条first_name为mary的记录，然后在server段用where筛选last_name的like条件；而启用icp后，由于last_name的like筛选可以通过索引字段进行，那么存储引擎内部通过索引与where条件的对比来筛选掉不符合where条件的记录，这个过程不需要读出整条记录，同时只返回给server筛选后的6条记录，因此提高了查询性能。
下面通过图两种查询的原理详细解释。
关闭icp
在不支持icp的系统下，索引仅仅作为data access使用。
开启icp
在icp优化开启时，在存储引擎端首先用索引过滤可以过滤的where条件，然后再用索引做data access，被index condition过滤掉的数据不必读取，也不会返回server端。
注意事项有几个关于icp的事情要注意：
icp只能用于二级索引，不能用于主索引。也不是全部where条件都可以用icp筛选，如果某where条件的字段不在索引中，当然还是要读取整条记录做筛选，在这种情况下，仍然要到server端做where筛选。icp的加速效果取决于在存储引擎内通过icp筛选掉的数据的比例。参考[1] https://mariadb.com/kb/en/index-condition-pushdown/
[2] http://dev.mysql.com/doc/refman/5.6/en/index-condition-pushdown-optimization.html
原文地址：mysql索引与index condition pushdown, 感谢原作者分享。