MySQL5.7新特性共享臨時(shí)表空間及臨時(shí)表改進(jìn)



〇 前言：

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

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


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

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

因?yàn)榇娣诺臄?shù)據(jù)特殊性，不會(huì)參與crash recovery，因此無(wú)需記錄redo log。

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

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


新增參數(shù)innodb_temp_data_file_path，通過修改其值可以將該共享臨時(shí)表空間的文件名，擴(kuò)展大小做修改。


比如在配置文件中加上innodb_temp_data_file_path = temp_tablespace:64M:autoextend
那么在啟動(dòng)實(shí)例之后，會(huì)生成一個(gè)大小為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臨時(shí)表統(tǒng)計(jì)信息優(yōu)化

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

5.7版本之后，在I_S里增加了一個(gè)表來統(tǒng)計(jì)該表的元數(shù)據(jù)信息INNODB_TEMP_TABLE_INFO。
可以通過I_S來查看該表的定義：
    
  1. SELECT * FROM information_schema.INNODB_TEMP_TABLE_INFO;
  2. +----------+---------------+--------+-------+----------------------+---------------+
  3. | TABLE_ID| NAME          | N_COLS| SPACE  | PER_TABLE_TABLESPACE| IS_COMPRESSED|
  4. +----------+---------------+--------+-------+----------------------+---------------+
  5. | 68       | #sql2b79_35_0| 4      | 37    | FALSE               | FALSE         |
  6. +----------+---------------+--------+-------+----------------------+---------------+
  7. 1 rowin set (0.00 sec)

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


〇 新特性 · innodb_tmpdir

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


〇 共享臨時(shí)表空間與tmpdir對(duì)比：

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

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

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

以下做一個(gè)測(cè)試，驗(yàn)證一下：

參數(shù)檢查：

  1. SELECT @@innodb_temp_data_file_path, @@innodb_file_per_table, @@tmpdir, @@innodb_data_home_dir;
  2. +--------------------------------+-------------------------+----------+------------------------+
  3. | @@innodb_temp_data_file_path    | @@innodb_file_per_table | @@tmpdir  | @@innodb_data_home_dir |
  4. +--------------------------------+-------------------------+----------+------------------------+
  5. | ibtmp1:12M:autoextend          | 1                       | /tmp     | NULL                     |
  6. +--------------------------------+-------------------------+----------+------------------------+
  7. 1 rowin set (0.00 sec)

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

  1. root@localhost [test]> CREATE TEMPORARYTABLE compress_table(idint, namechar(255)) ROW_FORMAT=COMPRESSED;
  2. Query OK, 0 rows affected(0.02 sec)

  4. root@localhost [test]> CREATE TEMPORARYTABLE uncompress_table(idint, namechar(255)) ;
  5. Query OK, 0 rows affected(0.00 sec)

  7. root@localhost [test]> SHOW CREATE TABLE compress_table\G
  8. *************************** 1. row***************************
  9.        Table: compress_table
  10. Create Table: CREATE TEMPORARYTABLE `compress_table` (
  11.   `id` int(11) DEFAULT NULL,
  12.   `name` char(255) DEFAULT NULL
  13. ) ENGINE=InnoDBDEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED
  14. 1 rowin set (0.00 sec)

  16. root@localhost [test]> SHOW CREATE TABLE uncompress_table\G
  17. *************************** 1. row***************************
  18.        Table: uncompress_table
  19. Create Table: CREATE TEMPORARYTABLE `uncompress_table` (
  20.   `id` int(11) DEFAULT NULL,
  21.   `name` char(255) DEFAULT NULL
  22. ) ENGINE=InnoDBDEFAULT CHARSET=latin1
  23. 1 rowin set (0.00 sec)

