在数据库场景中，排序是非常常见的需求，也是查询处理中的重要环节。在MySQL中排序实现是基于索引，提供了多种方式来优化排序操作，以提高查询性能。但如果没有合适的索引支持，可能会导致全表扫描，从而影响性能。
先了解下MySQL排序机制：
- 排序的基本流程：
当执行包含 ORDER BY 子句的查询时，MySQL内部处理流程，会经历以下步骤：
- 获取满足 WHERE 条件的数据。
- 将这些数据放入排序缓冲区(sort buffer)。
- 在缓冲区中对数据进行排序。
- 返回排序后的结果。
2.主要使用两种排序算法：
- 内存排序(快速排序)：当数据量小于sort_buffer_size时，直接在内存中快速排序。性能最佳，完全在内存中完成。
- 外部排序(归并排序)：当数据量超过sort_buffer_size时，将数据分成多个块，每块单独排序后写入临时文件。最后通过归并排序合并这些有序临时文件，涉及磁盘I/O，性能较差
3.当排序结合LIMIT使用时，MySQL内部会在找到所需行后立即停止排序，从而节省资源。
优化技术
在排序过程中，如果没有合适的索引，MySQL会使用一种称为File Sort的机制进行排序。 File Sort并不是字面意义上的 “文件排序”，而是一种内存中进行的排序算法。如果数据过大也会使用临时盘。
下面介绍如何进行排序场景下优化：
1.参数
可以允许MySQL在内存中进行更大的排序操作，减少磁盘I/O。调整 sort_buffer_size/tmp_table_size/read_rnd_buffer_size/max_sort_length，提高从磁盘读取排序结果的效率。
2.优化器
在MySQL8.0里optimizer_switch里prefer_ordering_index 参数控制优化器是否优先选择能够避免ORDER BY 或 GROUP BY并带有LIMIT子句的情况的排序情况。关闭后，优化器不再强制依赖排序索引，转而基于成本模型选择最优执行计划。
mysql> explain SELECT * FROM tb_order WHERE id=5 ORDER BY name;
mysql> explain SELECT * FROM tb_order WHERE id=5 ORDER BY name limit 1;
3.optimizer hint
MySQL8.0版本开始支持的优化器连接表的顺序。就是驱动表和被驱动表的选择。
连接hint包含：JOIN_FIXED_ORDER，JOIN_ORDER，JOIN_PREFIX，JOIN_SUFFIX
SELECT
/*+ JOIN_PREFIX(t2, t5@subq2, t4@subq1)
JOIN_ORDER(t4@subq1, t3)
JOIN_SUFFIX(t1) */
COUNT(*) FROM t1 JOIN t2 JOIN t3
WHERE t1.id IN (SELECT /*+ QB_NAME(subq1) */ id FROM t4)
AND t2.id IN (SELECT /*+ QB_NAME(subq2) */ id FROM t5);
4.SQL查询编写技巧
限制排序数据量：
-- 先过滤再排序
SELECT * FROM table
WHERE create_time > '2025-01-01'
ORDER BY create_time
LIMIT 1000;
选择覆盖索引：
-- 只选择必要列
ALTER TABLE table ADD INDEX idx_name_price (name,price)
SELECT i name, price FROM table ORDER BY name;
大数据量分页时先取ID再关联：
SELECT t.* FROM table t
JOIN (SELECT id FROM table ORDER BY col LIMIT 100000, 10) tmp
ON t.id = tmp.id;
优化问题排查
如果碰到排序导致性能瓶颈，通过EXPLAIN 分析检查是否使用了索引。执行计划中，常见的情况Extra列可以识别排序模式：
- Using filesort：表示使用了文件排序，不一定真的使用文件，可能只是内存排序。
- Using index：使用索引的有序性，避免了额外排序。最优的排序方式。
监控排序性能
查看排序操作Status统计，及时发现排序性能问题并采取优化措施。指标如下：
- Sort_merge_passes：归并排序的合并次数(值大表示需要优化)
- Sort_range：范围排序次数
- Sort_rows：已排序的行数
- Sort_scan：全表扫描排序次数
mysql> SHOW GLOBAL STATUS LIKE '%Sort%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Sort_merge_passes | 0 |
| Sort_range | 0 |
| Sort_rows | 467 |
| Sort_scan | 23 |
+-------------------+-------+
4 rows in set (0.00 sec)
动态排序
除了一些常规的排序法，也可以实现动态排序，通过搭配函数或者一些查询条件使用。很大可能索引会失效。如下示例：
#CASE WHEN 动态排序
SELECT * FROM table ORDER BY
CASE WHEN `name` <'D' THEN id END ,
CASE WHEN `id` > 2 THEN name END;
#附加了一个隐藏字段，status=3时返回0，否则返回1，然后再根据返回结果进行升序排序。
SELECT * FROM table ORDER BY IF (`status` = 3, '0', '1');
#函数拼接
SELECT * FROM table ORDER BY CONCAT(`status`, id) ;
总结
通过上述介绍，可以有效地识别和解决MySQL中的排序性能问题，提高查询的效率和响应速度。需要注意的是，不同的优化方法适用于不同的场景，具体效果可能因数据分布、查询模式等因素而异。因此，在实际应用中，建议结合具体情况进行多种方法的尝试和调整。
核心排序优化方向：
- 使用合适的索引进行排序，返回字段少的情况下，可以选择覆盖索引。
- 减少排序的数据量，添加合适的WHERE条件 或 LIMIT减少返回行数。
- 优化查询和表设计。短类型字段排序效果好。
- 调整排序相关参数。
- 避免复杂表多表Join情况下。先排序后取数。
- 避免不必要的排序。
- 多列排序：顺序要与组合索引顺序完全一致才能有效利用索引。
- BLOB/TEXT排序，需要特别处理，可能使用 max_sort_length 截断。
- 引发隐式排序。如：GROUP BY、DISTINCT 等
