mysql虛擬表

虛擬表，顧名思義，就是實際上並不存在（物理上不存在），但是邏輯上存在的表。這樣說很抽象，還是看一些實際的例子吧。

在mysql中，存在三種虛擬表：暫存資料表、記憶體表和視圖。視圖會單獨講，本節僅僅將暫存資料表和記憶體表。

一、mysql暫存資料表

1、什麼是暫存資料表

暫存資料表是建立在系統臨時檔案夾中的表，如果使用得當，完全可以像普通表一樣進行各種操作。 暫存資料表的資料和表結構都儲存在記憶體之中，退出時，其所佔的空間會自動被釋放。

2、建立暫存資料表

         (1)定義欄位

      CREATE TEMPORARY TABLE tmp_table (        name VARCHAR(10) NOT NULL,        value INTEGER NOT NULL)    

     (2)直接將查詢結果匯入暫存資料表

         CREATE TEMPORARY TABLE tmp_table SELECT * FROM table_name

3、查詢暫存資料表
select * from tmp_table

4、刪除暫存資料表
drop table tmp_table

二、暫存資料表的應用

當工作在十分大的表上運行時，在實際操作中你可能會需要運行很多的相關查詢，來獲的一個大量資料的小的子集。較好的辦法，不是對整個表運行這些查詢，而是讓MySQL每次找出所需的少數記錄，將記錄選擇到一個暫存資料表，然後對這些表執行查詢。

　　1）、當某一個SQL語句關聯的表在2張及以上，並且和一些小表關聯。可以採用將大表進行分拆並且得到比較小的結果集合存放在暫存資料表中。

　　2）、程式執行過程中可能需要存放一些臨時的資料，這些資料在整個程式的會話過程中都需要用的等等。

3）、暫存資料表預設的是MyISAM,但是可以修改。

4)、可以把一些經常訪問的資料放到暫存資料表中，這樣訪問時會快一些,因為資料是在伺服器記憶體中,另外每次查詢的時候，資料庫都需要產生一些臨時資料在暫存資料表裡

三、暫存資料表使用注意事項：

(1)暫存資料表只在當前串連可見，當這個串連關閉的時候，會自動drop。這就意味著你可以在兩個不同的串連裡使用相同的暫存資料表名，並且相互不會 衝突，或者使用 已經存在的表，但不是暫存資料表的表名。(當這個暫存資料表存在的時候，存在的表被隱藏了，如果暫存資料表被drop，存在的表就可見了)。

(2) 暫存資料表只能用在 memory,myisam,merge,或者innodb引擎。

（3）暫存資料表不支援mysql cluster(簇)。

（4）在同一個query語句中，你只能尋找一次暫存資料表。例如：下面的就不可用

　　mysql> SELECT * FROM temp_table, temp_table AS t2;

　　ERROR 1137: Can't reopen table: 'temp_table'

　　如果在一個儲存函數裡，你用不同的別名尋找一個暫存資料表多次，或者在這個儲存函數裡用不同的語句尋找，這個錯誤都會發生。

（5）show tables 語句不會列舉暫存資料表，但是會列出記憶體表。

（6）你不能用rename來重新命名一個暫存資料表。但是，你可以alter table代替：

　　mysql>ALTER TABLE orig_name RENAME new_name;

********************************************************************************************

四、記憶體表

1、記憶體表:表結構建在磁碟裡，資料在記憶體裡 ，當停止服務後，表中的資料丟失，而表的結構不會丟失。記憶體表也可以被看作是暫存資料表的一種。

2、記憶體表的建立：

CREATE TEMPORARY TABLE tmp_table (

     name VARCHAR(10) NOT NULL,       value INTEGER NOT NULL )  TYPE = HEAP  注意： TYPE = HEAP必須要有。

五、記憶體表的應用

記憶體表使用雜湊散列索引把資料儲存在記憶體中，因此具有極快的速度，適合緩衝中小型資料庫。

1、heap對所有使用者的串連是可見的，這使得它非常適合做緩衝。 
2、一旦伺服器重啟，所有heap表資料丟失，但是heap表結構仍然存在，因為heap表結構是存放在實際資料庫路徑下的，不會自動刪除。重啟之後，heap將被清空，這時候對heap的查詢結果都是空的。 
3、如果heap是複製的某資料表（建立普通使用者表這些也會丟失），則複製之後所有主鍵、索引、自增等格式將不複存在，需要重新添加主鍵和索引，如果需要的話。 
4、對於重啟造成的資料丟失，有以下的解決辦法： 
　a、在任何查詢之前，執行一次簡單的查詢，判斷heap表是否存在資料，如果不存在，則把資料重新寫入，或者DROP表重新複製某張表。這需要多做一次查詢。不過可以寫成include檔案，在需要用該heap表的頁面隨時調用，比較方便。 
　b、對於需要該heap表的頁面，在該頁面第一次且僅在第一次查詢該表時，對資料集結果進行判斷，如果結果為空白，則需要重新寫入資料。這樣可以節省一次查詢。 
　c、更好的辦法是在mysql每次重新啟動時自動寫入資料到heap，但是需要設定管理員，過程比較複雜，通用性受到限制。

六、記憶體表的注意事項

1、heap不允許使用xxxTEXT和xxxBLOB資料類型；只允許使用=和<=>操作符來搜尋記錄（不允 許& amp; lt;、>、<=或>=）；mysql4.1版本之前不支援auto_increment；只允許對非空資料列進行索引（not null）。

註：操作符 “<=>” 說明：NULL-safe equal.這個操作符和“=”操作符執行相同的比較操作，不過在兩個作業碼均為NULL時，其所得值為1而不為NULL，而當一個作業碼為NULL時，其所得值為0而不為NULL。 
2、記憶體表可以通過max_heap_table_size = 2048M來加大使用的記憶體。

3、記憶體表必須使用memory儲存引擎

參考文章：

1、百度百科-暫存資料表：

http://baike.baidu.com/view/4420349.htm

2、http://www.cnblogs.com/cy163/archive/2008/10/16/1313096.html

3、mysql暫存資料表

http://www.cnblogs.com/end/archive/2011/03/31/2001094.html

4、http://zhidao.baidu.com/question/211736868.html

5、mysql記憶體表的使用

http://zhangkun716717-126-com.iteye.com/blog/721177

6、mysql 的
記憶體表和暫存資料表

http://ifeisu.iteye.com/blog/1148506