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

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!

Confira outros conte�dos:

SQL SUM: somando os valores de uma...

SQL: INNER JOIN

SQL: Introdu��o ao Where

Por Wagner Em 2010

Black November

Desconto exclusivo para as primeiras 200 matrículas!

Pagamento integral

12x no cartão

De: R$ 69,00

Por: R$ 59,00

Total: R$ 708,00

Garanta o desconto

Formação FullStack Completa
Carreira Front-end I e II, Algoritmo e Javascript, Back-end e Mobile
+10.000 exercícios gamificados
+50 projetos reais
Comunidade com + 200 mil alunos
Estude pelo Aplicativo (Android e iOS)
Suporte online
12 meses de acesso

Pagamento facilitado

Cobrado mensalmente no cartão

De: R$ 79,00

Por: R$ 64,00 /mês

Tempo mínimo: 12 messes

Garanta o desconto

Formação FullStack Completa
Carreira Front-end I e II, Algoritmo e Javascript, Back-end e Mobile
+10.000 exercícios gamificados
+50 projetos reais
Comunidade com + 200 mil alunos
Estude pelo Aplicativo (Android e iOS)
Suporte online
Não compromete o limite do seu cartão

<Perguntas frequentes>

Carreira

Metodologia

Assinatura e Pagamentos

Cadastro

Nossos casos de sucesso

Leonardo Carlos

Eu sabia pouqu�ssimas coisas de programa��o antes de come�ar a estudar com voc�s, fui me especializando em v�rias �reas e ferramentas que tinham na plataforma, e com essa bagagem consegui um est�gio logo no in�cio do meu primeiro per�odo na faculdade.

Lucas Rodrigues

Estudo aqui na Dev desde o meio do ano passado! Nesse per�odo a Dev me ajudou a crescer muito aqui no trampo.
Fui o primeiro desenvolvedor contratado pela minha empresa. Hoje eu lidero um time de desenvolvimento!
Minha meta � continuar estudando e praticando para ser um Full-Stack Dev!

Her�clito J�nior

Economizei 3 meses para assinar a plataforma e sendo sincero valeu muito a pena, pois a plataforma � bem intuitiva e muuuuito did�tica a metodologia de ensino. Sinto que estou EVOLUINDO a cada dia. Muito obrigado!

Julio Cablen

Nossa! Plataforma maravilhosa. To amando o curso de desenvolvimento front-end, tinha coisas que eu ainda n�o tinha visto. A did�tica � do jeito que qualquer pessoa consegue aprender. S�rio, to apaixonado, adorando demais.

Joelberth Sena

Adquiri o curso de voc�s e logo percebi que s�o os melhores do Brasil. � um passo a passo incr�vel. S� n�o aprende quem n�o quer. Foi o melhor investimento da minha vida!

Felipe Nunes

Foi um dos melhores investimentos que j� fiz na vida e tenho aprendido bastante com a plataforma. Voc�s est�o fazendo parte da minha jornada nesse mundo da programa��o, irei assinar meu contrato como programador gra�as a plataforma.

Wanderson Oliveira

Comprei a assinatura tem uma semana, aprendi mais do que 4 meses estudando outros cursos. Exerc�cios pr�ticos que n�o tem como n�o aprender, est�o de parab�ns!

Jos� Lucas

Obrigado DevMedia, nunca presenciei uma plataforma de ensino t�o presente na vida acad�mica de seus alunos, parab�ns!

Eduardo Dorneles

Aprendi React na plataforma da DevMedia h� cerca de 1 ano e meio... Hoje estou h� 1 ano empregado trabalhando 100% com React!

Adauto Junior

J� fiz alguns cursos na �rea e nenhum � t�o bom quanto o de voc�s. Estou aprendendo muito, muito obrigado por existirem. Est�o de parab�ns... Espero um dia conseguir um emprego na �rea.

Ver todos os casos de sucesso