MySQL Index Condition Pushdown(ICP)优化

empolyees

mysql >   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.09  sec)

mysql >   alter   table  employees  add   index   idx_fn_ln (first_name,last_name );//添加联合索引
Query OK,   0  rows affected ( 3.98   sec)
Records:   0   Duplicates:  0   Warnings:  0 

Index Condition Pushdown打开的情况下

mysql >  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   |  idx_fn_ln      |  idx_fn_ln  |   16        |  const  |    224   |   Using index condition   | 
 +  --  --+-------------+-----------+------+---------------+-----------+---------+-------+------+-----------------------+ 
 1  row  in   set  ( 0.00  sec)

关闭的情况下：

mysql >   set  optimizer_switch  =   ‘  index_condition_pushdown=off  ‘  ; //关闭index condition pushdown
Query OK,   0  rows affected ( 0.02   sec)

mysql  >  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   |  idx_fn_ln      |  idx_fn_ln  |   16        |  const  |    224   |   Using where   | 
 +  --  --+-------------+-----------+------+---------------+-----------+---------+-------+------+-------------+ 
 1  row  in   set  ( 0.00  sec)

看where语句中包含上述联合索引，并且包含一个非索引列：

mysql >   set  optimizer_switch =  ‘  index_condition_pushdown=on  ‘  ;
Query OK,   0  rows affected ( 0.00   sec)
  
mysql  >  explain  select   *   from  employees  where  first_name  =  ‘  Mary  ‘   and  last_name  =  ‘  %man  ‘   and  gender  =  ‘  M  ‘  ;
  +  --  --+-------------+-----------+------+---------------+-----------+---------+-------------+------+------------------------------------+ 
 |  id  |  select_type  |   table       |  type  |  possible_keys  |   key         |  key_len  |  ref          |  rows  |  Extra                               | 
 +  --  --+-------------+-----------+------+---------------+-----------+---------+-------------+------+------------------------------------+ 
 |    1   |  SIMPLE       |  employees  |  ref   |  idx_fn_ln      |  idx_fn_ln  |   34        |  const,const  |      1   |   Using index condition; Using where   | 
 +  --  --+-------------+-----------+------+---------------+-----------+---------+-------------+------+------------------------------------+ 
 1  row  in   set  ( 0.01  sec)

  同样有using index condition,不过index过滤后，Server还要根据gender列来判断一下 storage engine返回的值；

  

    ICP只能用于二级索引，不能用于主索引。

   也不是全部where条件都可以用ICP筛选，如果某where条件的字段不在索引中，当然还是要读取整条记录做筛选，在这种情况下，仍然要到server端做where筛选。

   ICP的加速效果取决于在存储引擎内通过ICP筛选掉的数据的比例。

   本文 使用employees数据库表和数据在这里************下载，该库功能数据齐全，employees_db-full-1.0.6.tar.bz2；

MySQL Index Condition Pushdown(ICP)优化

标签：

查看更多关于MySQL Index Condition Pushdown(ICP)优化的详细内容...