Artigos Banco de Dados Utilizando o MySQL Partitioning
Por que eu devo ler este 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.

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 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.

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)

Ele 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.

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.

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.

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 Listagem 2 (essa forma seria possível com a versão 5.5 do MySQL, ainda em desenvolvimento).

Listagem 2. Criando uma tabela particionada com RANGE() de forma errada.

  mysql> CREATE TABLE tab_hash_wrong ( 
      ->    id int not null, 
      ->    date datetime 
      -> ) ENGINE = MyISAM PARTITION BY RANGE(date) ( 
      ->   PARTITION p0 VALUES LESS THAN('1980-01-01'), 
      ->   PARTITION p1 VALUES LESS THAN('1990-01-01') 
      -> ); 
  ERROR 1064 (42000): VALUES value must be of same type as partition function near '), 
    PARTITION p1 VALUES LESS THAN('1990-01-01') 
  )' at line 5

Uma forma de lidar com isso parte justamente do princípio de que partições do tipo RANGE() trabalham melhor com tipos inteiros, como as funções TO_DAYS() e YEAR(). Como sabemos, a função YEAR() retornará a parte do ano de um valor do tipo DATETIME, e a função TO_DAYS() retornará quantos dias se passaram a partir de uma data inicial. Na Listagem 3 criaremos uma tabela utilizando RANGE() recebendo a coluna a ser particionada como parâmetro da função YEAR(). Com isso, o MySQL irá automaticamente criar tais partições para registros entre os anos 1999 (p0) até 2009 (p10 – atente que a função criada diz que armazenará valores MENORES QUE [“ LESS THAN” ] 2010), e ele será o responsável pela alocação dos registros em tais partições, de forma que isso fique transparente ao usuário.

Listagem 3. Criando uma tabela particionada com RANGE(YEAR(column)).

  mysql> CREATE TABLE ponto ( 
      ->   ponto_id int not null, 
      ->   funcionario_id int not null, 
      ->   data timestamp default CURRENT_TIMESTAMP() 
      -> ) ENGINE = MyISAM PARTITION BY RANGE(YEAR(data)) ( 
      ->   PARTITION p0 VALUES LESS THAN(2000), 
      ->   PARTITION p1 VALUES LESS THAN(2001), 
      ->   PARTITION p2 VALUES LESS THAN(2002), 
      ->   PARTITION p3 VALUES LESS THAN(2003), 
      ->   PARTITION p4 VALUES LESS THAN(2004), 
      ->   PARTITION p5 VALUES LESS THAN(2005), 
      ->   PARTITION p6 VALUES LESS THAN(2006), 
      ->   PARTITION p7 VALUES LESS THAN(2007), 
      ->   PARTITION p8 VALUES LESS THAN(2008), 
      ->   PARTITION p9 VALUES LESS THAN(2009), 
      ->   PARTITION p10 VALUES LESS THAN(2010) 
      -> ); 
  Query OK, 0 rows affected (0,10 sec)

Para verificarmos o funcionamento do particionamento, precisamos executar alguns INSERTS, considerando que ao final daqueles que são exibidos na Listagem 4, teremos 1 linha para cada partição.

Listagem 4. Inserindo dados na tabela ponto, particionada com RANGE()

  mysql> INSERT INTO ponto VALUES (1,32,'2000-01-10 08:10:21'); 
  mysql> INSERT INTO ponto VALUES (2,28,'2001-01-10 08:08:13'); 
  mysql> INSERT INTO ponto VALUES (3,13,'2002-01-10 08:12:07'); 
  mysql> INSERT INTO ponto VALUES (4,21,'2003-01-08 08:14:19'); 
  mysql> INSERT INTO ponto VALUES (5,30,'2004-01-08 08:01:02'); 
  mysql> INSERT INTO ponto VALUES (6,8,'2005-01-02 08:35:21'); 
  mysql> INSERT INTO ponto VALUES (7,11,'2006-03-01 08:09:01'); 
  mysql> INSERT INTO ponto VALUES (8,19,'2007-01-18 08:01:11'); 
  mysql> INSERT INTO ponto VALUES (9,89,'2008-01-16 08:02:11'); 
  mysql> INSERT INTO ponto VALUES (10,81,'2009-01-16 08:12:03');

Podemos verificar o arranjo de linhas consultando a tabela PARTITIONS do banco de dados, que é o dicionário de dados INFORMATION_SCHEMA. A consulta está exibida na Listagem 5.

Listagem 5. Verificando quantas linhas temos em cada partição.

  mysql> SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH 
      -> FROM INFORMATION_SCHEMA.PARTITIONS 
      -> WHERE TABLE_NAME ='ponto' AND TABLE_SCHEMA ='test';                                                              
  +------------+----------------+------------+----------------+ 
  | TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | 
  +------------+----------------+------------+----------------+ 
  | ponto      | p0             |          0 |              0 | 
  | ponto      | p1             |          1 |             13 | 
  | ponto      | p2             |          1 |             13 | 
  | ponto      | p3             |          1 |             13 | 
  | ponto      | p4             |          1 |             13 | 
  | ponto      | p5             |          1 |             13 | 
  | ponto      | p6             |          1 |             13 | 
  | ponto      | p7             |          1 |             13 | 
  | ponto      | p8             |          1 |             13 | 
  | ponto      | p9             |          1 |             13 | 
  | ponto      | p10            |          1 |             13 | 
  +------------+----------------+------------+----------------+ 
  11 rows in set (0,00 sec)

Ao observarmos na Listagem 5 o resultado da recuperação os dados referentes às partições criadas com a função RANGE() na tabela ponto, podemos perceber que existe uma linha para cada partição em TABLE_ROWS, caso contrário um número 0 (zero) apareceria para aquela partição que estivesse sem registros.

Outra característica para análise é a coluna PARTITION_NAME, que exibe os nomes das partições que criamos explicitamente. Por fim, a coluna AVG_ROW_LENGTH exibe o tamanho de cada linha em bytes. Contudo, existe um problema grave relacionado ao design da tabela ponto. Perceba que se um novo registro for adicionado com a data 2010-01-11 08:05:04 teríamos um grande problema, pois não há partições para armazenar o ano de 2010. A maior data ou ano que poderemos inserir nessa tabela com esse design será 2009-12-31 23:59:59. Veja na Listagem 6 o que acontecerá quando um INSERT com ano 2010 chega ao partition engine.

Listagem 6. Inserindo um dado não suportado na tabela ponto.

  mysql> INSERT INTO ponto VALUES (11,42,'2010-01-11 08:05:04'); 
  ERROR 1526 (HY000): Table has no partition for value 2010

Para corrigir este problema, precisamos criar uma nova partição utilizando a declaração MAXVALUE que fará com que valores maiores que o maior valor de uma partição que foi atribuído explicitamente seja armazenado nessa nova partição. Para adicionarmos uma nova partição denominada p11 à tabela ponto, para corrigir o problema da tentativa de inserção de um registro com data de 2010 reportado na Listagem 6, precisamos realizar alterações em nossas partições conforme descrito na Listagem 7.

Listagem 7. Alterando a tabela ponto e adicionando uma nova partição com a declaração MAXVALUE.

  mysql> ALTER TABLE ponto ADD PARTITION 
      -> (PARTITION p11 VALUES LESS THAN MAXVALUE); 
  Query OK, 0 rows affected (0,31 sec) 
  Records: 0  Duplicates: 0  Warnings: 0 
   
  mysql> INSERT INTO ponto VALUES (11,42,'2010-01-11 08:05:04'); 
  Query OK, 1 rows affected (0,31 sec)

Particionamento com LIST()

O particionamento de tabelas com a utilização da função de particionamento LIST() é bem similar ao tipo RANGE(). Todos os dois tipos obrigam o DBA a informar como parâmetro os valores que serão usados como critério para possibilitar o armazenamento de dados em cada partição. O que difere as duas são dois pontos: (1) os valores ou parâmetros dentro da função de particionamento LIST() são explicitamente informados, podendo ser um ou mais separados por vírgula e (2) para este tipo de particionamento, não existe uma declaração "pega-tudo" ou MAXVALUE, como vimos com a função RANGE().

Como exemplo da criação de tabelas particionadas com LIST(), podemos criar uma tabela que será particionada com base nas vendas de uma loja. Nesse modelo, teremos uma relação de uma LOJA para várias VENDAS. Já que entramos em questões de regras de negócio, consideraremos que temos as lojas 1, 2 e 3 localizadas no estado de São Paulo, as lojas 4, 5 e 6 localizadas no estado de Minas Gerais e a loja 10 localizada no Distrito Federal. Assim, particionaremos a tabela VENDA com base na coluna loja_id. Veja como fica este exemplo na Listagem 8.

Listagem 8. Particionando a tabela VENDA com a função de particionamento LIST().

  mysql> CREATE TABLE vendas ( 
      -> venda_id int not null, 
      -> loja_id int not null, 
      -> preco_custo decimal(10,2), 
      -> preco_venda decimal(10,2) 
      -> ) ENGINE = MyISAM PARTITION BY LIST(loja_id) ( 
      ->   PARTITION pLojasSP VALUES IN(1,2,3), 
      ->   PARTITION pLojasMG VALUES IN(4,5,6), 
      ->   PARTITION pLojasRJ VALUES IN(7,8,9), 
      ->   PARTITION pLojasDF VALUES IN(10) 
      -> ); 
  Query OK, 0 rows affected (0,09 sec)

