Performance e Otimização de Bancos de Dados MySQL

O seguinte Webinar foi apresentado ao vivo em 23/09/2015 pela empresa KingHost. Nele, eu – Jerônimo Fagundes – e Rodrigo Paris falamos sobre as melhores práticas para obter o máximo desempenho ao realizar buscas em um servidor MySQL, afim de tornar sua aplicação mais rápida, e melhorando a usabilidade da mesma.São apresentados 4 pontos-chave:

  1. Indexação de tabelas
  2. Particionamento de tabelas
  3. Otimização de consultas
  4. Infraestrutura

Aprimorando o particionamento em MySQL – Subpartições

Dando seguimento ao post anterior sobre particionamento de tabelas no MySQL, este post explica um passo a mais: subparticionamento.

O MySQL permite que você particione uma partição, onde o primeiro nível (PARTITION) é definido por uma expressão de particionamento, e o nível inferior (SUBPARTITION) é definido por outra.

Para utilizar o subparticionamento, é necessário seguir duas restrições:

  1. O tipo de particionamento (PARTITION) precisa ser dos tipos RANGE ou LIST;
  2. O tipo de particionamento (SUBPARTITION) precisa ser dos tipos HASH ou INDEX.

Portanto, não é possível subparticionar tabelas particionadas por HASH ou INDEX.

É importante notar que o número total de partições de uma tabela (nº de PARTITIONs * nº de SUBPARTITIONs) não pode ultrapassar 1024, que é o máximo que o MySQL suporta. Ex.: se uma tabela é particionada por RANGE em 2 partições, cada partição pode ser subparticionada em 512, pois 2 * 512 = 1024.

Abaixo segue um exemplo de tabela subparticionada:

CREATE TABLE ts (
    id INT,
    purchased DATE
)
PARTITION BY RANGE( YEAR(purchased) )
SUBPARTITION BY HASH( TO_DAYS(purchased) )
SUBPARTITIONS 2 (
    PARTITION p0 VALUES LESS THAN (1990),
    PARTITION p1 VALUES LESS THAN (2000),
    PARTITION p2 VALUES LESS THAN MAXVALUE
);

No exemplo acima a tabela ts possui 3 partições, e cada uma é subparticionada em 2 partições. Assim, temos 6 partições no total.

Veja outro exemplo:

CREATE TABLE ts (
    id INT,
    purchased DATE
)
PARTITION BY RANGE( YEAR(purchased) )
SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
    PARTITION p0 VALUES LESS THAN (1990) (
        SUBPARTITION s0,
        SUBPARTITION s1
    ),
    PARTITION p1 VALUES LESS THAN (2000) (
        SUBPARTITION s2
    ),
    PARTITION p2 VALUES LESS THAN MAXVALUE (
        SUBPARTITION s3,
        SUBPARTITION s4
    )
);

Neste outro exemplo, a tabela ts tem 5 partições no total, pois as partições p0 e p2 são subparticionadas em 2 partições cada, mas a partição p1 tem apenas uma subpartição.

Para saber mais sobre o subparticionamento em MySQL, consulte a documentação oficial do MySQL sobre subparticionamento.

Acelerando suas queries com o particionamento do MySQL

Quando você tem uma tabela com muitos registros no MySQL, as buscas podem se tornar extremamente lentas. Uma forma de otimizar a velocidade de suas buscas pode ser particionar a tabela.

Quando uma tabela é particionada, é como se você tivesse várias tabelinhas menores que, juntas, compõem a tabela completa.

Esta divisão é feita de acordo com algum critério lógico, de forma que quando você realize uma busca, o MySQL busque em apenas uma tabelinha, e não no universo todo de dados. Você não tem mais um grande universo de dados, mas vários “universinhos”. Como seu universo de busca se torna menor, a busca fica mais rápida.

O critério lógico de particionamento sempre é definido com base nos valores de uma ou mais colunas predeterminadas. Dependendo dos valores que essas colunas assumirem, uma row é guardada em uma ou em outra partição. Assim, quando formos buscar um conjunto de dados via SELECT, dependendo dos valores especificados para aquelas colunas na cláusula WHERE, sabemos exatamente em quais partições os dados se encontram, e assim evitamos a busca nas demais partições.

