MySQL 內(nèi)存表和臨時(shí)表的用法詳解

MySQL 內(nèi)存表和臨時(shí)表的用法

內(nèi)存表：

session 1
$ mysql -uroot
root@(none) 10:05:06>use test
Database changed
root@test 10:06:06>CREATE TABLE tmp_memory (i INT) ENGINE = MEMORY;
Query OK, 0 rows affected (0.00 sec)
root@test 10:08:46>insert into tmp_memory values (1);
Query OK, 1 row affected (0.00 sec)
root@test 10:08:46>
session2
$ mysql -uroot test
root@test 10:05:12>CREATE TABLE tmp_memory (i INT) ENGINE = MEMORY;
ERROR 1050 (42S01): Table 'tmp_memory' already exists
root@test 10:16:27>select * from tmp_memory;
+------+
| i |
+------+
| 1 |
+------+
1 row in set (0.00 sec)

1. 多個(gè)session，創(chuàng)建表的名字不能一樣

2. 一個(gè)session創(chuàng)建會(huì)話后，對(duì)其他session也是可見的

3. data目錄下只有tmp_memory.frm ,表結(jié)構(gòu)放在磁盤上，數(shù)據(jù)放在內(nèi)存中

4. mysql 重啟或者關(guān)閉后內(nèi)存表里的數(shù)據(jù)會(huì)丟失，但是表結(jié)構(gòu)仍然存在

5. 可以創(chuàng)建索引,刪除索引,支持唯一索引

6. 不影響主備，主庫上插入的數(shù)據(jù)，備庫也可以查到

7. show tables 看得到表

臨時(shí)表：

session1
$ mysql -uroot test
root@test 10:30:18>CREATE TEMPORARY TABLE tmp_table (name VARCHAR(10) NOT NULL,value INTEGER NOT NULL);
Query OK, 0 rows affected (0.05 sec)
root@test 10:31:54>select * from tmp_table;
+--------+-------+
| name | value |
+--------+-------+
| aaaaaa | 10 |
+--------+-------+
1 row in set (0.00 sec)
session2
root@test 10:20:13> CREATE TEMPORARY TABLE tmp_table (name VARCHAR(10) NOT NULL,value INTEGER NOT NULL);
Query OK, 0 rows affected (0.02 sec)
root@test 10:30:39>insert into tmp_table values ('bbbbbbb',10);
Query OK, 1 row affected (0.01 sec)
root@test 10:31:33>select * from tmp_table;
+---------+-------+
| name | value |
+---------+-------+
| bbbbbbb | 10 |
+---------+-------+
1 row in set (0.00 sec)
root@test 10:31:43>exit
Bye
[1 Single:MS-Master db152011.sqa.cm6: mysql ~ ]
$ mysql -uroot test
root@test 10:32:17>select * from tmp_table;
ERROR 1146 (42S02): Table 'test.tmp_table' doesn't exist
root@test 10:32:22>
root@test 10:32:23>

1. 創(chuàng)建的表的名字可以一樣

2. 表結(jié)構(gòu)和數(shù)據(jù)都放在內(nèi)存中

3. 會(huì)話消失表結(jié)構(gòu)和數(shù)據(jù)都消失

4. 可以創(chuàng)建索引,刪除索引

5. 主庫創(chuàng)建的表，備庫查不到，

6. show tables 看不到表

使用內(nèi)存表需要注意的事項(xiàng)

1. 內(nèi)存表需要自己delete數(shù)據(jù)或者drop表；需要drop權(quán)限，這點(diǎn)比較危險(xiǎn)

2. 內(nèi)存表的表結(jié)構(gòu)是保存在磁盤上的，如果多個(gè)session使用同一個(gè)表名，會(huì)存在沖突；如果不需要使用表名，如果使用一次都需要?jiǎng)?chuàng)建表結(jié)構(gòu)，到時(shí)候會(huì)有很多小文件存在，不利于db的維護(hù)，dba清理表也有風(fēng)險(xiǎn)；

基于以上不適合用內(nèi)存表

1. 臨時(shí)表是會(huì)話級(jí)別的，即使多個(gè)session創(chuàng)建的表名一樣，都相互不影響

2. 會(huì)話消失，所有的都消失，這點(diǎn)很不利于應(yīng)用排查問題

另外這兩個(gè)都需要消耗額外的內(nèi)存空間，雖然db端可以忍受，但是不太可控；DB端還有這個(gè)參數(shù):

max_tmp_tables 一個(gè)客戶能同時(shí)保持打開的臨時(shí)表的最大數(shù)量，這個(gè)值默認(rèn)32，可以根據(jù)需要調(diào)整此值

