5.2.2 sort_merge_passes
MySQL手册中对Sort_merge_passes的描述只有一句话
Sort_merge_passes The number of merge passes that the sort algorithm has had to do. If this value is large, you should consider increasing the value of the sort_buffer_size system variable.
这段话并没有把sort_merge_passes到底是什么，该值比较大时说明了什么，通过什么方式可以缓解这个问题。
我们把上面MySQL的外部排序算法搞清楚了，这个问题就清楚了。
其实sort_merge_passes对应的就是MySQL做归并排序的次数，也就是说，如果sort_merge_passes值比较大，说明sort_buffer和要排序的数据差距越大，我们可以通过增大sort_buffer_size或者让填入sort_buffer_size的键值对更小来缓解sort_merge_passes归并排序的次数。
对应的，我们可以在源码中看到证据。
上述MySQL外部排序的算法中第5到第7步，是通过sql/filesort.cc文件中merge_many_buff()函数来实现，第5步单次归并使用merge_buffers()实现，源码摘录如下：
int merge_many_buff(Sort_param *param, Sort_buffer sort_buffer, Merge_chunk_array chunk_array, size_t *p_num_chunks, IO_CACHE *t_file) { ... for (i=0 ; i < num_chunks - MERGEBUFF * 3 / 2 ; i+= MERGEBUFF) { if (merge_buffers(param, // param from_file, // from_file to_file, // to_file sort_buffer, // sort_buffer last_chunk++, // last_chunk [out] Merge_chunk_array(&chunk_array[i], MERGEBUFF), 0)) // flag goto cleanup; } if (merge_buffers(param, from_file, to_file, sort_buffer, last_chunk++, Merge_chunk_array(&chunk_array[i], num_chunks - i), 0)) break; /* purecov: inspected */ ... }
截取部分merge_buffers()的代码如下，
int merge_buffers(Sort_param *param, IO_CACHE *from_file, IO_CACHE *to_file, Sort_buffer sort_buffer, Merge_chunk *last_chunk, Merge_chunk_array chunk_array, int flag) { ... current_thd->inc_status_sort_merge_passes(); ... }
可以看到：每个merge_buffers()都会增加sort_merge_passes，也就是说每一次对MERGEBUFF (7) 个block归并排序都会让sort_merge_passes加一，sort_merge_passes越多表示排序的数据太多，需要多次merge pass。解决的方案无非就是缩减要排序数据的大小或者增加sort_buffer_size。
打个小广告，在我们的qmonitor中就有sort_merge_pass的性能指标和参数值过大的报警设置。
六、trace 结果解释
说明白了三种排序模式和外部排序的方法，我们回过头来看一下trace的结果。
6.1 是否存在磁盘外部排序
"number_of_tmp_files": 0,
number_of_tmp_files表示有多少个分片，如果number_of_tmp_files不等于0，表示一个sort_buffer_size大小的内存无法保存所有的键值对，也就是说，MySQL在排序中使用到了磁盘来排序。
6.2 是否存在优先队列优化排序
由于我们的这个SQL里面没有对数据进行分页限制，所以filesort_priority_queue_optimization并没有启用
"filesort_priority_queue_optimization": { "usable": false, "cause": "not applicable (no LIMIT)" },
而正常情况下，使用了Limit会启用优先队列的优化。优先队列类似于FIFO先进先出队列。
算法稍微有点改变，以回表排序模式为例。
- sort_buffer_size足够大
如果LIMIT 限制返回N条数据，并且N条数据比sort_buffer_size小，那么MySQL会把sort buffer作为priority queue，在第二步插入priority queue时会按序插入队列；在第三步，队列满了以后，并不会写入外部磁盘文件，而是直接淘汰最尾端的一条数据，直到所有的数据都正常读取完成。
算法如下：
- 根据索引或者全表扫描，按照过滤条件获得需要查询的数据
- 将要排序的列值和row ID组成键值对，按序存入中priority queue中
- 如果priority queue满了，直接淘汰最尾端记录。
- 重复上述步骤，直到所有的行数据都正常读取了完成
- 最后一轮循环，仅将row ID写入到结果文件中
- 根据结果文件中的row ID按序读取用户需要返回的数据。为了进一步优化性能，MySQL会读一批row ID，并将读到的数据按排序字段要求插入缓存区中(内存大小read_rnd_buffer_size)。
- sort_buffer_size不够大
N条数据比sort_buffer_size大的情况下，MySQL无法直接利用sort buffer作为priority queue，正常的文件外部排序还是一样的，只是在最后返回结果时，只根据N个row ID将数据返回出来。具体的算法我们就不列举了。
这里MySQL到底是否选择priority queue是在sql/filesort.cc的check_if_pq_applicable()函数中确定的，具体的代码细节这里就不展开了。
另外，我们也没有讨论limit m,n的情况，如果是Limit m,n， 上面对应的“N个row ID”就是“M+N个row ID”了，MySQL的limit m,n 其实是取m+n行数据，最后把M条数据丢掉。
从上面我们也可以看到sort_buffer_size足够大对limit数据比较小的情况，优化效果是很明显的。
七、MySQL其他相关排序参数
7.1 max_sort_length
这里需要区别max_sort_length 和max_length_for_sort_data。
max_length_for_sort_data是为了让MySQL选择”< sort_key, rowid >”还是”< sort_key, additional_fields >”的模式。
而max_sort_length是键值对的大小无法确定时（比如用户要查询的数据包含了 SUBSTRING_INDEX(col1, ‘.’,2)）MySQL会对每个键值对分配max_sort_length个字节的内存，这样导致内存空间浪费，磁盘外部排序次数过多。
7.2 innodb_disable_sort_file_cache
innodb_disable_sort_file_cache设置为ON的话，表示在排序中生成的临时文件不会用到文件系统的缓存，类似于O_DIRECT打开文件。
7.3 innodb_sort_buffer_size
这个参数其实跟我们这里讨论的SQL排序没有什么关系。innodb_sort_buffer_size设置的是在创建InnoDB 索引时，使用到的sort buffer的大小。
以前写死为1M，现在开放出来，允许用户自定义设置这个参数了。
八、MySQL排序优化总结
最后整理一下优化MySQL排序的手段
- 排序和查询的字段尽量少。只查询你用到的字段，不要使用select * ；使用limit查询必要的行数据；
- 要排序或者查询的字段，尽量不要用不确定字符函数，避免MySQL直接分配max_sort_length，导致sort buffer空间不足；
- 使用索引来优化或者避免排序；
- 增加sort_buffer_size大小，避免磁盘排序；
- 不得不使用original 排序算法时，增加read_rnd_buffer_size；
- 字段长度定义合适就好（避免过长）；
- tmpdir建议独立存放，放在高速存储设备上。
写到这里，大家可以回顾一下文章开头的那八个问题，如果回答不了这些问题，说明其实你没有真正的理解透MySQL的排序，或者说我们的这篇文章写的太乱了~
九、参考文献
https://dev.mysql.com/doc/refman/5.7/en/order-by-optimization.html
