MySQL MySQL表按月分区
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19355024/
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
MySQL table partition by month
提问by shaharmor
I have a huge table that stores many tracked events, such as a user click.
我有一个巨大的表格，其中存储了许多跟踪的事件，例如用户点击。
The table is already in the 10's of millions, and its growing larger everyday. The queries are starting to get slower when i try to fetch events from a large timeframe, and after reading quite a bit on the subject i understand that partitioning the table may boost the performance.
这张桌子已经有几百万张了，而且每天都在变大。当我尝试从大的时间范围内获取事件时，查询开始变慢，并且在阅读有关该主题的大量内容后，我明白对表进行分区可能会提高性能。
What i want to do is partition the table on a per month basis.
我想要做的是每月对表进行分区。
I have only found guides that show how to partition manually each month, is there a way to just tell MySQL to partition by month and it will do that automatically?
我只找到了显示如何每月手动分区的指南，有没有办法告诉 MySQL 按月分区，它会自动执行？
If not, what is the command to do it manually considering my partitioned by column is a datetime?
如果不是，考虑到我的按列分区是日期时间，手动执行此操作的命令是什么？
回答by Wolph
As explained by the manual: http://dev.mysql.com/doc/refman/5.6/en/partitioning-overview.html
如手册所述：http: //dev.mysql.com/doc/refman/5.6/en/partitioning-overview.html
This is easily possible by hash partitioning of the month output.
通过对月份输出进行散列分区，这很容易实现。
CREATE TABLE ti (id INT, amount DECIMAL(7,2), tr_date DATE)
ENGINE=INNODB
PARTITION BY HASH( MONTH(tr_date) )
PARTITIONS 6;
Do note that this only partitions by month and not by year, also there are only 6 partitions (so 6 monhts) in this example.
请注意，这仅按月而不是按年分区，在此示例中也只有 6 个分区（所以 6 个 monhts）。
And for partitioning an existing table (manual: https://dev.mysql.com/doc/refman/5.7/en/alter-table-partition-operations.html):
以及对现有表进行分区（手册：https: //dev.mysql.com/doc/refman/5.7/en/alter-table-partition-operations.html）：
ALTER TABLE ti
PARTITION BY HASH( MONTH(tr_date) )
PARTITIONS 6;
Querying can be done both from the entire table:
可以从整个表中进行查询：
SELECT * from ti;
Or from specific partitions:
或从特定分区：
SELECT * from ti PARTITION (HASH(MONTH(some_date)));
回答by Rick James
HASHingby month with 6 partitions means that two months a year will land in the same partition. What good is that?
HASHing按月有 6 个分区意味着一年中有两个月将落在同一个分区中。那有什么好处？
Don't bother partitioning, index the table.
不要打扰分区，索引表。
Assuming these are the only two queries you use:
假设这些是您使用的仅有的两个查询：
SELECT * from ti;
SELECT * from ti PARTITION (HASH(MONTH(some_date)));
then start the PRIMARY KEYwith the_date.
然后PRIMARY KEY以the_date.
The first query simply reads the entire table; no change between partitioned and not.
第一个查询只是读取整个表；分区和不分区之间没有变化。
The second query, assuming you want a single month, not all the months that map into the same partition, would need to be
第二个查询，假设您想要一个月，而不是映射到同一分区的所有月份，则需要
SELECT * FROM ti WHERE the_date >= '2019-03-01'
AND the_date < '2019-03-01' + INTERVAL 1 MONTH;
If you have other queries, let's see them.
如果您有其他疑问，让我们看看它们。
(I have not found any performance justification for ever using PARTITION BY HASH.)
（我还没有发现任何使用PARTITION BY HASH. 的性能理由。）
回答by giuseppe
Use TokuDb which has an access time independent of the table size.
使用具有独立于表大小的访问时间的 TokuDb。
