Introduction
Dans ce lab, vous apprendrez à partitionner des tables MySQL contenant de grands volumes de données afin d’améliorer les performances des requêtes et la gestion des données. Vous allez partitionner une table par plages, en utilisant plus précisément la colonne sale_date de la table sales.
Vous commencerez par vous connecter au serveur MySQL et par créer une base de données sales_data. Vous créerez ensuite la table sales et la partitionnerez selon l’année de sale_date, avec des partitions pour 2020, 2021, 2022 et 2023, ainsi qu’une partition pour les dates ultérieures. Les étapes suivantes vous apprendront à interroger des partitions spécifiques, à réorganiser des partitions avec ALTER TABLE et à examiner l’impact du partitionnement sur la vitesse des requêtes.
Remarque : Dans ce lab, vous n’avez besoin d’ouvrir le shell MySQL qu’une seule fois, au début, puis de le quitter à la fin. Exécutez toutes les commandes SQL des étapes suivantes dans la même session MySQL.
Créer une table partitionnée
Dans cette étape, vous allez créer une base de données et une table partitionnée dans MySQL. Le partitionnement permet de gérer de grands volumes de données en divisant une table en parties plus petites et plus faciles à gérer, selon une règle définie. Il peut améliorer considérablement les performances des requêtes, en particulier lorsque celles-ci filtrent les données à l’aide de la clé de partitionnement.
Commencez par ouvrir un terminal dans la VM LabEx. Vous devriez déjà vous trouver dans le répertoire ~/project.
Connectez-vous au serveur MySQL en tant qu’utilisateur root. Cette commande n’est à exécuter qu’une seule fois, au début du lab :
sudo mysql -u root
Vous êtes maintenant dans le shell MySQL. Exécutez toutes les commandes SQL suivantes dans cette même session jusqu’à la fin du lab.
Activez la journalisation de la session afin que les vérifications de l’étape puissent lire les commandes SQL saisies tant que la session reste ouverte :
tee /home/labex/project/partitioning-session.log
Laissez cette journalisation activée jusqu’à la fin du lab. Les commandes SQL saisies dans cette session ne sont pas enregistrées dans l’historique du terminal.
Créez une base de données appelée sales_data pour y stocker la table :
CREATE DATABASE sales_data;
Sélectionnez la base de données que vous venez de créer :
USE sales_data;
Créez maintenant une table sales et partitionnez-la selon l’année de la colonne sale_date. Vous allez créer des partitions pour 2020, 2021, 2022 et 2023, ainsi qu’une partition qui accueillera toutes les dates ultérieures.
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
);
Voici ce que signifie la clause PARTITION BY RANGE :
PARTITION BY RANGE (YEAR(sale_date)): partitionne la table selon les plages de valeurs renvoyées par la fonctionYEAR()appliquée à la colonnesale_date.PARTITION p2020 VALUES LESS THAN (2021): crée une partition appeléep2020. Les lignes dont l’année desale_dateest inférieure à 2021, c’est-à-dire 2020, seront stockées dans cette partition.PARTITION p2021 VALUES LESS THAN (2022): crée une partition appeléep2021pour les données de l’année 2021.PARTITION p2022 VALUES LESS THAN (2023): crée une partition appeléep2022pour les données de l’année 2022.PARTITION p2023 VALUES LESS THAN (2024): crée une partition appeléep2023pour les données de l’année 2023.PARTITION pFuture VALUES LESS THAN MAXVALUE: crée une partition appeléepFuturepour les données dont l’année desale_dateest supérieure ou égale à 2024.MAXVALUEest une valeur spéciale, toujours supérieure à toutes les autres valeurs.
Après avoir exécuté l’instruction CREATE TABLE, vérifiez la structure de la table et ses partitions à l’aide de cette commande :
SHOW CREATE TABLE sales;
Dans le résultat, repérez la clause PARTITION BY RANGE pour confirmer que la table a été créée avec les partitions prévues.
Insérez maintenant quelques exemples de données dans la table sales. MySQL placera automatiquement chaque ligne dans la partition correspondant à la valeur de 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éé une table partitionnée et y avez inséré des données. À l’étape suivante, vous apprendrez à interroger des partitions spécifiques.
Interroger des partitions spécifiques
Dans cette étape, vous allez apprendre à interroger efficacement une table partitionnée en ciblant des partitions spécifiques. C’est l’un des principaux avantages du partitionnement : MySQL peut analyser uniquement les partitions pertinentes, ce qui réduit considérablement la quantité de données traitées et améliore 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, sélectionnez-la avec la commande suivante :
USE sales_data;
Pour interroger une partition spécifique, vous pouvez ajouter une clause WHERE qui filtre les données à partir de la clé de partitionnement. L’optimiseur de requêtes de MySQL sait souvent déterminer quelles partitions sont pertinentes en fonction de cette clause.
Par exemple, pour récupérer toutes les ventes de l’année 2021, exécutez la requête suivante. Elle utilise une condition de plage directe sur sale_date. L’utilisation d’une fonction telle que YEAR(sale_date) dans la clause WHERE peut empêcher MySQL d’élaguer les partitions et l’obliger à les analyser toutes.
SELECT * FROM sales WHERE sale_date >= '2021-01-01' AND sale_date < '2022-01-01';
Pour voir quelles partitions MySQL utilise pour cette requête, exécutez l’instruction EXPLAIN PARTITIONS :
EXPLAIN PARTITIONS SELECT * FROM sales WHERE sale_date >= '2021-01-01' AND sale_date < '2022-01-01';
Dans le résultat de EXPLAIN PARTITIONS, examinez la colonne partitions. Elle devrait afficher p2021, ce qui indique que MySQL analyse uniquement la partition p2021 pour exécuter la 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 réparties sur plusieurs partitions. Par exemple, pour récupérer les ventes des années 2022 et 2023 :
SELECT * FROM sales WHERE sale_date >= '2022-01-01' AND sale_date < '2024-01-01';
Exécutez de nouveau EXPLAIN PARTITIONS pour voir 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 doit afficher 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 |
+----+-------------+-------+---------------+------+---------------+------+---------+------+------+-------------+
Cet exemple montre comment MySQL écarte les partitions non pertinentes pendant l’exécution d’une requête. L’élagage des partitions accélère les résultats, surtout avec de très grandes tables dont l’analyse complète prendrait beaucoup de temps.
Pour connaître le nombre de lignes dans chaque partition, interrogez 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 permet de voir clairement comment les données sont réparties entre les partitions.
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p2020 | 1 |
| p2021 | 2 |
| p2022 | 2 |
| p2023 | 2 |
| pFuture | 1 |
+----------------+------------+
Vous avez interrogé des partitions spécifiques et observé comment MySQL utilise le partitionnement pour optimiser les requêtes.
Réorganiser et gérer les partitions
Dans cette étape, vous allez apprendre à modifier la structure de partitionnement d’une table existante avec l’instruction ALTER TABLE. Cette opération est utile pour adapter votre stratégie 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, sélectionnez-la avec la commande suivante :
USE sales_data;
Supposons que vous vouliez ajouter une partition pour l’année 2024. Pour le moment, les données de 2024 et des années suivantes 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 et doit toujours être la dernière.
Vous devez donc réorganiser la partition pFuture pour la diviser en deux partitions : p2024 pour l’année 2024, et une nouvelle partition pFuture pour les années suivantes.
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 les données de 2024 vers la nouvelle partition p2024, puis redéfinit pFuture pour couvrir les dates à partir de 2025. La ligne dont la valeur sale_date est '2024-01-01' sera déplacée dans p2024.
Vérifiez la nouvelle structure des partitions et le nombre de lignes dans chacune :
SELECT
PARTITION_NAME,
TABLE_ROWS
FROM
INFORMATION_SCHEMA.PARTITIONS
WHERE
TABLE_SCHEMA = 'sales_data' AND TABLE_NAME = 'sales';
La nouvelle partition p2024 devrait apparaître. 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 |
+----------------+------------+
Fusionnez maintenant des partitions. Supposons que vous vouliez regrouper les partitions p2020 et p2021 dans une seule partition appelée p2020_2021.
ALTER TABLE sales REORGANIZE PARTITION p2020, p2021 INTO (
PARTITION p2020_2021 VALUES LESS THAN (2022)
);
Cette commande fusionne les données des partitions p2020 et p2021 dans une nouvelle partition appelée p2020_2021. La clause VALUES LESS THAN (2022) définit la nouvelle limite de cette partition fusionnée.
Vérifiez de nouveau la structure des partitions :
SELECT
PARTITION_NAME,
TABLE_ROWS
FROM
INFORMATION_SCHEMA.PARTITIONS
WHERE
TABLE_SCHEMA = 'sales_data' AND TABLE_NAME = 'sales';
Les partitions p2020 et p2021 ne devraient plus apparaître. Vous devriez voir p2020_2021 avec le nombre total de lignes des deux partitions.
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p2020_2021 | 3 |
| p2022 | 2 |
| p2023 | 2 |
| p2024 | 0 |
| pFuture | 0 |
+----------------+------------+
Enfin, supprimez une partition. Vous pouvez supprimer la partition p2024. Notez que cette opération supprimera également toutes les données qu’elle contient.
ALTER TABLE sales DROP PARTITION p2024;
Vérifiez une dernière fois la structure des partitions :
SELECT
PARTITION_NAME,
TABLE_ROWS
FROM
INFORMATION_SCHEMA.PARTITIONS
WHERE
TABLE_SCHEMA = 'sales_data' AND TABLE_NAME = 'sales';
La partition p2024 ne devrait plus figurer dans la liste.
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p2020_2021 | 3 |
| p2022 | 2 |
| p2023 | 2 |
| pFuture | 0 |
+----------------+------------+
Vous avez réorganisé, fusionné et supprimé des partitions avec ALTER TABLE. Ces opérations montrent la souplesse avec laquelle vous pouvez gérer des tables partitionnées à mesure que vos données évoluent.
Évaluer l’impact du partitionnement sur la vitesse des requêtes
Dans cette étape, vous allez examiner l’impact du partitionnement sur les performances des requêtes. Même si le jeu de données utilisé ici est petit, vous pourrez observer le principe de l’élagage des partitions : MySQL analyse uniquement les partitions nécessaires. Cet effet est bien plus marqué avec de grands volumes de données.
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, sélectionnez-la avec la commande suivante :
USE sales_data;
Pour observer l’impact du partitionnement, utilisez l’instruction EXPLAIN, qui affiche le plan d’exécution d’une requête. Plus précisément, EXPLAIN PARTITIONS indique quelles partitions sont utilisées.
Exécutez une requête qui filtre les données à partir de la clé de partitionnement, c’est-à-dire l’année de sale_date :
EXPLAIN PARTITIONS SELECT * FROM sales WHERE sale_date >= '2023-01-01' AND sale_date < '2024-01-01';
Dans le résultat, examinez la colonne partitions. Elle devrait indiquer que seule la partition p2023 est analysée.
Exécutez maintenant une requête qui filtre sur une autre colonne (amount), et non directement sur la clé de partitionnement :
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 peut devoir analyser plusieurs partitions, voire toutes, pour trouver les lignes correspondantes. La colonne partitions du résultat de EXPLAIN PARTITIONS indique les partitions prises en compte. Avec notre petit jeu de données, MySQL peut tout de même analyser toutes les partitions.
Pour examiner plus en détail le déroulement de l’exécution d’une requête et le temps qu’elle prend, vous pouvez utiliser la fonction de profilage de MySQL.
Activez le profilage :
SET profiling = 1;
Exécutez de 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;
Affichez les résultats du profilage :
SHOW PROFILES;
Le résultat répertorie les requêtes exécutées et leur durée. Vous pouvez ensuite examiner les détails d’une requête précise à l’aide de son Query_ID :
SHOW PROFILE FOR QUERY [Query_ID];
Remplacez [Query_ID] par l’identifiant de la requête que vous souhaitez analyser, tel qu’il apparaît dans le résultat de SHOW PROFILES. Examinez les différentes étapes d’exécution et le temps passé à chacune d’elles.
Avec notre petit jeu de données, l’écart de durée peut être négligeable. En revanche, dans un contexte réel comportant des millions de lignes, les requêtes qui peuvent tirer parti de l’élagage des partitions, comme celle qui filtre selon l’année de sale_date, seront nettement plus rapides que celles qui doivent analyser plusieurs partitions ou toutes les partitions.
Désactivez enfin le profilage :
SET profiling = 0;
Cette étape vous a montré comment utiliser EXPLAIN PARTITIONS et le profilage pour comprendre l’effet du partitionnement sur l’exécution et les performances des requêtes.
Résumé
Dans ce lab, vous avez appris à partitionner des tables MySQL contenant de grands volumes 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 plages selon l’année d’une colonne de date. Vous avez ensuite interrogé des partitions spécifiques et observé comment MySQL utilise l’élagage des partitions pour optimiser les requêtes. Enfin, vous avez appris à réorganiser les partitions en en ajoutant, en les divisant et en les fusionnant avec l’instruction ALTER TABLE. Vous avez également découvert comment utiliser EXPLAIN PARTITIONS et le profilage pour comprendre l’impact du partitionnement sur la vitesse des requêtes. Le partitionnement est une technique efficace pour gérer et interroger de grandes tables dans MySQL.
Une fois toutes les étapes terminées, quittez le shell MySQL en saisissant :
exit;
