走进官方手册系列 --- MySQL内部临时表的使用

最新推荐文章于 2024-08-29 10:43:19 发布

亮总说数

最新推荐文章于 2024-08-29 10:43:19 发布

阅读量525

点赞数

分类专栏：走进官方手册

走进官方手册专栏收录该内容

3 篇文章 0 订阅

订阅专栏

官方Manual：https://dev.mysql.com/doc/refman/5.6/en/internal-temporary-tables.html

MySQL在执行语句过程中，有时会自动创建内部临时表，而用户无法直接干预。
MySQL一般在如下场景下会创建临时表：
1. 含有UNION的语句
2. 使用到TEMPTABLE算法、UNION或者两者兼有的视图
3. 派生表（from从句的子查询）
4. 在做子查询或半连接（semi-join）物化（materialization）时创建的表
5. ORDER BY和GROUP BY的从句不同；或者，ORDER BY或GROUP BY的字段来源于除了join队列中的第一个表之外的其他表
6. DISTINCT和ORDER BY同时使用
7. 使用到SQL_SMALL_RESULT修饰符的查询，MySQL将会用到一个全内存临时表；当查询包含一些需要进行磁盘存储的元素时，则会用到磁盘临时表
8. INSERT … SELECT 语句中，如果select from和insert into的是同一张表，MySQL会创建一张内部临时表来存放select到的行记录，然后再将其插入到目标表中。
9. 多表UPDATE语句
10. 含有GROUP_CONCAT() 或者COUNT(DISTINCT) 函数的语句

一条语句是否用到临时表，最简单的办法还是使用EXPLAIN，查看Extra输出中是否含有“Using temporary”。派生或实物化的临时表，在explain过程中不一定会显示“Using temporary”。

一旦MySQL创建了一张内部临时表（无论是存放在内存中还是转到磁盘上），都会增加Created_tmp_tables 的状态值。如果临时表是被创建到磁盘上（包括最初就被创建到磁盘上，以及由全内存表转换到磁盘上的），则会增加Created_tmp_disk_tables的状态值。

如下几种情况下，将不会使用内存临时表，而会直接在磁盘上创建临时表：
1. 表字段存在BLOB或TEXT类型。This includes user-defined variables having a string value because they are treated as BLOB or TEXT columns, depending on whether their value is a binary or nonbinary string, respectively.
2. GROUP BY或者DISTINCT子句中存在大于512bytes的字符串类型的字段；Presence of any string column in a GROUP BY or DISTINCT clause larger than 512 bytes for binary strings or 512 characters for nonbinary strings.
3. Presence of any string column with a maximum length larger than 512 (bytes for binary strings, characters for nonbinary strings) in the SELECT list, if UNION or UNION ALL is used.
4. The SHOW COLUMNS and DESCRIBE statements use BLOB as the type for some columns, thus the temporary table used for the results is an on-disk table.
5. Internal Temporary Table Storage Engine
6. Internal Temporary Table Storage Format

参考文档：
MySQL · 新特性分析 · 5.7中Derived table变形记