Há diversos tipos de particionamento no MySQL. Cada um atende a um tipo de critério lógico de particionamento.

Particionamento por RANGE

No particionamento por RANGE, o MySQL decide quais rows vão em uma partição de acordo com um intervalo de valores de uma coluna (ou expressão baseada em colunas). Na seguinte tabela:

CREATE TABLE `Funcionarios` (
    `cpf` VARCHAR(14) NOT NULL,
    `nome` VARCHAR(255) NOT NULL,
    `admissao` DATE NOT NULL
    PRIMARY KEY (`cpf`)
)
PARTITION BY RANGE(MONTH(admissao)) (
    PARTITION primeiro_trimestre VALUES LESS THAN (4),
    PARTITION segundo_trimestre VALUES LESS THAN (7),
    PARTITION terceito_trimestre VALUES LESS THAN (10),
    PARTITION quarto_trimestre VALUES LESS THAN MAXVALUE
);

Definimos 4 partições baseadas no mês da asmissão:

A partição primeiro_trimestre vai conter todos os registros de funcionários cuja admissão ocorreu nos meses 1, 2 ou 3 (janeiro, fevereiro ou março), ou seja, segundo a definição, todos os meses menores que 4. A partição segundo_trimestre conterá todos os registros de funcionários cujo mês de admissão é maior ou igual a 4 e menor que 7, ou seja, os meses de abril, maio e junho. A partição terceiro_trimestre conterá os registros de funcionários admitidos nos meses 7, 8 e 9, ou seja, julho, agosto e setembro. A partição quarto_trimestre conterá os registros dos demais funcionários. Assim, caso o seguinte select fosse feito:

SELECT * FROM Funcionarios WHERE admissao = '2015-03-15';

o MySQL saberia automaticamente que teria que procurar apenas na partição primeiro_trimestre, e não consideraria as outras partições na busca. O universo de busca fica muito menor, e a query mais rápida.

É importante notar que na expressão PARTITION BY (expr), expr deve ser uma expressão que retorne necessariamente um valor inteiro.

Particionamento por LIST

O particionamento por LIST é bem parecido com o RANGE. Todavia, em vez de especificarmos um intervalo de valores, vamos especificar um conjunto discreto de valores, fixos, predeterminados.

CREATE TABLE `Funcionarios` (
    `cpf` VARCHAR(14) NOT NULL,
    `nome` VARCHAR(255) NOT NULL,
    `filial` INT NOT NULL
    PRIMARY KEY (`cpf`)
)
PARTITION BY LIST(filial) (
    PARTITION regiao_norte VALUES IN (1, 2, 7),
    PARTITION regiao_sul VALUES IN (3, 9),
    PARTITION regiao_leste VALUES IN (4, 5, 6),
    PARTITION regiao_oeste VALUES IN (8),
);

Neste exemplo, se buscássemos todos os funcionários da filial número 5, o MySQL buscaria apenas na partição regiao_leste, que contém os funcionários das filiais 4, 5 e 6.

Particionamento por RANGE COLUMNS

É igual ao particionamento com RANGE, mas a expressão de particionamento pode ser de outros tipos que não um número inteiro, a saber: DATE, DATETIME, CHAR, VARCHAR, BINARY e VARBINARY. Além disso, pode-se usar tuplas de colunas em vez de uma só coluna.

CREATE TABLE `xyz` (
    `a` INT NOT NULL,
    `b` INT NOT NULL,
    `c` DATETIME NOT NULL
)
PARTITION BY RANGE COLUMNS (a, MONTH(c)) (
    PARTITION p0 VALUES LESS THAN (3, 7),
    PARTITION p1 VALUES LESS THAN (4, 9),
    PARTITION p2 VALUES LESS THAN (4, 11),
    PARTITION p3 VALUES LESS THAN (MAXVALUE, MAXVALUE)
);

