subpartiton by key doesn't evenly distribute data
Hi,
I made a partitioning table with subpartition by key.
As I know, Key and Hash partitioning distributes data evenly.
But the result was not even.
This is the schema of partitoning table;
-----------------------------------------------------------
CREATE TABLE `PT_SUBSR_MERGE_INFO` (
`SA_ID` varchar(20) NOT NULL,
`CONTS_ID` varchar(20) NOT NULL,
`BUY_DT` datetime DEFAULT NULL,
`END_DT` datetime DEFAULT NULL,
`WATCH_START_DT` datetime DEFAULT NULL,
`WATCH_END_DT` datetime DEFAULT NULL,
`INSRT_DT` datetime DEFAULT NULL,
`PARTITION_KEY` mediumint(8) unsigned NOT NULL,
PRIMARY KEY (`SA_ID`,`CONTS_ID`,`PARTITION_KEY`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE (PARTITION_KEY)
SUBPARTITION BY KEY (SA_ID)
SUBPARTITIONS 10
(PARTITION p1201 VALUES LESS THAN (201202) ENGINE = InnoDB,
PARTITION p1202 VALUES LESS THAN (201203) ENGINE = InnoDB,
PARTITION p1203 VALUES LESS THAN (201204) ENGINE = InnoDB,
PARTITION p1204 VALUES LESS THAN (201205) ENGINE = InnoDB,
PARTITION p1205 VALUES LESS THAN (201206) ENGINE = InnoDB,
PARTITION p1206 VALUES LESS THAN (201207) ENGINE = InnoDB,
PARTITION p1207 VALUES LESS THAN (201208) ENGINE = InnoDB,
PARTITION p1208 VALUES LESS THAN (201209) ENGINE = InnoDB,
PARTITION p1209 VALUES LESS THAN (201210) ENGINE = InnoDB,
PARTITION p1210 VALUES LESS THAN (201211) ENGINE = InnoDB,
PARTITION p1211 VALUES LESS THAN (201212) ENGINE = InnoDB,
PARTITION p1212 VALUES LESS THAN (201301) ENGINE = InnoDB,
PARTITION p1301 VALUES LESS THAN (201302) ENGINE = InnoDB,
PARTITION p1302 VALUES LESS THAN (201303) ENGINE = InnoDB,
PARTITION p1303 VALUES LESS THAN (201304) ENGINE = InnoDB,
PARTITION p1304 VALUES LESS THAN (201305) ENGINE = InnoDB,
PARTITION p1305 VALUES LESS THAN (201306) ENGINE = InnoDB,
PARTITION p1306 VALUES LESS THAN (201307) ENGINE = InnoDB,
PARTITION p1307 VALUES LESS THAN (201308) ENGINE = InnoDB,
PARTITION p1308 VALUES LESS THAN (201309) ENGINE = InnoDB,
PARTITION p1309 VALUES LESS THAN (201310) ENGINE = InnoDB,
PARTITION p1310 VALUES LESS THAN (201311) ENGINE = InnoDB,
PARTITION p1311 VALUES LESS THAN (201312) ENGINE = InnoDB,
PARTITION p1312 VALUES LESS THAN (201401) ENGINE = InnoDB,
PARTITION p1401 VALUES LESS THAN (201402) ENGINE = InnoDB,
PARTITION p1402 VALUES LESS THAN (201403) ENGINE = InnoDB,
PARTITION p1403 VALUES LESS THAN (201404) ENGINE = InnoDB,
PARTITION p1404 VALUES LESS THAN (201405) ENGINE = InnoDB,
PARTITION p1405 VALUES LESS THAN (201406) ENGINE = InnoDB,
PARTITION p1406 VALUES LESS THAN (201407) ENGINE = InnoDB,
PARTITION p1407 VALUES LESS THAN (201408) ENGINE = InnoDB,
PARTITION p1408 VALUES LESS THAN (201409) ENGINE = InnoDB,
PARTITION p1409 VALUES LESS THAN (201410) ENGINE = InnoDB,
PARTITION p1410 VALUES LESS THAN (201411) ENGINE = InnoDB,
PARTITION p1411 VALUES LESS THAN (201412) ENGINE = InnoDB,
PARTITION p1412 VALUES LESS THAN (201501) ENGINE = InnoDB,
PARTITION p1501 VALUES LESS THAN (201502) ENGINE = InnoDB,
PARTITION p1502 VALUES LESS THAN (201503) ENGINE = InnoDB,
PARTITION p1503 VALUES LESS THAN (201504) ENGINE = InnoDB,
PARTITION p1504 VALUES LESS THAN (201505) ENGINE = InnoDB,
PARTITION p1505 VALUES LESS THAN (201506) ENGINE = InnoDB,
PARTITION p1506 VALUES LESS THAN (201507) ENGINE = InnoDB,
PARTITION p1507 VALUES LESS THAN (201508) ENGINE = InnoDB,
PARTITION p1508 VALUES LESS THAN (201509) ENGINE = InnoDB,
PARTITION p1509 VALUES LESS THAN (201510) ENGINE = InnoDB,
PARTITION p1510 VALUES LESS THAN (201511) ENGINE = InnoDB,
PARTITION p1511 VALUES LESS THAN (201512) ENGINE = InnoDB,
PARTITION p1512 VALUES LESS THAN (201601) ENGINE = InnoDB);
--------------------------------------------------------------
This is the result;
---------------------------------------------------------------
mysql>select table_name,partition_name,subpartition_name,table_rows
from information_schema.partitions where table_name= 'PT_SUBSR_MERGE_INFO' and partition_name='p1403';
| PT_SUBSR_MERGE_INFO | p1403 | p1403sp0 | 169962 |
| PT_SUBSR_MERGE_INFO | p1403 | p1403sp1 | 2835 |
| PT_SUBSR_MERGE_INFO | p1403 | p1403sp2 | 162751 |
| PT_SUBSR_MERGE_INFO | p1403 | p1403sp3 | 2808 |
| PT_SUBSR_MERGE_INFO | p1403 | p1403sp4 | 168671 |
| PT_SUBSR_MERGE_INFO | p1403 | p1403sp5 | 2763 |
| PT_SUBSR_MERGE_INFO | p1403 | p1403sp6 | 175408 |
| PT_SUBSR_MERGE_INFO | p1403 | p1403sp7 | 2884 |
| PT_SUBSR_MERGE_INFO | p1403 | p1403sp8 | 156632 |
| PT_SUBSR_MERGE_INFO | p1403 | p1403sp9 | 2835 |
Data wasn't distirbuted evenly in the subpartitions.
mysql> select count(*) from PT_SUBSR_MERGE_INFO partition(p1403sp0);
+----------+
| count(*) |
+----------+
| 166446 |
+----------+
mysql> select count(*) from PT_SUBSR_MERGE_INFO partition(p1403sp1);
+----------+
| count(*) |
+----------+
| 2835 |
+----------+
mysql> select count(*) from PT_SUBSR_MERGE_INFO partition(p1403sp2);
+----------+
| count(*) |
+----------+
| 166684 |
+----------+
mysql> select count(*) from PT_SUBSR_MERGE_INFO partition(p1403sp3);
+----------+
| count(*) |
+----------+
| 2808 |
+----------+
mysql> select count(*) from PT_SUBSR_MERGE_INFO partition(p1403sp4);
+----------+
| count(*) |
+----------+
| 165855 |
+----------+
mysql> select count(*) from PT_SUBSR_MERGE_INFO partition(p1403sp5);
+----------+
| count(*) |
+----------+
| 2763 |
+----------+
mysql> select count(*) from PT_SUBSR_MERGE_INFO partition(p1403sp6);
+----------+
| count(*) |
+----------+
| 167031 |
+----------+
mysql> select count(*) from PT_SUBSR_MERGE_INFO partition(p1403sp7);
+----------+
| count(*) |
+----------+
| 2884 |
+----------+
mysql> select count(*) from PT_SUBSR_MERGE_INFO partition(p1403sp8);
+----------+
| count(*) |
+----------+
| 167161 |
+----------+
mysql> select count(*) from PT_SUBSR_MERGE_INFO partition(p1403sp9);
+----------+
| count(*) |
+----------+
| 2835 |
+----------+
MySQL Version: 5.6.23-log MySQL Community Server (GPL)
Is the result right? or wrong?
Is Partitioning by Key used primarily to ensure an even distiribution of data among a predetermined number of partitions ?
Please give me help.
Thanks