Cuando alguna de las tablas de una base de datos llega a
crecer tanto que el rendimiento empieza a ser un problema, es hora de empezar a
conocer algo sobre optimización. Una característica de MySQL son las
particiones.
Particionar tablas en MySQL nos permite rotar la
información de nuestras tablas en diferentes particiones, consiguiendo así
realizar consultas más rápidas y recuperar espacio en disco al borrar los registros.
El uso más común de particionado es según la fecha.
Para ver si nuestra base de datos soporta particionado
simplemente ejecutamos:
SHOW VARIABLES LIKE '%partition%';
Se puede particionar una tabla de 5 maneras diferentes:
·
Por Rango: para construir las particiones
se especifican rangos de valores.
ALTER TABLE contratos
PARTITION BY RANGE (YEAR (fechaInicio)) (
PARTITION partDecada80 VALUES LESS THAN (1990),
PARTITION partDecada90 VALUES LESS THAN (2000),
PARTITION partDecada00 VALUES LESS THAN (2010),
PARTITION partDefault VALUES LESS THAN MAXVALUE
);
La última partición (partDefault) tendrá todos los
registros que no entren en las particiones anteriores. De esta manera nos
aseguramos que la información nunca dejará de insertarse en la tabla.
·
Por Listas: para construir nuestras
particiones especificamos listas de valores concretos.
ALTER TABLE contratos
PARTITION BY LIST (YEAR (fechaInicio)) (
PARTITION partDecada80
VALUES IN (1980, 1981, 1982, 1983, 1984, 1985, 1986, 1987, 1988, 1989),
PARTITION partDecada90
VALUES IN (1990, 1991, 1992, 1993, 1994, 1995, 1996, 1997, 1998, 1999),
PARTITION partDecada00
VALUES IN (2000, 2001, 2002, 2003, 2004, 2005, 2006,
2007, 2008, 2009),
PARTITION partDecada10 VALUES IN (2010, 2011, 2012,
2013, 2014, 2015, 2016,
2017, 2018, 2019)
);
·
Por Hash: MySQL se encarga de distribuir
las tuplas automáticamente usando una operación de módulo. Sólo hay que pasarle
una columna o expresión que resulte en un entero (el hash) y el número de
particiones que queramos crear.
ALTER TABLE contratos
PARTITION BY HASH (YEAR (fechaInicio))
PARTITIONS 7;
·
Por Clave: similar a la partición por
hash, pero en este caso no necesitamos pasarle un entero; MySQL utilizará su
propia función de hash para generarlo. Si no se indica ninguna columna a partir
de la que generar el hash, se utiliza la clave primaria por defecto.
ALTER TABLE contratos
PARTITION BY KEY ()
PARTITIONS 7;
·
Compuesta: podemos combinar los distintos
métodos de particionado y crear particiones de particiones
Borrar Particiones
Lo bueno de trabajar con particiones es que podemos borrar
rápidamente registros sin tener que recorrer toda la tabla e inmediatamente
recuperar el espacio en disco utilizado por la tabla.
Por ejemplo si queremos borrar la partición más antigua
simplemente ejecutamos:
ALTER TABLE reports DROP PARTITION p201111;
Añadir particiones
En el ejemplo anterior las 2 últimas particiones creadas
han sido:
PARTITION p201205 VALUES LESS THAN (TO_DAYS
("2012-06-01")),
PARTITION pDefault VALUES LESS THAN MAXVALUE
El problema es que todos los INSERT que se hagan
después de mayo de 2012 se insertarán en pDefault. La solución sería añadir
particiones nuevas para cubrir los próximos meses:
ALTER TABLE reports REORGANIZE PARTITION pDefault INTO (
PARTITION p201206 VALUES LESS THAN (TO_DAYS
("2012-07-01")),
PARTITION pDefault VALUES LESS THAN MAXVALUE);
En el caso que no tuviéramos una partición del tipo
pDefault simplemente ejecutamos:
ALTER TABLE reports ADD PARTITION (PARTITION p201206 VALUES
LESS THAN (TO_DAYS ("2012-07-01")));
Consultar Particiones
Para consultar información de particiones creadas en una
tabla así como también los registros que contiene cada una ejecutamos:
SELECT PARTITION_NAME, TABLE_ROWS FROM information_schema.PARTITIONS
WHERE TABLE_NAME='reports';
Comentarios
Publicar un comentario