当工作在很大的表上时,您可能偶尔需要运行很多查询获得一个大量数据的小的子集,不是对整个表运行这些查询,而是让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多万。
本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/jesenblog/archive/2009/04/27/4130983.aspx
创建临时表很容易,给正常的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多万。
本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/jesenblog/archive/2009/04/27/4130983.aspx