Particionamento do MySQL para grandes conjuntos de dados

Introdução

Neste laboratório, você aprenderá a implementar o particionamento do MySQL para grandes conjuntos de dados, melhorando o desempenho das consultas e o gerenciamento dos dados. O foco é particionar uma tabela por intervalo, usando especificamente a coluna sale_date da tabela sales.

Primeiro, você se conectará ao servidor MySQL e criará o banco de dados sales_data. Em seguida, criará a tabela sales, particionada pelo ano de sale_date, com partições para 2020, 2021, 2022, 2023 e uma partição para datas futuras. Depois, você consultará dados de partições específicas, reorganizará partições com ALTER TABLE e verificará o impacto do particionamento na velocidade das consultas.

Observação: Neste laboratório, você só precisa entrar no shell do MySQL uma vez, no início, e sair no final. Execute todos os comandos SQL das etapas seguintes na mesma sessão do MySQL.

Criar uma tabela particionada

Nesta etapa, você criará um banco de dados e uma tabela particionada no MySQL. O particionamento ajuda a gerenciar grandes conjuntos de dados, dividindo uma tabela em partes menores e mais fáceis de administrar, de acordo com uma regra especificada. Isso pode melhorar significativamente o desempenho das consultas, especialmente quando elas filtram dados com base na chave de particionamento.

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

Conecte-se ao servidor MySQL como usuário root. Faça isso somente uma vez, no início do laboratório:

sudo mysql -u root

Agora você está no shell do MySQL. Execute todos os comandos SQL seguintes nesta sessão até concluir o laboratório.

Ative uma transcrição para que as verificações da etapa possam ler os comandos SQL inseridos nesta sessão enquanto ela permanecer aberta:

tee /home/labex/project/partitioning-session.log

Mantenha a transcrição ativada até concluir o laboratório. Os comandos SQL inseridos aqui não são registrados no histórico do shell do terminal.

Crie o banco de dados sales_data para armazenar a tabela:

CREATE DATABASE sales_data;

Selecione o banco de dados que você acabou de criar:

USE sales_data;

Agora, crie a tabela sales e particione-a pelo ano da coluna sale_date. Você criará partições para os anos 2020, 2021, 2022 e 2023, além de uma partição para todas as 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
);

Veja o que faz a cláusula PARTITION BY RANGE:

• PARTITION BY RANGE (YEAR(sale_date)): define que a tabela será particionada de acordo com os intervalos de valores retornados pela função YEAR() aplicada à coluna sale_date.
• PARTITION p2020 VALUES LESS THAN (2021): cria uma partição chamada p2020. Todas as linhas cujo ano em sale_date seja menor que 2021 (ou seja, 2020) serão armazenadas nela.
• PARTITION p2021 VALUES LESS THAN (2022): cria uma partição chamada p2021 para os dados do ano de 2021.
• PARTITION p2022 VALUES LESS THAN (2023): cria uma partição chamada p2022 para os dados do ano de 2022.
• PARTITION p2023 VALUES LESS THAN (2024): cria uma partição chamada p2023 para os dados do ano de 2023.
• PARTITION pFuture VALUES LESS THAN MAXVALUE: cria uma partição chamada pFuture para armazenar dados cujo ano em sale_date seja igual ou posterior a 2024. MAXVALUE é um valor especial, sempre maior que qualquer outro valor.

Depois de executar a instrução CREATE TABLE, verifique a estrutura da tabela e suas partições com o comando:

SHOW CREATE TABLE sales;

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

Agora, insira alguns dados de exemplo na tabela sales. O MySQL colocará cada linha automaticamente na partição correta, com base em 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 uma tabela particionada e inseriu dados nela. Na próxima etapa, você aprenderá a consultar dados de partições específicas.

Consultar dados de partições específicas

Nesta etapa, você verá como consultar dados de uma tabela particionada com eficiência, direcionando a consulta a partições específicas. Essa é uma das principais vantagens do particionamento: o MySQL pode analisar somente 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. Caso não esteja, execute:

USE sales_data;

Para consultar dados de uma partição específica, você pode incluir uma cláusula WHERE que filtre pela chave de particionamento. Em geral, o otimizador de consultas do MySQL consegue identificar quais partições são relevantes com base na cláusula WHERE.

Por exemplo, para obter todas as vendas de 2021, use a consulta a seguir. Observe que usamos uma condição de intervalo diretamente em sale_date. O uso de funções como YEAR(sale_date) na cláusula WHERE pode impedir que o MySQL faça a eliminação de partições, fazendo com que ele analise 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 acessa nessa consulta, use 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á analisando somente a partição p2021 para executar essa 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 os dados de vendas de 2022 e 2023:

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

Se você executar EXPLAIN PARTITIONS novamente, verá 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 elimine (exclua) partições irrelevantes durante a execução de uma consulta. Assim, os resultados são obtidos mais rapidamente, especialmente em tabelas muito grandes, nas quais analisar a tabela inteira levaria mais tempo.

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

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

