按月份分割MySQL表

Question

按月份分割MySQL表

mysqlpartition

21

我有一张巨大的表格，存储许多跟踪事件，例如用户点击。

这张表已经超过了数千万行，并且每天都在不断增长。当我尝试从一个较长时间范围内获取事件时，查询开始变慢。阅读了很多相关主题后，我明白分区表可能会提高性能。

我想做的是按月份对表进行分区。

我只找到了手动分区每个月的指南，是否有一种方式可以告诉MySQL按月份分区并自动完成？

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

- shaharmor

4个回答

4

CREATE TABLE `mytable` (
  `post_id` int DEFAULT NULL,
  `viewid` int DEFAULT NULL,
  `user_id` int DEFAULT NULL,
  `post_Date` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
PARTITION BY RANGE (extract(year_month from `post_Date`))
(PARTITION P0 VALUES LESS THAN (202012) ENGINE = InnoDB,
 PARTITION P1 VALUES LESS THAN (202104) ENGINE = InnoDB,
 PARTITION P2 VALUES LESS THAN (202108) ENGINE = InnoDB,
 PARTITION P3 VALUES LESS THAN (202112) ENGINE = InnoDB,
 PARTITION P4 VALUES LESS THAN MAXVALUE ENGINE = InnoDB)

- GuttakindaBhaskar

1

请将下面与编程有关的内容从英语翻译为中文。只返回已翻译的文本：添加上下文以防止负投票。结束审查。 - ZF007

优化器是否足够智能，可以处理extract(year_month...)？（在“PARTITION”语句中可以有效使用的表达式非常有限。） - Rick James

1

请注意通过哈希分区进行分区时的“惰性”效应：

正如文档所述：

您还应该记住，每次插入或更新行（或可能删除行）时都会评估此表达式；这意味着非常复杂的表达式可能会导致性能问题，特别是在执行影响许多行的操作（例如批量插入）时。

最有效的哈希函数是一个仅基于单个表列运行并且其值随着列值一致地增加或减少的函数，因为这允许对分区范围进行“修剪”。也就是说，表达式与其基于的列的值越接近，MySQL就可以更有效地使用该表达式进行哈希分区。

例如，假设date_col是一个类型为DATE的列，则表达式TO_DAYS(date_col)被称为随着date_col的值直接变化，因为对于date_col值的每一次更改，表达式的值都会以一致的方式发生变化。与TO_DAYS(date_col)相比，表达式YEAR(date_col)相对于date_col的变化不太直接，因为并非每个可能的date_col变化都会产生等效的YEAR(date_col)变化。

- Jonas Matos

但是按日期分区会创建太多的数据文件。从长远来看，这不应该成为一个问题吗？ - Feng Jiang

-2

哈希按月分区，使用6个分区意味着每年会有两个月落在同一个分区中。那有什么好处呢？

不必分区，对表进行索引。

假设这是您唯一使用的两个查询：

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

然后使用the_date开始PRIMARY KEY。

第一个查询只是读取整个表格；在分区和非分区之间没有变化。

第二个查询，假设您只想要一个月份，而不是映射到同一分区的所有月份，则需要进行以下操作：

SELECT * FROM ti  WHERE the_date >= '2019-03-01'
                    AND the_date  < '2019-03-01' + INTERVAL 1 MONTH;

如果您有其他疑问，请告诉我们。

(我没有找到任何性能证明，表明使用 PARTITION BY HASH 是必要的。)

- Rick James

嗯，我觉得它看起来不错，但我又懂什么呢？ - Strawberry

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Wolph · Accepted Answer

根据手册的说明：http://dev.mysql.com/doc/refman/5.6/en/partitioning-overview.html 通过对月份进行哈希分区，这是很容易实现的。

CREATE TABLE ti (id INT, amount DECIMAL(7,2), tr_date DATE)
    ENGINE=INNODB
    PARTITION BY HASH( MONTH(tr_date) )
    PARTITIONS 6;

请注意，这仅按月份进行分区，而不是按年份进行分区，并且在此示例中只有6个分区（因此为6个月）。

如果要对现有表进行分区（手动： 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;

查询可以从整个表中进行：

SELECT * from ti;

或从特定的分区：

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