mysql的order by,group by和distinct优化
order by,group by和distinct三类操作是在mysql中经常使用的,而且都涉及到排序,所以就把这三种操作放在一起介绍。
order by的实现与优化
order by的实现有两种方式,主要就是按用没用到索引来区分:
1. 根据索引字段排序,利用索引取出的数据已经是排好序的,直接返回给客户端;
2. 没有用到索引,将取出的数据进行一次排序操作后返回给客户端。
下面通过示例来介绍这两种方式间的差异,首先利用索引进行order by操作,使用explain分析得出的执行计划:
[sql] view plain copy
EXPLAIN SELECT m.id,m.subject,c.content FROM group_message m,group_message_content c WHERE m.group_id = 1 AND m.id = c.group_msg_id ORDER BY m.user_id\G;
[plain] view plain copy
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: m
type: ref
possible_keys: PRIMARY,idx_group_message_gid_uid
key: idx_group_message_gid_uid
key_len: 4
ref: const
rows: 4
Extra: Using where
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: c
type: ref
possible_keys: group_message_content_msg_id
key: group_message_content_msg_id
key_len: 4
ref: m.id
rows: 11
Extra:
从执行计划里可以看出,进行了order by操作但是执行计划里并没有排序操作,因为optimizer对query进行了优化,它会按照m.user_id上的索引顺序来访问数据,这样获取的数据已经是排好序的。
这种利用索引实现数据排序的方法是 MySQL 中实现结果集排序的最佳做法,可以完全避免因为排序所带来的资源消耗。所以,在我们优化 query 语句中的 order by 的时候,尽可能利用已有的索引避免实际的排序计算,可以很大幅度的提升 order by 操作的性能。在有些 query 的优化过程中,为了避免实际的排序操作而调整索引字段的顺序,甚至是增加索引字段也是值得的。当然,在调整索前,同时还需要评估调整该索引对其他 query 所带来的影响,平衡整体得失。
如果没有用到索引,mysql就会将取出的数据按照一定的排序算法进行排序,然后再把排好序的数据返回给客户端。mysql中主要使用两种排序算法:
1. 取出用于排序的条件字段和指向相应数据行的指针,在sort buffer中对条件进行排序,排好序之后利用指针取出数据行中的请求数据,然后返回给客户端;
2. 取出用于排序的条件字段和其它所有请求数据,将不用于排序的字段存放在一块内存中,然后在sort buffer中对条件字段进行排序,排好序后利用行指针将在内存中的数据进行匹配合并结果集,然后将排好序的数据返回给客户端。
第二种排序算法是mysql4.1版本才开始支持的,第一种算法是各个版本都支持的。两种算法的比较,第二种利用内存减少了数据的二次访问,节省了IO操作,是一种空间换时间的优化方式。
下面用一个实例来分析不使用索引时的执行计划:
[sql] view plain copy
EXPLAIN SELECT m.id,m.subject,c.content FROM group_message m,group_message_content c WHERE m.group_id = 1 AND m.id = c.group_msg_id ORDER BY m.subject\G;
[plain] view plain copy
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: m
type: ref
possible_keys: PRIMARY,idx_group_message_gid_uid
key: idx_group_message_gid_uid
key_len: 4
ref: const
rows: 4
Extra: Using where; Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: c
type: ref
possible_keys: group_message_content_msg_id
key: group_message_content_msg_id
key_len: 4
ref: m.id
rows: 11
Extra:
从执行计划里可以看出,在对group_message进行数据访问的时候,extra信息里显示Using filesort,这就表示从group_message获取数据后需要对数据进行排序操作,然后再利用排序后的结果集来驱动第二个表。
对于更复杂的情况,比如用于排序的字段存在在多个表中,或者在排序之前要先经过join操作,这样mysql必须先把join的结果集放入一个临时表,之后再把临时表中的数据取到sort buffer里进行排序。下面再用一个简单的实例来分析optimizer给出的执行计划。
[sql] view plain copy
explain select m.id,m.subject,c.content FROM group_message m,group_message_content c
WHERE m.group_id = 1 AND m.id = c.group_msg_id ORDER BY c.content\G;
给出的执行计划:
[plain] view plain copy
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: m
type: ref
possible_keys: PRIMARY,idx_group_message_gid_uid
key: idx_group_message_gid_uid
key_len: 4
ref: const
rows: 4
Extra: Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: c
type: ref
possible_keys: group_message_content_msg_id
key: group_message_content_msg_id
key_len: 4
ref: example.m.id
rows: 11
Extra:
可以看见Extra信息显示Using temporary,这就表示将两个表的join内容取出并放进到一个临时表中之后再进行filesort。
通过以上介绍知道order by的优化很简单,就是让mysql使用第二种排序算法,这样可以减少大量的IO操作,提高性能,但是如何做到呢:
1. 加大max_length_for_sort_data参数的设置。mysql通过该参数来决定使用哪种排序算法,当需要取出的所有数据长度小于这个参数的值的时候,mysql将采用第二中改进的排序算法,否则,使用第一种算法,所以只要内存充足就可以设置足够大的值来让mysql采用改进的排序算法;
2. 去掉不必要的返回字段,很容易从上一点知道原因;
3. 增大sort_buffer_size参数的值,当mysql对条件字段进行排序时,如果需要排序字段的总长度大于该参数的值的时候,mysql就会对需要排序的字段使用临时表进行分段,这样也会有性能的消耗。
group by的实现与优化
group by的实现过程除了要使用排序操作外,还要进行分组操作,如果使用到一些聚合函数,还要进行相应的聚合计算。group by的实现方式根据是否使用到索引分为三种:
1. 使用松散(Loose)索引扫描实现group by,所谓的松散索引扫描,就是mysql不需要扫描所有满足条件的索引键即可完成group by操作,下面通过一个简单的实例来分析一下这个过程。
[sql] view plain copy
create index idx_gid_uid_gc on group_message(group_id,user_id,gmt_create);
drop index idx_group_message_gid_uid on group_message;
EXPLAIN SELECT user_id,max(gmt_create) FROM group_message WHERE group_id < 10 GROUP BY group_id,user_id\G;
得到的执行计划:
[plain] view plain copy
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: group_message
type: range
possible_keys: idx_gid_uid_gc
key: idx_gid_uid_gc
key_len: 8
ref: NULL
rows: 4
Extra: Using where; Using index for group-by
可以看见Extra信息显示了Using index for group-by,这就是说mysql使用了松散索引扫描来实现group by操作。
要利用到松散索引扫描实现group by,需要满足以下几个条件:
group by条件字段必须在同一个索引中最前面的连续位置;
只能使用max和min这两个聚合函数;
索引的任何其它部分(除了那些来自查询中引用的GROUP BY)必须为常数(也就是说,必须按常量数量来引用它们),但min或max 函数的参数例外。
为什么松散索引的效率会高很多?
当没有where条件,即必须经过全索引扫描的时候,松散索引扫描的键值数量与分组的分组量一样多,也就是比实际存在的键值数量小很多。而当有where条件包含范围判断式或者等值表达式的时候,松散索引扫描查找满足范围条件的每个组的第1个关键字。
2. 使用紧凑(Tight)索引扫描实现group by,紧凑索引与松散索引最主要的区别就是在需要扫描索引的时候,紧凑索引读取所有满足条件的索引键,然后再来使用group by操作得到相应的结果。下面同样用一个例子来分析。
[sql] view plain copy
EXPLAIN SELECT max(gmt_create) FROM group_message WHERE group_id = 2 GROUP BY user_id\G;
得出的执行计划如下:
[plain] view plain copy
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: group_message
type: ref
possible_keys: idx_group_message_gid_uid,idx_gid_uid_gc
key: idx_gid_uid_gc
key_len: 4
ref: const
rows: 4
Extra: Using where; Using index
从执行计划里看见extra信息显示Using index而不是Using index for group by,意味着需要访问where条件所限定的所有索引键信息之后才能得出结果。optimizer首先会尝试通过松散索引扫描来完成group by操作,当发现有些情况(当group by 条件字段不连续或者不是索引前缀部分的时候,optimizer无法使用松散索引扫描)不满足松散索引时,才会选择紧凑索引扫描来实现。
3. 使用临时表实现group by
group by操作想要利用索引,必须满足group by字段必须同时存放于同一个索引中,且该索引是一个有序索引,而且,使用不同的聚合函数也会影响是否使用索引来实现group by操作。当optimizer无法找到合适的索引可以利用的时候,就会选择将读取的数据放入临时表中来完成group by操作。下面通过一个简单的例子来演示这个过程。
[sql] view plain copy
EXPLAIN SELECT max(gmt_create) FROM group_message WHERE group_id > 1 and group_id < 10 GROUP BY user_id\G;
得到的执行计划如下:
[plain] view plain copy
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: group_message
type: range
possible_keys: idx_group_message_gid_uid,idx_gid_uid_gc
key: idx_gid_uid_gc
key_len: 4
ref: NULL
rows: 32
Extra: Using where; Using index; Using temporary; Using filesort
从执行计划的extra信息中可以看见,对这次的group by操作进行了使用临时表(Using temporary)然后进行了排序操作才完成的。因为group_id并不是一个常量,而是一个范围,并且group by的字段为user_id,mysql无法根据索引的顺序来完成group by的实现,只能先通过索引范围扫描得到需要的数据,然后将数据放入一个临时表,然后再进行排序和分组操作最终完成group by操作。
上面介绍了实现group by操作的三种方式,可以得出以下几点用于group by优化:
1. 尽可能利用索引并且是松散索引来完成group by操作,这的依靠调整索引或者调整query来实现;
2. 当无法利用索引的时候,必须要提供足够的sort_buffer_size来供mysql完成排序操作,之前介绍过,不然mysql会将需要排序的字段进行分段排序,会影响性能。除此之外尽量不要对大结果集进行group by操作,因为一旦数据量超过系统最大临时表大小时,mysql会将临时表里的数据copy到磁盘上然后再进行操作,性能会成数量级的下降。
distinct的实现与优化
distinct的实现原理同group by类似,实现过程只是在group by之后只取出每一组中的第一条记录,所以distinct同样可以利用松散或者紧凑索引来实现,不同的是,当无法利用索引实现distinct时,mysql同样会将数据取出放进一个临时表,不过不会对临时表进行排序操作。下面同样通过一些简单的例子来显示其实现过程。
1.使用松散索引完成distinct操作:
[sql] view plain copy
EXPLAIN SELECT DISTINCT group_id FROM group_message\G;
得到的执行计划如下:
[plain] view plain copy
*************************** 1. row ***************************
id: 1
SELECT_type: SIMPLE
table: group_message
type: range
possible_keys: NULL
key: idx_gid_uid_gc
key_len: 4
ref: NULL
rows: 10
Extra: Using index for group-by
从extra信息里可以看见Using index for group by,意味着mysql使用了松散索引来完成group by操作,然后取出每组中的第一条数据来完成distinct操作。
2. 使用紧凑索引完成distinct操作:
[sql] view plain copy
EXPLAIN SELECT DISTINCT user_id FROM group_message where group_id =2\G;
得到的执行计划如下:
[plain] view plain copy
*************************** 1. row ***************************
id: 1
SELECT_type: SIMPLE
table: group_message
type: ref
possible_keys: idx_gid_uid_gc
key: idx_gid_uid_gc
key_len: 4
ref: const
rows: 4
Extra: Using WHERE; Using index
extra信息显示Using index,说明使用了紧凑索引。mysql让存储引擎扫描group_id=2的所有索引键,得出所有的user_id,因为是联合索引,所以取出的user_id已经是排好序的,对相同的user_id取出一条记录即完成了本次distinct操作。
3. 无法利用索引完成distinct操作:
[sql] view plain copy
EXPLAIN SELECT DISTINCT user_id FROM group_message WHERE group_id > 1 AND group_id < 10\G;
得到的执行计划如下:
[plain] view plain copy
*************************** 1. row ***************************
id: 1
SELECT_type: SIMPLE
table: group_message
type: range
possible_keys: idx_gid_uid_gc
key: idx_gid_uid_gc
key_len: 4
ref: NULL
rows: 32
Extra: Using WHERE; Using index; Using temporary
从extra信息看出,mysql使用的临时表,但并没有进行排序操作。
4. 同时使用distinct和group by:
[sql] view plain copy
EXPLAIN SELECT DISTINCT max(user_id) FROM group_message WHERE group_id > 1 AND group_id < 10 GROUP BY group_id\G;
得到的执行计划如下:
[plain] view plain copy
*************************** 1. row ***************************
id: 1
SELECT_type: SIMPLE
table: group_message
type: range
possible_keys: idx_gid_uid_gc
key: idx_gid_uid_gc
key_len: 4
ref: NULL
rows: 32
Extra: Using WHERE; Using index; Using temporary; Using filesort
从extra里看出mysql使用了排序操作,因为进行了group by操作。
因为distinct的实现原理同group by类似,所以优化手段也一样,尽量使用索引,无法使用索引的时候,确保不要在大结果集上进行distinct操作,磁盘上的IO操作和内存中的IO操作性能完全不是一个数量级的差距。