Atenção: esse artigo tem um vídeo complementar. Clique e assista!

Artigo no estilo: Curso

De que trata o artigo:

Este artigo trata dos recursos relacionados com o particionamento de tabelas com a finalidade de se obter melhor desempenho de bases de dados no MySQL, fornecendo ao leitor, nessa primeira parte do artigo, uma visão prática do recurso com base na sua aplicação em ambiente empresarial.


Para que serve:

Aprenda a particionar tabelas consideradas grandes – geralmente tabelas com mais de 2GB – no servidor de bancos de dados MySQL para obter melhor resultado relacionado com o tempo de resposta em consultas e melhor proveito de hardware e estratégias de TI, como a utilização de mais de um disco para armazenar tabelas de bancos de dados.


Em que situação o tema é útil:

Em situações onde o banco de dados de uma organização está crescendo de tamanho gradativamente, e é necessário buscar soluções para evitar problemas de desempenho em consultas SQL. Nesse sentido, a estratégia de particionamento se apresenta como uma alternativa interessante, pois divide a tabela em partes menores de forma que consultas possam ser realizadas apenas nas partes que contenham os dados requisitados.

Há algum tempo, a mais nova versão do servidor de bancos de dados MySQL foi publicada no site oficial do produto como sendo uma versão Generally Available Release, ou somente GA, que significa que o produto já foi testado o bastante e que foi considerado pela equipe de homologadores como estável, embora pequenos bugs ainda sejam observados. Com isso, vários novos recursos foram lançados juntamente com a nova versão, tais como melhor controle sobre transações ou novas declarações sintáticas para levantar erros e melhor tratá-los com base no SQLSTATE. O SQLSTATE (Nota DevMan 1) é uma sequência de caracteres que indica que algum tipo de erro aconteceu com uma manipulação de dados dentro do servidor MySQL. Este tipo de erro poderá se apresentar como um Error, um Warning ou ainda um Not Found.

Nota DevMan 1. SQLState

SQLSTATE fornece códigos de retorno em caso de sucesso, alerta e condições de erro encontrados em um banco de dados. Os valores SQLSTATE são particularmente úteis quando estamos lidando com erros em instruções SQL. Esses valores são consistentes com a especificação SQLSTATE contida no padrão SQL 1999.

Um valor SQLSTATE é um código de retorno que indica o resultado da instrução SQL mais recente que foi executada. O mecanismo usado para acessar os valores SQLSTATE depende de onde a instrução SQL é executada.

O MySQL possui uma função chamada mysql_sqlstate() [cuja sintaxe é const char *mysql_sqlstate(MYSQL *mysql)] que retorna uma string contendo o código do erro SQLSTATE para as instruções SQL mais recentes que foram executadas.

O código de erro consiste de 5 caracteres.'00000'significa “sem erro”. Os valores são especificados por ANSI SQL e ODBC. Para uma lista de todos os possíveis valores, acesse o link http://dev.mysql.com/doc/refman/5.0/en/error-handling.html.

O valor de SQLSTATE retornado pela função mysql_sqlstate()é diferente do número de erro específico do MySQL retornado pela função mysql_errno(). Por exemplo, o programa de cliente do mysqlexibe um erro usando o seguinte formato, onde 1146 é o valor de mysql_errno()e '42S02'é o valor correspondente da função mysql_sqlstate().


  shell> SELECT * FROM no_such_table;
  ERROR 1146 (42S02): Table 'test.no_such_table' doesn't exist

Nem todos os números de erros do MySQL são mapeados para códigos de erro do SQLSTATE. O valor 'HY000'(erro geral) é usado para números de erros não mapeados.

No entanto, o recurso desenvolvido por Mikael Ronstrom (MySQL Architect and Lead Developer) que mais agradou a "gregos e troianos" pela dinâmica e melhoria de desempenho relacionado com a leitura e escrita de dados em tabelas no MySQL foi o recurso de particionamento de tabelas, denominado MySQL Partitioning, que é muito comentado pela comunidade em geral e também pelos próprios membros do MySQL Developer Team.

Neste artigo, conheceremos um pouco mais sobre o recurso de particionamento do MySQL (MySQL Partitioning), os diferentes tipos de particionamento que podem ser criados e também como criar subparticionamentos, possibilitando a divisão de particionamentos em diferentes discos.

Visão Geral do MySQL Partitioning

