排序优化

对于order by关键字进行优化前，首先大家要先知道索引不仅用于检索还用于排序

MySQL支持两种方式的排序，index和filesort，index效率高，可以根据索引本身来完成排序，filesort效率较低

最好在进行explain进行分析时不要出现filesort

使用index

通过有序索引而直接取得有序的数据，这样就可以不需要进行任何排序操作即可满足客户端要求的有序数据

order by 语句使用索引最左前列
使用where 子句和order by子句组合满足索引最左前列

如果使用多字段排序，需要保证排序方向一致，要么就全是ASC要么就全是DESC

show status like 'sort%';
-- Sort_merge_passes    0
-- Sort_range    1
-- Sort_rows    2
-- Sort_scan    0

常规排序过程

从表中获取满足where条件的记录
对于每条记录，将记录的主键以及排序键(id,order_column)取出放入sort buffer中
如果sort buffer可以存放所有满足条件的行，则进行排序(采用快速排序算法)；如果sort buffer满了后，会排序并写入临时文件(按照sort buffer分组放到多个临时文件中)
如果排序中产生了临时文件，需要利用归并排序算法，保证记录有序
循环上述过程，直到所有记录全部参与排序
扫描排好序的(id,order_column)，并利用id去取select需要返回的字段

使用filesort

在使用explain时如果发现在Extra信息中出现Using filesort，表示使用了filesort排序

需用通过Mysql的排序算法将存储引擎中返回的数据进行排序，然后再将排序后的数据返回给客户端

如果逼不得已使用filesort，MySQL中filesort有两种算法，双路排序和单路排序

双路排序(两次传输排序)常规

4.1之前使用的是双路排序，即需要扫描两次磁盘，读取行指针和order by列，对它们进行排序，然后扫描已经排好序的列表，按照列表中的值重新从列表中读取对应的数据输出（回表），第二次读取数据的时候，是读取的排序后的所有记录，会产生大量的随机IO

取出满足过滤条件的用于排序条件的字段以及可以直接定位到行数据的行指针信息，在Sort Buffer中进行实际的排序操作，然后利用排好序之后的数据根据指针信息返回表中取得客户端请求的其他字段的数据

此时在trace中看到的信息是 <sort_key,rowid>

单路排序(单次传输排序)

4.1之后出现单路排序，从磁盘中查询所有列，按照order by列在buffer进行排序，然后扫描排序后的列表进行输出，减少了数据的二次访问，节省了IO操作

根据过滤条件一次取出排序字段以及客户端请求的所有其他字段的数据，并将不需要排序的字段存放在一块内存区域中，然后在sort Buffer中将排序字段和行指针信息进行排序，最后再利用排序后的行指针与存放在内存区域中的其他字段一起的行指针信息进行匹配合并结果集

但是如果需要返回的列非常多，会占用大量的空间

此时在trace中看到的信息是<sort_key,additional_fields>或<sort_key,packedadditional_fields>

两个重要的配置

sort_buffer_size : 该配置是sort buffer的大小，增大该配置，可以尽量减少在排序过程中对需要排序的数据进行分段，如果进行分段会导致MySQL不得不使用临时表来进行数据交换

查看optimizer trace中num_initial_chunks_spilled_to_disk的值，如果该值过大，可以调大sort_buffer_size配置

查看status中的sort_merge_passes变量的值，如果过大，说明归并排序次数过多，可以调大sort_buffer_size配置
max_length_for_sort_data: 当query的字段大小总和小于max_length_for_sort_data且字段中不包含text或者blob类型时，会使用单路排序
read_rnd_buffer_size 一次顺序io返回的结果大小
max_sort_length 排序时最多取多少字节，优化时可以调小

禁忌

有时候需要排序的字段同时存在于两个表，或者在经过一次join之后才进行排序，此时仅使用sort Buffer已经不能满足要求了，需要使用临时表来将之前join的结果集存放在临时表中，再将临时表的数据提取到sort Buffer中进行操作，此时性能可就很差了，使用explain执行计划可以看到extra字段中为using temporary和using filesort