MySQL分区：分区中的数据不平衡

Question

我运行 MySQL 5.1.47，分区插件处于活动状态。

我创建下表：

CREATE TABLE IF NOT EXISTS `prova` ( 
`NE` varchar(8) NOT NULL, 
`ASSERT` longtext NOT NULL 
) ENGINE=MyISAM 
PARTITION BY KEY(NE) 
PARTITIONS 4; 

然后插入 4 行：

INSERT INTO prova values ('AAA','this assert is from AAA'); 
INSERT INTO prova values ('BBB','this assert is from BBB'); 
INSERT INTO prova values ('CCC','this assert is from CCC'); 
INSERT INTO prova values ('DDD','this assert is from DDD'); 

我希望在每个分区中找到 1 条记录，但是：

mysql> explain partitions select * from prova where NE='AAA'; 
+----+-------------+-------+------------+--------+---------------+------+---------+------+------+-------+ 
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | 
+----+-------------+-------+------------+--------+---------------+------+---------+------+------+-------+ 
| 1 | SIMPLE | prova | p2 | system | NULL | NULL | NULL | NULL | 1 | | 
+----+-------------+-------+------------+--------+---------------+------+---------+------+------+-------+ 
1 row in set (0.00 sec) 

mysql> explain partitions select * from prova where NE='BBB'; 
+---+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+ 
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | 
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+ 
| 1 | SIMPLE | prova | p1 | ALL | NULL | NULL | NULL | NULL | 4 | Using where | 
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+ 
1 row in set (0.00 sec) 

mysql> explain partitions select * from prova where NE='CCC'; 
+----+-------------+-------+------------+--------+---------------+------+---------+------+------+-------+ 
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | 
+----+-------------+-------+------------+--------+---------------+------+---------+------+------+-------+ 
| 1 | SIMPLE | prova | p0 | system | NULL | NULL | NULL | NULL | 1 | | 
+----+-------------+-------+------------+--------+---------------+------+---------+------+------+-------+ 
1 row in set (0.00 sec) 

mysql> explain partitions select * from prova where NE='DDD'; 
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+ 
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | 
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+ 
| 1 | SIMPLE | prova | p1 | ALL | NULL | NULL | NULL | NULL | 4 | Using where | 
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+ 
1 row in set (0.00 sec)

所以，我的问题是： 我做错了什么？为什么 4 个插入没有分成 4 个分区？ 为什么BBB和DDD要在同一个分区？

非常感谢您的帮助！ 埃文）

Answer 1

根据MySQL手册 http://dev.mysql.com/doc /refman/5.1/en/partitioning-key.html 按 KEY 分区与按 HASH 分区类似，只是散列函数现在由 MySQL 内部选择（而不是像 HASH 分区那样由用户选择）。

手册对此并没有明确说明，但该函数应该是 MD5()、PASSWORD() 或基于后者的函数。因此它不会根据 NE 的值来选择分区，而是根据它计算出的哈希值来选择分区。使用的哈希函数具有均匀分布，因此当您有很多行时，它们将在分区之间均匀分布。

所以如果你想选择分区的函数，你应该使用HASH分区。但你在期待什么？或者 NE 列的可能值是什么？