mysql創(chuàng)建臨時(shí)表，將查詢結(jié)果插入已有表中

今天遇到一個(gè)很棘手的問題，想臨時(shí)存起來一部分?jǐn)?shù)據(jù)，然后再讀取。我記得學(xué)數(shù)據(jù)庫理論課老師說可以創(chuàng)建臨時(shí)表，不知道m(xù)ysql有沒有這樣的功能呢？臨時(shí)表在內(nèi)存之中，讀取速度應(yīng)該比視圖快一些。然后還需要將查詢的結(jié)果存儲(chǔ)到臨時(shí)表中。

下面是創(chuàng)建臨時(shí)表以及插入數(shù)據(jù)的例子，以供大家參考。

A、臨時(shí)表再斷開于mysql的連接后系統(tǒng)會(huì)自動(dòng)刪除臨時(shí)表中的數(shù)據(jù)，但是這只限于用下面語句建立的表：

1)定義字段

CREATE TEMPORARY TABLE tmp_table (
 name VARCHAR(10) NOT NULL, 
 time date NOT NULL
 )

更高級(jí)點(diǎn)就是：

create temporary TABLE `temtable` (
 `jws` varchar(100) character set utf8 collate utf8_bin NOT NULL,
 `tzlb` varchar(100) character set utf8 collate utf8_bin NOT NULL,
 `uptime` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1″

連編碼方式都規(guī)定了。。呵呵，以防亂碼啊。

2)直接將查詢結(jié)果導(dǎo)入臨時(shí)表

CREATE TEMPORARY TABLE tmp_table SELECT * FROM table_name

B、另外mysql也允許你在內(nèi)存中直接創(chuàng)建臨時(shí)表，因?yàn)槭窃趦?nèi)存中所有速度會(huì)很快，語法如下：

 CREATE TEMPORARY TABLE tmp_table (
 name VARCHAR(10) NOT NULL,
 value INTEGER NOT NULL
 ) TYPE = HEAP

那如何將查詢的結(jié)果存入已有的表呢？

1、可以使用A中第二個(gè)方法

2、使用insert into temtable (select a,b,c,d from tablea)”;

MySQL 數(shù)據(jù)庫之內(nèi)存臨時(shí)表參數(shù)

一、內(nèi)部內(nèi)存臨時(shí)表的最大大小

該變量不適用于用戶創(chuàng)建的MEMORY表。臨時(shí)表會(huì)在一些較復(fù)雜查詢中創(chuàng)建。實(shí)際限制由tmp_table_size和max_heap_table_size中較小的值確定。
tmp-table-size參數(shù)Default 16777216

1.1、內(nèi)存臨時(shí)表和硬盤臨時(shí)表
如果內(nèi)存中的臨時(shí)表超出限制，MySQL會(huì)自動(dòng)將其轉(zhuǎn)換為磁盤上的臨時(shí)表。
從MySQL 5.7.5開始，internal_tmp_disk_storage_engine選項(xiàng)定義用于磁盤臨時(shí)表的存儲(chǔ)引擎。
在MySQL 5.7.5之前，使用MyISAM存儲(chǔ)引擎。
創(chuàng)建的內(nèi)存臨時(shí)表的數(shù)量：Created_tmp_tables
創(chuàng)建的硬盤臨時(shí)表的數(shù)量：Created_tmp_disk_tables
補(bǔ)充：Created_tmp_files

1.2、設(shè)置參考
在設(shè)置tmp-table-size時(shí)，請(qǐng)參考硬盤臨時(shí)表和內(nèi)存臨時(shí)表的數(shù)值。
一般的比例關(guān)系是:Created_tmp_disk_tables/Created_tmp_tables5%
如果您執(zhí)行了許多高級(jí)的GROUP BY查詢并且您擁有大量內(nèi)存，請(qǐng)?jiān)黾觮mp_table_size的值（如有必要，請(qǐng)?jiān)龃髆ax_heap_table_size）。

二、MEMORY表最大SIZE

該變量設(shè)置用戶創(chuàng)建的MEMORY表允許增長的最大大小。他的變量值是用來計(jì)算MEMORY表的MAX_ROWS值的。
max_heap_table_size參數(shù)default 16777216

2.1、使用方式
設(shè)置此變量對(duì)任何現(xiàn)有的MEMORY表都沒有影響，除非使用CREATE TABLE等語句重新創(chuàng)建表或使用ALTER TABLE或TRUNCATE TABLE進(jìn)行更改。
服務(wù)器重啟也將現(xiàn)有MEMORY表的最大大小設(shè)置為全局max_heap_table_size值。
該變量還與tmp_table_size一起使用，以限制內(nèi)部內(nèi)存表的大小。

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教。