派生表 - Derived Table详解

定义

派生表 (Derived Table) 是SQL查询中出现在FROM子句里的子查询。数据库引擎会先执行这个子查询,将结果集物化为一个临时表(称为派生表),然后外层查询再从这个临时表中读取数据。派生表只在查询执行期间存在,不会永久存储。

核心特征

特征	说明
位置	FROM子句中
生命周期	查询执行期间
存储方式	物化为内部临时表
可见性	仅外层查询可见
别名要求	必须有别名
性能影响	可能触发全表扫描

基本语法

-- 派生表示例
SELECT 
  dt.category,
  dt.avg_price,
  dt.product_count
FROM (
  -- 这是派生表
  SELECT 
    category,
    AVG(price) as avg_price,
    COUNT(*) as product_count
  FROM products
  GROUP BY category
) AS dt  -- 必须有别名
WHERE dt.avg_price > 100
ORDER BY dt.product_count DESC;

为什么需要派生表?

问题1: 聚合后过滤

-- 需求: 找出平均价格高于100的品类

-- 方案A: 使用HAVING
SELECT 
  category,
  AVG(price) as avg_price,
  COUNT(*) as product_count
FROM products
GROUP BY category
HAVING AVG(price) > 100;


-- 方案B: 使用派生表(更灵活)
SELECT 
  dt.category,
  dt.avg_price,
  dt.product_count,
  ROUND(dt.avg_price * 1.2, 2) as suggested_price  -- 可在外层计算
FROM (
  SELECT 
    category,
    AVG(price) as avg_price,
    COUNT(*) as product_count
  FROM products
  GROUP BY category
) AS dt
WHERE dt.avg_price > 100;

-- ✓ 派生表优势: 外层可以进行更复杂的计算和JOIN

问题2: 分页优化

-- 需求: 订单列表分页,显示用户信息

-- 方案A: 直接JOIN(低效)
SELECT o.*, u.username, u.email
FROM orders o
JOIN users u ON o.user_id = u.id
ORDER BY o.created_at DESC
LIMIT 100 OFFSET 10000;
-- 需要排序10100行,然后丢弃前10000行


-- 方案B: 派生表优化(高效)
SELECT o.*, u.username, u.email
FROM (
  -- 先在派生表中完成分页
  SELECT id, user_id, amount, created_at
  FROM orders
  ORDER BY created_at DESC
  LIMIT 100 OFFSET 10000
) AS o
JOIN users u ON o.user_id = u.id;

-- ✓ 只JOIN 100行,性能提升100倍!

原理:

方案A执行流程:
1. orders JOIN users → 100万行
2. 排序100万行
3. 取第10001-10100行
4. 返回100行

方案B执行流程:
1. 派生表: orders排序并分页 → 100行
2. 100行 JOIN users → 100行
3. 返回100行

性能差异:
- JOIN行数: 100万 vs 100
- 内存使用: 高 vs 低
- 响应时间: 5秒 vs 50ms

问题3: 多层聚合

-- 需求: 计算每个品类的平均订单金额的平均值

-- 使用派生表分步计算
SELECT 
  AVG(category_total) as avg_category_total,
  MAX(category_total) as max_category_total,
  MIN(category_total) as min_category_total
FROM (
  -- 第一层: 计算每个品类的总金额
  SELECT 
    p.category,
    SUM(oi.quantity * oi.price) as category_total
  FROM order_items oi
  JOIN products p ON oi.product_id = p.id
  JOIN orders o ON oi.order_id = o.id
  WHERE o.created_at >= '2024-01-01'
  GROUP BY p.category
) AS category_stats;

-- ✓ 清晰的分层计算

执行机制

物化(Materialization)

EXPLAIN FORMAT=JSON
SELECT * FROM (
  SELECT category, AVG(price) as avg_price
  FROM products
  GROUP BY category
) AS dt
WHERE dt.avg_price > 100;

-- 输出:
{
  "query_block": {
  "table": {
  "materialized_from_subquery": {
    "using_temporary_table": true,
    "using_filesort": true,
    "query_block": {
    "table": {
    "table_name": "products",
    "access_type": "ALL",
    "rows": 100000
    }
    }
  }
  }
  }
}

-- 关键信息:
-- "using_temporary_table": true  ← 创建了临时表
-- "using_filesort": true   ← 进行了排序

物化过程:

Step 1: 执行子查询
  SELECT category, AVG(price) FROM products GROUP BY category
  ↓
Step 2: 结果写入临时表
  ┌─────────────────────────┐
  │ Temporary Table   │
  ├─────────────────────────┤
  │ Electronics | 299.99  │
  │ Clothing  | 89.99   │
  │ Books   | 45.50   │
  └─────────────────────────┘
  ↓
Step 3: 外层查询从临时表读取
  SELECT * FROM temporary_table WHERE avg_price > 100
  ↓
Step 4: 返回最终结果

合并(Merge)优化

-- MySQL 5.7+ 可能将派生表合并到外层查询

-- 简单派生表可能被合并
EXPLAIN
SELECT * FROM (
  SELECT * FROM products WHERE category = 'Electronics'
) AS dt
WHERE dt.price > 100;

-- 实际执行计划可能是:
-- SELECT * FROM products 
-- WHERE category = 'Electronics' AND price > 100
-- ↑ 派生表被"上拉"合并,避免创建临时表

-- 查看是否被合并
EXPLAIN FORMAT=JSON ...
-- 如果没有 "materialized_from_subquery",说明被合并了

合并条件:

• ✓ 派生表不包含聚合函数
• ✓ 派生表不包含GROUP BY
• ✓ 派生表不包含DISTINCT
• ✓ 派生表不包含LIMIT