O MySQL Partitioning está disponível a partir da versão 5.1 do MySQL. Tal recurso funciona para vários Storage Engines (ver Nota DevMan 2), exceto para MERGE, CSV e FEDERATED. Ele funciona como um plugin e na verdade é também um engine, chamado de partition engine, localizado na primeira camada do MySQL juntamente com outros módulos como parser, optimizer, transformation, etc.

Nota DevMan 2. Storage Engines no MySQL

O MySQL possui um conceito chamado storage engines (motor de armazenamento) ou simplesmente tipos de tabela. Assim, o servidor e o desenvolvedor podem escolher como e onde uma tabela de banco de dados será armazenada e qual storage engine seria mais adequado para uma situação particular.

Para determinar quais storage engines seu servidor MySQL suporta, podemos usar o comando SHOW ENGINES. O valor da coluna Supportindica se um engine pode ser usado. Um valor de YES,NO, ouDEFAULTindica que um engine está disponível, não disponível ou disponível e configurado atualmente como o storage engine padrão. Veja o exemplo a seguir:


  mysql> SHOW ENGINES\G
  *************************** 1. row ***************************
  Engine: FEDERATED
  Support: NO
  Comment: Federated MySQL storage engine
  Transactions: NULL
  XA: NULL
  Savepoints: NULL
  *************************** 2. row ***************************
  Engine: MRG_MYISAM
  Support: YES
  Comment: Collection of identical MyISAM tables
  Transactions: NO
  XA: NO
  Savepoints: NO

Entre as storage engines (tipos de tabelas) disponíveis no MySQL, podemos citar: MyISAM, InnoDB, Memory, Merge, Archive, Federated, NDBCluster, CVS, dentre outras. As storage engines variam de acordo com diversas características, como: limite de armazenamento, suporte a transações em BD, granularidade de bloqueios, índices em tabelas hash, compressão dos dados, suporte a consultas em full-text, suporte a backup, criptografia dos dados, recurso de chave estrangeira, recurso de replicação de dados, dentre outros.

Podemos executar algumas consultas para checar a existência do partition engine em um servidor MySQL e se ele está atualmente habilitado como exibido na Listagem 1.

Listagem 1. Consultas em um servidor MySQL para verificar a ativação do partition engine


  mysql> SHOW PLUGINS; 
  +------------+--------+----------------+---------+---------+ 
  | Name       | Status | Type           | Library | License | 
  +------------+--------+----------------+---------+---------+ 
  | binlog     | ACTIVE | STORAGE ENGINE | NULL    | GPL     | 
  | partition  | ACTIVE | STORAGE ENGINE | NULL    | GPL     | 
  | ARCHIVE    | ACTIVE | STORAGE ENGINE | NULL    | GPL     | 
  | BLACKHOLE  | ACTIVE | STORAGE ENGINE | NULL    | GPL     | 
  | CSV        | ACTIVE | STORAGE ENGINE | NULL    | GPL     | 
  | FEDERATED  | ACTIVE | STORAGE ENGINE | NULL    | GPL     | 
  | MEMORY     | ACTIVE | STORAGE ENGINE | NULL    | GPL     | 
  | InnoDB     | ACTIVE | STORAGE ENGINE | NULL    | GPL     | 
  | MyISAM     | ACTIVE | STORAGE ENGINE | NULL    | GPL     | 
  | MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL    | GPL     | 
  +------------+--------+----------------+---------+---------+ 
  10 rows in set (0,00 sec) 
   
  mysql> show variables like '%partitioning%'; 
  +-------------------+-------+ 
  | Variable_name     | Value | 
  +-------------------+-------+ 
  | have_partitioning | YES   | 
  +-------------------+-------+ 
  1 row in set (0,00 sec) 

Podemos verificar no retorno da consulta da Listagem 1 que em ambas as consultas o partition engine (ver Nota DevMan 3) está habilitado atualmente, com o status de ACTIVE, ou seja, podemos utilizar os recursos propostos para o particionamento de tabelas. Na segunda consulta, recebemos o segundo sinal verde para continuarmos em nosso artigo. Caso o retorno de tais consultas em seu MySQL não retorne os dados como são visualizados acima, verifique a versão do MySQL em utilização e veja se é compatível com o recurso que estamos abordando neste artigo.

Nota DevMan 3. Partitioning Engine – Motor de Particionamento

Software modular que está localizado na primeira camada do servidor de bancos de dados MySQL que possibilita a criação de partições em tabelas controladas por qualquer Storage Engine.

