MySQL5.7新特性共享臨時表空間及臨時表改進

〇前言：

在MySQL5.6引用了獨立undo tablespace之后，MySQL5.7在temporary tablespace上做了改進。
已經(jīng)實現(xiàn)將temporary tablespace從ibdata（系統(tǒng)表空間文件）中分離。
并且可以重啟重置大小，避免出現(xiàn)像ibdata難以釋放的問題。

但下面所有的討論只針對InnoDB，并且指定了innodb_file_per_table，所用版本為MySQL 5.7.x

〇新特性 · 共享臨時表空間(shared temporary tablespace)：

共享臨時表空間出現(xiàn)于MySQL 5.7.1，為的是將臨時表空間從系統(tǒng)表空間(system tablespace)文件中獨立出來。該共享臨時表空間用于存儲非壓縮InnoDB臨時表(non-compressed InnoDB temporary tables)、關(guān)系對象(related objects)、回滾段(rollback segment)等數(shù)據(jù)。更多信息可以參考【MySQL 5.7 Reference Manual 8.4.4 Internal Temporary Table Use in MySQL】

因為存放的數(shù)據(jù)特殊性，不會參與crash recovery，因此無需記錄redo log。

該共享臨時表空間默認(rèn)大小為12MB。在實例關(guān)閉之后，將會被刪除。在實例啟動時則會被創(chuàng)建。

默認(rèn)的，該共享臨時表空間存放在innodb_data_home_dir中的ibtmp1里，而innodb_data_home_dir默認(rèn)為datadir。
所以一般該ibtmp1存放在datadir下，顯然，其路徑與共享表空間的路徑一樣，取決于innodb_data_home_dir。

新增參數(shù)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

該參數(shù)默認(rèn)出現(xiàn)于5.7.1，靜態(tài)，默認(rèn)值為ibtmp1:12M:autoextend。

〇新特性 · InnoDB臨時表統(tǒng)計信息優(yōu)化

因為臨時表特性，是無法在SHOW TABLES;與通過information_schema.TABLES查詢到其元數(shù)據(jù)信息的。
老版本可能只能通過一些比較麻煩的方法來查看：
比如SHOW CREATE TABLE tmp_a\G

5.7版本之后，在I_S里增加了一個表來統(tǒng)計該表的元數(shù)據(jù)信息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 rowin set (0.00 sec)

字段介紹：
TABLE_ID：表id
NAME：表名，這個名字對應(yīng)的表結(jié)構(gòu)為$NAME.frm，若該表為壓縮臨時表，對應(yīng)的數(shù)據(jù)文件為$NAME.ibd，反之則無。
N_COLS：列的數(shù)量，1個被我顯示創(chuàng)建的列，其他3個為InnoDB的隱藏列(DB_ROW_ID, DB_TRX_ID, and DB_ROLL_PTR)
SPACE：臨時表的表空間id，總是非0，并且隨實例重啟動態(tài)變化，
PER_TABLE_TABLESPACE：如果為TRUE，則表明該臨時表有自己的臨時表空間（有自己的ibd文件），如果為FALSE，則表明該臨時表用共享表空間。
IS_COMPRESSED：如果為TRUE，則表明該表被壓縮，反之則未壓縮。

〇新特性 · innodb_tmpdir

出現(xiàn)在5.7.11以后的版本，用于在做某些Online DDL時存放臨時數(shù)據(jù)。
innodb_tmpdir的值覆蓋tmpdir，此特性只針對于Online DDL生效。

〇共享臨時表空間與tmpdir對比：

通過CREATE TEMPORARY TABLE ... 創(chuàng)建的表，該表定義會放在tmpdir下，默認(rèn)為/tmp

tmpdir不是個新參數(shù)，一般也不需要指定，默認(rèn)值為/tmp，此處還是提及并與共享臨時表空間做一個對比。
tmpdir參數(shù)用于指定臨時文件(temporary files)和臨時表(temporary tables)的存放目錄。
可以設(shè)定為一個集合并做輪詢調(diào)度（用:分割），如果要用，建議指定多個磁盤目錄以提高性能。

此外，對于顯式創(chuàng)建的臨時表（create temporary table）：
與共享臨時表空間不同的是，tmpdir存儲的是compressed InnoDB temporary tables的臨時獨立表空間。

以下做一個測試，驗證一下：

參數(shù)檢查：

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 rowin set (0.00 sec)

先創(chuàng)建兩張臨時表，引擎均為默認(rèn)的InnoDB，其中第一張指定行格式為COMRESSED，第二張不壓縮：

