MySQL 执行引擎：排序与临时表机制深度解析

在 MySQL 高性能开发中，ORDER BY（排序）和内部临时表（Internal Temporary Table）是两类最消耗 CPU 与 I/O 资源的算子。本文将从执行流程、内存分配策略及物理存储结构三个维度进行技术总结。

一、 排序机制：FileSort 算法剖析

当查询无法利用索引的有序性时，MySQL 优化器会启动FileSort。根据单行数据的大小，系统会选择不同的排序模式。

1. 全字段排序 (Full-row Sort)

在sort_buffer空间充足且单行字节数小于max_length_for_sort_data时采用此模式。

• 执行步骤：
  1. 从磁盘读取符合WHERE条件的行。
  2. 提取SELECT指定的所有列和ORDER BY涉及的列。
  3. 在sort_buffer中进行排序。
  4. 排序完成后，直接返回结果集。
• 内存代价：由于VARCHAR在此阶段会被展开为定义的最大长度，容易导致sort_buffer溢出。

2. RowID 排序 (Double-pass Sort)

当单行数据过大时（如包含TEXT或长VARCHAR），为节省缓冲区空间而采用。

• 执行步骤：
  1. 仅提取ORDER BY字段和主键 ID（RowID）放入sort_buffer。
  2. 排序完成后，通过主键 ID回表读取SELECT的其他列。
• 权衡：减少了排序阶段的内存压力，但增加了排序后的随机磁盘 I/O 成本。

二、 内部临时表机制

当执行DISTINCT、GROUP BY、UNION或某些多表关联（Join）时，执行引擎需要建立临时表来存储中间计算状态。

1. 存储引擎的演进

• Memory 存储引擎：MySQL 8.0 之前默认使用。所有数据存在内存，不支持BLOB/TEXT字段，且VARCHAR会被转换为定长 CHAR存储。
• TempTable 存储引擎：MySQL 8.0 引入的默认引擎。支持变长字段存储，提高了内存利用率。

2. 内存到磁盘的退化触发

当临时表的大小超过tmp_table_size或max_heap_table_size的阈值时，MySQL 会将内存临时表转换为磁盘临时表（使用InnoDB或MyISAM引擎），此过程涉及大量磁盘写 I/O。

三、 内存分配中的定长处理逻辑

在排序缓冲区或内部临时表中，MySQL 对VARCHAR类型的处理与磁盘存储存在显著差异。

1. VARCHAR 的“膨胀”现象

为了实现O(1)O(1)O(1)的随机访问（Random Access），执行引擎在内存中对VARCHAR(n)采取定长排布。

• 示例：定义VARCHAR(255)，字符集utf8mb4。
• 物理存储：存入"abc"仅占 3 字节 + 长度前缀。
• 排序/临时表内存：分配255×4=1020255 \times 4 = 1020255×4=1020字节。

2. 影响分析

1. 排序效率：单行占用的内存越大，sort_buffer容纳的行数越少。
2. 磁盘归并：当行数超出限制，会产生Sort_merge_passes，即将中间结果写入磁盘临时文件进行归并排序。
3. 索引失效：即使定义了前缀索引，也无法在排序阶段利用索引。