檢查一下兩張臨時(shí)表的表定義：

  1. root@localhost [test]> SELECT * FROM information_schema.INNODB_TEMP_TABLE_INFO;
  2. +----------+-------------+--------+-------+----------------------+---------------+
  3. | TABLE_ID| NAME        | N_COLS| SPACE  | PER_TABLE_TABLESPACE| IS_COMPRESSED|
  4. +----------+-------------+--------+-------+----------------------+---------------+
  5. | 73       | #sqlb48_3_1| 5     | 58    | FALSE               | FALSE         |
  6. | 72       | #sqlb48_3_0| 5     | 59    | TRUE                 | TRUE         |
  7. +----------+-------------+--------+-------+----------------------+---------------+
  8. 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。

  1. # ll/tmp/
  2. total 88
  3. -rw-r----- 1 root root 8586 Jun 20 16:38 #sqlb48_3_0.frm
  4. -rw-r----- 1 root root 65536 Jun 20 16:38 #sqlb48_3_0.ibd
  5. -rw-r----- 1 root root 8586 Jun 20 16:39 #sqlb48_3_1.frm
  6. -rw-------. 1 root root 0 Jan 3 2014 yum.log

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

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

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

對(duì)compress_table表：

  1. root@localhost [test]> INSERT INTO compress_tableSELECT id, nameFROM a limit 50000;
  2. Query OK, 50000 rows affected(1.20 sec)
  3. Records: 50000 Duplicates: 0 Warnings: 0
  4. （a表為一個(gè)測(cè)試數(shù)據(jù)用表）

  6. root@localhost [test]> \! ls -l/tmp
  7. -rw-r-----   1 root root 11534336Jun 20 16:54 #sqlb48_3_0.ibd

  9. root@localhost [test]> INSERT INTO compress_table SELECT id, name FROM a limit 20000; 
  10. Query OK, 20000 rows affected (0.53 sec)
  11. Records: 20000  Duplicates: 0  Warnings: 0
  12. （a表為一個(gè)測(cè)試數(shù)據(jù)用表）

  14. root@localhost [test]> \! ls -l /tmp/*.ibd
  15. -rw-r-----  1 root root 14680064Jun 20 16:55 #sqlb48_3_0.ibd

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


再簡(jiǎn)單測(cè)試一下非壓縮的InnoDB臨時(shí)表：

  1. root@localhost [test]> \! ls -l/data/mysql-data/mysql57-3357/datadir/ibtmp1
  2. -rw-r----- 1 root root 12582912Jun 20 16:57/data/mysql-data/mysql57-3357/datadir/ibtmp1

  4. root@localhost [test]> INSERT INTO uncompress_tableSELECT id, nameFROM a limit 50000;
  5. Query OK, 50000 rows affected(0.53 sec)
  6. Records: 50000 Duplicates: 0 Warnings: 0

  8. root@localhost [test]> \! ls -l/data/mysql-data/mysql57-3357/datadir/ibtmp1
  9. -rw-r----- 1 root root 79691776Jun 20 17:02/data/mysql-data/mysql57-3357/datadir/ibtmp1

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

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


〇 slave_load_tmpdir

該參數(shù)也不是5.7的新伙計(jì)，默認(rèn)值取決于tmpdir的參數(shù)。
用于存放slave上產(chǎn)生的特殊的臨時(shí)文件：
在master上出現(xiàn)LOAD DATA INFILE ... 時(shí)，被記錄到binlog并發(fā)送給slave，在SQL thread從relaylog提取數(shù)據(jù)時(shí)，寫入指定的目錄下，然后執(zhí)行LOAD DATA LOCAL INFILE ...，結(jié)束之后則會(huì)刪掉這個(gè)文件。
增加這個(gè)參數(shù)是為了復(fù)制的可靠性和數(shù)據(jù)一致性。
如果默認(rèn)放在tmpdir下，如果此時(shí)遭遇重啟，文件丟失，則會(huì)導(dǎo)致復(fù)制失敗。
如果master有使用這樣的語(yǔ)句，建議將該目錄指定在基于可靠存儲(chǔ)設(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新特性共享臨時(shí)表空間及臨時(shí)表改進(jìn)
本文路徑：http://muchs.cn/article26/jcjicg.html

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

廣告

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

成都網(wǎng)頁(yè)設(shè)計(jì)公司