〇前言：

在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

5.7版本之後，在I_S裡增加了一個表來統計該表的後設資料資訊INNODB_TEMP_TABLE_INFO。
可以透過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儲存的是compressed 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檔案的大小變化：

對compress_table表：

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

該引數也不是5.7的新夥計，預設值取決於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

MySQL 5.7 新特性共享臨時表空間及臨時表改進

相關文章

MySQL 5.7 新特性 共享臨時表空間及臨時表改進

相關文章

MySQL 5.7 新特性共享臨時表空間及臨時表改進