ORDER BY主键触发临时表与文件排序：慢查询原因及优化方案

问题背景 #

在MySQL 5.6.21和MariaDB 10.4.28中执行以下查询时，因排序操作耗时0.5秒，执行计划显示使用了临时表（temporary table）和文件排序（filesort）。将SELECT t1.*改为SELECT *后，会生成20GB+的超大临时文件，导致请求超时。

查询语句 #

SELECT t1.* FROM `data` AS t1 
LEFT JOIN `data_sources` AS t2 ON(t1.source_id = t2.id) 
WHERE t2.period_id = 1 
ORDER BY t1.id ASC;

执行计划（Explain） #

1   SIMPLE  t2  ref PRIMARY,data_sources_period_id  data_sources_period_id          8   const       1   Using where; Using index; Using temporary; Using filesort
    
1   SIMPLE  t1  ref data_source_id_index          data_source_id_index              8   t2.id   40889   NULL    

表结构 #

data表：

CREATE TABLE `data` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `source_id` bigint(20) UNSIGNED NOT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `data`
  ADD PRIMARY KEY (`id`),
  ADD KEY `data_source_id_index` (`source_id`);

data_sources表：

CREATE TABLE `data_sources` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `period_id` bigint(20) UNSIGNED NOT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `data_sources`
  ADD PRIMARY KEY (`id`),
  ADD KEY `data_sources_period_id` (`period_id`) USING BTREE;

ALTER TABLE `data_sources`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

原因分析 #

• 临时表与文件排序触发逻辑：原查询的LEFT JOIN因WHERE t2.period_id = 1的条件，实际等价于INNER JOIN（左连接中不匹配t2的记录会被过滤）。执行计划中先扫描t2表获取符合条件的记录，再关联t1表返回匹配数据，但这些数据的t1.id是无序的，MySQL需要将所有结果存入临时表，再执行文件排序来满足ORDER BY t1.id的要求。
• 超大临时文件产生原因：改为SELECT *后，查询返回t1和t2的所有字段，数据量剧增。当临时表无法在内存中容纳时，会写入磁盘，最终生成超大临时文件，导致IO开销暴增，引发请求超时。

优化方案 #

方案1：调整查询逻辑，利用t1主键有序性 #

通过子查询获取符合条件的source_id，直接扫描t1的主键索引（天然有序），避免临时表与排序：

SELECT t1.* 
FROM `data` AS t1 
WHERE t1.source_id IN (SELECT id FROM `data_sources` WHERE period_id = 1)
ORDER BY t1.id ASC;

方案2：创建联合索引优化关联与排序 #

在t1表上创建(source_id, id)联合索引，让MySQL在关联时直接返回有序的t1.id数据：

ALTER TABLE `data` ADD INDEX idx_source_id_id (`source_id`, `id`);

创建后执行原查询，优化器会利用该联合索引匹配source_id，同时返回的id已满足排序要求，无需临时表和文件排序。

方案3：明确使用INNER JOIN优化执行路径 #

将查询改为明确的INNER JOIN（原查询逻辑已等价于内连接），结合上述联合索引，引导优化器选择更高效的执行顺序：

SELECT t1.* 
FROM `data` AS t1 
INNER JOIN `data_sources` AS t2 ON t1.source_id = t2.id 
WHERE t2.period_id = 1 
ORDER BY t1.id ASC;

内容的提问来源于stack exchange，提问作者root66