MySQL百分位數計算(第三次優化)

壹頁書發表於2018-07-04
MySql優化
第一版:
http://blog.itpub.net/29254281/viewspace-2157111/

第二版:
http://blog.itpub.net/29254281/viewspace-2157209/

第三版:
http://blog.itpub.net/29254281/viewspace-2157299/


在第三版 第二個SQL的基礎上優化

主要是採用儲存過程,在中間計算結果中,增加了一個索引

原來的SQL

  1. select t5.query_time,t5.ts,t2.v from (
  2.     select query_time,total,v, floor(1+(total-1)*v) rn
  3.     from (
  4.          select query_time,count(*) total from t group by query_time
  5.     ) t3, (select 0.71 v,1 seq union all select 0.81,2 union all select 0.91,3) t4
  6. )
  7. t2 inner join (
  8.     select
  9.     query_time,
  10.     case when @gid=query_time then @rn:=@rn+1 when @gid:=query_time then @rn:=1 end rn,
  11.     ts
  12.     from (
  13.         select * from t ,(select @gid:='',@rn:=0) vars order by query_time,ts
  14.     ) t1
  15. ) t5 on (t2.query_time=t5.query_time and t2.rn=t5.rn )


改進為儲存過程

  1. drop procedure p;
  2. delimiter $$
  3. create procedure p()
  4. begin
  5.     drop table if exists tmp_result;
  6.     
  7.     create temporary table tmp_result(
  8.         query_time date,
  9.         rn int,
  10.         ts float,
  11.         key(query_time,rn)
  12.     ) engine =memory;
  13.     
  14.     insert ignore into tmp_result
  15.     select
  16.     query_time ,
  17.     case when @gid=query_time then @rn:=@rn+1 when @gid:=query_time then @rn:=1 end rn,
  18.     ts
  19.     from (
  20.         select * from t ,(select @gid:='',@rn:=0) vars order by query_time,ts
  21.     ) t1 ;

  23.     select t5.query_time,t5.ts,t2.v from (
  24.         select query_time,total,v, floor(1+(total-1)*v) rn
  25.         from (
  26.              select query_time,count(*) total from t group by query_time
  27.         ) t3, (select 0.71 v,1 seq union all select 0.81,2 union all select 0.91,3) t4
  28.     )
  29.     t2 inner join tmp_result t5 on (t2.query_time=t5.query_time and t2.rn=t5.rn );
  30. end $$

  32. delimiter ;

  34. call p
使用儲存過程 一般 11秒 左右

優化從 140秒 到 33秒 到 22秒 到 15秒 
最終定版在 11秒

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29254281/viewspace-2157303/，如需轉載，請註明出處，否則將追究法律責任。

相關文章