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.
Primeiro vou preencher a tabela de teste com valores aleatórios para o campo date_time.
Verifique para ver que tipo de valores que eu acabei com:
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.
O meu espectáculo criar a tabela é muito diferente agora.
Então, podemos ainda contar com o nosso valor como esperado?
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.
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.
Espero que isso ajude, boa sorte.
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.
