在 MySQL 高性能开发中ORDER BY排序和内部临时表Internal Temporary Table是两类最消耗 CPU 与 I/O 资源的算子。本文将从执行流程、内存分配策略及物理存储结构三个维度进行技术总结。一、 排序机制FileSort 算法剖析当查询无法利用索引的有序性时MySQL 优化器会启动FileSort。根据单行数据的大小系统会选择不同的排序模式。1. 全字段排序 (Full-row Sort)在sort_buffer空间充足且单行字节数小于max_length_for_sort_data时采用此模式。执行步骤从磁盘读取符合WHERE条件的行。提取SELECT指定的所有列和ORDER BY涉及的列。在sort_buffer中进行排序。排序完成后直接返回结果集。内存代价由于VARCHAR在此阶段会被展开为定义的最大长度容易导致sort_buffer溢出。2. RowID 排序 (Double-pass Sort)当单行数据过大时如包含TEXT或长VARCHAR为节省缓冲区空间而采用。执行步骤仅提取ORDER BY字段和主键 IDRowID放入sort_buffer。排序完成后通过主键 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×41020255 \times 4 1020255×41020字节。2. 影响分析排序效率单行占用的内存越大sort_buffer容纳的行数越少。磁盘归并当行数超出限制会产生Sort_merge_passes即将中间结果写入磁盘临时文件进行归并排序。索引失效即使定义了前缀索引也无法在排序阶段利用索引。