MySql临时表

临时表

一. 简介

非压缩的，用户创建的临时表和磁盘内部临时表共享一个存储空间，通过 innodb_temp_data_file_path 变量定义临时表空间数据文件的相对路径、名称、大小和属性。默认为 innodb_data_home_dir 目录下创建一个名为ibtmp1的自动扩展的数据文件，该文件略大于12MB。

临时表空间会在正常关机或初始化中止时被移除，并在每次服务器启动时被重新创建。如果服务器意外停止，临时表空间不会被删除。在这种情况下，数据库管理员可以手动删除临时表空间，或者重新启动服务器，服务器会自动删除并重新创建临时表空间。

（5.6版本，非压缩的临时表是在临时文件目录中的每个文件的表空间中创建的，在MySQL 5.7中引入的共享临时表空间消除了与为每个临时表创建和删除逐个文件表空间有关的性能成本）

在 MySQL 5.7 之前， SQL 运行中产生的临时表是 MYISAM，而且只能是 MYISAM。MySQL 从 5.7 开始提供了参数 Internal_tmp_mem_storage_engine 来定义内部的临时表引擎，可选值为 MYISAM 和 INNODB 。内部的临时表默认保存在临时表空间 ibtmp1 (可以用参数 innodb_temp_data_file_path 设置大小等)。需要注意控制 ibtmp1 的大小，防止把磁盘撑爆。

但是MySQL 5.7 之前都没有解决如下问题：

• VARCHAR的变长存储。如果临时表的字段定义是 VARCHAR(200) ，映射到内存里处理的字段变为CHAR(200) ，假设 VARCHAR(200) 只存一个字符会造成浪费。
• 大对象的默认磁盘存储，比如 TEXT，BLOB， JSON等，不管里面存放了什么，直接转化为磁盘存储。

MySQL 8.0 开始，专门实现了一个临时表的引擎 TempTable , 解决了 VARCHAR 字段的边长存储以及大对象的内存存储。由变量 interal_tmp_mem_storage_engine 来控制，可选值为 TempTable（默认）和 Memory ；新引擎的大小由参数 temp_table_max_ram 来控制，默认为1G。超过了则存储在磁盘上（ibtmp1）。并且计数器由性能字典的表 memory_summary_global_by_event_name 来存储。

二. 查看当前临时表信息

INFORMATION_SCHEMA.FILES 提供关于InnoDB临时表空间的元数据，这些临时表目前在InnoDB实例中是活跃的。

SELECT * FROM INFORMATION_SCHEMA.FILES WHERE TABLESPACE_NAME='innodb_temporary';

那么这两种临时表的计数器通常用 show global status like '%tmp_%tables%' 来查看。

show global status like '%tmp_%tables%';

mysql> show status like '%tmp_%tables%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
| Created_tmp_tables      | 0     |
+-------------------------+-------+
2 rows in set (0.00 sec)

以上结果分别代表，只创建磁盘上的临时表计数以及临时表的总计数。这两个计数器由参数 tmp_table_size 和 max_heap_table_size 两个取最小值来控制。

memory/ temptable/physical_disk 代表放入磁盘上的临时表计数情况，memory/temptable/physical_ram 代表放入内存的临时表计数情况。

mysql> SELECT * FROM performance_schema. memory_summary_global_by_event_name WHERE event_name like '%temptable%'G
*************************** 1. row ***************************                  EVENT_NAME: **memory/temptable/physical_disk**                 
COUNT_ALLOC: 0                  
COUNT_FREE: 0   
SUM_NUMBER_OF_BYTES_ALLOC: 0    
SUM_NUMBER_OF_BYTES_FREE: 0              
LOW_COUNT_USED: 0          
CURRENT_COUNT_USED: 0             
HIGH_COUNT_USED: 0    
LOW_NUMBER_OF_BYTES_USED: 0
CURRENT_NUMBER_OF_BYTES_USED: 0   
HIGH_NUMBER_OF_BYTES_USED: 0
*************************** 2. row ***************************                  EVENT_NAME: **memory/temptable/physical_ram**                 
COUNT_ALLOC: 1                  
COUNT_FREE: 0   
SUM_NUMBER_OF_BYTES_ALLOC: 1048576    
SUM_NUMBER_OF_BYTES_FREE: 0              
LOW_COUNT_USED: 0          
CURRENT_COUNT_USED: 1             
HIGH_COUNT_USED: 1    
LOW_NUMBER_OF_BYTES_USED: 0
CURRENT_NUMBER_OF_BYTES_USED: 1048576   
HIGH_NUMBER_OF_BYTES_USED: 1048576
2 rows in set (0.03 sec)