Caso você tenha compilado o MySQL 5.1 a partir do código fonte, garanta que a opção --with-partition tenha sido fornecida para habilitar o partition engine. Por outro lado, na intenção de desabilitar o recurso, o que não é caso, você poderá iniciar o mysqld (servidor de bancos de dados MySQL propriamente dito) com a opção --skip-partitioning ou mesmo informar esta opção no arquivo my.cnf ou my.ini, dentro do agrupamento [mysqld].

O MySQL Partitioning possibilitará que o DBA lide com grandes problemas relacionados com os limites do MySQL, que na verdade estão localizados no sistema operacional. Um deles é bastante conhecido: ter arquivos muito grandes localizados em um só diretório, o que em muitas distribuições Linux já é possível driblar. Porém, é muito mais adequado que, no caso de bancos de dados, a solução para este problema seja usar o MySQL Partitioning. Isto por que particionar tabelas também oferece grandes ganhos no desempenho ou na capacidade de resolução de consultas e diminuição do tempo de resposta por parte do SGBD.

Até a versão atual em produção, que é a 5.5, o MySQL Partitioning somente suporta o particionamento de tabelas de forma horizontal. O particionamento horizontal divide uma tabela em várias tabelas. Cada tabela contém o mesmo número de colunas, mas menos linhas. Por exemplo, uma tabela pode ser particionada horizontalmente em 12 tabelas, com cada tabela menor representando um mês de dados de um ano específico. Geralmente, tabelas muito grandes, aquelas maiores que 4GB, são candidatas a uma análise, e de acordo com a regra de negócio essas devem ser particionadas. Isso reduzirá o tamanho do arquivo em disco e, consequentemente, resultará em diminuição do tempo de resposta de consultas.

Outro ponto que o MySQL Partitioning facilita a vida de DBAs é a possibilidade de distribuir os arquivos de tabelas particionadas por dois ou mais discos localizados na estrutura de servidores que a empresa possui. Isso será possível com a criação de subpartições ou então das chamadas composite partitioning, que veremos mais à frente dentro neste artigo.

Devido à disponibilização deste recurso, também nesta versão do MySQL foi adicionada a tabela PARTITIONS, localizada no dicionário de dados do MySQL, o INFORMATION_SCHEMA. Após criarmos as partições nas tabelas de um banco de dados, podemos acompanhar o comportamento relacionado com o preenchimento das partições após as inserções de algumas linhas. Através da tabela PARTITIONS podemos verificar os nomes dados às partições, bem como a quantidade de registros em cada partição, a média de bytes que uma linha contém e várias outras informações que não são tão importantes neste momento. Ao passo que formos utilizando as colunas desta tabela do dicionário falaremos sobre qual dado ela apresenta.

Funções ou Tipo de Particionamento

Para particionar tabelas de um bancos de dados localizado no MySQL você precisa conhecer as partition functions, ou funções de particionamento. São estas funções que definem os critérios que serão utilizados para separar os dados de uma tabela de forma que estes sejam inseridos ou atualizados nas suas devidas partições.

Cada partição deverá receber um nome único, normalmente p0, p1 e assim por diante. Além disso, cada partição deverá abordar uma função de particionamento (normalmente funções Date & Time da biblioteca do MySQL). Funções de particionamento também são conhecidas como Tipo de Particionamento e devem seguir critérios para sua definição que veremos a seguir.

Particionamento com RANGE()

A função de particionamento RANGE() permite a divisão dos dados de uma tabela em diferentes partições a partir de um intervalo (em inglês, range) de valores a ser definido em uma coluna. Com isso, cada registro será direcionado para uma partição de acordo com o conteúdo da coluna escolhida para criação do particionamento.

Neste tipo de particionamento é obrigatório informar as partições e qual é o valor menor ao qual os dados serão armazenados, ou seja, se uma partição X possui intervalo de partição igual a 100 na coluna ABC, todos os registros da tabela que possuam conteúdo da coluna ABC inferior a 100 serão direcionados para a partição X em questão. Este tipo de particionamento somente aceitará valores inteiros, o que nos trará algumas complexidades ao lidar com informações do tipo data.

Nesse caso, para criar uma tabela com uma coluna dos tipos DATE, TIME, DATETIME particionada com a função de particionamento RANGE(), devemos tomar alguns cuidados. Caso você tente criar uma tabela particionada com RANGE() nessas condições, será retornado um erro conforme apresentado na ...

Quer ler esse conteúdo completo? Tenha acesso completo