Mantenimiento de tablas particionadas en MySQL

Públicado en por

Hola,

En esta ocasión veremos un interesante recurso que tiene MySQL: El particionamiento de tablas.

Esta técnica se suele aplicar cuando se manejan millones de registros en una sola tabla. Existen varios tipos de particionamiento, pero os vamos a hablar del particionamiento por rango. Este tipo de particionamiento es muy útil en el caso de campos con tipo fecha, en los que vamos a crear particiones por día, mes, años…

Esta metodología tiene el inconveniente de que requiere cierto mantenimiento, ya que hay que crear cada vez más particiones e ir borrando antiguas. Para MySQL internamente cada partición es un fichero abierto cuando se lanza una query, por eso tampoco es recomendable tener muchas particiones ya que se irían acumulando y podría provocar incluso la caída de la instancia de MySQL. Por este motivo os vamos a enseñar como crear un procedimiento que lleve este mantenimiento. Se encargará por un lado de borrar la última partición y por otro, de crear una nueva.

Creación del procedimiento

Una vez que tenemos nuestro criterio de particionamiento elegido y una tabla particionada (en este caso por día), crearemos el procedimiento de la siguiente manera:

DELIMITER $$
CREATE DEFINER=`gpsoses`@`%` PROCEDURE `rotar_particion_pedidos`(newPartValue DATETIME)
BEGIN
-- Declaramos las variables necesarias
DECLARE keepStmt VARCHAR(2000) DEFAULT @stmt;
DECLARE partitionToDrop VARCHAR(64);

-- Añade una nueva partición con la fecha actual
SET @stmt = CONCAT('ALTER TABLE pedidos REORGANIZE PARTITION pmaxvalue INTO (PARTITION p', DATE_FORMAT(curdate(),'%Y%m%d'), ' VALUES LESS THAN (\'', DATE_FORMAT(curdate() + interval 1 day, '%Y-%m-%d 00:00:00'),'\'),
PARTITION pmaxvalue VALUES LESS THAN MAXVALUE);');
PREPARE pStmt FROM @stmt;
EXECUTE pStmt;
DEALLOCATE PREPARE pStmt;

-- Busca y borra la primera partición de la tabla
SELECT partition_name
INTO partitionToDrop
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE table_schema='facturacion'
AND table_name='pedidos'
AND partition_ordinal_position=1;

SET @stmt = CONCAT('ALTER TABLE pedidos DROP PARTITION ', partitionToDrop);
PREPARE pStmt FROM @stmt;
EXECUTE pStmt;
DEALLOCATE PREPARE pStmt;

-- Cleanup
SET @stmt = keepStmt;
END$$
DELIMITER ;

Llamada al procedimiento

Una vez creado el procedimiento, habría que ejecutarlo de la siguiente manera de forma manual, o a través de cron o algún evento de MySQL creado para tal fin que se ejecute una vez al día, al mes, año…

call rotar_particion_pedidos(NOW());

Si os ha gustado y os ha sido útil esta entrada, no os perdáis la siguiente, en la que hablaremos de cómo crear una tabla particionada o particionar una ya existente entre otras cosas.

Nos vemos en próximas entradas,

Equipo de base de datos

Fuentes:

http://anothermysqldba.blogspot.com.es/2014/04/mysql-partitions-timestamp-datetime.html

http://dan1456bd.blogspot.com.es/p/particiones-en-mysql-y-oracle.html