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多万。