MySQL：复制数据以提高性能？

Question

我有一张大表格（200'000'000行）; 声明如下：

 thread( forum_id tinyint, thread_id int, 
         date_first datetime, date_last datetime
         replycount mediumint, extra blob )

论坛 ID 和主题 ID 是主键。对于大型论坛（约一百万个主题），我有时需要运行像 SELECT thread_id FROM thread ORDER BY date_last DESC LIMIT 500000, 10 这样的查询。这些带有巨大偏移量的查询需要一秒钟或者更长时间才能运行。

因此，我想通过复制数据，在拥有最多主题的论坛中创建一些表来加快速度。只有少数几个论坛的主题超过了 100,000 个，因此将会有一个类似于下面的表：

  thread_for_forumid_123456 ( thread_id int, 
         date_first datetime, date_last datetime
         replycount mediumint )

你对此有何看法？它是否能加速大型偏移查询？你有其他建议吗？谢谢。

- cedivad

---

你为什么需要使用如此高的偏移量来运行查询？还是只是为了测试目的？ - konsolenfreddy

性能？性能？那是什么可怕的非词汇！！！ - James Butler

不要复制数据，创建一个视图？ - Greg K

这不仅仅是一个测试，它是一个真实的应用程序！抱歉我的英语很糟糕。我会检查这个“视图”，谢谢。 - cedivad

在哪个现实世界的（论坛）应用程序中，您需要从数据库中提取4999990条记录？ - konsolenfreddy

抱歉，我混淆了。我想获取从500000开始的10行... - cedivad

Answer 1

首先，我会尽量避免你的做法。我认为这是避免性能问题的“最后机会”。

你有一些替代方案（从硬件到软件），可以购买Fusion i/o或者只是一个SSD硬盘 (Raid vs SSD vs FusionIO)。但是，你可以使用软件解决这个问题而且不需要投资任何资金。 在你的情况下，如果你还没有使用缓存（例如memcached），应该使用它。 MySQL有分区，虽然不是最好的选择，但是可以获得一些很好的性能提升。

但是，如果你采用你的想法，我建议你分片你的数据，使用一个值将你的数据分割成更平衡的表。 你可以做一些不好看的事情，比如创建50个像thread_0到thread_49的表，然后( forumid % 50 )这样你就能落到这50个表中的一个。 这样你就避免了每次创建论坛时都创建一个新表的问题，也避免了有N个表的问题。并且使用索引的forum_id查询将非常快。 而且你可以在应用程序中编写一些逻辑来管理分页，避免巨大的偏移。

祝好运！

PS: 我在MysqlPerfomanceBlog上添加了一篇博客文章“为什么你不想分片”

- Gonza

Answer 2

MySQL分区听起来像是你可能考虑的一个功能

- Sergey Kudriavtsev

Answer 3

看起来你的问题是大偏移量LIMIT 500000, 10。

我不明白为什么这里有5'000'000。如果你按照thread_id和forum_id进行过滤，偏移量应该非常小，因为我不相信一个帖子中有5百万个帖子。

SELECT thread_id FROM thread WHERE thread_id = 123456 ORDER BY date_last DESC LIMIT 10, 50

也许你可以看一下http://dev.mysql.com/doc/refman/5.1/en/limit-optimization.html。

- PiTheNumber

---

那个查询对我来说没有任何意义。为什么我要选择一个已知的线程ID的线程ID呢？ - cedivad

你打算如何在没有线程ID的情况下调用 thread_for_forumid_123456(...) 函数？ - PiTheNumber

123456是论坛ID。在这样的表格中，我将有几个主题ID，我会按日期排序并返回...我想我没有理解你的答案，抱歉。 - cedivad

我重新写了我的回答。现在可能更容易理解了。 - PiTheNumber

这里不是关于帖子的问题... 无论如何，你是在说如果我像我已经做的那样限制搜索（forumid =，threadid =），MySQL会像其他字段不存在一样，在偏移时间性能方面表现吗？谢谢！ - cedivad