MySQL5.6之use_index_extensions优化

InnoDB的二级索引(Secondary Index)除了存储索引列key值,还存储着主键值(而不是指向主键的指针)。为什么这样做?因为InnoDB是以聚集索引方式组织数据的存储,即主键值相邻的数据行紧凑的存储在一起(索引组织表)。当数据行移动或者发生页分裂的时候,可以减少大量的二级索引维护工作。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的schema设计如上面所示。二级索引k_d(d)的元组在InnoDB内部实际被扩展成(d,i1,i2),即包含主键值。因此在设计主键的时候,常见的一条设计原则是要求主键字段尽量简单(整型数值,自增),以避免二级索引过大。在MySQL5.6.9之前,优化器在决定是否使用一个索引或者怎样使用一个索引的时候,并不考虑索引中扩展的主键列这一部分。而从MySQL5.6.9开始,优化器开始考虑使用扩展的主键列,这样可以产生更高效的执行计划和更好的性能。
优化器可以把扩展的二级索引用于ref,range,index_merge索引访问、松散索引扫描、连接和排序优化、min()和max()优化。
在5.6中,可以通过优化器开关optimizer_switch(Golbal,Session:Dynamic)来开启或者关闭use_index_extensions优化。

set [global|session] optimizer_switch="use_index_extensions=off|on";

下面通过Handler_read_key状态信息explain执行计划两方面的信息来观察use_index_extensions对mysql执行效率和性能的影响。先往t1中插入一些数据,方便观察status和explain输出的信息。

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');

关闭use_index_extensions,观察explain输出的信息。

mysql> set  optimizer_switch="use_index_extensions=off";
mysql> EXPLAIN SELECT * 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
1 row in set (0.00 sec)

打开use_index_extensions,再观察explain输出结果。

mysql> set  optimizer_switch="use_index_extensions=on";
mysql> EXPLAIN SELECT * 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
1 row in set (0.00 sec)

当use_index_extensions=off的时候,仅使用索引k_d中d列的数据,忽略了扩展的主键列的数据。而use_index_extensions=on时,使用了k_d索引中(i1,i2,d)三列的数据。可以从上面两种情况下的explain输出结果中信息得以验证。
key_len:由4变到8,说明不仅仅使用了d列上的索引,而且使用了扩展的主键i1列的数据。
ref:有const变为”const,const”, 使用了索引的两部分。
rows:从5变为1,只需要检查更少的数据行就可以产生结果集。
Extra:”using index,Using where” 变为”Using index”,通过索引覆盖就完成数据查询,而不需要读取任何的数据行。

另外,从status信息中“Handler_read_%”相关状态值可以观察实际执行过程中索引和数据行的访问统计。flush table关闭已打开的数据表,并清除缓存(表缓存和查询缓存)。flush status把status计数器清零。
关闭use_index_extensions情况下,status的统计信息。

mysql> set  optimizer_switch="use_index_extensions=off";
mysql> flush table t1; flush status;
mysql> SELECT * FROM t1 WHERE i1 = 3 AND d = '2000-01-01';
+----+----+------------+
| i1 | i2 | d          |
+----+----+------------+
|  3 |  3 | 2000-01-01 |
+----+----+------------+
1 row in set (0.00 sec)

mysql> show status like "Handler_read%";
+-----------------------+-------+
| 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     |
+-----------------------+-------+
7 rows in set (0.00 sec)

打开use_index_extensions情况下,status的统计信息。

mysql> set  optimizer_switch="use_index_extensions=on";
mysql> flush table t1; flush status;
mysql> SELECT * FROM t1 WHERE i1 = 3 AND d = '2000-01-01';
+----+----+------------+
| i1 | i2 | d          |
+----+----+------------+
|  3 |  3 | 2000-01-01 |
+----+----+------------+
1 row in set (0.00 sec)

mysql> show status like "Handler_read%";
+-----------------------+-------+
| 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     |
+-----------------------+-------+
7 rows in set (0.00 sec)

Handler_read_next的值从5变为1,索引的访问效率更高了,减少了数据行的读取次数。

参考资料:

Use of Index Extensions

InnoDB Primary Key versus Secondary Index: An Interesting Lesson from EXPLAIN

此条目发表在mysql分类目录,贴了, , 标签。将固定链接加入收藏夹。

MySQL5.6之use_index_extensions优化》有 1 条评论

  1. 尚吾网说:

    坚持更新博客就像坚持写日记一样,不仅是习惯,也是耐力,表示支持

发表评论

电子邮件地址不会被公开。 必填项已用*标注

您可以使用这些HTML标签和属性: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>