mysql GROUP BY、DISTINCT、ORDER BY语句优化

GROUP BY、DISTINCT、ORDERBY这几类子句比较类似，GROUP BY默认也是要进行ORDERBY排序的，笔者在本书中 把它们归为一类，优化的思路也是类似的。

可以考虑的优化方式如下。

1、尽量对较少的行进行排序。

2、如果连接了多张表，ORDERBY的列应该属于连接顺序的第一张表。

3、利用索引排序，如果不能利用索引排序，那么EXPLAIN查询语句将会看到有filesort。

4、GROUP BY、ORDERBY语句参考的列应该尽量在一个表中，如果不在同一个表中，那么可以考虑冗余一些列，或者合并表。

5、需要保证索引列和ORDERBY的列相同，且各列均按相同的方向进行排序。

6、增加sort_buffer_size。 sort_buffer_size是为每个排序线程分配的缓冲区的大小。增加该值可以加快ORDERBY或GROUP BY *** 作。但是，这是为每 个客户端分配的缓冲区，因此不要将全局变量设置为较大的值，因为每个需要排序的连接都会分配sort_buffer_size大小的内存。

7、增加read_rnd_buffer_size。 当按照排序后的顺序读取行时，通过该缓冲区读取行，从而避免搜索硬盘。将该变量设置为较大的值可以大大改进ORDER BY的性能。但是，这是为每个客户端分配的缓冲区，因此你不应将全局变量设置为较大的值。相反，只用为需要运行大查询 的客户端更改会话变量即可。

8、改变tmpdir变量指向基于内存的文件系统或其他更快的磁盘。 如果MySQL服务器正作为复制从服务器被使用，那么不应将“--tmpdir”设置为指向基于内存的文件系统的目录，或者当服务 器主机重启时将要被清空的目录。因为，对于复制从服务器，需要在机器重启时仍然保留一些临时文件，以便能够复制临时表 或执行LOADDATAINFILE *** 作。如果在服务器重启时丢失了临时文件目录下的文件，那么复制将会失败。

9、指定ORDERBY NULL。 默认情况下，MySQL将排序所有GROUP BY的查询，如果想要避免排序结果所产生的消耗，可以指定ORDERBY NULL。 例如：SELECT count(*) cnt, cluster_id FROM stat GROUP BY cluster_id ORDER BY NULL LIMIT 10·

10、优化GROUP BY WITHROLLUP。 GROUP BY WITHROLLUP可以方便地获得整体分组的聚合信息（superaggregation），但如果存在性能问题，可以考虑在应用层实现这个功能，这样往往会更高效，伸缩性也更佳。

11、使用非GROUP BY的列来代替GROUP BY的列。 比如，原来是“GROUP BYxx_name,yy_name”，如果GROUP BYxx_id可以得到一样的结果，那么使用GROUP BYxx_id也是可 行的。

12、可以考虑使用Sphinx等产品来优化GROUP BY语句，一般来说，它可以有更好的可扩展性和更佳的性能。

首先直接结论：

1. 如果对 group by 语句的结果没有排序要求，要在语句后面加 order by null；

2. 尽量让 group by 过程用上表的索引，确认方法是 explain 结果里没有 Using

temporary 和 Using filesort；

3. 如果 group by 需要统计的数据量不大，尽量只使用内存临时表；也可以通过适当调大

tmp_table_size 参数，来避免用到磁盘临时表；

4. 如果数据量实在太大，使用 SQL_BIG_RESULT 这个提示，来告诉优化器直接使用排序

算法得到 group by 的结果。

group by 执行流程：

select id%10 as m, count(*) as c from t1 group by m 

上面sql语句的执行流程如下：

1. 创建内存临时表，表里有两个字段 m 和 c，主键是 m；

2. 扫描表 t1 的索引 a，依次取出叶子节点上的 id 值，计算 id%10 的结果，记为 x；

    如果临时表中没有主键为 x 的行，就插入一个记录 (x,1)

    如果表中有主键为 x 的行，就将 x 这一行的 c 值加 1；

3. 遍历完成后，再根据字段 m 做排序，得到结果集返回给客户端。（为什么有的个结论的目的）。

SQL 语句末尾增加 order by null，这样就跳过了最后排序的阶段，直接从临时表中取数据返回。

参数 tmp_table_size 就是控制这个内存临时表大小的，默认是 16M。

如果执行中存放的数据超过内存临时表的上限，这个时候就会把内存临时表转为磁盘临时表。

不论是使用内存临时表还是磁盘临时表，group by 逻辑都需要构造一个带唯

一索引的表，执行代价都是比较高的。我们有什么优化的方法呢？

group by field    //给field加索引

在 group by 语句中加入 SQL_BIG_RESULT 这个提示（hint），就可以告诉优化器：这个

语句涉及的数据量很大，请直接用磁盘临时表。

MySQL 的优化器一看，磁盘临时表是 B+ 树存储，存储效率不如数组来得高。所以，既

然你告诉我数据量很大，那从磁盘空间考虑，还是直接用数组来存吧。

select SQL_BIG_RESULT id%100 as m, count(*) as c from t1 group by m

这个语句的执行没有再使用临时表，而是直接用了排序算法。

注意：union 跟 union all的一个性能区别是：union涉及到去重，所以，用到了临时表。

---