MySQL表按月分区 [英] MySQL table partition by month

问题描述

我有一个巨大的表，其中存储了许多跟踪的事件，例如用户的点击.

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.

我只发现了一些指南，这些指南显示了如何每月进行手动分区，有没有一种方法可以告诉MySQL按月进行分区，并且它将自动执行该操作?

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?

如果没有，考虑到按列划分的日期时间是什么，手动执行的命令是什么?

If not, what is the command to do it manually considering my partitioned by column is a datetime?

推荐答案

如手册中所述:这可以通过对月份输出进行哈希分区来轻松实现.

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;

请注意，这仅按月而不是按年进行分区，在此示例中也只有6个分区(即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.

并用于对现有表进行分区(手册: https://dev.mysql.com/doc/refman/5.7/zh-CN/alter-table-partition-operations.html ):

And for partitioning an existing table (manual: 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;

或来自特定分区:

SELECT * from ti PARTITION (HASH(MONTH(some_date)));

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