mysql臨時表和記憶體表

1.手冊 http://dev.mysql.com/doc/refman/5.1/zh/storage-engines.html#memory-storage-engine
2.注意最大記憶體表的大小，MySQL中的變數是max_heap_table_size，可以透過MySQL客戶端工具中執行命令 show variables like '%heap%'; 來觀察。有幾種方法可以修改，一是啟動選項中修改啟動mysql的時候加引數 -O max_heap_table_size=32M ，一是修改MySQL的配置檔案，在[mysqld]的段中增加 max_heap_table_size=32M ，一是透過MySQL客戶端工具中執行命令 set global max_heap_table_size=32777216; 。
3.注意記憶體表對於變長資料的處理，例如：varchar(50)這樣的欄位，對於MyISAM的表結構而言，會按照欄位中實際儲存的內容計算空間，而記憶體表則按照char(50)的方式計算空間，這樣就會使記憶體表佔據的空間大幅度上升，在實際使用的過程中，一定要注意。
4.對於記憶體表需要進行實時的監控，防止資料庫重啟後的資料丟失，資料庫重啟後，對記憶體表僅保留表結構而不保留資料。
5.記憶體表不支援like操作，效能是非常的差。

 記憶體表：
1. 引數控制：max_heap_table_size
2. 到達上線後報錯。
3. 表定義儲存在磁碟上，資料和索引儲存在記憶體裡面。
4. 不能包含TEXT,BLOB等欄位。
臨時表：
1. 引數控制：tmp_table_size。
2. 到達上線後建立檔案在磁碟上。
3. 表定義和資料都在記憶體裡。
4. 可以包含TEXT, BLOB等欄位。
  
而且tmp_table_size和max_heap_table_size的範圍挺複雜。
有三個範圍： global, session, table。 而且都適用。
  
當工作在非常大的表上時，你可能偶爾需要執行很多查詢獲得一個大量資料的小的子集，不是對整個表執行這些查詢，而是讓MySQL每次找出所需的少數記錄，將記錄選擇到一個臨時表可能更快些，然後多這些表執行查詢。

建立臨時表很容易，給正常的CREATE TABLE語句加上TEMPORARY關鍵字：

CREATE TEMPORARY TABLE tmp_table (name VARCHAR(10) NOT NULL,value INTEGER NOT NULL)
 臨時表將在你連線MySQL期間存在。當你斷開時，MySQL將自動刪除表並釋放所用的空間。當然你可以在仍然連線的時候刪除表並釋放空間。

DROP TABLE tmp_table

如果在你建立名為tmp_table臨時表時名為tmp_table的表在資料庫中已經存在，臨時表將有必要遮蔽（隱藏）非臨時表tmp_table。

如果你宣告臨時表是一個HEAP表，MySQL也允許你指定在記憶體中建立它：

CREATE TEMPORARY TABLE tmp_table (name VARCHAR(10) NOT NULL,value INTEGER NOT NULL) TYPE = HEAP
 
因為HEAP表儲存在記憶體中，你對它執行的查詢可能比磁碟上的臨時錶快些。然而，HEAP表與一般的表有些不同，且有自身的限制。詳見MySQL參考手冊。
正如前面的建議，你應該測試臨時表看看它們是否真的比對大量資料庫執行查詢快。如果資料很好地索引，臨時表可能一點不快。

----------------------

mysql最佳化： 記憶體表和臨時表

    CIC有著非常龐大的資料，對這些資料進行拆分就涉及到OLAP的知識。需要建立很多箇中間臨時表用來進行切分操作。
