Mysql 索引特性ICP以及explain extra类型分析

一、Index Condition Pushdown(ICP)是MySQL 5.6中新特性，是一种在存储引擎层使用索引过滤数据的一种 优化方式。ICP可以减少存储引擎访问基表的次数以及MySQL服务器访问存储引擎的次数。

在不使用ICP索引扫描的过程：
storage层：
只将满足index key条件的索引记录对应的整行记录取出，返回给server层
server 层：
对返回的数据，使用后面的where条件过滤，直至返回最后一行。

使用ICP扫描的过程：
storage层：
首先将index key条件满足的索引记录区间确定，然后在索引上使用index filter进行过滤
将满足的index filter条件的索引记录才去回表取出整行记录返回server层
不满足index filter条件的索引记录丢弃，不回表、也不会返回server层
server 层：
对返回的数据，使用table filter条件做最后的过滤。

测试：

CREATE TABLE `account` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `balance` decimal(10,0) NOT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `Index_ba` (`balance`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

INSERT INTO `account` (`id`, `balance`, `name`) VALUES ('1', '1200', 'tom');
INSERT INTO `account` (`id`, `balance`, `name`) VALUES ('2', '2000', 'q');
INSERT INTO `account` (`id`, `balance`, `name`) VALUES ('3', '2000', 'qq');

explain select * from Account where balance =2000;

explain select * from Account where balance >2000;

explain select id,balance from Account where balance >2000;

ICP的使用条件：
1.只能用于二级索引(secondary index)。
2.查询需要访问表的整行数据。
3.ICP可以用于MyISAM和InnnoDB存储引擎，不支持分区表

如上即为mysql的索引ICP的使用，没有ICP的情况下，查询 balance >2000时，会先返回数据再使用where过滤，使用了ICP直接在索引上过滤。

二、extra类型汇总:
【Using where】
SQL使用了where条件过滤数据。

【Using index】
SQL所需要返回的所有列数据均在一棵索引树上，而无需访问实际的行记录

【Using index condition】ICP
确实命中了索引，但不是所有的列数据都在索引树上，还需要访问实际的行记录。

【Using filesort】
得到所需结果集，需要对所有记录进行文件排序；
这类SQL语句性能极差，需要进行优化。
典型的，在一个没有建立索引的列上进行了order by，就会触发filesort，常见的优化方案是，在order by的列上添加索引，避免每次查询都全量排序。

【Using temporary】
需要建立临时表(temporary table)来暂存中间结果。
这类SQL语句性能较低，往往也需要进行优化。
典型的，group by和order by同时存在，且作用于不同的字段时，就会建立临时表，以便计算出最终的结果集。

【Using join buffer (Block Nested Loop)】
需要进行嵌套循环计算；
这类SQL语句性能往往也较低，需要进行优化。
典型的，两个关联表join，关联字段均未建立索引，就会出现这种情况。常见的优化方案是，在关联字段上添加索引，避免每次嵌套循环计算。