Partitionnement MySQL pour les grands ensembles de données

Introduction

Dans ce laboratoire, vous apprendrez comment implémenter le partitionnement MySQL pour les grands ensembles de données afin d'améliorer les performances des requêtes et la gestion des données. Le laboratoire se concentre sur le partitionnement d'une table par plage (range partitioning), en utilisant spécifiquement la colonne sale_date d'une table sales.

Vous commencerez par vous connecter au serveur MySQL et créer une base de données sales_data. Ensuite, vous créerez la table sales, en la partitionnant par l'année de la colonne sale_date en partitions pour 2020, 2021, 2022, 2023 et une partition future. Les étapes suivantes couvriront l'interrogation des données à partir de partitions spécifiques, la réorganisation des partitions à l'aide de ALTER TABLE et la vérification de l'impact du partitionnement sur la vitesse des requêtes.

Remarque : Pour ce laboratoire, vous n'avez besoin d'entrer dans le shell MySQL qu'une seule fois au début et d'en sortir à la fin. Toutes les commandes SQL des étapes suivantes doivent être exécutées dans la même session MySQL.

Créer une table partitionnée

Dans cette étape, nous allons créer une base de données et une table partitionnée dans MySQL. Le partitionnement aide à gérer de grands ensembles de données en divisant une table en parties plus petites et plus gérables, basées sur une règle spécifiée. Cela peut améliorer considérablement les performances des requêtes, en particulier pour les requêtes qui filtrent les données en fonction de la clé de partitionnement.

Tout d'abord, ouvrez un terminal dans la VM LabEx. Vous devriez déjà être dans le répertoire ~/project.

Connectez-vous au serveur MySQL en tant qu'utilisateur root (faites-le une seule fois au début du laboratoire) :

sudo mysql -u root

Vous êtes maintenant dans le shell MySQL. Toutes les commandes SQL ultérieures doivent être exécutées dans cette session jusqu'à la fin du laboratoire.

Créons une base de données nommée sales_data pour stocker notre table :

CREATE DATABASE sales_data;

Basculez vers la base de données nouvellement créée :

USE sales_data;

Maintenant, nous allons créer une table nommée sales et la partitionner par l'année de la colonne sale_date. Nous allons créer des partitions pour les années 2020, 2021, 2022, 2023 et une partition "rattrapage" (catch-all) pour les dates futures.

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
);

Comprenons la clause PARTITION BY RANGE :

• PARTITION BY RANGE (YEAR(sale_date)): Ceci spécifie que la table sera partitionnée en fonction de la plage de valeurs retournées par la fonction YEAR() appliquée à la colonne sale_date.
• PARTITION p2020 VALUES LESS THAN (2021): Ceci crée une partition nommée p2020. Toute ligne dont l'année de sale_date est inférieure à 2021 (c'est-à-dire 2020) sera stockée dans cette partition.
• PARTITION p2021 VALUES LESS THAN (2022): Ceci crée une partition nommée p2021 pour les données de l'année 2021.
• PARTITION p2022 VALUES LESS THAN (2023): Ceci crée une partition nommée p2022 pour les données de l'année 2022.
• PARTITION p2023 VALUES LESS THAN (2024): Ceci crée une partition nommée p2023 pour les données de l'année 2023.
• PARTITION pFuture VALUES LESS THAN MAXVALUE: Ceci crée une partition nommée pFuture qui stockera toutes les données dont l'année de sale_date est supérieure ou égale à 2024. MAXVALUE est une valeur spéciale qui est toujours supérieure à toute autre valeur.

Après avoir exécuté l'instruction CREATE TABLE, vous pouvez vérifier la structure de la table et ses partitions en utilisant la commande suivante :

SHOW CREATE TABLE sales;

Recherchez la clause PARTITION BY RANGE dans la sortie pour confirmer que la table a été créée avec les partitions spécifiées.

Maintenant, insérons quelques données d'exemple dans la table sales. MySQL placera automatiquement chaque ligne dans la partition correcte en fonction de la 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);

Vous avez créé avec succès une table partitionnée et y avez inséré des données. Dans la prochaine étape, nous apprendrons comment interroger les données à partir de partitions spécifiques.

Interroger des données à partir de partitions spécifiques

Dans cette étape, nous allons explorer comment interroger efficacement des données à partir d'une table partitionnée en ciblant des partitions spécifiques. C'est l'un des principaux avantages du partitionnement, car il permet à MySQL de ne scanner que les partitions pertinentes, réduisant ainsi considérablement la quantité de données traitées et améliorant les performances des requêtes.

