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

Question

有1个存储过程:proc_sp1；
功能：
按规则循环读取数据库某表的数据，插入临时表tmp_tb中。现在因为执行这个过程耗时很长，打算优化下，
考虑临时表只是插入临时数据供查询而已，打算修改临时的存储引擎；
（当前数据库存储引擎为INNODB）执行测试结果如下：
MYISAM : 91.922 sec；
INNODB : 92.438 sec；
MEMORY : 91.856 sec；这样比较下，几乎没有任何不同，
是不是临时表使用什么引擎都一样？

Answer 1

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

Answer 2

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

Answer 3

我贴出过程的代码，给大家看看
CREATE procedure Proc_Weather_Search(bdate datetime,edate datetime,irwid int,deffer int)
BEGINdeclare 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//

Answer 4

这个贴出来看看。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中。

Answer 5

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

Answer 6

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