mysql的內(nèi)存表和臨時表

1 內(nèi)存表的表機構(gòu)還是在數(shù)據(jù)庫中的，只是數(shù)據(jù)時放在內(nèi)存中的。 2 內(nèi)存表中不可以使用TEXT和BLOB類型的字段。 3 內(nèi)存表可以通過max_heap_table_size = 2048M來加大使用的內(nèi)存。 1 臨時表的表機構(gòu)和數(shù)據(jù)都是放在內(nèi)存中的 具體見下： 內(nèi)存表使用哈希散列索引把

1 內(nèi)存表的表機構(gòu)還是在數(shù)據(jù)庫中的，只是數(shù)據(jù)時放在內(nèi)存中的。

2 內(nèi)存表中不可以使用TEXT和BLOB類型的字段。

3 內(nèi)存表可以通過max_heap_table_size = 2048M來加大使用的內(nèi)存。

1 臨時表的表機構(gòu)和數(shù)據(jù)都是放在內(nèi)存中的

具體見下：

內(nèi)存表使用哈希散列索引把數(shù)據(jù)保存在內(nèi)存中，因此具有極快的速度，適合緩存中小型數(shù)據(jù)庫。
官方手冊對內(nèi)存表有以下的一些解釋：
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.
1、heap對所有用戶的連接是可見的，這使得它非常適合做緩存。
2、僅適合使用的場合。heap不允許使用xxxTEXT和xxxBLOB數(shù)據(jù)類型；只允許使用=和<=>操作符來搜索記錄（不允許& amp; lt;、>、<=或>=）；mysql4.1版本之前不支持auto_increment；只允許對非空數(shù)據(jù)列進行索引（not null）。
注：操作符 “<=>” 說明：NULL-safe equal.這個操作符和“=”操作符執(zhí)行相同的比較操作，不過在兩個操作碼均為NULL時，其所得值為1而不為NULL，而當一個操作碼為NULL時，其所得值為0而不為NULL。
3、一旦服務器重啟，所有heap表數(shù)據(jù)丟失，但是heap表結(jié)構(gòu)仍然存在，因為heap表結(jié)構(gòu)是存放在實際數(shù)據(jù)庫路徑下的，不會自動刪除。重啟之后，heap將被清空，這時候?qū)eap的查詢結(jié)果都是空的。
4、如果heap是復制的某數(shù)據(jù)表（創(chuàng)建普通用戶表這些也會丟失），則復制之后所有主鍵、索引、自增等格式將不復存在，需要重新添加主鍵和索引，如果需要的話。
5、對于重啟造成的數(shù)據(jù)丟失，有以下的解決辦法：
　a、在任何查詢之前，執(zhí)行一次簡單的查詢，判斷heap表是否存在數(shù)據(jù)，如果不存在，則把數(shù)據(jù)重新寫入，或者DROP表重新復制某張表。這需要多做一次查詢。不過可以寫成include文件，在需要用該heap表的頁面隨時調(diào)用，比較方便。
　b、對于需要該heap表的頁面，在該頁面第一次且僅在第一次查詢該表時，對數(shù)據(jù)集結(jié)果進行判斷，如果結(jié)果為空，則需要重新寫入數(shù)據(jù)。這樣可以節(jié)省一次查詢。
　c、更好的辦法是在mysql每次重新啟動時自動寫入數(shù)據(jù)到heap，但是需要配置服務器，過程比較復雜，通用性受到限制。

6、一些預期可能用到的sql語句
//如果表存在，則刪除
DROP TABLE IF EXISTS `abc`;
//復制整張表xyz為heap表abc（包含所有數(shù)據(jù)）
CREATE TABLE `abc` type=heap select * from `xyz`;
//添加主鍵id
ALTER TABLE `abc` ADD PRIMARY KEY (`id`);
//添加索引username
ALTER TABLE `abc` ADD INDEX `abc` (`username`);
7.建表實例
CREATE TABLE `DB` (
`id` int(11) default NULL,
`songname` varchar(255) NOT NULL default ”,
`singer` varchar(255) NOT NULL default ”,
KEY `songname` (`songname`,`singer`)
) TYPE=HEAP
建表時TABLE TYPE 選項也有
這個表結(jié)構(gòu)就是建立了內(nèi)存表。
注意：type=heap創(chuàng)建內(nèi)存表的方式在5.2中將刪除，支持的創(chuàng)建方式是最后加上engine=memory.
臨時表和內(nèi)存表的區(qū)別：
1）臨時表的默認存儲引擎是myisam，它的表結(jié)構(gòu)和數(shù)據(jù) 都是存放在內(nèi)存中的，當mysql斷開連接后，就會將其都刪除；
2）內(nèi)存表的存儲引擎是memory，它的表結(jié)構(gòu)信息存放在磁盤中，數(shù)據(jù) 存放在內(nèi)存中，當mysql斷開連接后，數(shù)據(jù)會被清空，而表結(jié)構(gòu)信息仍然存在。
另內(nèi)存表存儲引擎memory要比臨時表存儲引擎myisam快，具體還沒有測試。
其它參考：
官方文檔：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 .把臨時表和內(nèi)存表放在一起使用確實會快不少：create table tmp2(id int not null) engine memory;
內(nèi)存表的建立還有一些限制條件：
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. 在同一時間需要足夠的內(nèi)存.
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.為了釋放內(nèi)存，你應該執(zhí)行DELETE FROM heap_table或DROP TABLE heap_table。
另外如何創(chuàng)建內(nèi)存表？
創(chuàng)建內(nèi)存表非常的簡單，只需注明 ENGINE= MEMORY 即可:
CREATE TABLE `tablename` ( `columnName` varchar(256) NOT NUL) ENGINE=MEMORY DEFAULT CHARSET=utf-8 MAX_ROWS=100000000;

或 寫成 TYPE=HEAP MAX_ROWS=100000000; 效果是一樣的

注意：
當內(nèi)存表中的數(shù)據(jù)大于max_heap_table_size設定的容量大小時，mysql會轉(zhuǎn)換超出的數(shù)據(jù)存儲到磁盤上，因此這是性能就大打折扣了，所以我們還需要根據(jù)我們的實際情況調(diào)整max_heap_table_size，例如在.cnf文件中[mysqld]的下面加入：
max_heap_table_size = 2048M
另外在建表語句中還可以通過MAX_ROWS來控制表的記錄數(shù)。

內(nèi)存表使用哈希散列索引把數(shù)據(jù)保存在內(nèi)存中，因此具有極快的速度，適合緩存中小型數(shù)據(jù)庫，但是使用上受到一些限制。

聲明：本網(wǎng)頁內(nèi)容旨在傳播知識，若有侵權(quán)等問題請及時與本網(wǎng)聯(lián)系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com