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