关于临时表的存储引擎问题

[gungod]

有1个存储过程:proc_sp1；
功能：
按规则循环读取数据库某表的数据，插入临时表tmp_tb中。

现在因为执行这个过程耗时很长，打算优化下，
考虑临时表只是插入临时数据供查询而已，打算修改临时的存储引擎；
（当前数据库存储引擎为INNODB）

执行测试结果如下：
MYISAM : 91.922 sec；
INNODB : 92.438 sec；
MEMORY : 91.856 sec；

这样比较下，几乎没有任何不同，
是不是临时表使用什么引擎都一样？

[gungod]

我贴出过程的代码，给大家看看

CREATE procedure Proc_Weather_Search(bdate datetime,edate datetime,irwid int,deffer int)
BEGIN

declare tBeforDateTime datetime;
declare tInDateTime datetime;
declare tSingInDateTime datetime;
declare tInstantDir int;
declare tInstantSp int;
declare tVisiValue1Min int;
declare tMin1RVR int;
declare tAirtemp numeric(18, 1);
declare tHumity int;
declare tDp numeric(18, 1);
declare tQFE numeric(18, 1);
declare tQNH numeric(18, 1);
declare tInstantRainValue numeric(18, 1);
declare tCloudBase int;

CREATE TEMPORARY TABLE  if not exists tmp_Weather_Search(SingInDateTime datetime,InstantDir int,InstantSp int,VisiValue1Min int,Min1RVR int,
Airtemp numeric(18, 1),Humity int,Dp numeric(18, 1),QFE numeric(18, 1),QNH numeric(18, 1),InstantRainValue numeric(18, 1),
CloudBase int ,primary key(SingInDateTime)) ENGINE = MYISAM;
delete from tmp_Weather_Search;
       
	if deffer=1 or deffer=2 then
	BEGIN
		set tSingInDateTime = bdate;
		WHILE(tSingInDateTime <= edate) DO
			set tBeforDateTime = ADDDATE(tSingInDateTime, INTERVAL -1 MINUTE);
	      set tInDateTime 	= ADDDATE(tSingInDateTime, INTERVAL 1 MINUTE);
	      set tInstantDir 	= NULL;
	      set tInstantSp 		= NULL;
	      set tVisiValue1Min = NULL;
	      set tMin1RVR 		= NULL;
	      set tAirtemp 		= NULL;
	      set tHumity 			= NULL;
	      set tDp 				= NULL;
	      set tQFE 				= NULL;
	      set tQNH 				= NULL;
	      set tInstantRainValue 	= NULL;
	      set tCloudBase	= NULL;
	      
		select InstantDir ,InstantSp into tInstantDir,tInstantSp 
			from hy_wind_c left join hy_terminal_t on tmname = terminalid
			where rwid = irwid and SingInDateTime > tBeforDateTime and SingInDateTime <= tSingInDateTime
			order by SingInDateTime desc  limit 1;
	      if tInstantDir IS NULL OR  tInstantSp IS NULL then
				select  InstantDir,InstantSp into tInstantDir,tInstantSp 
				from hy_wind_c left join hy_terminal_t on tmname = terminalid
				where rwid = irwid and SingInDateTime >= tSingInDateTime and SingInDateTime < tInDateTime
				order by SingInDateTime  limit 1;
	      end if;
	      
	

			select VisiValue1Min INTO tVisiValue1Min 
			from hy_visi_c_Mor left join hy_terminal_t on tmname = terminalid
			where rwid = irwid and SingInDateTime > tBeforDateTime and SingInDateTime <= tSingInDateTime
			order by SingInDateTime desc limit 1;  
	      
			
			select Min1RVR into tMin1RVR 
			from hy_visi_c_Rvr left join hy_terminal_t on tmname = terminalid
			where rwid = irwid and SingInDateTime > tBeforDateTime and SingInDateTime <= tSingInDateTime
			order by SingInDateTime desc limit 1;  
			 
		
			select Airtemp into tAirtemp
			from hy_Tmpr_c left join hy_terminal_t on tmname = terminalid   
			where rwid = irwid and SingInDateTime > tBeforDateTime and SingInDateTime <= tSingInDateTime
			order by SingInDateTime desc limit 1;   
			
		
			select Humity into tHumity
			from hy_Humi_c left join hy_terminal_t on tmname = terminalid
			where rwid = irwid and SingInDateTime > tBeforDateTime and SingInDateTime <= tSingInDateTime
			order by SingInDateTime desc limit 1; 
			
			
			select Dp into tDp
			from hy_Dp_c left join hy_terminal_t on tmname = terminalid
			where rwid = irwid and SingInDateTime > tBeforDateTime and SingInDateTime <= tSingInDateTime
			order by SingInDateTime desc limit 1; 
			
		
			select QFE,QNH into tQFE,tQNH 
			from hy_Pres_c left join hy_terminal_t on tmname = terminalid
			where rwid = irwid and SingInDateTime > tBeforDateTime and SingInDateTime <= tSingInDateTime
			order by SingInDateTime desc limit 1; 
			
		
			select InstantRainValue into tInstantRainValue 
			from hy_Rain_c left join hy_terminal_t on tmname = terminalid
			where rwid = irwid and SingInDateTime > tBeforDateTime and SingInDateTime <= tSingInDateTime
			order by SingInDateTime desc limit 1; 
			
			
			select CloudBase into tCloudBase 
			from hy_Cloud_c left join hy_terminal_t on tmname = terminalid
			where rwid = irwid and SingInDateTime > tBeforDateTime and SingInDateTime <= tSingInDateTime
			order by SingInDateTime desc limit 1; 
		
	    	insert into tmp_Weather_Search 
			values(tSingInDateTime,tInstantDir,tInstantSp,tVisiValue1Min,tMin1RVR,tAirtemp,tHumity,
				tDp,tQFE,tQNH,tInstantRainValue,tCloudBase);
				
			 set tSingInDateTime = DATE_ADD(tSingInDateTime, interval deffer minute);
		END WHILE;
	END;
	END if;
	
