理解 MySQL 排序的工作原理
在 MySQL 的查询执行计划中，ORDER BY 会触发排序操作，这一过程通常成为性能瓶颈的源头。排序成本来自于需要把检索到的多行记录按指定列进行比较、排列，以及在必要时把数据写入临时表。对于没有被索引覆盖的排序，数据库往往需要把中间结果写入磁盘中，这会显著增加 I/O 与 CPU 的使用率。
如果 ORDER BY 的列顺序与现有索引的前缀不匹配，MySQL 可能无法直接利用索引来排序，此时就会触发filesort，并伴随可能的磁盘临时表。理解这一点有助于在后续章节中通过设计合适的复合索引来避免全表排序。
排序成本的来源
常见的成本来源包括：大结果集的排序、缺乏匹配的复合索引、需要对字符集进行排序比较，以及对大字段（如 TEXT）进行排序引发的额外 I/O。通过对查询进行分步分析，可以将成本分解为扫描、排序、以及临时表的阶段。
EXPLAIN 是排查排序成本的关键工具。通过查看 Extra 字段，可以判断是否使用了 Using filesort、Using temporary、以及是否利用了覆盖索引。正确理解这些标记，是后续优化的第一步。
EXPLAIN SELECT id, created_at
FROM orders
WHERE status = 'OPEN'
ORDER BY created_at DESC, id ASC
LIMIT 100;
通过索引优化排序性能
选择合适的索引是排序优化的核心。对于 ORDER BY 的列顺序，推荐建立一个复合索引，左前缀原则必须匹配排序字段的顺序。例如：ORDER BY created_at DESC, id ASC 可以通过复合索引 (created_at, id) 来实现部分排序。注意：MySQL 只能利用一个索引来排序，除非查询覆盖全部排序列并且条件匹配索引前缀。
在创建索引后，通过 EXPLAIN 验证查询是否能够使用该索引进行排序，避免额外的 filesort。下列示例展示了如何通过一个简单的复合索引实现排序的“就地”完成。
CREATE INDEX idx_orders_created_at_id ON orders (created_at, id);
EXPLAIN SELECT id, created_at
FROM orders
WHERE created_at >= '2024-01-01'
ORDER BY created_at DESC, id ASC
LIMIT 100;
覆盖索引与最左前缀原则
覆盖索引（covering index）是指查询只需要从索引中就能取回所需的字段，而不需要回表查找数据行。若你的查询只检索索引中的字段，MySQL 可以通过索引扫描完成整个查询，从而避免额外的行访问和排序开销。最左前缀原则要求排序字段在复合索引中的排列顺序与查询的 ORDER BY 字段顺序一致，才能充分利用索引。
以下示例展示了在覆盖索引存在时，如何通过查询只访问索引列来获得排序结果的效果。
SELECT created_at, id
FROM orders
WHERE created_at >= '2024-01-01'
ORDER BY created_at DESC, id ASC
LIMIT 100;
实战技巧：从慢查询到高效排序的策略
在追求高效排序的过程中，利用覆盖索引避免额外排序、在大数据量表上使用分页排序，以及优化临时表和磁盘 I/O，都是常见且有效的做法。下面从实际操作层面给出可执行的要点。
利用覆盖索引避免排序
如果查询只需要索引中的列，可以避免回表与额外排序，直接从索引中返回结果。尽量让 SELECT 的字段都包含在索引中，并确保 ORDER BY 的字段顺序与索引顺序一致。
SELECT created_at, id
FROM orders
WHERE status = 'OPEN'
ORDER BY created_at DESC, id ASC
LIMIT 50;
在大数据量表上应用分页排序
对于需要分页的排序查询，避免使用大 OFFSET 的分页方式，改用键集分页（Keyset Pagination）可以显著降低排序成本。通过记录上一页的最后一行的排序字段值，继续向后取数据。
SELECT id, created_at
FROM orders
WHERE (created_at, id) > (?, ?)
ORDER BY created_at DESC, id ASC
LIMIT 50;
排序时的临时表和磁盘 IO 优化
当确实需要临时表来完成排序时，提升内存中的临时表比例可以显著降低磁盘 I/O。通过调整内存相关参数，可以减少临时表写入磁盘的次数。
SET GLOBAL tmp_table_size = 67108864; -- 64 MB
SET GLOBAL max_heap_table_size = 67108864; -- 64 MB
MySQL 版本与引擎对排序的影响
不同的版本与存储引擎对排序的实现细节有显著差异。对性能敏感的场景，理解引擎的排序路径可以帮助你更有效地设计索引与查询。
InnoDB 的排序实现
InnoDB 在大多数场景下更倾向于使用覆盖索引和主键回表最小化排序成本。当 ORDER BY 和索引前缀匹配时，Using index 的情况最常见；否则可能走 Using filesort，并伴随临时表的创建。对多列排序，复合索引的作用尤为重要。
EXPLAIN SELECT id, created_at
FROM orders
WHERE status = 'OPEN'
ORDER BY created_at DESC, id ASC
LIMIT 100;
MyISAM 与 InnoDB 的排序差异
MyISAM 在历史上对排序的处理方式与 InnoDB 略有不同，通常在高并发和事务性需求较低的场景下表现不同。InnoDB 的事务性与行级锁在并发性与可预测性方面往往更有优势，且对排序相关的索引设计要求更严格。
实用 SQL 调优案例
通过具体的查询改写，可以将排序成本降到最低。下面给出两个常见场景的实战案例，帮助你快速落地优化。
示例：改写查询以使用索引排序
原查询可能因为缺少合适的覆盖索引而产生大量排序开销。下面的改写通过创建复合索引来提高排序效率。
-- 原查询（慢查询示例）
SELECT * FROM orders
WHERE status = 'OPEN'
ORDER BY created_at DESC, id ASC
LIMIT 100;-- 改写后，利用覆盖索引完成排序
CREATE INDEX idx_orders_status_created_at_id ON orders (status, created_at DESC, id);
SELECT id, created_at, status
FROM orders
WHERE status = 'OPEN'
ORDER BY created_at DESC, id ASC
LIMIT 100;
EXPLAIN SELECT id, created_at
FROM orders
WHERE status = 'OPEN'
ORDER BY created_at DESC, id ASC
LIMIT 100;
示例：避免不必要的排序的查询重写
通过优化筛选条件的位置以及重新构造查询，可以避免触发排序操作。
-- 改写前的查询（可能触发排序）
SELECT id, created_at
FROM orders
WHERE status = 'OPEN'
ORDER BY created_at DESC
LIMIT 100;-- 改写后，利用覆盖索引并限定筛选条件
SELECT id, created_at
FROM orders
WHERE status = 'OPEN' AND created_at >= '2024-01-01'
ORDER BY created_at DESC
LIMIT 100;
