MySQL :: MySQL 8.4 Reference Manual :: 10.3.10 Use of Index Extensions

version 8.4

8.0 current
5.7

8.0 Japanese

MySQL 8.4 Reference Manual / ... / Use of Index Extensions

10.3.10 索引扩展的使用

InnoDB 自动将每个次要索引扩展到添加主键列。考虑以下表定义：

Press CTRL+C to copy
 
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)列上。它还定义了一个次要索引k_d在(d)列上，但实际上InnoDB将扩展这个索引并将其视为(d, i1, i2)列。

优化器在确定是否使用该索引时考虑了扩展的次要索引的主键列。这可能会导致更高效的查询执行计划和更好的性能。

优化器可以将扩展的次要索引用于ref、range、index_merge索引访问、Loose Index Scan访问、join和排序优化，以及MIN()/MAX()优化。

以下示例展示了优化器是否使用扩展的次要索引对执行计划的影响。假设t1包含以下行：

Press CTRL+C to copy
 
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');

现在考虑以下查询：

Press CTRL+C to copy
 
EXPLAIN SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = '2000-01-01'

执行计划取决于是否使用扩展索引。

优化器不考虑索引扩展时，它将索引k_d视为只包含(d)。对查询的EXPLAIN结果如下：

Press CTRL+C to copy
 
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

优化器考虑索引扩展时，它将k_d视为(d, i1, i2)。在这种情况下，它可以使用左most索引前缀(d, i1)来生成更好的执行计划：

Press CTRL+C to copy
 
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输出显示了使用扩展索引时的改进：

key_len从4个字节增加到8个字节，表示键查找使用列d和i1，而不是只使用d。
ref值从const变为const, const，因为键查找使用两个键部分，而不是一个。
rows计数从5减少到1，表示InnoDB需要检查更少的行来生成结果。
Extra值从Using where; Using index变为Using index。这意味着可以使用索引读取行，而不需要consulting数据行的列。

使用扩展索引的优化器行为差异也可以通过SHOW STATUS看到：

Press CTRL+C to copy
 
FLUSH TABLE t1;
FLUSH STATUS;
SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = '2000-01-01';
SHOW STATUS LIKE 'handler_read%'

前面的语句包括FLUSH TABLES和FLUSH STATUS以刷新表缓存并清除状态计数器。

没有索引扩展时，SHOW STATUS产生以下结果：

Press CTRL+C to copy
 
+-----------------------+-------+
| 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     |
+-----------------------+-------+

有索引扩展时，SHOW STATUS产生以下结果。Handler_read_next值从5降到1，表明了更高效的索引使用：

Press CTRL+C to copy
 
+-----------------------+-------+
| 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     |
+-----------------------+-------+

use_index_extensions优化器开关变量的标志允许控制是否在确定如何使用InnoDB表的次要索引时考虑主键列。默认情况下，use_index_extensions启用。要检查是否禁用索引扩展可以改善性能，请使用以下语句：

Press CTRL+C to copy
 
SET optimizer_switch = 'use_index_extensions=off';

优化器对索引扩展的使用受通常的索引key部分数量限制（16）和最大键长度限制（3072字节）。

PREV HOME UP NEXT