mysql存储过程中使用临时表 转

转自https://www.cnblogs.com/opaljc/archive/2013/05/27/3101033.html

当工作在很大的表上时，您可能偶尔需要运行很多查询获得一个大量数据的小的子集，不是对整个表运行这些查询，而是让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多万。