一个sql里有多个count ,耗时严重,难道要拆开查询吗?哪个更快?

7哥 发布于 2016/06/16 13:27
阅读 2K+
收藏 2
SQL

一个sql里有多个count ,耗时严重,难道要拆开查询吗?哪个更快?

我查询一个list,难道要再遍历,分别区统计他的评论数,点赞数吗?这样岂不更费时间?

求指点

SELECT p.pid,p.uid,p.tid,p.title,p.text,p.tags,p.field,p.republish,p.transfer,p.view,p.time,p.poster
 ,u.uid,u.nickname,u.username,u.domain,u.vcode,u.email,u.phone,u.photo40 as p4,u.photo60 as p6,u.photo100 as p100 
,img.pid,img.path,img.size,img.frm,img.thumb,img.time as itime
 ,t.tid,t.name,t.descr,t.tags,t.vetted,t.defaultphoto,t.customphoto,t.time as ttime
 ,count(DISTINCT g.uid) as goodNum
 ,count(DISTINCT g.uid) as badNum 
,count(DISTINCT cmt.cid) as cmtNum
 ,count(DISTINCT hsg.uid) as hasGood 
,COUNT(DISTINCT mb.uid) as mcount
 ,COUNT(DISTINCT pt.pid) as pcount 
,COUNT(DISTINCT ufu.fid) as funsCount 
,COUNT(DISTINCT ufo.uid) as followsCount 
FROM post as p JOIN user as u ON u.uid=p.uid 
JOIN team as t ON t.tid=p.tid JOIN hot as h ON h.aid=p.pid AND h.model='post'
LEFT JOIN image as img ON img.pid=p.pid AND img.frm='post' 
LEFT JOIN motion as g ON g.tid=p.pid AND g.target='post' AND g.name='good' 
LEFT JOIN comment as cmt ON cmt.aid=p.pid AND cmt.model='post' 
LEFT JOIN member as mb ON mb.tid=t.tid LEFT JOIN post as pt ON pt.tid=t.tid 
LEFT JOIN motion as hsg ON hsg.tid=p.pid AND hsg.target='post' AND hsg.name='good' AND hsg.uid=3 
LEFT JOIN user_funs as ufo ON ufo.fid=u.uid AND ufo.uid!=ufo.fid 
LEFT JOIN user_funs as ufu ON ufu.uid=u.uid AND ufu.uid!=ufu.fid 
GROUP BY p.pid ORDER BY p.pid DESC




加载中
2
喜之郎
喜之郎
显示是表结构设计有问题。这些count的结果可以加冗余字段嘛。比较说要统计一个人的评论数,就在member表加一个commentCount字段。每发表一个评论就加一。这样就不需要每次count。又不是财务数据,少加了两次都无所谓的。
0
else
else
这是要实实显示的台下?
7哥
7哥
台下?
0
biny16
biny16
目测耗时应该不是count导致的,是太多表关联了吧
7哥
7哥
这关联的不算多
7哥
7哥
去掉所有count用时3s多 加上所有count,用时9到10s之多
0
ToBeHJH
ToBeHJH

这是什么数据库的?

可否贴一下这个SQL的PLAN?

ToBeHJH
ToBeHJH
回复 @7哥 : 执行计划。我觉得是关联太多的问题,按你提供的SQL,改了一下,你看看对不对
小小phper
小小phper
回复 @7哥 : 查询计划,一般的数据库工具都有会显示的
7哥
7哥
mysql 什么是sql plan?
0
乌龟壳
乌龟壳
内存不够大吧?
0
空无一长物
explain select .....
0
爱新觉罗玄楠
爱新觉罗玄楠
我觉得是关联太多的原因
0
小小phper
小小phper
对于这样的查询需求,一个字:缓存
0
ToBeHJH
ToBeHJH

引用来自“ToBeHJH”的评论

这是什么数据库的?

可否贴一下这个SQL的PLAN?

SELECT p.pid,p.uid,p.tid,p.title,p.text,p.tags,p.field,p.republish,p.transfer,p.view,p.time,p.poster
 ,u.uid,u.nickname,u.username,u.domain,u.vcode,u.email,u.phone,u.photo40 as p4,u.photo60 as p6,u.photo100 as p100 
,img.pid,img.path,img.size,img.frm,img.thumb,img.time as itime
 ,t.tid,t.name,t.descr,t.tags,t.vetted,t.defaultphoto,t.customphoto,t.time as ttime
 ,count(DISTINCT g.uid) as goodNum
 ,count(DISTINCT g.uid) as badNum 
,count(DISTINCT cmt.cid) as cmtNum
 ,count(DISTINCT case when g.uid=3 then hsg.uid end) as hasGood 
,COUNT(DISTINCT mb.uid) as mcount
 ,COUNT(DISTINCT pt.pid) as pcount 
,COUNT(DISTINCT ufu.fid) as funsCount 
,COUNT(DISTINCT ufu.uid) as followsCount 
FROM post as p JOIN user as u ON u.uid=p.uid 
JOIN team as t ON t.tid=p.tid 
JOIN hot as h ON h.aid=p.pid
INNER JOIN image as img ON img.pid=p.pid
INNER JOIN motion as g ON g.tid=p.pid
INNER JOIN comment as cmt ON cmt.aid=p.pid 
LEFT JOIN member as mb ON mb.tid=t.tid 
LEFT JOIN post as pt ON pt.tid=t.tid 
LEFT JOIN user_funs as ufo ON ufo.fid=u.uid AND ufo.uid!=ufo.fid 
WHERE h.model='post' AND img.frm='post' AND cmt.model='post' AND g.target='post' AND g.name='good'
GROUP BY p.pid ORDER BY p.pid DESC
7哥
7哥
您可以告诉我思路,不用帮我改
7哥
7哥
您给的这个执行不通
0
7哥
7哥
执行计划,你们要的是这个吗?
7哥
7哥
有索引
ToBeHJH
ToBeHJH
可以在连接列上建索引,干掉join buffer,SQL就跑得快了
返回顶部
顶部