FAQ系列 | index extensions特性介绍

0、导读

本文介绍MySQL的index extensions特性,以及如何利用这个特性实现SQL查询优化。

1、什么是index extensions

index extensions是MySQL 5.6.9之后的新特性,关于这个特性,手册中的解释是这样的:InnoDB automatically extends each secondary index by appending the primary key columns to it(出处详见手册 8.2.1.7 Use of Index Extensions,原文链接:https://dev.mysql.com/doc/refman/5.6/en/index-extensions.html 简言之就是,InnoDB引擎表中,会把主键所有列值附加存储在辅助索引中

假设有这样一个表:
CREATE TABLE t(
a int not null,
b int not null,
c int not null,
d int not null,
PRIMARY KEY(a, b),
KEY i_c(c)
) ENGINE=InnoDB;

意思是,该表中的辅助索引 i_c 的索引键值,实际上也同时存储了主键中的两个列值,也就是说,i_c 的索引数据结构中,实际上存储的列是:c、a、b 三列的值。

我们可通过 innodb_table_monitor 查看验证下:

TABLE: name test/t, id 681, flags 1, columns 7, indexes 2, appr.rows 0
COLUMNS: a: DATA_INT DATA_BINARY_TYPE DATA_NOT_NULL len 4; b: DATA_INT DATA_BINARY_TYPE DATA_NOT_NULL len 4; c: DATA_INT DATA_BINARY_TYPE DATA_NOT_NULL len 4; d: DATA_INT DATA_BINARY_TYPE DATA_NOT_NULL len 4; DB_ROW_ID: DATA_SYS prtype 256 len 6; DB_TRX_ID: DATA_SYS prtype 257 len 6; DB_ROLL_PTR: DATA_SYS prtype 258 len 7;

 INDEX: name PRIMARY, id 1159, fields 2/6, uniq 2, type 3
root page 3, appr.key vals 0, leaf pages 1, size pages 1
  FIELDS:  a b DB_TRX_ID DB_ROLL_PTR c d

 INDEX: name i_c, id 1160, fields 1/3, uniq 3, type 0
root page 4, appr.key vals 0, leaf pages 1, size pages 1
  FIELDS:  c a b

可见,确实是如此。我们顺便也看到 PRIMARY KEY 里包含了所有的列值,以及 DB_TRX_ID、DB_ROLL_PTR 等额外属性(InnoDB引擎独有特性,用于实现InnoDB的事务)。

2、怎么利用index extensions

事实上,辅助索引实际也存储主键值的特性,在InnoDB引擎中一直都是如此,只是从5.6.9版本开始后,在计算执行计划时,查询优化器(optimizer)才能识别到这个特性,并且利用这个特性。而在5.6.9以前,虽然这个特性也存在,但并不被查询优化器识别,也就无法被利用了。

这个特性可适用于 ref, range, and index_merge 等多种索引访问方式,在稀松索引扫描(loose index scan)、联接(join)、排序以及MIN()/MA()等场景下。

我们来看看这个特性怎么被优化器识别并利用的,假设上述测试表中的测试数据有:
SELECT * FROM t;
+—-+—-+—-+—-+
| a | b | c | d |
+—-+—-+—-+—-+
| 1 | 2 | 4 | 2 |
| 1 | 3 | 2 | 2 |
| 1 | 4 | 9 | 2 |
| 1 | 5 | 9 | 2 |
| 1 | 6 | 8 | 2 |
| 2 | 2 | 9 | 2 |
| 3 | 2 | 8 | 2 |
| 4 | 2 | 6 | 2 |
| 5 | 2 | 6 | 2 |
| 6 | 2 | 1 | 2 |
+—-+—-+—-+—-+

MySQL版本:5.6.21-70.0-log Percona Server (GPL), Release 70.0, Revision 688。

假设有下面的查询,看下它的执行计划:
mysql> DESC SELECT a,b,c FROM t WHERE a = 1 AND c = 9\G
id: 1
select_type: SIMPLE
table: t
type: ref
possible_keys: PRIMARY,i_c
key: i_c
     key_len: 8
ref: const,const
rows: 2
Extra: Using index

在5.6.9以前的版本(或者修改优化器开关,关闭 index extensions 特性。如果用5.6.9以后的版本测试,还请记得):
mysql> DESC SELECT a,b,c FROM t WHERE a = 1 AND c = 9\G
id: 1
select_type: SIMPLE
table: t
type: ref
possible_keys: PRIMARY,i_c
key: i_c
     key_len: 4
ref: const
rows: 3
Extra: Using where;
Using index

可执行下面的命令关闭 index extensions 特性:
mysql> SET optimizer_switch = ‘use_index_extensions=off’;

这两个执行计划的区别在于:

  • 前者的key_len是8而后者是4,预示着可以用到的索引不仅是i_c这个索引,还有主键索引;
  • 前者的ref列值是const,const,而后者只有const,预示着前者用到了2个索引部分,而后者只有一个;
  • 前者评估的rows为2,而后者评估的rows为3,因为前者效率更高;
  • 后者的Extra列中多了Using Where,表示后者还需要从结果中再次过滤数据,而不能像前者那样直接利用索引取得结果。

我们还可以根据观察STATUS中的Handler_read_%值差异来对比两个SQL的实际执行代价(执行FLUSH STATUS后,执行查询SQL,再执行SHOW STATUS LIKE ‘Handler_read_%’ 查看):

  • 后者的代价是 Handler_read_next = 3;
  • 前者的代价是 Handler_read_next = 2;
  • 如果数据量更大的话,这个差值也会随之增大。

由此可见,前者的效率确实要比后者来的更高。

3、后记

我们应该经常关注新版本的新特性,利用这些新特性提升SQL效率 :)

 

关于MySQL的方方面面大家想了解什么,可以直接留言回复,我会从中选择一些热门话题进行分享。 同时希望大家多多转发,多一些阅读量是老叶继续努力分享的绝佳助力,谢谢大家 :)

最后打个广告,运维圈人士专属铁观音茶叶微店上线了,访问:http://yejinrong.com 获得专属优惠

发表评论

电子邮件地址不会被公开。

To create code blocks or other preformatted text, indent by four spaces:

    This will be displayed in a monospaced font. The first four 
    spaces will be stripped off, but all other whitespace
    will be preserved.
    
    Markdown is turned off in code blocks:
     [This is not a link](http://example.com)

To create not a block, but an inline code span, use backticks:

Here is some inline `code`.

For more help see http://daringfireball.net/projects/markdown/syntax

Time limit is exhausted. Please reload CAPTCHA.