MySQL5.6之use_index_extensions优化

180次阅读

共计 4528 个字符，预计需要花费 12 分钟才能阅读完成。

InnoDB 的二级索引（Secondary Index）除了存储索引列 key 值，还存储着主键值(而不是指向主键的指针)。为什么这样做？因为 InnoDB 是以聚集索引方式组织数据的存储，即主键值相邻的数据行紧凑的存储在一起（索引组织表）。当数据行移动或者发生页分裂的时候，可以减少大量的二级索引维护工作。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;

数据表 t1 的 schema 设计如上面所示。二级索引 k_d（d）的元组在 InnoDB 内部实际被扩展成（d,i1,i2），即包含主键值。因此在设计主键的时候，常见的一条设计原则是要求主键字段尽量简单（整型数值，自增），以避免二级索引过大。在 MySQL5.6.9 之前，优化器在决定是否使用一个索引或者怎样使用一个索引的时候，并不考虑索引中扩展的主键列这一部分。而从 MySQL5.6.9 开始，优化器开始考虑使用扩展的主键列，这样可以产生更高效的执行计划和更好的性能。
优化器可以把扩展的二级索引用于 ref，range，index_merge 索引访问、松散索引扫描、连接和排序优化、min()和 max()优化。
在 5.6 中，可以通过优化器开关 optimizer_switch(Golbal,Session:Dynamic) 来开启或者关闭 use_index_extensions 优化。
set[global|session] optimizer_switch="use_index_extensions=off|on";

下面通过 Handler_read_key 状态信息和explain 执行计划两方面的信息来观察 use_index_extensions 对 mysql 执行效率和性能的影响。先往 t1 中插入一些数据，方便观察 status 和 explain 输出的信息。
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’);

关闭 use_index_extensions, 观察 explain 输出的信息。

set optimizer_switch=”use_index_extensions=off”;
mysql> explain select * from t1 where i1=3 and d=’2000-01-01′;
+—-+————-+——-+——+—————+——+———+——-+——+————————–+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——+—————+——+———+——-+——+————————–+
| 1 | SIMPLE | t1 | ref | PRIMARY,k_d | k_d | 4 | const | 5 | Using where; Using index |
+—-+————-+——-+——+—————+——+———+——-+——+————————–+

打开 use_index_extensions, 再观察 explain 输出结果。

set optimizer_switch=”use_index_extensions=on”;
mysql> explain select * from t1 where i1=3 and d=’2000-01-01′;
+—-+————-+——-+——+—————+——+———+————-+——+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——+—————+——+———+————-+——+————-+
| 1 | SIMPLE | t1 | ref | PRIMARY,k_d | k_d | 8 | const,const | 1 | Using index |
+—-+————-+——-+——+—————+——+———+————-+——+————-+

当 use_index_extensions=off 的时候，仅使用索引 k_d 中 d 列的数据，忽略了扩展的主键列的数据。而 use_index_extensions=on 时，使用了 k_d 索引中（i1,i2,d）三列的数据。可以从上面两种情况下的 explain 输出结果中信息得以验证。
key_len：由 4 变到 8，说明不仅仅使用了 d 列上的索引，而且使用了扩展的主键 i1 列的数据。
ref：有 const 变为”const,const”, 使用了索引的两部分。
rows: 从 5 变为 1，只需要检查更少的数据行就可以产生结果集。
Extra：”using index,Using where”变为”Using index”，通过索引覆盖就完成数据查询，而不需要读取任何的数据行。

另外，从 status 信息中“Handler_read_%”相关状态值可以观察实际执行过程中索引和数据行的访问统计。flush table 关闭已打开的数据表，并清除缓存（表缓存和查询缓存）。flush status 把 status 计数器清零。
关闭 use_index_extensions 情况下，status 的统计信息。

set optimizer_switch=”use_index_extensions=off”;
flush table t1; flush status;
SELECT * FROM t1 WHERE i1 = 3 AND d = ‘2000-01-01’;
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 |
+———————–+——-+

打开 use_index_extensions 情况下，status 的统计信息。

set optimizer_switch=”use_index_extensions=on”;
flush table t1; flush status;
SELECT * FROM t1 WHERE i1 = 3 AND d = ‘2000-01-01’;
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 |
+———————–+——-+

Handler_read_next 的值从 5 变为 1，索引的访问效率更高了，减少了数据行的读取次数。

Handler_read_first 代表读取索引头的次数，如果这个值很高，说明全索引扫描很多。
Handler_read_key 代表一个索引被使用的次数，如果我们新增加一个索引，可以查看 Handler_read_key 是否有增加，如果有增加，说明 sql 用到索引。
Handler_read_next 代表读取索引的下列，一般发生 range scan。
Handler_read_prev 代表读取索引的上列，一般发生在 ORDER BY … DESC。
Handler_read_rnd 代表在固定位置读取行，如果这个值很高，说明对大量结果集进行了排序、进行了全表扫描、关联查询没有用到合适的 KEY。
Handler_read_rnd_next 代表进行了很多表扫描，查询性能低下。

参考资料：