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语句，一般来说，它可以有更好的可扩展性和更佳的性能。

只有5种吗？我知道十种以上的说。

索引（没我得全表查询了）

改变数据储引擎（MyISAM没事务再也不用担心锁表了）

增加冗余数来减少连表查询数（消耗硬盘空间减少CPU使用）

调整查询顺序减少查询量优先（数量少了连表的笛卡儿积也少了）

全文索引（文字长度有限制，而且IO使用量会大增，但是妥妥的快）

查询尽量不要用函数（函数可是不走索引的哦亲）

查询变量类型要提前对好减少系统负担（我提前改变了系统你就不用检测了）

升级服务器硬件（没什么是氪金解决不了的）

配置好临时表空间，合理理由临时表减少主表查询抢资源（唯我独查）

合理理由函数减少系统的判断（明明都能确认内容不同你用UNION 系统还是傻傻的查一遍是否重复 UNION  ALL则跳过这个步骤同理 inner join 和 left join 也一样 ）

强制走索引（复合索引的情况有时候手动走比系统判断要好哦）

脏读、幻读等（你堵车我绕路）

数据归档，迁移（没用的数据要进仓哦，别占着主表的资源）

表的碎片整理（迁移后碎片整理更健康哦亲）

索引重构（数据都走了索引也应该重构一下才能保证速度哦）

善用存储过程（串N个表（N大于10）的查询千万别一个SQL到底，分布式查询在吧结果集合并吧骚年）

预处理数据（mysql也有job哦，对于经常要子查询的数据可以先弄个明细表根据主表在后台进行补完，查询的时候就更方便了）

懒得说了。。。。。。。。。。。。。。。。。。

一、MySQL数据库有几个配置选项可以帮助我们及时捕获低效SQL语句

1，slow_query_log

这个参数设置为ON，可以捕获执行时间超过一定数值的SQL语句。

2，long_query_time

当SQL语句执行时间超过此数值时，就会被记录到日志中，建议设置为1或者更短。

3，slow_query_log_file

记录日志的文件名。

4，log_queries_not_using_indexes

这个参数设置为ON，可以捕获到所有未使用索引的SQL语句，尽管这个SQL语句有可能执行得挺快。

二、检测mysql中sql语句的效率的方法

1、通过查询日志

（1）、Windows下开启MySQL慢查询

MySQL在Windows系统中的配置文件一般是是my.ini找到[mysqld]下面加上

代码如下

log-slow-queries = F:/MySQL/log/mysqlslowquery。log

long_query_time = 2

（2）、Linux下启用MySQL慢查询

MySQL在Windows系统中的配置文件一般是是my.cnf找到[mysqld]下面加上

代码如下

log-slow-queries=/data/mysqldata/slowquery。log

long_query_time=2

---