Friday, December 19, 2014

一個MySQL分區和SUBPARTITION示例

Original post: http://anothermysqldba.blogspot.com/2014/12/a-mysql-partition-and-subpartition.html

因此,這是一個如何建立一個分區和MySQL的一個SUBPARTITION只是一個簡單的例子。 這裡的概念是,你必須在一個時間字段無數值表中的數據。 你可能有分佈在很多年(最有可能的,你做的)數據。 所以這個分區數據的一種方法是通過一年來排序,但隨後也即每年分區內按月份排序。 

以下是你可以用考慮一個例子。 

考慮到測試表。 你的表當然有更多的領域。 

CREATE TABLE `t1` ( 
`id` int(11) NOT NULL AUTO_INCREMENT, 
`date_time` datetime DEFAULT NOW(), 
PRIMARY KEY (`id`) 
) ENGINE=InnoDB; 


首先,我將填充測試表隨機值的DATE_TIME領域。 

delimiter // 
CREATE PROCEDURE populate_t1( IN rowsofdata INT ) 
BEGIN 

SET @A = 1; 
SET @B = 25 - @A; 

WHILE rowsofdata > 0 DO 
SELECT FLOOR( @A + (RAND() * @B )) INTO @randvalue; 
INSERT INTO t1 
SELECT NULL, NOW() - INTERVAL @randvalue MONTH; 
SET rowsofdata = rowsofdata - 1; 
END WHILE; 
END// 
delimiter ; 
call populate_t1(1000); 


檢查,看看我結束了什麼樣的價值觀為: 

> SELECT COUNT(*) FROM t1 WHERE date_time BETWEEN '2012-01-01 00:00:00' AND '2013-01-01 00:00:00'\G 
*************************** 1. row *************************** 
COUNT(*): 43 
1 row in set (0.00 sec) 

> SELECT COUNT(*) FROM t1 WHERE date_time BETWEEN '2013-01-01 00:00:00' AND '2014-01-01 00:00:00'\G 
*************************** 1. row *************************** 
COUNT(*): 529 
1 row in set (0.00 sec) 

> SELECT COUNT(*) FROM t1 WHERE date_time BETWEEN '2014-01-01 00:00:00' AND NOW() \G
*************************** 1. row *************************** 
COUNT(*): 428 
1 row in set (0.00 sec) 


現在,我可以改變表,所以我可以通過分區添加我的分區,然後測試值計數。 

ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY (`id`,`date_time`), LOCK=SHARED; 
ALTER TABLE t1 
PARTITION BY RANGE( YEAR(date_time) ) 
SUBPARTITION BY HASH(MONTH(date_time) ) ( 

PARTITION p2012 VALUES LESS THAN (2013) ( 
SUBPARTITION dec_2012, 
SUBPARTITION jan_2012, 
SUBPARTITION feb_2012, 
SUBPARTITION mar_2012, 
SUBPARTITION apr_2012, 
SUBPARTITION may_2012, 
SUBPARTITION jun_2012, 
SUBPARTITION jul_2012, 
SUBPARTITION aug_2012, 
SUBPARTITION sep_2012, 
SUBPARTITION oct_2012, 
SUBPARTITION nov_2012 
), 

PARTITION p2013 VALUES LESS THAN (2014) ( 
SUBPARTITION dec_2013, 
SUBPARTITION jan_2013, 
SUBPARTITION feb_2013, 
SUBPARTITION mar_2013, 
SUBPARTITION apr_2013, 
SUBPARTITION may_2013, 
SUBPARTITION jun_2013, 
SUBPARTITION jul_2013, 
SUBPARTITION aug_2013, 
SUBPARTITION sep_2013, 
SUBPARTITION oct_2013, 
SUBPARTITION nov_2013 

), 
PARTITION p2014 VALUES LESS THAN (2015) ( 
SUBPARTITION dec_2014, 
SUBPARTITION jan_2014, 
SUBPARTITION feb_2014, 
SUBPARTITION mar_2014, 
SUBPARTITION apr_2014, 
SUBPARTITION may_2014, 
SUBPARTITION jun_2014, 
SUBPARTITION jul_2014, 
SUBPARTITION aug_2014, 
SUBPARTITION sep_2014, 
SUBPARTITION oct_2014, 
SUBPARTITION nov_2014 
), 

PARTITION pmax VALUES LESS THAN MAXVALUE ( 
SUBPARTITION dec_max, 
SUBPARTITION jan_max, 
SUBPARTITION feb_max, 
SUBPARTITION mar_max, 
SUBPARTITION apr_max, 
SUBPARTITION may_max, 
SUBPARTITION jun_max, 
SUBPARTITION jul_max, 
SUBPARTITION aug_max, 
SUBPARTITION sep_max, 
SUBPARTITION oct_max, 
SUBPARTITION nov_max 
) 
); 