三. 临时表空间大小

默认情况下，临时表空间数据文件是自动扩展的，并根据需要增加大小以适应磁盘上的临时表。例如，如果一个操作创建了一个大小为20MB的临时表，临时表空间数据文件在创建时默认为12MB大小，它的大小会扩展以适应它。当临时表被丢弃时，释放的空间可以重新用于新的临时表，但是数据文件仍然是扩展的大小。

在使用大型临时表或广泛使用临时表的环境中，自动扩展的临时表空间数据文件可能变得很大。一个大的数据文件也可能来自使用临时表的长期运行的查询。

通过检查 innodb_temp_data_file_path 设置确认一个临时表空间数据文件是否是自动扩展的：

mysql> SELECT @@innodb_temp_data_file_path;
+------------------------------+
| @@innodb_temp_data_file_path |
+------------------------------+
| ibtmp1:12M:autoextend        |
+------------------------------+

检查临时表空间数据文件的大小：

SELECT FILE_NAME, TABLESPACE_NAME, ENGINE, INITIAL_SIZE, TOTAL_EXTENTS*EXTENT_SIZE
       AS TotalSizeBytes, DATA_FREE, MAXIMUM_SIZE FROM INFORMATION_SCHEMA.FILES
       WHERE TABLESPACE_NAME = 'innodb_temporary'\G
*************************** 1. row ***************************
      FILE_NAME: ./ibtmp1
TABLESPACE_NAME: innodb_temporary
         ENGINE: InnoDB
   INITIAL_SIZE: 12582912
 TotalSizeBytes: 12582912
      DATA_FREE: 6291456
   MAXIMUM_SIZE: NULL

TotalSizeBytes 表示临时表空间数据文件的当前大小。

临时表相关配置：

• tmp_table_size ：指定系统创建的内存临时表最大大小；
• max_heap_table_size : 指定用户创建的内存表的最大大小；

注意：最终的系统创建的内存临时表大小是取上述两个配置值的最小值。

四. MySQL创建临时表的场景

MySQL临时表分为内存临时表和磁盘临时表，其中内存临时表使用MySQL的MEMORY存储引擎，磁盘临时表使用MySQL的MyISAM存储引擎；

一般情况下，MySQL会先创建内存临时表，但内存临时表超过配置指定的值后，MySQL会将内存临时表导出到磁盘临时表；Linux平台上缺省是/tmp目录，/tmp目录小的系统需要注意。

4.1 内存临时表

MySQL在以下几种情况会创建临时表：

1. UNION查询；
2. 用到TEMPTABLE算法或者是UNION查询中的视图；
3. ORDER BY和GROUP BY的子句不一样时， 例如：ORDERY BY price GROUP BY name ；
4. 表连接中，ORDER BY的列不是驱动表中的。在JOIN查询中，ORDER BY或者GROUP BY使用了不是第一个表的列，例如：SELECT * from TableA, TableB ORDER BY TableA.price GROUP by TableB.name ；
5. ORDER BY中使用了DISTINCT关键字 ORDERY BY DISTINCT(price) ；
6. SQL中用到SQL_SMALL_RESULT选项时，SELECT语句中指定了SQL_SMALL_RESULT关键字，意思就是告诉MySQL结果会很小，请直接使用内存临时表，不需要使用索引排序 SQL_SMALL_RESULT必须和GROUP BY、DISTINCT或DISTINCTROW一起使用 一般情况下，我们没有必要使用这个选项，让MySQL服务器选择即可。
7. FROM中的子查询；
8. 子查询或者semi-join时创建的表；

EXPLAIN 查看执行计划结果的 Extra 列中，如果包含 Using Temporary 就表示会用到临时表。

