作者：Przemysław Malkowski
译者：姚远
如果您曾经不得不处理与临时表相关的性能和/或磁盘空间问题，我打赌您最终会发现自己很困惑。根据临时表的类型、设置和所使用的MySQL版本，可能会有很多情况。由于几个原因，我们已经观察到在这个问题上有一个相当长的演变。其中之一是需要完全消除使用过时的MyISAM引擎的需要，同时引入更高性能和更可靠的替代方案。另一组改进是与InnoDB相关的，需要降低使用该引擎的临时表的开销。
出于这个原因，我决定将它们收集成一个摘要，这可能有助于解决它们的使用问题。由于MySQL主要版本之间的巨大变化，我按照MySQL的版本号来划分这篇文章。
MySQL 5.6
———————
(如果您仍在使用该版本，建议您尽快升级。)
用户创建的临时表
当使用CREATE TEMPORARY TABLE子句创建表时，如果没有明确定义，它将使用default_tmp_storage_engine定义的引擎(默认为InnoDB )，并将存储在tmpdir变量定义的目录中。一个例子可能是这样的:
mysql > create temporary table tmp1 (id int, a varchar(10));Query OK, 0 rows affected (0.02 sec) mysql > show create table tmp1\G*************************** 1. row ***************************Table: tmp1Create Table: CREATE TEMPORARY TABLE `tmp1` (`id` int(11) DEFAULT NULL,`a` varchar(10) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin11 row in set (0.00 sec)
但是如何找到在磁盘上创建的存储这个表数据的文件呢？虽然下面的查询可能有所帮助:
mysql > select table_id,space,name,path from information_schema.INNODB_SYS_DATAFILES join information_schema.INNODB_SYS_TABLES using (space) where name like '%tmp%'\G*************************** 1. row ***************************table_id: 21space: 7name: tmp/#sql11765a_2_1path: /data/sandboxes/msb_5_6_51/tmp/#sql11765a_2_1.ibd1 row in set (0.00 sec)
我们在这里看不到原始的表名。即使通过查看缓冲池，我们仍然没有真正的名称:
mysql > select TABLE_NAME from information_schema.INNODB_BUFFER_PAGE where table_name like '%tmp%';+-------------------------+| TABLE_NAME |+-------------------------+| `tmp`.`#sql11765a_2_1` |+-------------------------+1 row in set (0.07 sec)
MySQL 5.6版本的 Percona Server for MySQL提供了一个可用的视图information_schema.GLOBAL_TEMPORARY_TABLES。有了它，我们可以设计一个查询来提供更多的信息:
mysql > select SPACE,TABLE_SCHEMA,TABLE_NAME,ENGINE,g.NAME,PATH from information_schema.GLOBAL_TEMPORARY_TABLES g LEFT JOIN information_schema.INNODB_SYS_TABLES s ON s.NAME LIKE CONCAT('%', g.name, '%') LEFT JOIN information_schema.INNODB_SYS_DATAFILES USING(SPACE)\G*************************** 1. row ***************************SPACE: 16TABLE_SCHEMA: testTABLE_NAME: tmp1ENGINE: InnoDBNAME: #sql12c75d_2_0PATH: /data/sandboxes/msb_ps5_6_47/tmp/#sql12c75d_2_0.ibd*************************** 2. row ***************************SPACE: NULLTABLE_SCHEMA: testTABLE_NAME: tmp3ENGINE: MEMORYNAME: #sql12c75d_2_2PATH: NULL*************************** 3. row ***************************SPACE: NULLTABLE_SCHEMA: testTABLE_NAME: tmp2ENGINE: MyISAMNAME: #sql12c75d_2_1PATH: NULL3 rows in set (0.00 sec)
因此，至少对于InnoDB临时表，我们可以将确切的表名与文件路径相关联。
内部临时表
这些是MySQL在执行查询的过程中创建的。我们不能访问这样的表，但是让我们看看如何研究它们的用法。
只要这类表的大小不超过tmp_table_size或max_heap_table_size，并且没有使用TEXT/BLOB列，就会在内存中创建该类型(使用Memory引擎)。如果这样一个表必须存储在磁盘上，那么在MySQL 5.6中，它将使用MyISAM存储，还将tmpdir用作一个位置。举个简单的例子，在10M行的sysbench表上，查询产生了一个大的内部临时表:
mysql > SELECT pad, COUNT(*) FROM sbtest1 GROUP BY pad;
我们可以看到相关文件在增长:
$ ls -lh /data/sandboxes/msb_5_6_51/tmp/total 808M-rw-rw---- 1 przemek przemek 329M Sep 29 23:24 '#sql_11765a_0.MYD'-rw-rw---- 1 przemek przemek 479M Sep 29 23:24 '#sql_11765a_0.MYI'
但是，可能很难将特定的临时表与其客户端连接相关联。我找到的唯一信息是：
mysql > select FILE_NAME,EVENT_NAME from performance_schema.file_summary_by_instance where file_name like '%tmp%' \G*************************** 1. row ***************************FILE_NAME: /data/sandboxes/msb_5_6_51/tmp/Innodb Merge Temp FileEVENT_NAME: wait/io/file/innodb/innodb_temp_file*************************** 2. row ***************************FILE_NAME: /data/sandboxes/msb_5_6_51/tmp/#sql_11765a_0.MYIEVENT_NAME: wait/io/file/myisam/kfile*************************** 3. row ***************************FILE_NAME: /data/sandboxes/msb_5_6_51/tmp/#sql_11765a_0.MYDEVENT_NAME: wait/io/file/myisam/dfile3 rows in set (0.00 sec)
MySQL 5.7
———————
用户创建的临时表
如前所述，default_tmp_storage_engine变量决定了所使用的引擎。但是这里发生了两个变化。InnoDB临时表现在使用一个公用的表空间——ibtmp1，除非它被压缩。此外，还多了一个视图INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO。鉴于此，我们可以获得如下信息:
mysql > select name, FILE_NAME, FILE_TYPE, TABLESPACE_NAME, SPACE, PER_TABLE_TABLESPACE, IS_COMPRESSED from INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO join INFORMATION_SCHEMA.FILES on FILE_ID=SPACE\G*************************** 1. row ***************************name: #sql12cf58_2_5FILE_NAME: ./ibtmp1FILE_TYPE: TEMPORARYTABLESPACE_NAME: innodb_temporarySPACE: 109PER_TABLE_TABLESPACE: FALSEIS_COMPRESSED: FALSE*************************** 2. row ***************************name: #sql12cf58_2_4FILE_NAME: /data/sandboxes/msb_ps5_7_33/tmp/#sql12cf58_2_4.ibdFILE_TYPE: TEMPORARYTABLESPACE_NAME: innodb_file_per_table_110SPACE: 110PER_TABLE_TABLESPACE: TRUEIS_COMPRESSED: TRUE2 rows in set (0.01 sec)
但同样的，为了与表名相关联，需要使用 Percona Server for MySQL变种:
mysql > select g.TABLE_SCHEMA, g.TABLE_NAME, name, FILE_NAME, FILE_TYPE, TABLESPACE_NAME, SPACE, PER_TABLE_TABLESPACE, IS_COMPRESSED from INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO join INFORMATION_SCHEMA.FILES on FILE_ID=SPACE join information_schema.GLOBAL_TEMPORARY_TABLES g using (name)\G*************************** 1. row ***************************TABLE_SCHEMA: testTABLE_NAME: tmp1name: #sql12cf58_2_5FILE_NAME: ./ibtmp1FILE_TYPE: TEMPORARYTABLESPACE_NAME: innodb_temporarySPACE: 109PER_TABLE_TABLESPACE: FALSEIS_COMPRESSED: FALSE*************************** 2. row ***************************TABLE_SCHEMA: testTABLE_NAME: tmp3name: #sql12cf58_2_4FILE_NAME: /data/sandboxes/msb_ps5_7_33/tmp/#sql12cf58_2_4.ibdFILE_TYPE: TEMPORARYTABLESPACE_NAME: innodb_file_per_table_110SPACE: 110PER_TABLE_TABLESPACE: TRUEIS_COMPRESSED: TRUE2 rows in set (0.01 sec)
或者，也可以查看MyISAM和相关的frm文件，我们可以使用：
mysql > SELECT g.TABLE_SCHEMA, g.TABLE_NAME, NAME, f.FILE_NAME, g.ENGINE, TABLESPACE_NAME, PER_TABLE_TABLESPACE, SPACE FROM information_schema.GLOBAL_TEMPORARY_TABLES g join performance_schema.file_instances f ON FILE_NAME LIKE CONCAT('%', g.name, '%') left join INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO using (name) left join INFORMATION_SCHEMA.FILES fl on space=FILE_ID order by table_name\G*************************** 1. row ***************************TABLE_SCHEMA: testTABLE_NAME: tmp1NAME: #sql12cf58_2_5FILE_NAME: /data/sandboxes/msb_ps5_7_33/tmp/#sql12cf58_2_5.frmENGINE: InnoDBTABLESPACE_NAME: innodb_temporaryPER_TABLE_TABLESPACE: FALSESPACE: 109*************************** 2. row ***************************TABLE_SCHEMA: testTABLE_NAME: tmp2NAME: #sql12cf58_2_6FILE_NAME: /data/sandboxes/msb_ps5_7_33/tmp/#sql12cf58_2_6.MYDENGINE: MyISAMTABLESPACE_NAME: NULLPER_TABLE_TABLESPACE: NULLSPACE: NULL*************************** 3. row ***************************TABLE_SCHEMA: testTABLE_NAME: tmp2NAME: #sql12cf58_2_6FILE_NAME: /data/sandboxes/msb_ps5_7_33/tmp/#sql12cf58_2_6.MYIENGINE: MyISAMTABLESPACE_NAME: NULLPER_TABLE_TABLESPACE: NULLSPACE: NULL*************************** 4. row ***************************TABLE_SCHEMA: testTABLE_NAME: tmp2NAME: #sql12cf58_2_6FILE_NAME: /data/sandboxes/msb_ps5_7_33/tmp/#sql12cf58_2_6.frmENGINE: MyISAMTABLESPACE_NAME: NULLPER_TABLE_TABLESPACE: NULLSPACE: NULL*************************** 5. row ***************************TABLE_SCHEMA: testTABLE_NAME: tmp3NAME: #sql12cf58_2_4FILE_NAME: /data/sandboxes/msb_ps5_7_33/tmp/#sql12cf58_2_4.frmENGINE: InnoDBTABLESPACE_NAME: innodb_file_per_table_110PER_TABLE_TABLESPACE: TRUESPACE: 110*************************** 6. row ***************************TABLE_SCHEMA: testTABLE_NAME: tmp3NAME: #sql12cf58_2_4FILE_NAME: /data/sandboxes/msb_ps5_7_33/tmp/#sql12cf58_2_4.ibdENGINE: InnoDBTABLESPACE_NAME: innodb_file_per_table_110PER_TABLE_TABLESPACE: TRUESPACE: 1106 rows in set (0.01 sec)
内部临时表
对于5.7中的内部临时表，在内存中的临时表方面也是类似的。但是磁盘上临时表的默认引擎是通过一个新变量定义的：internal_tmp_disk_storage_engine，它现在也默认为InnoDB，并且ibtmp1表空间也用于存储其内容。
对这个共享临时表空间的了解非常有限。我们可以检查它的大小以及当前有多少可用空间。一个示例视图是在大量查询进行期间拍摄的:
mysql > select FILE_NAME, FILE_TYPE, TABLESPACE_NAME, ENGINE, TOTAL_EXTENTS, FREE_EXTENTS, EXTENT_SIZE/1024/1024 as 'extent in MB', MAXIMUM_SIZE from INFORMATION_SCHEMA.FILES where file_name like '%ibtmp%'\G*************************** 1. row ***************************FILE_NAME: ./ibtmp1FILE_TYPE: TEMPORARYTABLESPACE_NAME: innodb_temporaryENGINE: InnoDBTOTAL_EXTENTS: 588FREE_EXTENTS: 1extent in MB: 1.00000000MAXIMUM_SIZE: NULL1 row in set (0.00 sec)
在这个10M行的sysbench表上查询完成后，我们可以看到大部分空间被释放了(FREE_EXTENTS):
mysql > select FILE_NAME, FILE_TYPE, TABLESPACE_NAME, ENGINE, TOTAL_EXTENTS, FREE_EXTENTS, EXTENT_SIZE/1024/1024 as 'extent in MB', MAXIMUM_SIZE from INFORMATION_SCHEMA.FILES where file_name like '%ibtmp%'\G*************************** 1. row ***************************FILE_NAME: ./ibtmp1FILE_TYPE: TEMPORARYTABLESPACE_NAME: innodb_temporaryENGINE: InnoDBTOTAL_EXTENTS: 780FREE_EXTENTS: 764extent in MB: 1.00000000MAXIMUM_SIZE: NULL1 row in set (0.00 sec)
但是，除非MySQL重新启动，否则表空间不会被截断（truncate）：
$ ls -lh msb_5_7_35/data/ibtmp*-rw-r----- 1 przemek przemek 780M Sep 30 19:50 msb_5_7_35/data/ibtmp1
查看写入活动(单个查询的写入活动可能比总的写入活动大小增长高得多):
mysql > select FILE_NAME, SUM_NUMBER_OF_BYTES_WRITE/1024/1024/1024 as GB_written from performance_schema.file_summary_by_instance where file_name like '%ibtmp%' \G*************************** 1. row ***************************FILE_NAME: /data/sandboxes/msb_5_7_35/data/ibtmp1GB_written: 46.9259338378911 row in set (0.00 sec)
MySQL 8.0
———————
为简单起见，让我们跳过8.0.16之前的工作方式，只讨论自那以后的工作方式，因为在这方面的变化非常显著:
- internal _ tmp _ disk _ storage _ engine变量已被删除，无法再对内部临时表使用MyISAM引擎。
- 共享的ibtmp1表空间不再用于任何一种临时表类型。（它存放用户创建临时表的回滚段——译者注）
- 引入了一个新的会话临时表空间池来处理磁盘上的用户和内部临时表，默认情况下，该池位于主数据目录中。
- 新的TempTable引擎用于内存表对内存和磁盘上的映射文件表的空间的管理。
用户创建的临时表
创建一个临时表的例子：
mysql > create temporary table tmp1 (id int, a varchar(10));Query OK, 0 rows affected (0.00 sec) mysql > select * from information_schema.INNODB_TEMP_TABLE_INFO;+----------+----------------+--------+------------+| TABLE_ID | NAME | N_COLS | SPACE |+----------+----------------+--------+------------+| 1089 | #sqlbbeb3_a_12 | 5 | 4243767289 |+----------+----------------+--------+------------+1 row in set (0.00 sec)
我们可以通过查看空间编号来关联该池中使用了哪个文件:
mysql > select * from INFORMATION_SCHEMA.INNODB_SESSION_TEMP_TABLESPACES ;+----+------------+----------------------------+-------+----------+-----------+| ID | SPACE | PATH | SIZE | STATE | PURPOSE |+----+------------+----------------------------+-------+----------+-----------+| 10 | 4243767290 | ./#innodb_temp/temp_10.ibt | 81920 | ACTIVE | INTRINSIC || 10 | 4243767289 | ./#innodb_temp/temp_9.ibt | 98304 | ACTIVE | USER || 0 | 4243767281 | ./#innodb_temp/temp_1.ibt | 81920 | INACTIVE | NONE || 0 | 4243767282 | ./#innodb_temp/temp_2.ibt | 81920 | INACTIVE | NONE || 0 | 4243767283 | ./#innodb_temp/temp_3.ibt | 81920 | INACTIVE | NONE || 0 | 4243767284 | ./#innodb_temp/temp_4.ibt | 81920 | INACTIVE | NONE || 0 | 4243767285 | ./#innodb_temp/temp_5.ibt | 81920 | INACTIVE | NONE || 0 | 4243767286 | ./#innodb_temp/temp_6.ibt | 81920 | INACTIVE | NONE || 0 | 4243767287 | ./#innodb_temp/temp_7.ibt | 81920 | INACTIVE | NONE || 0 | 4243767288 | ./#innodb_temp/temp_8.ibt | 81920 | INACTIVE | NONE |+----+------------+----------------------------+-------+----------+-----------+10 rows in set (0.00 sec)
但同样的，没有办法查找表名。幸运的是 Percona Server for MySQL,仍然有GLOBAL_TEMPORARY_TABLES表，因此使用三个可用的系统视图，我们可以获得关于使用各种引擎的用户创建的临时表的更多信息，如下所示:
mysql > SELECT SESSION_ID, SPACE, PATH, TABLE_SCHEMA, TABLE_NAME, SIZE, DATA_LENGTH, INDEX_LENGTH, ENGINE, PURPOSE FROM information_schema.GLOBAL_TEMPORARY_TABLES LEFT JOIN information_schema.INNODB_TEMP_TABLE_INFO USING(NAME) LEFT JOIN INFORMATION_SCHEMA.INNODB_SESSION_TEMP_TABLESPACES USING(SPACE)\G*************************** 1. row *************************** SESSION_ID: 10 SPACE: 4243767290 PATH: ./#innodb_temp/temp_10.ibtTABLE_SCHEMA: test TABLE_NAME: tmp3 SIZE: 98304DATA_LENGTH: 16384INDEX_LENGTH: 0 ENGINE: InnoDB PURPOSE: USER*************************** 2. row *************************** SESSION_ID: 13 SPACE: NULL PATH: NULLTABLE_SCHEMA: test TABLE_NAME: tmp41 SIZE: NULLDATA_LENGTH: 24INDEX_LENGTH: 1024 ENGINE: MyISAM PURPOSE: NULL*************************** 3. row *************************** SESSION_ID: 13 SPACE: NULL PATH: NULLTABLE_SCHEMA: test TABLE_NAME: tmp40 SIZE: NULLDATA_LENGTH: 128256INDEX_LENGTH: 0 ENGINE: MEMORY PURPOSE: NULL*************************** 4. row *************************** SESSION_ID: 13 SPACE: 4243767287 PATH: ./#innodb_temp/temp_7.ibtTABLE_SCHEMA: test TABLE_NAME: tmp33 SIZE: 98304DATA_LENGTH: 16384INDEX_LENGTH: 0 ENGINE: InnoDB PURPOSE: USER4 rows in set (0.01 sec)
类似于ibtmp1，这些表空间在MySQL重启时不会被截断。
从上面我们可以看到，用户连接10有一个打开的InnoDB临时表，连接13有三个使用三个不同引擎的临时表。
内部临时表
当大量查询在连接10中运行时，我们可以获得以下视图:
mysql > show processlist\G...*************************** 2. row *************************** Id: 10 User: msandbox Host: localhost db: testCommand: Query Time: 108 State: converting HEAP to ondisk Info: SELECT pad, COUNT(*) FROM sbtest1 GROUP BY pad 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: 2 COUNT_FREE: 0 SUM_NUMBER_OF_BYTES_ALLOC: 1073741824 SUM_NUMBER_OF_BYTES_FREE: 0 LOW_COUNT_USED: 0 CURRENT_COUNT_USED: 2 HIGH_COUNT_USED: 2 LOW_NUMBER_OF_BYTES_USED: 0CURRENT_NUMBER_OF_BYTES_USED: 1073741824 HIGH_NUMBER_OF_BYTES_USED: 1073741824*************************** 2. row *************************** EVENT_NAME: memory/temptable/physical_ram COUNT_ALLOC: 12 COUNT_FREE: 1 SUM_NUMBER_OF_BYTES_ALLOC: 1074790400 SUM_NUMBER_OF_BYTES_FREE: 1048576 LOW_COUNT_USED: 0 CURRENT_COUNT_USED: 11 HIGH_COUNT_USED: 11 LOW_NUMBER_OF_BYTES_USED: 0CURRENT_NUMBER_OF_BYTES_USED: 1073741824 HIGH_NUMBER_OF_BYTES_USED: 10737418242 rows in set (0.00 sec) mysql > select * from INFORMATION_SCHEMA.INNODB_SESSION_TEMP_TABLESPACES where id=10\G*************************** 1. row *************************** ID: 10 SPACE: 4243767290 PATH: ./#innodb_temp/temp_10.ibt SIZE: 2399141888 STATE: ACTIVEPURPOSE: INTRINSIC*************************** 2. row *************************** ID: 10 SPACE: 4243767289 PATH: ./#innodb_temp/temp_9.ibt SIZE: 98304 STATE: ACTIVEPURPOSE: USER2 rows in set (0.00 sec)
从上面，我们可以看到查询创建了一个巨大的临时表，它首先超过了temptable_max_ram变量，并在一个mmap映射文件(仍然是temptable引擎)中继续增长，但是当到达temptable_max_mmap时，该表必须转换为磁盘上的InnoDB固有表。在这种情况下，使用了相同的临时InnoDB表池，但是我们可以看到目的信息，这取决于表是外部的(用户创建的)还是内部的。
映射的文件在文件系统中不可见，因为它处于删除状态，但是可以使用lsof查看：
mysqld 862655 przemek 52u REG 253,3 133644288 52764900 /data/sandboxes/msb_ps8_0_23/tmp/mysql_temptable.8YIGV8 (deleted)
需要注意的是，只要没有超过mmapped 空间，Created_tmp_disk_tables计数器就不会递增，即使在磁盘上创建了一个文件。此外，在Percona Server for MySQL中，扩展的慢速日志没有计算使用TempTable引擎时临时表的大小（https://jira.percona.com/browse/PS-5168），它显示Tmp_table_sizes: 0。在某些用例中，报告了关于TempTable的问题。
internal_tmp_mem_storage_engine变量控制着内存引擎：mysql> show variables like 'internal%';+---------------------------------+-----------+| Variable_name | Value |+---------------------------------+-----------+| internal_tmp_mem_storage_engine | TempTable |+---------------------------------+-----------+1 row in set (0.00 sec)
如果需要，可以通过internal_tmp_mem_storage_engine变量切换回旧的内存引擎。
原文地址：https://www.percona.com/blog/temporary-tables-in-mysql-never-ending-story/
