MySql 排序

1. 多条件排序 ：

      根据喜欢数倒序（喜欢数相同时，根据创建时间倒序）查询状态为正常的10条记录。

SELECT * FROM aaa WHERE `status` = 1 ORDER BY love_count DESC, create_time DESC  LIMIT 0,10;

 

*注意：

    如果上述sql写为如此形式呢？

SELECT * FROM aaa WHERE `status` = 1 ORDER BY love_count, create_time DESC  LIMIT 0,10;

    由于默认排序是升序，DESC 关键字仅适用于在它前面的列名( create_time ) ；不影响love_count 列的排序顺序。

    所以，查询结果为：根据创建时间倒序，喜欢数升序，查询状态为正常的10条记录。

 

2. 排序的执行 ：

   分两种情况：

      1) 存在索引，按索引顺序扫描。利用索引进行排序操作是非常快的，而且可以利用同一索引同时进行查找和排序操作；

      2) 没有索引，使用filesort（文件排序），在使用explain语句的时候一般都会在extra中看到filesort。

 

    当MySQL不能使用索引进行排序时，就会利用自己的排序算法(快速排序算法)在内存(sort buffer)中对数据进行排序，如果内存装载不下，它会将磁盘上的数据进行分块，再对各个数据块进行排序，然后将各个块合并成有序的结果集（实际上就是 外排序）。

 

    对于filesort，MySQL有两种排序算法：
      (1)两遍扫描算法(Two passes)，又名双路排序
      实现方式是先将须要排序的字段和可以直接定位到相关行数据的指针信息取出，然后在设定的内存（通过参数sort_buffer_size设定）中进行排序，完成排序之后再次通过行指针信息取出所需的Columns。
      注：该算法是4.1之前采用的算法，它需要两次访问数据，尤其是第二次读取操作会导致大量的随机I/O操作。另一方面，内存开销较小。

      (2)    一次扫描算法(single pass)，又名单路排序
      该算法一次性将所需的Columns全部取出，在内存中排序后直接将结果输出。
      注： 从 MySQL 4.1 版本开始使用该算法。它减少了I/O的次数，效率较高，但是内存开销也较大。如果我们将并不需要的Columns也取出来，就会极大地浪费排序过程所需要 的内存。在 MySQL 4.1 之后的版本中，可以通过设置 max_length_for_sort_data 参数来控制 MySQL 选择第一种排序算法还是第二种。当取出的所有大字段总大小大于 max_length_for_sort_data 的设置时，MySQL 就会选择使用第一种排序算法，反之，则会选择第二种。为了尽可能地提高排序性能，我们自然更希望使用第二种排序算法，所以在 Query 中仅仅取出需要的 Columns 是非常有必要的。

 

    延伸一下：

    当对连接操作进行排序时，如果ORDER BY仅仅引用第一个表的列，MySQL对该表进行filesort操作，然后进行连接处理，此时，EXPLAIN输出“Using filesort”；否则，MySQL必须将查询的结果集生成一个临时表，在连接完成之后进行filesort操作，此时，EXPLAIN输出 “Using temporary;Using filesort”。

 

3. 排序优化：

    如果想要增加ORDER BY 的速度，首先看是否可以让MySQL 使用索引而不是额外的排序阶段。如果不能，可以尝试下面的策略：

·    增加sort_buffer_size 变量的大小。

·    增加read_ rnd_buffer_size 变量的大小。

·    更改tmpdir 指向具有大量空闲空间的专用文件系统。该选项接受几个使用round-robin( 循环) 模式的路径。在Unix 中路径应用冒号( ‘: ’) 区间开，在Windows 、NetWare 和OS/2 中用分号( ‘； ’) 。可以使用该特性将负载均分到几个目录中。注释： 路径应为位于不同物理 硬盘上的文件系统的目录，而不是同一硬盘的不同的分区。

 

    默认情况下，MySQL 排序所有GROUP BY col1 ，col2 ，... 查询的方法如同在查询中指定ORDER BY col1 ，col2 ，... 。如果显式包括一个包含相同的列的ORDER BY 子句，MySQL 可以毫不减速地对它进行优化，尽管仍然进行排序。如果查询包括GROUP BY 但你想要避免排序结果的消耗，你可以指定ORDER BY NULL 禁止排序。例如：

SELECT a, COUNT(*) FROM bar GROUP BY a ORDER BY NULL;

 

 

参考书籍：《Mysql_High_Performance_高性能MySql》、《MySql手册》