由於直接使用臨時表來建立中間表，其速度不如人意，因而就有了把臨時表建成記憶體表的想法。但記憶體表和臨時表的區別且並不熟悉，需要查詢資料了。
一開始以為臨時表是建立後存在，當連線斷開時臨時表就會被刪除，即臨時表是存在於磁碟上的。而實際操作中發現臨時表建立後去目錄下檢視發現並沒有發現對應 的臨時表檔案（未斷開連結）.因而猜測臨時表的資料和結構都是存放在記憶體中，而不是在磁碟中.
    這樣一想 記憶體表不是也是存在在記憶體中嗎，那麼他和臨時表有什麼區別？他們的速度是什麼樣子？

    查詢了官方手冊有以下的一些解釋：
The MEMORY storage engine creates tables with contents that are stored in memory. Formerly, these were known as HEAP tables. MEMORY is the preferred term, although HEAP remains supported for backward compatibility.

Each MEMORY table is associated with one disk file. The filename begins with the table name and has an extension of .frm to indicate that it stores the table definition.

由此可以看出來記憶體表會把表結構存放在磁碟上，把資料放在記憶體中。
並做了以下實驗：
臨時表
mysql> create temporary table tmp1(id int not null);
Query OK, 0 rows affected (0.00 sec)

mysql> show create table tmp1;
+-------+----------------------------------------------------------------------------------------------+
| Table | Create Table                                                                               |
+-------+----------------------------------------------------------------------------------------------+
| tmp1   | CREATE TEMPORARY TABLE `tmp1` ( `id` int(11) NOT NULL) ENGINE=MyISAM DEFAULT CHARSET=utf8    |
+-------+----------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

記憶體表
mysql> create table tmp2(id int not null) TYPE＝HEAP;
Query OK, 0 rows affected (0.00 sec)

mysql> show create table tmp2;
+-------+------------------------------------------------------------------------------------+
| Table | Create Table                                                                       |
+-------+------------------------------------------------------------------------------------+
| tmp2   | CREATE TABLE `tmp2` (
   `id` int(11) NOT NULL
) ENGINE=MEMORY DEFAULT CHARSET=utf8 |
+-------+------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

可以看出來臨時表和記憶體表的ENGINE 不同，臨時表預設的是MyISAM,而記憶體表是MEMORY .去資料庫目錄檢視，發現tmp2.frm而沒有tmp1表的任何檔案。看來實際情況是符合官方解釋的。

那麼速度方面呢（即MyISAM和MEMORY之間的區別）？
實驗開始：
實現手段：對基於2張千萬級別的表做一些OLAP切分操作，中間表的建立使用2種不同的方式。最後把中間表的資料按照要求取出，插入到結果表中
實驗目的;測試臨時記憶體表和臨時表的速度
1.中間表的建立使用Create temporary table type = heap 即 把中間表建立成臨時記憶體表
2.中間表直接使用Create temporary table建立

實驗結果：
臨時記憶體表： 1小時
1 2008-09-25 11:03:48
1 2008-09-25 12:03:39
臨時表：1小時17分鐘
2 2008-09-25 12:25:28
2 2008-09-25 13:42:37

由此發現MEMORY比MyISAM快大概20％。

接著查詢官方手冊：
As indicated by the name, MEMORY tables are stored in memory. They use hash indexes by default, which makes them very fast, and very useful for creating temporary tables. However, when the server shuts down, all rows stored in MEMORY tables are lost. The tables themselves continue to exist because their definitions are stored in .frm files on disk, but they are empty when the server restarts.

可以看出來MEMORY確實是very fast，and very useful for creating temporary tables .把臨時表和記憶體表放在一起使用確實會快不少：create table tmp2(id int not null) engine memory;

記憶體表的建立還有一些限制條件：
MEMORY tables cannot contain        BLOB or TEXT columns. HEAP不支援BLOB/TEXT列。   
The server needs sufficient memory to maintain all   MEMORY tables that are in use at the same time. 在同一時間需要足夠的記憶體.
To free memory used by a MEMORY table when   you no longer require its contents, you should execute DELETE or TRUNCATE TABLE, or remove the table altogether using DROP TABLE.
為了釋放記憶體，你應該執行DELETE FROM heap_table或DROP TABLE heap_table。