当前位置:Gxlcms > PHP教程 > 一条SQL执行多次sum特别慢

一条SQL执行多次sum特别慢

时间:2021-07-01 10:21:17 帮助过:27人阅读

SELECT agentid,count(id) AS allnum,
    sum(if(review_flag = 'p'AND delflag IN ('N' , 'P'),1,0)) AS approved_1,
    sum(if(review_flag = 'E', 1, 0)) AS approved_2,
    sum(if(review_mode = '3' AND review_flag = 'Y',1,0)) AS approved_3,
    sum(if(review_mode = '2' AND review_flag = 'Y',1,0)) AS approved_4,
    sum(if(review_mode = '1' AND review_flag = 'Y',1,0)) AS approved_5,
    sum(if(review_flag = 'N' AND resubmit = 'Y',1,0)) AS approved_6,
    sum(if(review_flag = 'N' AND resubmit = 'N',1,0)) AS approved_7
    FROM talbe WHERE agentid IN ('A' , 'B', 'C') AND delflag IN ('N' , 'P', 'H') 
    GROUP BY agentid

      
该语句执行的时候 需要20多S才完成。

回复内容:

SELECT agentid,count(id) AS allnum,
    sum(if(review_flag = 'p'AND delflag IN ('N' , 'P'),1,0)) AS approved_1,
    sum(if(review_flag = 'E', 1, 0)) AS approved_2,
    sum(if(review_mode = '3' AND review_flag = 'Y',1,0)) AS approved_3,
    sum(if(review_mode = '2' AND review_flag = 'Y',1,0)) AS approved_4,
    sum(if(review_mode = '1' AND review_flag = 'Y',1,0)) AS approved_5,
    sum(if(review_flag = 'N' AND resubmit = 'Y',1,0)) AS approved_6,
    sum(if(review_flag = 'N' AND resubmit = 'N',1,0)) AS approved_7
    FROM talbe WHERE agentid IN ('A' , 'B', 'C') AND delflag IN ('N' , 'P', 'H') 
    GROUP BY agentid

      
该语句执行的时候 需要20多S才完成。

  1. 调整表结构. 似乎你把很复杂的逻辑放在 数据表结构里了.

  2. 多次sum不是问题. 主要在于这个group by上, 一般的, mysql 全表扫一边, 创建临时表, 排序分组. 这个是很慢的. 你可以试一下 建立 (agentid, delflag, review_flag, review_mode, resubmit, id) 的联合索引.

索引,从新设计你自己标的结构。。。怎么会sum这么多次。。。

这不是sum的问题啊,这条语句本来就复杂。

这种情况我想应该是索引建立的不够科学吧,你可以借助EXPLAIN来具体查看什么地方会卡住,是否用到索引。
同时,给你一个建议,你应该把表结构贴出来,这样路过的大神就能具体帮你分析分析了。

先分组 sum,把结果取到你的程序里去继续加工

人气教程排行