mysql - 按范围或按列表分区 - Mysql？

Question

在我的情况下，我可以为我的新网站按范围或按列表进行分区，下面是我可以选择的两者的示例，

partition BY LIST(`category`)( 
PARTITION p0 VALUES IN(0), 
PARTITION p1 VALUES IN(1), 
PARTITION p2 VALUES IN(2), 
PARTITION p3 VALUES IN(3),
PARTITION p4 VALUES IN(4),
PARTITION p5 VALUES IN(5),
PARTITION p6 VALUES IN(6)
);

或者我可以，

PARTITION BY RANGE (`category`)(
PARTITION p0 VALUES LESS THAN (1),
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 BY HASH(`category`)
partitions 7;

是否有更好的选择，或者真的没有关系吗？

作为最后一个问题 - 我有 7 个不同的类别。如果我使用超过 7 个分区，选择最后一个选项会怎样？这样做是不是很愚蠢，或者它会提高性能。

感谢您花时间阅读我的问题!

Answer 1

MySQL 中有 4 种可用的分区策略:RANGE、LIST、KEY 和 HASH。

如果您需要按分区列的特定值进行分区，RANGE 和 LIST 就是为此目的而设计的。如果您不需要通过列的特定值为文件分配行，那么 KEY 和 HASH 更适合您。

在您的情况下，所有三种方法(LIST、RANGE 和 HASH)的工作方式完全相同，因为您有 7 个分区，并且 `category` = 0 的行将存储在 p0 中，`category` = 1 存储在 p1 中等等。

在 HASH 分区中，为了确定哪个文件将用于存储特定行，MySQL 使用模 (mod) 操作:

partition_number = hash(partitioning_column) mod number_of_partitions

在您的 HASH 情况下，分区号将是:

partition_number = category mod 7(您没有使用任何哈希函数，因此默认使用identity)。

创建比值更多(或更少)的分区可以具有分区列不一定无用，但如果您使用身份作为哈希函数，那么创建更多分区是无用的。

假设您只有 7 个类别，但定义了 20 个分区:

PARTITION BY HASH(`category`)
PARTITIONS 20;

由于 0 mod 7 = 0、1 mod 7 = 1、2 mod 7 = 2、3 mod 7 = 3、4 mod 7 = 4、5 mod 7 = 5 和 6 mod 7 = 6，其余分区(p7-p19) 永远不会被使用。它不会提高或降低性能，只是没用。

现在假设您仍然有 7 个类别，但只有 4 个分区:

PARTITION BY HASH(`category`)
PARTITIONS 4;

0 mod 4 = 0, 1 mod 4 = 1, 2 mod 4 = 2, 3 mod 4 = 3, 4 mod 4 = 0, 5 mod 4 = 1, 6 mod 4 = 2。

发生了什么事？文件 p0 将包含“category”= 0 和“category”= 4 的行，p1 将包含“category”= 1 和“category”= 5 的行，p2 将包含“category”= 2 和“category”的行` = 6 并且 p3 将仅包含 `category` = 3 的行。

这会提高还是降低性能？这取决于您在此表上运行的 SELECT 查询。如果您运行这样的查询:

SELECT * FROM `table_name` WHERE `category` = 0 or `category` = 4;

非常完美，因为只会访问一个分区。但是另一个例子是相反的:

SELECT * 
FROM `table_name` 
WHERE `category` = 0 OR `category` = 1 OR `category` = 2 OR`category` = 3;

需要访问所有分区以检索数据。

如果您键入以下内容，MySQL 会为您提供此信息:

EXPLAIN PARTITIONS SELECT_QUERY;
example: EXPLAIN PARTITIONS SELECT * FROM `table_name` WHERE `category` = 0 or `category` = 4;

关于使用哪种替代方案，因为 LIST 和 RANGE 分区旨在指定一个列表或一系列值，并且每个分区只有一个值，在这种情况下我会选择 HASH 分区 .