END//

[zuoxingyu]

你所谓的“临时表”是TEMPORARY TABLE么？还是逻辑意义上的临时表？

[gungod]

[Quote=引用 1 楼 zuoxingyu 的回复:]
你所谓的“临时表”是TEMPORARY TABLE么？还是逻辑意义上的临时表？
[/Quote]

TEMPORARY TABLE,
不好意思，没说清楚。

[ACMAIN_CHM]

你的表是什么样？ 查询语句是什么？你的测试结果是怎么得出来的？

[zuoxingyu]

贴出你的插入语句吧。

[zuoxingyu]

这个贴出来看看。

explain select InstantRainValue into tInstantRainValue 
            from hy_Rain_c left join hy_terminal_t on tmname = terminalid
            where rwid = irwid and SingInDateTime > tBeforDateTime and SingInDateTime <= tSingInDateTime
            order by SingInDateTime desc limit 1; 

从你的逻辑来看，从不同的表取出不同的数，然后组合起来，再写入到临时表，那么慢的地方应该是在每个取值的SQL中。

[gungod]

[Quote=引用 7 楼 zuoxingyu 的回复:]
这个贴出来看看。


SQL code
explain select InstantRainValue into tInstantRainValue
from hy_Rain_c left join hy_terminal_t on tmname = terminalid
where rwid = irwid and SingInDat……
[/Quote]
很对，有办法优化没？

[zuoxingyu]

[Quote=引用 8 楼 gungod 的回复:]

引用 7 楼 zuoxingyu 的回复:
这个贴出来看看。


SQL code
explain select InstantRainValue into tInstantRainValue
from hy_Rain_c left join hy_terminal_t on tmname = terminalid
where rwid = irwid and SingInDat………
[/Quote]

你用explain分析每个小查询，看看他们的效率，是否使用到索引了。一个个分析。
不过你现在的做法有点不好，我的建议是单独出来的每个小查询都各自做各自的，在程序里面组装。

[gungod]

代码已贴，自己顶一下。

[gungod]

哦，因为涉及到循环，可能这个就没办法优化了，
我每个查询都加了条件了，我想能不能设置缓存大小，让MYSQL占用更多资源。
这样的话，执行会不会快点？

[zuoxingyu]

分别分析上面的所有子查询，然后给他们对应的字段加上索引，这样效率会快很多了。