Particionamento no MySQL

A

ideia aqui é introduzir os conceitos de particionamento, exibir os recursos disponíveis no MySQL e passar um exemplo prático de como uma tabela pode ser particionada e quais os benefícios dessa prática. Futuramente abordarei o “subparticionamento”, veja os tópicos que serão abordados:

• Conceitos de particionamento
• Tipos de particionamento
• Elegendo as clausulas de particionamento
• Exemplo prático de particionamento
• Manutenção
• Limitações no MySQL (atualmente)

Conceitos:

O particionamento é um recurso clássico de banco de dados que resolve muitos problemas de escalabilidade e performance além de facilitar o gerenciamento e armazenamento de grandes volumes de dados. Com esse recurso é possível segregar o armazenamento dos dados em segmentos segundo uma clausula, ou seja, por determinado dado presente no registro o mesmo será armazenado com seus demais semelhantes e assim durante uma busca o banco de dados conseguirá indexar melhor os dados isso também otimizará manutenções, manipulação ou realocação dos dados  graças à essa mesma “segregação”, portanto é muito importante eleger corretamente uma clausula de particionamento.

Tipos de particionamento:

• RANGE – Utilizado para um “intervalo” de dados, geralmente utilizado para particionar campos de data ou outro tipo de dado que possibilite criar um “RANGE” de dados, muito útil para consultas com “between” e por ai vai.
• LIST – É um conceito similar ao “RANGE” porém quando você utiliza um “RANGE” obrigatoriamente esses dados são sequenciais, afinal trata-se de um “RANGE”. Já no tipo “LIST” você poderá definirá item a item os dados que pertencem
• HASH – Particionamento realizado por HASH é literalmente um “HASH” do valor da coluna, comumente utilizado com campos de “ID” ou qualquer outra chave, data também é um campo comumente utilizado nesse tipo de particionamento.
• KEY – Particionamento similar ao “HASH”, porém não é possível utilizar funções tais como “YEAR()”,”MONTH()” ou qualquer outro tipo de função sobre os dados, sendo exclusiva a função de particionamento “KEY()”, comumente utilizado em colunas do tipo “INT” onde existem valores auto_increment.

Para mais detalhes e informações sobre os tipos de particionamentos e exemplos claros de cada tipo, acesse:

https://dev.mysql.com/doc/refman/5.6/en/partitioning-types.html

Elegendo as clausulas de particionamento:

Antes mesmo de definir qual será a coluna a ser particionada e a regra e tipo de particionamento, é preciso descobrir quais são as tabelas que “precisam” ou terão melhor aproveitamento desse recurso. Recomendo particionar as maiores tabelas do sistema, geralmente particiono as “top 10” em outros casos até mais tabelas, a recomendação da Oracle para o Oracle Database é que seja realizado o particionamento de tabelas com mais de 2GB, podemos tomar essa métrica como base para o MySQL. Segue uma consulta que ajuda a listar as 10 maiores tabelas do um determinado schema:

select
table_name as TABLE_NAME,
concat(round(sum(DATA_LENGTH+INDEX_LENGTH+DATA_FREE) / 1024/1024/1024, 2), 'G') as 'TOTAL(G)',
concat(round(sum(DATA_LENGTH) / 1024/1024/1024, 2), 'G') 'DADOS(G)',
concat(round(sum(INDEX_LENGTH) / 1024/1024/1024, 2), 'G') as 'INDEX(G)',
concat(round(sum(DATA_FREE) / 1024/1024/1024, 2), 'G') as 'FREE(G)'
from
information_schema.tables
where
table_schema = 'SEU_SCHEMA'
group by table_name
order by sum(DATA_LENGTH+INDEX_LENGTH+DATA_FREE) desc
limit 10

Para selecionar a coluna a ser particionada, verifique quais colunas da PK ou da UNIQUE KEY são favoráveis a serem particionados segundo a descrição dos tipos de particionamento acima, lembre-se que deve-se particionar apenas colunas presentes na PK ou UNIQUE KEY pois do contrário não será performático, visto que em muitos casos a verificação da CONSTRAINT exigirá a checagem de todas as partições, ¿ cachai po wéon?

O exemplo a seguir é baseado em um database de testes  “employee” da própria Oracle disponível no site oficial do MySQL:

https://dev.mysql.com/doc/employee/en/

Exemplo prático de particionamento:

Temos uma tabela com os registros de pagamento de salários que chamaremos de “salaries”, essa tabela possui a seguinte estrutura:

CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`),
KEY `emp_no` (`emp_no`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

A ideia é particionar pelo campo “from_date”, dividindo os dados pelo “ano” de pagamento, dessa forma as inserções, atualizações e consultas que são mais comuns “ano corrente” serão realizadas em um datafile menor, veja:

Para isso vamos utilizar o tipo “RANGE”, com o auxilio da função “YEAR”, segue:

CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`),
KEY `emp_no` (`emp_no`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (year(from_date))
(PARTITION p1 VALUES LESS THAN (2006) ENGINE = InnoDB,
PARTITION p2 VALUES LESS THAN (2007) ENGINE = InnoDB,
PARTITION p3 VALUES LESS THAN (2008) ENGINE = InnoDB,
PARTITION p4 VALUES LESS THAN (2009) ENGINE = InnoDB,
PARTITION p5 VALUES LESS THAN (2010) ENGINE = InnoDB,
PARTITION p6 VALUES LESS THAN (2011) ENGINE = InnoDB,
PARTITION p7 VALUES LESS THAN (2012) ENGINE = InnoDB,
PARTITION p8 VALUES LESS THAN (2013) ENGINE = InnoDB,
PARTITION p9 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */;

O datadir conterá os respectivos datafiles:

ALTER TABLE salaries ADD PARTITION (PARTITION p77 VALUES LESS THAN (2015));

ou mesmo deletar uma partição e assim por diante através de um comando de drop:

ALTER TABLE salaries DROP PARTITION p99, p100;

uteis para manutenção em tabelas.

Manutenção:

Outro fator relevante ao ser particionar uma tabela é a capacidade de dar manutenção no banco de dados, visto que o tamanho dos “dataset” de dados fica menor. Até a versão 5.6 o MySQL não permite a realização do “OPTIMIZE TABLE” ou “ALTER TABLE” online, sendo assim as rotinas de manutenção tabelas particionadas gera lock, mas da versão 5.7 em diante isso já foi otimizado, sendo assim é possível realizar manutenções de forma mais simples. Outra capacidade interessante é a possibilidade de “retirar” uma partição da tabela através do comando “ALTER TABLE … DISCARD PARTITION … TABLESPACE“, isso facilita a exclusão de determinado “RANGE” de dados ou realocação desses dados em outro servidor, o que acaba sendo muito mais difícil em uma tabela não particionada pois necessitará de operações DML para exportação e importação dos dados.

https://dev.mysql.com/doc/refman/5.7/en/tablespace-copying.html

Limitações e restrições até a versão 5.6:

https://dev.mysql.com/doc/refman/5.6/en/partitioning-limitations.html

Documentação e guia de referência do Particionamento:

https://dev.mysql.com/doc/refman/5.6/en/partitioning.html