別のMySQL DBA: 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 
) 
);

これは、最高の幸運をお役に立てば幸いです。

別のMySQL DBA

Pages

2014年12月19日金曜日

MySQLのパーティションとSUBPARTITION例