Particionamento MySQL para Grandes Conjuntos de Dados

Introdução

Neste laboratório, você aprenderá como implementar o particionamento do MySQL para grandes conjuntos de dados para melhorar o desempenho de consultas e o gerenciamento de dados. O laboratório foca no particionamento de uma tabela por intervalo (range), especificamente usando a coluna sale_date de uma tabela sales.

Você começará conectando-se ao servidor MySQL e criando um banco de dados sales_data. Em seguida, criará a tabela sales, particionando-a pelo ano da sale_date em partições para 2020, 2021, 2022, 2023 e uma partição futura. Passos subsequentes cobrirão a consulta de dados de partições específicas, a reorganização de partições usando ALTER TABLE e a verificação do impacto do particionamento na velocidade das consultas.

Nota: Para este laboratório, você só precisa entrar no shell do MySQL uma vez no início e sair no final. Todos os comandos SQL nos passos seguintes devem ser executados dentro da mesma sessão do MySQL.

Criar uma Tabela Particionada

Nesta etapa, criaremos um banco de dados e uma tabela particionada no MySQL. O particionamento ajuda no gerenciamento de grandes conjuntos de dados, dividindo uma tabela em partes menores e mais gerenciáveis com base em uma regra especificada. Isso pode melhorar significativamente o desempenho de consultas, especialmente para consultas que filtram dados com base na chave de particionamento.

Primeiro, abra um terminal na VM LabEx. Você já deve estar no diretório ~/project.

Conecte-se ao servidor MySQL como usuário root (faça isso apenas uma vez no início do laboratório):

sudo mysql -u root

Você está agora no shell do MySQL. Todos os comandos SQL subsequentes devem ser executados nesta sessão até que você termine o laboratório.

Vamos criar um banco de dados chamado sales_data para armazenar nossa tabela:

CREATE DATABASE sales_data;

Mude para o banco de dados recém-criado:

USE sales_data;

Agora, criaremos uma tabela chamada sales e a particionaremos pelo ano da coluna sale_date. Criaremos partições para os anos 2020, 2021, 2022, 2023 e uma partição "catch-all" para datas futuras.

CREATE TABLE sales (
    sale_id INT NOT NULL,
    sale_date DATE NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    PRIMARY KEY (sale_id, sale_date)
)
PARTITION BY RANGE (YEAR(sale_date)) (
    PARTITION p2020 VALUES LESS THAN (2021),
    PARTITION p2021 VALUES LESS THAN (2022),
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION pFuture VALUES LESS THAN MAXVALUE
);

Vamos entender a cláusula PARTITION BY RANGE:

• PARTITION BY RANGE (YEAR(sale_date)): Isso especifica que a tabela será particionada com base no intervalo de valores retornados pela função YEAR() aplicada à coluna sale_date.
• PARTITION p2020 VALUES LESS THAN (2021): Isso cria uma partição chamada p2020. Qualquer linha onde o ano de sale_date seja menor que 2021 (ou seja, 2020) será armazenada nesta partição.
• PARTITION p2021 VALUES LESS THAN (2022): Isso cria uma partição chamada p2021 para dados do ano de 2021.
• PARTITION p2022 VALUES LESS THAN (2023): Isso cria uma partição chamada p2022 para dados do ano de 2022.
• PARTITION p2023 VALUES LESS THAN (2024): Isso cria uma partição chamada p2023 para dados do ano de 2023.
• PARTITION pFuture VALUES LESS THAN MAXVALUE: Isso cria uma partição chamada pFuture que armazenará quaisquer dados com um ano de sale_date maior ou igual a 2024. MAXVALUE é um valor especial que é sempre maior que qualquer outro valor.

Após executar a instrução CREATE TABLE, você pode verificar a estrutura da tabela e suas partições usando o seguinte comando:

SHOW CREATE TABLE sales;

Procure pela cláusula PARTITION BY RANGE na saída para confirmar que a tabela foi criada com as partições especificadas.

