1. Mécanismes d'Indexation

1.1 Principe fondamental des index

Un index est une structure de données associée aux colonnes d'une table, conçue pour optimiser les performances des requêtes de lecture. Tout comme le sommaire d'un ouvrage technique permet de localiser rapidement un chapitre sans parcourir l'intégralité du livre, un index de base de données réduit considérablement l'espace de recherche du moteur SQL.

Sans index, le moteur effectue un balayage complet de la table (Full Table Scan), comparant chaque ligne séquentiellement. Avec un index, le moteur utilise une structure arborescente (généralement un B-Tree équilibré) pour localiser directement les données cibles. Les données dans un B-Tree sont triées, ce qui permet des recherches par intervalles extrêmement rapides via un parcours infixe.

1.2 Implémentation et stockage

Chaque enregisterment d'une table possède une adresse physique sur le disque. L'index agit comme un pointeur vers cette adresse. Le stockage physique de l'index dépend du moteur de stockage utilisé :

• InnoDB : Les index sont stockés dans le tablespace (fichiers .ibd).
• MyISAM : Les index sont stockés dans des fichiers dédiés (.MYI).
• Memory : Les index résident directement dans la mémoire vive.

Note : Les colonnes définies comme clé primaire (Primary Key) ou avec une contrainte d'unicité (UNIQUE) bénéficient automatiquement de la création d'un index par le système.

1.3 Critères de création d'un index

L'ajout d'index n'est pas anodin et consomme des ressources lors des opérations d'écriture (INSERT, UPDATE, DELETE) car l'arbre doit être rééquilibré. Il est recommandé de créer des index lorsque :

1. Le volume de données de la table est substantiel.
2. La colonne est fréquemment utilisée dans les clauses WHERE, JOIN ou ORDER BY.
3. La colonne subit peu de modifications (faible taux d'opérations DML).

1.4 Syntaxe de gestion des index

Création d'un index sur la colonne email de la table clients :

CREATE INDEX idx_clients_email ON clients(email);

Suppression de l'index :

DROP INDEX idx_clients_email ON clients;

1.5 Analyse de l'exécution des requêtes

L'instruction EXPLAIN permet de vérifier si le moteur utilise correctement les index. Voici un exemple d'analyse après la création d'un index :

EXPLAIN SELECT * FROM clients WHERE email = 'marie.dupont@example.com';

+----+-------------+---------+------+-----------------+-----------------+---------+-------+------+-------------+
| id | select_type | table   | type | possible_keys   | key             | key_len | ref   | rows | Extra       |
+----+-------------+---------+------+-----------------+-----------------+---------+-------+------+-------------+
|  1 | SIMPLE      | clients | ref  | idx_clients_email| idx_clients_email| 33     | const |    1 | Using where |
+----+-------------+---------+------+-----------------+-----------------+---------+-------+------+-------------+

Le type ref indique une recherche par index, contrairement au type ALL qui signale un balayage complet de la table.

1.6 Cas d'invalidation des index

Même si un index existe, l'optimiseur de requêtes peut décider de ne pas l'utiliser dans les situations suivantes :

• Caractère générique en début de chaîne : WHERE nom LIKE '%dupont' empêche l'utilisation du B-Tree.
• Conditions OR non indexées : Si une condition OR porte sur une colonne non indexée, l'index de l'autre colonne est ignoré.
• Non-respect du préfixe gauche (Index composites) : Pour un index sur (categorie, prix), une recherche uniquement sur prix n'utilisera pas l'index.
• Opérations arithmétiques : WHERE prix + 10 = 60.00 invalide l'index sur la colonne prix.
• Utilisation de fonctions : WHERE UPPER(email) = 'TEST@EXEMPLE.COM' force un balayage complet.

2. Les Vues (Views)

2.1 Définition et utilité

Une vue est une table virtuelle dont le contenu est défini par une requête SQL (DQL). Elle permet d'abstraire la complexité du modèle de données, de restreindre l'accès à certaines colonnes sensibles et de simplifier les requêtes répétitives en les encapsulant.

2.2 Création et suppression

CREATE VIEW vue_commandes_clients AS
SELECT c.nom_client, co.date_commande, co.montant_total
FROM clients c
JOIN commandes co ON c.id_client = co.id_client;

DROP VIEW vue_commandes_clients;

2.3 Opérations DML sur les vues

Il est possible d'effectuer des opérations d'insertion, de mise à jour ou de suppression via une vue, à condition que celle-ci soit modifiable (pas de fonctions d'agrégasion, de DISTINCT, ou de jointures complexes). Ces opérations répercutent directement les modifications sur les tables sous-jacentes.

UPDATE vue_commandes_clients 
SET montant_total = 150.00 
WHERE nom_client = 'Jean Martin' AND date_commande = '2023-10-01';

3. Administration et Sauvegarde (DBA)

3.1 Exportation des données

L'outil mysqldump permet de générer un script SQL contenant la structure et les données de la base. Cette commande s'exécute depuis le terminal du système d'exploitation :

mysqldump -u administrateur -p ecommerce_db > /backups/ecommerce_backup.sql

3.2 Importation des données

Pour restaurer la base, il faut d'abord la créer, puis exécuter le script via la commande SOURCE dans le client MySQL :

CREATE DATABASE ecommerce_db;
USE ecommerce_db;
SOURCE /backups/ecommerce_backup.sql;

4. Normalisation et Conception de Bases de Données

4.1 Première Forme Normale (1FN)

Une table est en 1FN si elle possède une clé primaire et si toutes ses colonnes sont atomiques (non divisibles). Par exemple, une colonne telephones contenant plusieurs numéros séparés par des virgules viole cette règle. Il faut séparer ces données dans une table dédiée pour garantir l'atomicité.

4.2 Deuxième Forme Normale (2FN)

La 2FN exige que la table soit en 1FN et qu'aucune colonne non-clé ne dépende d'une partie seulement de la clé primaire (absence de dépendance partielle). Cela s'applique principalement aux tables avec des clés primaires composites.

Exemple : Une table lignes_commandes avec la clé composite (id_commande, id_produit) ne doit pas contenir le nom_produit, car ce dernier ne dépend que de id_produit. Il faut extraire les produits dans une table séparée.

4.3 Troisième Forme Normale (3FN)

La 3FN impose que la table soit en 2FN et qu'aucune colonne non-clé ne dépende d'une autre colonne non-clé (absence de dépendance transitive).

Exemple : Dans une table employes, si l'on stocke id_departement et nom_departement, le nom du département dépend de l'identifiant du département, et non directement de l'employé. Il faut créer une table departements distincte.

4.4 Règles de conception des relations

Lors de la modélisation des relations entre les entités, les structures suivantes doivent être appliquées :

• Relation Un-à-Plusieurs (1:N) : Deux tables sont nécessaires. La clé primaire de l'entité "Un" est ajoutée comme clé étrangère dans la table de l'entité "Plusieurs".
• Relation Plusieurs-à-Plusieurs (M:N) : Trois tables sont requises. Une table de jointure centrale est créée, contenant deux clés étrangères référençant les deux entités principales.
• Relation Un-à-Un (1:1) : Deux tables sont utilisées pour séparer des données volumineuses ou sensibles. La clé étrangère est placée dans l'une des tables et doit impérativement posséder une contrainte UNIQUE.