如何使用MySQL优化查询表是否包含10000个条目? [英] How to optimize query if table contain 10000 entries using MySQL?

问题描述

当我像这样执行此查询时，它们花费了很多时间，因为user_fans表包含 10000 个用户条目.我该如何优化呢?

When I execute this query like this they take so much execution time because user_fans table contain 10000 users entries. How can I optimize it?

查询

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中获取....

I would try to simplify this COMPLETELY by putting triggers on your other tables, and just adding a few columns to your User_Fans table... One for each respective count() you are trying to get... from Posts, PostLikes, PostComments, PostCommentLikes.

将记录添加到任何表中时，只需更新user_fans表以将1加到计数...无论如何，它实际上是基于用户键ID的.至于"LIKES" ...类似，仅在某些事物被触发为"Like"的情况下，才添加1.然后，您的查询将是单个记录上的直接数学运算，并且不依赖于任何联接来计算加权"总价值.当您的表变得更大时，查询也将变得更长，因为它们有更多的数据要通过并进行汇总.您正在遍历每条user_fan记录，从本质上讲，它是查询所有其他表中的每条记录.

When a record is added to whichever table, just update your user_fans table to add 1 to the count... it will be virtually instantaneous based on the user's key ID anyhow. As for the "LIKES"... Similar, only under the condition that something is triggered as a "Like", add 1.. Then your query will be a direct math on the single record and not rely on ANY joins to compute a "weighted" total value. As your table gets even larger, the queries too will get longer as they have more data to pour through and aggregate. You are going through EVERY user_fan record which in essence is querying every record from all the other tables.

话虽如此，将表保持原样，我将进行如下重组...

All that being said, keeping the tables as you have them, I would restructure as follows...

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.

The LEFT JOIN to prequeries runs those queries ONCE through, then the entire thing is joined instead of being hit as a sub-query for every record. By using the COALESCE(), if there are no such entries in the LEFT JOINed table results, you won't get hit with NULL values messing up the calcs, so I've defaulted them to 000000.

问题的澄清

CLARIFICATION OF YOUR QUESTIONS

您可以将任何QUERY作为"AS AliasResult". "As"还可以用于简化任何长表名，以简化可读性.别名也可以使用相同的表，但用作不同的别名，以获取相似的内容，但用途不同.

You can have any QUERY as an "AS AliasResult". The "As" can also be used to simplify any long table names for simpler readability. Aliases can also be using the same table but as a different alias to get similar content, but for different purpose.

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").如果您想预先限制一组其他复杂条件，并希望在对所有其他最终结果进行额外联接之前要使用较小的表，则可以这样做.

Now, the third query above is not practical requiring the ( full query resulting in alias "PQ" representing "PreQuery" ). This could be done if you wanted to pre-limit a certain set of other complex conditions and wanted a smaller set BEFORE doing extra joins to many other tables for all final results.

由于"FROM"并不一定要是实际的表，而是可以本身就是一个查询，也可以是该查询中使用的任何其他位置，因此它必须知道如何引用此预查询结果集.

Since a "FROM" does not HAVE to be an actual table, but can be a query in itself, any place else used in the query, it has to know how to reference this prequery resultset.

此外，在查询字段时，它们也可以是"As FinalColumnName"，以将结果简化为也可以使用它们的地方.

Also, when querying fields, they too can be "As FinalColumnName" to simplify results to where ever they will be used too.

选择 CONCAT(User.Salutation，User.LastName)作为CourtesyName 来自...

select CONCAT( User.Salutation, User.LastName ) as CourtesyName from ...

选择 Order.NonTaxable +可订购 +(Order.Taxable * Order.SalesTaxRate)作为OrderTotalWithTax 来自...

select Order.NonTaxable + Order.Taxable + ( Order.Taxable * Order.SalesTaxRate ) as OrderTotalWithTax from ...

"As" columnName不是必需的聚合，但最常见的是这种方式.

