http://blog.itpub.net/29254281/viewspace-2157111/
第二版:
http://blog.itpub.net/29254281/viewspace-2157209/
第三版:
http://blog.itpub.net/29254281/viewspace-2157299/
在第三版 第二个SQL的基础上优化
主要是采用存储过程,在中间计算结果中,增加了一个索引
原来的SQL
-
select t5.query_time,t5.ts,t2.v from (
-
select query_time,total,v, floor(1+(total-1)*v) rn
-
from (
-
select query_time,count(*) total from t group by query_time
-
) t3, (select 0.71 v,1 seq union all select 0.81,2 union all select 0.91,3) t4
-
)
-
t2 inner join (
-
select
-
query_time,
-
case when @gid=query_time then @rn:=@rn+1 when @gid:=query_time then @rn:=1 end rn,
-
ts
-
from (
-
select * from t ,(select @gid:='',@rn:=0) vars order by query_time,ts
-
) t1
- ) t5 on (t2.query_time=t5.query_time and t2.rn=t5.rn )
改进为存储过程
-
drop procedure p;
-
delimiter $$
-
create procedure p()
-
begin
-
drop table if exists tmp_result;
-
-
create temporary table tmp_result(
-
query_time date,
-
rn int,
-
ts float,
-
key(query_time,rn)
-
) engine =memory;
-
-
insert ignore into tmp_result
-
select
-
query_time ,
-
case when @gid=query_time then @rn:=@rn+1 when @gid:=query_time then @rn:=1 end rn,
-
ts
-
from (
-
select * from t ,(select @gid:='',@rn:=0) vars order by query_time,ts
-
) t1 ;
-
-
select t5.query_time,t5.ts,t2.v from (
-
select query_time,total,v, floor(1+(total-1)*v) rn
-
from (
-
select query_time,count(*) total from t group by query_time
-
) t3, (select 0.71 v,1 seq union all select 0.81,2 union all select 0.91,3) t4
-
)
-
t2 inner join tmp_result t5 on (t2.query_time=t5.query_time and t2.rn=t5.rn );
-
end $$
-
-
delimiter ;
-
- call p
优化从 140秒 到 33秒 到 22秒 到 15秒
最终定版在 11秒