orderby优化,解决orderby排序慢

MySQL优化：order by和limit

order by和limit一起使用，避免引起全表扫描和数据排序是非常重要的，因此借助合适的索引提高查询效率。

in的参数个数为1个，联合索引生效，大于1个，索引失效。所以使用了强制索引使联合索引生效。

原因分析：

第一、取决于B树的数据结构，单参数的IN只会得到一颗基于model子树，该子树的code本身是有序的，所以索引生效，查询效率高；多参数的IN会得到多颗基于model的子树，每颗子树的code字段是有序的，但是总体上可能不是有序的，所以索引失效，查询效率低。

第二、使用强制索引后，理论上无法保证order by的顺序，但是如果数据本身的特性，比如时间递增的这类数据，总体上还是有序的，笔者试过多中途径想要迫使强制索引得到错误的结果，结果都对了。强制索引需进一步研究。

此时，通过子查询优化limit，效果如下：

以上数据来自一张超过2000万的MySQL单表，仅供参考，能够说明子查询明显能够提升效率，笔者开始尝试把子查询的order by去掉，发现查询效率又提升2倍，但是对比发现数据不正确，explain后发现查询优化器给出的子查询索引并不是id（此表建有多个索引，id是主键，区分度最高），这一点比较困惑。

ps：在sql语句中，limt关键字是最后才用到的。以下条件的出现顺序一般是：where-group by-having-order by-limit

请教关于MYSQL中order by的效率优化

1，索引一般对where后面的字段比较有用。并且频率越高越好用。

2，mysql 多个order by需要重新计算再来排序，oracle这个方面好点，从右往左边计算的。

3，你这里order by是对两个表分别排序的，这里应该是影响的重要原因，你不妨试试用一个表里面的两个order by看性能怎样。

order by优化，避免filesort

当我们在MySQL执行计划中，遇到了 Using filesort ，这就证明MySQL在执行这条语句的时候用到了filesort，而没有使用我们的索引进行排序。所以就需要进行优化。

具体filesort的过程如下：

1、根据表的索引或者全表扫描，读取所有满足条件的记录。

2、对与每一行，存储一对值到缓冲区（排序列，行记录指针），一个是排序的索引列的值，即order by用到的列值，和指向该行数据的行指针，缓冲区的大小为sort_buffer_size大小。

3、当缓冲区满后，运行一个快速排序（qsort）来将缓冲区中数据排序，并将排序完的数据存储到一个临时文件，并保存一个存储块的指针，当然如果缓冲区不满，则不会重建临时文件了。

4、重复以上步骤，直到将所有行读完，并建立相应的有序的临时文件。

5、对块级进行排序，这个类似与归并排序算法，只通过两个临时文件的指针来不断交换数据，最终达到两个文件，都是有序的。

6、重复5直到所有的数据都排序完毕。

7、采取顺序读的方式，将每行数据读入内存，并取出数据传到客户端，这里读取数据时并不是一行一行读，读如缓存大小由read_rnd_buffer_size来指定。

我们的目标就是优化为 Using index

官网文档里边有很多优化的方法，这里就只列举其中讲到的几点。

1.select字段中只包含索引字段，避免包含无关字段。

这样避免了filesort，pk是主键，这个也是可以通过索引查询到了。如果使用*的话，涉及到了回表，这样操作，还不如直接进行filesort。不管怎样，我们日常开发过程中，都应该避免使用*。

2.使用constant查询联合order by

使用了constant查询后，之后对索引进行order by，这样做后大几率会比全表查询效率要好！

3.避免order by条件中一个desc 一个 asc

还有更多细节可以参考 官方文档

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