mysql order by 固定_mysql 优化order by

MySQL 中有两种排序方式：

1.通过有序索引扫描直接返回有序数据，这种方式在使用explain分析查询的时候显示为using index,

不需要额外的排序，操作效率较高。

mysql> explain select start_time  from warning_repaired order by start_time desc\G;

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: warning_repaired

type: index

possible_keys: NULL

key: start_time

key_len: 5

ref: NULL

rows: 499471

Extra: Using index

1 row in set (0.00 sec)

2.通过对返回数据进行排序，也就是通常所说的filesort排序，所有不是通过索引直接返回排序结果的排序

都叫filesort排序。 filesort并不代表通过磁盘文件进行排序，而只是进行了一个排序操作，至于排序操作

是否使用了磁盘文件或者临时表等，则取决于MySQL服务器对排序参数的设置和需要排序数据的大小。

mysql> explain select *  from warning_repaired order by start_time desc\G;

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: warning_repaired

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 499471

Extra: Using filesort

1 row in set (0.00 sec)

Filesort是通过相应的排序算法，将取得的数据在sort_buffer_size系统变量设置的内存排序区中进行排序，

如果内存装载不下，它就会将磁盘上的数据进行分块，再对各个数据进行排序，然后将各个块合并成有序的

结果集。sort_buffer_size 设置的排序区是每个线程独占的，所以同一个时刻，MySQL 中存在多个

sort buffer 排序区。

了解了MySQL 排序方式，优化的目标就清楚了：尽量减少额外的排序，通过索引直接返回有序数据。

Where 条件和ORDER BY使用相同的索引，并且ORDER BY 的顺序和索引顺序相同， 并且ORDER BY的字段都是

升序或者都是降序。否则肯定需要额外的排序操作，这样就会出现Filesort.

mysql> explain select domain_moid, start_time  from warning_repaired order by domain_moid desc\G;

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: warning_repaired

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 499471

Extra: Using filesort

1 row in set (0.00 sec)

ERROR:

No query specified

mysql> alter table warning_repaired add index domain_moid_start_time(domain_moid, start_time);

Query OK, 0 rows affected (8.32 sec)

Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select domain_moid, start_time  from warning_repaired order by domain_moid desc\G;

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: warning_repaired

type: index

possible_keys: NULL

key: domain_moid_start_time

key_len: 115

ref: NULL

rows: 499471

Extra: Using index

1 row in set (0.00 sec)

Filesort的优化：

通过创建合适的索引能够减少filesort的出现，但是某些情况下，条件限制不能让Filesort消失，那就需要

想办法加快filesort的操作。对于filesort， MySQL 有两种排序算法。

1.两次扫描算法(two passes): 首先根据条件取出排序字段和行指针信息，之后在排序区Sort buffer 中排序，如果排序区sort bufer 不够，则在临时表Temporary Table中存储排序结果，完成排序后，根据行指针

回表读取数据。该算法是在mysql4.1之前采用的算法，需要两次访问数据，第一次获取排序字段和行指针信息。

第二次根据行指针获取记录，尤其是第二次读取操作可能导致大量随机I/O；优点是排序的时候内存开销小。

2. 一次扫描算法(single Pass):一次性取出满足条件的行的所有字段，然后在排序区sort buffer 中排序后直接

输出结果集。排序的时候内存开销大比较大。但是排序效率比两次扫描算法要高。

MySQL 通过比较系统变量max_length_for_sort_data 的大小和Query 语句取出的字段总大小来判断使用

哪种算法。如果max_length_for_sort_data 更大，那么使用第二种优化之后的算法，否则使用第一种算法。

适当的加大系统变量max_length_for_sort_data的值，能够让MySQL选择更优化的Filesort排序算法。但是，如果

max_length_for_sort_data设置过大，就会造成CPU利用率过低和磁盘I/O过高，CPU和I/O利用平衡就足够了。

适当加大sort_buffer_size排序区，尽量让排序在内存中完成，而不是通过创建临时表放在文件中进行；

当然也不能无限加大sort_buffer_size排序区，因为sort_buffer_size参数是每个线程独占的。设置过大，

会导致服务器SWAP严重，要考虑数据库活动连接数和服务器内存的大小适当设置排序区。

尽量只使用必要的字段，SELECT 具体的字段名称，而不是SELECT * 选择所有字段，

这样可以减少排序区的使用，提高SQL 性能。

学习自《深入浅出mysql》

参考文章：

http://my.oschina.net/wojibuzhu/blog/118035