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