共计 6683 个字符，预计需要花费 17 分钟才能阅读完成。
〇 前言：
在 MySQL 5.6 引用了独立 undo tablespace之后，MySQL 5.7 在 temporary tablespace 上做了改进。
已经实现 将 temporary tablespace从 ibdata（共享表空间文件）中分离。
并且可以重启重置大小，避免出现像 ibdata 难以释放的问题。
但 下面所有的讨论只针对 InnoDB，并且指定了 innodb_file_per_table，所用版本为 MySQL 5.7.x
〇 新特性 · 共享临时表空间 (shared temporary tablespace)：
共享临时表空间出现于 MySQL 5.7.1，为的是将临时表空间从系统表空间 (system tablespace) 文件中独立出来。该共享临时表空间用于存储非压缩 InnoDB 临时表 (non-compressed InnoDB temporary tables)、关系对象(related objects)、回滚段(rollback segment) 等数据。更多信息可以参考【MySQL 5.7 Reference Manual 8.4.4 Internal Temporary Table Use in MySQL】
因为存放的数据特殊性，不会参与 crash recovery，因此无需记录 redo log。
该共享临时表空间默认大小为 12MB。在实例关闭之后，将会被删除。在实例启动时则会被创建。
默认的，该共享临时表空间存放在 innodb_data_home_dir 中的 ibtmp1 里，而 innodb_data_home_dir 默认为 datadir。
所以一般该 ibtmp1 存放在 datadir 下，显然，其路径与共享表空间的路径一样，取决于 innodb_data_home_dir。
新增参数 innodb_temp_data_file_path，通过修改其值可以将该共享临时表空间的文件名，扩展大小做修改。
比如在配置文件中加上 innodb_temp_data_file_path = temp_tablespace:64M:autoextend
那么在启动实例之后，会生成一个大小为 64MB 的 temp_tablespace 文件
-rw-r—– 1 root root 67108864 Jun 20 17:29 temp_tablespace
该参数默认出现于 5.7.1，静态，默认值为 ibtmp1:12M:autoextend。
〇 新特性 · InnoDB 临时表统计信息优化
因为临时表特性，是无法在 SHOW TABLES; 与通过 information_schema.TABLES 查询到其元数据信息的。
老版本可能只能通过一些比较麻烦的方法来查看：
比如 SHOW CREATE TABLE tmp_a\G
可以通过 I_S 来查看该表的定义：
- SELECT * FROM information_schema.INNODB_TEMP_TABLE_INFO;
- +––––––––––+–––––––––––––––+––––––––+–––––––+––––––––––––––––––––––+–––––––––––––––+
- | TABLE_ID | NAME | N_COLS | SPACE | PER_TABLE_TABLESPACE | IS_COMPRESSED |
- +––––––––––+–––––––––––––––+––––––––+–––––––+––––––––––––––––––––––+–––––––––––––––+
- | 68 | #sql2b79_35_0 | 4 | 37 | FALSE | FALSE |
- +––––––––––+–––––––––––––––+––––––––+–––––––+––––––––––––––––––––––+–––––––––––––––+
- 1 row in set (0.00 sec)
字段介绍：
TABLE_ID：表 id
NAME：表名，这个名字对应的表结构为 $NAME.frm，若该表为压缩临时表，对应的数据文件为 $NAME.ibd，反之则无。
N_COLS：列的数量，1 个被我显示创建的列，其他 3 个为 InnoDB 的隐藏列(DB_ROW_ID, DB_TRX_ID, and DB_ROLL_PTR)
SPACE：临时表的表空间 id，总是非 0，并且随实例重启动态变化，
PER_TABLE_TABLESPACE：如果为 TRUE，则表明该临时表有自己的临时表空间（有自己的 ibd 文件），如果为 FALSE，则表明该临时表用共享表空间。
IS_COMPRESSED：如果为 TRUE，则表明该表被压缩，反之则未压缩。
〇 新特性 · innodb_tmpdir
出现在 5.7.11 以后的版本，用于在做某些 Online DDL 时存放临时数据。
innodb_tmpdir 的值覆盖 tmpdir，此特性只针对于 Online DDL 生效。
〇 共享临时表空间与 tmpdir 对比：
通过 CREATE TEMPORARY TABLE … 创建的表，该表定义会放在 tmpdir 下，默认为 /tmp
tmpdir 不是个新参数，一般也不需要指定，默认值为 /tmp，此处还是提及并与共享临时表空间做一个对比。
tmpdir 参数用于指定临时文件 (temporary files) 和临时表 (temporary tables) 的存放目录。
可以设定为一个集合并做轮询调度（用: 分割），如果要用，建议指定多个磁盘目录以提高性能。
此外，对于 显式 创建的临时表 （create temporary table）：
与共享临时表空间不同的是，tmpdir 存储的是 c ompressed InnoDB temporary tables的 临时独立表空间。
以下做一个测试，验证一下：
参数检查：
- SELECT @@innodb_temp_data_file_path, @@innodb_file_per_table, @@tmpdir, @@innodb_data_home_dir;
- +––––––––––––––––––––––––––––––––+–––––––––––––––––––––––––+––––––––––+––––––––––––––––––––––––+
- | @@innodb_temp_data_file_path | @@innodb_file_per_table | @@tmpdir | @@innodb_data_home_dir |
- +––––––––––––––––––––––––––––––––+–––––––––––––––––––––––––+––––––––––+––––––––––––––––––––––––+
- | ibtmp1:12M:autoextend | 1 | /tmp | NULL |
- +––––––––––––––––––––––––––––––––+–––––––––––––––––––––––––+––––––––––+––––––––––––––––––––––––+
- 1 row in set (0.00 sec)
先创建两张临时表，引擎均为默认的 InnoDB，其中第一张指定行格式为 COMRESSED，第二张不压缩：
- root@localhost [test]> CREATE TEMPORARY TABLE compress_table (id int, name char(255)) ROW_FORMAT=COMPRESSED;
- Query OK, 0 rows affected (0.02 sec)
- root@localhost [test]> CREATE TEMPORARY TABLE uncompress_table (id int, name char(255)) ;
- Query OK, 0 rows affected (0.00 sec)
- root@localhost [test]> SHOW CREATE TABLE compress_table\G
- *************************** 1. row ***************************
- Table: compress_table
- Create Table: CREATE TEMPORARY TABLE `compress_table` (
- `id` int(11) DEFAULT NULL,
- `name` char(255) DEFAULT NULL
- ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED
- 1 row in set (0.00 sec)
- root@localhost [test]> SHOW CREATE TABLE uncompress_table\G
- *************************** 1. row ***************************
- Table: uncompress_table
- Create Table: CREATE TEMPORARY TABLE `uncompress_table` (
- `id` int(11) DEFAULT NULL,
- `name` char(255) DEFAULT NULL
- ) ENGINE=InnoDB DEFAULT CHARSET=latin1
- 1 row in set (0.00 sec)
检查一下两张临时表的表定义：
- root@localhost [test]> SELECT * FROM information_schema.INNODB_TEMP_TABLE_INFO;
- +––––––––––+–––––––––––––+––––––––+–––––––+––––––––––––––––––––––+–––––––––––––––+
- | TABLE_ID | NAME | N_COLS | SPACE | PER_TABLE_TABLESPACE | IS_COMPRESSED |
- +––––––––––+–––––––––––––+––––––––+–––––––+––––––––––––––––––––––+–––––––––––––––+
- | 73 | #sqlb48_3_1 | 5 | 58 | FALSE | FALSE |
- | 72 | #sqlb48_3_0 | 5 | 59 | TRUE | TRUE |
- +––––––––––+–––––––––––––+––––––––+–––––––+––––––––––––––––––––––+–––––––––––––––+
- 2 rows in set (0.00 sec)
根据 TABLE_ID 和 IS_COMPRESSED 和 PER_TABLE_TABLESPACE 参数
可得知，#sqlb48_3_0 为 compress_table，#sqlb48_3_1 为 uncompress_table
创建好了之后，检查 /tmp 目录，也就是 tmpdir。
- # ll /tmp/
- total 88
- -rw-r—– 1 root root 8586 Jun 20 16:38 #sqlb48_3_0.frm
- -rw-r—– 1 root root 65536 Jun 20 16:38 #sqlb48_3_0.ibd
- -rw-r—– 1 root root 8586 Jun 20 16:39 #sqlb48_3_1.frm
- -rw——-. 1 root root 0 Jan 3 2014 yum.log
可以发现，两张显式创建的临时表的【表定义文件】都被放到了 tmpdir 下。
此外，#sqlb48_3_0 也就是 IS_COMPRESSED 为 TRUE 的那张压缩表，ibd 文件也放在了 tmpdir 文件中。
那么理论上，#sqlb48_3_1 这张未压缩的表的数据放到了 ibtmp1 中，也就是放到了共享临时表空间中。
简单验证一下，验证思路为两张表插入大量数据。
并分别检查 ibtmp1 文件和 #sqlb48_3_0.ibd 文件的大小变化：
- root@localhost [test]> INSERT INTO compress_table SELECT id, name FROM a limit 50000;
- Query OK, 50000 rows affected (1.20 sec)
- Records: 50000 Duplicates: 0 Warnings: 0
- （a 表为一个测试数据用表）
- root@localhost [test]> \! ls -l /tmp
- -rw-r—– 1 root root 11534336 Jun 20 16:54 #sqlb48_3_0.ibd
- root@localhost [test]> INSERT INTO compress_table SELECT id, name FROM a limit 20000;
- Query OK, 20000 rows affected (0.53 sec)
- Records: 20000 Duplicates: 0 Warnings: 0
- （a 表为一个测试数据用表）
- root@localhost [test]> \! ls -l /tmp/*.ibd
- -rw-r—– 1 root root 14680064 Jun 20 16:55 #sqlb48_3_0.ibd
可以发现，针对压缩的 InnoDB 临时表，其数据放在 tmpdir 下的 ibd 文件中
再简单测试一下非压缩的 InnoDB 临时表：
- root@localhost [test]> \! ls -l /data/mysql-data/mysql57-3357/datadir/ibtmp1
- -rw-r—– 1 root root 12582912 Jun 20 16:57 /data/mysql-data/mysql57-3357/datadir/ibtmp1
- root@localhost [test]> INSERT INTO uncompress_table SELECT id, name FROM a limit 50000;
- Query OK, 50000 rows affected (0.53 sec)
- Records: 50000 Duplicates: 0 Warnings: 0
- root@localhost [test]> \! ls -l /data/mysql-data/mysql57-3357/datadir/ibtmp1
- -rw-r—– 1 root root 79691776 Jun 20 17:02 /data/mysql-data/mysql57-3357/datadir/ibtmp1
显然，非压缩 的 InnoDB 临时表将数据存放在了共享临时表空间。
tmpdir 下的东西和共享临时表空间最大的 共同点 以及特性就是，实例关闭之后，将会被删除。
〇 slave_load_tmpdir
用于存放 slave 上产生的特殊的临时文件：
在 master 上出现 LOAD DATA INFILE … 时，被记录到 binlog 并发送给 slave，在 SQL thread 从 relaylog 提取数据时，写入指定的目录下，然后执行 LOAD DATA LOCAL INFILE …，结束之后则会删掉这个文件。
增加这个参数是为了复制的可靠性和数据一致性。
如果默认放在 tmpdir 下，如果此时遭遇重启，文件丢失，则会导致复制失败。
如果 master 有使用这样的语句，建议将该目录指定在基于可靠存储设备上。
〇 可能遇到的问题：
MySQL 5.7.6 以后，开始支持 32KB 和 64KB 的 page size，若将 page size 修改为 32 或者 64KB，则不能使用 ROW_FORMAT=COMPRESSED，该行格式能支持的最大 page size 为 16KB。
若要保证ROW_FORMAT=COMPRESSED 生效，innodb_file_format 必须设置为Barracuda。
〇 参考文档：
MySQL 5.7 Reference Manual 5.1.3 Server Option and Variable Reference
MySQL 5.7 Reference Manual 14.4.12 Temporary Tablespace
MySQL 5.7 Reference Manual 14.15.7 InnoDB INFORMATION_SCHEMA Temporary Table Information Table
本文永久更新链接地址：http://www.linuxidc.com/Linux/2017-06/145039.htm
