MySQL内部临时表

不同于create temporary table 创建的临时表，内存临时表是指MySQL在处理语句时内部创建的用于存储中间结果的临时表。用户无法控制内部临时表的创建，MySQL优化器会自行判断是否使用内部临时表。通常 group by、order by、union、CTE、distinct、insert……select、窗口函数 和多表的update等语句可能会使用到内部临时表。

要确定语句是否使用了临时表，可以通过explain返回的结果来确认。通常MySQL会优先在内存中创建内部临时表，但在某些情况下会直接在磁盘创建内部临时表。例如表中存在BLOB或TEXT列；以及在union或union all中select的字段存在大于512的字符串等等情况。

内部临时表在内存中使用TempTable或Memory引擎，在磁盘上使用InnoDB引擎组织存储数据。

内存内部临时表

MySQL8.0开始TempTable引擎作为默认的内存内部临时表引擎，代替以前的Memory引擎。新增参数internal_tmp_mem_storage_engine用以控制内存内部临时表的引擎，该参数可以设置全局或session级别配置，并且支持动态修改。

TempTable 存储引擎为 VARCHAR 和 VARBINARY 列提供高效的存储，并且从8.0.13开始支持存储Blob列。TempTable将Null标志，数据长度和数据的指针存储在单元格中，每一行维护一个单元格数组。列值按顺序连续存放在单个内存区域中，存储在数组后。每个单元格占16个字节。如内存临时表使用Memory引擎，对 VARCHAR 和 VARBINARY 列转换成定长格式CHAR 和 BINARY，填充到最大列长度存储。

参数temptable_max_ram用来控制TempTable引擎使用的最大内存大小，默认1G，可以动态调整。

参数temptable_use_mmap决定当TempTable存储引擎占用的内存量超过temptable_max_ram时，是在磁盘创建内存映射临时文件还是创建InnoDB临时表。

参数 temptable_max_mmap确定磁盘创建内存映射临时文件的大小，超过该大小后将会创建InnoDB临时表。temptable_use_mmap=OFF 与 temptable_max_mmap=0 等价

内存映射临时文件的存储路径受参数tmpdir控制，当创建和打开后将会立刻删除，无法在tmpdir定义的目录中可见。只有当临时文件被TempTable 存储引擎关闭，或者mysqld进程关闭后占用的空间才会被回收。需要注意的是，数据不会在内存和内存映射临时文件间移动。如果内存用量没有超过temptable_max_ram，则新数据写入内存，反之亦然。例如表的数据写入临时文件一部分后内存有空闲了，则剩余数据会写入内存。

但设置temptable_use_mmap=OFF 或者 temptable_max_mmap=0后，innodb引擎临时表将作为TempTable存储的溢出存储，超过temptable_max_ram限制的内存表将转换为InnoDB磁盘内部临时表表，并且属于该表的任何行都从内存移动到 InnoDB 磁盘内部临时表。

如internal_tmp_mem_storage_engine=memory，内存中临时表的最大大小由 tmp_table_size 或 max_heap_table_size 值定义，以较小者为准。超过后将转换成磁盘临时表。

磁盘内部临时表

从MySQL8.0.16开始，磁盘内存临时表都使用InnoDB引擎。8.0.15之前参数internal_tmp_disk_storage_engine可以控制磁盘内部临时表的引擎。注意MyISAM不支持CTE。

如果内部临时表占用磁盘空间较多，可以调整temptable_max_mmap控制内存映射临时文件的大小，也可以调整innodb_temp_data_file_path限制innodb临时表空间的最大大小。

监控临时表的创建

状态值 Created_tmp_tables 反应Memory引擎的内部临时表创建次数；

状态值 Created_tmp_disk_tables 反应磁盘内部临时表的创建次数。注意：该指标不反应内存映射临时文件的创建。

mysql> show global status like 'Created_tmp_tables';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| Created_tmp_tables | 7     |
+--------------------+-------+
1 row in set (0.00 sec)

