MySQL有两种排序方式:通过所以扫描直接返回有序的数据,先返回无序数据,后对数据进行排序(filesort)。
第一种方式:前提条件索引必须是有序的,在查询时,where条件和order by 使用相同的索引,而且order by和索引的顺序一致。否则就会filesort,在SQL中使用索引的情况有:
(1),select *from table_name order by key,
(2),select *from table_name where key=‘demo’ order by key ASC / key DESC;
(2),select *from table_name order by key1 desc ,key2 desc;
其余情况不会使用索引。
第二种方式:filesort,即先查询数据,在对无序数据排序,一般情况需要避免filesort的发生,若无法避免,则优化filesort,使其耗时降低,SQL有两种方式优化filesort。
(1),两次扫描算法,首先根据where条件获取数据和行指针信息,在排序区排序,如果sortbuffer不够,则自动建临时表存储排序的结果,排序完成后,根据指针在排序表中读取有序数据。这种方式在排序时内存消耗较少,但会产生大量的I/O操作。
(2),一次扫描算法,依据where条件一次性取出所有数据,然后在sort buffer中排序后直接返回数据结果。这种方式排序的效率很高,但内存的消耗很大。
sort buffer容量可以通过max_length_for_sort_data调整sort buffer的大小,让MySQL选择合适的方式进行排序,但sort buffer的大小要平衡,如果设置过大会降低计算机的性能,因为sort buffer是单线程独占的。
设置sort buffer大小: