如何通过order by，group by和join提高查询性能 [英] How to improve query performance with order by, group by and joins

问题描述

当联接具有数百万个数据的多个表时，顺序出现问题.但是我得到了解决方案，因为不是通过单独使用EXISTS来进行连接，而是通过以下问题来提高性能

I Had a problem with order by when joins multiple tables which have millions of data. But I got solution as instead of join with distinct use of EXISTS will improve performance from the following question

如何通过连接来改善性能mysql

SELECT
   `tracked_twitter` . *,
   COUNT( * ) AS twitterContentCount,
   retweet_count + favourite_count + reply_count AS engagement 
FROM
   `tracked_twitter` 
   INNER JOIN
      `twitter_content` 
      ON `tracked_twitter`.`id` = `twitter_content`.`tracked_twitter_id` 
   INNER JOIN
      `tracker_twitter_content` 
      ON `twitter_content`.`id` = `tracker_twitter_content`.`twitter_content_id` 
WHERE
   `tracker_twitter_content`.`tracker_id` = '88'
GROUP BY
   `tracked_twitter`.`id` 
ORDER BY
   twitterContentCount DESC LIMIT 20 OFFSET 0

但是该方法解决了是否只需要父表的结果集.如果我想在父表以外的地方执行分组计数和其他数学函数，该怎么办?我写了一个查询来解决我的标准，但是执行需要20秒.我如何优化它?

But that method solves if I only need the result set from the parent table. What if, I want to execute grouped count and other math functions in other than parent table. I wrote a query that solves my criteria, but it takes 20 sec to execute. How can I optimize it ??.

预先感谢

推荐答案

鉴于查询已经相当简单，我要查找的选项是...

Given the query is already fairly simple the options I'd look in to are ...

• 执行计划(查找可能要添加的缺失索引)
• 缓存(以确保SQL已经将所有数据存储在ram中)
• 反规范化(将查询转为平面选择)
• 将数据缓存在应用程序中(因此您可以在其上使用PLINQ之类的东西)
• 使用基于ram的商店(redis，弹性商店)
• 文件组调整(将数据库物理地移动到速度更快的光盘上)
• 对表进行分区(以将原始数据分布在多个物理磁盘上)

您在列表中越靠下，解决方案就涉及越多. 我想这取决于您需要查询的速度以及扩展解决方案所需的数量.

The further you go down this list the more involved the solutions become. I guess it depends how fast you need the query to be and how much you need your solution to scale.

这篇关于如何通过order by，group by和join提高查询性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！