mysql> show global status like 'Created_tmp_disk_tables';  
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
+-------------------------+-------+
1 row in set (0.00 sec)
复制

对于TempTable引擎，在performance_schema.memory_summary_global_by_event_name视图中可以根据EVENT_NAME分别查看内存和磁盘使用情况

前提是要打开相关的instrument

#查看instrument状态
mysql> select * from performance_schema.setup_instruments where NAME LIKE 'memory/temptable/%';
+--------------------------------+---------+-------+-------------------+------------+---------------+
| NAME                           | ENABLED | TIMED | PROPERTIES        | VOLATILITY | DOCUMENTATION |
+--------------------------------+---------+-------+-------------------+------------+---------------+
| memory/temptable/physical_disk | YES     | NULL  |                   |          0 | NULL          |
| memory/temptable/physical_ram  | YES     | NULL  | global_statistics |          0 | NULL          |
+--------------------------------+---------+-------+-------------------+------------+---------------+
2 rows in set (0.00 sec)

#开启
UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME LIKE 'memory/temptable/%'; 

#关闭
UPDATE performance_schema.setup_instruments SET ENABLED = 'NO' WHERE NAME LIKE 'memory/temptable/%'; 
复制

查看TempTable引擎内存和磁盘使用情况

#TempTable引擎分配的内存量
mysql> SELECT *  FROM performance_schema.memory_summary_global_by_event_name  WHERE EVENT_NAME = 'memory/temptable/physical_ram'\G            
*************************** 1. row ***************************
                  EVENT_NAME: memory/temptable/physical_ram
                 COUNT_ALLOC: 3                  #分配次数
                  COUNT_FREE: 2                  #free次数
   SUM_NUMBER_OF_BYTES_ALLOC: 3145728            #分配的总字节数
    SUM_NUMBER_OF_BYTES_FREE: 2097152            #清理的总字节数
              LOW_COUNT_USED: 0                  #CURRENT_COUNT_USED值的低水位线
          CURRENT_COUNT_USED: 1                  #当前正在使用的数量 = COUNT_ALLOC - COUNT_FREE
             HIGH_COUNT_USED: 1                  #CURRENT_COUNT_USED值的高水位线
    LOW_NUMBER_OF_BYTES_USED: 0                  #CURRENT_NUMBER_OF_BYTES_USED值的低水位线
CURRENT_NUMBER_OF_BYTES_USED: 1048576            #正在使用的字节数
   HIGH_NUMBER_OF_BYTES_USED: 1048576            #CURRENT_NUMBER_OF_BYTES_USED值的高水位线
1 row in set (0.00 sec)


#TempTable引擎溢出后分配的磁盘内存映射临时文件量
mysql> SELECT *  FROM performance_schema.memory_summary_global_by_event_name  WHERE EVENT_NAME = 'memory/temptable/physical_disk'\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
1 row in set (0.00 sec)
复制

简单测试

查看当前配置

#使用TempTable作为内存内部临时表
mysql> select @@global.internal_tmp_mem_storage_engine;
+------------------------------------------+
| @@global.internal_tmp_mem_storage_engine |
+------------------------------------------+
| TempTable作为内存内部临时表                                |
+------------------------------------------+
1 row in set (0.00 sec)

#溢出后在磁盘创建内存映射临时文件
mysql> select @@global.temptable_use_mmap;
+-----------------------------+
| @@global.temptable_use_mmap |
+-----------------------------+
|                           1 |
+-----------------------------+
1 row in set (0.00 sec)

#TempTablez最大内存 2M
mysql> select @@global.temptable_max_ram;
+----------------------------+
| @@global.temptable_max_ram |
+----------------------------+
|                    2097152 |
+----------------------------+
1 row in set (0.00 sec)

