MySQL Partitioning – RANGE() Partition

Muitos profissionais que trabalham com o servidor de bancos de dados MySQL “around the world” já utilizam as grandes novidades que foram entregues aos usuários em versões mais novas do banco de dados open source mais utilizado do mundo. Aquela que mais me chamou a atenção e já venho utilizando muito em vários dos clientes que já migraram de versões anteriores para a versão 5.1 é o MySQL Partitioning e é sobre este recurso aplicado ao design de tabelas em bancos de dados que falarei neste post.

Muita gente que conheço, que está ativamente trabalhando com o MySQL no dia-dia torce o nariz quando cito que atualizar a versão do servidor de bancos de dados, assim como a do sistema operacional e todos os aparatos envolvidos no funcionamento da tecnologia da informação é muito bom e não faz tanto mal assim.
Lógico que há atualizações que trarão grandes impactos à sistemas transacionais, de suporte à decisão, networking e outros, mas, de maneira geral, acompanhar a evolução da tecnologia é bem interessante.

O MySQL Partitioning surgiu principalmente com foco na necessidade de o DBA administrar melhor os arquivos de tabelas que estão localizados no diretório de dados do MySQL (DATADIR), sob o diretório do respectivo banco de dados – sabemos que a cada banco de dados que criamos no MySQL, um novo diretório é adicionado no DATADIR. Por que driblar o tamanho dos arquivos? Esta é uma limitação que o MySQL encontra, tamanho de arquivos localizados no sistema de arquivos.

Quando você particiona horizontalmente uma tabela, novos arquivos são criados no diretório de dados para armazenar os valores da coluna que foi particionada, ou seja, uma tabela InnoDB que conta com apenas um arquivo “.frm”, após particionada contará com mais x arquivos, onde x é o número de partições criadas.

Para tabelas controladas pelo Storage Engine MyISAM, os arquivos de partição são criados no mesmo local dos arquivos da própria tabela (.MYI, .MYD, .frm) e tem extensão “.MYD” (tbl_part_range#P#p11.MYD, por exemplo). Tabelas InnoDB tem arquivos de partições com extensão “.par” (t_innodb_hash.par, por exemplo) e também são criados no mesmo local ou diretório dos arquivos de extensão “.frm” da tabela original.

Um dos grandes benefícios entregues pelo recurso de particionamento de tabelas é realmente o melhoramento de performance, uma vez tendo ciência dos tipos de partições que podem ser aplicados aos atributos e regras de negócio mais adequados. O MySQL Partitioning somente suporta o particionamento de tabela de forma horizontal, ou seja, você criará partições com base nos valores inseridos em uma coluna de uma tabela. Para criar tal particionamento, você deverá informar qual das partition functions deseja utilizar e qual coluna deseja criar uma nova partição baseado em valores, como veremos mais à frente. Cada partição informada deverá ter um nome único.

Os tipos de particionamento que podemos aplicar em tabelas são:

• RANGE (focado neste post)
• HASH ou LINEAR HASH
• LIST
• KEY

Em muitos casos é necessário buscar dados em intervalos de de datas, o que geralmente é muito complicado utilizado índices B-TREE somando isso a uma grande quantidade de dados que será preciso manipular. Para facilitar, você poderia criar partições do tipo RANGE(), buscando manipular menos dados ao invés de ler toda a tabela.

Para criar uma tabela com partições RANGE(), basta definirmos quais as colunas serão particionadas e aplicar a sintaxe disponível, como segue:

CREATE TABLE test.tbl_part_range (
	id int not null,
	nome char(50) not null,
	data_registro datetime not null
) ENGINE = MyISAM
  PARTITION BY RANGE(YEAR(data_registro)) (
	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),
	PARTITION p11 VALUES LESS THAN(MAXVALUE)
);

Percebemos então que ao criar a tabela tbl_part_range, criamos 12 partições para armazenar dados inseridos de acordo com o ano da data. Uma linha que seja adicionada a esta tabela com uma data 2009-01-01, o valor da coluna data será adicionado à partição p09 e assim por diante.

mysql> insert into test.tbl_part_range set id=1,
    -> nome='wbianchi',
    -> data_registro='2010-02-07 10:45:23';
Query OK, 1 row affected (0,00 sec)

Para verificar em qual partição a linha acima foi adicionada, temos a seguinte consulta:

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

O que vale checarmos são as melhorias relacionadas com performance de consultas que recuperam dados baseados em colunas particionadas com a partition function RANGE(). Um dos pontos que eu considero ser o mais interessante é que, com RANGE() partition o que é chamado de partition pruning será utilizado, ou seja, uma consulta que precisa recuperar dados entre uma data inicial e final, um intervalo, percorrerá somente as partições necessárias para recuperar os dados, como exibido com o EXPLAIN PARTITIONS, veja só:

mysql> EXPLAIN PARTITIONS SELECT *
    -> FROM test.tbl_part_range
    -> WHERE data_registro >= '2009-01-01 00:00:00'
    -> AND data_registro <= '2010-05-10 00:00:00'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tbl_part_range
   partitions: p10,p11
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 7
        Extra: Using where
1 row in set (0,00 sec)

Como pedi para o SGBD me trazer os dados entre duas datas bem próximas, dando um intervalo de apenas um ano, o que vemos no EXPLAIN PARTITIONS é que o processo de recuperação de dados interno ao MySQL percorreu somente 2 partições, >= 2009 e <= 2010. Mesmo que o type nos aponte ALL, não é ruim, pis são bem menos dados do que todas as partições. Imagine uma tabela com 90.000.000 de linhas.

Um ponto interessante é, se trocarmos a função utilizada para criar as partções RANGE() de YEAR() para MONTH(), não haverá mais otimização por parte do Partition Pruning, para a mesma consulta, veja:

mysql> EXPLAIN PARTITIONS SELECT *
    -> FROM test.tbl_part_range
    -> WHERE data_registro >= '2009-01-01 00:00:00'
    -> AND data_registro <= '2010-05-10 00:00:00'\G                                                                                             *************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tbl_part_range
   partitions: p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3
        Extra: Using where
1 row in set (0,00 sec)

Observe então que, a consulta percorreu todas as partições para comparar valores linha a linha. Nesse ponto o particionamento deixa de ser interessante, pois, uma tabela particionada e uma não-particionada daria o mesmo custo de processamento de consultas ao ambiente.

No próximo post mostrarei alguns truques para lidar com partições do tipo HASH e LINEAR HASH, suportados pelo MySQL a partir da versão 5.1.

Happy MySQL’ing.