linux mysql5.6 优化_MySQL5.6之use_index_extensions优化

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 代表进行了很多表扫描，查询性能低下。

参考资料：