优化两个大表的简单查询(Optimizing a simple query on two large

我想提供一个功能，我可以告诉的朋友最常浏览的网页。 我的朋友们表有570万行和意见表有530万行。 目前，我只是想运行这两个表的查询，并通过一个人的朋友找到了20点最观看页面的ID。

这里是因为我有现在查询：

SELECT page_id 
FROM `views` INNER JOIN `friendships` ON friendships.receiver_id = views.user_id 
WHERE (`friendships`.`creator_id` = 143416) 
GROUP BY page_id 
ORDER BY count(views.user_id) desc 
LIMIT 20

这里是如何的解释长相：

+----+-------------+-------------+------+-----------------------------------------+---------------------------------+---------+-----------------------------------------+------+----------------------------------------------+
| id | select_type | table       | type | possible_keys                           | key                             | key_len | ref                                     | rows | Extra                                        |
+----+-------------+-------------+------+-----------------------------------------+---------------------------------+---------+-----------------------------------------+------+----------------------------------------------+
|  1 | SIMPLE      | friendships | ref  | PRIMARY,index_friendships_on_creator_id | index_friendships_on_creator_id | 4       | const                                   |  271 | Using index; Using temporary; Using filesort | 
|  1 | SIMPLE      | views       | ref  | PRIMARY                                 | PRIMARY                         | 4       | friendships.receiver_id                 |   11 | Using index                                  | 
+----+-------------+-------------+------+-----------------------------------------+---------------------------------+---------+-----------------------------------------+------+----------------------------------------------+

意见表有（USER_ID，PAGE_ID）的一个主键，你可以看到这个被使用。 的友谊表具有（receiver_id，creator_id）的主键，和一个次级指数（creator_id）。

如果我通过和限进行查询，而不群，有这个特定的用户大约25000行 - 这是典型的。

在最近的实际运行中，这个查询了7秒过于执行，这对于一个Web应用程序体面的响应的时间太长了。

有一两件事我不知道是我是否应该调整二级指标是（creator_id，receiver_id）。 我不知道这会给多大的性能提升的虽然。 我今天可能会尝试根据这个问题的答案。

你能看到任何方式查询可以被改写，使之减轻快？

更新：我需要做的就可以了更多的测试，但它似乎我讨厌查询工作出更好，如果我不这样做的分组，并在数据库排序，但这样做在以后的红宝石。 总的时间要短得多 - 约80％，它似乎。 也许我的早期测试是有缺陷的 - 但这绝对值得更多的研究。 如果这是真的 - 那么跆拳道是Mysql的做什么？

Answer 1:

据我所知，最好的办法做出那样的“快如闪电”的查询，是创造每创造者跟踪每页的朋友页面访问量汇总表。

你可能会想保持它最新与触发器。 那么你的聚集已经做了你，这是一个简单的查询来获取最常浏览的网页。 你可以确保你有对汇总表进行正确的索引，使数据库甚至没有进行排序，以获得最大的观看。

汇总表的关键是保持在读为主的环境，聚集型的查询性能良好。 你做的前期，发生更新时（罕见），然后查询（频繁）没有工作，做任何工作。

如果您的数据并不能是完美的，而你写实际上是相当频繁的（这可能是对于像网页浏览的情况下），你可以在存储器批量了意见，并在后台处理它们，这样的朋友不要“T不得不采取保持汇总表上最新的打击，因为他们查看网页。 该解决方案还减少了对数据库（更少的过程更新所述简表）的争用。

Answer 2:

你绝对应该考虑非规范化此表。 如果创建一个单独的表维护用户ID和精确计数的每个页面观看了您的查询应该变得非常简单。

您可以轻松地通过使用你的意见表中，确实更新了“views_summary”表每当插入发生在“若干意见”表中的触发器保持此表。

你甚至可以进行非规范化此通过查看实际的关系进一步，或者只是维持每人顶的X页

希望这可以帮助，

翻转

Answer 3:

你看指标正确但如果friendship有非常大的行，你可能希望在指数(creator_id, receiver_id)以避免读到它的全部。

然而事情并不在这里，你为什么做了271行的文件排序？ 请确保您的MySQL有至少一个几兆tmp_table_size和max_heap_table_size 。 这应该使GROUP BY更快。

sort_buffer也应该有一个理智的价值。

文章来源: Optimizing a simple query on two large tables