详解MySQL InnoDB的索引扩展

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

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)

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

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

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

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

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

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

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: refpossible_keys: PRIMARY,k_d     key: PRIMARY   key_len: 4     ref: const     rows: 5   filtered: 20.00    Extra: Using where1 row in set, 1 warning (0.00 sec)

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: refpossible_keys: PRIMARY,k_d     key: k_d   key_len: 8     ref: const,const     rows: 1   filtered: 100.00    Extra: Using index1 row in set, 1 warning (0.00 sec)

.key_len从4字节变成了8字节，指示键查找使用了列d和i1，不仅仅是d。

.ref的值从const变成了const,const,因为键查找使用两个键的列而不是一个。

.rows:从5减到1，指示InnoDB将会检查更少的行来生成查询结果。

.Extra值从Using where;Using index变成了Using index。这意味着查询记录只需要使用索引而不用查询数据行记录。

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

mysql> flush table t1;Query OK, 0 rows affected (0.01 sec)mysql> flush status;Query OK, 0 rows affected (0.03 sec)

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

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)

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)

mysql> SET optimizer_switch = 'use_index_extensions=off';Query OK, 0 rows affected (0.01 sec)

更多相关文章

随机推荐