当然了，如果临时表中需要存储的数据量超过了上限（ tmp-table-size 或 max-heap-table-size 中取其大者），这时候就需要生成基于磁盘的临时表了。

4.2 磁盘临时表

在以下几种情况下，会创建磁盘临时表：

1. 数据表中包含BLOB/TEXT列；
2. 在 GROUP BY 或者 DISTINCT 的列中有超过512字符的字符类型列（或者超过 512字节的二进制类型列，在5.6.15之前只管是否超过512字节）；
3. 在 SELECT 、UNION 、UNION ALL 查询中，SELECT子句存在最大长度超过512的列（对于字符串类型是512个字符，对于二进制类型则是512字节）；
4. 执行 SHOW COLUMNS / FIELDS 、DESCRIBE 等SQL命令，因为它们的执行结果用到了BLOB列类型。

从5.7.5开始，新增一个系统选项 internal_tmp_disk_storage_engine 可定义磁盘临时表的引擎类型为 InnoDB，而在这以前，只能使用MyISAM。而在5.6.3以后新增的系统选项 default_tmp_storage_engine 是控制 CREATE TEMPORARY TABLE 创建的临时表的引擎类型，在以前默认是MEMORY，不要把这二者混淆了。

见下例：

mysql> set default_tmp_storage_engine = "InnoDB";
-rw-rw----   1 mysql mysql  8558 Jul  7 15:22 #sql4b0e_10_0.frm -- InnoDB引擎的临时表
-rw-rw----   1 mysql mysql 98304 Jul  7 15:22 #sql4b0e_10_0.ibd
-rw-rw----   1 mysql mysql  8558 Jul  7 15:25 #sql4b0e_10_2.frm

mysql> set default_tmp_storage_engine = "MyISAM";
-rw-rw----   1 mysql mysql     0 Jul  7 15:25 #sql4b0e_10_2.MYD -- MyISAM引擎的临时表
-rw-rw----   1 mysql mysql  1024 Jul  7 15:25 #sql4b0e_10_2.MYI

mysql> set default_tmp_storage_engine = "MEMORY";
-rw-rw----   1 mysql mysql  8558 Jul  7 15:26 #sql4b0e_10_3.frm -- MEMORY引擎的临时表

用户自定义的临时表，比如：

create temporary table (id int, str1 varchar(100));

SQL执行过程中产生的内部临时表，比如：UNION , 聚合类 ORDER BY ，派生表，大对象字段的查询，子查询或者半连接的固化等场景。

五. 临时表优化

表的设计原则：使用临时表一般都意味着性能比较低，特别是使用磁盘临时表，性能更慢，因此我们在实际应用中应该尽量避免临时表的使用。 常见的避免临时表的方法有：

1. 创建索引：在ORDER BY或者GROUP BY的列上创建索引；
2. 分拆很长的列：一般情况下，TEXT、BLOB，大于512字节的字符串，基本上都是为了显示信息，而不会用于查询条件， 因此表设计的时候，应该将这些列独立到另外一张表。

SQL优化：如果表的设计已经确定，修改比较困难，那么也可以通过优化SQL语句来减少临时表的大小，以提升SQL执行效率。

常见的优化SQL语句方法如下：

1. 拆分SQL语句：临时表主要是用于排序和分组，很多业务都是要求排序后再取出详细的分页数据，这种情况下可以将排序和取出详细数据拆分成不同的SQL，以降低排序或分组时临时表的大小，提升排序和分组的效率，我们的案例就是采用这种方法。
2. 优化业务，去掉排序分组等操作：有时候业务其实并不需要排序或分组，仅仅是为了好看或者阅读方便而进行了排序，例如数据导出、数据查询等操作，这种情况下去掉排序和分组对业务也没有多大影响。

如何判断使用了临时表？使用 explain 查看执行计划，Extra列看到 Using temporary 就意味着使用了临时表。

案例：

