Particionamento no MySQL

[vc_row][vc_column][vc_column_text]

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)

[/vc_column_text][wgl_spacing spacer_size=”40px”][vc_column_text]

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.[/vc_column_text][wgl_spacing spacer_size=”60px”][vc_column_text]

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[/vc_column_text][wgl_spacing spacer_size=”60px”][vc_column_text]

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:

[/vc_column_text][wgl_spacing spacer_size=”16px”][vc_column_text]

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

[/vc_column_text][wgl_spacing spacer_size=”16px”][vc_column_text]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?[/vc_column_text][wgl_spacing spacer_size=”16px”][/vc_column][/vc_row][vc_row][vc_column][vc_message message_box_color=”warning” icon_fontawesome=”fas fa-highlighter”]Dica:

Verifique qual é o campo de maior cardinalidade e com a maior capacidade de agrupamento, esse será o melhor a ser utilizado em seu particionamento. Campos do tipo “data” são quase sempre os melhores a serem particionados, pois são agrupados por “dia,mês,ano” e tem um alto índice de cardinalidade. Além dos campos de data, campos de “ID” ou “HASH” também são ótimos para particionamento.[/vc_message][wgl_spacing spacer_size=”16px”][vc_column_text]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/[/vc_column_text][wgl_spacing spacer_size=”60px”][vc_column_text]

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:[/vc_column_text][wgl_spacing spacer_size=”16px”][vc_column_text]

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;

[/vc_column_text][wgl_spacing spacer_size=”16px”][vc_column_text]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:[/vc_column_text][wgl_spacing spacer_size=”32px”][vc_single_image image=”1512″ img_size=”full” alignment=”center”][wgl_spacing spacer_size=”32px”][vc_column_text]Para isso vamos utilizar o tipo “RANGE”, com o auxilio da função “YEAR”, segue:[/vc_column_text][wgl_spacing spacer_size=”16px”][vc_column_text]

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) */;

[/vc_column_text][wgl_spacing spacer_size=”16px”][vc_column_text]O datadir conterá os respectivos datafiles:[/vc_column_text][wgl_spacing spacer_size=”16px”][/vc_column][/vc_row][vc_row][vc_column][vc_single_image image=”1518″ img_size=”full” css_animation=”none” css=”.vc_custom_1590512432099{border-radius: 15px !important;}”][wgl_spacing spacer_size=”30px”][vc_message]Nota:

O exemplo acima exibe o comando de criação de uma tabela particionada, sendo também possível particionar uma tabela já existente, ou adicionar e excluir partições, exemplos:[/vc_message][vc_column_text]

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

[/vc_column_text][vc_column_text]

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

[/vc_column_text][vc_column_text]

ALTER TABLE salaries DROP PARTITION p99, p100;

[/vc_column_text][vc_column_text]

uteis para manutenção em tabelas.

[/vc_column_text][wgl_spacing spacer_size=”60px”][/vc_column][/vc_row][vc_row][vc_column][vc_column_text]

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[/vc_column_text][wgl_spacing spacer_size=”60px”][vc_column_text]

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

https://dev.mysql.com/doc/refman/5.6/en/partitioning-limitations.html[/vc_column_text][wgl_spacing spacer_size=”60px”][vc_column_text]

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

https://dev.mysql.com/doc/refman/5.6/en/partitioning.html[/vc_column_text][vc_empty_space height=”20px”][vc_message]Nota:

Entre as restrições atuais ( que já estão em curso de melhoria ) a restrição mais crítica é a limitação de trabalhar simultaneamente em uma mesma tabela com “Foreign Key” e “Particionamento”, ou seja, não é possível atualmente particionar uma tabela que contenha “Foreign Key”.[/vc_message][/vc_column][/vc_row]

Por Rox Partner

• Banco de Dados

Otimização e Performance de Banco de Dados: Conte com a Rox!

Quer otimizar ainda mais a performance do seu banco de dados? A Rox Partner pode te ajudar a transformar dados em insights estratégicos para o seu negócio. Fale com a gente!

Nossos Serviços