ITPub博客

首页 > 数据库 > MySQL > MySQL5.6之use_index_extensions优化

MySQL5.6之use_index_extensions优化

MySQL 作者:wzgchen 时间:2015-07-28 11:10:27 0 删除 编辑


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

参考资料:

Use of Index Extensions

InnoDB Primary Key versus Secondary Index: An Interesting Lesson from EXPLAIN

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/91975/viewspace-1753953/,如需转载,请注明出处,否则将追究法律责任。

下一篇: Oracle日常操作
请登录后发表评论 登录
全部评论

注册时间:2015-05-01

  • 博文量
    383
  • 访问量
    181055