Percona 5.6.6里坑人的index_condition_pushdown

1. 确认optimizer_switch的设置：

# 查看版本
| version                         | 5.6.6-m9-alpha60.1-log                                            | 
| version_comment                 | Percona Server with XtraDB (GPL), Release alpha60.1, Revision 285 | 

mysql> show global variables like 'optimizer_switch'\G
*************************** 1. row ***************************
Variable_name: optimizer_switch
        Value: 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_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on

# 看表状态
mysql> show table status like 'mytab'\G
*************************** 1. row ***************************
           Name: mytab
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 44150743
 Avg_row_length: 841
    Data_length: 47338626468
Max_data_length: 281474976710655
   Index_length: 9326730240
      Data_free: 10181027160
 Auto_increment: 1
    Create_time: 2012-12-07 16:17:38
    Update_time: 2013-04-27 21:20:39
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 

# 看建表DDL
CREATE TABLE `mytab` (
  `pid` int(10) unsigned NOT NULL,
  `fid` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `tid` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `first` tinyint(1) NOT NULL DEFAULT '0',
  `author` varchar(40) NOT NULL DEFAULT '',
  `authorid` int(10) unsigned NOT NULL DEFAULT '0',
  `subject` varchar(80) NOT NULL DEFAULT '',
  `dateline` int(10) unsigned NOT NULL DEFAULT '0',
  `message` mediumtext NOT NULL,
  `useip` varchar(15) NOT NULL DEFAULT '',
  `invisible` tinyint(1) NOT NULL DEFAULT '0',
  `anonymous` tinyint(1) NOT NULL DEFAULT '0',
  `usesig` tinyint(1) NOT NULL DEFAULT '0',
  `htmlon` tinyint(1) NOT NULL DEFAULT '0',
  `bbcodeoff` tinyint(1) NOT NULL DEFAULT '0',
  `smileyoff` tinyint(1) NOT NULL DEFAULT '0',
  `parseurloff` tinyint(1) NOT NULL DEFAULT '0',
  `attachment` tinyint(1) NOT NULL DEFAULT '0',
  `rate` smallint(6) NOT NULL DEFAULT '0',
  `ratetimes` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `status` int(10) NOT NULL DEFAULT '0',
  `tags` varchar(255) NOT NULL DEFAULT '0',
  `comment` tinyint(1) NOT NULL DEFAULT '0',
  `replycredit` int(10) NOT NULL DEFAULT '0',
  `position` int(8) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`tid`,`position`),
  UNIQUE KEY `pid` (`pid`),
  KEY `fid` (`fid`),
  KEY `authorid` (`authorid`,`invisible`),
  KEY `dateline` (`dateline`),
  KEY `invisible` (`invisible`),
  KEY `displayorder` (`tid`,`invisible`,`dateline`),
  KEY `first` (`tid`,`first`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

# 看执行计划: Using index condition
mysql> explain select * from mytab WHERE tid=3442629 AND position>=558 AND position<572 ORDER BY position DESC;
+----+-------------+------------------+-------+----------------------------+---------+---------+------+------+-----------------------+
| id | select_type | table            | type  | possible_keys              | key     | key_len | ref  | rows | Extra                 |
+----+-------------+------------------+-------+----------------------------+---------+---------+------+------+-----------------------+
|  1 | SIMPLE      | mytab            | range | PRIMARY,displayorder,first | PRIMARY | 7       | NULL |   35 | Using index condition | 
+----+-------------+------------------+-------+----------------------------+---------+---------+------+------+-----------------------+

# 执行倒序查询，非常慢，需要 8.52秒
mysql> select sql_no_cache * from mytab WHERE tid=3442629 AND position>=558 AND position<572 ORDER BY position DESC;
…
14 rows in set (8.52 sec)

# 如果不是倒序排序，则执行效率很快：
mysql> explain select * from mytab WHERE tid=3442629 AND position>=558 AND position<572 ORDER BY position;
+----+-------------+------------------+-------+----------------------------+---------+---------+------+------+-----------------------+
| id | select_type | table            | type  | possible_keys              | key     | key_len | ref  | rows | Extra                 |
+----+-------------+------------------+-------+----------------------------+---------+---------+------+------+-----------------------+
|  1 | SIMPLE      | mytab | range | PRIMARY,displayorder,first | PRIMARY | 7       | NULL |   35 | Using index condition | 
+----+-------------+------------------+-------+----------------------------+---------+---------+------+------+-----------------------+

# 只需要 0.10秒
mysql> select sql_no_cache * from mytab WHERE tid=3442629 AND position>=558 AND position<572 ORDER BY position;          
……
14 rows in set (0.10 sec)

2. 关闭index_condition_pushdown：

mysql> set optimizer_switch="index_condition_pushdown=off";
Query OK, 0 rows affected (0.00 sec)

查看新的执行计划：

mysql> explain select sql_no_cache * from mytab WHERE tid=3442629 AND position >= 558 AND position < 572 ORDER BY position DESC;
+----+-------------+------------------+-------+----------------------------+---------+---------+------+------+-------------+
| id | select_type | table            | type  | possible_keys              | key     | key_len | ref  | rows | Extra       |
+----+-------------+------------------+-------+----------------------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | mytab            | range | PRIMARY,displayorder,first | PRIMARY | 7       | NULL |   35 | Using where | 
+----+-------------+------------------+-------+----------------------------+---------+---------+------+------+-------------+

查看实际执行时间：

mysql> select sql_no_cache * from mytab WHERE tid=3442629 AND position>=558 AND position<572 ORDER BY position DESC;
……
14 rows in set (0.00 sec)
# 只需要 0.00秒

3. 如果把SQL优化成JOIN，即使不关闭 index_condition_pushdown 也可以达到优化后的效果：

mysql> explain SELECT a.* FROM mytab a join (select tid,position from mytab WHERE tid=3442629 AND 
                 position>=558 AND position<572 ORDER BY position DESC) b on a.tid=b.tid and a.position=b.position;
+----+-------------+------------------+--------+----------------------------+---------+---------+------------------+------+--------------------------+
| id | select_type | table            | type   | possible_keys              | key     | key_len | ref              | rows | Extra                    |
+----+-------------+------------------+--------+----------------------------+---------+---------+------------------+------+--------------------------+
|  1 | PRIMARY     |        | ALL    | NULL                       | NULL    | NULL    | NULL             |   27 | NULL                     | 
|  1 | PRIMARY     | a                | eq_ref | PRIMARY,displayorder,first | PRIMARY | 7       | b.tid,b.position |    1 | NULL                     | 
|  2 | DERIVED     | mytab | range  | PRIMARY,displayorder,first | PRIMARY | 7       | NULL             |   35 | Using where; Using index | 
+----+-------------+------------------+--------+----------------------------+---------+---------+------------------+------+--------------------------+

mysql> SELECT SQL_NO_CACHE a.* FROM mytab a join (select tid,position from mytab WHERE tid=3442629 AND 
                 position>=558 AND position<572 ORDER BY position DESC) b on a.tid=b.tid and a.position=b.position;
……
14 rows in set (0.00 sec)
# 只需要 0.00秒

问题分析：
猜测在MySQL 5.6中，启用index_condition_pushdown之后，会把所有符合过滤条件的数据先全部取出，然后再进行排序。
上面的例子中，由于 tid, position 已是联合主键，因此默认就是正序排序，所以如果不是倒序排序的话，效率还是很高的。

关闭index_condition_pushdown后，优化器认为可以从联合主键索引中取出符合条件的索引记录，并且利用主键完成倒序排序，
最后再根据主键取出相应的行记录，所以效率会更高，这个可以从改造成JOIN后的执行计划得到佐证。

没有源码研究功底，分析的也不一定正确，期待专业人士给个解释吧，呵呵。