Agora, vamos inserir alguns dados de exemplo na tabela sales. O MySQL colocará automaticamente cada linha na partição correta com base na sale_date.

INSERT INTO sales (sale_id, sale_date, amount) VALUES
(1, '2020-12-31', 100.00),
(2, '2021-01-15', 150.00),
(3, '2021-12-25', 200.00),
(4, '2022-06-01', 120.00),
(5, '2022-12-31', 180.00),
(6, '2023-03-10', 250.00),
(7, '2023-09-20', 300.00),
(8, '2024-01-01', 350.00);

Você criou com sucesso uma tabela particionada e inseriu dados nela. Na próxima etapa, aprenderemos como consultar dados de partições específicas.

Consultar Dados de Partições Específicas

Nesta etapa, exploraremos como consultar dados de forma eficiente de uma tabela particionada, direcionando partições específicas. Este é um dos principais benefícios do particionamento, pois permite que o MySQL escaneie apenas as partições relevantes, reduzindo significativamente a quantidade de dados processados e melhorando o desempenho das consultas.

Lembrete: Você ainda deve estar no shell do MySQL e usando o banco de dados sales_data. Se não estiver, use:

USE sales_data;

Para consultar dados de uma partição específica, você pode incluir uma cláusula WHERE que filtra pela chave de particionamento. O otimizador de consultas do MySQL geralmente é inteligente o suficiente para identificar quais partições são relevantes com base na cláusula WHERE.

Por exemplo, para recuperar todas as vendas do ano de 2021, você pode usar a seguinte consulta. Observe que estamos usando uma condição de intervalo direta em sale_date. O uso de funções como YEAR(sale_date) na cláusula WHERE pode impedir que o MySQL utilize o "partition pruning" (poda de partições), o que faria com que ele escaneasse todas as partições.

SELECT * FROM sales WHERE sale_date >= '2021-01-01' AND sale_date < '2022-01-01';

Para ver quais partições o MySQL está acessando para esta consulta, você pode usar a instrução EXPLAIN PARTITIONS:

EXPLAIN PARTITIONS SELECT * FROM sales WHERE sale_date >= '2021-01-01' AND sale_date < '2022-01-01';

Na saída de EXPLAIN PARTITIONS, observe a coluna partitions. Ela deve mostrar p2021, indicando que o MySQL está escaneando apenas a partição p2021 para atender a esta consulta.

+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | sales | p2021      | ALL  | PRIMARY       | NULL | NULL    | NULL |    2 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+

Você também pode consultar dados que abrangem várias partições. Por exemplo, para obter dados de vendas de 2022 e 2023:

SELECT * FROM sales WHERE sale_date >= '2022-01-01' AND sale_date < '2024-01-01';

Usando EXPLAIN PARTITIONS novamente, mostrará que o MySQL acessa as partições p2022 e p2023:

EXPLAIN PARTITIONS SELECT * FROM sales WHERE sale_date >= '2022-01-01' AND sale_date < '2024-01-01';

A coluna partitions mostrará p2022,p2023.

+----+-------------+-------+---------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | partitions    | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+---------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | sales | p2022,p2023   | ALL  | PRIMARY       | NULL | NULL    | NULL |    4 | Using where |
+----+-------------+-------+---------------+------+---------------+------+---------+------+------+-------------+

Isso demonstra como o particionamento permite que o MySQL "pode" (exclua) partições irrelevantes durante a execução da consulta, levando a resultados mais rápidos, especialmente em tabelas muito grandes onde escanear a tabela inteira seria demorado.

Para ver o número de linhas em cada partição, você pode consultar a tabela INFORMATION_SCHEMA.PARTITIONS:

SELECT
    PARTITION_NAME,
    TABLE_ROWS
FROM
    INFORMATION_SCHEMA.PARTITIONS
WHERE
    TABLE_SCHEMA = 'sales_data' AND TABLE_NAME = 'sales';

Esta consulta fornece uma visão clara de como os dados são distribuídos entre suas partições.

