mysql最佳化： 記憶體表和暫存資料表

由於直接使用暫存資料表來建立中間表，其速度不如人意，因而就有了把暫存資料表建成記憶體表的想法。但記憶體表和暫存資料表的區別且並不熟悉，需要尋找資料了。
一開始以為暫存資料表是建立後存在，當串連斷開時暫存資料表就會被刪除，即暫存資料表是存在於磁碟上的。而實際操作中發現暫存資料表建立後去目錄下查看發現並沒有發現對應的暫存資料表檔案（未取消連結）.因而猜測暫存資料表的資料和結構都是存放在記憶體中，而不是在磁碟中.
    這樣一想記憶體表不是也是存在在記憶體中嗎，那麼他和暫存資料表有什麼區別？他們的速度是什麼樣子？

    尋找了官方手冊有以下的一些解釋：
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。