1. Origin Table
CREATE TABLE `TEST` (
`NO` int(11) NOT NULL AUTO_INCREMENT,
`TIME` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`ID` varchar(32) NOT NULL,
`EVENTNO` int(11) NOT NULL DEFAULT '0',
`VALUENO` int(11) NOT NULL DEFAULT '0',
`VALUE` bigint(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`NO`),
KEY `ID` (`ID`),
KEY `EVENTNO` (`EVENTNO`),
KEY `VALUENO` (`VALUENO`)
) ENGINE=MyISAM AUTO_INCREMENT=0
ALTER TABLE TEST DROP PRIMARY KEY, ADD UNIQUE KEY `ID_EVENTNO_VALUENO` (`ID`,`EVENTNO`,`VALUENO`), ADD UNIQUE KEY `NO_EVENTNO` (`NO`,`EVENTNO`), DROP KEY `VALUENO`;
ALTER TABLE TEST PARTITION BY HASH(`EVENTNO`) PARTITIONS 10;
PS : We could also DROP KEY `ID` and `EVENTNO` ...
Use EVENTNO to be Hash Key into 10 partition
2. Explain
EXPLAIN PARTITIONS SELECT VALUE FROM TEST WHERE ID = 'AAAA' AND EVENTNO = 200015 AND VALUENO = 10008;
+----+-------------+-----------------+------------+-------+-------------------------------+--------------------+---------+-------------------+------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------------+------------+-------+-------------------------------+--------------------+---------+-------------------+------+-------+
| 1 | SIMPLE | TEST | p5 | const | ID_EVENTNO_VALUENO,ID,EVENTNO | ID_EVENTNO_VALUENO | 74 | const,const,const | 1 | NULL |
+----+-------------+-----------------+------------+-------+-------------------------------+--------------------+---------+-------------------+------+-------+
partitions = p5 ==> we use EVENTNO to partition
key ID_EVENTNO_VALUENO
rows = 1 , only 1 row, good index
3. Partition Rule
A: HASH (N) , N must be an integer
B: All columns used in the partitioning expression for a partitioned table must be part of every unique key that the table may have.
(https://dev.mysql.com/doc/refman/5.6/en/partitioning-limitations-partitioning-keys-unique-keys.html)
也就是說 拿來當 Partition 的Key, 必須存在在所有的 Primay / Unique Key中
