MySQL GROUPBY、DISTINCT、ORDERBY 语句优化指南

mysqlGROUPBY、DISTINCT、ORDERBY语句优化

GROUPBY、DISTINCT和ORDERBY子句比较相似，GROUPBY也需要ORDERBY排序。
作者在本书中将它们归为一类，优化思路类似。
可以考虑的优化方法如下。
1.尝试对尽可能少的行进行排序。
2.如果连接多个表，则ORDERBY列应属于连接序列中的第一个表。
3.使用索引排序如果不能使用索引排序，EXPLAIN查询将看到文件。
4.GROUPBY和ORDERBY语句引用的列应尽可能在一张表中。
如果它们不在同一个表中，请考虑冗余列或合并表。
5、需要保证索引列和ORDERBY列相等并且每列排序方向相同。
6.增加sort_buffer_size。
sort_buffer_size是为每个排序线程分配的缓冲区的大小。
增加此值可以加快ORDERBY或GROUPBY操作的速度。
但是，这是为每个客户端分配的缓冲区，因此不要将全局变量设置为较大的值，因为需要排序的每个连接都会分配大小为sort_buffer_size的内存。
7.增加read_rnd_buffer_size。
当按排序顺序读取行时，将通过此缓冲区读取行，从而避免在硬盘上进行搜索。
将此变量设置为较大的值可以显着提高ORDERBY性能。
但是，这是为每个客户端分配的缓冲区，因此不应将全局变量设置为较大的值。
相反，您只需更改需要运行大型搜索的客户端的会话变量。
8.更改tmpdir变量以指向基于内存的文件系统或其他更快的磁盘。
如果MySQL服务器用作复制从机，则“--tmpdir”不应设置为指向内存文件系统上的目录或服务器主机重新启动时将被删除的目录。
因为，对于复制从服务器来说，机器重启时需要保留一些临时文件，以便可以复制临时表或者进行LOADDATAINFILE操作。
如果服务器重启时临时文件目录中的文件丢失，复制将会失败。
9.指定ORDERBYNULL。
默认情况下，MySQL将对所有GROUPBY查询进行排序。
如果您想避免消耗排序结果，可以指定ORDERBYNULL。
例如：SELECTcount(*)cnt,cluster_idFROMstatGROUPBYcluster_idORDERBYNULLLIMIT10;·10优化GROUPBYWITHROLLUP。
GROUPBYWITHROLLUP可以很方便地获取整个组的聚合信息（超级聚合），但如果存在性能问题，可以考虑在应用层实现此功能，这样往往效率更高，并且具有更好的扩展性。
11.使用非GROUPBY列而不是GROUPBY列。
例如，结果是“GROUPBYxx_name,yy_name”如果GROUPBYxx_id可以得到相同的结果，那么使用GROUPBYxx_id也是可以的。
12、可以考虑使用Sphinx等产品来优化GROUPBY语句一般来说，可能会有更好的扩展性和更好的性能。

MySQL怎样处理排序⭐️如何优化需要排序的查询？

MySQL查询中的排序处理和优化至关重要。
orderby和groupby语句都涉及排序字段，根据查询条件和数据库结构，排序过程有所不同。
首先，当查询语句利用二级索引的有序性时，比如对a表的a2列进行排序，如果优化器选择使用a2索引，则查询结果将直接利用索引的有序性，而不需要使用a2索引。
额外的排序开销。
但是，优化器可能会根据情况切换到全表扫描，索引的有序性不再适用。
当索引无法提供排序时，MySQL将使用文件排序方法。
如果查询中排序字段的长度小于max_length_for_sort_data，则所有相关字段都会放入sort_buffer中进行内存排序；如果超过此限制，则仅存储排序字段和主键，排序后将使用主键值来查询表。
当内存不足时，排序会转换为磁盘上的临时文件排序，并使用归并排序算法。
优化器跟踪信息（optimizer_trace）可以显示辅助排序文件的数量和sort_buffer大小。
为了减少磁盘交互，应该优化索引设计以确保排序字段的有效利用。
综上所述，处理排序的关键是优化索引和内存使用。
通过合理的索引设计，可以避免或减少文件排序的使用，提高查询效率。
在处理大规模数据时，需要仔细调整sort_buffer和相关变量设置，以平衡内存和磁盘使用。
最后，本文内容已纳入《从点到线，从线到面，构建MySQL知识体系》系列专栏。
感兴趣的朋友可以继续关注。
文章注释和案例也可以在gitee-StudyJava和github-StudyJava找到。
如果需要交流，欢迎在评论区提问。
点赞、关注、收藏都是对作者的支持。
谢谢你！关注公众号“菜菜的后台私房菜”，获取更多技术分享。