篇首语&＃xff1a;
截止到目前的5.7.4版本为止&＃xff0c;MySQL的联合索引仍无法支持联合索引使用不同排序规则&＃xff0c;例如&＃xff1a;ALTER TABLE t ADD INDEX idx(col1, col2 DESC)。
先来了解下MySQL关于索引的一些基础知识要点&＃xff1a;
• a、EXPLAIN结果中的key_len只显示了条件检索子句需要的索引长度&＃xff0c;但 ORDER BY、GROUP BY 子句用到的索引则不计入 key_len 统计值&＃xff1b;
• b、联合索引(composite index)&＃xff1a;多个字段组成的索引&＃xff0c;称为联合索引&＃xff1b;
例如&＃xff1a;ALTER TABLE t ADD INDEX &＃96;idx&＃96; (col1, col2, col3)
• c、覆盖索引(covering index)&＃xff1a;如果查询需要读取到索引中的一个或多个字段&＃xff0c;则可以从索引树中直接取得结果集&＃xff0c;称为覆盖索引&＃xff1b;
例如&＃xff1a;SELECT col1, col2 FROM t;
• d、最左原则(prefix index)&＃xff1a;如果查询条件检索时&＃xff0c;只需要匹配联合索引中的最左顺序一个或多个字段&＃xff0c;称为最左索引原则&＃xff0c;或者叫最左前缀&＃xff1b;
例如&＃xff1a;SELECT * FROM t WHERE col1 &＃61; ? AND col2 &＃61; ?;
• e、在老版本(大概是5.5以前&＃xff0c;具体版本号未确认核实)中&＃xff0c;查询使用联合索引时&＃xff0c;可以不区分条件中的字段顺序&＃xff0c;在这以前是需要按照联合索引的创建顺序书写SQL条件子句的&＃xff1b;
例如&＃xff1a;SELECT * FROM t WHERE col3 &＃61; ? AND col1 &＃61; ? AND col2 &＃61; ?;
• f、MySQL截止目前还只支持多个字段都是正序索引&＃xff0c;不支个别字段持倒序索引&＃xff1b;
例如&＃xff1a;ALTER TABLE t ADD INDEX &＃96;idx&＃96; (col1, col2, col3 DESC)&＃xff0c;这里的DESC只是个预留的关键字&＃xff0c;目前还不能真正有作用
• g、联合索引中&＃xff0c;如果查询条件中最左边某个索引列使用范围查找&＃xff0c;则只能使用前缀索引&＃xff0c;无法使用到整个索引&＃xff1b;
例如&＃xff1a;SELECT * FROM t WHERE col1 &＃61; ? AND col2 >&＃61; ? AND col3 &＃61; ?; 这时候&＃xff0c;只能用到 idx 索引的最左2列进行检索&＃xff0c;而col3条件则无法利用索引进行检索
• h、InnoDB引擎中&＃xff0c;二级索引实际上包含了主键索引值&＃xff1b;
关于 key_len 的计算规则&＃xff1a;
• 当索引字段为定长数据类型&＃xff0c;比如&＃xff1a;char&＃xff0c;int&＃xff0c;datetime&＃xff0c;需要有是否为空的标记&＃xff0c;这个标记需要占用1个字节&＃xff1b;
• 当索引字段为变长数据类型&＃xff0c;比如&＃xff1a;varchar&＃xff0c;除了是否为空的标记外&＃xff0c;还需要有长度信息&＃xff0c;需要占用2个字节&＃xff1b;
• 当字段定义为非空的时候&＃xff0c;是否为空的标记将不占用字节&＃xff1b;
• 同时还需要考虑表所使用字符集的差异&＃xff0c;latin1编码一个字符1个字节&＃xff0c;gbk编码一个字符2个字节&＃xff0c;utf8编码一个字符3个字节&＃xff1b;
因此&＃xff0c;key_len长度的计算公式
• varchr(10)变长字段且允许NULL : 10*(Character Set&＃xff1a;utf8&＃61;3,gbk&＃61;2,latin1&＃61;1)&＃43;1(NULL标记位)&＃43;2(变长字段)
• varchr(10)变长字段且不允许NULL : 10*(Character Set&＃xff1a;utf8&＃61;3,gbk&＃61;2,latin1&＃61;1)&＃43;2(变长字段)
• char(10)固定字段且允许NULL : 10*(Character Set&＃xff1a;utf8&＃61;3,gbk&＃61;2,latin1&＃61;1)&＃43;1(NULL标记位)
• char(10)固定字段且不允许NULL : 10*(Character Set&＃xff1a;utf8&＃61;3,gbk&＃61;2,latin1&＃61;1)
附&＃xff0c;关于 filesort 排序算法&＃xff1a;
光看 filesort 字面意思&＃xff0c;可能以为是要利用磁盘文件进行排序&＃xff0c;实则不全然。
当MySQL不能使用索引进行排序时&＃xff0c;就会利用自己的排序算法(快速排序算法)在内存(sort buffer)中对数据进行排序&＃xff0c;如果内存装载不下&＃xff0c;它会将磁盘上的数据进行分块&＃xff0c;再对各个数据块进行排序&＃xff0c;然后将各个块合并成有序的结果集(实际上就是外排序)。
对于filesort&＃xff0c;MySQL有两种排序算法&＃xff1a;
1、两遍扫描算法(Two passes)
实现方式是先将须要排序的字段和可以直接定位到相关行数据的指针信息取出&＃xff0c;然后在设定的内存(通过参数 sort_buffer_size 设定)中进行排序&＃xff0c;完成排序之后再次通过行指针信息取出所需的列。
注&＃xff1a;该算法是4.1之前只有这种算法&＃xff0c;它需要两次访问数据&＃xff0c;尤其是第二次读取操作会导致大量的随机I/O操作。不过&＃xff0c;这种方法内存开销较小。
2、一次扫描算法(single pass)
该算法一次性将所需的列全部取出&＃xff0c;在内存中排序后直接将结果输出。
注&＃xff1a;从 MySQL 4.1 版本开始支持该算法。它减少了I/O的次数&＃xff0c;效率较高&＃xff0c;但是内存开销也较大。如果我们将并不需要的列也取出来&＃xff0c;就会极大地浪费排序过程所需要的内存。在 MySQL 4.1 之后的版本中&＃xff0c;可以通过设置 max_length_for_sort_data 参数来控制 MySQL 选择第一种排序算法还是第二种。当取出的所有大字段总大小大于max_length_for_sort_data 的设置时&＃xff0c;MySQL 就会选择使用第一种排序算法&＃xff0c;反之&＃xff0c;则会选择第二种。为了尽可能地提高排序性能&＃xff0c;我们自然更希望使用第二种排序算法&＃xff0c;所以在SQL中仅仅取出需要的列是非常有必要的。
当对连接操作进行排序时&＃xff0c;如果ORDER BY仅仅引用第一个表的列&＃xff0c;MySQL对该表进行filesort操作&＃xff0c;然后进行连接处理&＃xff0c;此时&＃xff0c;EXPLAIN输出“Using filesort”&＃xff1b;否则&＃xff0c;MySQL必须将查询的结果集生成一个临时表&＃xff0c;在连接完成之后进行filesort操作&＃xff0c;此时&＃xff0c;EXPLAIN输出“Using temporary;Using filesort”。
后面是几个几个测试结果&＃xff0c;有兴趣不怕累的可以看看&＃xff0c;哈哈。
测试MySQL版本&＃xff1a;5.5.37-log MySQL Community Server (GPL)
创建一个测试表&＃xff0c;id是主键字段&＃xff0c;(a1, a2) 组成联合索引
(yejr&＃64;imysql.com)> show create table t\G
*************************** 1\. row ***************************
Table: t
Create Table: CREATE TABLE &＃96;t&＃96; (
&＃96;id&＃96; int(10) unsigned NOT NULL AUTO_INCREMENT,
&＃96;a1&＃96; int(10) unsigned NOT NULL DEFAULT &＃39;0&＃39;,
&＃96;a2&＃96; int(10) unsigned NOT NULL DEFAULT &＃39;0&＃39;,
&＃96;aa&＃96; varchar(20) NOT NULL DEFAULT &＃39;&＃39;,
PRIMARY KEY (&＃96;id&＃96;),
KEY &＃96;idx&＃96; (&＃96;a1&＃96;,&＃96;a2&＃96;)
) ENGINE&＃61;InnoDB AUTO_INCREMENT&＃61;65 DEFAULT CHARSET&＃61;utf8
填充了64条测试数据
(yejr&＃64;imysql.com)> show table status like &＃39;t&＃39;\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 正序排序&＃xff0c;同时取a1、a2两个字段&＃xff0c;可以直接使用该联合索引取回结果&＃xff0c;并且排序完成
符合规则c
(yejr&＃64;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 倒序排序&＃xff0c;同时取a1、a2两个字段&＃xff0c;可以直接使用该联合索引取回结果&＃xff0c;并且排序完成
由于同时对a1、a2都是倒序排序&＃xff0c;因此完全可以用到索引的顺序&＃xff0c;只是反向扫描而已
符合规则c
(yejr&＃64;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正序排序&＃xff0c;只取a1字段&＃xff0c;可以直接使用该联合索引取回结果&＃xff0c;并且排序完成
匹配规则c
(yejr&＃64;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 正序排序&＃xff0c;只取a2字段&＃xff0c;可以直接使用该联合索引取回结果&＃xff0c;并且排序完成
符合规则c
(yejr&＃64;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 正序排序&＃xff0c;同时取a1、a2两个字段&＃xff0c;可以直接使用该联合索引取回结果&＃xff0c;并且排序完成
符合规则c
(yejr&＃64;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 正序排序&＃xff0c;对 a2 倒序排序&＃xff0c;只取a1字段&＃xff0c;可以直接使用该联合索引取回结果&＃xff0c;但排序时需要进行filesort排序&＃xff0c;不能利用索引直接得到排序结果
这时虽然只读取一个字段&＃xff0c;但实际还是扫描了整个索引&＃xff0c;并非使用前缀索引
符合规则c、f
(yejr&＃64;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字段&＃xff0c;同时只对 a1 字段正序排序&＃xff0c;这时可用联合索引取得结果&＃xff0c;同时也可以利用前缀索引的原则进行排序
符合规则c
(yejr&＃64;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字段&＃xff0c;同时只对 a2 字段正序排序&＃xff0c;这时虽然可用联合索引取得结果&＃xff0c;但排序时需要进行filesort排序&＃xff0c;不能利用索引直接得到排序结果
符合规则c、f
(yejr&＃64;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 正序排序&＃xff0c;对a2 倒序排序&＃xff0c;只取a1字段&＃xff0c;可以直接使用该联合索引取回结果&＃xff0c;但排序时需要进行filesort排序&＃xff0c;不能利用索引直接得到排序结果
这时虽然只读取一个字段&＃xff0c;但实际还是扫描了整个索引&＃xff0c;并非使用前缀索引
符合规则c、f
(yejr&＃64;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 正序排序&＃xff0c;对a2 倒序排序&＃xff0c;只取a2字段&＃xff0c;可以直接使用该联合索引取回结果&＃xff0c;但排序时需要进行filesort排序&＃xff0c;不能利用索引直接得到排序结果
这时虽然只读取一个字段&＃xff0c;但实际还是扫描了整个索引&＃xff0c;并非使用前缀索引
符合规则c、f
(yejr&＃64;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 正序排序&＃xff0c;对a2 倒序排序&＃xff0c;只取a2字段&＃xff0c;可以直接使用该联合索引取回结果&＃xff0c;但排序时需要进行filesort排序&＃xff0c;不能利用索引直接得到排序结果
这时虽然只读取一个字段&＃xff0c;但实际还是扫描了整个索引&＃xff0c;并非使用前缀索引
符合规则c、f
(yejr&＃64;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顺序排序&＃xff0c;取得主键id字段&＃xff0c;可以直接使用该联合索引取回结果并完成排序。
这里需要注意下&＃xff0c;二级索引其实是包括主键索引的&＃xff0c;因此用idx索引即可取到全部结果。
下面这个SQL也是一样的效果&＃xff1a;select a1,a2,id from t order by a1, a2;
符合规则c、h
(yejr&＃64;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 正序排序&＃xff0c;对a2 倒序排序&＃xff0c;取得主键id字段&＃xff0c;可以直接使用该联合索引取回结果&＃xff0c;但需要进行filesort排序。
符合规则c、f、h
(yejr&＃64;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 倒序排序&＃xff0c;对a2 正序排序&＃xff0c;取得主键id字段&＃xff0c;可以直接使用该联合索引取回结果&＃xff0c;但需要进行filesort排序。
符合规则c、f、h
(yejr&＃64;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字段(使用前缀索引扫描&＃xff0c;key_len为4)&＃xff0c;对a2字段进行正序排序&＃xff0c;取得主键id字段&＃xff0c;可以直接使用联合索引取回结果
符合规则a、c、d、h
(yejr&＃64;imysql.com)> explain select id from t where a1 &＃61; 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
京公网安备 11010802041100号