对index condition pushdown很感兴趣，并且跟踪代码让自己受益良多，因此就来跟一下相关代码。

看的是mysql5.6.7-rc官方社区版。

先说说我对研究MySQL源码的看法：

每个使用MySQL数据库的人都应该看代码吗？不是的，那意味着MySQL数据库的使用门槛太高，几乎不可用；但另一方面，如果看MySQL代码的人多了，意味着有更多的人对MySQL数据库的了解更加深入。能够进一步推动MySQL数据库广泛而恰当地使用，为使用者、相关从业者创造更多的赢利机会和就业机会。

下面进入正题。

1. 单一字段索引

mysql> show create table pushdown\G

*************************** 1. row ***************************

Table: pushdown

Create Table: CREATE TABLE `pushdown` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(100) DEFAULT NULL,

`info` varchar(200) DEFAULT NULL,

`other` int(11) DEFAULT NULL,

PRIMARY KEY (`id`),

KEY `name` (`name`)

) ENGINE=InnoDB AUTO_INCREMENT=718829 DEFAULT CHARSET=latin1

1 row in set (0.08 sec)

mysql> select count(*) from pushdown;

mysql> explain select * from pushdown where name=’name6011′;

mysql> explain select * from pushdown where name=’name6011′ and info like ‘%1′;

Using where的意思是从存储引擎取得数据以后，还要再和条件进行匹配。本例就是与like ‘%1’进行匹配。

通过secondary index name查到name=’name6011’的主键；

再通过主键的值在cluster index中查出*（全部数据）；

在用like ‘%1’把这些数据库不满足条件的过滤掉；

如果我们去比较5.1版本中的using where，发现using where会在我们以为不该出现的时候也出现。即我们以为通过索引已经找到了满足所有条件的数据，但还是能看到using where。这个问题似乎在5.6版本里解决了。

2. 两个字段联合索引

mysql> alter table pushdown drop key name;

Query OK, 0 rows affected (0.17 sec)

Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table pushdown add key name_info(name,info);

Query OK, 0 rows affected (1 min 1.80 sec)

Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table pushdown\G

*************************** 1. row ***************************

Table: pushdown

Create Table: CREATE TABLE `pushdown` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(100) DEFAULT NULL,

`info` varchar(200) DEFAULT NULL,

`other` int(11) DEFAULT NULL,

PRIMARY KEY (`id`),

KEY `name_info` (`name`,`info`)

) ENGINE=InnoDB AUTO_INCREMENT=718829 DEFAULT CHARSET=latin1

1 row in set (0.00 sec)

explain select * from pushdown where name=’name6011′;

只以name为条件查询，执行计划没有变化

explain select * from pushdown where name=’name6011′ and info like ‘%1′;

用两个条件查，比只在name上建索引时少了using where。这说明在利用secondary index name_info查到满足name=’name6011’的索引KEY时，又使用了like ‘%1’，也在索引name_info中查找，把不满足条件的记录过滤掉了。然后再利用索引name_info中得到的主键去cluster index查全部数据。

这是依据执行计划和以前看过5.1版本的代码来推测的。

下面跟代码找真相。

一路跟下去，找到比较where条件的位置。先把call stack记下来备查：

>  mysqld.exe!Arg_comparator::compare_string()  行1374  C++

mysqld.exe!Arg_comparator::compare()  行84 + 0x1c 字节  C++

mysqld.exe!Item_func_eq::val_int()  行2198 + 0xe 字节   C++

mysqld.exe!Item::val_bool()  行203 + 0xf 字节 C++

mysqld.exe!Item_cond_and::val_int()  行5084 + 0xf 字节  C++

mysqld.exe!innobase_index_cond(void * file=0x0ef9b678)  行16388 + 0x2e 字节 C++

mysqld.exe!row_search_idx_cond_check(unsigned char * mysql_rec=0x0ef9d0a0, row_prebuilt_t * prebuilt=0x0efb11e0, const unsigned char * rec=0x06d3eab7, const unsigned long * offsets=0x11c9cf74)  行3604 + 0xf 字节 C++

mysqld.exe!row_search_for_mysql(unsigned char * buf=0x0ef9d0a0, unsigned long mode=2, row_prebuilt_t * prebuilt=0x0efb11e0, unsigned long match_mode=1, unsigned long direction=0)  行4678 + 0×18 字节   C++

mysqld.exe!ha_innobase::index_read(unsigned char * buf=0x0ef9d0a0, const unsigned char * key_ptr=0x0efb2560, unsigned int key_len=103, ha_rkey_function find_flag=HA_READ_KEY_EXACT)  行7438 + 0x1d 字节   C++

mysqld.exe!handler::index_read_map(unsigned char * buf=0x0ef9d0a0, const unsigned char * key=0x0efb2560, unsigned long keypart_map=1, ha_rkey_function find_flag=HA_READ_KEY_EXACT)  行2174 + 0×22 字节   C++

