MySQL中臨時(shí)表的使用示例

    這兩天事情稍微有點(diǎn)多，公眾號(hào)也停止更新了幾天，結(jié)果有讀者催更了，也是，說明還是有人關(guān)注，利己及人，挺好。

    今天分享的內(nèi)容是MySQL中的臨時(shí)表，對(duì)于臨時(shí)表，之前我其實(shí)沒有過多的研究，只是知道MySQL在某些特定場(chǎng)景下會(huì)使用臨時(shí)表來輔助進(jìn)行g(shù)roup by等一些列操作，今天就來認(rèn)識(shí)下臨時(shí)表吧。

1、首先。臨時(shí)表是session級(jí)別的，當(dāng)前session創(chuàng)建的表，在其他session中看不到。

session 1：

mysql> create temporary table test3 (id_tmp int)engine=innodb;
Query OK, 0 rows affected (0.00 sec)

session 2：

mysql> show create table test3\G
ERROR 1146 (42S02): Table 'test.test3' doesn't exist

2、臨時(shí)表在session中，可以和正式的表重名。

mysql> create table test2 (id int)engine=innodb;
Query OK, 0 rows affected (0.01 sec)

mysql> create temporary table test2 (id_tmp int)engine=innodb;
Query OK, 0 rows affected (0.00 sec)

   可以看到，創(chuàng)建同名的test2表的時(shí)候，并沒有出現(xiàn)報(bào)錯(cuò)的情況。

3、當(dāng)數(shù)據(jù)庫(kù)中物理表和臨時(shí)表的時(shí)候，使用show create table查看的是臨時(shí)表的內(nèi)容：

mysql> show create table test2\G
*************************** 1. row ***************************
    Table: test2
