11 Transaction & Indexation
Versions sans animation, plein écran, imprimable.
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 Il est possible de créer un index sur plusieurs colonnes. Créer un index sur les colonnes Pourquoi cet index est-il plus performant que deux index séparés ? Une fois que la base de données a trouvé les lignes correspondantes à Indice 2
Solution
variable
et geo_name
de la table education
:CREATE INDEX idx_variable_geo_name ON education (variable, geo_name);
Solution 2
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
- Clic droit sur la base de données >
- 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
- Organisé par
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)
- Chaque client a un
- Contient les
PostgreSQL Exercises
https://pgexercises.com/questions/basic/