如何按天分割MySQL表？

Question

我正在运行MySQL 5.1，并将Web日志数据存储到表中。有一个日期时间列，我想按日进行分区。每天晚上，我都会将前一天的新数据添加到表中，这就是为什么我想按日进行分区的原因。通常情况下会有几百万行数据。我想按日进行分区，因为MySQL查询通常需要20秒才能完成。

简而言之，我要按每天进行分区，因为用户可以点击日历获取包含一天数据量的网站日志信息。数据跨越数百万行（针对单个日期）。

我看到很多分区文章的问题是您必须明确指定要分区的值？我不喜欢这种方式，因为这意味着我每晚都必须更改表以添加额外的分区。是否有内置的MySQL功能可以自动为我执行此操作，还是我必须编写bash脚本/ cron作业来每晚为我更改表？

例如，如果我要遵循以下示例： http://datacharmer.blogspot.com/2008/12/partition-helper-improving-usability.html “一年内，我会有365个分区。”

- egidra

---

@Bugs，还没有，但我打算在日期时间列上建立索引。这样能带来多少改进呢？不过，除此之外，我还需要进行分区吗？ - egidra

通过正确的索引，您可以期望获得很大的改进。我曾经只是通过添加一个索引，将执行时间从几个小时缩短到几秒钟。 - Bugs

我同意Bugs的观点；你的第一步应该是确保你已经正确地建立了索引。你应该使用EXPLAIN来帮助确定你需要哪些索引。一旦你拥有了正确的索引，你就会知道是否真的需要每天进行分区（不太可能）。 - Eric B.

Answer 1

索引是任何表格所必需的。索引（们）的详细信息来源于您拥有的SELECT；让我们看看它们。

经验法则：

• 不要对少于一百万行的表格进行分区。
• 不要使用超过约50个分区。
• 如果您在若干天/周/月后“清除旧数据”，请参阅我的博客以获取如何执行该操作的代码。
• PARTITION BY RANGE()是唯一有用的分区机制。

- Rick James

Answer 2

我曾经尝试过这个方法。最终我创建了一个定期运行的cron作业来进行分区（每月一次）。请注意，每个表最多只能有1024个分区（http://dev.mysql.com/doc/refman/5.1/en/partitioning-limitations.html）。

我可能不会直接推荐它。根据我的需求，我发现这在需要跨分区结果的任何搜索中都会显著减慢速度。

基于您提供的更新说明，我首先建议创建必要的索引。我建议阅读MySQL Optimization章节（特别是关于索引的部分），以更好地学习如何确保您拥有必要的索引。您还可以使用slow_query日志来帮助隔离问题查询。

一旦您缩小了范围，我可以看到您需要分区来限制特定分区的大小（例如为存储空间或快速截断等）。此时，您可以决定按月或按年进行分区。

使用日期作为分区键进行分区显然会强制您创建日期字段的索引。在开始进行定期分区等额外工作之前，请先完成这一步骤并查看效果如何。

- Eric B.

---

你建议我做什么替代方案？我从不需要跨分区查询。按这种方式进行分区是否值得？ - egidra

我不确定...我在考虑这个问题，但是脑海中没有一个好的解决方案。我的直觉是最多按月进行分区 - 至少这样你有100年的时间，而不是2年，来弄清楚你对模式要做什么。第二个问题是弄清楚你的确切需求是什么。你为什么要进行分区？如果你列出/解释一下你试图通过分区解决的问题，可能会有人有一个好的解决方案。 - Eric B.

嗨，我在原帖中添加了我的理由。 - egidra