ITPub博客

首页 > 数据库 > MySQL > MySQL百分位数计算(第三次优化)

MySQL百分位数计算(第三次优化)

原创 MySQL 作者:壹頁書 时间:2018-07-04 12:03:14 1 删除 编辑
第一版:
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 ;

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

  30. delimiter ;

  31. call p
使用存储过程 一般 11秒 左右

优化从 140秒 到 33秒 到 22秒 到 15秒 
最终定版在 11秒

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29254281/viewspace-2157303/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2013-10-19

  • 博文量
    621
  • 访问量
    5957364