root@localhost [test]> CREATE TEMPORARYTABLE compress_table(idint, namechar(255)) ROW_FORMAT=COMPRESSED;
Query OK, 0 rows affected(0.02 sec)
root@localhost [test]> CREATE TEMPORARYTABLE uncompress_table(idint, namechar(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 TEMPORARYTABLE `compress_table` (
`id` int(11) DEFAULT NULL,
`name` char(255) DEFAULT NULL
) ENGINE=InnoDBDEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED
1 rowin set (0.00 sec)
root@localhost [test]> SHOW CREATE TABLE uncompress_table\G
*************************** 1. row***************************
Table: uncompress_table
Create Table: CREATE TEMPORARYTABLE `uncompress_table` (
`id` int(11) DEFAULT NULL,
`name` char(255) DEFAULT NULL
) ENGINE=InnoDBDEFAULT CHARSET=latin1
1 rowin 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 rowsin set (0.00 sec)

根據(jù)TABLE_ID和IS_COMPRESSED和PER_TABLE_TABLESPACE參數(shù)
可得知，#sqlb48_3_0為compress_table，#sqlb48_3_1為uncompress_table

創(chuàng)建好了之后，檢查/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

可以發(fā)現(xiàn)，兩張顯式創(chuàng)建的臨時表的【表定義文件】都被放到了tmpdir下。
此外，#sqlb48_3_0也就是IS_COMPRESSED為TRUE的那張壓縮表，ibd文件也放在了tmpdir文件中。

那么理論上，#sqlb48_3_1這張未壓縮的表的數(shù)據(jù)放到了ibtmp1中，也就是放到了共享臨時表空間中。

簡單驗證一下，驗證思路為兩張表插入大量數(shù)據(jù)。
并分別檢查ibtmp1文件和#sqlb48_3_0.ibd文件的大小變化：

對compress_table表：

root@localhost [test]> INSERT INTO compress_tableSELECT id, nameFROM a limit 50000;
Query OK, 50000 rows affected(1.20 sec)
Records: 50000 Duplicates: 0 Warnings: 0
（a表為一個測試數(shù)據(jù)用表）
root@localhost [test]> \! ls -l/tmp
-rw-r----- 1 root root 11534336Jun 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表為一個測試數(shù)據(jù)用表）
root@localhost [test]> \! ls -l /tmp/*.ibd
-rw-r----- 1 root root 14680064Jun 20 16:55 #sqlb48_3_0.ibd

可以發(fā)現(xiàn)，針對壓縮的InnoDB臨時表，其數(shù)據(jù)放在tmpdir下的ibd文件中

再簡單測試一下非壓縮的InnoDB臨時表：

root@localhost [test]> \! ls -l/data/mysql-data/mysql57-3357/datadir/ibtmp1
-rw-r----- 1 root root 12582912Jun 20 16:57/data/mysql-data/mysql57-3357/datadir/ibtmp1
root@localhost [test]> INSERT INTO uncompress_tableSELECT id, nameFROM 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 79691776Jun 20 17:02/data/mysql-data/mysql57-3357/datadir/ibtmp1

顯然，非壓縮的InnoDB臨時表將數(shù)據(jù)存放在了共享臨時表空間。

tmpdir下的東西和共享臨時表空間最大的共同點以及特性就是，實例關(guān)閉之后，將會被刪除。

〇 slave_load_tmpdir

該參數(shù)也不是5.7的新伙計，默認(rèn)值取決于tmpdir的參數(shù)。
用于存放slave上產(chǎn)生的特殊的臨時文件：
在master上出現(xiàn)LOAD DATA INFILE ... 時，被記錄到binlog并發(fā)送給slave，在SQL thread從relaylog提取數(shù)據(jù)時，寫入指定的目錄下，然后執(zhí)行LOAD DATA LOCAL INFILE ...，結(jié)束之后則會刪掉這個文件。
增加這個參數(shù)是為了復(fù)制的可靠性和數(shù)據(jù)一致性。
如果默認(rèn)放在tmpdir下，如果此時遭遇重啟，文件丟失，則會導(dǎo)致復(fù)制失敗。
如果master有使用這樣的語句，建議將該目錄指定在基于可靠存儲設(shè)備上。

〇可能遇到的問題：

MySQL 5.7.6以后，開始支持32KB和64KB的page size，若將page size修改為32或者64KB，則不能使用ROW_FORMAT=COMPRESSED，該行格式能支持的最大page size為16KB。

若要保證ROW_FORMAT=COMPRESSED生效，innodb_file_format必須設(shè)置為Barracuda。

〇參考文檔：

MySQL 5.7 Reference Manual5.1.3 Server Option and Variable Reference
MySQL 5.7 Reference Manual14.4.12 Temporary Tablespace
MySQL 5.7 Reference Manual14.15.7 InnoDB INFORMATION_SCHEMA Temporary Table Information Table

分享標(biāo)題：MySQL5.7新特性共享臨時表空間及臨時表改進
本文路徑：http://m.rwnh.cn/article26/jcjicg.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供品牌網(wǎng)站建設(shè)、建站公司、網(wǎng)站制作、、網(wǎng)頁設(shè)計公司、軟件開發(fā)

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源：創(chuàng)新互聯(lián)

猜你還喜歡下面的內(nèi)容

中文字幕日韩精品一区二区免费_精品一区二区三区国产精品无卡在_国精品无码专区一区二区三区_国产αv三级中文在线

MySQL5.7新特性共享臨時表空間及臨時表改進