详解MySQL InnoDB的索引扩展

吾爱主题阅读：140 2024-04-05 14:00:01 评论：0

索引扩展,InnoDB通过将主键列附加到每个辅助索引中来自动扩展该索引。创建如下表结构:

1 2 3 4 5 6 7 8 9 mysql> 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; Query OK, 0 rows affected (0.14 sec)

表t1在列(i1,i2)上定义了主键。同时也在列(d)上定义了一个辅助索引，但InnoDB扩展了这个索引并且将它视为(d,i1,i2)来处理。

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

优化器可以使用扩展的二级索引来进行ref、range和index_merge索引访问，进行松散索引扫描，进行连接和排序优化，以及进行MIN()/MAX()优化。

下面的示例将显示优化器是否使用扩展辅助索引来影响执行计划向表t1插入以下数据:

1 2 3 4 5 6 7 8 mysql> 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' ); Query OK, 25 rows affected (0.05 sec) Records: 25 Duplicates: 0 Warnings: 0

假设执行下面的查询:

1 2	`SET` `optimizer_switch =` `'use_index_extensions=off'` `;` `explain` `select` `count` `(*)` `from` `t1` `where` `i1=3` `and` `d=` `'2000-01-01'` `;`

在这种情况下，优化器不能使用主键，因为主键包含列(i1、i2)，并且查询没有引用i2。相反，优化器可以使用列(d)上的辅助索引k_d，执行计划取决于是否使用扩展索引。

当优化器不考虑索引扩展时，它将索引k_d仅视为(d)

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 mysql> SET optimizer_switch = 'use_index_extensions=off' ; Query OK, 0 rows affected (0.00 sec) mysql> explain select count (*) from t1 where i1=3 and d= '2000-01-01' \G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table : t1 partitions: NULL type: ref possible_keys: PRIMARY ,k_d key : PRIMARY key_len: 4 ref: const rows : 5 filtered: 20.00 Extra: Using where 1 row in set , 1 warning (0.00 sec)

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 mysql> SET optimizer_switch = 'use_index_extensions=on' ; Query OK, 0 rows affected (0.00 sec) mysql> explain select count (*) from t1 where i1=3 and d= '2000-01-01' \G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table : t1 partitions: NULL type: ref possible_keys: PRIMARY ,k_d key : k_d key_len: 8 ref: const,const rows : 1 filtered: 100.00 Extra: Using index 1 row in set , 1 warning (0.00 sec)

在这两种情况下，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。这意味着查询记录只需要使用索引而不用查询数据行记录。

可以使用show status来查看优化器在使用与不使用扩展索引时的差异:

1 2 3 4 5 mysql> flush table t1; Query OK, 0 rows affected (0.01 sec) mysql> flush status; Query OK, 0 rows affected (0.03 sec)

上面的flush table和flush status语句用来清除表的缓存和清除状数据统计数据。

不使用索引扩展时show status产生的结果如下:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 mysql> SET optimizer_switch = 'use_index_extensions=off' ; Query OK, 0 rows affected (0.01 sec) mysql> select count (*) from t1 where i1=3 and d= '2000-01-01' ; + ----------+ | count (*) | + ----------+ | 1 | + ----------+ 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)

使用索引扩展时，show status产生的结果如下，其中handler_read_next的值从5减到1，指示使用这个索引更有效率:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 mysql> flush table t1; Query OK, 0 rows affected (0.01 sec) mysql> flush status -> ; Query OK, 0 rows affected (0.02 sec) mysql> SET optimizer_switch = 'use_index_extensions=on' ; Query OK, 0 rows affected (0.00 sec) mysql> select count (*) from t1 where i1=3 and d= '2000-01-01' ; + ----------+ | count (*) | + ----------+ | 1 | + ----------+ 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.01 sec)