Original post: http://anothermysqldba.blogspot.com/2014/12/a-mysql-partition-and-subpartition.html
Así que esto es sólo un ejemplo sencillo de cómo configurar una partición y un subpartición en MySQL. El concepto aquí es que usted tiene datos en una tabla con numerosos valores en un campo de fecha y hora. Es posible que tenga datos que se propagan a través de numerosos años (probablemente lo hace). Así que una forma de partición de estos datos es de tipo que por años, pero luego también ordenarla por mes dentro de esa partición anual.
A continuación se muestra un ejemplo que puede utilizar para su consideración.
Tenga en cuenta la tabla de prueba. Su mesa con tener muchos más campos de golf.
En primer lugar voy a llenar la tabla de prueba con valores aleatorios para el campo fecha_hora.
Compruebe para ver qué tipo de valores que terminé con:
Ahora puedo modificar la tabla por lo que puedo añadir mis particiones y luego probar los recuentos de valores a través de la partición.
Mi presentación crear la tabla es muy diferente ahora.
Entonces, ¿podemos seguir contando nuestro valor como se esperaba?
Hasta aquí todo bien, todos los valores corresponden a la cuenta que teníamos antes. Así también podemos contar o seleccione por el subpartición.
Esto es muy bueno y práctico, pero lo que sucede cuando el año 2015 o 2016 viene por ahí? Todos esos datos estarían en la partición pmáx. Entonces, ¿cómo le añadimos una nueva partición entre P2014 y pmax?
Si tuvieras hay datos en pmax podría caerse al suelo y añadir una nueva partición en el extremo. Pero es igual de fácil para reorganizar la partición. Esto se llevará a la partición pmax y alterar en nuestras nuevas particiones.
Espero que esto ayude, mejor de las suertes.
Así que esto es sólo un ejemplo sencillo de cómo configurar una partición y un subpartición en MySQL. El concepto aquí es que usted tiene datos en una tabla con numerosos valores en un campo de fecha y hora. Es posible que tenga datos que se propagan a través de numerosos años (probablemente lo hace). Así que una forma de partición de estos datos es de tipo que por años, pero luego también ordenarla por mes dentro de esa partición anual.
A continuación se muestra un ejemplo que puede utilizar para su consideración.
Tenga en cuenta la tabla de prueba. Su mesa con tener muchos más campos de golf.
CREATE TABLE `t1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`date_time` datetime DEFAULT NOW(),
PRIMARY KEY (`id`)
) ENGINE=InnoDB; En primer lugar voy a llenar la tabla de prueba con valores aleatorios para el campo fecha_hora.
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); Compruebe para ver qué tipo de valores que terminé con:
> 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) Ahora puedo modificar la tabla por lo que puedo añadir mis particiones y luego probar los recuentos de valores a través de la partición.
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
)
); Mi presentación crear la tabla es muy diferente ahora.
> 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)) Entonces, ¿podemos seguir contando nuestro valor como se esperaba?
> 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 Hasta aquí todo bien, todos los valores corresponden a la cuenta que teníamos antes. Así también podemos contar o seleccione por el subpartición.
> 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 |
+-----+---------------------+ Esto es muy bueno y práctico, pero lo que sucede cuando el año 2015 o 2016 viene por ahí? Todos esos datos estarían en la partición pmáx. Entonces, ¿cómo le añadimos una nueva partición entre P2014 y pmax?
Si tuvieras hay datos en pmax podría caerse al suelo y añadir una nueva partición en el extremo. Pero es igual de fácil para reorganizar la partición. Esto se llevará a la partición pmax y alterar en nuestras nuevas particiones.
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 esto ayude, mejor de las suertes.
