2015年3月25日 星期三

MySQL Partition ( VER 5.6.6 up)

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中


沒有留言:

張貼留言