执行过程

1. 初始化 sort_buffer
2. 根据 sql 语句查询相关数据（查询过程参考MySql 索引）
3. 将查询数据放入 sort_buffer
4. 对 sort_buffer 中的数据按照排序字段做快速排序
5. 返回排序后的数据，如果查询 sql 中有 limit，则截取 limit 的记录数返回（注意：这里是会查询所有符合的条件的记录，在遍历得到符合 limit 范围的数据，如果 limit 的范围很靠后，性能会比较差）

不是所有包含order by的 sql 都需要排序，如果排序字段在索引树中已经时有序的，则不需要排序，直接返回。是否需要排序可以使用explain命令查看

explain SELECT user_id, name, status FROM user_encounter_x WHERE user_id = 123 ORDER BY update_time DESC LIMIT 100;
+----+-------------+------------------+------------+------+---------------+-------------+---------+-------------+------+----------+---------------------------------------+
| id | select_type | table            | partitions | type | possible_keys | key         | key_len | ref         | rows | filtered | Extra                                 |
+----+-------------+------------------+------------+------+---------------+-------------+---------+-------------+------+----------+---------------------------------------+
| 1  | SIMPLE      | user_encounter_x | <null>     | ref  | idx_user_id   | idx_user_id | 10      | const,const | 5208 | 100.0    | Using index condition; Using filesort |
+----+-------------+------------------+------------+------+---------------+-------------+---------+-------------+------+----------+---------------------------------------+

Extra 字段的值有Using filesort表示查询需要排序，MySQL 会为每个线程分配一块内存用来排序（sort_buffer），并且在排序完成后释放。

当查询数据太大，超过sort_buffer_size时，就需要用到临时文件来做归并排序了，涉及到磁盘 IO，性能是非常差的，可以打开optimizer_trace来查看 sql 是否使用到了临时文件来排序。

/* 打开 optimizer_trace，只对当前回话有效 */
SET optimizer_trace='enabled=on';

/* 查询语句 */
SELECT user_id, name, status FROM user_encounter_x WHERE user_id = 123 ORDER BY update_time DESC LIMIT 100;

/* 查看 trace */
SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`\G

找到filesort_summary

"filesort_summary": {
    "rows": 601,
    "examined_rows": 2789,
    "number_of_tmp_files": 0,
    "sort_buffer_size": 30056,
    "sort_mode": "<sort_key, additional_fields>"
}

• number_of_tmp_files是使用到的临时文件个数，MySQL 使用归并排序算法，先在每个文件排序，再整合到一个文件。
• sort_buffer_size就是分配的内存大小，如果查询数据超过sort_buffer_size，number_of_tmp_files就会大于 0，并且数据量越大number_of_tmp_files的值就越大。
• examined_rows是参与排序的行数。

优化方案

1. 利用索引有序性，避免出现 file_sort 的情况

   如果排序字段时索引的一部分，数据就是有序的，不需要再次排序，可以提高查询性能。

   索引创建原则参考《阿里巴巴Java开发手册》- 索引规约5

   如果有order by的场景，请注意利用索引的有序性。order by 最后的字段是组合索引的一部分，并且放在索引组合顺序的最后，避免出现file_sort的情况，影响查询性能。 正例：where a=? and b=? order by c; 索引：a_b_c 反例：索引如果存在范围查询，那么索引有序性无法利用，如：WHERE a>10 ORDER BY b; 索引a_b无法排序。

2. 调整sort_buffer_size大小

   sort_buffer_size时每个排序的 sql 可以申请的最大内存，session 级别的参数，默认值是 1048576（1M）,根据实际情况建议调整到 2097152（2M），太大的话会导致内存占用比较高。

   注意

   注意不要跟innodb_sort_buffer_size混淆，innodb_sort_buffer_size是创建索引时，对数据进行排序的缓冲区大小，在索引创建完成时释放。

3. 先查询主键和排序排序字段，减少单行数据大小，让排序可以在内存进行，得到排好序的结果再通过主键查询完整数据

   mysql 默认是会将所有查询字段都放到 sort_buffer 的，可能会因为数据量过大，无法使用内存排序，而需要使用临时文件。 为了避免这种情况，可以先只查询主键和排序字段，减少行数据大小，这样排序有可能可以在内存就能完成了，但是这样会增加额外的查询，是否有性能上的提升，还需要根据实际情况判断。