SQL子查询与临时表的性能对比如何？实战测试分析告诉你答案

在SQL开发中，当需要实现多层级数据关联、复杂条件过滤等需求时，子查询和临时表是两种常用的实现方案。两者的适用场景和性能表现差异明显，很多开发者在实际选型时缺乏明确的判断依据，容易因为选错方案导致查询效率低下。

测试环境准备

本次测试使用MySQL 8.0数据库，创建两张测试表，分别模拟用户基础信息和用户订单信息，表结构如下：

-- 用户表
CREATE TABLE user_info (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    user_name VARCHAR(50) NOT NULL,
    age INT NOT NULL,
    register_time DATETIME NOT NULL,
    INDEX idx_age (age)
);

-- 订单表
CREATE TABLE order_info (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    order_amount DECIMAL(10,2) NOT NULL,
    order_time DATETIME NOT NULL,
    INDEX idx_user_id (user_id),
    INDEX idx_order_time (order_time)
);

测试数据通过存储过程批量生成，user_info表分别插入1万、10万、100万条数据，order_info表对应每个用户生成1-5条订单数据，保证测试场景覆盖不同数据量级。

同需求两种实现方案

测试需求为：查询年龄大于30岁的用户，在近30天内产生的订单总金额，同时返回用户的基础信息。分别用子查询和临时表实现该需求。

子查询实现方案

SELECT 
    u.user_id,
    u.user_name,
    u.age,
    IFNULL(o.total_amount, 0) AS total_order_amount
FROM user_info u
LEFT JOIN (
    -- 子查询：统计每个用户近30天的订单总金额
    SELECT 
        user_id,
        SUM(order_amount) AS total_amount
    FROM order_info
    WHERE order_time >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
    GROUP BY user_id
) o ON u.user_id = o.user_id
WHERE u.age > 30;

临时表实现方案

-- 创建临时表存储近30天订单统计数据
CREATE TEMPORARY TABLE tmp_user_order_amount (
    user_id INT PRIMARY KEY,
    total_amount DECIMAL(10,2)
) ENGINE=InnoDB;

-- 向临时表插入数据
INSERT INTO tmp_user_order_amount
SELECT 
    user_id,
    SUM(order_amount) AS total_amount
FROM order_info
WHERE order_time >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
GROUP BY user_id;

-- 关联用户表查询最终结果
SELECT 
    u.user_id,
    u.user_name,
    u.age,
    IFNULL(t.total_amount, 0) AS total_order_amount
FROM user_info u
LEFT JOIN tmp_user_order_amount t ON u.user_id = t.user_id
WHERE u.age > 30;

-- 测试完成后删除临时表（会话结束会自动删除，此处为规范操作）
DROP TEMPORARY TABLE IF EXISTS tmp_user_order_amount;

性能测试结果对比

分别在1万、10万、100万条用户数据的场景下，执行两种方案的SQL，记录执行耗时和资源占用情况，结果如下：

性能差异原因分析

从测试结果可以看出，数据量较小时子查询性能更优，数据量增大后临时表性能反超，核心原因和两者的执行机制有关。

子查询的执行逻辑

上述场景中的子查询属于派生表子查询，MySQL会先将子查询的结果集物化为一个临时表，再和主表进行关联查询。在数据量较小时，物化过程开销小，且子查询的结果集可以直接利用order_info表的idx_user_id索引，整体效率较高。但当子查询返回的结果集较大时，物化临时表的内存开销增加，且如果子查询中无法有效利用索引，扫描行数会大幅上升，性能下降明显。

临时表的执行逻辑

临时表的实现方案将统计逻辑拆分到独立的临时表中，首先通过INSERT语句将统计结果写入临时表，此时可以为临时表创建索引（本次测试为临时表添加了user_id主键索引），后续关联查询时可以直接利用临时表的索引快速匹配。数据量较小时，创建临时表、插入数据、创建索引的额外开销会导致整体耗时比子查询高；但数据量较大时，临时表的索引优势会抵消额外的操作开销，整体性能更优。

选型建议

结合测试结果和执行逻辑，给出以下选型参考：

• 当数据量较小（单表数据量低于10万），且子查询逻辑简单、可以有效利用索引时，优先选择子查询，减少额外操作开销。
• 当数据量较大，或者子查询逻辑复杂、需要多次复用子查询结果时，优先选择临时表，通过为临时表创建合适的索引提升关联效率。
• 如果子查询的结果集需要被多个查询复用，临时表的优势更明显，避免重复执行子查询逻辑。
• 注意临时表会占用会话级的内存或磁盘空间，如果临时表数据量极大，需要评估数据库的内存配置，避免出现临时表磁盘化的情况。

实际开发中还需要结合具体数据库引擎的特性调整，比如PostgreSQL的子查询优化机制更完善，小数据量场景下子查询的性能优势会更明显，建议针对自己的数据库环境做针对性测试。

可以通过EXPLAIN命令查看两种方案的详细执行计划，进一步确认索引使用情况和扫描行数，辅助做出更准确的选型决策。

-- 查看子查询方案的执行计划
EXPLAIN
SELECT 
    u.user_id,
    u.user_name,
    u.age,
    IFNULL(o.total_amount, 0) AS total_order_amount
FROM user_info u
LEFT JOIN (
    SELECT 
        user_id,
        SUM(order_amount) AS total_amount
    FROM order_info
    WHERE order_time >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
    GROUP BY user_id
) o ON u.user_id = o.user_id
WHERE u.age > 30;

SQL子查询临时表性能优化修改时间：2026-06-29 14:15:19