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

华夏小卒 2014-08-12 11:59:12
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)
...全文
1023 4 打赏 收藏 举报
写回复
4 条回复
切换为时间正序
请发表友善的回复…
发表回复
new_up 2015-12-31
  • 打赏
  • 举报
回复
猜测只有在2的n次方减一长度是才会都有值
华夏小卒 2014-08-13
  • 打赏
  • 举报
回复
引用 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吗?
华夏小卒 2014-08-13
  • 打赏
  • 举报
回复
一个同事根据password函数,分析并测出,key分区,只能指定分区数目为质数,才能保证每个分区都有数据。 我测了下,从11个分区,到17个分区。 只有11,13,17 ,这3个分区的数据是基本平均分布的。
ACMAIN_CHM 2014-08-12
  • 打赏
  • 举报
回复
引用
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().
算法问题。
相关推荐
发帖
MySQL

5.6w+

社区成员

MySQL相关内容讨论专区
社区管理员
  • MySQL
加入社区
帖子事件
创建了帖子
2014-08-12 11:59
社区公告
暂无公告