深度解析MySQL 5.7之暫存資料表空間_Mysql

暫存資料表

暫存資料表顧名思義，就是臨時的，用完銷毀掉的表。 資料既可以儲存在臨時的檔案系統上，也可以儲存在固定的磁碟檔案系統上。

暫存資料表有下面幾種：

1、全域暫存資料表

這種暫存資料表從資料庫執行個體啟動後開始生效，在資料庫執行個體銷毀後失效。在MySQL裡面這種暫存資料表對應的是記憶體表，即memory引擎。

2、會話層級暫存資料表

這種暫存資料表在使用者登入系統成功後生效，在使用者退出時失效。在MySQL裡的暫存資料表指的就是以create temporary table 這樣的關鍵詞建立的表。

3、事務層級暫存資料表

這種暫存資料表在事務開始時生效，事務提交或者復原後失效。 在MySQL裡面沒有這種暫存資料表，必須利用會話層級的暫存資料表間接實現。

4、檢索層級暫存資料表

這種暫存資料表在SQL語句執行之間產生，執行完畢後失效。 在MySQL裡面這種暫存資料表不是很固定，跟隨MySQL預設儲存引擎來變化。比如預設儲存引擎是MyISAM,暫存資料表的引擎就是MyISAM,並且檔案產生形式以及資料運作形式和MyISAM一樣，只是資料儲存在記憶體裡；如果預設引擎是INNODB，那麼暫存資料表的引擎就是INNODB，此時它的所有資訊都儲存在共用資料表空間ibdata裡面。

MySQL 5.7之暫存資料表空間

MySQL 5.7對於InnoDB儲存引擎的暫存資料表空間做了最佳化。在MySQL 5.7之前，INNODB引擎的暫存資料表都儲存在ibdata裡面，而ibdata的貪婪式磁碟佔用導致暫存資料表的建立與刪除對其他正常表產生非常大的效能影響。在MySQL5.7中，對於暫存資料表做了下面兩個重要方面的最佳化：

1、MySQL 5.7 把暫存資料表的資料以及復原資訊（僅限於未壓縮表）從共用資料表空間裡面剝離出來，形成自己單獨的資料表空間，參數為innodb_temp_data_file_path。

2、在MySQL 5.7 中把暫存資料表的相關檢索資訊儲存在系統資訊表中：information_schema.innodb_temp_table_info. 而MySQL 5.7之前的版本想要查看暫存資料表的系統資訊是沒有太好的辦法。

需要注意的一點就是：雖然INNODB暫存資料表有自己的資料表空間，但是目前還不能自己定義暫存資料表空間檔案的儲存路徑，只能是繼承innodb_data_home_dir。此時如果想要拿其他的磁碟，比如記憶體盤來充當暫存資料表空間的儲存地址，只能用老辦法，做軟鏈。舉個小例子：

我現在用的OS是 Ubuntu12.X，想用tmpfs檔案系統充當暫存資料表空間，

root@ytt-master-VirtualBox:/usr/local/mysql/data# ln -s/run/shm/ /usr/local/mysql/data/tmp_space2root@ytt-master-VirtualBox:/usr/local/mysql/data#ls -l | grep 'shm'lrwxrwxrwx1 root root  9 Nov 13 10:28tmp_space2 -> /run/shm/

然後把innodb_temp_data_file_path=tmp_space2/ibtmp2:200M:autoextend添加到my.cnf裡的[mysqld]下面一行, 重啟MySQL服務後:

mysql>select @@innodb_temp_data_file_path\G***************************1. row ***************************@@innodb_temp_data_file_path:tmp_space2/ibtmp2:200M:autoextend1 rowin set (0.00 sec)

先寫一個大量建立暫存資料表的預存程序：

DELIMITER$$USE`t_girl`$$DROPPROCEDURE IF EXISTS `sp_create_temporary_table`$$CREATEDEFINER=`root`@`localhost` PROCEDURE `sp_create_temporary_table`(  IN f_cnt INT UNSIGNED )BEGIN   DECLARE i INT UNSIGNED DEFAULT 1;   WHILE i <= f_cnt   DO    SET @stmt = CONCAT('create temporarytable tmp',i,' ( id int, tmp_desc varchar(60));');     PREPARE s1 FROM @stmt;    EXECUTE s1;    SET i = i + 1;   END WHILE;   DROP PREPARE s1;  END$$DELIMITER;

現在來建立10張暫存資料表：

mysql>call sp_create_temporary_table(10);QueryOK, 0 rows affected (0.07 sec)

如果在以前，我們只知道建立了10張暫存資料表，但是只能憑記憶或者手工記錄下來暫存資料表的名字等資訊。

現在可以直接從資料字典裡面檢索相關資料。

mysql> select * frominformation_schema.innodb_temp_table_info;+----------+--------------+--------+-------+----------------------+---------------+|TABLE_ID | NAME   | N_COLS | SPACE| PER_TABLE_TABLESPACE | IS_COMPRESSED |+----------+--------------+--------+-------+----------------------+---------------+|  56 | #sql1705_2_9 |  5 | 36 | FALSE    |FALSE   ||  55 | #sql1705_2_8 |  5 | 36 | FALSE    |FALSE   ||  54 | #sql1705_2_7 |  5 | 36 | FALSE    |FALSE   ||  53 | #sql1705_2_6 |  5 | 36 | FALSE    |FALSE   ||  52 | #sql1705_2_5 |  5 | 36 | FALSE    |FALSE   ||  51 | #sql1705_2_4 |  5 | 36 | FALSE    |FALSE   ||  50 | #sql1705_2_3 |  5 | 36 | FALSE    |FALSE   ||  49 | #sql1705_2_2 |  5 | 36 | FALSE    |FALSE   ||  48 | #sql1705_2_1 |  5 | 36 | FALSE    |FALSE   ||  47 | #sql1705_2_0 |  5 | 36 | FALSE    |FALSE   |+----------+--------------+--------+-------+----------------------+---------------+10rows in set (0.00 sec)

總結

功能性我就寫到這裡，大家效能方面如果有興趣可以找時間去測試。希望本文的內容對大家學習或者使用mysql5.7能帶來一定的協助，如果有疑問大家可以留言交流。