#磁盘内存映射临时文件 4M
mysql> select @@global.temptable_max_mmap;
+-----------------------------+
| @@global.temptable_max_mmap |
+-----------------------------+
|                     4194304 |
+-----------------------------+
1 row in set (0.00 sec)

#InnoDB临时表空间大小14M
mysql> select @@global.innodb_temp_data_file_path;
+-------------------------------------+
| @@global.innodb_temp_data_file_path |
+-------------------------------------+
| ibtmp1:12M:autoextend:max:14M       |
+-------------------------------------+
1 row in set (0.00 sec)
复制

构建一个查询，从执行计划看会使用临时表

mysql> explain select * from sbtest3 a,sbtest4 b,sbtest6 c where a.c=b.c and b.k=c.k order by a.id,c.k\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: a
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 98704
     filtered: 100.00
        Extra: Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: b
   partitions: NULL
         type: ALL
possible_keys: k_4
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 98704
     filtered: 10.00
        Extra: Using where; Using join buffer (hash join)
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: c
   partitions: NULL
         type: ref
possible_keys: k_6
          key: k_6
      key_len: 4
          ref: test_recovery_2.b.k
         rows: 5
     filtered: 100.00
        Extra: NULL
3 rows in set, 1 warning (0.00 sec)
复制

多执行几次查询后再检查下内存表的创建情况

#TempTable共分配了10次内存表，最大内存使用量达到上限temptable_max_ram 2M
mysql> SELECT *  FROM performance_schema.memory_summary_global_by_event_name  WHERE EVENT_NAME = 'memory/temptable/physical_ram'\G
*************************** 1. row ***************************
                  EVENT_NAME: memory/temptable/physical_ram
                 COUNT_ALLOC: 10
                  COUNT_FREE: 8
   SUM_NUMBER_OF_BYTES_ALLOC: 10485760
    SUM_NUMBER_OF_BYTES_FREE: 8388608
              LOW_COUNT_USED: 0
          CURRENT_COUNT_USED: 2
             HIGH_COUNT_USED: 2
    LOW_NUMBER_OF_BYTES_USED: 0
CURRENT_NUMBER_OF_BYTES_USED: 2097152
   HIGH_NUMBER_OF_BYTES_USED: 2097152
1 row in set (0.00 sec)



#TempTable共分配了35次磁盘空间存储临时内存映射文件，最分配大小为4M，达到temptable_max_mmap参数定义上上限4M
mysql> SELECT *  FROM performance_schema.memory_summary_global_by_event_name  WHERE EVENT_NAME = 'memory/temptable/physical_disk'\G
*************************** 1. row ***************************
                  EVENT_NAME: memory/temptable/physical_disk
                 COUNT_ALLOC: 35
                  COUNT_FREE: 34
   SUM_NUMBER_OF_BYTES_ALLOC: 56623104
    SUM_NUMBER_OF_BYTES_FREE: 55574528
              LOW_COUNT_USED: 0
          CURRENT_COUNT_USED: 1
             HIGH_COUNT_USED: 3
    LOW_NUMBER_OF_BYTES_USED: 0
CURRENT_NUMBER_OF_BYTES_USED: 1048576
   HIGH_NUMBER_OF_BYTES_USED: 4194304
1 row in set (0.00 sec)



#有3次InnoDB磁盘临时表创建
mysql> show global status like 'Created_tmp_%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 3     |
| Created_tmp_files       | 2595  |
| Created_tmp_tables      | 53    |
+-------------------------+-------+
3 rows in set (0.00 sec)


#查看innodb临时表空间，空间大小还未达最大值
mysql> 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: /data/mysql/data/ibtmp1
TABLESPACE_NAME: innodb_temporary
         ENGINE: InnoDB
   INITIAL_SIZE: 12582912         #初始化的文件大小
 TotalSizeBytes: 12582912         
      DATA_FREE: 6291456          #整个表空间的可用空间总量
   MAXIMUM_SIZE: 14680064         #最大文件大小
1 row in set (0.01 sec)
复制