MySQL中按月存储的范围和散列分区

Question

对于我的表，我需要根据created 时间戳字段按Month进行分区。

我正在评估以下两种方法：

• RANGE

按范围更改表my_table分区(月份(创建))(分区p1值小于(2)，分区p2值小于(3)，分区p3值小于(4)，分区p4值小于(5)，分区p5值小于(6)，分区p6值小于(7)，分区p7值小于(8)，分区p8值小于(9)，分区p9值小于(10)，分区p10值小于(11)，分区p11值小于(12)，分区p12值小于(13 )；

• HASH

按散列更改表my_table分区((年份(创建)* 100) +月份(创建)分区13;

用例：

我的用例是，我想按月归档，因为这个月已经超过了一年。例如，如果当前月份是2020年7月-2020年，那么对应于7月-2019年的分区将被归档，第二个用例也是分区剪枝以提高性能，因为大多数查询都包括这个时间戳列。

为什么在散列一个中有13个分区？

如前所述，我将存档从本月起的第13个月。

对于这个用例，哪种方法更适合？据我所知，当我按范围定义它时，我可以直接控制哪些数据进入哪个分区，在哈希的情况下，它将由MySQL哈希函数(mod)定义，这将使我们很难识别“过去一年”分区并将其具体归档。

或者对于这个用例有什么完全不同的方法吗？

Answer 1

PARTITION BY HASH没用。句号。

如果您想清除“旧”数据，PARTITION BY RANGE可能很有用。详细信息：http://mysql.rjweb.org/doc.php/partitionmaint

明年一月你打算做什么？

让我看看你的SELECTs和SHOW CREATE TABLE。我将帮助您为非分区版本优化INDEXes。它将运行得比您认为的架构更快或更快。

More

当你有一个“范围”时，

• BY HASH是无用的。优化器将始终选择所有分区，从而降低查询速度。(此缺陷适用于大多数分区方法。)
• (如果始终使用WHERE month=constant )，最好在索引的早期使用列month。MONTH(date_col) = constant是另一回事。(我没有想清楚所有的含义。(让我们看看您的查询。)
• 作为一般规则，您可以在非分区表上构建一个索引，该索引将提供与分区剪枝相同的功能。(链接仅列出规则的4个例外情况。我花了十年时间寻找更多用例。)相关:当切换到分区/从分区切换时，包括PRIMARY KEY在内的所有索引都应该是我用例的PRIMARY KEY，即使用“可传输表空间”来归档整个分区。您可能可以在BY HASH中使用它；很清楚如何使用BY HASH，我博客的主要重点是解释DROPping (或‘传输’)--13个月的分区中最古老的一个，REORGANIZE以获得一个新的“月份”(或其他时间范围)。