Olha que ponto interessante podemos verificar após criarmos a tabela da Listagem 8. Cada estado movimentará somente os seus dados, ou seja, a loja 4 movimentará dados somente da partição de Minas Gerais, enquanto a loja 1, somente das lojas de São Paulo e assim por diante. Para efeito de diminuição no tempo de resposta e melhoria de desempenho, é nítido pensar no ganho obtido pelo fato de ao invés de ler todos os dados (full table scan) ou ler todo o índice (full index scan), uma consulta vinda de um sistema de Minas Gerais ler somente os dados da partição pLojasMG. Nesse momento, seria muito válido cadastrar algumas vendas na tabela criada na Listagem 8 para observar estes ganhos, como mostra a Listagem 9.

Listagem 9. Inserindo dados na tabela de VENDA.

  mysql> INSERT INTO vendas VALUES (1,1,'2.00','2.75'); 
  mysql> INSERT INTO vendas VALUES (2,4,'6.55','7.80'); 
  mysql> INSERT INTO vendas VALUES (3,7,'0.15','1.00'); 
  mysql> INSERT INTO vendas VALUES (4,10,'2.89','3.99');

Como citado no início do artigo, neste momento poderemos utilizar o dicionário de dados do MySQL para então verificar como as partições estão sendo preenchidas, quantas linhas temos em cada uma delas e quais são os seus respectivos nomes. Veja na Listagem 10 o resultado da consulta à tabela PARTITIONS.

Listagem 10. Selecionando dados de partições relacionados com a tabela VENDAS.

  mysql> SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH 
      -> FROM INFORMATION_SCHEMA.PARTITIONS 
      -> WHERE TABLE_NAME ='vendas' 
      -> AND TABLE_SCHEMA ='test'; 
  +------------+----------------+------------+----------------+ 
  | TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | 
  +------------+----------------+------------+----------------+ 
  | vendas     | pLojasSP       |          1 |             19 | 
  | vendas     | pLojasMG       |          1 |             19 | 
  | vendas     | pLojasRJ       |          1 |             19 | 
  | vendas     | pLojasDF       |          1 |             19 | 
  +------------+----------------+------------+----------------+ 
  4 rows in set (0,00 sec)

Perceba que este tipo de particionamento simplifica o processo de exclusão de linhas ou expurgo de dados. Ao invés de enviarmos um DELETE com a intenção de excluir os dados da tabela para lojas de uma região que foi desativada, podemos simplesmente excluir uma partição. Por exemplo, imagine que fechamos as lojas em São Paulo, pois estas não estavam dando o retorno previsto. Neste novo cenário, bastaria um comando ALTER TABLE, como o que é visto na Listagem 11, para excluir todas as vendas desta região e diminuir o tamanho total da tabela (atenção: um backup anterior a este processo seria mais que necessário). Após a alteração, repetimos o SELECT da Listagem 10 somente para verificar quais partições fazem parte da tabela atualmente.

Listagem 11. Excluindo uma partição da tabela VENDAS.

  mysql> ALTER TABLE vendas DROP PARTITION pLojasSP; 
  Query OK, 0 rows affected (0,33 sec) 
  Records: 0  Duplicates: 0  Warnings: 0

Devo chamar sua atenção para problemas que podem acontecer no projeto de tabelas particionadas com a função de particionamento LIST(), pois caso uma nova loja seja considerada para a tabela de LOJAS, mesmo existindo o relacionamento entre as tabelas LOJA e VENDA como já enfatizamos, essa nova linha não será cadastrada, causando um erro por não haver partição para uma loja com identificador maior que 10. Nesse caso, é preciso ou associar esta nova loja a uma partição já existente ou criar uma nova partição para receber os dados de venda desta nova loja.

Particionamento com HASH()

A função de particionamento HASH() difere um pouco das funções abordadas anteriormente. Particionando uma tabela com HASH(), poderemos criar uma ou mais partições para que os dados sejam acomodados, o que acontece automaticamente. Ao criar partições com esta função, o DBA não precisará se preocupar em parametrizar ou mesmo explicitar as partições a serem criadas, simplesmente abordando que o particionamento será do tipo HASH() e a quantidade de partições após a declaração PARTITIONS. Caso a declaração PARTITIONS não seja citada, uma partição será criada por padrão. Veja um exemplo de uma tabela particionada com a função HASH() na Listagem 12.

Listagem 12. Criando uma tabela particionada com HASH().

  mysql> CREATE TABLE tb_part_hash ( 
      ->   col1 int, 
      ->   col2 int, 
      ->   col3 int 
      -> ) ENGINE = MyISAM PARTITION BY HASH(col1); 
  Query OK, 0 rows affected (0,11 sec)

A tabela criada na Listagem 12 terá uma única partição para armazenar os dados da coluna col1, o que em alguns casos seria interessante já que não teria nenhuma melhoria relacionada a desempenho. Mas caso seja uma tabela muito grande e o DBA deseje diminuir o tamanho do arquivo de dados, vale a pena a solução. Esta única partição por padrão será batizada como "p0", onde o número zero será incrementado até o número total de partições criadas. As informações recuperadas do INFORMATION_SCHEMA podem ser verificadas na Listagem 13.

Listagem 13. Recuperando informações sobre a tabela tb_hash_partition do INFORMATION_SCHEMA.PARTITIONS.

  mysql> SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH 
      -> FROM INFORMATION_SCHEMA.PARTITIONS 
      -> WHERE TABLE_NAME ='tb_part_hash' 
      -> AND TABLE_SCHEMA ='test'; 
  +--------------+----------------+------------+----------------+ 
  | TABLE_NAME   | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | 
  +--------------+----------------+------------+----------------+ 
  | tb_part_hash | p0             |          0 |              0 | 
  +--------------+----------------+------------+----------------+ 
  1 row in set (0,00 sec)

Para criar mais de uma partição, podemos proceder da maneira como já abordamos no início dessa explicação, adicionando ao final do comando CREATE TABLE a declaração PARTITIONS seguido pelo número de partições que você deseja criar. Criar mais de uma partição quando se utiliza a partition function HASH() somente se justificará quando combinarmos mais de uma coluna que juntas formam um só hash e estes valores são armazenados em partições diferentes.

Façamos o seguinte teste: com o comando ALTER TABLE podemos alterar a tabela tb_part_hash criada na Listagem 12 para que ela passe a ter 4 partições e que a partição HASH() considere 2 colunas ao mesmo tempo. Para criarmos mais partições com a função HASH(), utilizaremos a declaração PARTITIONS, já para "somar" uma coluna com a outra, utilizaremos o sinal "+" (sinal de soma). O código referente a esta operação está apresentado na Listagem 14.

Listagem 14. Alterando a tabela da Listagem 12 para considerar duas colunas no particionamento e quatro partições.

  mysql> ALTER TABLE tb_part_hash PARTITION BY HASH(col1 + col2) 
      -> PARTITIONS 4; 
  Query OK, 0 rows affected (0,08 sec) 
  Records: 0  Duplicates: 0  Warnings: 0

Nesse momento, a tabela tb_part_hash possui 4 partições HASH(), considerando o hash gerado na conjunção das colunas col1 + col2. É interessante perceber que se inserirmos um registro com os valores col1=1 e col2=1 e em seguida outro registro com os valores col1=1 e col2=2, teremos duas partições preenchidas automaticamente. Vemos isso através dos testes exibidos na Listagem 15.

Listagem 15. Testando o preenchimento automático das partições HASH().

  mysql> INSERT INTO tb_part_hash SET col1=1, col2=1, col3=1; 
  mysql> INSERT INTO tb_part_hash SET col1=1, col2=2, col3=1; 
  mysql> INSERT INTO tb_part_hash SET col1=1, col2=3, col3=1; 
   
  -- recuperando informação sobre a tabela tb_part_hash 
  -- INFORMATION_SCHEMA.PARTITIONS 
   
  mysql> SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH 
      -> FROM INFORMATION_SCHEMA.PARTITIONS 
      -> WHERE TABLE_NAME ='tb_part_hash' 
      -> AND TABLE_SCHEMA ='test'; 
  +--------------+----------------+------------+----------------+ 
  | TABLE_NAME   | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | 
  +--------------+----------------+------------+----------------+ 
  | tb_part_hash | p0             |          1 |             13 | 
  | tb_part_hash | p1             |          0 |              0 | 
  | tb_part_hash | p2             |          1 |             13 | 
  | tb_part_hash | p3             |          1 |             13 | 
  +--------------+----------------+------------+----------------+ 
  4 rows in set (0,00 sec)

Particionamento com KEY()

A mais simples função de particionamento de tabelas é a função KEY(). O controle deste tipo de particionamento é promovido por funções nativas e já conhecidas pela maioria dos profissionais que trabalham com o MySQL há algum tempo. Muito parecida com a função de particionamento HASH(), a função de particionamento KEY() ao invés de utilizar um hash para popular partições, utilizará a função MD5() para tabelas NBD Cluster e a função PASSWORD() para demais Storage Engines.

A partir do MySQL 5.1.6, KEY() receberá como parâmetro nenhuma ou muitas colunas, sendo que no caso do não fornecimento de um parâmetro a chave primária da tabela será automaticamente particionada, sendo esta a primeira opção. Caso não haja uma chave primária definida, uma coluna que tenha um índice UNIQUE será considerada. Caso não existam nem chave primária e nem índice unique, um erro igual ao que é exibido na Listagem 16 será apresentado.

Listagem 16. Criando uma tabela com o tipo de particionamento KEY(), omitindo a coluna, em uma tabela sem PK e UNIQUE INDEX.

  mysql> CREATE TABLE tb_part_key ( 
      ->    col1 int, 
      ->    col2 int, 
      ->    col3 int 
      -> ) ENGINE = MyISAM PARTITION BY KEY(); 
  ERROR 1488 (HY000): Field in list of fields for partition function not found in table