mysqld.exe!handler::ha_index_read_map(unsigned char * buf=0x0ef9d0a0, const unsigned char * key=0x0efb2560, unsigned long keypart_map=1, ha_rkey_function find_flag=HA_READ_KEY_EXACT)  行2669 + 0×70 字节  C++

mysqld.exe!join_read_always_key(st_join_table * tab=0x0efb2354)  行2185 + 0×32 字节  C++

mysqld.exe!sub_select(JOIN * join=0x0ef96d00, st_join_table * join_tab=0x0efb2354, bool end_of_records=false)  行1239 + 0xe 字节   C++

mysqld.exe!do_select(JOIN * join=0x0ef96d00)  行932 + 0×17 字节   C++

mysqld.exe!JOIN::exec()  行191 + 0×9 字节  C++

mysqld.exe!mysql_execute_select(THD * thd=0x057c1090, st_select_lex * select_lex=0x057c2bd8, bool free_join=true)  行1086   C++

mysqld.exe!mysql_select(THD * thd=0x057c1090, TABLE_LIST * tables=0x0ef96260, unsigned int wild_num=1, List<Item> & fields={…}, Item * conds=0x0ef96ba0, SQL_I_List<st_order> * order=0x057c2cd0, SQL_I_List<st_order> * group=0x057c2c6c, Item * having=0×00000000, unsigned __int64 select_options=2147748608, select_result * result=0x0ef96ce0, st_select_lex_unit * unit=0x057c2760, st_select_lex * select_lex=0x057c2bd8)  行1204 + 0×23 字节 C++

mysqld.exe!handle_select(THD * thd=0x057c1090, select_result * result=0x0ef96ce0, unsigned long setup_tables_done_option=0)  行110 + 0×78 字节 C++

mysqld.exe!execute_sqlcom_select(THD * thd=0x057c1090, TABLE_LIST * all_tables=0x0ef96260)  行4990 + 0xf 字节  C++

mysqld.exe!mysql_execute_command(THD * thd=0x057c1090)  行2554 + 0xd 字节  C++

mysqld.exe!mysql_parse(THD * thd=0x057c1090, char * rawbuf=0x0ef96080, unsigned int length=63, Parser_state * parser_state=0x11c9f840)  行6094 + 0×9 字节  C++

mysqld.exe!dispatch_command(enum_server_command command=COM_QUERY, THD * thd=0x057c1090, char * packet=0x0ef8dd91, unsigned int packet_length=63)  行1314 + 0×28 字节 C++

mysqld.exe!do_command(THD * thd=0x057c1090)  行1038 + 0x1b 字节   C++

mysqld.exe!do_handle_one_connection(THD * thd_arg=0x057c1090)  行969 + 0×9 字节 C++

mysqld.exe!handle_one_connection(void * arg=0x057c1090)  行885 + 0×9 字节  C++

mysqld.exe!pfs_spawn_thread(void * arg=0x0edb4ba0)  行1853 + 0×9 字节 C++

mysqld.exe!pthread_start(void * p=0x0edbbd10)  行61 + 0×9 字节 C

mysqld.exe!_callthreadstartex()  行348 + 0xf 字节 C

mysqld.exe!_threadstartex(void * ptd=0x0ef91dd8)  行331 C

kernel32.dll!75dded6c()

接下来看当前比较的是什么：

回到下面的循环中，再做后续的比较：

longlong Item_cond_and::val_int()

{

DBUG_ASSERT(fixed == 1);

List_iterator_fast<Item> li(list);

Item *item;

null_value= 0;

while ((item=li++))

{

if (!item->val_bool())

{

if (abort_on_null || !(null_value= item->null_value))

return 0;         // return FALSE

}

}

return null_value ? 0 : 1;

}

再一路跟下去，进入like比较：

longlong Item_func_like::val_int()

{

DBUG_ASSERT(fixed == 1);

String* res = args[0]->val_str(&cmp.value1);

if (args[0]->null_value)

{

null_value=1;

return 0;

}

String* res2 = args[1]->val_str(&cmp.value2);

if (args[1]->null_value)

{

null_value=1;

return 0;

}

null_value=0;

if (canDoTurboBM)

return turboBM_matches(res->ptr(), res->length()) ? 1 : 0;

return my_wildcmp(cmp.cmp_collation.collation,

res->ptr(),res->ptr()+res->length(),

res2->ptr(),res2->ptr()+res2->length(),

escape,wild_one,wild_many) ? 0 : 1;

}

可以看到，一个是字段的值，一个是like后面的值。这一步info的值是info21885，不满足like ‘%1’，此记录就被过滤掉了。

当找到匹配的记录时，则还要从cluster index中读取数据，看代码

/* Check if the record matches the index condition. */

switch (row_search_idx_cond_check(buf, prebuilt, rec, offsets)) {

case ICP_NO_MATCH:

if (did_semi_consistent_read) {

row_unlock_for_mysql(prebuilt, TRUE);

}

goto next_rec;

case ICP_OUT_OF_RANGE:

err = DB_RECORD_NOT_FOUND;

goto idx_cond_failed;

case ICP_MATCH:

break;

}

