mysql global index_MySQL 5.6里坑人的index_condition_pushdown

1. 确认optimizer_switch的设置：

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后的执行计划得到佐证。

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