The "As" columnName is NOT required being an aggregate, but is most commonly seen that way.

现在，关于MySQL变量...如果您正在执行存储过程，那么许多人会在过程的其余部分之前预先声明它们设置其默认值.您只需设置并为结果提供别名"参考，就可以在查询中内联它们.在执行这些变量时，选择将模拟始终返回单记录值的值.它几乎就像查询中使用的可更新的单个记录.您不需要应用任何特定的"Join"条件，因为它可能对查询中的其余表没有任何影响...本质上，创建笛卡尔结果，但是永远不会创建针对任何其他表的一条记录无论如何都可以复制，因此不会对下游造成损害.

Now, with respect to the MySQL variables... If you were doing a stored procedure, many people will pre-declare them setting their default values before the rest of the procedure. You can do them in-line in a query by just setting and giving that result an "Alias" reference. When doing these variables, the select will simulate always returning a SINGLE RECORD worth of the values. Its almost like an update-able single record used within the query. You don't need to apply any specific "Join" conditions as it may not have any bearing on the rest of the tables in a query... In essence, creates a Cartesian result, but one record against any other table will never create duplicates anyhow, so no damage downstream.

select 
       ...
   from 
      ( select @SomeVar := 0,
               @SomeDate := curdate(),
               @SomeString := "hello" ) as SQLVars

现在，sqlvars是如何工作的.想一想线性程序...查询运行时，将按照确切的顺序执行一个命令.然后将该值重新存储回"SQLVars"记录中，以备下次使用.但是，您不会将其引用为SQLVars.SomeVar或SQLVars.SomeDate ...只是@SomeVar:= someNewValue.现在，在查询中使用@var时，它也将作为"As ColumnName"存储在结果集中.有时，这只是准备下一条记录时的占位符计算值.然后，每个值都可直接用于下一行.因此，鉴于以下示例...

Now, how the sqlvars work. Think of a linear program... One command is executed in the exact sequence as the query runs. That value is then re-stored back in the "SQLVars" record ready for the next time through. However, you don't reference it as SQLVars.SomeVar or SQLVars.SomeDate... just the @SomeVar := someNewValue. Now, when the @var is used in a query, it is also stored as an "As ColumnName" in the result set. Some times, this can be just a place-holder computed value in preparation of the next record. Each value is then directly available for the next row. So, given the following sample...

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的值是如何在每一列使用时使用的...因此，即使在同一记录上，更新的值也可立即用于下一列...也就是说，现在来看尝试构建一个模拟记录数/每个客户的排名...

Notice how the value of @SomeVar is used as each column uses it... So even on the same record, the updated value is immediately available for the next column... That said, now look at trying to build a simulated record count / ranking per each customer...

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 ...在路径上继续.

The "Order By" clause forces the results to be returned in sequence first. So, here, the records per customer are returned. First time through, LastID is 0 and customer ID is say...5. Since different, it returns 1 as the @SeqNo, THEN it preserves that customer ID into the @LastID field for the next record. Now, next record for customer... Last ID is the the same, so it takes the @SeqNo (now = 1), and adds 1 to 1 and becomes #2 for the same customer... Continue on the path...

为了更好地编写查询，请查看MySQL标记并查看一些重要的贡献者.研究问题和一些复杂的答案，以及解决问题的方式.并不是说没有其他一些信誉评分较低的人才刚刚起步并完全胜任，但是您会发现谁给出了很好的答案以及为什么给出了答案.看看他们发布的答案的历史.您阅读和遵循的内容越多，编写更复杂的查询的内容就越多.

As for getting better at writing queries, take a look at the MySQL tag and look at some of the heavy contributors. Look into the questions and some of the complex answers and how problems solving works. Not to say there are not others with lower reputation scores just starting out and completely competent, but you'll find who gives good answers and why's. Look at their history of answers posted too. The more you read and follow, the more you'll get a better handle on writing more complex queries.

这篇关于如何使用MySQL优化查询表是否包含10000个条目?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！