Rappel : Vous devriez toujours être dans le shell MySQL et utiliser la base de données sales_data. Si ce n'est pas le cas, utilisez :

USE sales_data;

Pour interroger des données à partir d'une partition spécifique, vous pouvez inclure une clause WHERE qui filtre sur la clé de partitionnement. L'optimiseur de requêtes de MySQL est souvent suffisamment intelligent pour identifier les partitions pertinentes en fonction de la clause WHERE.

Par exemple, pour récupérer toutes les ventes de l'année 2021, vous pouvez utiliser la requête suivante. Notez que nous utilisons une condition de plage directe sur sale_date. L'utilisation de fonctions comme YEAR(sale_date) dans la clause WHERE peut empêcher MySQL d'utiliser le "partition pruning" (élagage de partitions), ce qui le forcerait à scanner toutes les partitions.

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

Pour voir quelles partitions MySQL accède pour cette requête, vous pouvez utiliser l'instruction EXPLAIN PARTITIONS :

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

Dans la sortie de EXPLAIN PARTITIONS, regardez la colonne partitions. Elle devrait afficher p2021, indiquant que MySQL ne scanne que la partition p2021 pour satisfaire cette requête.

+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
| 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 |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+

Vous pouvez également interroger des données qui couvrent plusieurs partitions. Par exemple, pour obtenir les données de ventes pour 2022 et 2023 :

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

En utilisant à nouveau EXPLAIN PARTITIONS, vous verrez que MySQL accède aux partitions p2022 et p2023 :

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

La colonne partitions affichera 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 |
+----+-------------+-------+---------------+------+---------------+------+---------+------+------+-------------+

Cela démontre comment le partitionnement permet à MySQL d'élaguer (exclure) les partitions non pertinentes lors de l'exécution des requêtes, ce qui conduit à des résultats plus rapides, en particulier sur de très grandes tables où le scan de la table entière serait long.

Pour voir le nombre de lignes dans chaque partition, vous pouvez interroger la table INFORMATION_SCHEMA.PARTITIONS :

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

Cette requête fournit une vue claire de la manière dont les données sont distribuées entre vos partitions.

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

Vous avez interrogé avec succès des données à partir de partitions spécifiques et observé comment MySQL utilise le partitionnement pour l'optimisation des requêtes.

Réorganiser et gérer les partitions

Dans cette étape, nous allons apprendre à modifier la structure de partitionnement d'une table existante à l'aide de l'instruction ALTER TABLE. Ceci est utile pour adapter votre schéma de partitionnement à mesure que vos données augmentent ou que vos besoins évoluent.

Rappel : Vous devriez toujours être dans le shell MySQL et utiliser la base de données sales_data. Si ce n'est pas le cas, utilisez :

USE sales_data;

Supposons que nous voulions ajouter une nouvelle partition pour l'année 2024. Actuellement, les données de 2024 et au-delà se trouvent dans la partition pFuture. Vous ne pouvez pas ajouter une nouvelle partition avec ADD PARTITION car la partition pFuture est définie avec VALUES LESS THAN MAXVALUE, qui doit toujours être la dernière partition.

Au lieu de cela, nous devons REORGANIZE la partition pFuture pour la diviser. Nous allons diviser pFuture en deux nouvelles partitions : une pour l'année 2024 (p2024) et une nouvelle partition pFuture pour tout ce qui suit.

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

Cette commande prend la partition pFuture existante, déplace toutes les données de 2024 vers la nouvelle partition p2024, et redéfinit pFuture pour couvrir les dates à partir de 2025. La ligne avec sale_date '2024-01-01' sera déplacée vers p2024.

Vérifions la structure de partitionnement mise à jour et le nombre de lignes :

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

Vous devriez voir la nouvelle partition p2024. La ligne de 2024 se trouve maintenant dans p2024.

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

Démontrons maintenant la fusion de partitions. Supposons que nous voulions combiner les partitions p2020 et p2021 en une seule partition nommée p2020_2021.

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

Cette commande fusionne les données de p2020 et p2021 dans une nouvelle partition appelée p2020_2021. La clause VALUES LESS THAN (2022) définit la nouvelle limite pour cette partition fusionnée.

Vérifiez à nouveau la structure de partitionnement :

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

Vous verrez que p2020 et p2021 ont disparu, et que p2020_2021 existe avec le nombre total de lignes combiné.

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

