mysql預存程序中使用暫存資料表

當工作在很大的表上時，您可能偶爾需要運行很多查詢獲得一個大量資料的小的子集，不是對整個表運行這些查詢，而是讓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參考手冊。

　　正如前面的建議，您應該測試暫存資料表看看他們是否真的比對大量資料庫執行查詢快。假如資料很好地索引，暫存資料表可能一點不快。

delimiter ||
create procedure sp_test1(
IN pageno int, IN pagesize int,
OUT pagecount int
)
BEGIN
declare idlower bigint;
declare idupper bigint;
declare totalreccount int;

drop table if exists tmp_table21;
CREATE TEMPORARY TABLE tmp_table21(   
rowid bigint auto_increment primary key,
userid bigint
);

insert into tmp_table21 (userid) select ID from restcomments;
set idlower=(pageno-1)*pagesize+1;
set idupper=pageno*pagesize;
select * from tmp_table21;

/*計算總頁數*/
select count(*) from tmp_table21 into totalreccount;
set pagecount=totalreccount;
end||
delimiter ;
注意:

引言：某客戶新上線一個項目，利用預存程序處理使用者登入相關事務。在預存程序中，需要對使用者資料進行處理，於是他們採用暫存資料表(temporary table)來做這個動作，先建立一個暫存資料表，然後插入資料，處理；由於是採用串連池方式，擔心暫存資料表被複用，於是在最後刪除該暫存資料表。該客戶採用16G 的2950機器做mysql db server，利用loadrunner進行類比登入測試，發現並發量達到2,30萬之後，就再也上不去了，而且峰值不是很穩定的處於30多萬的層級上。
一開始以為是機器效能達到了極限，經過詢問各種狀況後，認為應該還可以得到改進和最佳化。經過現場分析後，發現在測試達到峰值時，會有大量的 "waiting for table"，以及大量的 create temporary table 和 drop table 的線程在等待。很明顯，瓶頸在於頻繁的建立和刪除暫存資料表，mysql需要頻繁的處理開啟和關閉表描述符，才會導致了上面的問題。還好他們採用了串連池，否則情況將會更糟糕。建議他們把最後的 drop table 改成 truncate table，把暫存資料表清空了，也就不會擔心下一次調用時暫存資料表不為空白了，省去了頻繁的處理表檔案描述符，並發使用者數也穩定的保持在了40多萬。