mysql中的key 分区为什么总是一半有数据，一半没有数据？

[华夏小卒]

CREATE TABLE `pis_supply_chain_a` (
  `ID` varchar(50) NOT NULL,
  `CMMDTY_CODE` varchar(18) NOT NULL,
  `CITY_TO` varchar(12) DEFAULT NULL,
  `LOGISTICS_RAD_MODE` varchar(3) DEFAULT NULL,
  `CITY_FROM` varchar(12) DEFAULT NULL,
  `VERSION_NO` varchar(18) DEFAULT NULL,
  `CREATE_TIME` datetime DEFAULT NULL,
  `UPDATE_TIME` datetime DEFAULT NULL,
  `DELETE_FLAG` varchar(4) DEFAULT 'N',
  PRIMARY KEY (`ID`,`CMMDTY_CODE`),
  UNIQUE KEY `supply_chain_a_index` (`CMMDTY_CODE`,`CITY_TO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='1\n3000W10'
PARTITION BY KEY (CMMDTY_CODE) PARTITIONS 10 


mysql> SELECT TABLE_NAME,PARTITION_NAME,TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME ='pis_supply_chain_a';
+--------------------+----------------+------------+
| TABLE_NAME         | PARTITION_NAME | TABLE_ROWS |
+--------------------+----------------+------------+
| pis_supply_chain_a | p0             |    3630867 |
| pis_supply_chain_a | p1             |          0 |
| pis_supply_chain_a | p2             |    4537399 |
| pis_supply_chain_a | p3             |          0 |
| pis_supply_chain_a | p4             |    5225392 |
| pis_supply_chain_a | p5             |          0 |
| pis_supply_chain_a | p6             |    5495859 |
| pis_supply_chain_a | p7             |          1 |
| pis_supply_chain_a | p8             |    4757965 |
| pis_supply_chain_a | p9             |          0 |
+--------------------+----------------+------------+
10 rows in set (0.35 sec)



mysql> select distinct CMMDTY_CODE from pis_supply_chain_a limit 20;
+--------------------+
| CMMDTY_CODE        |
+--------------------+
| 000000000100003931 |
| 000000000100004594 |
| 000000000100005662 |
| 000000000100019670 |
| 000000000100025155 |
| 000000000100028229 |
| 000000000100028764 |
| 000000000100030935 |
| 000000000100032337 |
| 000000000100032338 |
| 000000000100033242 |
| 000000000100034839 |
| 000000000100039735 |
| 000000000100039837 |
| 000000000100039848 |
| 000000000100040161 |
| 000000000100044946 |
| 000000000100044950 |
| 000000000100048556 |
| 000000000100050836 |
+--------------------+
20 rows in set (0.02 sec)

[ACMAIN_CHM]

引用

17.2.4. KEY Partitioning Partitioning by key is similar to partitioning by hash, except that where hash partitioning employs a user-defined expression, the hashing function for key partitioning is supplied by the MySQL server. MySQL Cluster uses MD5() for this purpose; for tables using other storage engines, the server employs its own internal hashing function which is based on the same algorithm as PASSWORD().

算法问题。

[华夏小卒]

一个同事根据password函数，分析并测出，key分区，只能指定分区数目为质数，才能保证每个分区都有数据。 我测了下，从11个分区，到17个分区。 只有11,13,17 ，这3个分区的数据是基本平均分布的。

[华夏小卒]

引用 1 楼 ACMAIN_CHM 的回复:

引用

17.2.4. KEY Partitioning Partitioning by key is similar to partitioning by hash, except that where hash partitioning employs a user-defined expression, the hashing function for key partitioning is supplied by the MySQL server. MySQL Cluster uses MD5() for this purpose; for tables using other storage engines, the server employs its own internal hashing function which is based on the same algorithm as PASSWORD().

算法问题。

嗯，查过资料，但是对这个password（）函数不太了解。 有没有办法解决呢。 或者说，这算是一个bug吗？

[new_up]

猜测只有在2的n次方减一长度是才会都有值