如何按天分区MySQL表? [英] How to partition MySQL table by day?

问题描述

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

I'm running MySQL 5.1 and storing data from web logs into a table. There is a datetime column which I want to partition by day. Every night I add new data from the previous day into the table, which is why I want to partition by day. It is usually a few million rows. I want to partition by day because it usually takes 20 seconds for a MySQL query to complete.

简而言之，我想按每天进行分区，因为用户可以单击日历以获取由一天的数据组成的Web日志信息.数据跨越数百万行(一天).

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

The problem that I've seen with a lot of partitioning articles is that you have to explicitly specify what values you want to partition for? I don't like this way because it means that I'll have to alter the table every night in order to add an extra partition. Is there a built in MySQL feature to do this for me automatically, or will I have to write a bash script/cron job to alter the table for me every night?

例如，如果我要遵循以下示例: http://datacharmer.blogspot.com/2008/12/partition -helper-improving-usability.html

For example, if I were to follow the following example: http://datacharmer.blogspot.com/2008/12/partition-helper-improving-usability.html

一年后，我将有365个分区.

In one year, I would have 365 partitions.

推荐答案

我尝试了一次.我最终创建了一个cron作业，以定期(每月一次)进行分区.请记住，每个表最多有1024个分区(http://dev.mysql.com/doc/refman/5.1/en/partitioning-limitations.html).

I tried this once. I ended up creating a cron job to do the partitioning on a regular basis (once a month). Keep in mind that you have a maximum of 1024 partitions per table (http://dev.mysql.com/doc/refman/5.1/en/partitioning-limitations.html).

暂时，我可能不会推荐它.出于我的需要，我发现这会大大降低需要跨分区结果的所有搜索的速度.

Offhand, I probably wouldn't recommend it. For my needs, I saw this created a significant slowdown in any searches that that required cross-partition results.

根据您最新的解释，我首先建议创建必要的索引.我会读 MySQL优化一章(具体在该部分索引)，以更好地学习如何确保您拥有必要的索引.您还可以使用slow_query日志来帮助隔离有问题的查询.

Based on your updated explanation, I would first recommend to create the necessary indexes. I would read MySQL Optimization chapter (in specific the section on indexes), to better learn how to ensure you have the necessary indexes. You can also use the slow_query log to help isolate the problematic queries.

一旦缩小范围，我就会看到您需要更改分区，以希望通过分区来限制特定分区的大小(可能是为了存储空间或快速截断等).此时，您可以决定按月或按年进行分区.

Once you have that narrowed down, I can see your need for partitioning change to wanting to partition to limit the size of a particular partition (perhaps for storage space or for quick truncation, etc). At that point, you may decide to partition on a monthly or annual basis.

使用日期作为分区键进行分区显然会迫使您为日期字段创建索引.首先，看看如何进行，然后再按计划进行额外的分区工作.

Partitioning using the date as a partition key will obviously force you into creating an index for the date field. Start with that and see how it goes before you get into the extra efforts of partitioning on a scheduled basis.

这篇关于如何按天分区MySQL表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！