Réponse

Les index sont des structures de données spéciales (la plupart du temps basées sur des arbres B) qui servent à rechercher rapidement des données par une colonne ou un ensemble de colonnes d'une table. Les index accélèrent l'extraction, le tri et le filtrage, réduisant le nombre de lignes examinées.

Types d'index :

• Ordinaires (B-tree, hash)
• Composés (multi-colonnes)
• Uniques (assurent l'unicité des valeurs)
• Couvrants (covering)
• En texte intégral (full-text)

Les index accélèrent :

• WHERE ... = ...
• JOIN par colonne indexée
• ORDER BY et GROUP BY par colonne indexée

Peuvent ralentir :

• Insertion, mise à jour, suppression — les index nécessitent des opérations supplémentaires pour maintenir leur structure.
• Si les requêtes utilisent souvent des colonnes pour lesquelles il n'y a pas d'index, les index seront inutiles, et lors des insertions massives — même nuisibles.

Exemple de création d'index :

CREATE INDEX idx_user_email ON users (email);

Exemple où l'index n'aide pas :

SELECT * FROM users WHERE lower(email) = 'test@example.com';
-- si l'index est construit sur email, mais la requête utilise la fonction lower(email), l'index n'est pas utilisé!

Question piège

Si l'on ajoute un index à toutes les colonnes de la table, tous les SELECT seront-ils toujours accélérés ?

Réponse :
Non. Les index accélèrent uniquement les requêtes où le filtrage ou le tri se produit strictement par la colonne indexée sans application de fonctions ou d'opérations empêchant l'utilisation de l'index. Un nombre excessif d'index ralentit non seulement les INSERT/UPDATE/DELETE, mais occupe également beaucoup de place, et certaines requêtes complexes peuvent même contourner les index (par exemple, lors de l'analyse de plage avec une expression).

Exemple :

SELECT * FROM orders WHERE year(order_date) = 2023;
-- si l'index est seulement sur order_date, l'index ne fonctionne pas à cause de la fonction year()

---

Histoire

Dans un système de messagerie électronique, un déclencheur a été créé, qui créait un index pour chaque champ souvent utilisé. Après six mois, les performances du système ont chuté — chaque insertion ou modification de ligne prenait 4 à 5 fois plus de temps. Après un audit, le nombre d'index a été réduit, le système s'est accéléré.

---

Histoire

Sur une plateforme publicitaire, des requêtes SELECT avec un filtre sur substring(url, 1, 10) étaient fréquentes. Malgré l'index sur url, SQL n'a pas utilisé l'index à cause de la fonction substring. La solution a consisté à introduire un champ séparé pour ce type de sélection et un index sur celui-ci.

---

Histoire

Dans un programme de fidélité, un index composite sur les champs (customer_id, shop_id) a été créé. Lors de requêtes uniquement sur shop_id, l'index n'était pas utilisé, et un scan complet de la table (full scan) avait lieu. Cela a conduit à la perte des avantages de l'index lors du calcul des bonus. Une optimisation a aidé : un index séparé sur shop_id.