愛可生雲資料庫2020-04-26 16:52:08

先來說說臨時表的概念。 臨時表顧名思義，就是臨時的，用完銷燬掉的表。 資料既可以儲存在臨時的檔案系統上，也可以儲存在固定的磁碟檔案系統上。 臨時表有下面幾種：

1全域性臨時表

這種臨時表從資料庫例項啟動後開始生效，在資料庫例項銷燬後失效。在MySQL裡面這種臨時表對應的是記憶體表，即memory引擎。

2會話級別臨時表

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

3事務級別臨時表

這種臨時表在事務開始時生效，事務提交或者回滾後失效。 在MySQL裡面沒有這種臨時表，必須利用會話級別的臨時表間接實現。

4檢索級別臨時表

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

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

MySQL5。7 把臨時表的資料以及回滾資訊（僅限於未壓縮表）從共享表空間裡面剝離出來，形成自己單獨的表空間，引數為innodb_temp_data_file_path。

在MySQL5。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_space2

root@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：autoextend

1 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）

功能性我就寫到這裡，大家效能方面如果有興趣可以找時間去測試。

愛可生雲資料庫2020-04-26 17:18:01

先來說說臨時表的概念。 臨時表顧名思義，就是臨時的，用完銷燬掉的表。 資料既可以儲存在臨時的檔案系統上，也可以儲存在固定的磁碟檔案系統上。 臨時表有下面幾種：

1全域性臨時表

這種臨時表從資料庫例項啟動後開始生效，在資料庫例項銷燬後失效。在MySQL裡面這種臨時表對應的是記憶體表，即memory引擎。

2會話級別臨時表

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

3事務級別臨時表

這種臨時表在事務開始時生效，事務提交或者回滾後失效。 在MySQL裡面沒有這種臨時表，必須利用會話級別的臨時表間接實現。

4檢索級別臨時表

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

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

MySQL5。7 把臨時表的資料以及回滾資訊（僅限於未壓縮表）從共享表空間裡面剝離出來，形成自己單獨的表空間，引數為innodb_temp_data_file_path。

在MySQL5。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_space2

root@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：autoextend

1 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）

功能性我就寫到這裡，大家效能方面如果有興趣可以找時間去測試。