1. 【问题描述】：定位到一个慢查询，查询时服务器IO飙升，IO占用率达到100%， 执行时间长达7s左右。

   SELECT DISTINCT g.*, cp.name AS cp_name, c.name AS category_name, t.name AS type_name 
   FROM gm_game g 
   LEFT JOIN gm_cp cp 
   ON cp.id = g.cp_id AND cp.deleted = 0 
   LEFT JOIN gm_category c 
   ON c.id = g.category_id AND c.deleted = 0 
   LEFT JOIN gm_type t 
   ON t.id = g.type_id AND t.deleted = 0 
   WHERE g.deleted = 0 
   ORDER BY g.modify_time DESC LIMIT 20;

2. 【问题分析】：使用explain查看执行计划，结果：Using temporary 和 Using filesort 。这条sql语句的问题其实还是比较明显的：查询了大量数据(包括数据条数、以及g.* )，然后使用临时表order by，但最终又只返回了20条数据。观察到的IO高，是因为sql语句生成了一个巨大的临时表，内存放不下，于是全部拷贝到磁盘，导致IO飙升。

3. 【优化方案】：优化的总体思路是拆分sql，将排序操作和查询所有信息的操作分开。

   1. 第一条语句：查询符合条件的数据，只需要查询g.id即可：

      SELECT DISTINCT g.id 
      FROM gm_game g 
      LEFT JOIN gm_cp cp ON cp.id = g.cp_id AND cp.deleted = 0 
      LEFT JOIN gm_category c ON c.id = g.category_id AND c.deleted = 0 
      LEFT JOIN gm_type t ON t.id = g.type_id AND t.deleted = 0 
      WHERE g.deleted = 0 
      ORDER BY g.modify_time DESC LIMIT 20 ;

   2. 第二条语句：查询符合条件的详细数据，将第一条sql的结果使用in操作拼接到第二条的sql：

      SELECT DISTINCT g.*, cp.name AS cp_name,c.name AS category_name,t.name AS type_name 
      FROM gm_game g 
      LEFT JOIN gm_cp cp ON cp.id = g.cp_id AND cp.deleted = 0 
      LEFT JOIN gm_category c ON c.id = g.category_id AND c.deleted = 0 
      LEFT JOIN gm_type t ON t.id = g.type_id AND t.deleted = 0 
      WHERE g.deleted = 0 and g.id in(…………………) 
      ORDER BY g.modify_time DESC ;

4. 【实测效果】：在SATA机器上测试，优化前大约需要50s，优化后第一条0.3s，第二条0.1s，优化后执行速度是原来的100倍以上，IO从100%降到不到1%；在SSD机器上测试，优化前大约需要7s，优化后第一条0.3s，第二条0.1s，优化后执行速度是原来的10倍以上，IO从100%降到不到1%。可以看出，优化前磁盘io是性能瓶颈，SSD的速度要比SATA明显要快，优化后磁盘不再是瓶颈，SSD和SATA性能没有差别。

5. 【理论分析】：MySQL在执行SQL查询时可能会用到临时表，一般情况下，用到临时表就意味着性能较低。

六. 扩展

6.1 INFORMATION_SCHEMA.FILES 介绍

表结构:

• FILE_ID ：

  • For InnoDB ：表空间ID，也被称为 space_id 或 fil_space_t::id 。
  • For NDB ：一个文件标识符。FILE_ID 列的值是自动生成的。

• FILE_NAME ：

  • For InnoDB ：数据文件的名称。
    • 表空间包含单个InnoDB表的数据和索引（File-per-table）和一般表空间的文件名扩展名为 .ibd 。
    • Undo 表空间的前缀是 undo 。
    • 系统表空间的前缀是 ibdata 。
    • 临时表空间的前缀是 ibtmp 。
    • 文件名包括文件路径，可以是相对于MySQL数据目录 （the value of the datadir system variable）。
  • For NDB ：由 CREATE LOGFILE GROUP 或 ALTER LOGFILE GROUP 创建的 UNDO 日志文件名； 或由 CREATE TABLESPACE 或 ALTER TABLESPACE 创建的数据文件名。

• FILE_TYPE ：

  • For InnoDB ：表空间文件的类型。InnoDB 文件有三种可能的文件类型：

    • TABLESPACE 是任何系统的、一般的、或 file-per-table 文件的表空间文件的文件类型，它持有表、索引、或其他形式的用户数据。

    • TEMPORARY 是临时表空间的文件类型。

    • UNDO LOG 是UNDO表空间的文件类型，用于保存撤销记录。

  • For NDB ： UNDO LOG 、DATAFILE 或 TABLESPACE 中的一个。