我型我秀創建表非常不同了。 

> show create table t1; 
CREATE TABLE `t1` ( 
`id` int(11) NOT NULL AUTO_INCREMENT, 
`date_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, 
PRIMARY KEY (`id`,`date_time`) 
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=latin1 
/*!50100 PARTITION BY RANGE ( YEAR(date_time)) 
SUBPARTITION BY HASH (MONTH(date_time)) 
(PARTITION p2012 VALUES LESS THAN (2013) 
(SUBPARTITION dec_2012 ENGINE = InnoDB, 
SUBPARTITION jan_2012 ENGINE = InnoDB, 
SUBPARTITION feb_2012 ENGINE = InnoDB, 
SUBPARTITION mar_2012 ENGINE = InnoDB, 
SUBPARTITION apr_2012 ENGINE = InnoDB, 
SUBPARTITION may_2012 ENGINE = InnoDB, 
SUBPARTITION jun_2012 ENGINE = InnoDB, 
SUBPARTITION jul_2012 ENGINE = InnoDB, 
SUBPARTITION aug_2012 ENGINE = InnoDB, 
SUBPARTITION sep_2012 ENGINE = InnoDB, 
SUBPARTITION oct_2012 ENGINE = InnoDB, 
SUBPARTITION nov_2012 ENGINE = InnoDB), 
PARTITION p2013 VALUES LESS THAN (2014) 
(SUBPARTITION dec_2013 ENGINE = InnoDB, 
SUBPARTITION jan_2013 ENGINE = InnoDB, 
SUBPARTITION feb_2013 ENGINE = InnoDB, 
SUBPARTITION mar_2013 ENGINE = InnoDB, 
SUBPARTITION apr_2013 ENGINE = InnoDB, 
SUBPARTITION may_2013 ENGINE = InnoDB, 
SUBPARTITION jun_2013 ENGINE = InnoDB, 
SUBPARTITION jul_2013 ENGINE = InnoDB, 
SUBPARTITION aug_2013 ENGINE = InnoDB, 
SUBPARTITION sep_2013 ENGINE = InnoDB, 
SUBPARTITION oct_2013 ENGINE = InnoDB, 
SUBPARTITION nov_2013 ENGINE = InnoDB), 
PARTITION p2014 VALUES LESS THAN (2015) 
(SUBPARTITION dec_2014 ENGINE = InnoDB, 
SUBPARTITION jan_2014 ENGINE = InnoDB, 
SUBPARTITION feb_2014 ENGINE = InnoDB, 
SUBPARTITION mar_2014 ENGINE = InnoDB, 
SUBPARTITION apr_2014 ENGINE = InnoDB, 
SUBPARTITION may_2014 ENGINE = InnoDB, 
SUBPARTITION jun_2014 ENGINE = InnoDB, 
SUBPARTITION jul_2014 ENGINE = InnoDB, 
SUBPARTITION aug_2014 ENGINE = InnoDB, 
SUBPARTITION sep_2014 ENGINE = InnoDB, 
SUBPARTITION oct_2014 ENGINE = InnoDB, 
SUBPARTITION nov_2014 ENGINE = InnoDB), 
PARTITION pmax VALUES LESS THAN MAXVALUE 
(SUBPARTITION dec_max ENGINE = InnoDB, 
SUBPARTITION jan_max ENGINE = InnoDB, 
SUBPARTITION feb_max ENGINE = InnoDB, 
SUBPARTITION mar_max ENGINE = InnoDB, 
SUBPARTITION apr_max ENGINE = InnoDB, 
SUBPARTITION may_max ENGINE = InnoDB, 
SUBPARTITION jun_max ENGINE = InnoDB, 
SUBPARTITION jul_max ENGINE = InnoDB, 
SUBPARTITION aug_max ENGINE = InnoDB, 
SUBPARTITION sep_max ENGINE = InnoDB, 
SUBPARTITION oct_max ENGINE = InnoDB, 
SUBPARTITION nov_max ENGINE = InnoDB)) 


所以,我們還能指望我們的價值預期? 

> SELECT count(*) FROM t1 PARTITION (p2012) \G 
*************************** 1. row *************************** 
count(*): 43 
> SELECT count(*) FROM t1 PARTITION (p2013) \G 
*************************** 1. row *************************** 
count(*): 529 
> SELECT count(*) FROM t1 PARTITION (p2014) \G 
*************************** 1. row *************************** 
count(*): 428 


到目前為止好,所有的價值觀匹配了我們所收到的數量。 所以我們也可以算或每子分區中進行選擇。 


> SELECT * FROM t1 PARTITION (dec_2012) limit 5; 
+-----+---------------------+ 
| id | date_time | 
+-----+---------------------+ 
| 59 | 2012-12-19 00:59:57 | 
| 68 | 2012-12-19 00:59:58 | 
| 93 | 2012-12-19 00:59:59 | 
| 105 | 2012-12-19 00:59:59 | 
| 111 | 2012-12-19 00:59:59 | 
+-----+---------------------+ 

> SELECT * FROM t1 PARTITION (jan_2013) limit 5; 
+-----+---------------------+ 
| id | date_time | 
+-----+---------------------+ 
| 6 | 2013-01-19 00:59:55 | 
| 29 | 2013-01-19 00:59:56 | 
| 55 | 2013-01-19 00:59:57 | 
| 79 | 2013-01-19 00:59:58 | 
| 100 | 2013-01-19 00:59:59 | 
+-----+---------------------+ 

> SELECT * FROM t1 PARTITION (jan_2014) limit 5; 
+-----+---------------------+ 
| id | date_time | 
+-----+---------------------+ 
| 16 | 2014-01-19 00:59:55 | 
| 190 | 2014-01-19 01:00:04 | 
| 191 | 2014-01-19 01:00:04 | 
| 229 | 2014-01-19 01:00:05 | 
| 234 | 2014-01-19 01:00:06 | 
+-----+---------------------+ 

> SELECT * FROM t1 PARTITION (jun_2014) limit 5; 
+-----+---------------------+ 
| id | date_time | 
+-----+---------------------+ 
| 13 | 2014-06-19 00:59:55 | 
| 189 | 2014-06-19 01:00:04 | 
| 221 | 2014-06-19 01:00:05 | 
| 222 | 2014-06-19 01:00:05 | 
| 238 | 2014-06-19 01:00:06 | 
+-----+---------------------+ 

> SELECT * FROM t1 PARTITION (dec_2013) limit 5; 
+-----+---------------------+ 
| id | date_time | 
+-----+---------------------+ 
| 50 | 2013-12-19 00:59:57 | 
| 74 | 2013-12-19 00:59:58 | 
| 98 | 2013-12-19 00:59:59 | 
| 107 | 2013-12-19 00:59:59 | 
| 167 | 2013-12-19 01:00:02 | 
+-----+---------------------+ 


這是偉大的,方便的,但是,當2015年或2016年左右出現什麼情況? 所有這些數據將在PMAX分區。 那麼,我們如何P2014和Pmax的在增加一個新的分區? 

如果您在PMAX沒有數據,你可以刪除它並添加一個新的分區到年底。 但它也很容易重新組織的分區。 這將需要的PMAX分區,改變成我們新的分區。 


ALTER TABLE t1 REORGANIZE PARTITION pmax INTO ( 
PARTITION p2015 VALUES LESS THAN (2016) ( 
SUBPARTITION dec_2015, 
SUBPARTITION jan_2015, 
SUBPARTITION feb_2015, 
SUBPARTITION mar_2015, 
SUBPARTITION apr_2015, 
SUBPARTITION may_2015, 
SUBPARTITION jun_2015, 
SUBPARTITION jul_2015, 
SUBPARTITION aug_2015, 
SUBPARTITION sep_2015, 
SUBPARTITION oct_2015, 
SUBPARTITION nov_2015 
), 
PARTITION pmax VALUES LESS THAN MAXVALUE ( 
SUBPARTITION dec_max, 
SUBPARTITION jan_max, 
SUBPARTITION feb_max, 
SUBPARTITION mar_max, 
SUBPARTITION apr_max, 
SUBPARTITION may_max, 
SUBPARTITION jun_max, 
SUBPARTITION jul_max, 
SUBPARTITION aug_max, 
SUBPARTITION sep_max, 
SUBPARTITION oct_max, 
SUBPARTITION nov_max 
) 
); 


希望這可以幫助,好運。