Vale salientar que uma coluna que possua um índice único somente será considerada pelo particionamento KEY() caso a mesma também seja NOT NULL, do contrário o erro exibido na Listagem 16 também será apresentado pelo mysqld. Assim como é feito quando usando a função HASH(), utilizamos a declaração PARTITIONS para criar mais de uma partição para acomodar os dados, como exibido na Listagem 17.

Listagem 17. Criando uma tabela particionada com KEY() e criando mais de uma partição.

  mysql> CREATE TABLE tb_part_key ( 
      ->    col1 int, 
      ->    col2 int, 
      ->    col3 int, 
      ->    PRIMARY KEY(col1) 
      -> ) ENGINE = MyISAM PARTITION BY KEY() 
      -> PARTITIONS 4; 
  Query OK, 0 rows affected (0,09 sec)

Com esse cenário, vale salientar que caso a chave primária de uma tabela que você deseja particionar conte com mais de uma coluna, ou seja, for uma chave composta, lembre-se que a criação também será automática. O que não poderá acontecer é particionar a tabela da Listagem 17 com a partition function KEY() recebendo como parâmetro col1 e col3. Todas as colunas que compõem a chave primária deverão ser informadas para que seja uma partição válida.

Para tabelas no MySQL do tipo NBD Cluster (ver Nota DevMan 2 no início do artigo), as tabelas são particionadas por padrão utilizando este tipo de particionamento, mesmo que não haja uma chave primária explícita. Internamente, toda tabela controlada por esse Storage Engine possui uma chave primária. Para tabelas controladas por qualquer outro storage engine e particionada com o tipo de particionamento KEY(), ao tentar excluir a chave primária com um comando ALTER TABLE, um erro:

ERROR 1466 (HY000): Field in list of fields for partition function not found in table

Será enviado. Esta mesma situação se realizada em uma tabela do tipo NDB Cluster, causará somente uma reorganização dos dados que agora passarão a utilizar uma chave primária implícita.

Subparticionamento

Um dos recursos mais interessantes providos atualmente pelo MySQL é poder particionar tabelas e posicionar dados e índices em outros discos, separando o acesso aos arquivos de dados dos arquivos de índices. Com isso, evitamos aquela sina de que o MySQL fica sempre limitado ao tamanho de arquivos em um diretório como o DATADIR (geralmente localizado em /var/lib/mysql). Além disso, podemos ter ganhos consideráveis no desempenho do banco de dados, adquirido após o re-projeto das tabelas do esquema lógico/físico do banco de dados. Isso é possível apenas com o recurso chamado de subparticionamento, conhecido ainda como subpartitioning ou composite partitioning. Ele nos permite dividir um particionamento criado em uma tabela.

É possível se trabalhar com o subparticionamento com os tipos RANGE(), LIST(), HASH() e KEY(), sendo utilizada a cláusula SUBPARTITION para especificar subpartições individuais em meio a um CREATE TABLE. Buscando melhorar o entendimento com a prática, na Listagem 18 é exibida uma tabela com particionamento e subparticionamento.

Listagem 18. Criando subparticionamentos.

  mysql> CREATE TABLE tb_part_sub (id INT, data DATE) 
      ->     PARTITION BY RANGE(YEAR(data)) 
      ->     SUBPARTITION BY HASH(TO_DAYS(data)) ( 
      ->         PARTITION p0 VALUES LESS THAN (2008) ( 
      ->             SUBPARTITION sp0, 
      ->             SUBPARTITION sp1 
      ->         ), 
      ->         PARTITION p1 VALUES LESS THAN (2009) ( 
      ->             SUBPARTITION sp2, 
      ->             SUBPARTITION sp3 
      ->         ), 
      ->         PARTITION p2 VALUES LESS THAN MAXVALUE ( 
      ->             SUBPARTITION sp4, 
      ->             SUBPARTITION sp5 
      ->         ) 
      ->     ); 
  Query OK, 0 rows affected (0,09 sec)

Observe na Listagem 18 que ao pensar em retrabalhar o projeto de uma tabela do seu modelo de banco de dados lógico/físico, transformando a mesma em uma tabela subparticionada, o DBA precisará estar atento quanto a informar o mesmo número de subpartições em cada partição. Caso você se decida por criar 3 partições, cada subpartições deverá contar com o mesmo número de subpartições, devidamente nomeadas, normalmente e convencionalmente com "s0" ou "sp0", onde o número zero será incrementado até o final de todas as subpartições internas de cada partição. Observe que na Listagem 18 o número de subpartições é 2 para todas as partições criadas, o que está correto.

Veja na Listagem 19 o que poderá acontecer quando o número de subpartições não bate. Neste exemplo, a partição p0 possui 2 subpartições, enquanto que a partição p1 possui apenas 1.

Listagem 19. Criando uma tabela de forma errada, sem o mesmo número de subpartições.

  mysql> CREATE TABLE it (d date) 
      -> ENGINE = MyISAM PARTITION BY RANGE(YEAR(d)) 
      -> SUBPARTITION BY HASH(TO_DAYS(d)) ( 
      ->    PARTITION p0 VALUES LESS THAN(2009) ( 
      ->          SUBPARTITION sp0, 
      ->          SUBPARTITION sp1 
      ->    ), 
      ->    PARTITION p1 VALUES LESS THAN(2010) ( 
      ->          SUBPARTITION sp2 
      ->    ) 
      -> ); 
   ERROR 1064 (42000): Wrong number of subpartitions defined, mismatch with previous setting near ') 
  )' at line 10

Uma grande vantagem de se trabalhar com subpartições com foco em tabelas grandes, que são alvos de grande quantidade de carga, é a de realmente poder distribuir melhor os arquivos de dados e de índices por vários discos através das cláusulas DATA_DIRECTORY e INDEX_DIRECTORY. Ambas as cláusulas definem o caminho absoluto do disco onde residirão os respectivos arquivos de dados e de índices, e estas somente poderão ser utilizadas com a adoção de subpartições. Veja na Listagem 20 como criar uma tabela com as cláusulas que direcionam os arquivos de dados e índices para outros discos.

Listagem 20. Criando uma tabela com subpartições, distribuindo os arquivos de dados e de índices e outros discos.

  CREATE TABLE tb_part_sub_disk (id INT, data DATE) 
      PARTITION BY RANGE(YEAR(data)) 
      SUBPARTITION BY HASH(TO_DAYS(data)) ( 
          PARTITION p0 VALUES LESS THAN (2009) ( 
              SUBPARTITION sp0 
                  DATA DIRECTORY = '/disk0/dados' 
                  INDEX DIRECTORY = '/disk0/indices', 
              SUBPARTITION sp1 
                  DATA DIRECTORY = '/disk1/dados' 
                  INDEX DIRECTORY = '/disk1/indices', 
          ), 
          PARTITION p1 VALUES LESS THAN (2010) ( 
              SUBPARTITION sp2 
                  DATA DIRECTORY = '/disk2/dados' 
                  INDEX DIRECTORY = '/disk2/indices', 
              SUBPARTITION sp3 
                  DATA DIRECTORY = '/disk3/dados' 
                  INDEX DIRECTORY = '/disk3/indices', 
          ), 
          PARTITION p2 VALUES LESS THAN MAXVALUE ( 
              SUBPARTITION sp4 
                  DATA DIRECTORY = '/disk4/dados' 
                  INDEX DIRECTORY = '/disk4/indices', 
              SUBPARTITION sp5 
                  DATA DIRECTORY = '/disk5/dados' 
                  INDEX DIRECTORY = '/disk5/indices', 
          ) 
      );

Conclusão

Finalizamos então o primeiro artigo dedicado à criação de tabelas particionadas, recurso este que foi disponibilizado a partir da versão 5.1 do MySQL e está presente nas versões 5.4, que está prevista para se tornar GA (Generaliza Avaiense) em breve, e 5.5, que evolui mais vagarosamente, mas promete grandes melhorias em seu desempenho. Explicamos quais são as funções de particionamento de tabelas que podem ser adotadas e os pontos mais importantes nos quais devemos nos atentar para não vacilar nos projetos.

No próximo artigo desta série vamos falar sobre as novidades relacionadas com o recurso de particionamento de tabelas em versões mais novas do MySQL, que continua a ser desenvolvido a cada dia. Falaremos sobre o gerenciamento de partições, reorganização das mesmas com as técnicas de merge e coalesce, como lidar com tabelas que tem uma chave primária definida em sua estrutura, como partições lidam com valores nulos e os recursos relacionados com a gestão do melhor desempenho de tabelas particionadas, que é Partition Pruning.

Parte 2

Este artigo trata principalmente dos recursos disponíveis no MySQL, mais precisamente localizados no engine partition, que nos permitem gerenciar as partições criadas em tabelas de nosso bancos de dados, adicionando, agregando, desmembrando e cuidando para que as partições ofereçam maior performance e/ou desempenho ao ambiente de bancos de dados.


Para que serve:

Aprenda a particionar tabelas ainda sem partições e adicionar novas partições a tabelas que já utilizam este recurso. Aprenda ainda a reorganizar tais particionamentos, efetuando operações denominadas merge partitions, coalesce partitions, trocar o tipo ou a partition function de uma tabela já particionada, remover partições de maneira eficiente, utilizar o partition prunning para melhorar o desempenho de determinadas consultas e conhecer as limitações e particularidades do particionamento de tabelas controladas pelos Storage Engines MyISAM e InnoDB.