+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p2020          |          1 |
| p2021          |          2 |
| p2022          |          2 |
| p2023          |          2 |
| pFuture        |          1 |
+----------------+------------+

Você consultou com sucesso dados de partições específicas e observou como o MySQL utiliza o particionamento para otimização de consultas.

Reorganizar e Gerenciar Partições

Nesta etapa, aprenderemos como modificar a estrutura de partição de uma tabela existente usando a instrução ALTER TABLE. Isso é útil para adaptar seu esquema de particionamento à medida que seus dados crescem ou suas necessidades mudam.

Lembrete: Você ainda deve estar no shell do MySQL e usando o banco de dados sales_data. Se não estiver, use:

USE sales_data;

Digamos que queremos adicionar uma nova partição para o ano de 2024. Atualmente, os dados de 2024 em diante estão na partição pFuture. Você não pode adicionar uma nova partição com ADD PARTITION porque a partição pFuture é definida com VALUES LESS THAN MAXVALUE, que deve ser sempre a última partição.

Em vez disso, precisamos REORGANIZE a partição pFuture para dividi-la. Dividiremos pFuture em duas novas partições: uma para o ano de 2024 (p2024) e uma nova partição pFuture para tudo depois disso.

ALTER TABLE sales REORGANIZE PARTITION pFuture INTO (
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION pFuture VALUES LESS THAN MAXVALUE
);

Este comando pega a partição pFuture existente, move quaisquer dados de 2024 para a nova partição p2024 e redefine pFuture para cobrir datas a partir de 2025. A linha com sale_date '2024-01-01' será movida para p2024.

Vamos verificar a estrutura de partição atualizada e as contagens de linhas:

SELECT
    PARTITION_NAME,
    TABLE_ROWS
FROM
    INFORMATION_SCHEMA.PARTITIONS
WHERE
    TABLE_SCHEMA = 'sales_data' AND TABLE_NAME = 'sales';

Você deverá ver a nova partição p2024. A linha de 2024 agora está em p2024.

+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p2020          |          0 |
| p2021          |          2 |
| p2022          |          2 |
| p2023          |          2 |
| p2024          |          0 |
| pFuture        |          0 |
+----------------+------------+

Agora, vamos demonstrar a fusão de partições. Suponha que queremos combinar as partições p2020 e p2021 em uma única partição chamada p2020_2021.

ALTER TABLE sales REORGANIZE PARTITION p2020, p2021 INTO (
    PARTITION p2020_2021 VALUES LESS THAN (2022)
);

Este comando mescla os dados de p2020 e p2021 em uma nova partição chamada p2020_2021. A cláusula VALUES LESS THAN (2022) define o novo limite para esta partição mesclada.

Verifique a estrutura de partição novamente:

SELECT
    PARTITION_NAME,
    TABLE_ROWS
FROM
    INFORMATION_SCHEMA.PARTITIONS
WHERE
    TABLE_SCHEMA = 'sales_data' AND TABLE_NAME = 'sales';

Você verá que p2020 e p2021 desapareceram, e p2020_2021 existe com a contagem de linhas combinada.

+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p2020_2021     |          3 |
| p2022          |          2 |
| p2023          |          2 |
| p2024          |          0 |
| pFuture        |          0 |
+----------------+------------+

Finalmente, vamos remover uma partição. Podemos remover a partição p2024. Observe que isso também excluirá todos os dados dentro dessa partição.

ALTER TABLE sales DROP PARTITION p2024;

Verifique a estrutura de partição uma última vez:

SELECT
    PARTITION_NAME,
    TABLE_ROWS
FROM
    INFORMATION_SCHEMA.PARTITIONS
WHERE
    TABLE_SCHEMA = 'sales_data' AND TABLE_NAME = 'sales';

A partição p2024 não deve mais ser listada.

+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p2020_2021     |          3 |
| p2022          |          2 |
| p2023          |          2 |
| pFuture        |          0 |
+----------------+------------+

