MySQL中order by的工作原理

1、全字段排序

假设有查询语句：select city, name, age from t where city=‘杭州’ order by name limit 1000，且city上建立索引。

MySQL为order by排序开辟的内存称为sort_buffer，如果要排序的数据量小于这块内存，则在内存中进行排序。

在内存中排序过程：
①初始化sort_buffer，放入name, city, age三个字段；
②从索引city找到第一个满足city='杭州’条件的主键id；
③从主键id索引取出整行，并取得name, city, age三个字段的值，存入sort_buffer中；
④从索引city取下一个满足条件的主键id；
⑤重复③和④的操作，直到不满足条件为止；
⑥对sort_buffer中的数据按照name做快速排序；
⑦取排序结果前1000行返回给客户端。

如果要排序的数据量大于排序内存，则需要使用磁盘临时文件进行辅助排序，也就是普通的外部排序。先把所有的数据分成若干份，每份排序后放入磁盘临时文件，最后再对所有的有序临时文件进行归并排序，合成一个有序的大文件。

2、rowid排序

如果查询要返回的字段很多，那么sort_buffer里面要放的字段数会比较多，这样内存中能够放下的行数就会很少，就可能需要分成很多临时文件来辅助排序，导致性能下降。

对于单行太长的情况，MySQL将使用rowid排序。MySQL中用于设置待排序的行数据的长度的参数是max_length_for_sort_data，如果大于这个值，则转而使用rowid排序。

排序过程：
①初始化sort_buffer，只放入两个字段，name和主键id；
②从索引city找到第一个满足city=‘杭州’的主键id；
③从主键索引上取出整行，并取出name和主键id，放入sort_buffer；
④从索引city上取得下一个满足条件的主键id；
⑤重复③和④，直到不满足条件为止；
⑥对sort_buffer中的数据安装name进行排序；
⑦排序完成后的结果取前1000行，再拿着这1000行的主键id回到原表取得city, name, age三个字段值，依次返回给客户端。

可以看出，这种排序方式需要做额外的回表取数据操作。

注意：MySQL实在担心排序内存不够的时候，才会采用rowid排序，这样排序过程中一次可以排序很多行，但是需要回表取数据；如果内存足够，还是会选择全字段排序，即把所要查询的字段全部读入sort_buffer中，排完序直接返回。因此，rowid排序并不会被优先选择。

3、优化order by

合理地利用索引，将可以做到执行order by的时候，竟然可以不用排序，因为索引上的数据已经做好了排序。

联合索引：
如果对city, name建立联合索引，则对于所有city=‘杭州’的索引，name部分是有序的。此时，排序过程变成：
①从‘city, name’索引上取得一个满足city=‘杭州’的主键id；
②到主键索引上取得整行，取city, name, age三个字段值，返回给客户端；
③在‘city, name’索引上取下一个满足条件的主键id；
④重复②和③，直到第1000条记录，或者不满足条件为止。

覆盖索引：
使用‘city, name’的联合索引，每次还是需要再回表取数据，如果再激进一点，不想回表的话，就需要使用覆盖索引，即对’city, name, age’建立联合索引。此时就就可以在索引树上，直接取得所有需要的信息。需要注意的是，索引是有维护代价的，并不是每次都会为了查询而建立联合覆盖索引，需要在两者间进行权衡。

4、如何随机选取三个字段值

方法一：使用order by rand() limit 3
假设单词表有两个字段（主键id和word），现在我们要从单词表中随机地取出三个英语单词。如果使用order by rand()这种方式，需要using temporary和using filesort。将表中每行的word字段值读入内存临时表，同时对word值调用rand()函数生成一个(0, 1)的随机小数，作为该临时表的第二个字段R，然后要按照字段R进行rowid排序。之所以选择时rowid排序，因为此时使用的是内存临时表，回表过程很快，只需要直接访问内存来获取数据，现在优化器会优先考虑用于排序的行越短越好，所以选择rowid排序。接下来，要从内存临时表中取出随机数字段和位置信息（可以把内存临时表看作是数组，位置就是数组的索引）放入sort_buffer中进行排序，排序完成后还要回内存临时表取出所要查询的字段，返回给客户端。

可以看到，使用这种方式查询的代价比较大，因此要尽量避开。

方法二：随机取某1行，取3次
①取整个表的总行数，假设为N；
②取Y1=floor(N*rand())，floor()表示取整；
③再用limit Y1,1取得一行；
④重复②和③，取再取两行Y2和Y3。
扫描的总行数=N+(Y1+1)+(Y2+1)+(Y3+1)。执行代价小于方法一。

方法三：基于方法二的优化
①取整个表的总行数，假设为N；
②取Y1=floor(Nrand())，Y2=floor(Nrand())，Y3=floor(N*rand())，floor()表示取整；
③取Y1、Y2、Y3中最大的数为M，最小的数为N，剩下的数为T；
④执行select * from t limit N, M-N+1；
⑤取出M-N+1行，此时，取出的第一行就是N+1行，第(T-N+1)行就是第T+1行，最后一个为M+1行。
扫描的总行数=N+(M+1)，扫描行数少于方法二。