标签归档:index

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 获得专属优惠

[MySQL FAQ]系列 — MySQL联合索引是否支持不同排序规则

篇首语:
截止到目前的5.7.4版本为止,MySQL的联合索引仍无法支持联合索引使用不同排序规则,例如:ALTER TABLE t ADD INDEX idx(col1, col2 DESC)。

先来了解下MySQL关于索引的一些基础知识要点:

• a、EXPLAIN结果中的key_len只显示了条件检索子句需要的索引长度,但 ORDER BY、GROUP BY 子句用到的索引则不计入 key_len 统计值;
• b、联合索引(composite index):多个字段组成的索引,称为联合索引;
例如:ALTER TABLE t ADD INDEX `idx` (col1, col2, col3)
• c、覆盖索引(covering index):如果查询需要读取到索引中的一个或多个字段,则可以从索引树中直接取得结果集,称为覆盖索引;
例如:SELECT col1, col2 FROM t;
• d、最左原则(prefix index):如果查询条件检索时,只需要匹配联合索引中的最左顺序一个或多个字段,称为最左索引原则,或者叫最左前缀;
例如:SELECT * FROM t WHERE col1 = ? AND col2 = ?;
• e、在老版本(大概是5.5以前,具体版本号未确认核实)中,查询使用联合索引时,可以不区分条件中的字段顺序,在这以前是需要按照联合索引的创建顺序书写SQL条件子句的;
例如:SELECT * FROM t WHERE col3 = ? AND col1 = ? AND col2 = ?;
• f、MySQL截止目前还只支持多个字段都是正序索引,不支个别字段持倒序索引;
例如:ALTER TABLE t ADD INDEX `idx` (col1, col2, col3 DESC),这里的DESC只是个预留的关键字,目前还不能真正有作用
• g、联合索引中,如果查询条件中最左边某个索引列使用范围查找,则只能使用前缀索引,无法使用到整个索引;
例如:SELECT * FROM t WHERE col1 = ? AND col2 >= ? AND col3 = ?; 这时候,只能用到 idx 索引的最左2列进行检索,而col3条件则无法利用索引进行检索
• h、InnoDB引擎中,二级索引实际上包含了主键索引值;

关于 key_len 的计算规则:

• 当索引字段为定长数据类型,比如:char,int,datetime,需要有是否为空的标记,这个标记需要占用1个字节;
• 当索引字段为变长数据类型,比如:varchar,除了是否为空的标记外,还需要有长度信息,需要占用2个字节;
• 当字段定义为非空的时候,是否为空的标记将不占用字节;
• 同时还需要考虑表所使用字符集的差异,latin1编码一个字符1个字节,gbk编码一个字符2个字节,utf8编码一个字符3个字节;

因此,key_len长度的计算公式

• varchr(10)变长字段且允许NULL : 10*(Character Set:utf8=3,gbk=2,latin1=1)+1(NULL标记位)+2(变长字段)
• varchr(10)变长字段且不允许NULL : 10*(Character Set:utf8=3,gbk=2,latin1=1)+2(变长字段)
• char(10)固定字段且允许NULL : 10*(Character Set:utf8=3,gbk=2,latin1=1)+1(NULL标记位)
• char(10)固定字段且不允许NULL : 10*(Character Set:utf8=3,gbk=2,latin1=1)

附,关于 filesort 排序算法:
光看 filesort 字面意思,可能以为是要利用磁盘文件进行排序,实则不全然。
当MySQL不能使用索引进行排序时,就会利用自己的排序算法(快速排序算法)在内存(sort buffer)中对数据进行排序,如果内存装载不下,它会将磁盘上的数据进行分块,再对各个数据块进行排序,然后将各个块合并成有序的结果集(实际上就是外排序)。

对于filesort,MySQL有两种排序算法:
1、两遍扫描算法(Two passes)
实现方式是先将须要排序的字段和可以直接定位到相关行数据的指针信息取出,然后在设定的内存(通过参数 sort_buffer_size 设定)中进行排序,完成排序之后再次通过行指针信息取出所需的列。
注:该算法是4.1之前只有这种算法,它需要两次访问数据,尤其是第二次读取操作会导致大量的随机I/O操作。不过,这种方法内存开销较小。

2、一次扫描算法(single pass)
该算法一次性将所需的列全部取出,在内存中排序后直接将结果输出。

