mysqlORDERBY,GROUPBY和DISTINCT原理及执⾏顺序
写的顺序：select ... order by..
执⾏顺序： select ...
前⾔
除了常规的Join语句之外，还有⼀类Query语句也是使⽤⽐较频繁的，那就是ORDERBY，GROUP BY以及DISTINCT这三类查询。考虑到这三类查询都涉及到数据的排序等操作，所以我将他们放在了⼀起，下⾯就针对这三类Query语句做基本的分析。
ORDER BY 的实现与优化
在MySQL中，ORDERBY的实现有如下两种类型：
⼀种是通过有序索引⽽直接取得有序的数据，这样不⽤进⾏任何排序操作即可得到满⾜客户端要求的有序数据返回给客户端；
另外⼀种则需要通过MySQL的排序算法将存储引擎中返回的数据进⾏排序然后再将排序后的数据返回给客户端。
下⾯我们就针对这两种实现⽅式做⼀个简单的分析。⾸先分析⼀下第⼀种不⽤排序的实现⽅式。同样还是通过⽰例来说话吧：
sky@localhost : example 09:48:41> EXPLAIN
-> SELECT m.id,m.t
-> FROM group_message m,group_message_content c
-> up_id = 1 AND m.id = c.group_msg_id
-> ORDER BY m.user_id\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
*************************** 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: id
rows: 11
Extra:
看看上⾯的这个Query语句，明明有ORDER BY user_id，为什么在执⾏计划中却没有排序操作呢？其实这⾥正是因为MySQL Query Optimizer选择了⼀个有序的索引来进⾏访问表中的数据（idx_group_message_gid_uid），这样，我们通过group_id的条件得到的数据已经是按照group_id和user_id进⾏排序的了。⽽虽然我们的排序条件仅仅只有⼀个user_id，但是我们的WHERE条件决定了返回数据的
group_id全部⼀样，也就是说不管有没有根据group_id来进⾏排序，返回的结果集都是完全⼀样的。
我们可以通过如下的图⽰来描述整个执⾏过程：
图中的TableA和TableB分别为上⾯Query中的group_message和gruop_message_content这两个表。
这种利⽤索引实现数据排序的⽅法是MySQL中实现结果集排序的最佳做法，可以完全避免因为排序计算所带来的资源消耗。所以，在我们优化Query语句中的ORDERBY的时候，尽可能利⽤已有的索引来避免实际的排序计算，可以很⼤幅度的提升ORDERBY操作的性能。在有些Query的优化过程中，即使为了避免实际的排序操作⽽调整索引字段的顺序，甚⾄是增加索引字段也是值得的。当然，在调整索引之前，同时还需要评估调整该索引对其他Query所带来的影响，平衡整体得失。
如果没有索引利⽤的时候，MySQL⼜如何来实现排序呢？这时候MySQL⽆法避免需要通过相关的排序算法来将存储引擎返回的数据进⾏排序运算了。下⾯我们再针对这种实现⽅式进⾏相应的分析。
在MySQL第⼆种排序实现⽅式中，必须进⾏相应的排序算法来实现数据的排序。MySQL⽬前可以通过两种算法来实现数据的排序操作。
取出满⾜过滤条件的⽤于排序条件的字段以及可以直接定位到⾏数据的⾏指针信息，在Sort Buffer中进⾏实际的排序操作，然后利⽤排好序之后的数据根据⾏指针信息返回表中取得客户端请求的其他字段的数据，再返回给客户端；
根据过滤条件⼀次取出排序字段以及客户端请求的所有其他字段的数据，并将不需要排序的字段存放
在⼀块内存区域中，然后在SortBuffer中将排序字段和⾏指针信息进⾏排序，最后再利⽤排序后的⾏指针与存放在内存区域中和其他字段⼀起的⾏指针信息进⾏匹配合并结果集，再按照顺序返回给客户端。
上⾯第⼀种排序算法是MySQL⼀直以来就有的排序算法，⽽第⼆种则是从MySQL4.1版本才开始增加的改进版排序算法。第⼆种算法与第⼀种相⽐较，主要优势就是减少了数据的⼆次访问。在排序之后不需要再⼀次回到表中取数据，节省了IO操作。当然，第⼆种算法会消耗更多的内存，正是⼀种典型的通过内存空间换取时间的优化⽅式。下⾯我们同样通过⼀个实例来看看当MySQL不得不使⽤排序算法的时候的执⾏计划，仅仅只是更改⼀下排序字段：
sky@localhost : example 10:09:06> explain
-> select m.id,m.t
-> FROM group_message m,group_message_content c
-> up_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: id
rows: 11
Extra:
⼤概⼀看，好像整个执⾏计划并没有什么区别啊？但是细⼼的读者朋友可能已经发现，在group_message表的Extra信息中，多了⼀
distinct查询个“Using filesort”的信息，实际上这就是MySQL Query Optimizer在告诉我们，他需要进⾏排序操作才能按照客户端的要求返回有序的数据。执⾏图⽰如下：
这⾥我们看到了，MySQL在取得第⼀个表的数据之后，先根据排序条件将数据进⾏了⼀次filesort，也就是排序操作。然后再利⽤排序后的结果集作为驱动结果集来通过Nested Loop Join访问第⼆个表。当然，⼤家不要误解，这个filesort并不是说通过磁盘⽂件进⾏排序，仅仅只是告诉我们进⾏了⼀个排序操作。
上⾯，我们看到了排序结果集来源仅仅只是单个表的⽐较简单的filesort操作。⽽在我们实际应⽤中，很多时候我们的业务要求可能并不是这样，可能需要排序的字段同时存在于两个表中，或者MySQL在经过⼀次Join之后才进⾏排序操作。这样的排序在MySQL中并不能简单的⾥利⽤Sort Buffer进⾏排序，⽽是必须先通过⼀个临时表将之前Join的结果集存放⼊临时表之后在将临时表的数据取到Sort Buffer中进⾏操作。下⾯我们通过再次更改排序要求来⽰例这样的执⾏计划，当我们选择通过group_message_content表上⾯的content字段来进⾏排序之后：
sky@localhost : example 10:22:42> explain
-> select m.id,m.t
-> FROM group_message m,group_message_content c
-> up_id = 1 AND m.id = c.group_msg_id
-> ORDER t\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: id
rows: 11
Extra:
这时候的执⾏计划中出现了“Using temporary”，正是因为我们的排序操作需要在两个表Join之后才能进⾏，下图展⽰了这个Query的执⾏过程：
⾸先是TableA和TableB进⾏Join，然后结果集进⼊临时表，再进⾏filesort，最后得到有序的结果集数据返回给客户端。
上⾯我们通过两个不同的⽰例展⽰了当MySQL⽆法避免要使⽤相应的排序算法进⾏排序操作的时候的实现原理。虽然在排序过程中所使⽤的排序算法有两种，但是两种排序的内部实现机制⼤体上差不多。
当我们⽆法避免排序操作的时候，我们⼜该如何来优化呢？很显然，我们应该尽可能让MySQL选择使⽤第⼆种算法来进⾏排序。这样可以减少⼤量的随机IO操作，很⼤幅度的提⾼排序⼯作的效率。
1.加⼤max_length_for_sort_data参数的设置；
在MySQL中，决定使⽤第⼀种⽼式的排序算法还是新的改进算法的依据是通过参数max_length_for_s
ort_data来决定的。当我们所有返回字段的最⼤长度⼩于这个参数值的时候，MySQL就会选择改进后的排序算法，反之，则选择⽼式的算法。所以，如果我们有充⾜的内存让MySQL存放需要返回的⾮排序字段的时候，可以加⼤这个参数的值来让MySQL选择使⽤改进版的排序算法。
2. 去掉不必要的返回字段；
当我们的内存并不是很充裕的时候，我们不能简单的通过强⾏加⼤上⾯的参数来强迫MySQL去使⽤改进版的排序算法，因为如果那样可能会造成MySQL不得不将数据分成很多段然后进⾏排使⽤序，这样的结果可能会得不偿失。在这种情况下，我们就需要去掉不必要的返回字段，让我们的返回结果长度适应max_length_for_sort_data参数的限制。
3.增⼤sort_buffer_size参数设置；
增⼤sort_buffer_size并不是为了让MySQL可以选择改进版的排序算法，⽽是为了让MySQL可以尽量减少在排序过程中对需要排序的数据进⾏分段，因为这样会造成MySQL不得不使⽤临时表来进⾏交换排序。
GROUP BY 的实现与优化
由于GROUP BY实际上也同样需要进⾏排序操作，⽽且与ORDER BY相⽐，GROUP BY主要只是多
了排序之后的分组操作。当然，如果在分组的时候还使⽤了其他的⼀些聚合函数，那么还需要⼀些聚合函数的计算。所以，在GROUP BY的实现过程中，与ORDERBY⼀样也可以利⽤到索引。
在MySQL中，GROUP BY的实现同样有多种（三种）⽅式，其中有两种⽅式会利⽤现有的索引信息来完成GROUP BY，另外⼀种为完全⽆法使⽤索引的场景下使⽤。下⾯我们分别针对这三种实现⽅式做⼀个分析。
1. 使⽤松散（Loose）索引扫描实现GROUP BY
何谓松散索引扫描实现GROUP BY呢？实际上就是当MySQL完全利⽤索引扫描来实现GROUP BY的时候，并不需要扫描所有满⾜条件的索引键即可完成操作得出结果。
下⾯我们通过⼀个⽰例来描述松散索引扫描实现GROUP BY，在⽰例之前我们需要⾸先调整⼀下group_message表的索引，将gmt_create 字段添加到group_id和user_id字段的索引中：
sky@localhost : example 08:49:45> create index idx_gid_uid_gc
-> on group_message(group_id,user_id,gmt_create);
Query OK, rows affected (0.03 sec)
Records: 96 Duplicates: 0 Warnings: 0
sky@localhost : example 09:07:30> drop index idx_group_message_gid_uid
-> on group_message;
Query OK, 96 rows affected (0.02 sec)
Records: 96 Duplicates: 0 Warnings: 0
然后再看如下Query的执⾏计划：
sky@localhost : example 09:26:15> EXPLAIN
-> SELECT user_id,max(gmt_create)
-> FROM group_message
-> WHERE group_id < 10
-> GROUP BY group_id,user_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: 8
ref: NULL
rows: 4
Extra: Using where; Using index for group-by
1 row in set (0.00 sec)
我们看到在执⾏计划的Extra信息中有信息显⽰“Using index for group-by”，实际上这就是告诉我们，MySQLQueryOptimizer通过使⽤松散索引扫描来实现了我们所需要的GROUP BY操作。
下⾯这张图⽚描绘了扫描过程的⼤概实现：要利⽤到松散索引扫描实现GROUP BY，需要⾄少满⾜以下⼏个条件：
GROUP BY 条件字段必须在同⼀个索引中最前⾯的连续位置；
在使⽤GROUP BY的同时，只能使⽤MAX和MIN这两个聚合函数；
如果引⽤到了该索引中GROUP BY条件之外的字段条件的时候，必须以常量形式存在；
为什么松散索引扫描的效率会很⾼？
因为在没有WHERE⼦句，也就是必须经过全索引扫描的时候，松散索引扫描需要读取的键值数量与分组的组数量⼀样多，也就是说⽐实际存在的键值数⽬要少很多。⽽在WHERE⼦句包含范围判断式或者等值表达式的时候，松散索引扫描查满⾜范围条件的每个组的第1个关键字，并且再次读取尽可能最少数量的关键字。
2. 使⽤紧凑（Tight）索引扫描实现GROUP BY
紧凑索引扫描实现GROUP BY和松散索引扫描的区别主要在于他需要在扫描索引的时候，读取所有满⾜条件的索引键，然后再根据读取恶的数据来完成GROUP BY操作得到相应结果。
sky@localhost : example 08:55:14> EXPLAIN
-> SELECT max(gmt_create)
-> FROM group_message
-> WHERE group_id = 2
-> GROUP BY user_id\G
*************************** 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
1 row in set (0.01 sec)
这时候的执⾏计划的Extra信息中已经没有“Using index for group-by”了，但并不是说MySQL的GROUP BY操作并不是通过索引完成的，只不过是需要访问WHERE条件所限定的所有索引键信息之后才能得出结果。这就是通过紧凑索引扫描来实现GROUP BY的执⾏计划输出信息。
下⾯这张图⽚展⽰了⼤概的整个执⾏过程：
在MySQL中，MySQL Query Optimizer⾸先会选择尝试通过松散索引扫描来实现GROUP BY操作，当发现某些情况⽆法满⾜松散索引扫描实现GROUP BY的要求之后，才会尝试通过紧凑索引扫描来实现。
当GROUP BY条件字段并不连续或者不是索引前缀部分的时候，MySQL Query Optimizer⽆法使⽤松散索引扫描，设置⽆法直接通过索引完成GROUP BY操作，因为缺失的索引键信息⽆法得到。但是，如果Query语句中存在⼀个常量值来引⽤缺失的索引键，则可以使⽤紧凑索引扫描完成GROUP BY操作，因为常量填充了搜索关键字中的“差距”，可以形成完整的索引前缀。这些索引前缀可以⽤于索引查。⽽如果需要排序GROUP BY结果，并且能够形成索引前缀的搜索关键字，MySQL还可以避免额外的排序操作，因为使⽤有顺序的索引的前缀进⾏搜索已经按顺序检索到了所有关键字。3. 使⽤临时表实现GROUP BY
MySQL在进⾏GROUP BY操作的时候要想利⽤所有，必须满⾜GROUP BY的字段必须同时存放于同⼀个索引中，且该索引是⼀个有序索引（如Hash索引就不能满⾜要求）。⽽且，并不只是如此，是否能够利⽤索引来实现GROUP BY还与使⽤的聚合函数也有关系。
前⾯两种GROUP BY的实现⽅式都是在有可以利⽤的索引的时候使⽤的，当MySQL Query Optimizer⽆法到合适的索引可以利⽤的时候，就不得不先读取需要的数据，然后通过临时表来完成GROUP BY操作。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。