Thursday, December 18, 2014

Uma partição MySQL e subpartição Exemplo

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

Portanto, este é apenas um exemplo simples de como configurar uma partição e um subpartição em MySQL. O conceito aqui é que você tem dados em uma tabela com vários valores em um campo de data e hora. Você pode ter dados que estão espalhados por vários anos (provavelmente você). Portanto, uma forma de particionar esses dados é classificá-lo por ano, mas, em seguida, também classificá-lo por mês nessa partição anual. 

Abaixo está um exemplo que você pode usar para apreciação. 

Considere a tabela de teste. Sua mesa com ter muitos mais campos de curso. 

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


Primeiro vou preencher a tabela de teste com valores aleatórios para o campo 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); 


Verifique para ver que tipo de valores que eu acabei com: 

> 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) 


Agora eu posso alterar a tabela para que eu possa adicionar as minhas partições e depois testar a contagem de valores através da partição. 

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 
) 
); 


O meu espectáculo criar a tabela é muito diferente agora. 

> 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)) 


Então, podemos ainda contar com o nosso valor como esperado? 

> 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 


Até aí tudo bem, todos os valores combinados até a contagem que tínhamos antes. Por isso, também pode contar ou selecione per da subpartição. 


> 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 | 
+-----+---------------------+ 


Isso é ótimo e útil, mas o que acontece quando o ano de 2015 ou 2016 vem por aí? Todos os dados de que seria na partição pmax. Então, como vamos adicionar uma nova partição entre P2014 e Pmax? 

Se você não tinha dados em pmax você poderia largá-lo e adicionar uma nova partição para o final. Mas é tão fácil de reorganizar a partição. Isso levará a partição pmax e alterá-lo em nossas novas partições. 


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 
) 
); 


Espero que isso ajude, boa sorte.