Particiones MySQL timestamp - fecha y hora

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

Así que hace poco me di cuenta de que aún no he hablado mucho sobre las particiones de MySQL.
Muchos mensajes de blog en buenas particiones MySQL ya existe y yo hemos enumerado algunos abajo.

• http://glynndba.blogspot.com/2009/10/rolling-time-based-partitions.html
• http://www.mysqlperformanceblog.com/2010/12/11/mysql-partitioning-can-save-you-or-kill-you/
• http://datacharmer.blogspot.com/2010/05/two-quick-performance-tips-with-mysql.html
• http://dev.mysql.com/tech-resources/articles/mysql_55_partitioning.html

Sucede que venir a través del siguiente situación y espero que sea útil a los demás.

Si bien a menudo se utiliza el tipo de datos de marca de tiempo de fecha y hora funciona mejor con particiones.
(Timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP)

Así que el siguiente es un ejemplo de cómo la forma de solucionar este problema.

Para empezar tenemos esta sencilla tabla.


CREATE TABLE `t1` (
`t1_id` int(11) NOT NULL AUTO_INCREMENT,
`field1` varchar(25) DEFAULT NULL,
`field2` int(10) DEFAULT '0',
`time_recorded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`t1_id`),
KEY `tr` (`time_recorded`)
) ENGINE=InnoDB AUTO_INCREMENT=856964

Queremos crear particiones en el campo time_recorded. Para que sea de particular vamos a romperlo a cabo al mes.


ALTER TABLE t1
PARTITION BY RANGE ( TO_DAYS(time_recorded) ) (
PARTITION Jan2014 VALUES LESS THAN (TO_DAYS('2014-02-01')),
PARTITION Feb2014 VALUES LESS THAN (TO_DAYS('2014-03-01')),
PARTITION Mar2014 VALUES LESS THAN (TO_DAYS('2014-04-01')),
PARTITION Apr2014 VALUES LESS THAN (TO_DAYS('2014-05-01')),
PARTITION May2014 VALUES LESS THAN (TO_DAYS('2014-06-01')),
PARTITION Jun2014 VALUES LESS THAN (TO_DAYS('2014-07-01')),
PARTITION Jul2014 VALUES LESS THAN (TO_DAYS('2014-08-01')),
PARTITION Aug2014 VALUES LESS THAN (TO_DAYS('2014-09-01')),
PARTITION Sep2014 VALUES LESS THAN (TO_DAYS('2014-10-01')),
PARTITION Oct2014 VALUES LESS THAN (TO_DAYS('2014-11-01')),
PARTITION Nov2014 VALUES LESS THAN (TO_DAYS('2014-12-01')),
PARTITION Dec2014 VALUES LESS THAN (TO_DAYS('2015-01-01')),
PARTITION Jan2015 VALUES LESS THAN (TO_DAYS('2015-02-01'))
);
ERROR 1486 (HY000): Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed

¿Y ahora qué ....

Bueno, yo sé que tenemos la clave primaria actualizada si queremos que la partición.


ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY (`t1_id`,`time_recorded`), LOCK=NONE;
Query OK, 0 rows affected (38.96 sec)
Records: 0 Duplicates: 0 Warnings: 0

Tenga en cuenta que tengo LOCK = NONE esto se debe a que estoy usando MySQL 5.6

• http://dev.mysql.com/doc/ refman/5.6/en/innodb-online- ddl.html
• http://dev.mysql.com/doc/ refman/5.6/en/innodb-create- index-examples.html
• https://blogs.oracle.com/ mysqlinnodb / entrada / Online_ alter_table_in_mysql

El cambio de clave principal no permitirá que añada las particiones sin embargo, todavía tengo que ajustar el tipo de datos. Estoy utilizando los = BLOQUEADO COMPARTIDOS este momento. Por favor revise los hipervínculos arriba para obtener más información. Si quieres pasar a recoger un tipo que no funciona, por lo general sugiere una solución viable.


ALTER TABLE t1 CHANGE time_recorded time_recorded datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, LOCK=SHARED;
Query OK, 854312 rows affected (41.89 sec)
Records: 854312 Duplicates: 0 Warnings: 0

Así que ahora podemos añadir nuestra partición.


ALTER TABLE t1
-> PARTITION BY RANGE ( TO_DAYS(time_recorded) ) (
-> PARTITION Jan2014 VALUES LESS THAN (TO_DAYS('2014-02-01')),
-> PARTITION Feb2014 VALUES LESS THAN (TO_DAYS('2014-03-01')),
-> PARTITION Mar2014 VALUES LESS THAN (TO_DAYS('2014-04-01')),
-> PARTITION Apr2014 VALUES LESS THAN (TO_DAYS('2014-05-01')),
-> PARTITION May2014 VALUES LESS THAN (TO_DAYS('2014-06-01')),
-> PARTITION Jun2014 VALUES LESS THAN (TO_DAYS('2014-07-01')),
-> PARTITION Jul2014 VALUES LESS THAN (TO_DAYS('2014-08-01')),
-> PARTITION Aug2014 VALUES LESS THAN (TO_DAYS('2014-09-01')),
-> PARTITION Sep2014 VALUES LESS THAN (TO_DAYS('2014-10-01')),
-> PARTITION Oct2014 VALUES LESS THAN (TO_DAYS('2014-11-01')),
-> PARTITION Nov2014 VALUES LESS THAN (TO_DAYS('2014-12-01')),
-> PARTITION Dec2014 VALUES LESS THAN (TO_DAYS('2015-01-01')),
-> PARTITION Jan2015 VALUES LESS THAN (TO_DAYS('2015-02-01'))
-> );
Query OK, 854312 rows affected (50.74 sec)
Records: 854312 Duplicates: 0 Warnings: 0

Podemos SELECT, DELETE, UPDATE, INSERT y etc por partición. Más sobre esto aquí: https://dev.mysql.com/doc/refman/5.6/en/partitioning-selection.html .


SELECT COUNT(t1_id) FROM t1 PARTITION (Jan2014);
+--------------+
| COUNT(t1_id) |
+--------------+
| 661752 |
+--------------+
1 row in set (0.55 sec)
SELECT COUNT(t1_id) FROM t1 PARTITION (Feb2014);
+--------------+
| COUNT(t1_id) |
+--------------+
| 64952 |
+--------------+
1 row in set (0.04 sec)
SELECT COUNT(t1_id) FROM t1 PARTITION (Mar2014);
+--------------+
| COUNT(t1_id) |
+--------------+
| 71336 |
+--------------+
1 row in set (0.04 sec)
SELECT COUNT(t1_id) FROM t1 PARTITION (Apr2014);
+--------------+
| COUNT(t1_id) |
+--------------+
| 56272 |
+--------------+
1 row in set (0.05 sec)

Aunque esto funciona, y nosotros ahora tenemos una partición. Ahora también tenemos que considerar el mantenimiento de la partición. blog de ​​Glynn da un muy buen ejemplo de un goteo automatizado y adición de particiones. Por supuesto, si usted no quiere eliminar la partición que tendrá que ajustar el ejemplo.
He ajustado para mi ejemplo de la tabla. Por favor revise el blog de ​​Glynn para más detalles.


DROP PROCEDURE IF EXISTS Rotate_t1_Partition;
DELIMITER ;;
CREATE PROCEDURE Rotate_t1_Partition (newPartValue DATETIME)
BEGIN
-- Setup
DECLARE keepStmt VARCHAR(2000) DEFAULT @stmt;
DECLARE partitionToDrop VARCHAR(64);

-- Find and drop the first partition in the table.
SELECT partition_name
INTO partitionToDrop
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE table_schema='forums_mysql'
AND table_name='t1'
AND partition_ordinal_position=1;
SET @stmt = CONCAT('ALTER TABLE t1 DROP PARTITION ', partitionToDrop);
PREPARE pStmt FROM @stmt;
EXECUTE pStmt;
DEALLOCATE PREPARE pStmt;

-- Add a new partition using the input date for a value limit.
SET @stmt = CONCAT('ALTER TABLE t1 ADD PARTITION (PARTITION ', DATE_FORMAT(newPartValue - interval 1 MONTH, '%b%Y'), ' VALUES LESS THAN (TO_DAYS(\'', DATE_FORMAT(newPartValue, '%Y-%m-%d'),'\')))');
PREPARE pStmt FROM @stmt;
EXECUTE pStmt;
DEALLOCATE PREPARE pStmt;

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

Así que puedo actualizar esta tabla ahora con la siguiente sencilla.


CALL Rotate_t1_Partition('2015-03-01');
Query OK, 0 rows affected (1.11 sec)

Puede también, como el blog de ​​Glynn señala, puede utilizar esto con un NOW () + intervalo de 1 mes o cada vez que marco de tiempo que usted decida para sus particiones. Subvención que tiene que tener en cuenta que se producirá un error si se pasa una fecha que ya es una partición.

Para continuar con la automatización se puede añadir esto a un evento.


CREATE EVENT Rotate_t1_Partition
-> ON SCHEDULE EVERY 1 MONTH
-> DISABLE ON SLAVE
-> COMMENT 'Remove oldest partition and add a new one '
-> DO
-> CALL Rotate_t1_Partition(NOW() + interval 1 MONTH);
Query OK, 0 rows affected (0.04 sec)

Tenga en cuenta un tiempo válido para el ejemplo anterior. Acabo de utilizar esto como un ejemplo.