Mysql Index extends优化

  Innodb通过自动把主键列添加到每个二级索引来扩展它们:

 

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;

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。