当我像这样执行此查询时,它们花费了很多时间,因为user_fans表包含10000个用户条目.我该如何优化？
询问
SELECT uf.`user_name`,uf.`user_id`,
@post := (SELECT COUNT(*) FROM post WHERE user_id = uf.`user_id`) AS post,
@post_comment_likes := (SELECT COUNT(*) FROM post_comment_likes WHERE user_id = uf.`user_id`) AS post_comment_likes,
@post_comments := (SELECT COUNT(*) FROM post_comments WHERE user_id = uf.`user_id`) AS post_comments,
@post_likes := (SELECT COUNT(*) FROM post_likes WHERE user_id = uf.`user_id`) AS post_likes,
(@post+@post_comments) AS `sum_post`,
(@post_likes+@post_comment_likes) AS `sum_like`,
((@post+@post_comments)*10) AS `post_cal`,
((@post_likes+@post_comment_likes)*5) AS `like_cal`,
((@post*10)+(@post_comments*10)+(@post_likes*5)+(@post_comment_likes*5)) AS `total`
FROM `user_fans` uf ORDER BY `total` DESC lIMIT 20解决方法:
我会尝试通过在其他表上放置触发器,并仅向您的User_Fans表添加几列来简化此过程.您尝试从Posts,PostLikes,PostComments, PostCommentLikes.
当将记录添加到任何表时,只需更新user_fans表以将1加到计数…无论如何,它实际上是基于用户键ID的.至于“ LIKES” …类似,仅在某些事物被触发为“ Like”的情况下,添加1.然后,您的查询将是单个记录上的直接数学运算,并且不依赖于任何联接来计算“加权”总价值.当您的表变得更大时,查询也将变得更长,因为它们有更多的数据需要注入和汇总.您正在遍历每条user_fan记录,从本质上讲,它是查询所有其他表中的每条记录.
话虽如此,将表保持原样,我将进行如下重组…
SELECT
uf.user_name,
uf.user_id,
@pc := coalesce( PostSummary.PostCount, 000000 ) as PostCount,
@pl := coalesce( PostLikes.LikesCount, 000000 ) as PostLikes,
@cc := coalesce( CommentSummary.CommentsCount, 000000 ) as PostComments,
@cl := coalesce( CommentLikes.LikesCount, 000000 ) as CommentLikes,
@pc + @cc AS sum_post,
@pl + @cl AS sum_like,
@pCalc := (@pc + @cc) * 10 AS post_cal,
@lCalc := (@pl + @cl) * 5 AS like_cal,
@pCalc + @lCalc AS `total`
FROM
( select @pc := 0,
@pl := 0,
@cc := 0,
@cl := 0,
@pCalc := 0
@lCalc := 0 ) sqlvars,
user_fans uf
LEFT JOIN ( select user_id, COUNT(*) as PostCount
from post
group by user_id ) as PostSummary
ON uf.user_id = PostSummary.User_ID
LEFT JOIN ( select user_id, COUNT(*) as LikesCount
from post_likes
group by user_id ) as PostLikes
ON uf.user_id = PostLikes.User_ID
LEFT JOIN ( select user_id, COUNT(*) as CommentsCount
from post_comment
group by user_id ) as CommentSummary
ON uf.user_id = CommentSummary.User_ID
LEFT JOIN ( select user_id, COUNT(*) as LikesCount
from post_comment_likes
group by user_id ) as CommentLikes
ON uf.user_id = CommentLikes.User_ID
ORDER BY
`total` DESC
LIMIT 20
My variables are abbreviated as
"@pc" = PostCount
"@pl" = PostLikes
"@cc" = CommentCount
"@cl" = CommentLike
"@pCalc" = weighted calc of post and comment count * 10 weighted value
"@lCalc" = weighted calc of post and comment likes * 5 weighted value进行预查询的LEFT JOIN一次运行这些查询,然后将整个事物联接在一起,而不是作为每条记录的子查询命中.通过使用COALESCE(),如果LEFT JOINed表结果中没有此类条目,则不会被NULL值打乱计算,因此,我将其默认设置为000000.
澄清您的问题
您可以将任何QUERY作为“ AS AliasResult”. “ As”还可以用于简化任何长表名,以简化可读性.别名也可以使用相同的表,但用作不同的别名,以获取相似的内容,但用途不同.
select
MyAlias.SomeField
from
MySuperLongTableNameInDatabase MyAlias ...
select
c.LastName,
o.OrderAmount
from
customers c
join orders o
on c.customerID = o.customerID ...
select
PQ.SomeKey
from
( select ST.SomeKey
from SomeTable ST
where ST.SomeDate between X and Y ) as PQ
JOIN SomeOtherTable SOT
on PQ.SomeKey = SOT.SomeKey ...现在,上面的第三个查询不切实际,需要(完整查询导致别名“ PQ”代表“ PreQuery”).如果您想预先限制某些其他复杂条件的集合,并希望在对所有其他最终结果进行额外联接到许多其他表之前,需要一个较小的集合,则可以这样做.
由于“ FROM”不一定要是实际的表,而可以是查询本身,也可以是查询中使用的任何其他位置,因此它必须知道如何引用此预查询结果集.
同样,在查询字段时,它们也可以是“ As FinalColumnName”,以将结果简化为也可以使用它们的地方.
选择
CONCAT(User.Salutation,User.LastName)作为CourtesyName
从…
选择
Order.NonTaxable
Order.Taxable
(Order.Taxable * Order.SalesTaxRate)作为OrderTotalWithTax从…
不需要“ As” columnName是一个聚合,但是最常见的是这种方式.
现在,关于MySQL变量…如果您正在执行存储过程,许多人会在过程的其余部分之前预先声明它们设置其默认值.您只需设置并为结果提供“别名”引用,就可以在查询中内联它们.在执行这些变量时,选择将模拟始终返回价值单记录的值.它几乎就像查询中使用的可更新的单个记录.您不需要应用任何特定的“联接”条件,因为它可能对查询中的其余表没有任何影响…本质上,创建笛卡尔结果,但是永远不会创建针对任何其他表的一条记录无论如何重复,因此不会对下游造成损害.
select
...
from
( select @SomeVar := 0,
@SomeDate := curdate(),
@SomeString := "hello" ) as SQLVars现在,sqlvars是如何工作的.想一想线性程序…查询运行时,将按照确切的顺序执行一个命令.然后将该值重新存储回“ SQLVars”记录中,以备下次使用.但是,您不会将其引用为SQLVars.SomeVar或SQLVars.SomeDate …只是@SomeVar：= someNewValue.现在,在查询中使用@var时,它也将作为“ As ColumnName”存储在结果集中.有时,这只是准备下一条记录时的占位符计算值.然后,每个值可直接用于下一行.因此,鉴于以下示例…
select
@SomeVar := SomeVar * 2 as FirstVal,
@SomeVar := SomeVar * 2 as SecondVal,
@SomeVar := SomeVar * 2 as ThirdVal
from
( select @SomeVar := 1 ) sqlvars,
AnotherTable
limit 3
Will result in 3 records with the values of
FirstVal SecondVal ThirdVal
2 4 8
16 32 64
128 256 512请注意,@ SomeVar的值是如何在每个列使用它时使用的…因此,即使在同一记录上,更新的值也可立即用于下一个列…也就是说,现在来看尝试构建模拟记录数/每个客户的排名…
select
o.CustomerID,
o.OrderID
@SeqNo := if( @LastID = o.CustomerID, @SeqNo +1, 1 ) as CustomerSequence,
@LastID := o.CustomerID as PlaceHolderToSaveForNextRecordCompare
from
orders o,
( select @SeqNo := 0, @LastID := 0 ) sqlvars
order by
o.CustomerID“ Order By”子句强制首先按顺序返回结果.因此,这里将返回每个客户的记录.第一次使用,LastID为0,客户ID为… 5.由于不同,它将返回1作为@SeqNo,然后将该客户ID保留在@LastID字段中以用于下一条记录.现在,客户的下一条记录…上一个ID是相同的,因此它使用@SeqNo(现在为1),并将1加1并成为同一客户的#2 …在路径上继续. .
为了更好地编写查询,请看一下MySQL标记并看一下其中一些重要的因素.研究问题和一些复杂的答案以及解决问题的方式.并不是说没有其他一些信誉评分较低的人才刚刚起步并完全胜任,但您会发现谁给出了很好的答案,为什么给出了答案.看看他们发布的答案的历史.您阅读和遵循的内容越多,越能更好地处理更复杂的查询.
标签：sql,mysql,optimization,query-optimization