SQL子查询在执行过程中，数据库引擎需要临时存储中间结果来完成后续的计算和关联操作，当内存空间不足以容纳这些中间结果时，就会将数据写入磁盘生成临时文件。而内存缓冲区的配置和排序字段的选择，直接决定了中间结果是否需要落盘，以及落盘的数据量大小。
SQL子查询产生临时文件的常见原因
子查询的执行逻辑决定了其容易触发临时文件的生成，主要有以下几种场景：
- 子查询返回的结果集过大，超过了内存缓冲区的可用空间，引擎只能将中间结果写入磁盘临时文件
- 子查询中包含
ORDER BY、GROUP BY、DISTINCT等需要排序的操作，排序过程需要额外的内存空间，内存不足时就会产生临时文件 - 相关子查询需要反复执行，每次执行的中间结果如果无法复用，会多次生成临时文件
- 子查询和主查询的关联条件没有合适的索引，导致引擎需要生成临时表来完成关联计算，进而产生临时文件
内存缓冲区对临时文件生成的影响
数据库的内存缓冲区参数直接决定了中间结果可以存放在内存中的上限，不同数据库的内存缓冲区参数配置不同，以下是常见数据库的相关参数说明：
| 数据库类型 | 相关内存参数 | 参数作用 |
|---|---|---|
| MySQL | tmp_table_size、max_heap_table_size | 控制内存临时表的最大大小，超过该值会转为磁盘临时表生成临时文件 |
| MySQL | sort_buffer_size | 控制排序操作可用的内存大小，排序数据超过该值会使用磁盘临时文件辅助排序 |
| PostgreSQL | work_mem | 控制单个查询操作可用的内存大小，包括排序、哈希操作等，超过则生成临时文件 |
| Oracle | pga_aggregate_target | 控制程序全局区的总内存大小，影响排序、哈希等操作的可用内存 |
如果内存缓冲区参数设置过小，即使子查询的中间结果不算特别大，也会触发临时文件的生成。比如MySQL中tmp_table_size默认是16M，如果子查询返回的结果集超过16M，就会直接生成磁盘临时文件。
排序字段对临时文件生成的影响
子查询中的排序操作是临时文件生成的重要诱因，排序字段的选择会直接影响排序的内存消耗：
- 如果排序字段没有索引，引擎需要对全量结果集进行排序，内存消耗更大，更容易触发临时文件生成
- 排序字段的数据类型越大，占用的内存空间越多，比如
VARCHAR(255)比INT类型排序时占用更多内存，更容易超过内存缓冲区上限 - 排序字段包含表达式或者函数计算，比如
ORDER BY YEAR(create_time)，引擎无法利用索引排序，只能进行全量排序，增加临时文件生成概率
优化内存缓冲区与排序字段的具体方案
1. 合理调整内存缓冲区参数
根据服务器的实际内存情况，适当调大相关内存参数，避免中间结果过早落盘。以MySQL为例，调整参数的方式如下：
-- 查看当前tmp_table_size配置 SHOW VARIABLES LIKE 'tmp_table_size'; -- 临时调整参数，重启后失效 SET GLOBAL tmp_table_size = 64 * 1024 * 1024; -- 永久调整需要修改my.cnf配置文件 [mysqld] tmp_table_size = 64M max_heap_table_size = 64M sort_buffer_size = 4M
注意调整参数时要结合服务器总内存，避免设置过大导致数据库占用过多内存，影响其他服务运行。
2. 优化子查询中的排序字段
尽量减少不必要的排序操作，对必须排序的场景优化排序字段：
- 为排序字段创建合适的索引，让引擎可以利用索引完成排序，避免额外排序操作。比如子查询需要按
user_id排序，就为user_id创建索引 - 避免使用大字段作为排序字段，如果必须排序，可以考虑只取大字段的前缀作为排序依据，或者将大字段拆分到关联表中
- 不要在排序字段中使用函数或者表达式，如果需要对计算后的值排序，可以提前将计算结果存为冗余字段，直接对该字段排序
3. 改写子查询逻辑减少中间结果
很多子查询可以通过改写减少中间结果的大小，从而减少临时文件的生成：
- 将子查询改写为关联查询，避免子查询返回全量结果集。比如将
SELECT * FROM t1 WHERE id IN (SELECT t1_id FROM t2)改写为SELECT t1.* FROM t1 JOIN t2 ON t1.id = t2.t1_id - 给子查询增加合适的过滤条件，减少返回的结果集数量，比如子查询中提前加上
WHERE status = 1这类过滤条件，减少中间结果大小 - 避免不必要的相关子查询，相关子查询会多次执行，尽量改写为非相关子查询或者关联查询
4. 验证优化效果
优化完成后可以通过数据库的监控命令查看临时文件的生成情况，确认优化是否生效。以MySQL为例，可以通过以下命令查看临时表相关状态：
-- 查看临时表创建情况，Created_tmp_disk_tables为磁盘临时表数量，Created_tmp_tables为总临时表数量 SHOW GLOBAL STATUS LIKE 'Created_tmp%'; -- 执行目标子查询后，对比两个指标的变化，确认磁盘临时表数量是否减少
如果Created_tmp_disk_tables的数值明显下降，说明优化起到了效果，临时文件生成量已经减少。