[翻译] MySQL index extentions

Posted on | By admin

本文主要翻译自 MySQL5.6 官方文档

MySQL自从5.6.9开始支持index extention。

index extentions的意思是，将二级索引后面的主键列识别为扩展索引。

考虑到下面一张表：

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;

这张表在列(i1,i2)上创建了主键索引。并且在列(d)上创建了二级索引k_d。但是MySQL内部会把这个索引视为列(d,i1,i2)。

把主键列视为索引列的一部分，可以使得执行计划更高效，并且性能更高。

查询优化器可以在ref，range和index_merge这些访问索引的时候使用扩展的二级索引。也可以在稀疏索引扫描，联合查询和排序优化时用到。另外聚集函数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');

插入后数据，这里以d，i1列进行排序，以便后面理解后面的语句：

mysql> select * from t1 order by d,i1;
+----+----+------------+
| i1 | i2 | d          |
+----+----+------------+
|  1 |  1 | 1998-01-01 |
|  2 |  1 | 1998-01-01 |
|  3 |  1 | 1998-01-01 |
|  4 |  1 | 1998-01-01 |
|  5 |  1 | 1998-01-01 |
|  1 |  2 | 1999-01-01 |
|  2 |  2 | 1999-01-01 |
|  3 |  2 | 1999-01-01 |
|  4 |  2 | 1999-01-01 |
|  5 |  2 | 1999-01-01 |
|  1 |  3 | 2000-01-01 |
|  2 |  3 | 2000-01-01 |
|  3 |  3 | 2000-01-01 |
|  4 |  3 | 2000-01-01 |
|  5 |  3 | 2000-01-01 |
|  1 |  4 | 2001-01-01 |
|  2 |  4 | 2001-01-01 |
|  3 |  4 | 2001-01-01 |
|  4 |  4 | 2001-01-01 |
|  5 |  4 | 2001-01-01 |
|  1 |  5 | 2002-01-01 |
|  2 |  5 | 2002-01-01 |
|  3 |  5 | 2002-01-01 |
|  4 |  5 | 2002-01-01 |
|  5 |  5 | 2002-01-01 |
+----+----+------------+

我们考虑这个查询:

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

这种情况下优化器无法使用主键索引(i1,i2)。因为不是对i2列进行查询。但是优化器可以使用二级索引k_d 进行查询。这个执行计划会受到是否使用扩展的索引的影响。

当优化器没有考虑index extentions时，仅仅将索引k_d 视为(d)。用EXPLAIN获得的结果：

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 extentions时,优化器将k_d视为 (d,i1,i2)。这种情况下它使用索引最左原则生成更好的执行计划。

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。但 EXPLAIN显示出使用index extentions获得的提升。

  • key_len从4字节变成8字节，显示出使用了列d和i1作为索引键，而不仅仅是d。
  • ref 的值从 const 变成 const，const。因为使用了索引的两个列。
  • rows 数目从5变成1.指示出InnoDB会检查更少的行来生成结果。
  • Extra 从 Using where; Using index 变成 Using 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和FLUSH STATUS用来清除表缓存和状态计数器。

在没有使用 index extentions时， 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 extentions时，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_extention标志控制着优化器在使用InnoDB表的二级时是否把主键列视为索引的一部分。默认情况下，use_index_extention是打开的。为了检测关闭使用index extention时能否提高性能，可以这样关闭：

SET optimizer_switch = 'use_index_extentions=off';

优化器对使用index extentions受到通常的限制：在一个索引中最多可以使用多少列（16）,并且最大的key 长度是多大（3072字节）。

Category: 未分类