Enfin, supprimons une partition. Nous pouvons supprimer la partition p2024. Notez que cela supprimera également toutes les données contenues dans cette partition.

ALTER TABLE sales DROP PARTITION p2024;

Vérifions la structure de partitionnement une dernière fois :

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

La partition p2024 ne devrait plus être listée.

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

Vous avez réussi à réorganiser, fusionner et supprimer des partitions en utilisant ALTER TABLE. Cela démontre la flexibilité de la gestion des tables partitionnées à mesure que vos données évoluent.

Vérifier l'impact des partitions sur la vitesse des requêtes

Dans cette étape, nous allons explorer comment le partitionnement peut affecter les performances des requêtes. Bien que notre ensemble de données actuel soit petit, nous pouvons toujours observer le principe de l'élagage de partitions (partition pruning), où MySQL ne scanne que les partitions nécessaires. Pour des ensembles de données plus importants, cet effet est beaucoup plus prononcé.

Rappel : Vous devriez toujours être dans le shell MySQL et utiliser la base de données sales_data. Si ce n'est pas le cas, utilisez :

USE sales_data;

Pour observer l'impact du partitionnement, nous pouvons utiliser l'instruction EXPLAIN, qui montre le plan d'exécution d'une requête. Plus précisément, EXPLAIN PARTITIONS montrera quelles partitions sont accédées.

Exécutons une requête qui filtre sur la clé de partitionnement (année de sale_date) :

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

Observez la colonne partitions dans la sortie. Elle devrait indiquer que seule la partition p2023 est scannée.

Maintenant, exécutons une requête qui ne filtre pas directement sur la clé de partitionnement, mais sur une autre colonne (amount) :

EXPLAIN PARTITIONS SELECT * FROM sales WHERE amount > 200;

Dans ce cas, comme la condition de la requête ne porte pas directement sur la clé de partitionnement (sale_date), MySQL pourrait avoir besoin de scanner plusieurs partitions, voire toutes, pour trouver les lignes correspondantes. La colonne partitions dans la sortie de EXPLAIN PARTITIONS indiquera quelles partitions ont été considérées. Avec notre petit ensemble de données, il se peut qu'il scanne toujours toutes les partitions.

Pour obtenir une vue plus détaillée du processus d'exécution de la requête et du temps passé, vous pouvez utiliser la fonctionnalité de profilage de MySQL.

Activer le profilage :

SET profiling = 1;

Maintenant, exécutez à nouveau les deux requêtes :

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

Visualisez les résultats du profilage :

SHOW PROFILES;

La sortie listera les requêtes exécutées et leur durée. Vous pouvez ensuite examiner les détails d'une requête spécifique en utilisant son Query_ID :

SHOW PROFILE FOR QUERY [Query_ID];

Remplacez [Query_ID] par l'ID de la requête que vous souhaitez analyser à partir de la sortie de SHOW PROFILES. Examinez les différentes étapes d'exécution et le temps passé dans chacune.

Bien que la différence de temps puisse être négligeable avec notre petit ensemble de données, dans un scénario réel avec des millions de lignes, les requêtes qui peuvent utiliser l'élagage de partitions (comme celle filtrant sur YEAR(sale_date)) seront significativement plus rapides que celles qui doivent scanner plusieurs partitions ou toutes les partitions.

Enfin, désactivez le profilage :

SET profiling = 0;

Cette étape a démontré comment utiliser EXPLAIN PARTITIONS et le profilage pour comprendre comment le partitionnement affecte l'exécution et les performances des requêtes.

Résumé

Dans ce laboratoire, vous avez appris à implémenter le partitionnement MySQL pour les grands ensembles de données afin d'améliorer les performances des requêtes et la gestion des données. Vous avez commencé par créer une base de données et une table partitionnée par plage en fonction de l'année d'une colonne de date. Vous vous êtes ensuite entraîné à interroger des données à partir de partitions spécifiques, en observant comment MySQL utilise l'élagage de partitions (partition pruning) pour optimiser les requêtes. Enfin, vous avez appris à réorganiser les partitions en les ajoutant, en les divisant et en les fusionnant à l'aide de l'instruction ALTER TABLE, et vous avez exploré comment utiliser EXPLAIN PARTITIONS et le profilage pour comprendre l'impact du partitionnement sur la vitesse des requêtes. Le partitionnement est une technique puissante pour gérer et interroger efficacement de grandes tables dans MySQL.

Lorsque vous avez terminé toutes les étapes, vous pouvez quitter le shell MySQL en tapant :

exit;