阻止合并(强制物化):

-- 使用SQL_NO_CACHE或添加聚合
SELECT * FROM (
  SELECT /*+ NO_MERGE(dt) */ * 
  FROM products
  GROUP BY category  -- 有GROUP BY,无法合并
) AS dt;

实际应用案例

案例1: Top-N查询

-- 需求: 每个品类最贵的3个商品

-- 使用派生表 + ROW_NUMBER()
SELECT 
  dt.category,
  dt.product_name,
  dt.price,
  dt.rank
FROM (
  SELECT 
    category,
    product_name,
    price,
    ROW_NUMBER() OVER (
    PARTITION BY category 
    ORDER BY price DESC
    ) as rank
  FROM products
) AS dt
WHERE dt.rank <= 3
ORDER BY dt.category, dt.rank;

-- 输出:
-- +-------------+---------------+--------+------+
-- | category  | product_name  | price  | rank |
-- +-------------+---------------+--------+------+
-- | Books   | Book A    | 199.00 | 1  |
-- | Books   | Book B    | 189.00 | 2  |
-- | Books   | Book C    | 179.00 | 3  |
-- | Clothing  | Shirt X   | 299.00 | 1  |
-- | Clothing  | Shirt Y   | 289.00 | 2  |
-- | Clothing  | Shirt Z   | 279.00 | 3  |
-- +-------------+---------------+--------+------+

案例2: 数据透视表

-- 需求: 按月统计各品类销售额,横向展示

SELECT 
  dt.month,
  MAX(CASE WHEN dt.category = 'Electronics' THEN dt.total END) as electronics,
  MAX(CASE WHEN dt.category = 'Clothing' THEN dt.total END) as clothing,
  MAX(CASE WHEN dt.category = 'Books' THEN dt.total END) as books,
  SUM(dt.total) as grand_total
FROM (
  SELECT 
    DATE_FORMAT(o.created_at, '%Y-%m') as month,
    p.category,
    SUM(oi.quantity * oi.price) as total
  FROM order_items oi
  JOIN products p ON oi.product_id = p.id
  JOIN orders o ON oi.order_id = o.id
  GROUP BY month, p.category
) AS dt
GROUP BY dt.month
ORDER BY dt.month;

-- 输出:
-- +---------+-------------+----------+-------+-------------+
-- | month | electronics | clothing | books | grand_total |
-- +---------+-------------+----------+-------+-------------+
-- | 2024-01 | 50000   | 30000  | 20000 | 100000  |
-- | 2024-02 | 55000   | 32000  | 22000 | 109000  |
-- | 2024-03 | 60000   | 35000  | 25000 | 120000  |
-- +---------+-------------+----------+-------+-------------+

案例3: 反半连接(Anti-Join)

-- 需求: 找出没有下过单的用户

-- 使用派生表 + LEFT JOIN
SELECT u.*
FROM users u
LEFT JOIN (
  SELECT DISTINCT user_id FROM orders
) AS dt ON u.id = dt.user_id
WHERE dt.user_id IS NULL;

-- 或者使用NOT EXISTS(通常更快)
SELECT u.*
FROM users u
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id = u.id
);

性能优化

优化1: 减少派生表大小

-- ❌ 糟糕: 派生表返回所有列
SELECT dt.name, dt.email
FROM (
  SELECT * FROM users  -- 返回所有列,浪费
) AS dt
WHERE dt.age > 18;

-- ✓ 优秀: 只返回需要的列
SELECT dt.name, dt.email
FROM (
  SELECT id, name, email FROM users WHERE age > 18
) AS dt;

优化2: 在派生表中建立索引

-- MySQL 8.0+ 支持在派生表中创建索引
SELECT * FROM (
  SELECT category, product_name, price
  FROM products
  WHERE price > 100
) AS dt
WHERE dt.category = 'Electronics';

-- 如果products表在category上有索引,派生表会自动利用

优化3: 限制结果集大小

-- 在派生表中使用LIMIT
SELECT * FROM (
  SELECT * FROM orders
  ORDER BY created_at DESC
  LIMIT 1000  -- 只处理最近1000条
) AS dt
WHERE dt.amount > 1000;

最佳实践

1. 何时使用派生表

适合使用:
✓ 需要分层聚合
✓ 分页优化
✓ 简化复杂JOIN
✓ 代码可读性优先

不适合:
✗ 简单查询(增加开销)
✗ 实时性要求极高
✗ 数据量极大(临时表过大)

2. 替代方案对比

-- 方案A: 派生表
SELECT * FROM (SELECT ...) AS dt;

-- 方案B: CTE(公用表表达式) - MySQL 8.0+
WITH cte AS (SELECT ...)
SELECT * FROM cte;
-- ✓ 更清晰,可递归,可多次引用

-- 方案C: 临时表
CREATE TEMPORARY TABLE tmp AS SELECT ...;
SELECT * FROM tmp;
-- ✓ 可建索引,可多次查询

-- 选择建议:
-- 简单查询: CTE
-- 复杂分层: 派生表
-- 多次使用: 临时表

3. 监控派生表性能

-- 查看是否使用了临时表
SHOW GLOBAL STATUS LIKE 'Created_tmp_tables';

-- 分析执行计划
EXPLAIN FORMAT=JSON your_query;

-- 关注:
-- "using_temporary_table": true  ← 创建了临时表
-- "rows": 数值过大      ← 可能需要优化

参考资料

相关术语

• 临时表
• 物化视图
• 执行计划

MySQL官方文档

• Derived Tables
• Optimizing Derived Tables

---

版本历史:

• 2026-04-12: 初始版本,讲解派生表原理与优化