注:从 MySQL 4.1 版本开始支持该算法。它减少了I/O的次数,效率较高,但是内存开销也较大。如果我们将并不需要的列也取出来,就会极大地浪费排序过程所需要的内存。在 MySQL 4.1 之后的版本中,可以通过设置 max_length_for_sort_data 参数来控制 MySQL 选择第一种排序算法还是第二种。当取出的所有大字段总大小大于 max_length_for_sort_data 的设置时,MySQL 就会选择使用第一种排序算法,反之,则会选择第二种。为了尽可能地提高排序性能,我们自然更希望使用第二种排序算法,所以在SQL中仅仅取出需要的列是非常有必要的。

当对连接操作进行排序时,如果ORDER BY仅仅引用第一个表的列,MySQL对该表进行filesort操作,然后进行连接处理,此时,EXPLAIN输出“Using filesort”;否则,MySQL必须将查询的结果集生成一个临时表,在连接完成之后进行filesort操作,此时,EXPLAIN输出“Using temporary;Using filesort”。

后面是几个几个测试结果,有兴趣不怕累的可以看看,哈哈。

测试MySQL版本:5.5.37-log MySQL Community Server (GPL)

#创建一个测试表,id是主键字段,(a1, a2) 组成联合索引

(yejr@imysql.com)> show create table t\G
*************************** 1. row ***************************
Table: t
Create Table: CREATE TABLE `t` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`a1` int(10) unsigned NOT NULL DEFAULT '0',
`a2` int(10) unsigned NOT NULL DEFAULT '0',
`aa` varchar(20) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `idx` (`a1`,`a2`)
) ENGINE=InnoDB AUTO_INCREMENT=65 DEFAULT CHARSET=utf8

#填充了64条测试数据

(yejr@imysql.com)> show table status like 't'\G
*************************** 1. row ***************************
Name: t
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 64
Avg_row_length: 256
Data_length: 16384
Max_data_length: 0
Index_length: 16384
Data_free: 0
Auto_increment: 122
Create_time: 2014-09-15 17:17:09
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:

#对 a1、a2 正序排序,同时取a1、a2两个字段,可以直接使用该联合索引取回结果,并且排序完成
#符合规则c

(yejr@imysql.com)> explain select a1, a2 from t order by a1, a2\G
id: 1
select_type: SIMPLE
table: t
type: index
possible_keys: NULL
key: idx
key_len: 8
ref: NULL
rows: 64
Extra: Using index

#对 a1、a2 倒序排序,同时取a1、a2两个字段,可以直接使用该联合索引取回结果,并且排序完成
#由于同时对a1、a2都是倒序排序,因此完全可以用到索引的顺序,只是反向扫描而已
#符合规则c

(yejr@imysql.com)> explain select a1, a2 from t order by a1 desc, a2 desc\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t
type: index
possible_keys: NULL
key: idx
key_len: 8
ref: NULL
rows: 64
Extra: Using index

#对 a1、a2正序排序,只取a1字段,可以直接使用该联合索引取回结果,并且排序完成
#匹配规则c

(yejr@imysql.com)> explain select a1 from t order by a1, a2\G
id: 1
select_type: SIMPLE
table: t
type: index
possible_keys: NULL
key: idx
key_len: 8
ref: NULL
rows: 64
Extra: Using index

#对 a1、a2 正序排序,只取a2字段,可以直接使用该联合索引取回结果,并且排序完成
#符合规则c

(yejr@imysql.com)> explain select a2 from t order by a1, a2 \G
id: 1
select_type: SIMPLE
table: t
type: index
possible_keys: NULL
key: idx
key_len: 8
ref: NULL
rows: 64
Extra: Using index

#只对 a1 正序排序,同时取a1、a2两个字段,可以直接使用该联合索引取回结果,并且排序完成
#符合规则c

(yejr@imysql.com)> explain select a1, a2 from t order by a1\G
id: 1
select_type: SIMPLE
table: t
type: index
possible_keys: NULL
key: idx
key_len: 8
ref: NULL
rows: 64
Extra: Using index

#对 a1 正序排序,对 a2 倒序排序,只取a1字段,可以直接使用该联合索引取回结果,但排序时需要进行filesort排序,不能利用索引直接得到排序结果
#这时虽然只读取一个字段,但实际还是扫描了整个索引,并非使用前缀索引
#符合规则c、f

(yejr@imysql.com)> explain select a1 from t order by a1, a2 desc \G
id: 1
select_type: SIMPLE
table: t
type: index
possible_keys: NULL
key: idx
key_len: 8
ref: NULL
rows: 64
Extra: Using index; Using filesort

