Mantenimiento de tablas particionadas en MySQL

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=`nuestro_usuario_de_base_de_datos`@`%` 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

Uso de cookies

GPS Open Source, S.L, le informa que este sitio web utiliza Cookies tanto propias como de terceros para facilitar la navegación y para recopilar información estadística sobre su navegación. Si pulsa aceptar o continúa navegando entenderemos que acepta la instalación de las mismas. Más Info, política de cookies

ACEPTAR
Show Buttons
Share On Facebook
Share On Twitter
Share On Linkedin
Contact us
Hide Buttons