Neste exemplo, se buscássemos por uma row em que a = 4 e c = ‘2015-10-01′, o MySQL buscaria na partição p2. Já uma row em que a = 4 e c = ‘2015-11-20′ estaria na partição p3.

Particionamento por LIST COLUMNS

Assim como o RANGE COLUMNS é uma extensão do RANGE, o LIST COLUMNS é uma extensão do LIST. Ele também permite que a expressão de particionamento seja de outros tipos, e também permite tuplas.

CREATE TABLE `Funcionarios` (
    `cpf` VARCHAR(14) NOT NULL,
    `estado`VARCHAR(2) NOT NULL DEFAULT 'RS',
    PRIMARY KEY (`cpf`)
)
PARTITION BY LIST COLUMNS (estado) (
    PARTITION regiao_sul VALUES IN ('RS', 'SC, 'PR'),
    PARTITION regiao_sudeste VALUES IN ('SP', 'RG', 'MG', 'ES'),
    PARTITION regiao_centro_oeste VALUES IN ('MT', 'MS', 'GO', 'DF'),
    PARTITION regiao_norte VALUES IN ('AC', 'AM', 'RO', 'RR', 'PA', 'AP', 'TO'),
    PARTITION regiao_nordeste VALUES IN ('MA', 'PI', 'CE', 'RN', 'PB', 'PE', 'AL', 'SE', 'BA')
);

Particionamento por HASH ou por LINEAR HASH

O particionamento por HASH é um dos mais simples, e de uso mais comum. A sua expressão de particionamento deve ser um valor inteiro. Neste tipo de particionamento, você deve especificar não só a expressão de particionamento, mas também o número de partições a utilizar. O MySQL vai nomear essas partições automaticamente, e vai escolher a melhor partição para sua row de acordo com o módulo da expressão de particionamento pelo número de partições. Esse é o tipo de particionamento recomendado se você quer ter uma distribuição semelhante de rows entre as partições.

CREATE TABLE `Pedidos` (
    `id`NOT NULL AUTO_INCREMENT,
    `id_cliente` INT NOT NULL,
    `valor` DECIMAL(5, 2) NOT NULL,
    `descricao` VARCHAR(255) NOT NULL
)
PARTITION BY HASH (id_cliente)
PARTITIONS 10;

Neste exemplo, criamos 10 partições por id de cliente. Isso significa, por exemplo, que todos os pedidos do cliente com id 7 vão residir na partição 7, pois 7 % 10 = 7 (sete módulo dez é igual a sete). Já os pedidos do cliente 22 estarão todos na partição 2, pois 22 % 10 = 2.

O número máximo de partições por tabela que o MySQL permite é 1024.

O particionamento por LINEAR HASH é muito semelhante ao por HASH. Todavia, em vez do módulo, o MySQL usa outra fórmula baseada em potências de 2.

Particionamento por KEY ou por LINEAR Key

Muito parecida com a partição por HASH. Todavia, no particionamento por KEY, o MySQL server é que vai escolher o melhor algoritmo para o cálculo da partição resultante.

A expressão de particionamento pode ser zero ou mais colunas, conforme segue:

Se não é especificada nenhuma coluna, o MySQL vai usar a chave primária se houver; se não houver chave primária, vai usar uma chave única que houver. Se houver colunas especificadas, elas precisam fazer parte da chave primária ou da chave única. LINEAR KEY é semelhante à KEY, usando cálculo de potências de 2 (assim como LINEAR HASH).

Um cuidado deve ser tomado: se você efetuar uma busca (SELECT) e não especificar na cláusula WHERE o valor da coluna de particionamento, o MySQL não vai saber em qual partição buscar, e vai acabar varrendo todas as partições para efetuar sua busca. Isso será mais lento que buscar em uma tabela não-particionada. Assim sendo, o particionamento é uma poderosa ferramenta, mas suas queries terão de ser adaptadas para aproveitar esse particionamento.

Quer saber mais sobre particionamento no MySQL? Consulte a documentação oficial do MySQL sobre particionamento.