Em que situação o tema é útil

O tema abordado neste artigo é principalmente útil quando se gerencia um banco de dados que possui muitos dados e estas tabelas são consideradas grandes. O objetivo final é obter melhor desempenho com a leitura de uma quantidade menor de dados. [/lead

No primeiro artigo que abriu esta série que dedicamos ao particionamento de tabelas usando o MySQL, mostramos ao leitor todas as possíveis funções de partição que estão disponíveis e podem ser aplicadas na criação de tabelas particionadas.

Vimos também a teoria relacionada e todos os benefícios aparentes que este recurso poderá trazer ao ambiente, bem como algumas armadilhas que podem ser evitadas quando trabalhamos com este recurso em tabelas localizadas em bancos de dados criados no MySQL.

Este artigo focará na administração e no gerenciamento dos recursos de particionamento de tabelas para que o administrador de bancos de dados (DBA) tenha competência e habilidade suficiente para lidar com possíveis problemas de desempenho ou mesmo traçar novas estratégias para utilização deste recurso. Com isso, será bastante interessante atentar às boas práticas que serão abordadas para que este recurso e respectivo trabalho a ser desenvolvido sejam bem aproveitados.

Particionamento de Tabelas no MySQL

Somente para recordar, para que você consiga realmente trabalhar com os recursos que estamos discutindo ou mesmo testar os códigos SQL que desenvolveremos neste artigo, será necessário verificar a versão do servidor de bancos de dados MySQL o qual você está utilizando. O recurso de particionamento de tabelas (MySQL Partitioning) está disponível em versões superiores à 5.1. Para checar qual é a versão do seu servidor de bancos de dados efetue o login através do terminal ou mysql client/monitor e digite a consulta apresentada na Listagem 1.

Listagem 1. Checando a versão do servidor de bancos de dados MySQL.


  mysql> SELECT VERSION() AS 'Versão do MySQL';
  +------------------+
  | Versão do MySQL  |
  +------------------+
  | 5.1.44-community |
  +------------------+
  1 row in set (0.00 sec)

Existem outras formas de verificação da versão do MySQL ou se o recurso de particionamento de tabelas está habilitada em seu servidor de bancos de dados, como o comando SHOW VARIABLES LIKE "%part%" que deverá retornar o valor YES caso o MySQL Partitioning esteja habilitado, ou mesmo o SHOW PLUGINS que deverá exibir ACTIVE na linha do partition engine.

Uma vez verificada a versão do servidor de bancos de dados MySQL, e tendo a certeza que esta é maior ou igual a 5.1.1, poderemos desenvolver todos os exemplos que serão abordados no decorrer deste artigo para explicar o gerenciamento de partições, visualização do plano de execução de consultas que lêem dados em partições e utilizar o recurso Partition Prunning. Vale lembrar que se você estiver utilizando a versão 5.5.1, você também contará com outros recursos que comentaremos até final deste artigo, pois esta versão traz muitas novidades interessantes relacionadas ao MySQL Partitioning.

Para a escrita deste artigo utilizaremos a nova release do MySQL (versão 5.1.44), como visto na Listagem 1. Para efetuar o download deste novo release, acesse o endereço www.mysql.com na guia downloads.

Gerenciando partições

Como salientamos no início deste artigo, é esperado que um DBA tenha plena condição de criar estruturas que dê condições ao banco de dados para efetuar trabalho melhor na escrita de dados ou mesmo na sua leitura.

Como vimos no primeiro artigo, podemos criar partições utilizando as partition functions HASH(), LIST(), RANGE() ou KEY(), e ainda poderemos utilizar do recurso de subparticionamento de tabelas, com o qual poderemos utilizar vários discos para armazenar datafiles e índices, sendo que você poderá armazenar datafile em um disco “A” e o arquivo de índice em um disco “B”.

Temos ainda uma boa fonte de informação sobre as partições criadas em tabelas de um banco de dados ao consultar o dicionário de dados do MySQL, o INFORMATION_SCHEMA, que conta com a tabela PARTITIONS que é responsável por armazenar todos os dados descritivos e estatísticos das partições. A Listagem 2 relembra o primeiro artigo com a criação de uma tabela particionada (chamada vendas) com a função RANGE() e em seguida faz uma consulta ao dicionário para recuperar os metadados da tabela criada.

Listagem 2. Criando uma tabela particionada e recuperando informação do dicionário de dados do MySQL – INFORMATION_SCHEMA.

  mysql> CREATE TABLE vendas (
      ->   vendas_id int not null,
      ->   loja_id int not null,
      ->   preco decimal(10,2) not null default '0.00',
      ->   venda_data timestamp default current_timestamp()
      -> ) ENGINE = InnoDB PARTITION BY RANGE(loja_id) (
      ->   PARTITION p1 VALUES LESS THAN (2),
      ->   PARTITION p2 VALUES LESS THAN (3),
      ->   PARTITION p3 VALUES LESS THAN (4),
      ->   PARTITION p4 VALUES LESS THAN (5),
      ->   PARTITION p5 VALUES LESS THAN (6),
      ->   PARTITION p6 VALUES LESS THAN (7),
      ->   PARTITION p7 VALUES LESS THAN (8)
      -> );
  Query OK, 0 rows affected (0.20 sec)
   
  mysql> SELECT TABLE_NAME, PARTITION_NAME, 
      -> PARTITION_DESCRIPTION, TABLE_ROWS
      -> FROM INFORMATION_SCHEMA.PARTITIONS
      -> WHERE TABLE_NAME ='vendas' AND 
      -> TABLE_SCHEMA ='test';
  +------------+----------------+-----------------------+------------+
  | TABLE_NAME | PARTITION_NAME | PARTITION_DESCRIPTION | TABLE_ROWS |
  +------------+----------------+-----------------------+------------+
  | vendas     | p1             | 2                     |          0 |
  | vendas     | p2             | 3                     |          0 |
  | vendas     | p3             | 4                     |          0 |
  | vendas     | p4             | 5                     |          0 |
  | vendas     | p5             | 6                     |          0 |
  | vendas     | p6             | 7                     |          0 |
  | vendas     | p7             | 8                     |          0 |
  +------------+----------------+-----------------------+------------+
  7 rows in set (0.01 sec)

Ao verificar o resultado apurado na Listagem 2, podemos perceber que ao criarmos uma tabela particionada através de qualquer função de particionamento disponível, todas as partições podem ser listadas através da tabela PARTITIONS, acompanhados de vários metadados como o nome da partição, nome da tabela, o nome do banco de dados a qual esta tabela pertence, quantidade de linhas em cada uma das partições e várias outras informações.

Como a nossa tabela vendas foi particionada utilizando a coluna loja_id, ou seja, separando o ID de cada loja em uma partição diferente, levamos em consideração que a rede lojista conta com uma matriz e mais seis lojas filiais, o que vemos na coluna PARTITION_DESCRIPTION, onde a partição p1 armazenará as vendas realizadas na loja de ID igual a 1, a partição p2 armazenará as vendas realizadas na loja cujo ID é igual a 2 e assim por diante. Explicando melhor, como utilizamos a declaração VALUES LESS THAN (2), no caso da partição p1, valores menores que (2) serão armazenados nesta partição.

Na Listagem 3 podemos perceber que após inserir um registro de venda para cada uma das lojas, cada partição apresentará uma linha armazenada em suas respectivas partições.

Listagem 3. Inserindo e verificando registros de venda para cada loja

 
  mysql> INSERT INTO vendas VALUES (1, 1, '10.55', NOW());
  mysql> INSERT INTO vendas VALUES (2, 2, '1.30', NOW());
  mysql> INSERT INTO vendas VALUES (3, 3, '7.72', NOW());
  mysql> INSERT INTO vendas VALUES (4, 4, '4.10', NOW());
  mysql> INSERT INTO vendas VALUES (5, 5, '0.10', NOW());
  mysql> INSERT INTO vendas VALUES (6, 6, '8.99', NOW());
  mysql> INSERT INTO vendas VALUES (7, 7, '10.99', NOW());
   
  mysql> SELECT TABLE_NAME, PARTITION_NAME, 
      -> PARTITION_DESCRIPTION, TABLE_ROWS
      -> FROM INFORMATION_SCHEMA.PARTITIONS
      -> WHERE TABLE_NAME ='vendas' AND 
      -> TABLE_SCHEMA ='test';
  +------------+----------------+-----------------------+------------+
  | TABLE_NAME | PARTITION_NAME | PARTITION_DESCRIPTION | TABLE_ROWS |
  +------------+----------------+-----------------------+------------+
  | vendas     | p1             | 2                     |          1 |
  | vendas     | p2             | 3                     |          1 |
  | vendas     | p3             | 4                     |          1 |
  | vendas     | p4             | 5                     |          1 |
  | vendas     | p5             | 6                     |          1 |
  | vendas     | p6             | 7                     |          1 |
  | vendas     | p7             | 8                     |          1 |
  +------------+----------------+-----------------------+------------+
  7 rows in set (0.01 sec)

Observe que na Listagem 3 inserimos um registro de venda para cada loja, sendo que foi considerado que a matriz seria a loja 1 e as demais em seqüência até a loja seis. Cada partição vai armazenar os identificadores de venda de cada loja, o que agilizará a consulta na hora de pesquisar dados com filtro por loja.

Mas, como a vida é uma caixinha de surpresas, nossa tabela tem dois problemas graves. Quem leu a primeira parte do artigo já percebeu, mas se você não leu, vamos abordar mais uma vez o problema de projeto que poderá ser acarretado por uma implementação assim.

Imagine se o pessoal do departamento administrativo da empresa faça planos para abrir mais uma loja. Neste caso, o novo cenário de sistemas e bancos de dados deverá contemplar mais uma loja (ex: ID = 8). Nesse momento, ao inserirmos uma venda para uma nova loja, veja o resultado desta operação na Listagem 4.

Listagem 4. Problemas no projeto de particionamento de tabelas


  mysql> INSERT INTO vendas VALUES (8, 8, '10.99', NOW());
  ERROR 1526 (HY000): Table has no partition for value 8

Para resolver o problema apontado na Listagem 4, basta sabermos gerenciar de forma dinâmica o projeto de particionamento de uma tabela. Sabendo exatamente qual o objetivo que se deseja atingir, conseguiremos nos guiar através da remontagem das partições de tabelas, sempre com o auxílio do comando ALTER TABLE.

Precisamos, então, alterar a tabela com problemas atribuindo mais uma partição configurada com a opção MAXVALUE, ou seja, a qualquer momento que uma nova loja for adicionada ou inaugurada, o mesmo sistema atenderá perfeitamente, sem uma nova intervenção do DBA. Para que isso seja efetuado, utilizamos um comando similar ao apresentado na Listagem 5.

Listagem 5. Alterando a tabela adicionado mais uma partição para armazenar os identificadores das novas lojas.

  mysql> ALTER TABLE vendas ADD PARTITION (
      ->     PARTITION p9 VALUES LESS THAN (MAXVALUE)
      -> );
  Query OK, 0 rows affected (0.00 sec)
  Records: 0  Duplicates: 0  Warnings: 0

Antes de qualquer coisa, vale salientar que o comando exibido na Listagem 5 quando executado com intenção de alterar uma tabela com muitos registros, demorará bem mais tempo se comparado ao tempo que obtivemos aqui.

Com isso, a tabela vendas já poderá receber os dados de vendas realizadas em novas lojas que utilizam o sistema de informação da empresa. A Listagem 6 mostra que novos INSERTs com identificadores de novas lojas serão armazenados sem o menor problema na nova partição que criamos para armazenar novos valores maiores que sete. Também, nesta listagem, é exibida a nova partição criada para a tabela vendas, agora filtrando pelo nome da partição.

Listagem 6. Inserindo vendas de novas lojas e verificando as linhas adicionadas à partição p9 criada com o comando ALTER TABLE.

  mysql> INSERT INTO vendas VALUES (8, 8, '17.90', NOW());
  mysql> INSERT INTO vendas VALUES (9, 9, '120.00', NOW());
  mysql> INSERT INTO vendas VALUES (10, 10, '0.90', NOW());
   
  mysql> SELECT TABLE_NAME, PARTITION_NAME,
      -> TABLE_ROWS, PARTITION_DESCRIPTION
      -> FROM INFORMATION_SCHEMA.PARTITIONS
      -> WHERE TABLE_NAME ='vendas' AND
      -> PARTITION_NAME ='p9';
  +------------+----------------+------------+-----------------------+
  | TABLE_NAME | PARTITION_NAME | TABLE_ROWS | PARTITION_DESCRIPTION |
  +------------+----------------+------------+-----------------------+
  | vendas     | p9             |          3 | MAXVALUE              |
  +------------+----------------+------------+-----------------------+
  1 row in set (0.02 sec)

Como visto, o problema foi resolvido, mas o trabalho do DBA não pára por aqui, pois logo a recuperação de dados referentes a uma nova loja estará comprometida.

Imagine uma nova loja que iniciou a utilização do sistema e começou também a se destacar como a loja que mais vende em toda a rede. Para atender tal demanda, o sistema é extremamente utilizado, sendo que muitos dados passam a ser armazenados. Com a adição de uma nova partição para armazenar identificadores de novas lojas, tais identificadores maiores que sete passaram a ser armazenados em uma mesma partição p9, tornando a busca de dados menos eficiente, pois ao invés de serem recuperados somente os dados contidos em uma partição condizente ao número de uma loja específica (como veremos à frente com o partition prunning), uma consulta terá que varrer toda a partição (full partition scan) testando linha a linha para verificar quais delas pertencem ao conjunto resultado final da consulta.

Precisamos corrigir este problema de performance. Através do comando ALTER TABLE ... REORGANIZE PARTITION poderemos reorganizar as partições, realocando cada identificador de cada nova loja em uma partição individual e sem perda de dados. Sendo assim, nosso objetivo agora é reorganizar nossa tabela, criando novas partições para as novas lojas 9, 10, 11, e uma nova partição MAXVALUE.

Veja na Listagem 7 como proceder para efetuar essa reorganização (vale a pena relembrar quais partições temos na tabela até o momento nas Listagens 3 e 6).

Listagem 7. Reorganizando os dados contidos de uma partição MAXVALUE em novas partições.

  mysql> ALTER TABLE vendas REORGANIZE PARTITION p9 INTO (
      ->     PARTITION new1 VALUES LESS THAN (9),
      ->     PARTITION new2 VALUES LESS THAN (10),
      ->     PARTITION new3 VALUES LESS THAN (11),
      ->     PARTITION new4 VALUES LESS THAN (MAXVALUE)
      -> );
  Query OK, 0 rows affected (0.01 sec)
  Records: 0  Duplicates: 0  Warnings: 0

O resultado obtido com a reorganização da tabela vendas é o menor tempo de resposta relacionado com consultas que filtram dados com base na coluna particionada e maior organização dos dados armazenados nas partições. Para o DBA, é preciso lembrá-lo que será necessário atentar-se aos planos de inauguração de novas lojas, pois com o tempo os dados deverão ser reorganizados novamente para que não haja queda no desempenho de recuperação de dados.

Imagine que agora o seu sistema evoluiu para um nível superior, ou seja, você tem um sistema online para uma loja virtual e deseja controlar as vendas dos afiliados. Nesse novo cenário, teremos um ID para cada afiliado espalhado por vários cantos do mundo, sendo que a cada venda deve ser indicada à loja principal pela afiliada. Você quer armazenar tais IDs em partições com um intervalo de dados maior para facilitar a exibição de um relatório na área de afiliados do site. Reunindo com a equipe, você decide que o menor impacto seria realmente aplicar o particionamento para armazenar mais lojas por partição (loja_id que nesse momento passa a ser os IDs dos afiliados). Ao visualizar a tabela e decidir-se sobre qual seria o trabalho, as modificações são aplicadas com um ALTER TABLE ... REORGANIZE PARTITION das partições p1, p2, p3, p4, p5, p6, p7, new1, new2, new3 e new4 em uma nova partição, denominada pMAX, que armazenará os IDs em uma nova partição configurada com MAXVALUE.

Após esta arrumação de todos os dados em uma mesma partição, faremos a divisão por intervalo de valores maiores que aqueles que utilizamos em momentos anteriores neste artigo. A Listagem 8 exibe como efetuar tal realocação dos dados (sem perda dos mesmos) para uma nova partição com a finalidade de arrumar a tabela para uma próxima etapa, onde faremos a nova divisão dos dados, particionando a coluna loja_id com intervalos maiores de dados.

Listagem 8. Armazenado os dados de várias partições em uma só – MERGE PARTITION.

  mysql> ALTER TABLE vendas REORGANIZE PARTITION
      -> p1,p2,p3,p4,p5,p6,p7,new1,new2,new3,new4
      -> INTO (PARTITION pMAX VALUES LESS THAN (MAXVALUE));
  Query OK, 10 rows affected (0.58 sec)
  Records: 10  Duplicates: 0  Warnings: 0
   
  mysql> SELECT PARTITION_NAME, PARTITION_DESCRIPTION, 
      -> TABLE_ROWS, PARTITION_EXPRESSION
      -> FROM INFORMATION_SCHEMA.PARTITIONS
      -> WHERE TABLE_NAME ='vendas' AND 
  -> TABLE_SCHEMA ='test';
  +----------------+-----------------------+------------+----------------------+
  | PARTITION_NAME | PARTITION_DESCRIPTION | TABLE_ROWS | PARTITION_EXPRESSION |
  +----------------+-----------------------+------------+----------------------+
  | pMAX           | MAXVALUE              |         10 | loja_id             |
  +----------------+-----------------------+------------+----------------------+
  10 rows in set (0.01 sec)

Observando o resultado apresentado pelo dicionário de dados do MySQL na Listagem 8, podemos perceber que todos os registros que temos até agora na tabela estão armazenados na única partição. Nessa operação, todas as partições foram excluídas e os dados foram armazenados em uma nova partição que foi criada com o nome de pMAX. Esta operação é denominada merge partition, ou seja, faz a união de várias partições em uma só. Lembre-se que os valores máximos devem ser sempre respeitados, caso contrário um erro com o SQLSTATE igual a HY000 será apontado.

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.

Já que o intervalo entre os dados que serão armazenados em partições de agora para frente será maior, podemos definir o seguinte planejamento:

  • Partição p0 armazenará dados até o id 1000;
  • Partição p1 armazenará dados até o id 2000;
  • Partição p2 armazenará dados até o id 3000;
  • Partição p3 armazenará dados até o id 4000;
  • Partição p4 armazenará dados até o id 5000;
  • Partição p5 armazenará dados até o id 6000;
  • Partição p6 armazenará dados até o id 7000;
  • Partição p7 armazenará dados até o id 8000;
  • Partição p8 armazenará dados até o id 9000;
  • Partição p9 armazenará dados MAXVALUE;

Com o planejamento fechado e bem interpretado, já temos tudo que precisamos para executar o trabalho e deixar a tabela vendas de forma que possamos armazenar melhor os dados de acordo com o número de afiliados que esperamos que o sistema atenda. Veja na Listagem 9 o comando ALTER TABLE ... REORGANIZE PARTITION que utilizaremos para fazer com que as novas partições sejam adicionadas à tabela e os sejam dados reorganizados.

Listagem 9. Adicionando novas partições à tabela vendas.

  mysql> ALTER TABLE vendas REORGANIZE PARTITION pMAX
      -> INTO (
      ->         PARTITION p0 VALUES LESS THAN (100),
      ->         PARTITION p1 VALUES LESS THAN (200),
      ->         PARTITION p2 VALUES LESS THAN (300),
      ->         PARTITION p3 VALUES LESS THAN (400),
      ->         PARTITION p4 VALUES LESS THAN (500),
      ->         PARTITION p5 VALUES LESS THAN (600),
      ->         PARTITION p6 VALUES LESS THAN (700),
      ->         PARTITION p7 VALUES LESS THAN (800),
      ->        PARTITION p8 VALUES LESS THAN (900),
      ->        PARTITION p9 VALUES LESS THAN (MAXVALUE)
      -> );
  Query OK, 10 rows affected (1.06 sec)
  Records: 10  Duplicates: 0  Warnings: 0

Após criarmos as novas partições, podemos verificar através do dicionário de dados a reorganização dos dados nas novas partições. Um alerta interessante é que se após algum tempo de manipulação de dados na tabela a gente repita o mesmo comando ALTER TABLE da Listagem 9, o efeito disso é a otimização das partições com a desfragmentação das mesmas e a liberação de espaços mal utilizados, o que trará mais velocidade no atendimento a consultas. A Listagem 10 exibe o momento atual com relação ao número de partições, número de linhas e outras informações complementares como a descrição das partições e o nome das mesmas.

Listagem 10. Momento atual da tabela vendas.

  mysql> SELECT PARTITION_NAME, PARTITION_DESCRIPTION,
      ->        TABLE_ROWS, PARTITION_EXPRESSION
      -> FROM INFORMATION_SCHEMA.PARTITIONS
      -> WHERE TABLE_NAME ='vendas' 
      -> AND TABLE_SCHEMA ='test';
  +----------------+-----------------------+------------+----------------------+
  | PARTITION_NAME | PARTITION_DESCRIPTION | TABLE_ROWS | PARTITION_EXPRESSION |
  +----------------+-----------------------+------------+----------------------+
  | p0             | 100                   |         10 | loja_id              |
  | p1             | 200                   |          0 | loja_id              |
  | p2             | 300                   |          0 | loja_id              |
  | p3             | 400                   |          0 | loja_id              |
  | p4             | 500                   |          0 | loja_id              |
  | p5             | 600                   |          0 | loja_id              |
  | p6             | 700                   |          0 | loja_id              |
  | p7             | 800                   |          0 | loja_id              |
  | p8             | 900                   |          0 | loja_id              |
  | p9             | MAXVALUE              |          0 | loja_id              |
  +----------------+-----------------------+------------+----------------------+
  10 rows in set (0.01 sec)

Com o resultado recuperado do dicionário na Listagem 10, percebemos que os dados foram associados à partição p0 por esta contemplar a condição de valores de loja_id menores que 100. Caso tenhamos nessa mesma tabela dados oriundos de novos afiliados, por exemplo, um afiliado que tenha um ID igual a 210, este valor será armazenado na partição p2 e assim por diante. Sempre teremos essa dinâmica. Com o tempo, talvez seja interessante observar qual a partição que apresenta mais linhas e voltar a particionar a mesma em duas ou mais partições.

Para criar um exemplo completo, vamos desenvolver um script simples dentro de um Stored Procedure para inserir uma certa quantidade de linhas na tabela vendas, preenchendo as partições para que passemos então a dividir uma partição em duas ou mais novas partições a fim de prover melhorias no desempenho do banco de dados. A Listagem 11 apresenta o procedimento e a execução do mesmo, inserindo na tabela 1000 linhas.

Listagem 11. Procedimento para inserir linhas, popular partições.

  mysql> DELIMITER //
  mysql> CREATE PROCEDURE sp_populaParticoes(v_num int)
      -> BEGIN
      ->     -- iniciamos a lógica do procedimento
      ->     SET @var = 1;
      ->     WHILE (v_num >= @var) DO
      ->         INSERT INTO vendas SET venda_id   =@var,
      ->                                loja_id   =@var+100,
      ->                                preco      ='0.00',
      ->                                venda_data =now();
      ->         SET @var = @var + 1;
      ->     END WHILE;
      -> END;
      -> //
  Query OK, 0 rows affected (0.00 sec)
   
  mysql> CALL sp_populaParticoes(1000)//
  Query OK, 0 rows affected (22.91 sec)

Após termos produzido uma pequena massa de dados para a tabela vendas, podemos verificar o dicionário de dados para então vermos qual partição será candidata a ser reparticionada, com atenção àquela que apresentar maior quantidade de linhas. Isso nos leva a crer que ao efetuar o split partition, estamos buscando armazenar cada vez menos dados para que quando uma consulta filtrar os dados, percorra menos dados e utilize a varredura de somente uma partição e não uma partição que contenha muitos dados. A Listagem 12 exibe como estão as partições da tabela vendas nesse momento.

Listagem 12. Exibindo os metadados da tabela vendas.

  mysql> SELECT PARTITION_NAME, TABLE_ROWS,
      ->        TABLE_NAME, PARTITION_DESCRIPTION
      -> FROM INFORMATION_SCHEMA.PARTITIONS
      -> WHERE TABLE_NAME ='vendas' 
      -> AND TABLE_SCHEMA ='test';
  +----------------+------------+------------+-----------------------+
  | PARTITION_NAME | TABLE_ROWS | TABLE_NAME | PARTITION_DESCRIPTION |
  +----------------+------------+------------+-----------------------+
  | p0             |         10 | vendas     | 100                   |
  | p1             |         99 | vendas     | 200                   |
  | p2             |        100 | vendas     | 300                   |
  | p3             |        100 | vendas     | 400                   |
  | p4             |        100 | vendas     | 500                   |
  | p5             |        100 | vendas     | 600                   |
  | p6             |        100 | vendas     | 700                   |
  | p7             |        100 | vendas     | 800                   |
  | p8             |        100 | vendas     | 900                   |
  | p9             |        201 | vendas     | MAXVALUE              |
  +----------------+------------+------------+-----------------------+
  10 rows in set (0.01 sec)

Percebemos então que a partição que tem mais linhas é p9, candidata ao reparticionamento ou split partition por apresentar mais dados que as outras. Como inserimos 1000 linhas na tabela, já era de se esperar que cada partição contasse com 100 linhas, mas um detalhe deve ser salientado: no script, ao inserir as linhas, a coluna loja_id recebeu @var + 100, o que fez com que o registro fosse inserido em uma partição acima da partição que deveria ser inserido originalmente, pois as partições são dividas a cada grupo de 100 (ex: de 1 a 100, de 101 a 200, e assim por diante). Sendo assim, um registro, por exemplo, que teria originalmente a coluna loja_id com o valor 1 teria seu valor incrementado em 100 unidades e passaria a ter o valor 101, de forma que seria encaixado na partição p1. Esta operação não permite adicionar nenhum novo registro na partição p0, e mantém esta partição apenas com os registros que estavam nessa partição originalmente, antes desta nova divisão de partições (ver Listagem 10, que indica que 10 registros já estão inseridos na partição p0).

Para que as consultas tenham melhor performance, nesse momento precisamos encarar o particionamento de uma partição existente para que os valores para a coluna loja_id sejam armazenados em partições que atendam às condições dos dados. Para tal, primeiramente precisamos analisar que se estamos particionando tal coluna para armazenar dados em intervalo de 100 em 100 e a partição p9 contém 200 linhas cujos valores de loja_id são superiores a 900, então precisamos adicionar três novas partições: uma nova partição p9 que armazenará valores menores que 1000, outra partição denominada p10 que armazenará dados menores que 1100 e por final uma nova partição configurada para armazenar valores MAXVALUE maiores que 1100 denominada p11. Tal alteração pode ser verificada na Listagem 13.

Listagem 13. Dividindo a partição com o maior número de linhas em busca de melhoria de performance para consultas que filtram dados pela coluna loja_id.

  mysql> ALTER TABLE vendas REORGANIZE PARTITION p9
      -> INTO (
      ->        PARTITION p9 VALUES LESS THAN (1000),
      ->        PARTITION p10 VALUES LESS THAN (1100),
      ->        PARTITION p11 VALUES LESS THAN (MAXVALUE)
      -> );
  Query OK, 201 rows affected (0.76 sec)
  Records: 201  Duplicates: 0  Warnings: 0

É muito importante que você mantenha sempre uma partição configurada com MAXVALUE para evitar problemas associados à impossibilidade de inserção de novos dados em sua base. Em caso de dúvidas, volte ao início do artigo para relembrar o porquê ou leia o primeiro artigo onde falamos de problemas relacionados com o particionamento e design de tabelas de bancos de dados.

Por fim, tabelas que são particionadas com as partition function KEY() e HASH() não podem sofrer alterações merge em suas partições com partições RANGE() ou LIST(). Para resolver este problema, temos a declaração ALTER TABLE ... COALESCE PARTITION que trabalhará igual ao REORGANIZE PARTITION em uma operação em que o DBA necessita juntar duas ou mais partições em uma só (merge partition). Para exemplificar rapidamente o que faz o COALESCE, na Listagem 14 criamos uma tabela de nome sqlmagazine, particionada com a função de particionamento HASH(), onde criamos três partições para a coluna sqlmagazine_id e inserimos cinco valores inteiros para a coluna com um único comando de INSERT.

Listagem 14. Criando uma tabela sqlmagazine particionada com HASH().

  mysql> CREATE TABLE sqlmagazine (
      ->    sqlmagazine_id int not null
      -> ) ENGINE = MyISAM PARTITION BY HASH(sqlmagazine_id)
      -> PARTITIONS 3;
  Query OK, 0 rows affected (0.08 sec)
   
  mysql> INSERT INTO sqlmagazine VALUES (1), (2), (3), (4), (5);
  Query OK, 5 rows affected (0.04 sec)
  Records: 5  Duplicates: 0  Warnings: 0

Observe que a partição p0 conterá uma linha de acordo com o algoritmo interno que monta o hash dos valores contidos em cada partição, e as outras duas partições conterão duas linhas cada uma, como exibido na Listagem 15.

Listagem 15. Recuperando dados sobre a tabela sqlmagazine de acordo as linhas inseridas na Listagem 14.

  mysql> SELECT PARTITION_NAME, TABLE_ROWS,
      ->        TABLE_NAME, PARTITION_DESCRIPTION
      -> FROM INFORMATION_SCHEMA.PARTITIONS
      -> WHERE TABLE_NAME ='sqlmagazine' AND TABLE_SCHEMA ='test';
  +----------------+------------+------------+-----------------------+
  | PARTITION_NAME | TABLE_ROWS | TABLE_NAME | PARTITION_DESCRIPTION |
  +----------------+------------+------------+-----------------------+
  | p0             |          1 | sqlmagazine| NULL                  |
  | p1             |          2 | sqlmagazine| NULL                  |
  | p2             |          2 | sqlmagazine| NULL                  |
  +----------------+------------+------------+-----------------------+
  3 rows in set (0.01 sec)

A operação iniciada após enviarmos ao servidor de bancos de dados MySQL o comando COALESCE é a de excluir uma das partições apontadas na sintaxe na ordem como elas aparecem no dicionário de dados, a iniciar por 1 que equivale a partição p0 e assim por diante. No exemplo que exibiremos na Listagem 16, faremos esta operação com a partição 1 (p0) e as linhas que estão atualmente contidas nesta partição serão deslocadas para a partição p1.

Listagem 16. COALESCE na partição p0 da tabela sqlmagazine e o deslocamento dos valores para outra partição.

  mysql> ALTER TABLE sqlmagazine COALESCE PARTITION 1;
  Query OK, 5 rows affected (0.37 sec)
  Records: 5  Duplicates: 0  Warnings: 0
   
  mysql> SELECT PARTITION_NAME, TABLE_ROWS,
      ->        TABLE_NAME, PARTITION_DESCRIPTION
      -> FROM INFORMATION_SCHEMA.PARTITIONS
      -> WHERE TABLE_NAME ='sqlmagazine' 
      -> AND TABLE_SCHEMA ='test';
  +----------------+------------+------------+-----------------------+
  | PARTITION_NAME | TABLE_ROWS | TABLE_NAME | PARTITION_DESCRIPTION |
  +----------------+------------+------------+-----------------------+
  | p0             |          2 | sqlmagazine| NULL                  |
  | p1             |          3 | sqlmagazine| NULL                  |
  +----------------+------------+------------+-----------------------+
  2 rows in set (0.00 sec)

Você ainda poderá redefinir completamente o particionamento de uma tabela particionada, por exemplo, com a partition function HASH() para LIST(), assim como excluir uma partição, como fazemos na Listagem 17.

Listagem 17. Redefinindo o tipo de particionamento da tabela sqlmagazine de HASH() para LIST().

  mysql> ALTER TABLE sqlmagazine PARTITION BY LIST(sqlmagazine_id) (
      ->   PARTITION p0 VALUES IN(1),
      ->   PARTITION p1 VALUES IN(2),
      ->   PARTITION p2 VALUES IN(3),
      ->   PARTITION p3 VALUES IN(4),
      ->   PARTITION p4 VALUES IN(5)
      -> );
  Query OK, 5 rows affected (0.09 sec)
  Records: 5  Duplicates: 0  Warnings: 0
   
  mysql> ALTER TABLE sqlmagazine DROP PARTITION p4;
  Query OK, 0 rows affected (0.31 sec)
  Records: 0  Duplicates: 0  Warnings: 0

O DBA deverá se atentar ao excluir uma partição, pois todos os valores da coluna que estiverem armazenados na mesma serão excluídos mais rápido que uma consulta DELETE. Ao trocar o tipo de particionamento de uma tabela, nenhum dos dados serão excluídos, muito menos em operações merge, split e COALESCE.

Partition Prunning

Neste momento, vamos voltar nossa atenção à melhoria de desempenho relacionada a consultas SELECT. Para isso, continuaremos a utilizar nosso exemplo do sistema de controle de afiliados, e nosso objetivo é fornecer relatórios de forma mais rápida. Podemos observar que conseguimos com certa facilidade diminuir o número de linhas em partições da tabela vendas para que o otimizador de consultas leia o menor número de linhas possível. É justamente nesse ponto que entra o Partition Prunning!

Este mecanismo foi criado para que uma consulta responda ainda em menos tempo, permitindo que ajustemos as mesmas para que, em conjunto com a estrutura de partições criadas, leia somente as linhas que satisfazem à condição imposta para a recuperação de dados, mirando nas partições em que estas linhas se encontram.

Para isso, precisaremos alterar a estrutura da tabela vendas que utilizamos na maior parte dos exemplos deste artigo para que a mesma trabalhe com uma PRIMARY KEY nas colunas venda_id que passará a ser do tipo auto_increment e na coluna loja_id que continuará recebendo o ID da loja ou afiliado que efetuou a venda.

Como não podemos simplesmente tornar os campos PK pelo fato de a coluna loja_id estar particionada e todos os campos participantes da chave composta terem que participar da definição de particionamento, não nos resta outra opção a não ser criar uma nova tabela e efetuar um INSERT ... SELECT ou efetuar um backup utilizando o comando mysqldump. A Listagem 18 mostra as duas opções. Para melhor segurança, proceda com as duas opções.

O comando MySQLDUMP

Este é um utilitário para descarregar um banco de dados ou uma coleção de bancos de dados para backup ou transferência para outro servidor de banco de dados (não necessariamente um servidor MySQL). A descarga irá conter instruções SQL para criar a tabela e/ou popular a tabela.

Se a ideia é backup do servidor, deve ser considerada a utilização do mysqlhotcopy.

A sua sintaxe é:

shell> mysqldump [OPÇÕES] banco_de_dados [tabelas]

OR mysqldump [OPÇÕES] --databases [OPÇÕES] BD1 [BD2 BD3...]

OR mysqldump [OPÇÕES] --all-databases [OPÇÕES]

Se você não fornecer nenhuma tabela ou utilizar oparâmetro --databases ou --all-databases, todo(s) o(s) banco(s) de dados será(ão) descarregado(s).

Você pode obter uma lista das opções que sua versão do mysqldump suporta executandoo comando mysqldump --help.

Perceba que se você executar o comando mysqldump sem a opção--quick ou --opt, o mysqldump irá carregar todo o conjunto do resultado na memória antes de descarregar o resultado. Isto provavelmente será um problema se você está descarregando um banco de dados grande.

Note que se você estiver utilizando uma cópia nova do programa mysqldump e se você for fazer uma descarga que será lida em um servidor MySQL muito antigo, você não deve utilizar as opções --opt ou -e.

O comando mysqldump suporta as seguintes opções:

  • --add-locks: adicionaLOCK TABLESantes deUNLOCK TABLEdepois de cada descarga de tabelas (para obter inserções mais rápidas no MySQL)
  • --add-drop-table: adiciona umdrop tableantes de cada instrução create.
  • -A, --all-databases: descarrega todos os bancos de dados. Isto irá ser o mesmo que --databases com todos os bancos de dados selecionados.
  • -a, --all: inclui todas as opções do create específicas do MySQL.
  • -c, --complete-insert: utiliza instruções de insert completas (com nomes de colunas).
  • -C, --compress: compacta todas as informações entre o cliente e o servidor se ambos suportarem a compactação.
  • -B, --databases: para descarregar diversos bancos de dados.
  • -e, --extended-insert: utiliza a nova sintaxe multilinhasINSERT (fornece instruções de inserção mais compactas e mais rápidas).
  • --help: exibe uma mensagem de ajuda e sai.
  • -t, --no-create-info: não grava informações de criação de tabelas (a instruçãoCREATE TABLE).
  • -d, --no-data: não grava nenhuma informação de registros para a tabela. Isto é muito útil se você deseja apenas um dump da estrutura da tabela.
  • -r, --result-file=... : direciona a saída para um determinado arquivo. Esta opção deve ser usada no MSDOS porque previne a conversão de nova linha '\n' para '\n\r' (nova linha + retorno de carro).
  • --tables: sobrepõe a opção --databases (-B).
  • -u user_name, --user=user_name: o nome do usuário do MySQL para usar ao conectar ao servidor. O valor padrão é seu nome de usuário no Unix.

O uso mais comum docomando mysqldump é provavelmente para fazer backups de bancos de dados inteiros, como no exemplo abaixo, onde o conteúdo do banco de dados com o nome banco_dados é armazenado no arquivo texto arquivo-backup.sql.

mysqldump
--opt banco_dados > arquivo-backup.sql

Para restaurar os dados copiados através do comando mysqldump, precisamos usar o comando mysql como no exemplo a seguir:

mysql
banco_dados < arquivo-backup.sql
Listagem 18. Efetuando o backup da tabela para alterarmos sua estrutura e prepará-la da melhor forma para utilizarmos o partition prunning.

  -- procedimento de backup com criação de tabela
  mysql> CREATE TABLE vendas_copia LIKE vendas;
  Query OK, 0 rows affected (0.37 sec)
   
  mysql> INSERT INTO vendas_copia SELECT * FROM vendas;
  Query OK, 1010 rows affected (0.10 sec)
  Records: 1010  Duplicates: 0  Warnings: 0
   
  -- procedimento de backup com mysqldump
  C:\Users\Wagner Bianchi>mysqldump -u root -p12345 test vendas > vendas.sql

Feito isso, já de olho nas alterações que devemos proceder na tabela, primeiramente excluiremos as partições existentes. Em seguida, adicionaremos a propriedade auto_increment à coluna loja_id e depois criaremos a chave primária (PK) com as colunas venda_id e loja_id. Um cuidado que você deverá considerar é verificar os relacionamentos ou a propriedade CASCADE da chave estrangeira de loja_id, no caso da utilização do InnoDB ou qualquer outro Storage Engine com suporte a integridade referencial. A Listagem 19 apresenta todos os processos que deverão ser realizados.

Listagem 19. Processos de adaptação da tabela vendas.

  mysql> ALTER TABLE vendas REMOVE PARTITIONING;
  Query OK, 1010 rows affected (0.07 sec)
  Records: 0  Duplicates: 0  Warnings: 0
   
  mysql> ALTER TABLE vendas MODIFY venda_id INT NOT NULL;
  Query OK, 1010 rows affected (0.02 sec)
  Records: 0  Duplicates: 0  Warnings: 0
   
  mysql> ALTER TABLE vendas ADD PRIMARY KEY (venda_id, loja_id);
  Query OK, 1010 rows affected (0.05 sec)
  Records: 0  Duplicates: 0  Warnings: 0
   
   
  mysql> ALTER TABLE vendas PARTITION BY RANGE(venda_id + loja_id) (
      ->    PARTITION p0 VALUES LESS THAN (100),
      ->    PARTITION p1 VALUES LESS THAN (200),
      ->    PARTITION p2 VALUES LESS THAN (300),
      ->    PARTITION p3 VALUES LESS THAN (400),
      ->    PARTITION p4 VALUES LESS THAN (500),
      ->    PARTITION p5 VALUES LESS THAN (600),
      ->    PARTITION p6 VALUES LESS THAN (700),
      ->    PARTITION p7 VALUES LESS THAN (800),
      ->    PARTITION p8 VALUES LESS THAN (900),
      ->    PARTITION p9 VALUES LESS THAN (1000),
      ->    PARTITION p10 VALUES LESS THAN (1100),
      ->    PARTITION p11 VALUES LESS THAN (MAXVALUE)
      -> );
  Query OK, 1010 rows affected (0.09 sec)
  Records: 1010  Duplicates: 0  Warnings: 0

O comando REMOVE PARTITIONING permitirá que a tabela continue intacta, sem que ela tenha partições e com os seus dados sem qualquer alteração. Mesmo assim, ainda é preferível efetuar um backup antes de realizar esta operação.

Após as alterações propostas na Listagem 19, podemos visualizar quantas linhas estão sendo lidas para uma consulta particular simples (156 linhas), e quais são as partições utilizadas pelo otimizador para recuperar as linhas (todas as 12 partições). Veja a Listagem 20.

Listagem 20. Verificando quantas linhas e quais partições são utilizadas pelo MySQL para resolver uma consulta particular.

  mysql> EXPLAIN PARTITIONS SELECT * FROM vendas 
      -> WHERE venda_id > 100 AND venda_id < 200\G
  *************************** 1. row ***************************
             id: 1
    select_type: SIMPLE
          table: vendas
     partitions: p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11
           type: range
  possible_keys: PRIMARY
            key: PRIMARY
        key_len: 4
            ref: NULL
           rows: 156
          Extra: Using where
  1 row in set (0.00 sec)

A Listagem 20 nos mostra como não proceder para particionar uma tabela com a função RANGE() como foi feito na Listagem 19, onde utilizamos dois campos para definir o critério de particionamento. Observe no resultado da Listagem 20 que ao realizar um SELECT dos registros com venda_id entre 101 e 199, que por definição só poderiam estar na partição p1, todas as partições e um total de 156 linhas estão sendo acessadas pelo otimizador para retornar o resultado da busca, o que torna a busca mais lenta. Na verdade, a melhor opção seria o particionamento através de uma só coluna, e é o que vamos realizar agora com a coluna venda_id, conforme exibido na Listagem 21.

Listagem 21. Novas alterações na tabela vendas.

  mysql> ALTER TABLE vendas REMOVE PARTITIONING;
  Query OK, 1000 rows affected (0.76 sec)
  Records: 1000  Duplicates: 0  Warnings: 0
   
  mysql> ALTER TABLE vendas MODIFY venda_id INT NOT NULL;
  Query OK, 0 rows affected (0.05 sec)
  Records: 0  Duplicates: 0  Warnings: 0
   
  mysql> ALTER TABLE vendas DROP PRIMARY KEY;
  Query OK, 1000 rows affected (0.29 sec)
  Records: 1000  Duplicates: 0  Warnings: 0
   
  mysql> ALTER TABLE vendas MODIFY venda_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY;
  Query OK, 1000 rows affected (0.24 sec)
  Records: 1000  Duplicates: 0  Warnings: 0
   
  mysql> ALTER TABLE vendas PARTITION BY RANGE(venda_id) (
      ->    PARTITION p0 VALUES LESS THAN (100),
      ->    PARTITION p1 VALUES LESS THAN (200),
      ->    PARTITION p2 VALUES LESS THAN (300),
      ->    PARTITION p3 VALUES LESS THAN (400),
      ->    PARTITION p4 VALUES LESS THAN (500),
      ->    PARTITION p5 VALUES LESS THAN (600),
      ->    PARTITION p6 VALUES LESS THAN (700),
      ->    PARTITION p7 VALUES LESS THAN (800),
      ->    PARTITION p8 VALUES LESS THAN (900),
      ->    PARTITION p9 VALUES LESS THAN (1000),
      ->    PARTITION p10 VALUES LESS THAN (1100),
      ->    PARTITION p11 VALUES LESS THAN (MAXVALUE)
      -> );
  Query OK, 1000 rows affected (0.74 sec)
  Records: 1000  Duplicates: 0  Warnings: 0

Para finalizarmos a explicação do partitioning prunning, vamos repetir a mesma consulta que foi exibida na Listagem 20 e comparar os resultados. Veja na Listagem 22 quais partições estão agora sendo utilizadas (apenas a partição P1) e a quantidade de linhas acessadas pelo otimizador (50 linhas).

Listagem 22. O resultado da aplicação de uma bom design de partições em tabelas do banco de dados.

  mysql> EXPLAIN PARTITIONS SELECT * FROM vendas 
      -> WHERE venda_id > 100 AND venda_id < 200\G
  *************************** 1. row ***************************
             id: 1
    select_type: SIMPLE
          table: vendas
     partitions: p1
           type: range
  possible_keys: PRIMARY
            key: PRIMARY
        key_len: 4
            ref: NULL
           rows: 50
          Extra: Using where
  1 row in set (0.00 sec)

Fazendo um comparativo entre os cenários, vemos que houve uma melhora de pelo menos 67% em relação à quantidade de linhas que a consulta de exemplo precisará ler para recuperar os dados (de 156 para 50 – conforme a Figura 1), sendo que a linhas serão lidas somente em uma partição, no caso p1. Veja que uma vez bem aplicadas as teorias do MySQL Partitioning, o DBA conseguirá fazer com que as consultas a bancos de dados do dia-a-dia de uma empresa sejam atendidas mais rapidamente, no caso apresentado, lendo somente os dados que interessa para uma consulta. Perceba que se modificarmos o intervalo de dados que procuramos, uma nova partição será adicionada ou retirada do escopo de busca de dados de uma consulta.

Comparação de linhas consultadas em partições mal e bem projetadas
Figura 1. Comparação de linhas consultadas em partições mal e bem projetadas.

O gráfico apresentado mostra a quantidade de linhas lidas, num comparativo entre a leitura de dados em uma tabela mal particionada e a mesma operação em uma tabela particionada corretamente. Os ganhos são relativos à diminuição do tempo de recuperação dos dados em virtude de ler menos linhas.

Conclusão

Neste artigo fechamos um dos assuntos que tem movimentado muito toda a comunidade de usuários do MySQL, o MySQL Partitioning. Relembrando, os dois artigos que foram escritos para cobrir os recursos relacionados à criação e gerenciamento desta nova feature. Vimos ainda como prover melhorias no desempenho de consultas utilizando tal recurso com muita prática, acompanhada da teoria necessária para seu entendimento.

O MySQL 5.5 traz muitas melhorias em relação aos tipos de particionamento existentes, mas ainda estão em versão beta. São eles: o RANGE COLUMNS(), que dá mais flexibilidade em trabalhar com tipos de dados DATE suportando a função UNIX_TIMESTMP() para retornar um valor inteiro, e LIST_COLUMNS(), que possibilita informar valores do tipo string. Como estamos falando de uma versão ainda em beta, mais à frente prometo aos leitores trazer mais novidades quanto aos novos recursos do MySQL Partitioning que se encontra em pleno desenvolvimento e promete muitas novidades. Fiquem ligados e até a próxima!