Essa consulta mostra claramente como os dados estão distribuídos entre as partições.

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

Você consultou dados de partições específicas e observou como o MySQL usa o particionamento para otimizar consultas.

Reorganizar e gerenciar partições

Nesta etapa, você aprenderá a modificar a estrutura de partições de uma tabela existente usando a instrução ALTER TABLE. Isso é útil para adaptar o esquema de particionamento à medida que os dados aumentam ou as necessidades mudam.

Lembrete: Você ainda deve estar no shell do MySQL e usando o banco de dados sales_data. Caso não esteja, execute:

USE sales_data;

Suponha que queremos adicionar uma nova partição para o ano de 2024. No momento, os dados de 2024 em diante estão na partição pFuture. Não é possível adicionar uma nova partição com ADD PARTITION, porque a partição pFuture foi definida com VALUES LESS THAN MAXVALUE e precisa ser sempre a última partição.

Em vez disso, precisamos usar REORGANIZE para dividir a partição pFuture. Vamos dividi-la em duas novas partições: uma para 2024 (p2024) e outra chamada pFuture, que continuará armazenando todos os dados posteriores.

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

Esse comando usa a partição pFuture existente e move para a nova partição p2024 todos os dados de 2024. Em seguida, redefine pFuture para abranger datas a partir de 2025. A linha com sale_date igual a '2024-01-01' será movida para p2024.

Verifique a estrutura atualizada das partições e a quantidade 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 como mesclar 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)
);

Esse 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 dessa partição mesclada.

Verifique novamente a estrutura das partições:

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

Você verá que p2020 e p2021 não existem mais e que p2020_2021 existe com a quantidade combinada de linhas.

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

Por fim, vamos excluir uma partição. Você pode remover a partição p2024. Observe que isso também excluirá todos os dados dessa partição.

ALTER TABLE sales DROP PARTITION p2024;

Verifique a estrutura das partições pela ú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 deverá mais aparecer na lista.

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

Você reorganizou, mesclou e excluiu partições usando ALTER TABLE. Isso demonstra a flexibilidade do gerenciamento de tabelas particionadas conforme os dados evoluem.

Verificar o impacto do particionamento na velocidade das consultas

Nesta etapa, você verá como o particionamento pode afetar o desempenho das consultas. Embora o conjunto de dados atual seja pequeno, ainda é possível observar o princípio da eliminação de partições: o MySQL analisa somente as partições necessárias. Em conjuntos de dados maiores, esse efeito é muito mais perceptível.

Lembrete: Você ainda deve estar no shell do MySQL e usando o banco de dados sales_data. Caso não esteja, execute:

USE sales_data;

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

Execute uma consulta que filtre pela chave de particionamento (o 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 deverá indicar que somente a partição p2023 está sendo analisada.

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

EXPLAIN PARTITIONS SELECT * FROM sales WHERE amount > 200;

Nesse caso, como a condição da consulta não usa diretamente a chave de particionamento (sale_date), o MySQL talvez precise analisar várias partições ou todas elas 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, talvez todas as partições ainda sejam analisadas.

Para examinar com mais detalhes o processo de execução da consulta e o tempo gasto, você pode usar o recurso de profiling do MySQL.

Ative o profiling:

SET profiling = 1;

Agora, execute novamente as duas consultas:

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

Veja os resultados do profiling:

SHOW PROFILES;

A saída listará as consultas executadas e a duração de cada uma. Em seguida, você poderá 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ê quer analisar, conforme exibido na saída de SHOW PROFILES. Observe as diferentes etapas da execução e o tempo gasto em cada uma.

A diferença de tempo pode ser insignificante com nosso pequeno conjunto de dados. Porém, em um cenário real com milhões de linhas, consultas que podem aproveitar a eliminação de partições (como a consulta que filtra por YEAR(sale_date)) serão significativamente mais rápidas do que aquelas que precisam analisar várias partições ou todas elas.

Por fim, desative o profiling:

SET profiling = 0;

Nesta etapa, você usou EXPLAIN PARTITIONS e o 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, melhorando o desempenho das consultas e o gerenciamento dos dados. Primeiro, você criou um banco de dados e uma tabela particionada por intervalo, com base no ano de uma coluna de data. Depois, praticou a consulta de dados em partições específicas e observou como o MySQL usa a eliminação de partições para otimizar consultas. Por fim, você aprendeu a reorganizar partições, adicionando, dividindo e mesclando partições com a instrução ALTER TABLE. Você também explorou como usar EXPLAIN PARTITIONS e o profiling para entender o impacto do particionamento na velocidade das consultas. O particionamento é uma técnica poderosa para gerenciar e consultar grandes tabelas com eficiência no MySQL.

Quando concluir todas as etapas, saia do shell do MySQL digitando:

exit;