Original post: http://anothermysqldba.blogspot.com/2014/04/mysql-partitions-timestamp-datetime.html
Então, eu recentemente percebi que eu ainda não falei muito sobre partições MySQL.
Muitos bons posts sobre partições MySQL já existe e eu listei alguns abaixo.
Embora o tipo de dados timestamp é usado frequentemente datetime funciona melhor com partições.
(Timestamp NOT CURRENT_TIMESTAMP PADRÃO NULL ON UPDATE CURRENT_TIMESTAMP)
Assim, o que se lhe segue um exemplo de como a forma de corrigir isso.
Para começar, temos esta tabela simples.
Queremos dividir em campo time_recorded. Para torná-lo dirigir vamos quebrá-lo fora por mês.
Então agora o que ....
Bem, eu sei que precisa da chave principal atualizado, se queremos que a partição.
Repare que eu tenho BLOQUEIO = NONE isso é porque eu estou usando o MySQL 5.6
Então, agora podemos acrescentar a nossa partição.
Podemos selecionar, DELETE, UPDATE, INSERT e etc por partição. Mais sobre isso aqui: https://dev.mysql.com/doc/refman/5.6/en/partitioning-selection.html .
Enquanto isso funciona e nós agora temos uma partição. Também temos agora de considerar a manutenção de partição. blog de Glynn dá um exemplo muito bom de um Dropping automatizada e adicionando de partições. Concedido, se você não quer deixar cair a partição que você terá que ajustar o exemplo.
Eu ajustei-o para o meu exemplo de tabela. Por favor, revise o blog de Glynn para mais detalhes.
Para que eu possa atualizar esta tabela agora com o seguinte facilmente.
Você pode também, como o blog de Glynn aponta, você pode usar isso com um NOW () + intervalo de 1 mês ou sempre que tempo que você decidir por suas partições. Grant você tem que ter em mente que ele irá falhar se você passar uma data que já é uma partição.
Para continuar com a automação você pode adicionar este a um evento.
Tenha em mente uma hora válida para o exemplo acima. Eu apenas usei isso como um exemplo.
Então, eu recentemente percebi que eu ainda não falei muito sobre partições MySQL.
Muitos bons posts sobre partições MySQL já existe e eu listei alguns abaixo.
- 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
Embora o tipo de dados timestamp é usado frequentemente datetime funciona melhor com partições.
(Timestamp NOT CURRENT_TIMESTAMP PADRÃO NULL ON UPDATE CURRENT_TIMESTAMP)
Assim, o que se lhe segue um exemplo de como a forma de corrigir isso.
Para começar, temos esta tabela simples.
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 dividir em campo time_recorded. Para torná-lo dirigir vamos quebrá-lo fora por mês.
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 Então agora o que ....
Bem, eu sei que precisa da chave principal atualizado, se queremos que a partição.
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 Repare que eu tenho BLOQUEIO = NONE isso é porque eu estou usando o 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 / entry / Online_ alter_table_in_mysql
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 Então, agora podemos acrescentar a nossa partição.
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 selecionar, DELETE, UPDATE, INSERT e etc por partição. Mais sobre isso aqui: 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) Enquanto isso funciona e nós agora temos uma partição. Também temos agora de considerar a manutenção de partição. blog de Glynn dá um exemplo muito bom de um Dropping automatizada e adicionando de partições. Concedido, se você não quer deixar cair a partição que você terá que ajustar o exemplo.
Eu ajustei-o para o meu exemplo de tabela. Por favor, revise o blog de Glynn para mais detalhes.
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 ; Para que eu possa atualizar esta tabela agora com o seguinte facilmente.
CALL Rotate_t1_Partition('2015-03-01');
Query OK, 0 rows affected (1.11 sec) Você pode também, como o blog de Glynn aponta, você pode usar isso com um NOW () + intervalo de 1 mês ou sempre que tempo que você decidir por suas partições. Grant você tem que ter em mente que ele irá falhar se você passar uma data que já é uma partição.
Para continuar com a automação você pode adicionar este a um 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) Tenha em mente uma hora válida para o exemplo acima. Eu apenas usei isso como um exemplo.
