MySQL中order by的实现和by rand() 优化

有同学上周问了个问题 “MySQL 里面的order by rand()”是怎么实现的。我们今天来简单说说MySQL里的order by。

　　几种order by的情况

　　乍一看这个问题好像有点复杂，我们从最简单的case开始看起。

　　用这个表来说明：(10w行数据)

　　

　　1、 最简单的order ―― order by索引字段

　　

点击放大

　　从explain的结果来看(Extra列)，这个语句并不作排序。因为字段a已经是有顺序的。就是按照索引a的顺序依次读pk的值(在这里是隐藏的系统列)，一个个从聚簇索引的data中读入。

　　2、 复杂一点 ―― order by 非索引字段

　　

点击放大

　　这里Extra列显示一个Using filesort。这里的filesort并不是指字面上的“文件排序”，说的就是与上面一种情况相比，在Server层作了排序。至于是否使用文件，取决于排序过程中的内存是否足够，不够则需要临时文件。

　　并不到此为止，我们细细想一下，server层要怎么作排序呢?

　　一个简单的想法是把表数据都读到内存，然后排序。读到内存当然可以想怎么整就怎么整。但是这个做法很耗费内存。需要占用与表一样大小的内存。

　　另外一个做法，只读入字段b和其对应的主键id。可以想象为这两个字段构成的结构体，按照b的值作排序。排序完成后，按字段b的顺序依次取主键id，取得结果返回。

　　实际上第二种作法就是这个例子中的实际执行过程。存放用于排序的字段值的结构我们称为sort_keys.

　　至于order by b,c这样的语句，效果与order by b相同，可以简单理解为上面结构体多了一个字段。

　　3、 字段函数排序

　　有了上面的流程，这里就简单了，还是按顺序读入所有的字段b，只是sort_keys中存的是b的长度而已。

　　

点击放大4、 Order by rand()

　　按照自然想法， order by rand() 也可以仿照上面描述的做法，对于每一行，将生成的rand()的值放入sort_kyes里即可。但实际上上效果如下：

　　

点击放大

　　Extra字段里面有一个Using temporary， 也就是说用到了临时表。那么Using temporary的时候操作流程是怎样的呢?

　　a) 创建一个heap引擎的临时表，字段名为 ”” a b c d, 第一个字段为匿名;

　　b) 将表tb中的数据按行读入到临时表中，同时给第一字段填入一个随机实数(0,1);

　　c) 按照第一个字段排序，返回

　　d) 查询完成删除临时表

　　分析一下这个过程,由于把数据从InnoDB表里面读入临时表，则InnoDB表实际上也已经读入内存，在这个过程中，若不考虑内存不够时的写文件策略， 则内存中有两份表的全拷贝;另外多了从内存中将数据一一拷贝到临时表的过程。

　　这个查询在我的测试环境中耗时2.41s(多次次执行，不计第一次加载数据的时间)

　　order by rand()的改进

　　我们前面说过，实际上对于这种简单的order by rand() 的情况，也可以等同于按照非索引字段来处理。在sort_array 中存入随机值即可。

　　按照这个思路的patch在这里，效果上

　　

点击放大

　　执行时间减少为1.89s，性能提升21%, 这个例子单行1k，单行越大提升效果越好。

原文出处：http://www.mysqlops.com/2012/05/02/mysql%e4%b8%adorder-by%e7%9a%84%e5%ae%9e%e7%8e%b0-%e5%92%8c-by-rand-%e5%92%8c%e4%bc%98%e5%8c%96.html