Há farta documentação sobre o recurso de particionamento no MySQL 5.1. No entanto, eu pensei que seria útil ter um rápido “how-to” (como-fazer) de particionamento por datas. Eu vou usar o esquema `world` (disponível em http://dev.mysql.com/doc/index-other.html), assim, fica mais fácil de seguir os meus exemplos.
Particionamento de uma tabela por um intervalo de datas é bastante popular. Infelizmente, a partição RANGE só aceita um número inteiro (ou uma função que retorne um número inteiro) como a expressão que determinará a criação da partição. Isso é ótimo se você quiser partição tipos numéricos como este:
ALTER PARTITION BY RANGE City TABLE (id)
(
PARTITION p0 VALUES LESS THAN (1000),
PARTITION p1 VALUES LESS THAN (2000),
PARTITION p2 VALUES LESS THAN (3000),
PARTITION p3 VALUES LESS THAN (4000),
PARTITION p4 VALUES LESS THAN (MAXVALUE)
);
NOTA IMPORTANTE: quando utilizando particionamento do tipo RANGE, as partições devem ser definidas usando-se “valores inferiores”, e os intervalos devem ser listados em ordem crescente.
Se você preferir fazer um particionamento de uma coluna DATE ou DATETIME, as coisas não são tão simples como o exemplo acima. Você não pode usar uma data diretamente (por exemplo, essa sintaxe apresentará um erro: date_column (intervalo) .. PARTITION p0 VALUES LESS THAN (‘2000-01-01 ‘)). Há uma diversas soluções possíveis, mas o meu favorito é o uso da função TO_DAYS.
Primeiro eu preciso de uma coluna DATE, e, por isso vamos adicionar uma à tabela City:
ALTER TABLE ADD City citydate DATE;
# Agora, que tal preencher a coluna, acima criada, com dias aleatórios dos últimos 3 anos ~
UPDATE City SET citydate = current_date – INTERVAL truncate(rand()*1000,0) DAY;
# Retire o particionamento (efetuado no início do texto) da tabela City
ALTER TABLE City REMOVE PARTITIONING;
# Remova o PRIMARY KEY e substitua o por outro índice (vou explicar abaixo)
ALTER TABLE DROP PRIMARY KEY City, ADD INDEX (id);
# Partição pela coluna citydate que é um DATE:
ALTER TABLE City PARTITION BY RANGE (to_days(citydate))
(
PARTITION p0 VALUES LESS THAN (to_days(‘2007-01-01’)),
PARTITION p1 VALUES LESS THAN (to_days(‘2008-01-01’)),
PARTITION p2 VALUES LESS THAN (to_days(‘2009-01-01’)),
PARTITION p3 VALUES LESS THAN (to_days(‘2009-03-01’)),
PARTITION p4 VALUES LESS THAN (to_days(‘2009-06-01’)),
PARTITION p5 VALUES LESS THAN (MAXVALUE)
);
Observe que as partições não precisam ter o mesmo padrão, digo, o mesmo intervalo. Isto é muito útil, você pode querer colocar os registros mais antigos que não são acessados frequentemente em uma partição e manter os dados recentes em pequenas, portanto, melhorando a performance.
Vamos ver o “mysql pruning”* em ação. Se eu executar uma consulta que só precisa de linhas de uma determinada hartição, o otimizador irá apenas ler as partições necessárias, descartando as demais:
EXPLAIN PARTITIONS SELECT count(*) FROM City WHERE citydate BETWEEN ‘2009-01-01’ AND ‘2009-08-01’;
+—-+————-+——-+————+——+—————+——+———+——+——+————-+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+————+——+—————+——+———+——+——+————-+
| 1 | SIMPLE | City | p3,p4,p5 | ALL | NULL | NULL | NULL | NULL | 4079 | Using where |
+—-+————-+——-+————+——+—————+——+———+——+——+————-+
* “Mysql pruning” é responsável por analisar a cláusula WHERE, e se, a tabela for particionada, ele determinará quais partições ler e quais descartar para determinada consulta (SELECT).
Observe que o otimizador percebeu que só precisará usar as partições p3, p4 e p5 para encontrar as linhas solicitadas pelo SELECT acima ( veja o conteúdo da coluna partitions).
Agora, deixe-me explicar porque eu removi a chave primária. Existe uma regra sobre o uso de restrições (constraints) de unicidade (primary keys) com particionamento. A resposta é curta e direta: você não pode ter uma restrição exclusiva (primary key) em uma coluna na qual não será usada no particionamento. A razão disto é que quando você inserir um registro, a singularidade (PK) deve ser verificada, e não queremos que ocorra uma busca através de cada partição para verificar a singularidade (validação da PK). Pois isto, seria muito dispendioso. Os índices são locais para cada partição (índices globais devem ser implementadas no futuro). Então você pode ter apenas uma restrição (constraint) exclusiva se todas as colunas na restrição (PK) forem utilizados na expressão de particionamento.
Sarah Sproehnle
MySQL Senior Instructor