mysql联合distinct_mysql的order by，group by和distinct优化

order by，group by和distinct三类操作是在mysql中经常使用的，而且都涉及到排序，所以就把这三种操作放在一起介绍。

1、order by的实现与优化

order by的实现有两种方式，主要就是按用没用到索引来区分：

1. 根据索引字段排序，利用索引取出的数据已经是排好序的，直接返回给客户端；

2. 没有用到索引，将取出的数据进行一次排序操作后返回给客户端。

下面通过示例来介绍这两种方式间的差异，首先利用索引进行order by操作，使用explain分析得出的执行计划：

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;

*************************** 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给出的执行计划。

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;

给出的执行计划：

*************************** 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就会对需要排序的字段使用临时表进行分段，这样也会有性能的消耗。

2、distinct的实现与优化

distinct的实现原理同group by类似，实现过程只是在group by之后只取出每一组中的第一条记录，所以distinct同样可以利用松散或者紧凑索引来实现，不同的是，当无法利用索引实现distinct时，mysql同样会将数据取出放进一个临时表，不过不会对临时表进行排序操作。下面同样通过一些简单的例子来显示其实现过程。

1.使用松散索引完成distinct操作：

EXPLAIN SELECT DISTINCT group_id FROM group_message\G;

得到的执行计划如下：

*************************** 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操作：

EXPLAIN SELECT DISTINCT user_id FROM group_message where group_id =2\G;

得到的执行计划如下：

*************************** 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操作：

EXPLAIN SELECT DISTINCT user_id FROM group_message WHERE group_id > 1 AND group_id < 10\G;

得到的执行计划如下：

*************************** 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：

EXPLAIN SELECT DISTINCT max(user_id) FROM group_message WHERE group_id > 1 AND group_id < 10 GROUP BY group_id\G;

得到的执行计划如下：

*************************** 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操作性能完全不是一个数量级的差距。