mysql - MySQL 中按月存储的 RANGE 与 HASH 分区

问题描述

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

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

范围

  ALTER TABLE my_table
      PARTITION BY RANGE ( MONTH(created) ) (
      PARTITION p1 VALUES LESS THAN (2),
      PARTITION p2 VALUES LESS THAN (3),
      PARTITION p3 VALUES LESS THAN (4),
      PARTITION p4 VALUES LESS THAN (5),
      PARTITION p5 VALUES LESS THAN (6),
      PARTITION p6 VALUES LESS THAN (7),
      PARTITION p7 VALUES LESS THAN (8),
      PARTITION p8 VALUES LESS THAN (9),
      PARTITION p9 VALUES LESS THAN (10),
      PARTITION p10 VALUES LESS THAN (11),
      PARTITION p11 VALUES LESS THAN (12),
      PARTITION p12 VALUES LESS THAN (13)
  );

哈希

  ALTER TABLE my_table 
      PARTITION BY HASH((YEAR(created) * 100) + MONTH(created)) 
      PARTITIONS 13;

用例：

我的用例是我想按月存档，对于已经超过 1 年的月份。例如，如果当前月份是 2020 年 7 月，那么对应于 2019 年 7 月的分区将被存档，并且次要用例是分区修剪以提高性能，因为大多数查询都包含此时间戳列。

为什么 HASH 1 中有 13 个分区？

如上所述，我将从当前月份开始归档第 13 个月。

对于这个用例，哪种方法更适合？据我了解，当我通过 RANGE 定义它时，我可以直接控制哪些数据进入哪个分区，并且在 HASH 的情况下，它将由 MySQL HASH 函数（mod）定义，这将使事情变得很难识别“超过一年”的分区并专门对其进行归档。

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

标签： mysqlpartitioningmysql-8.0

解决方案

PARTITION BY HASH没用。时期。
PARTITION BY RANGE如果您想清除“旧”数据，可能会很有用。详情：http: //mysql.rjweb.org/doc.php/partitionmaint

明年一月你会做什么？

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

更多的

BY HASH当你有一个“范围”时是没用的。优化器将始终选择所有分区，从而减慢查询速度。（此缺陷适用于大多数分区方法。）
如果您总是使用WHERE month=constant，则最好将列month放在索引的早期。 MONTH(date_col) = constant是另一回事。（我还没有考虑到所有的含义。让我们看看你的问题。）
作为一般规则，您可以在非分区表上构建索引，该索引将提供与分区修剪等效的功能。（该链接仅列出了该规则的 4 个例外。我花了十年时间寻找更多用例。） 相关性： 在切换到/从分区时PRIMARY KEY，应重新设计所有索引，包括 .
我的一个用例是使用“可传输表空间”来归档整个分区。您可能可以将其与BY HASH;一起使用很清楚如何使用BY RANGE.
我博客的主要重点是解释DROPping（或“传输”）13 个月的分区中最旧的一个，并REORGANIZE获得一个新的“月份”（或其他时间范围）。

mysql - MySQL 中按月存储的 RANGE 与 HASH 分区

问题描述

解决方案

推荐阅读