Você reorganizou, mesclou e removeu partições com sucesso usando ALTER TABLE. Isso demonstra a flexibilidade no gerenciamento de tabelas particionadas à medida que seus dados evoluem.

Verificar Impacto da Partição na Velocidade da Consulta

Nesta etapa, exploraremos como o particionamento pode afetar o desempenho das consultas. Embora nosso conjunto de dados atual seja pequeno, ainda podemos observar o princípio da poda de partições (partition pruning), onde o MySQL escaneia apenas as partições necessárias. Para conjuntos de dados maiores, esse efeito é muito mais pronunciado.

Lembrete: Você ainda deve estar no shell do MySQL e usando o banco de dados sales_data. Se não estiver, use:

USE sales_data;

Para observar o impacto do particionamento, podemos usar a instrução EXPLAIN, que mostra o plano de execução de uma consulta. Especificamente, EXPLAIN PARTITIONS mostrará quais partições são acessadas.

Vamos executar uma consulta que filtra pela chave de particionamento (ano de sale_date):

EXPLAIN PARTITIONS SELECT * FROM sales WHERE sale_date >= '2023-01-01' AND sale_date < '2024-01-01';

Observe a coluna partitions na saída. Ela deve indicar que apenas a partição p2023 está sendo escaneada.

Agora, vamos executar uma consulta que não filtra diretamente pela chave de particionamento, mas por outra coluna (amount):

EXPLAIN PARTITIONS SELECT * FROM sales WHERE amount > 200;

Neste caso, como a condição da consulta não está diretamente na chave de particionamento (sale_date), o MySQL pode precisar escanear várias ou todas as partições para encontrar as linhas correspondentes. A coluna partitions na saída de EXPLAIN PARTITIONS mostrará quais partições foram consideradas. Com nosso pequeno conjunto de dados, ele ainda pode escanear todas as partições.

Para obter uma visão mais detalhada do processo de execução da consulta e do tempo gasto, você pode usar o recurso de profiling do MySQL.

Habilitar profiling:

SET profiling = 1;

Agora, execute as duas consultas novamente:

SELECT * FROM sales WHERE sale_date >= '2023-01-01' AND sale_date < '2024-01-01';
SELECT * FROM sales WHERE amount > 200;

Visualize os resultados do profiling:

SHOW PROFILES;

A saída listará as consultas executadas e sua duração. Você pode então examinar os detalhes de uma consulta específica usando seu Query_ID:

SHOW PROFILE FOR QUERY [Query_ID];

Substitua [Query_ID] pelo ID da consulta que você deseja analisar na saída de SHOW PROFILES. Observe as diferentes fases de execução e o tempo gasto em cada uma.

Embora a diferença de tempo possa ser insignificante com nosso pequeno conjunto de dados, em um cenário do mundo real com milhões de linhas, consultas que podem utilizar a poda de partições (como a que filtra por YEAR(sale_date)) serão significativamente mais rápidas do que aquelas que precisam escanear várias ou todas as partições.

Finalmente, desabilite o profiling:

SET profiling = 0;

Esta etapa demonstrou como usar EXPLAIN PARTITIONS e profiling para entender como o particionamento afeta a execução e o desempenho das consultas.

Resumo

Neste laboratório, você aprendeu a implementar o particionamento do MySQL para grandes conjuntos de dados para melhorar o desempenho de consultas e o gerenciamento de dados. Você começou criando um banco de dados e uma tabela particionada por intervalo com base no ano de uma coluna de data. Em seguida, praticou a consulta de dados de partições específicas, observando como o MySQL usa a poda de partições (partition pruning) para otimizar consultas. Finalmente, você aprendeu a reorganizar partições adicionando, dividindo e mesclando-as usando a instrução ALTER TABLE, e explorou como usar EXPLAIN PARTITIONS e profiling para entender o impacto do particionamento na velocidade das consultas. O particionamento é uma técnica poderosa para gerenciar e consultar tabelas grandes de forma eficiente no MySQL.

Quando tiver concluído todas as etapas, você pode sair do shell do MySQL digitando:

exit;