/* Get the clustered index record if needed, if we did not do the

search using the clustered index. */

if (index != clust_index && prebuilt->need_to_access_clustered) {

requires_clust_rec:

ut_ad(index != clust_index);

/* We use a ‘goto’ to the preceding label if a consistent

read of a secondary index record requires us to look up old

versions of the associated clustered index record. */

ut_ad(rec_offs_validate(rec, index, offsets));

/* It was a non-clustered index and we must fetch also the

clustered index record */

mtr_has_extra_clust_latch = TRUE;

/* The following call returns ‘offsets’ associated with

‘clust_rec’. Note that ‘clust_rec’ can be an old version

built for a consistent read. */

err = row_sel_get_clust_rec_for_mysql(prebuilt, index, rec,

thr, &clust_rec,

&offsets, &heap, &mtr);

这个函数的名字很明确，get cluster record for mysql

函数说明：

/*********************************************************************//**

Retrieves the clustered index record corresponding to a record in a

non-clustered index. Does the necessary locking. Used in the MySQL

interface.

@return DB_SUCCESS, DB_SUCCESS_LOCKED_REC, or error code */

当ICP过滤的条件越多，需要从cluster index中读取的记录就越少。特别是当用到select *，特别是有blob、text字段时，从cluster index读取数据的代价就更为显著。

记得我们对like ‘%abc%’的优化吗？特别是表里有大字段的时候。可能会这样来优化：

select * from a,(select id from a where c1 like ‘%abc%’) b where a.id=b.id;

如果实际的SQL还需要一个等于的条件，比如c2=’ccc’。那么就可以这样优化：

建立索引c2_c1(c2,c1)；

select * from a where c2=’ccc’ and c1 like ‘%abc%’;

SQL并不用改写，而ICP会使用c2_c1过滤掉所有不满足条件的记录，只有满足条件的才会去读cluster_index，这和以前我们优化成derived table的思路是一样的。

请大家尝试，并把结果反馈给我，感激不尽。注意这是5.6才有的特性。

3. 回过头来再看只在name一字段上建立索引的情况

mysql> explain select * from pushdown where name=’name6011′ and info like ‘%1′;

这时候有using where。Using where是什么呢？看一下代码：

static enum_nested_loop_state

evaluate_join_record(JOIN *join, JOIN_TAB *join_tab)

{

bool not_used_in_distinct=join_tab->not_used_in_distinct;

ha_rows found_records=join->found_records;

Item *condition= join_tab->condition();

bool found= TRUE;

DBUG_ENTER(“evaluate_join_record”);

DBUG_PRINT(“enter”,

(“join: %p join_tab index: %d table: %s cond: %p”,

join, static_cast<int>(join_tab - join_tab->join->join_tab),

join_tab->table->alias, condition));

/*就是这里了，当condition有值时就是using where了。

上面的例子，如果没有ICP支持，like ‘%1’就会在这里出现。

同样，当把索引改为只在name上时，like ‘%1’就在这里出现了。

*/

if (condition)

{

found= test(condition->val_int());//这里在检测where条件是否匹配了

if (join->thd->killed)

{

join->thd->send_kill_message();

DBUG_RETURN(NESTED_LOOP_KILLED);

}

/* check for errors evaluating the condition */

if (join->thd->is_error())

DBUG_RETURN(NESTED_LOOP_ERROR);

}

两个比较的项，前者是info字段，后者是’%1’。

str_value里看到like条件’%1’

4.  在组内交流了一下，同学们给了很大帮助，提了非常有启发的问题：

两个字段模糊查询，能否用ICP？

除了模糊查询以后，函数能否用上ICP？

3个字段联合索引，第1和第3个字段有where条件，能否用上ICP？

mysql> select * from pushdown where name=’name658′ and info like ‘%6′;

+——-+———+———-+——-+

| id    | name    | info      | other |

+——-+———+———-+——-+

| 12266 | name658 | info12266 |  NULL |

|  3056 | name658 | info3056  |  NULL |

+——-+———+———-+——-+

2 rows in set (0.01 sec)

mysql> explain select * from pushdown where name=’name658′ and length(info)=8;

+—-+————-+———-+——+—————+———-+———+——-+——+———————-+

| id | select_type | table    | type | possible_keys | key       | key_len | ref   | rows | Extra                 |

+—-+————-+———-+——+—————+———-+———+——-+——+———————-+

|  1 | SIMPLE      | pushdown | ref  | name_info     | name_info | 53      | const |    4 | Using index condition |

+—-+————-+———-+——+—————+———-+———+——-+——+———————-+

1 row in set (0.02 sec)

mysql> show create table pushdown\G

*************************** 1. row ***************************

Table: pushdown

Create Table: CREATE TABLE `pushdown` (