Mysql Index extends优化

 

 CREATE   TABLE   t1 (
  i1   INT   NOT   NULL   DEFAULT   0  ,
  i2   INT   NOT   NULL   DEFAULT   0  ,
  d DATE   DEFAULT   NULL  ,
    PRIMARY   KEY   (i1, i2),
    INDEX   k_d (d)
) ENGINE   =  InnoDB;

 

   该表定义（t1,t2）为联合主键，也定义个二级索引k_id 在列（d）上，但是内部innodb会扩展它，变成列index（d,i1,i2）;

 

   版本5.6.9之前，优化器不会这么优化，但在5.6.9，开始支持，可以得到更好的性能和更有效的执行计划；

   优化器可以用扩展的二级索引来进行ref,range,index_merge等类型index access,松散的index sacns,join连接和排序优化，和min()/max()优化；

   数据：

 INSERT   INTO  t1  VALUES  
(  1 ,  1 ,  ‘  1998-01-01  ‘ ), ( 1 ,  2 ,  ‘  1999-01-01  ‘  ),
(  1 ,  3 ,  ‘  2000-01-01  ‘ ), ( 1 ,  4 ,  ‘  2001-01-01  ‘  ),
(  1 ,  5 ,  ‘  2002-01-01  ‘ ), ( 2 ,  1 ,  ‘  1998-01-01  ‘  ),
(  2 ,  2 ,  ‘  1999-01-01  ‘ ), ( 2 ,  3 ,  ‘  2000-01-01  ‘  ),
(  2 ,  4 ,  ‘  2001-01-01  ‘ ), ( 2 ,  5 ,  ‘  2002-01-01  ‘  ),
(  3 ,  1 ,  ‘  1998-01-01  ‘ ), ( 3 ,  2 ,  ‘  1999-01-01  ‘  ),
(  3 ,  3 ,  ‘  2000-01-01  ‘ ), ( 3 ,  4 ,  ‘  2001-01-01  ‘  ),
(  3 ,  5 ,  ‘  2002-01-01  ‘ ), ( 4 ,  1 ,  ‘  1998-01-01  ‘  ),
(  4 ,  2 ,  ‘  1999-01-01  ‘ ), ( 4 ,  3 ,  ‘  2000-01-01  ‘  ),
(  4 ,  4 ,  ‘  2001-01-01  ‘ ), ( 4 ,  5 ,  ‘  2002-01-01  ‘  ),
(  5 ,  1 ,  ‘  1998-01-01  ‘ ), ( 5 ,  2 ,  ‘  1999-01-01  ‘  ),
(  5 ,  3 ,  ‘  2000-01-01  ‘ ), ( 5 ,  4 ,  ‘  2001-01-01  ‘  ),
(  5 ,  5 ,  ‘  2002-01-01  ‘ );

查询sql:

EXPLAIN  SELECT   COUNT ( * )  FROM  t1  WHERE  i1  =   3   AND  d  =   ‘  2000-01-01  ‘ ;

    这种情况下，优化器不会使用主键，因为主键有（t1,t2）组成，但是该查询中没有引用i2;优化器会选择二级索引 k_d(d) ，执行计划依赖与是否扩展index被使用；

    当优化器没有使用index extensions时，他对待 k_d 仅仅为（d）.

mysql >  EXPLAIN  SELECT   COUNT ( * )  FROM  t1  WHERE  i1  =   3   AND  d  =   ‘  2000-01-01  ‘  \G
  ***************************   1 . row  ***************************  
           id:   1  
  select_type: SIMPLE
          table  : t1
         type: ref
possible_keys:   PRIMARY  ,k_d
            key  : k_d
      key_len:   4  
          ref: const
         rows:   5  
        Extra: Using   where ; Using  index 

当优化器把index extensions考虑在内的话，对待k_d （d,i1,i2)， 这种情况下,他可以使用最左前缀（d,i1）开得到一个更好的执行计划；

mysql >  EXPLAIN  SELECT   COUNT ( * )  FROM  t1  WHERE  i1  =   3   AND  d  =   ‘  2000-01-01  ‘  \G
  ***************************   1 . row  ***************************  
           id:   1  
  select_type: SIMPLE
          table  : t1
         type: ref
possible_keys:   PRIMARY  ,k_d
            key  : k_d
      key_len:   8  
          ref: const,const
         rows:   1  
        Extra: Using   index 

两种情况下，key列显示优化器都会选择用二级索引k-d，但是 ：

    1：key_len列从4bytes 变成了8 bytes,说明了key 是查找的 列 d 和 i1,而不是仅仅d;

    2: rows列计数从5减少到1，说明 innodb检测更少的行来得到结构；

    3：Extra列从 using where;using index 变成了using index,意味着结果只使用了index,没有access数据行；

 

优化器使用扩展的Index行为不同也可以通过show status指令来观看:

FLUSH  TABLE   t1;
FLUSH STATUS;
  SELECT   COUNT ( * )  FROM  t1  WHERE  i1  =   3   AND  d  =   ‘  2000-01-01  ‘  ;
SHOW STATUS   LIKE   ‘  handler_read%  ‘ 

flush table :清除Table cache;

flush status:清除状态计数;

没有index extendsions,show status:

 +  --  ---------------------+-------+ 
 |  Variable_name          |  Value  | 
 +  --  ---------------------+-------+ 
 |  Handler_read_first     |   0       | 
 |  Handler_read_key       |   1       | 
 |  Handler_read_last      |   0       | 
 |  Handler_read_next      |   5       | 
 |  Handler_read_prev      |   0       | 
 |  Handler_read_rnd       |   0       | 
 |  Handler_read_rnd_next  |   0       | 
 +  --  ---------------------+-------+ 

有index extensions,show status: handler_read_next 从5变成1

 +  --  ---------------------+-------+ 
 |  Variable_name          |  Value  | 
 +  --  ---------------------+-------+ 
 |  Handler_read_first     |   0       | 
 |  Handler_read_key       |   1       | 
 |  Handler_read_last      |   0       | 
 |  Handler_read_next      |   1       | 
 |  Handler_read_prev      |   0       | 
 |  Handler_read_rnd       |   0       | 
 |  Handler_read_rnd_next  |   0       | 
 +  --  ---------------------+-------+ 

optimizer_switch系统变量的use_index_extensions标志可以控制是否优化器进行二级索引扩展，默认，是打开的，

 SET  optimizer_switch  =   ‘  use_index_extensions=off  ‘ ;

Mysql Index extends优化

标签：

查看更多关于Mysql Index extends优化的详细内容...