mysql group by 不走range mysql group by order by

转载

mob64ca1410eb61 2024-04-29 19:32:46

文章标签 mysql 字段数据 文章分类 MySQL 数据库

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

order by的实现与优化

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

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

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

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

[sql]

1. 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

[plain]

1. *************************** 1. row ***************************  
2. id: 1  
3. select_type: SIMPLE  
4. table: m  
5. type: ref  
6. possible_keys: PRIMARY,idx_group_message_gid_uid  
7. key: idx_group_message_gid_uid  
8. key_len: 4  
9. ref: const  
10. rows: 4  
11. Extra: Using where  
12. *************************** 2. row ***************************  
13. id: 1  
14. select_type: SIMPLE  
15. table: c  
16. type: ref  
17. possible_keys: group_message_content_msg_id  
18. key: group_message_content_msg_id  
19. key_len: 4  
20. ref: m.id  
21. rows: 11  
22. 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]

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

[plain]

1. *************************** 1. row ***************************  
2. id: 1  
3. select_type: SIMPLE  
4. table: m  
5. type: ref  
6. possible_keys: PRIMARY,idx_group_message_gid_uid  
7. key: idx_group_message_gid_uid  
8. key_len: 4  
9. ref: const  
10. rows: 4  
11. Extra: Using where; Using filesort  
12. *************************** 2. row ***************************  
13. id: 1  
14. select_type: SIMPLE  
15. table: c  
16. type: ref  
17. possible_keys: group_message_content_msg_id  
18. key: group_message_content_msg_id  
19. key_len: 4  
20. ref: m.id  
21. rows: 11  
22. Extra:

从执行计划里可以看出，在对group_message进行数据访问的时候，extra信息里显示Using filesort，这就表示从group_message获取数据后需要对数据进行排序操作，然后再利用排序后的结果集来驱动第二个表。

对于更复杂的情况，比如用于排序的字段存在在多个表中，或者在排序之前要先经过join操作，这样mysql必须先把join的结果集放入一个临时表，之后再把临时表中的数据取到sort buffer里进行排序。下面再用一个简单的实例来分析optimizer给出的执行计划。

[sql]

1. explain select m.id,m.subject,c.content FROM
2. WHERE m.group_id = 1 AND m.id = c.group_msg_id ORDER BY

给出的执行计划：

[plain]

1. *************************** 1. row ***************************  
2. id: 1  
3. select_type: SIMPLE  
4. table: m  
5. type: ref  
6. possible_keys: PRIMARY,idx_group_message_gid_uid  
7. key: idx_group_message_gid_uid  
8. key_len: 4  
9. ref: const  
10. rows: 4  
11. Extra: Using temporary; Using filesort  
12. *************************** 2. row ***************************  
13. id: 1  
14. select_type: SIMPLE  
15. table: c  
16. type: ref  
17. possible_keys: group_message_content_msg_id  
18. key: group_message_content_msg_id  
19. key_len: 4  
20. ref: example.m.id  
21. rows: 11  
22. 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]

1. create index idx_gid_uid_gc on
2. drop index idx_group_message_gid_uid on
3. EXPLAIN SELECT user_id,max(gmt_create) FROM group_message WHERE group_id < 10 GROUP BY

得到的执行计划：

[plain]

1. *************************** 1. row ***************************  
2. id: 1  
3. select_type: SIMPLE  
4. table: group_message  
5. type: range  
6. possible_keys: idx_gid_uid_gc  
7. key: idx_gid_uid_gc  
8. key_len: 8  
9. ref: NULL  
10. rows: 4  
11. 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]

EXPLAIN SELECT max(gmt_create) FROM group_message WHERE group_id = 2 GROUP BY

得出的执行计划如下：

[plain]

1. *************************** 1. row ***************************  
2. id: 1  
3. select_type: SIMPLE  
4. table: group_message  
5. type: ref  
6. possible_keys: idx_group_message_gid_uid,idx_gid_uid_gc  
7. key: idx_gid_uid_gc  
8. key_len: 4  
9. ref: const  
10. rows: 4  
11. 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]

EXPLAIN SELECT max(gmt_create) FROM group_message WHERE group_id > 1 and group_id < 10 GROUP BY

得到的执行计划如下：

[plain]

1. *************************** 1. row ***************************  
2. id: 1  
3. select_type: SIMPLE  
4. table: group_message  
5. type: range  
6. possible_keys: idx_group_message_gid_uid,idx_gid_uid_gc  
7. key: idx_gid_uid_gc  
8. key_len: 4  
9. ref: NULL  
10. rows: 32  
11. 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]

EXPLAIN SELECT DISTINCT group_id FROM

得到的执行计划如下：

[plain]

1. *************************** 1. row ***************************  
2. id: 1  
3. SELECT_type: SIMPLE  
4. table: group_message  
5. type: range  
6. possible_keys: NULL  
7. key: idx_gid_uid_gc  
8. key_len: 4  
9. ref: NULL  
10. rows: 10  
11. Extra: Using index for group-by

从extra信息里可以看见Using index for group by，意味着mysql使用了松散索引来完成group by操作，然后取出每组中的第一条数据来完成distinct操作。

2. 使用紧凑索引完成distinct操作：

[sql]

EXPLAIN SELECT DISTINCT user_id FROM group_message where

得到的执行计划如下：

[plain]

1. *************************** 1. row ***************************  
2. id: 1  
3. SELECT_type: SIMPLE  
4. table: group_message  
5. type: ref  
6. possible_keys: idx_gid_uid_gc  
7. key: idx_gid_uid_gc  
8. key_len: 4  
9. ref: const  
10. rows: 4  
11. Extra: Using WHERE; Using index

extra信息显示Using index，说明使用了紧凑索引。mysql让存储引擎扫描group_id=2的所有索引键，得出所有的user_id，因为是联合索引，所以取出的user_id已经是排好序的，对相同的user_id取出一条记录即完成了本次distinct操作。

3. 无法利用索引完成distinct操作：

[sql]

EXPLAIN SELECT DISTINCT user_id FROM group_message WHERE group_id > 1 AND

得到的执行计划如下：

[plain]

1. *************************** 1. row ***************************  
2. id: 1  
3. SELECT_type: SIMPLE  
4. table: group_message  
5. type: range  
6. possible_keys: idx_gid_uid_gc  
7. key: idx_gid_uid_gc  
8. key_len: 4  
9. ref: NULL  
10. rows: 32  
11. Extra: Using WHERE; Using index; Using temporary

从extra信息看出，mysql使用的临时表，但并没有进行排序操作。

4. 同时使用distinct和group by：

[sql]

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

得到的执行计划如下：

[plain]

1. *************************** 1. row ***************************  
2. id: 1  
3. SELECT_type: SIMPLE  
4. table: group_message  
5. type: range  
6. possible_keys: idx_gid_uid_gc  
7. key: idx_gid_uid_gc  
8. key_len: 4  
9. ref: NULL  
10. rows: 32  
11. Extra: Using WHERE; Using index; Using temporary; Using filesort

从extra里看出mysql使用了排序操作，因为进行了group by操作。

因为distinct的实现原理同group by类似，所以优化手段也一样，尽量使用索引，无法使用索引的时候，确保不要在大结果集上进行distinct操作，磁盘上的IO操作和内存中的IO操作性能完全不是一个数量级的差距。

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。