MySQL - DATETIME列上的HASH PARTITION与RANGE PARTITION之间的区别？

Question

有人能告诉我对于DATETIME列来说，HASH PARTITION和RANGE PARTITION的优缺点吗？ 假设我们有一个POS表格，含有2000万条记录，并希望根据交易日期的年份创建分区，比如

PARTITION BY HASH(YEAR(TRANSACTION_DATE)) PARTITIONS 4;

或者

PARTITION BY RANGE(YEAR(TRANSACTION_DATE)) (
    PARTITION p0 VALUES LESS THAN (2010),
    PARTITION p1 VALUES LESS THAN (2012),
    PARTITION p2 VALUES LESS THAN (2013),
    PARTITION p4 VALUES LESS THAN MAXVALUE
);

为了提高查询“TRANSACTION_DATE BETWEEN '2013-03-01' AND '2013-09-29'”的性能，应该采取哪种方法？哪种方法更好？为什么？

- Murali Mopuru

---

我认为你应该在http://dba.stackexchange.com上提出这个问题。 - Jorge Campos

Answer 1

有一些显著的差异。如果您有一个涉及年份范围的where子句，例如：

where year(transaction_date) between 2009 and 2011

如果使用哈希分区，我认为它不会将其识别为只命中一个、两个或三个分区。范围分区应该能够识别此情况，从而减少这种查询的I/O。

更重要的区别在于数据管理。使用范围分区后，一旦创建了一个分区——并且年份已过去——假定该分区将不再被触及。这意味着你只需要备份一个分区，即当前分区。明年，你只需要备份一个分区。

如果想要将数据下线，也会出现类似的情况。相对于逐行删除，删除包含最旧年份数据的分区要容易得多。

当分区数仅为四个时，这些考虑可能没有太大的差异。关键是范围分区将每一行分配到已知分区。哈希分区将每一行分配到一个分区，但你不知道确切的分区。

编辑：

减少分区读取的特定优化称为“分区剪枝”。MySQL在这里很好地记录了这一点。特别是：

对于使用哈希或键分区的表，如果WHERE子句针对分区表达式中使用的列使用简单的=关系，则也可以进行分区修剪。

似乎不等式（甚至是in）的分区修剪需要范围分区。

- Gordon Linoff

---

在具有涉及日期范围的where子句的查询中，YEAR分区是否提高了性能？还是应该基于日期范围创建分区？ - Murali Mopuru

@Murali . . . 我认为你需要使用范围分区来处理where子句中的多个日期或不等式。 - Gordon Linoff