搞清楚 MySQL 派生表、物化表、临时表

cschmin

已于 2022-03-04 19:22:27 修改

阅读量4.7k

点赞数 6

CC 4.0 BY-SA版权

文章标签： mysql 数据库 database

于 2022-02-26 12:31:12 首次发布

本文链接：https://blog.csdn.net/cschmin/article/details/123148143

本文介绍了MySQL中的派生表、物化表和临时表的概念及区别。派生表和物化表本质上是临时表，分别用于存储子查询结果。派生表通常出现在SELECT子句中，而物化表常用于优化IN子查询，避免多次读取数据。临时表则广泛用于GROUP BY、DISTINCT和UNION等操作，以提高查询效率。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

以前只知道 MySQL 里有临时表，后来随着花在 MySQL 上的时间越来越多，奇怪的知识增加了不少，比如：物化、半连接、首次匹配……

刚开始看到一些新名词的时候，充满了疑惑，也傻傻的分不清楚，经过一段时间的折腾，对这些概念多了一些了解。

今天先来说说 3 种表：派生表、物化表、临时表，刚开始看到派生表、物化表的时候，虽然官方文档和一些书籍上都有介绍，但并不十分清楚它们都是干嘛的，会用在什么地方？

派生表、物化表，看起来高大上，实际上它们两个也是临时表，只是官方给某些场景下使用的临时表取了个名字而已。

1. 派生表

派生表，是用于存储子查询产生的结果的临时表，这个子查询特指 FROM 子句 里的子查询，如果是出现在其它地方的子查询，就不叫这个名字了，所以本质上来说，派生表也是临时表。

explain select * from t1 inner join (
    select distinct i1 from t3 where id in (3, 666, 990)
) as a on t1.i1 = a.i1

+----+-------------+------------+------------+-------+---------------+---------+---------+--------+------+----------+----------------------------------------------------+
| id | select_type | table      | partitions | type  | possible_keys | key     | key_len | ref    | rows | filtered | Extra                                              |
+----+-------------+------------+------------+-------+---------------+---------+---------+--------+------+----------+----------------------------------------------------+
| 1  | PRIMARY     | <derived2> | <null>     | ALL   | <null>        | <null>  | <null>  | <null> | 3    | 100.0    | <null>                                             |
| 1  | PRIMARY     | t1