Documentation Home
MySQL 8.0 参考手册  / 第8章优化  / 8.3 优化和索引  /  8.3.9 索引扩展的使用

8.3.9 索引扩展的使用

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;

该表定义列的主键(i1, i2)。它还 k_d在列上定义了二级索引(d),但在内部InnoDB扩展了该索引并将其视为列(d, i1, i2)

在确定如何以及是否使用该索引时,优化器会考虑扩展二级索引的主键列。这可以产生更高效的查询执行计划和更好的性能。

优化器可以将扩展二级索引用于 refrangeindex_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');

现在考虑这个查询:

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

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

当优化器不考虑索引扩展时,它只将索引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

When the optimizer takes index extensions into account, it treats k_d as (d, i1, i2). In this case, it can use the leftmost index prefix (d, i1) to produce a better execution plan:

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

In both cases, key indicates that the optimizer uses secondary index k_d but the EXPLAIN output shows these improvements from using the extended index:

  • key_len goes from 4 bytes to 8 bytes, indicating that key lookups use columns d and i1, not just d.

  • The ref value changes from const to const,const because the key lookup uses two key parts, not one.

  • The rows count decreases from 5 to 1, indicating that InnoDB should need to examine fewer rows to produce the result.

  • 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.

Differences in optimizer behavior for use of extended indexes can also be seen with SHOW STATUS:

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

The preceding statements include FLUSH TABLES and FLUSH STATUS to flush the table cache and clear the status counters.

Without index extensions, SHOW STATUS produces this result:

+-----------------------+-------+
| 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,表示更有效地使用索引:

+-----------------------+-------+
| 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标志允许控制优化器在确定如何使用表的二级索引optimizer_switch时是否考虑主键列 。InnoDB默认情况下,use_index_extensions 启用。要检查禁用索引扩展是否会提高性能,请使用以下语句:

SET optimizer_switch = 'use_index_extensions=off';

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