• TABLESPACE_NAME ：与该文件相关的表空间的名称。

• TABLE_CATALOG ：这个值总是空的。

• TABLE_SCHEMA ：这个值总是 NULL 。

• TABLE_NAME ：这个值总是 NULL 。

• LOGFILE_GROUP_NAME ：

  • For InnoDB ：这个值总是 NULL 。
  • For NDB ：日志文件或数据文件所属的日志文件组的名称。

• LOGFILE_GROUP_NUMBER ：

  • For InnoDB ：这个值总是 NULL 。

  • For NDB ：对于磁盘数据UNDO日志文件，自动生成的日志文件组的ID号码，该日志文件属于该组。这与 ndbinfo.dict_obj_info表中的 id 列以及 ndbinfo.logspaces 和 ndbinfo.logspaces 表中的 log_id 列所显示的该UNDO日志文件的值相同。

• ENGINE ：

  • For InnoDB ：这个值总是 InnoDB 。
  • For NDB ：这个值总是 ndbcluster 。

• FULLTEXT_KEYS ：这个值总是 NULL 。

• DELETED_ROWS ：这个值总是 NULL 。

• UPDATE_COUNT ：这个值总是 NULL 。

• FREE_EXTENTS ：

  • For InnoDB ：当前数据文件中完全空闲的扩展数。
  • For NDB ：文件尚未使用的extents的数量。

• TOTAL_EXTENTS ：

  • For InnoDB ：在当前数据文件中使用的完整扩展的数量。文件末尾的任何部分范围都不计算在内。
  • For NDB ：分配给文件的总扩展数。

• EXTENT_SIZE ：

  • For InnoDB ：

    • 对于页面大小为4KB、8KB或16KB的文件，其Extent大小为1048576（1MB）。
    • 对于页面大小为32KB的文件，Extent大小为2097152字节（2MB）。
    • 对于页面大小为64KB的文件，Extent大小为4194304字节（4MB）。

    FILES 不包括 InnoDB 的页面大小。页面大小是由 innodb_page_size 系统变量统计。也可以从 INNODB_SYS_TABLESPACES 表中检索Extent的大小信息，其中 FILES.FILE_ID = INNODB_SYS_TABLESPACES.SPACE 。

  • For NDB ：文件的范围大小，以字节为单位。

• INITIAL_SIZE ：

  • For InnoDB ：文件的初始大小，以字节为单位。
  • For NDB ：文件的大小，单位是字节。用于创建文件的 CREATE LOGFILE GROUP 、ALTER LOGFILE GROUP 、CREATE TABLESPACE 或 ALTER TABLESPACE 语句的 INITIAL_SIZE 子句中使用的值相同。

• MAXIMUM_SIZE ：

  • For InnoDB ：文件中允许的最大字节数。除了预定义的系统表空间数据文件，所有数据文件的值都是NULL 。

    • 最大的系统表空间文件大小是由 innodb_data_file_path 定义的。
    • 最大的临时表空间文件大小是由 innodb_temp_data_file_path 定义的。
    • 一个预定义的系统表空间数据文件的 NULL 值表示没有明确定义文件的大小限制。

  • For NDB ：这个值总是与 INITIAL_SIZE 值相同。

• AUTOEXTEND_SIZE ：表空间的自动扩展大小。对于NDB，AUTOEXTEND_SIZE 总是 NULL 。

• CREATION_TIME ：这个值总是 NULL 。

• LAST_UPDATE_TIME ：这个值总是 NULL 。

• LAST_ACCESS_TIME ：这个值总是 NULL 。

• RECOVER_TIME ：这个值总是 NULL 。

• TRANSACTION_COUNTER ：这个值总是 NULL 。

• VERSION ：

  • For InnoDB ：这个值总是 NULL 。

  • For NDB ：文件的版本号。

• ROW_FORMAT

  • For InnoDB ：这个值总是 NULL 。

  • For NDB ：FIXED 或 DYNAMIC 其中一个。

• TABLE_ROWS ：这个值总是 NULL 。

