Aller au contenu principal

11 Transaction & Indexation

F pour passer en plein écran ou O pour afficher la vue d'ensemble.
Versions sans animation, plein écran, imprimable.
Objectifs

Comment améliorer les performances d'une base de données ?

Exercices​

Transactions​

Quelles sont les propriétés ACID qu'une transaction doit respecter ?

Réponse
  • Atomicité : soit toutes les requêtes sont exécutées, soit aucune
  • Cohérence : l'état de la base de données est valide avant et après
  • Isolation : les transactions concurrentes ne se perturbent pas
  • Durabilité : les changements sont persistants après la confirmation, même en cas de panne

Ajouter une nouvelle colonne balance de type DECIMAL(5, 2) pour stocker un montant à deux décimales et cinq chiffres au total (de -999,99 à 999,99) à la table person :

Solution
ALTER TABLE person ADD COLUMN balance DECIMAL(5, 2);

Remplir la colonne balance avec 100.00 pour tout le monde :

Solution
UPDATE person SET balance = 100.00;

Transférer 10.00 de la balance de Alice à Bob sans utiliser de transaction et en vérifiant l'état de la base de données après chaque requête :

Solution
UPDATE person SET balance = balance - 10.00 WHERE name = 'Alice';
UPDATE person SET balance = balance + 10.00 WHERE name = 'Bob';

Transférer 10.00 de la balance de Alice à Bob en utilisant une transaction et en vérifiant l'état de la base de données après chaque requête :

Solution
START TRANSACTION;
UPDATE person SET balance = balance - 10.00 WHERE name = 'Alice';
UPDATE person SET balance = balance + 10.00 WHERE name = 'Bob';
COMMIT;

À quel moment les changements sont-ils visibles dans la base de données ?

Solution

Les changements sont visibles après la confirmation de la transaction avec COMMIT et pas avant.

Indexation​

Rechercher toutes les données de la table education qui sont à Lausanne :

Solution
SELECT * FROM education WHERE geo_name = 'Lausanne';

Quel est le coût de cette requête ?

Solution
EXPLAIN SELECT * FROM education WHERE geo_name = 'Lausanne';

Le coût est de 62,06.

QUERY PLAN                                                  |
------------------------------------------------------------+
Seq Scan on education e  (cost=0.00..62.06 rows=15 width=55)|
  Filter: ((geo_name)::text = 'Lausanne'::text)             |

Comment pourrait-on améliorer les performances de cette requête en utilisant un index ? Quel en serait le gain sur le coût ?

Solution

Créer un index sur la colonne geo_name de la table education :

CREATE INDEX idx_geo_name ON education (geo_name);

En expliquant de nouveau la requête :

EXPLAIN SELECT * FROM education WHERE geo_name = 'Lausanne';

On obtient un coût de 29,82.

QUERY PLAN                                                                |
--------------------------------------------------------------------------+
Bitmap Heap Scan on education  (cost=4.40..29.82 rows=15 width=55)        |
  Recheck Cond: ((geo_name)::text = 'Lausanne'::text)                     |
  ->  Bitmap Index Scan on idx_geo_name  (cost=0.00..4.39 rows=15 width=0)|
        Index Cond: ((geo_name)::text = 'Lausanne'::text)                 |

Comment supprimer l'index créé précédemment ?

Solution
DROP INDEX idx_geo_name;

Optimiser la base de données pour la requête suivante :

SELECT * FROM education WHERE variable = 'obl_sec1_20' AND geo_name = 'Yverdon-les-Bains';
Indice 1

Cost = 0.28..8.30

Indice 2

Il est possible de créer un index sur plusieurs colonnes.

Solution

Créer un index sur les colonnes variable et geo_name de la table education :

CREATE INDEX idx_variable_geo_name ON education (variable, geo_name);

Pourquoi cet index est-il plus performant que deux index séparés ?

Solution 2

Une fois que la base de données a trouvé les lignes correspondantes à variable, elle peut directement chercher les lignes correspondantes à geo_name qui sont déjà triées.

DBeaver sample database​

  • Créer le DBeaver sample database.
    • Ouvrir DBeaver
    • Menu Help > Create Sample Base
  • Observer le diagramme entité-relation
    • Clic droit sur la base de données > View Diagrams
  • Décrire ce que la base de données représente (contexte, entités, relations)
Solution

La base de données représente une boutique de musique en ligne. Elle contient les entités suivantes :

  • Track : les musiques disponibles à la vente
    • Organisé par Genre, Album (+ Artist), Media_type
    • Système de playlist pour les musiques favorites
  • Invoice : les factures des clients
    • Contient les InvoiceLine pour chaque musique achetée
    • Lié à Customer pour le client
      • Chaque client a un Employee de référence pour le suivi (support)

PostgreSQL Exercises​

https://pgexercises.com/questions/basic/

Références​

Dernière mise à jour le