Create Table: CREATE TEMPORARY TABLE `test2` (
 `id_tmp` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

4、臨時(shí)表drop掉之后，show create table查看的是物理表的內(nèi)容。

mysql> show tables like "test2";
+------------------------+
| Tables_in_test (test2) |
+------------------------+
| test2         |
+------------------------+
1 row in set (0.00 sec)

mysql> drop table test2;
Query OK, 0 rows affected (0.00 sec)

mysql> show tables like "test2";
+------------------------+
| Tables_in_test (test2) |
+------------------------+
| test2         |
+------------------------+
1 row in set (0.00 sec)

5、show tables命令，不能看到臨時(shí)表。

6、不同的session中可以創(chuàng)建同名的臨時(shí)表。

7、臨時(shí)表保存方法

   在MySQL中，使用.frm來保存表結(jié)構(gòu)，而使用.ibd來保存表數(shù)據(jù)，.frm文件一般是放在tmpdir這個(gè)參數(shù)指定的目錄下面的。臺(tái)式機(jī)windows平臺(tái)下MySQL的如下：

mysql> show variables like "%tmpdir%";
+-------------------+-------------------------------------------------+
| Variable_name   | Value                      |
+-------------------+-------------------------------------------------+
| innodb_tmpdir   |                         |
| slave_load_tmpdir | C:\WINDOWS\SERVIC~1\NETWOR~1\AppData\Local\Temp |
| tmpdir      | C:\WINDOWS\SERVIC~1\NETWOR~1\AppData\Local\Temp |
+-------------------+-------------------------------------------------+
3 rows in set, 1 warning (0.01 sec)

MySQL5.6版本下，會(huì)生成一個(gè).ibd的文件來保存臨時(shí)表。

MySQL5.7版本下，引入了臨時(shí)文件表空間，專門用來存放臨時(shí)文件的數(shù)據(jù)。

當(dāng)我們使用不同的session來創(chuàng)建相同名稱的臨時(shí)表的時(shí)候，會(huì)發(fā)現(xiàn)臨時(shí)表的目錄下面存在不同名稱的臨時(shí)表文件：

這些臨時(shí)表在內(nèi)存中是通過鏈表的方式來表示的，如果一個(gè)session中包含兩個(gè)臨時(shí)表，MySQL會(huì)創(chuàng)建一個(gè)臨時(shí)表的鏈表，將這兩個(gè)臨時(shí)表連接起來，實(shí)際的操作邏輯中，如果我們執(zhí)行了一條SQL，MySQL會(huì)遍歷這個(gè)臨時(shí)表的鏈表，檢查是否有這個(gè)SQL中指定表名字的臨時(shí)表，如果有臨時(shí)表，優(yōu)先操作臨時(shí)表，如果沒有臨時(shí)表，則操作普通的物理表。

8、臨時(shí)表在主從復(fù)制中的注意點(diǎn)

   臨時(shí)表由于是session級(jí)別的，那么在session退出的時(shí)候，是會(huì)刪除臨時(shí)表的。但是主節(jié)點(diǎn)中并沒有對(duì)臨時(shí)表進(jìn)行顯示的操作，而是關(guān)閉session即可刪除，那么從節(jié)點(diǎn)如何知道什么時(shí)候才能刪除臨時(shí)表呢？

假設(shè)主節(jié)點(diǎn)進(jìn)行如下SQL：

crete table tbl;
create temporary table tmp like tbl;
insert into tmp values (0,0);
insert into tbl select * from tmp;

  在binlog=statement/mixed模式下，如果不記錄臨時(shí)表相關(guān)操作的binlog，則最后一條insert語(yǔ)句會(huì)報(bào)錯(cuò)。因?yàn)檎也坏絫mp這個(gè)表。這種情況下，MySQL的binlog中會(huì)記錄臨時(shí)表的操作，當(dāng)主庫(kù)的session關(guān)閉的時(shí)候，自動(dòng)的在binlog中添加drop temporary table的SQL語(yǔ)句，從而保證主從數(shù)據(jù)的一致。

   在binlog=row模式下，跟臨時(shí)表有關(guān)的SQL，都不會(huì)記錄到binlog里面，因?yàn)閞ow模式下，數(shù)據(jù)的每個(gè)字段在binlog中都能找到，針對(duì)最后一個(gè)insert into select語(yǔ)句，binlog中會(huì)記錄成往tbl表中插入(0,0)這條記錄。

    binlog=row模式下，當(dāng)主庫(kù)上主動(dòng)使用drop table tmp的命令來刪除臨時(shí)表的時(shí)候，此時(shí)因?yàn)閎inlog中不記錄臨時(shí)表的相關(guān)操作，所以這條記錄也會(huì)被忽略。

9、不同線程的同名臨時(shí)表在從庫(kù)上如何同時(shí)存在？

   我們知道臨時(shí)表是session級(jí)別的，而且不同session之間的臨時(shí)表可以重名，在從庫(kù)進(jìn)行binlog回放的時(shí)候，從庫(kù)是如何知道這些重名的臨時(shí)表分別屬于哪個(gè)事務(wù)的呢？

 這個(gè)概念的理解可以參考函數(shù)中的形參和實(shí)參的概念，形參和實(shí)參可能有同樣的名字，進(jìn)行賦值的時(shí)候，二者的指針值是不一樣的，所以同名的參數(shù)，對(duì)編譯器來講，由于指針值不一樣，所以不會(huì)出現(xiàn)錯(cuò)誤。

   MySQL維護(hù)數(shù)據(jù)表，除了物理上要有文件外，內(nèi)存里面也有一套機(jī)制區(qū)別不同的表，每個(gè)表都對(duì)應(yīng)一個(gè)table_def_key。而這個(gè)table_def_key的值是由"庫(kù)名字+表名字+server_id+thread_id"組成的，因?yàn)閠hread_id不同，所以在從庫(kù)中進(jìn)行操作的時(shí)候，是不會(huì)沖突的。

以上就是詳解MySQL中的內(nèi)存臨時(shí)表的詳細(xì)內(nèi)容，更多關(guān)于MySQL 內(nèi)存臨時(shí)表的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！