#只取a1字段,同时只对 a1 字段正序排序,这时可用联合索引取得结果,同时也可以利用前缀索引的原则进行排序
#符合规则c

(yejr@imysql.com)> explain select a1 from t order by a1\G
id: 1
select_type: SIMPLE
table: t
type: index
possible_keys: NULL
key: idx
key_len: 8
ref: NULL
rows: 64
Extra: Using index

#只取a1字段,同时只对 a2 字段正序排序,这时虽然可用联合索引取得结果,但排序时需要进行filesort排序,不能利用索引直接得到排序结果
#符合规则c、f

(yejr@imysql.com)> explain select a1 from t order by a2\G
id: 1
select_type: SIMPLE
table: t
type: index
possible_keys: NULL
key: idx
key_len: 8
ref: NULL
rows: 64
Extra: Using index; Using filesort

#对 a1 正序排序,对a2 倒序排序,只取a1字段,可以直接使用该联合索引取回结果,但排序时需要进行filesort排序,不能利用索引直接得到排序结果
#这时虽然只读取一个字段,但实际还是扫描了整个索引,并非使用前缀索引
#符合规则c、f

(yejr@imysql.com)> explain select a1 from t order by a1, a2 desc \G
id: 1
select_type: SIMPLE
table: t
type: index
possible_keys: NULL
key: idx
key_len: 8
ref: NULL
rows: 64
Extra: Using index; Using filesort

#对 a1 正序排序,对a2 倒序排序,只取a2字段,可以直接使用该联合索引取回结果,但排序时需要进行filesort排序,不能利用索引直接得到排序结果
#这时虽然只读取一个字段,但实际还是扫描了整个索引,并非使用前缀索引
#符合规则c、f

(yejr@imysql.com)> explain select a2 from t order by a1, a2 desc \G
id: 1
select_type: SIMPLE
table: t
type: index
possible_keys: NULL
key: idx
key_len: 8
ref: NULL
rows: 64
Extra: Using index; Using filesort

#对 a1 正序排序,对a2 倒序排序,只取a2字段,可以直接使用该联合索引取回结果,但排序时需要进行filesort排序,不能利用索引直接得到排序结果
#这时虽然只读取一个字段,但实际还是扫描了整个索引,并非使用前缀索引
#符合规则c、f

(yejr@imysql.com)> explain select a1 from t order by a1, a2 \G
id: 1
select_type: SIMPLE
table: t
type: index
possible_keys: NULL
key: idx
key_len: 8
ref: NULL
rows: 64
Extra: Using index

#对 a1 、a2顺序排序,取得主键id字段,可以直接使用该联合索引取回结果并完成排序。
#这里需要注意下,二级索引其实是包括主键索引的,因此用idx索引即可取到全部结果。
#下面这个SQL也是一样的效果:select a1,a2,id from t order by a1, a2;
#符合规则c、h

(yejr@imysql.com)> explain select id from t order by a1, a2 \G
id: 1
select_type: SIMPLE
table: t
type: index
possible_keys: NULL
key: idx
key_len: 8
ref: NULL
rows: 64
Extra: Using index

#对 a1 正序排序,对a2 倒序排序,取得主键id字段,可以直接使用该联合索引取回结果,但需要进行filesort排序。
#符合规则c、f、h

(yejr@imysql.com)> explain select id from t order by a1, a2 desc \G
id: 1
select_type: SIMPLE
table: t
type: index
possible_keys: NULL
key: idx
key_len: 8
ref: NULL
rows: 64
Extra: Using index; Using filesort

#对 a1 倒序排序,对a2 正序排序,取得主键id字段,可以直接使用该联合索引取回结果,但需要进行filesort排序。
#符合规则c、f、h

(yejr@imysql.com)> explain select id from t order by a1 desc, a2 \G
id: 1
select_type: SIMPLE
table: t
type: index
possible_keys: NULL
key: idx
key_len: 8
ref: NULL
rows: 64
Extra: Using index; Using filesort

#过滤条件a1字段(使用前缀索引扫描,key_len为4),对a2字段进行正序排序,取得主键id字段,可以直接使用联合索引取回结果
#符合规则a、c、d、h

(yejr@imysql.com)> explain select id from t where a1 = 219 order by a2\G
id: 1
select_type: SIMPLE
table: t
type: ref
possible_keys: idx
key: idx
key_len: 4
ref: const
rows: 2
Extra: Using where; Using index