MySQL 內存表和臨時表的用法
內存表：
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. 多個session，創建表的名字不能一樣
2. 一個session創建會話后，對其他session也是可見的
3. data目錄下只有tmp_memory.frm ,表結構放在磁盤上，數據放在內存中
4. mysql 重啟或者關閉后內存表里的數據會丟失，但是表結構仍然存在
5. 可以創建索引,刪除索引,支持唯一索引
6. 不影響主備，主庫上插入的數據，備庫也可以查到
7. show tables 看得到表
臨時表：
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. 創建的表的名字可以一樣
2. 表結構和數據都放在內存中
3. 會話消失表結構和數據都消失
4. 可以創建索引,刪除索引
5. 主庫創建的表，備庫查不到，
6. show tables 看不到表
使用內存表需要注意的事項
1. 內存表需要自己delete數據或者drop表；需要drop權限，這點比較危險
2. 內存表的表結構是保存在磁盤上的，如果多個session使用同一個表名，會存在沖突；如果不需要使用表名，如果使用一次都需要創建表結構，到時候會有很多小文件存在，不利于db的維護，dba清理表也有風險；
基于以上不適合用內存表
1. 臨時表是會話級別的，即使多個session創建的表名一樣，都相互不影響
2. 會話消失，所有的都消失，這點很不利于應用排查問題
另外這兩個都需要消耗額外的內存空間，雖然db端可以忍受，但是不太可控；DB端還有這個參數:
max_tmp_tables 一個客戶能同時保持打開的臨時表的最大數量，這個值默認32，可以根據需要調整此值
mysql創建臨時表，將查詢結果插入已有表中
今天遇到一個很棘手的問題，想臨時存起來一部分數據，然后再讀取。我記得學數據庫理論課老師說可以創建臨時表，不知道mysql有沒有這樣的功能呢？臨時表在內存之中，讀取速度應該比視圖快一些。然后還需要將查詢的結果存儲到臨時表中。
下面是創建臨時表以及插入數據的例子，以供大家參考。
A、臨時表再斷開于mysql的連接后系統會自動刪除臨時表中的數據，但是這只限于用下面語句建立的表：
1)定義字段
CREATE TEMPORARY TABLE tmp_table (
name VARCHAR(10) NOT NULL,
time date NOT NULL
)
更高級點就是：
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″
連編碼方式都規定了。。呵呵，以防亂碼啊。
2)直接將查詢結果導入臨時表
CREATE TEMPORARY TABLE tmp_table SELECT * FROM table_name
B、另外mysql也允許你在內存中直接創建臨時表，因為是在內存中所有速度會很快，語法如下：
CREATE TEMPORARY TABLE tmp_table (
name VARCHAR(10) NOT NULL,
value INTEGER NOT NULL
) TYPE = HEAP
那如何將查詢的結果存入已有的表呢？
1、可以使用A中第二個方法
2、使用insert into temtable (select a,b,c,d from tablea)”;
MySQL 數據庫之內存臨時表參數
一、內部內存臨時表的最大大小
該變量不適用于用戶創建的MEMORY表。臨時表會在一些較復雜查詢中創建。實際限制由tmp_table_size和max_heap_table_size中較小的值確定。
tmp-table-size參數Default 16777216
1.1、內存臨時表和硬盤臨時表
如果內存中的臨時表超出限制，MySQL會自動將其轉換為磁盤上的臨時表。
從MySQL 5.7.5開始，internal_tmp_disk_storage_engine選項定義用于磁盤臨時表的存儲引擎。
在MySQL 5.7.5之前，使用MyISAM存儲引擎。
創建的內存臨時表的數量：Created_tmp_tables
創建的硬盤臨時表的數量：Created_tmp_disk_tables
補充：Created_tmp_files
1.2、設置參考
在設置tmp-table-size時，請參考硬盤臨時表和內存臨時表的數值。
一般的比例關系是:Created_tmp_disk_tables/Created_tmp_tables5%
如果您執行了許多高級的GROUP BY查詢并且您擁有大量內存，請增加tmp_table_size的值（如有必要，請增大max_heap_table_size）。
二、MEMORY表最大SIZE
該變量設置用戶創建的MEMORY表允許增長的最大大小。他的變量值是用來計算MEMORY表的MAX_ROWS值的。
max_heap_table_size參數default 16777216
2.1、使用方式
設置此變量對任何現有的MEMORY表都沒有影響，除非使用CREATE TABLE等語句重新創建表或使用ALTER TABLE或TRUNCATE TABLE進行更改。
服務器重啟也將現有MEMORY表的最大大小設置為全局max_heap_table_size值。
該變量還與tmp_table_size一起使用，以限制內部內存表的大小。
以上為個人經驗，希望能給大家一個參考，也希望大家多多支持腳本之家。如有錯誤或未考慮完全的地方，望不吝賜教。
您可能感興趣的文章:- MySql優化之InnoDB,4GB內存,多查詢的my.ini中文配置方案詳解
- 簡述MySQL InnoDB存儲引擎
- MySQL 學習總結 之 初步了解 InnoDB 存儲引擎的架構設計
- MySQL學習（七）：Innodb存儲引擎索引的實現原理詳解
- MySQL存儲引擎MyISAM與InnoDB區別總結整理
- MySQL InnoDB存儲引擎的深入探秘
- 詳解分析MySQL8.0的內存消耗
- MySQL8.0內存相關參數總結
- MySql減少內存占用的方法詳解
- 詳解MySQL InnoDB存儲引擎的內存管理
