Cours Bases de données — modèles, clés, index, transactions, sauvegarde et sécurité

Objectifs d'apprentissage

À la fin de ce cours, l'apprenant peut :

1. situer les familles de bases (relationnelle, documents, clé-valeur, colonnes) et dire à quoi sert chacune ;
2. modéliser un petit domaine : tables, clés primaires et étrangères, cardinalités ;
3. appliquer l'idée de normalisation et reconnaître quand dénormaliser ;
4. expliquer ce qu'apporte un index et ce qu'il coûte ;
5. définir ACID, une transaction et les niveaux d'isolation ;
6. distinguer sauvegarde logique et physique, et pourquoi une sauvegarde non restaurée ne prouve rien ;
7. décrire réplication synchrone et asynchrone, et dire ce que la réplication ne remplace pas ;
8. appliquer le moindre privilège aux accès et repérer les risques d'injection et de sauvegardes exposées.

Prérequis

• Savoir lire un tableau (lignes, colonnes) ; aucune connaissance de SQL n'est exigée (le cours « SQL » du même dossier prolonge celui-ci).
• Pour les exercices pratiques facultatifs : SQLite (fourni avec de nombreux systèmes, non vérifié sur le Mac de Stéphane) ; rien d'autre à installer.

Parcours et chapitres

1. Qu'est-ce qu'une base de données

Une base de données est un ensemble de données organisées, géré par un système de gestion de base de données (SGBD ou « moteur ») qui assure le stockage, la recherche, la cohérence et l'accès concurrent. Un SGBD peut être :

• embarqué : une bibliothèque dans l'application, les données dans un fichier (SQLite en est un exemple) ;
• serveur : un processus séparé auquel on se connecte par le réseau (PostgreSQL, MySQL/MariaDB, SQL Server, MongoDB, Redis).

2. Familles de bases

Éléments vérifiés dans les sources :

• MongoDB : « les données accédées ensemble doivent être stockées ensemble » ; schéma flexible, documents polymorphes ; deux manières de lier les données : imbrication (embedding, dénormalisée) ou référence (normalisée).
• Redis se décrit comme « un serveur de structures de données » : chaînes, hachages, listes, ensembles, ensembles triés, flux, JSON, etc.
• PostgreSQL, SQLite, MySQL et SQL Server sont des moteurs relationnels pilotés par SQL.

3. Le modèle relationnel : tables et clés

