《Mysql實例mysql優化之內存表與臨時表》要點：
本文介紹了Mysql實例mysql優化之內存表與臨時表，希望對您有用。如果有疑問，可以聯系我們。
MYSQL入門由于直接使用臨時表來創建中間表,其速度不如人意,因而就有了把臨時表建成內存表的想法.
MYSQL入門但內存表和臨時表的區別且并不熟悉,需要查找資料了.
一開始以為臨時表是創建后存在,當連接斷開時臨時表就會被刪除,即臨時表是存在于磁盤上的.而實際操作中發現臨時表創建后去目錄下查看發現并沒有發現對應的臨時表文件(未斷開鏈接).因而猜測臨時表的數據和結構都是存放在內存中,而不是在磁盤中.
MYSQL入門這樣一想內存表不是也是存在在內存中嗎,那么它與臨時表有什么區別?速度如何?
MYSQL入門查看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.
MYSQL入門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入門由此可以看出來內存表會把表結構存放在磁盤上,把數據放在內存中.
并做了以下實驗：
臨時表
?
MYSQL入門mysql> create temporary table tmp1(id int not null);
query ok, 0 rows affected (0.00 sec)
MYSQL入門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入門內存表
?
MYSQL入門mysql> create table tmp2(id int not null) type＝heap;
query ok, 0 rows affected (0.00 sec)
MYSQL入門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)
MYSQL入門可以看出來臨時表和內存表的engine 不同,臨時表默認的是myisam,而內存表是memory .去數據庫目錄查看,發現tmp2.frm而沒有tmp1表的任何文件.看來實際情況是符合官方解釋的.
MYSQL入門那么速度方面呢(即myisam和memory之間的區別)?
實驗開始：
實現手段：對基于2張千萬級別的表做一些olap切分操作,中間表的建立使用2種不同的方式.最后把中間表的數據依照要求取出,插入到結果表中
實驗目的;測試臨時內存表和臨時表的速度
1.中間表的建立使用create temporary table type = heap 即 把中間表建立成臨時內存表
2.中間表直接使用create temporary table建立
MYSQL入門實驗結果：
臨時內存表： 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
MYSQL入門由此發現memory比myisam快大概20％.
MYSQL入門接著查找官方手冊：
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.
MYSQL入門可以看出來memory確實是very fast,and very useful for creating temporary tables .把臨時表和內存表放在一起使用確實會快不少：create table tmp2(id int not null) engine memory;
MYSQL入門內存表的建立還有一些限制條件：
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.
MYSQL入門為了釋放內存,應該執行：
delete from heap_table或drop table heap_table.
mysql 內存表基礎知識
mysql 內存表與臨時表有哪些區別
mysql創建內存表辦法
有關MySQL內存表的特性及使用介紹
mysql 內存表在主從同步時的注意事項
《Mysql實例mysql優化之內存表與臨時表》是否對您有啟發，歡迎查看更多與《Mysql實例mysql優化之內存表與臨時表》相關教程，學精學透。維易PHP學院為您提供精彩教程。
轉載請注明本頁網址：
http://www.snjht.com/jiaocheng/8895.html