• AVG_ROW_LENGTH ：这个值总是 NULL 。

• DATA_LENGTH ：这个值总是 NULL 。

• MAX_DATA_LENGTH ：这个值总是 NULL 。

• INDEX_LENGTH ：这个值总是 NULL 。

• DATA_FREE ：

  • For InnoDB ：整个表空间的空闲空间总量（以字节为单位）。预定义的系统表空间，包括系统表空间和临时表空间，可以有一个或多个数据文件。

  • For NDB ：这个值总是 NULL 。

• CREATE_TIME ：这个值总是 NULL 。

• UPDATE_TIME ：这个值总是 NULL 。

• CHECK_TIME ：这个值总是 NULL 。

• CHECKSUM ：这个值总是 NULL 。

• STATUS ：

  • For InnoDB ：这个值默认为 NORMAL 。InnoDB 的 file-per-table 表空间可能为 IMPORTING ，这表明表空间尚未可用。

  • For NDB ：这个值总是 NORMAL 。

• EXTRA ：

  • For InnoDB ：这个值总是 NULL 。

  • For NDB：这一列显示了数据文件或 undo 日志文件属于哪个数据节点（每个数据节点都有每个文件的副本）； 对于 undo 日志文件，它还显示了撤销日志缓冲区的大小。假设你在一个有四个数据节点的NDB集群上使用这个语句：

    CREATE LOGFILE GROUP mygroup
        ADD UNDOFILE 'new_undo.dat'
        INITIAL_SIZE 2G
        ENGINE NDB;

    在成功运行 CREATE LOGFILE GROUP 语句后，你应该看到一个类似于，这里显示为针对 FILES 表的查询结果：

    mysql> SELECT LOGFILE_GROUP_NAME, FILE_TYPE, EXTRA
             FROM INFORMATION_SCHEMA.FILES
             WHERE FILE_NAME = 'new_undo.dat';
    
    +--------------------+-----------+-----------------------------------------+
    | LOGFILE_GROUP_NAME | FILE_TYPE | EXTRA                                   |
    +--------------------+-----------+-----------------------------------------+
    | mygroup            | UNDO LOG  | CLUSTER_NODE=5;UNDO_BUFFER_SIZE=8388608 |
    | mygroup            | UNDO LOG  | CLUSTER_NODE=6;UNDO_BUFFER_SIZE=8388608 |
    | mygroup            | UNDO LOG  | CLUSTER_NODE=7;UNDO_BUFFER_SIZE=8388608 |
    | mygroup            | UNDO LOG  | CLUSTER_NODE=8;UNDO_BUFFER_SIZE=8388608 |
    +--------------------+-----------+-----------------------------------------+

FILES 是一个非标准的 INFORMATION_SCHEMA 表。

适用于 InnoDB 数据文件的注意事项：

• FILES 报告的数据来自于 InnoDB 的内存缓存中的开放文件。相比之下， INNODB_SYS_DATAFILES 报告的数据来自 InnoDB SYS_DATAFILES 内部数据字典表。

• FILES报告的数据包括临时表空间数据。这些数据在 InnoDB 的 SYS_DATAFILES 内部数据字典表中是不可用的，因此 INNODB_SYS_DATAFILES 不会报告。

• Undo 的表空间数据由 FILES 报告。

• 下面的查询返回所有与 InnoDB 表空间有关的数据：

  SELECT
    FILE_ID, FILE_NAME, FILE_TYPE, TABLESPACE_NAME, FREE_EXTENTS,
    TOTAL_EXTENTS, EXTENT_SIZE, INITIAL_SIZE, MAXIMUM_SIZE,
    AUTOEXTEND_SIZE, DATA_FREE, STATUS
  FROM INFORMATION_SCHEMA.FILES WHERE ENGINE='InnoDB'\G

---

参考：

🔗 MySQL :: MySQL 5.7 Reference Manual :: 14.6.3.5 The Temporary Tablespace

🔗 MySQL :: MySQL 5.7 Reference Manual :: 24.3.9 The INFORMATION_SCHEMA FILES Table

🔗 From MYSQL’s ibtmp1 file is too big (programmer.help)

🔗 优化临时表使用