• Table (relation) : ensemble de lignes (enregistrements) de mêmes colonnes (attributs).
• Clé primaire : identifie une ligne de façon unique. Elle ne doit être ni nulle ni réutilisée. Une clé naturelle vient du domaine (risque : elle change) ; une clé de substitution (entier, identifiant généré) n'a pas de sens métier mais reste stable.
• Clé étrangère : colonne qui référence la clé d'une autre table ; le moteur peut refuser une valeur sans parent. SQLite, d'après sa documentation, n'applique pas les clés étrangères par défaut (activation à chaque connexion, voir le cours SQL) : vérifier ce point pour chaque moteur.
• Contrainte d'unicité : interdit les doublons sur une ou plusieurs colonnes. Le traitement de NULL dans une colonne unique diffère selon les moteurs (SQLite, PostgreSQL : les NULL sont distincts ; la page SQLite signale que d'autres moteurs, dont SQL Server, se comportent autrement).
• Cardinalités : un à un, un à plusieurs (client vers commandes), plusieurs à plusieurs (nécessite une table d'association portant deux clés étrangères).

4. Modélisation et normalisation

Démarche courante (connaissance générale, non issue des documentations consultées) : lister les entités et leurs règles, puis les relations, puis traduire en tables. La normalisation réduit la redondance :

Exemple : stocker le nom du client dans chaque commande le duplique ; s'il change, il faut tout corriger, sinon les données se contredisent (anomalie de mise à jour). La solution est une table client référencée par commande.

Dénormaliser (dupliquer volontairement) peut accélérer une lecture ou simplifier un modèle (c'est le choix par défaut d'une base de documents), au prix de la cohérence à maintenir. Le faire après mesure, jamais par principe.

5. Types de données et valeurs absentes

Choisir le type le plus précis (entier, décimal exact pour l'argent, texte, date, booléen). SQLite est particulier : typage dynamique, la valeur porte son type et la colonne n'a qu'une « affinité » ; les tables STRICT (depuis 3.37.0) imposent un typage rigide. Les dates y sont stockées en texte, réel ou entier selon la convention choisie (pas de type date dédié). Ne pas supposer ce comportement ailleurs.

NULL signifie « valeur absente ou inconnue », ce n'est ni zéro ni une chaîne vide. Il change les comparaisons, les agrégats et les doublons (cours SQL). Règle : mettre NOT NULL partout où une valeur est obligatoire.

6. Index

Un index est une structure auxiliaire, triée, qui évite de parcourir toute la table pour retrouver des lignes. Sans index : lecture complète (balayage). Avec : recherche ciblée.

• Gain : lectures par égalité, plage, tri, jointure sur la colonne indexée.
• Coût : espace disque, et ralentissement des écritures (chaque insertion ou modification met aussi les index à jour). Trop d'index nuit.
• PostgreSQL documente plusieurs types : B-tree (défaut, égalité et plages), Hash (égalité), GiST, SP-GiST, GIN (valeurs multiples, ex. tableaux), BRIN (grandes tables corrélées à l'ordre physique). SQLite documente les index uniques, partiels (clause WHERE) et sur expression.
• Index composite : l'ordre des colonnes compte (règle générale : utile pour un préfixe de colonnes ; à confirmer dans la documentation du moteur retenu).
• Une clé primaire et une contrainte d'unicité sont en général adossées à un index (comportement courant, à vérifier par moteur).

7. Transactions et ACID

Une transaction regroupe des opérations qui réussissent ou échouent ensemble. Exemple synthétique : virer 100 d'un compte A vers un compte B = débiter A et créditer B ; si la seconde étape échoue, la première est annulée (ROLLBACK).

ACID (définition générale ; MongoDB l'emploie dans sa documentation) :

• Atomicité : tout ou rien ;
• Cohérence : la base passe d'un état valide à un autre état valide (contraintes respectées) ;
• Isolation : des transactions simultanées ne se perturbent pas (voir chapitre 8) ;
• Durabilité : une transaction validée (COMMIT) survit à une panne.

Faits vérifiés :

• SQLite : sans BEGIN, chaque commande s'exécute dans sa propre transaction (mode automatique) ; BEGIN/COMMIT/ROLLBACK, SAVEPOINT pour imbriquer.
• MongoDB : les opérations sur un seul document sont atomiques ; les transactions multi-documents existent (jeux de réplicas, clusters shardés) mais coûtent plus cher en performances ; le manuel recommande un modèle de données qui les évite.

8. Isolation et concurrence

Phénomènes classiques : lecture sale (voir une donnée non validée), lecture non répétable, lecture fantôme, anomalie de sérialisation. Les niveaux standards sont Read Uncommitted, Read Committed, Repeatable Read, Serializable.

9. Sauvegarde et restauration

• Logique : export des données et du schéma sous forme d'instructions ou d'archive (pg_dump, mysqldump, .dump de SQLite). Portable, plus lente à restaurer.
• Physique : copie des fichiers de données (système de fichiers, instantané, sauvegarde du moteur). Rapide, liée à la version et à la plateforme.
• Continue / point dans le temps (PITR) : sauvegarde complète plus journaux d'écriture (PostgreSQL : archivage WAL ; MySQL : journal binaire).

Faits vérifiés :

• PostgreSQL (doc 18.6) : trois approches : export SQL, copie au niveau fichiers, archivage continu avec restauration à un instant donné. pg_dump exporte une base, donne une vue cohérente sans bloquer lecteurs ni écrivains, ne contient pas les rôles ni les tablespaces (pg_dumpall pour ces objets globaux).
• MySQL 8.4 : sauvegardes logiques et physiques, complètes et incrémentales, mysqldump, récupération à un instant via le journal binaire, réplica comme support de sauvegarde.
• SQLite : copier le fichier pendant une écriture est risqué ; la documentation propose l'API de sauvegarde en ligne et VACUUM INTO, et le shell offre .backup et .dump.
• Redis : persistance RDB (instantanés), AOF (journal des écritures), les deux combinés, ou aucune (cache) ; un fichier RDB peut être copié pendant que le serveur tourne.

Règle des sauvegardes : conserver plusieurs copies, dont une hors de la machine (règle courante « 3-2-1 », connaissance générale), chiffrer celles qui contiennent des données sensibles, et tester la restauration dans un environnement isolé : une sauvegarde jamais restaurée n'est pas prouvée.

pg_dump --format=custom --file=sauvegarde-demo.dump <base_de_demo>

• Contexte : exemple de forme pour PostgreSQL (version non connue) ; options à relire dans pg_dump ; <base_de_demo> est un espace réservé. Jamais exécuté par ce cours.
• Droits : un rôle autorisé à lire la base ; la connexion (hôte, rôle) est à fournir par l'environnement, jamais en clair dans une commande documentée.
• Effet : crée un fichier de sauvegarde dans le dossier courant ; lecture seule côté base.
• Vérification : pg_restore --list sauvegarde-demo.dump affiche le sommaire (forme à confirmer dans la doc de pg_restore, non vérifiée) ; surtout, restaurer dans une base de test.
• Retour arrière : supprimer le fichier créé.

10. Réplication et haute disponibilité

La réplication maintient des copies sur d'autres serveurs. Vocabulaire PostgreSQL : serveur primaire (lecture-écriture) et serveurs de secours (standby), « chaud » (accepte des lectures) ou « tiède » ; synchrone : une transaction n'est validée que lorsque les serveurs concernés l'ont enregistrée (pas de perte à la bascule, coût en performances) ; asynchrone : léger retard possible, des transactions peuvent être perdues à la bascule.

Autres notions : bascule (failover), lecture sur répliques (données parfois légèrement périmées), partitionnement horizontal (sharding). MongoDB (jeux de réplicas, clusters shardés) et MySQL/MariaDB (réplicas par journal binaire) proposent leurs propres mécanismes ; MariaDB : page de réplication non consultée (session interrompue), non vérifié.

11. Performances

Démarche : mesurer, puis changer une chose à la fois.

1. Identifier la requête lente (journaux, outils du moteur).
2. Lire son plan d'exécution (EXPLAIN) : balayage complet, index utilisé, tri temporaire.
3. Corriger : index adapté, requête réécrite, volume lu réduit, statistiques à jour.
4. Remesurer sur des données de taille réaliste.

Points de vigilance : requêtes N+1 (une requête par ligne au lieu d'une jointure), SELECT * inutile, transactions longues qui bloquent, mémoire et disque du serveur, latence réseau. Un cache (Redis par exemple) accélère les lectures mais ajoute un problème de cohérence : à décider explicitement.

12. Sécurité des accès

• Moindre privilège : un compte par application ; droits strictement nécessaires (lecture seule si possible) ; jamais le compte administrateur dans une application. PostgreSQL : le concept unique de rôle (utilisateur ou groupe), GRANT/REVOKE, authentification réglée côté serveur (pg_hba.conf).
• Réseau : n'écouter que sur l'interface nécessaire (boucle locale pour un poste de développement), pare-feu, chiffrement TLS des connexions.
• Secrets : mots de passe dans un gestionnaire (Trousseau), pas dans le code, les commits, les URL ni les journaux.
• Injection SQL : ne jamais construire une requête par concaténation de texte saisi ; utiliser des requêtes paramétrées (principe général du domaine).
• Sauvegardes : elles contiennent les mêmes données que la base ; mêmes droits, chiffrement, durée de conservation.
• Environnements : ne pas copier la production vers le développement sans anonymiser.
• Journalisation : consigner les accès sensibles sans y écrire les données.

Exercices et vérifications de fin de cours

1. Familles. Pour un carnet de contacts, un panier d'achats temporaire et un catalogue de fiches très variées, dire quelle famille vous essaieriez en premier et pourquoi.
2. Modèle. Dessiner (papier) les tables d'une bibliothèque : livres, auteurs, emprunteurs, emprunts ; placer clés et cardinalités.
3. Normalisation. Une table commande(id, nom_client, ville_client, produit, prix) : quelles anomalies ? Proposer le découpage.
4. Index. Une table de 10 millions de lignes est filtrée par date : que gagne-t-on avec un index ? que perd-on aux écritures ?
5. Transaction. Écrire en français les étapes d'un virement et dire où placer ROLLBACK.
6. Isolation. Pourquoi PostgreSQL demande-t-il de rejouer une transaction en niveau Serializable ?
7. Restauration. Rédiger la procédure (sans l'exécuter) de test de restauration d'une sauvegarde dans un environnement isolé.
8. Accès. Lister les droits d'un compte applicatif de lecture seule.
9. Pratique facultative (SQLite). Faire le premier exercice du cours SQL (base de démonstration) puis exporter avec .dump.

Critères de réussite

• Placer clés primaires et étrangères sur un schéma et justifier chaque table.
• Expliquer ACID sans jargon et citer un risque d'isolation insuffisante.
• Dire pourquoi une réplique n'est pas une sauvegarde.
• Proposer trois mesures de sécurité d'accès pour un compte applicatif.