Cours Master TD5 Optimisation Bases de Données PDF

TD 5 Optimisation des performances d’une base de données PostgreSQL Exercice Préliminaire : Création de la Base de Données de Commerce Électronique Objectif : Créer une base de données de gestion des commandes pour une entreprise de commerce électronique remplie avec 1 00...

TD 5 Optimisation des performances d’une base de données PostgreSQL Exercice Préliminaire : Création de la Base de Données de Commerce Électronique Objectif : Créer une base de données de gestion des commandes pour une entreprise de commerce électronique remplie avec 1 000 000 lignes de données. Étapes et Concepts : 1. Connexion à PostgreSQL : sql \c postgres; 2. Création de la base de données : sql CREATE DATABASE ecommerce; 3. Connexion à la nouvelle base de données : sql \c ecommerce; 4. Création des tables : sql CREATE TABLE clients ( id SERIAL PRIMARY KEY, nom VARCHAR(100), email VARCHAR(100) ); CREATE TABLE produits ( id SERIAL PRIMARY KEY, nom VARCHAR(100), prix DECIMAL ); CREATE TABLE commandes ( id SERIAL PRIMARY KEY,  client_id INT, produit_id INT, date_commande DATE ); 5. Insertion de données aléatoires : sql -- Insertion de 100 000 clients INSERT INTO clients (nom, email) SELECT md5(random()::text), md5(random()::text) || '@example.com' FROM generate_series(1, 100000); -- Insertion de 1 000 produits INSERT INTO produits (nom, prix) SELECT md5(random()::text), random() * 100 FROM generate_series(1, 1000); -- Insertion de 1 000 000 commandes INSERT INTO commandes (client_id, produit_id, date_commande) SELECT (random() * 100000)::int, (random() * 1000)::int, '2023-01-01'::date + (random() * 365)::int FROM generate_series(1, 1000000); Concept : Utiliser generate_series et random() pour générer des données aléatoires. md5(random()::text) produit des chaînes de caractères aléatoires et random() * 100 génère des nombres aléatoires pour les prix. Les dates sont également générées de manière aléatoire. Exercice 1 : Analyse des Requêtes Lentes Objectif : Identifier et optimiser une requête lente. Étapes et Concepts : 1. Activation des logs pour les requêtes lentes : sql SET log_min_duration_statement = 500; Concept : Configurer PostgreSQL pour enregistrer les requêtes dont la durée d'exécution dépasse 500 millisecondes. Cela aide à identifier les requêtes lentes. 2. Exécution d'une requête lente : sql SELECT * FROM commandes WHERE date_commande > '2023-01-01';  Concept : Sélectionner toutes les commandes dont la date est postérieure au 1er janvier 2023. Sans index, cette requête peut être lente. 3. Analyse du plan d'exécution : sql EXPLAIN ANALYZE SELECT * FROM commandes WHERE date_commande > '2023- 01-01'; Concept : EXPLAIN ANALYZE exécute la requête et affiche le plan d'exécution et les temps d'exécution réels. Cela permet d'identifier les opérations coûteuses. Plan d’exécution : Gather (cost=1000.00..11624.23 rows=99 width=16) (actual time=111.378..136.848 rows=0 loops=1) Workers Planned: 2 Workers Launched: 2 -> Parallel Seq Scan on commandes (cost=0.00..10614.33 rows=41 width=16) (actual time=60.539..6 0.539 rows=0 loops=3) Filter: (date_commande > '2023-01-01'::date) Rows Removed by Filter: 333333 Planning Time: 0.071 ms Execution Time: 136.864 ms (8 rows) Une explication de ce plan d'exécution en SQL : 1. Gather Description: La commande Gather est utilisée pour collecter les résultats des travailleurs parallèles. C'est comme une phase finale où tous les morceaux de travail sont regroupés. Coût: Les valeurs de coût (1000.00..11624.23) représentent le coût estimé pour exécuter cette étape, en termes d'accès aux données et de traitement. Temps réel: Ces temps (111.378..136.848 ms) indiquent combien de temps cette étape a réellement pris pour s'exécuter. Lignes: rows=0 signifie qu'aucune ligne n'a été renvoyée après le rassemblement. 2. Workers Planned/Launched Travailleurs planifiés : 2 travailleurs étaient prévus pour l'exécution parallèle.  Travailleurs lancés : Effectivement, 2 travailleurs ont été lancés pour effectuer l'analyse séquentielle parallèle. 3. Parallel Seq Scan Description: Parallel Seq Scan effectue une analyse séquentielle (lecture ligne par ligne) de la table commandes, mais de manière parallèle, c'est-à-dire que le travail est réparti entre plusieurs travailleurs. Coût: 0.00..10614.33 est le coût estimé de cette étape. Temps réel: 60.539 ms est le temps total pris par chaque travailleur pour accomplir cette tâche. Lignes estimées: 41 est le nombre de lignes que cette étape devrait traiter selon les estimations. Lignes réelles: 0 signifie que cette étape n'a renvoyé aucune ligne après le filtre. Boucles: 3 indique le nombre de fois où cette opération a été effectuée (une fois par travailleur). 4. Filtre Condition: (date_commande > '2023-01-01'::date) filtre les commandes passées après le 1er janvier 2023. Lignes supprimées par le filtre: 333333 lignes ne respectaient pas cette condition et ont donc été ignorées. 5. Temps de planification Description: 0.071 ms est le temps nécessaire pour planifier l'exécution de cette requête. Ce temps est généralement très court. 6. Temps d'exécution Description: 136.864 ms est le temps total nécessaire pour exécuter la requête du début à la fin. 4. Optimisation avec un index : sql CREATE INDEX idx_commandes_date_commande ON commandes (date_commande); Concept : Créer un index sur date_commande permet à PostgreSQL de localiser rapidement les lignes pertinentes en utilisant l'index, plutôt que de parcourir toute la table. 5. Relancer la requête EXPLAIN ANALYZE SELECT * FROM commandes WHERE date_commande > '2023- 01-01'; Plan d’exécution : Plan d’exécution : Index Scan using idx_commandes_date_commande on commandes (cost=0.42..8.44 rows=1 width=16) (actua l time=0.005..0.005 rows=0 loops=1) Index Cond: (date_commande > '2023-01-01'::date) Planning Time: 2.211 ms Execution Time: 0.020 ms (4 rows) Explication de plan d’exécution : Structure Générale 1. Index Scan sur la table commandes: o Index utilisé : idx_commandes_date_commande o Coût estimé : 0.42..8.44 o Nombre de lignes estimées : 1 o Largeur : 16 o Temps réel : 0.005..0.005 ms o Nombre de lignes réelles : 0 o Boucles : 1 2. Condition d'index: o Condition : (date_commande > '2023-01-01'::date) 3. Temps de planification: 2.211 ms 4. Temps d'exécution: 0.020 ms Analyse détaillée Index Scan: Cette étape utilise un index (idx_commandes_date_commande) pour rechercher les lignes dans la table commandes. Un scan d'index est plus rapide qu'une analyse séquentielle car il permet de localiser rapidement les lignes pertinentes. Coût: Les valeurs de coût (0.42..8.44) indiquent l'estimation du coût total pour accéder aux données et les traiter. Temps réel: Le temps réel (0.005 ms) montre que cette étape a été exécutée très rapidement. Lignes estimées: 1 ligne était attendue d'être renvoyée par cette étape. Lignes réelles: 0 signifie qu'aucune ligne n'a été renvoyée après l'application de la condition d'index. Condition d'index: Le filtre utilisé (date_commande > '2023-01-01'::date) sélectionne les commandes après le 1er janvier 2023. Temps de planification: 2.211 ms est le temps nécessaire pour planifier l'exécution de cette requête.  Temps d'exécution: 0.020 ms est le temps total nécessaire pour exécuter la requête du début à la fin. Conclusion Le plan d'exécution montre que la requête utilise efficacement un index pour rechercher des commandes après le 1er janvier 2023, mais aucune ligne ne satisfait cette condition dans les données actuelles. Le plan est rapide et efficace, comme en témoignent les temps d'exécution très courts. Exercice 2 : Utilisation des Index Objectif : Améliorer les performances des recherches en utilisant des index. Étapes et Concepts : 1. Lancer la requete: SELECT * FROM clients WHERE nom = ‘96a007831d3efbf4a066f031b50a520’; 2. Lancer la requete: EXPLAIN ANALYZE SELECT * FROM clients WHERE nom = ‘96a007831d3efbf4a066f031b50a520’; 3. Création d'un index sur nom : sql CREATE INDEX idx_clients_nom ON clients (nom); Concept : Un index sur nom permet d'accélérer les recherches basées sur cette colonne en réduisant le nombre de lignes à parcourir. 4. Exécution et analyse de la requête : sql EXPLAIN ANALYZE SELECT * FROM clients WHERE nom = ‘96a007831d3efbf4a066f031b50a520’; Concept : EXPLAIN ANALYZE montre le plan d'exécution réel et les temps d'exécution de la requête, permettant de comparer les performances avant et après l'ajout de l'index. Plan d’exécution : Index Scan using idx_clients_nom on clients (cost=0.42..8.44 rows=1 width=82) (actual time=0.062.. 0.062 rows=0 loops=1) Index Cond: ((nom)::text = '96a007831d3efbf4a066f031b50a520'::text) Planning Time: 0.148 ms  Execution Time: 0.087 ms Explication de plan d’exécution : Structure Générale 1. Index Scan sur la table clients: o Index utilisé : idx_clients_nom o Coût estimé : 0.42..8.44 o Nombre de lignes estimées : 1 o Largeur : 82 o Temps réel : 0.062..0.062 ms o Nombre de lignes réelles : 0 o Boucles : 1 2. Condition d'index: o Condition : ((nom)::text = '96a007831d3efbf4a066f031b50a520'::text) 3. Temps de planification: 0.148 ms 4. Temps d'exécution: 0.087 ms Analyse détaillée Index Scan: Cette étape utilise l'index idx_clients_nom pour rechercher les lignes dans la table clients. Un scan d'index est une méthode efficace pour rechercher des données spécifiques, car il permet de localiser rapidement les lignes pertinentes. Coût: Les valeurs de coût (0.42..8.44) montrent l'estimation du coût total pour accéder aux données et les traiter. Temps réel: Le temps réel (0.062 ms) indique que cette étape a été exécutée très rapidement. Lignes estimées: 1 ligne était attendue d'être renvoyée par cette étape. Lignes réelles: 0 signifie qu'aucune ligne n'a été renvoyée après l'application de la condition d'index. Condition d'index: Le filtre utilisé ((nom)::text = '96a007831d3efbf4a066f031b50a520'::text) cherche à trouver un client dont le nom correspond à cette valeur de texte. Temps de planification: 0.148 ms est le temps nécessaire pour planifier l'exécution de cette requête. Temps d'exécution: 0.087 ms est le temps total nécessaire pour exécuter la requête du début à la fin. Conclusion Le plan d'exécution montre que la requête utilise efficacement un index pour rechercher un client spécifique par son nom, mais aucune ligne ne satisfait cette condition dans les données actuelles. Le plan est très rapide et efficace, comme en témoignent les temps d'exécution très courts. Exercice 3 : Utilisation des Index Combinés Objectif : Améliorer les recherches multi-colonnes avec des index combinés. Étapes et Concepts : 1. Création d'un index combiné sur client_id et date_commande : sql CREATE INDEX idx_commandes_client_date ON commandes (client_id, date_commande); Concept : Un index combiné sur plusieurs colonnes permet d'optimiser les recherches qui filtrent sur plusieurs critères simultanément. 2. Exécution et analyse de la requête : sql EXPLAIN ANALYZE SELECT * FROM commandes WHERE client_id = 12345 AND date_commande > '2023-01-01'; Concept : EXPLAIN ANALYZE montre comment PostgreSQL utilise l'index combiné pour optimiser la requête, en réduisant le nombre de lignes à parcourir. Exercice 4 : Index Partiel Objectif : Utiliser des index partiels pour optimiser les performances. Étapes et Concepts : 1. Création d'un index partiel sur prix : sql CREATE INDEX idx_produits_prix ON produits (prix) WHERE prix > 50; Concept : Un index partiel cible un sous-ensemble de données, réduisant la taille de l'index et améliorant les performances pour les requêtes spécifiques. 2. Exécution et analyse de la requête : sql EXPLAIN ANALYZE SELECT * FROM produits WHERE prix > 50; Concept : EXPLAIN ANALYZE permet de vérifier l'impact de l'index partiel sur les performances de la requête. Exercice 5 : Optimisation des Jointures Objectif : Améliorer les performances des jointures avec des index. Étapes et Concepts : 1. Création des index sur les colonnes de jointure : sql CREATE INDEX idx_commandes_client_id ON commandes (client_id); CREATE INDEX idx_commandes_produit_id ON commandes (produit_id); Concept : Des index sur les colonnes utilisées dans les jointures permettent à PostgreSQL de localiser rapidement les lignes associées, améliorant les performances des jointures. 2. Exécution et analyse de la requête : sql EXPLAIN ANALYZE SELECT c.nom, p.nom, co.date_commande FROM clients c JOIN commandes co ON c.id = co.client_id JOIN produits p ON co.produit_id = p.id WHERE co.date_commande > '2023-01-01'; Concept : EXPLAIN ANALYZE montre comment les index aident à optimiser les performances des jointures. Exercice 6 : Réduire les Scans Séquentiels Objectif : Minimiser les accès disque en réduisant les scans séquentiels. Étapes et Concepts : 1. Identification des requêtes lentes : sql EXPLAIN ANALYZE SELECT * FROM commandes WHERE client_id = 12345; Concept : EXPLAIN ANALYZE permet d'identifier les requêtes qui effectuent des scans séquentiels, indiquant qu'il manque peut-être des index. 2. Création des index nécessaires : sql CREATE INDEX idx_commandes_client_id ON commandes (client_id);  Concept : Créer des index sur les colonnes souvent utilisées dans les filtres réduit le besoin de scans séquentiels, améliorant les performances des requêtes. Exercice 7 : Utiliser EXPLAIN et EXPLAIN ANALYZE Objectif : Comprendre les plans d'exécution pour optimiser les requêtes. Étapes et Concepts : 1. Utilisation de EXPLAIN : sql EXPLAIN SELECT * FROM commandes WHERE produit_id = 567; Concept : EXPLAIN affiche le plan d'exécution estimé sans exécuter la requête. Cela donne une idée du coût vous avez oubli d'autres exercices Ah, vous avez raison ! Voici la suite des exercices, en prenant en compte vos demandes et mes propositions : Exercice 7 : Utiliser EXPLAIN et EXPLAIN ANALYZE (suite) Objectif : Comprendre les plans d'exécution pour optimiser les requêtes. Étapes et Concepts : 1. Utilisation de EXPLAIN : sql EXPLAIN SELECT * FROM commandes WHERE produit_id = 567; Concept : EXPLAIN affiche le plan d'exécution estimé sans exécuter la requête. Cela donne une idée du coût estimé et des opérations que PostgreSQL va réaliser pour exécuter la requête. 2. Utilisation de EXPLAIN ANALYZE : sql EXPLAIN ANALYZE SELECT * FROM commandes WHERE produit_id = 567; Concept : EXPLAIN ANALYZE exécute la requête et montre le plan d'exécution réel, incluant les temps d'exécution précis et le nombre de lignes traitées. Cela aide à comparer les plans estimés et réels pour identifier les problèmes de performance. Exercice 8 : Utilisation des Index GIN Objectif : Améliorer les recherches textuelles avec les index GIN. Étapes et Concepts : 1. Création d'un index GIN sur la colonne nom des produits : sql CREATE INDEX idx_produits_nom_gin ON produits USING gin(to_tsvector('english', nom)); Concept : Les index GIN sont optimisés pour les recherches textuelles. to_tsvector convertit la colonne nom en un vecteur de recherche textuelle que l'index GIN utilise. 2. Exécution et analyse de la requête textuelle : sql EXPLAIN ANALYZE SELECT * FROM produits WHERE to_tsvector('english', nom) @@ to_tsquery('english', 'laptop'); Concept : @@ to_tsquery effectue une recherche textuelle sur la colonne nom en utilisant l'index GIN. EXPLAIN ANALYZE montre comment l'index GIN améliore les performances de la recherche. Exercice 9 : Automatisation de la Maintenance Objectif : Automatiser les tâches de maintenance pour optimiser les performances. Étapes et Concepts : 1. Configuration de autovacuum : sql -- Exemple de paramètres dans postgresql.conf autovacuum = on autovacuum_naptime = 60 Concept : autovacuum permet de nettoyer automatiquement les lignes mortes dans les tables. autovacuum_naptime définit la fréquence d'exécution de cette tâche en secondes. 2. Exécution de VACUUM : sql VACUUM ANALYZE;  Concept : VACUUM ANALYZE nettoie les lignes mortes et met à jour les statistiques de la base de données. Cela permet à PostgreSQL de planifier les requêtes plus efficacement. Exercice 10 : Optimisation de la Configuration Objectif : Adapter la configuration de PostgreSQL pour des performances optimales. Étapes et Concepts : 1. Ajustement des paramètres de configuration : sql -- Exemple de paramètres dans postgresql.conf shared_buffers = 4GB work_mem = 64MB Concept : shared_buffers définit la quantité de mémoire que PostgreSQL utilise pour le cache des blocs partagés. work_mem définit la mémoire disponible pour les opérations internes telles que le tri et les jointures. 2. Redémarrage de PostgreSQL et mesure de l'impact : sql -- Commande pour redémarrer PostgreSQL (dépend du système d'exploitation) sudo systemctl restart postgresql -- Exécution et analyse d'une requête pour mesurer l'impact EXPLAIN ANALYZE SELECT * FROM commandes WHERE client_id = 12345; Concept : Redémarrer PostgreSQL pour appliquer les nouveaux paramètres de configuration et utiliser EXPLAIN ANALYZE pour mesurer l'impact sur les performances des requêtes.