MySQL 如何按日期时间列对表进行分区？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6093585/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
how to partition a table by datetime column?
提问by tinychen
I want to partition a mysql table by datetime column. One day a partition.The create table scripts is like this:
我想按日期时间列对 mysql 表进行分区。一天一个分区。创建表的脚本是这样的：
CREATE TABLE raw_log_2011_4 (
id bigint(20) NOT NULL AUTO_INCREMENT,
logid char(16) NOT NULL,
tid char(16) NOT NULL,
reporterip char(46) DEFAULT NULL,
ftime datetime DEFAULT NULL,
KEY id (id)
) ENGINE=InnoDB AUTO_INCREMENT=286802795 DEFAULT CHARSET=utf8
PARTITION BY hash (day(ftime)) partitions 31;
But when I select data of some day.It could not locate the partition.The select statement is like this:
但是当我选择某天的数据时，它找不到分区。选择语句是这样的：
explain partitions select * from raw_log_2011_4 where day(ftime) = 30;
when i use another statement,it could locate the partition,but I coluld not select data of some day.
当我使用另一个语句时，它可以定位分区，但我无法选择某一天的数据。
explain partitions select * from raw_log_2011_4 where ftime = '2011-03-30';
Is there anyone tell me How I could select data of some day and make use of partition.Thanks!
有没有人告诉我如何选择某一天的数据并使用分区。谢谢！
回答by Steyx
Partitions by HASH is a very bad idea with datetime columns, because it cannot use partition pruning. From the MySQL docs:
Partitions by HASH 对于 datetime 列来说是一个非常糟糕的主意，因为它不能使用partition pruning。来自 MySQL 文档：
Pruning can be used only on integer columns of tables partitioned by HASH or KEY. For example, this query on table t4 cannot use pruning because dob is a DATE column:
修剪只能用于由 HASH 或 KEY 分区的表的整数列。例如，表 t4 上的这个查询不能使用修剪，因为 dob 是一个 DATE 列：
SELECT * FROM t4 WHERE dob >= '2001-04-14' AND dob <= '2005-10-15';
However, if the table stores year values in an INT column, then a query having WHERE year_col >= 2001 AND year_col <= 2005 can be pruned.
但是，如果表将年份值存储在 INT 列中，则可以修剪具有 WHERE year_col >= 2001 AND year_col <= 2005 的查询。
So you can store the value of TO_DAYS(DATE()) in an extra INTEGER column to use pruning.
因此，您可以将 TO_DAYS(DATE()) 的值存储在额外的 INTEGER 列中以使用修剪。
Another option is to use RANGE partitioning:
另一种选择是使用 RANGE 分区：
CREATE TABLE raw_log_2011_4 (
id bigint(20) NOT NULL AUTO_INCREMENT,
logid char(16) NOT NULL,
tid char(16) NOT NULL,
reporterip char(46) DEFAULT NULL,
ftime datetime DEFAULT NULL,
KEY id (id)
) ENGINE=InnoDB AUTO_INCREMENT=286802795 DEFAULT CHARSET=utf8
PARTITION BY RANGE( TO_DAYS(ftime) ) (
PARTITION p20110401 VALUES LESS THAN (TO_DAYS('2011-04-02')),
PARTITION p20110402 VALUES LESS THAN (TO_DAYS('2011-04-03')),
PARTITION p20110403 VALUES LESS THAN (TO_DAYS('2011-04-04')),
PARTITION p20110404 VALUES LESS THAN (TO_DAYS('2011-04-05')),
...
PARTITION p20110426 VALUES LESS THAN (TO_DAYS('2011-04-27')),
PARTITION p20110427 VALUES LESS THAN (TO_DAYS('2011-04-28')),
PARTITION p20110428 VALUES LESS THAN (TO_DAYS('2011-04-29')),
PARTITION p20110429 VALUES LESS THAN (TO_DAYS('2011-04-30')),
PARTITION future VALUES LESS THAN MAXVALUE
);
Now the following query will only use partition p20110403:
现在以下查询将只使用分区 p20110403：
SELECT * FROM raw_log_2011_4 WHERE ftime = '2011-04-03';
回答by Vineet1982
Hi You are doing the wrong partition in definition of the table the table definition would like this:
嗨，您在表的定义中执行了错误的分区，表定义如下所示：
CREATE TABLE raw_log_2011_4 (
id bigint(20) NOT NULL AUTO_INCREMENT,
logid char(16) NOT NULL,
tid char(16) NOT NULL,
reporterip char(46) DEFAULT NULL,
ftime datetime DEFAULT NULL,
KEY id (id)
) ENGINE=InnoDB AUTO_INCREMENT=286802795 DEFAULT CHARSET=utf8
PARTITION BY hash (TO_DAYS(ftime)) partitions 31;
And your select command would be:
您的选择命令将是：
explain partitions
select * from raw_log_2011_4 where TO_DAYS(ftime) = '2011-03-30';
The above command would select all the date required, as if you use the TO_DAYS command as
上面的命令会选择所有需要的日期，就像你使用 TO_DAYS 命令一样
mysql> SELECT TO_DAYS(950501);
-> 728779
mysql> SELECT TO_DAYS('2007-10-07');
-> 733321
Why to use the TO_DAYS AS The MySQL optimizer will recognize two date-based functions for partition pruning purposes: 1.TO_DAYS() 2.YEAR()
为什么要使用 TO_DAYS AS MySQL 优化器将识别两个基于日期的函数用于分区修剪目的： 1.TO_DAYS() 2.YEAR()
and this would solve your problem..
这将解决您的问题..
回答by John Rocha
I just recently read a MySQL blog post relating to this, at http://dev.mysql.com/tech-resources/articles/mysql_55_partitioning.html.
我最近在http://dev.mysql.com/tech-resources/articles/mysql_55_partitioning.html 上阅读了一篇与此相关的 MySQL 博客文章。
Versions earlier than 5.1 required special gymnastics in order to do partitioning based on dates. The link above discusses it and shows examples.
5.1 之前的版本需要特殊的体操才能根据日期进行分区。上面的链接讨论了它并显示了示例。
Versions 5.5 and later allowed you to do direct partitioning using non-numeric values such as dates and strings.
5.5 及更高版本允许您使用非数字值（如日期和字符串）进行直接分区。
回答by Rick James
Don't use CHAR, use VARCHAR. That will save a lot of space, hence decrease I/O, hence speed up queries.
不要用CHAR，用VARCHAR。这将节省大量空间，从而减少 I/O，从而加快查询速度。
reporterip: (46) is unnecessarily big for an IP address, even IPv6. See My blogfor further discussion, including how to shrink it to 16 bytes.
reporterip: (46) 对于 IP 地址来说是不必要的大，即使是 IPv6。有关进一步讨论，请参阅我的博客，包括如何将其缩小到 16 字节。
PARTITION BY RANGE(TO_DAYS(...))as @Steyx suggested, but don't have more than about 50 partitions. The more partitions you have, the slower queries get, in spite of the "pruning". HASHpartitioning is essentially useless.
PARTITION BY RANGE(TO_DAYS(...))正如@Steyx 建议的那样，但不要有超过 50 个分区。尽管进行了“修剪”，但您拥有的分区越多，查询速度就越慢。 HASH分区基本上没用。
More discussion of partitioning, especially the type you are looking at. That includes code for a sliding set of partitions over time.
更多关于分区的讨论，尤其是您正在查看的类型。这包括随时间推移的一组滑动分区的代码。
