mysql orderby多个_MySQL OrderBy

MySQL会为每个线程分配一个内存(sort_buffer)用于排序，该内存小大为 sort_buffer_size

如果排序的数量小于 sort_buffer_size，排序将会在内存中完成。

如果排序数据量很大，内存中无法存下这么多数据，则会使用磁盘临时文件来辅助排序，也称外部排序

在使用外部排序时，MySQL 会分成好几份单独的临时文件用来存放排序后的数据，然后再将这些文件合并成一个大文件

MySQL 会通过遍历索引将满足条件的数据读到 sort_buffer，并且按照排序字段进行快速排序

如果查询的字段不包含在辅助索引中，需要按照辅助索引记录的主键返回聚集索引所需字段。

该方式会造成随机 IO，在MySQL 5.6提供了 MRR 机制，会将副主索引匹配记录的主键取出来再内存中进行排序，然后再回表

按照情况建立联合索引来避免排序锁带来的性能损耗，允许的情况下也可以建立覆盖索引来避免回表。

全字段排序

通过索引将所需的字段全部读取到 sort_buffer 中

按照排序字段进行排序

将结果返回给客户端

缺点：

造成sort_buffer 中存放不下很多数据，因为除了排序字段还存放其他字段，对 sort_buffer 的利用效率不高

当所需排序数据量很大时，会有很多的临时文件，排序性能也会很差

优点： MySQL 认为内存足够大时会优先选择全字段排序，因为这种方式比 row_id排序避免了一次回表操作

rowid 排序

通过控制排序的行数据的长度来让 sort_buffer 中尽可能多地存放数据，max_length_for_sort_data

只将需要排序的字段和主键读到 sort_buffer 中，并按照排序字段进行排序

按照排序后的顺序，取id 进行回表取出想要获取的数据

将结果集返回给客户端

优点：更好地利用内存的 sort_buffer 进行排序操作，尽量减少对磁盘的访问

缺点：回表的操作时随机 IO，会造成大量的随机读，不一定就比全字段排序减少对磁盘的访问

按照排序的结果返回客户端所取行数

随机取出三行数据的需求

order by rand() 这个语句需要 Using temporary 和 Using filesort，查询的执行代价比较大

使用表的主键 id 的最大值和最小值来做随机算法。

这种方法如果在 id 有空洞的情况下，就不是真正的随机。代价比 1 小很多，因为只使用了 id 字段，没有像1 一样，需要在 order by rand() 方法组成的临时表排序，再获取 rowid 再回表。如果数据库使用了软删除，就可以避免空洞的情况，减少扫描行数。

取出整个表的行数C，随机获取三个随机值y1,y2,y3，limit y,1 得到三个数据。扫描行数 是 总行数c+(y1+1)+(y2+1)+(y3+1)

可以进一步优化：limit Ymin, (Ymax-Ymin), 取出id 后计算出 Y1，Y2，Y3对应的 id ，然后 select in ids，扫描行数减少为 C + ymax +3.

在实际的应用中，应该避免让 mysql 来排序，尽量将业务逻辑写在业务代码中，让数据库只做 “读写数据” 的事情。