8.2.1.7 Use of Index Extensions

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;
上面的d列上的索引会扩展成(d, i1, i2),当然这是在引擎的内部实现的,不过这在5.6.9之后的版本才加入的新特性,单从表定义上是不可见的；
表定义
(1) percona-server 5.6.27-76.0-log
mysql> select @@version\G show create table t1\G
*************************** 1. row ***************************
@@version: 5.6.27-76.0-log
1 row in set (0.00 sec)
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `i1` int(11) NOT NULL DEFAULT '0',
  `i2` int(11) NOT NULL DEFAULT '0',
  `d` date DEFAULT NULL,
  PRIMARY KEY (`i1`,`i2`),
  KEY `k_d` (`d`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

(2)mysql server 5.5.50-log
mysql> select @@version\G show create table t1\G
*************************** 1. row ***************************
@@version: 5.5.50-log
1 row in set (0.00 sec)
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `i1` int(11) NOT NULL DEFAULT '0',
  `i2` int(11) NOT NULL DEFAULT '0',
  `d` date DEFAULT NULL,
  PRIMARY KEY (`i1`,`i2`),
  KEY `k_d` (`d`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)


这种优化提高了ref, range, and index_merge,松散索引扫描、join、排序、min()以及max()等索引访问的效率;
下面的例子解释了这种使用使用扩展索引的优势;
我们向表t1插入如下数据,看下在不同版本上执行计划的表现
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');


(1)mysql server 5.5.50
mysql> select @@version;EXPLAIN SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = '2000-01-01'\G
+------------+
| @@version  |
+------------+
| 5.5.50-log |
+------------+
1 row in set (0.00 sec)
*************************** 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
1 row in set (0.00 sec)
在这种情况下的话,由于主键包含了i1，i2列，但是查询不包含i2列，优化器不会走主键索引，只能使用二级索引k_d(d);


(2) percona-server 5.6.27-76
mysql> select @@version;EXPLAIN SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = '2000-01-01'\G
+-----------------+
| @@version       |
+-----------------+
| 5.6.27-76.0-log |
+-----------------+
1 row in set (0.00 sec)
*************************** 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
1 row in set (0.01 sec)
当优化器采用扩展索引后,他会把k_d看成为(d,i1,i2),这种情况下会走最左前缀匹配用到索引前缀(d,i1)


在上面两种情况下,从key值可以看出优化器都采用了二级索引k_d,但是从explain的输出结果可以看出新版本使用了扩展的二级索引;
1.key_len由原来的4字节变成8字节,表明使用了d,i1的索引扫描,不只是d;
2.ref由原来的const变成了const,const表明使用了两个索引扫描，而非一个;
3.rows由5变成了1,表明innodb扫描比较少的行就能取得结果集;
4.The Extra value changes from Using where; Using index to Using index. This means that rows can be read using only the index, without consulting columns in the data row


优化器是否使用扩展索引的差异也可以通过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和FLUSH STATUS会清除table cache并清空status计数器,
没用到索引扩展的show status结果
Variable_name Value
Handler_read_first 0
Handler_read_key 17
Handler_read_last 0
Handler_read_next 5
Handler_read_prev 0
Handler_read_rnd 16
Handler_read_rnd_next 75


用到索引扩展的show status结果,Handler_read_next从5变成了1，
Variable_name Value
Handler_read_first 0
Handler_read_key 16
Handler_read_last 0
Handler_read_next 1
Handler_read_prev 0
Handler_read_rnd 15
Handler_read_rnd_next 72

通过设置optimize_switch系统变量的use_index_extensions标志的值来启用或者禁用二级索引的扩展,默认是启用的,可以设置不同的值来测试那种方式性能更好：