Leçons du module (1/4)
Index et B-Trees
Comment une base de données peut-elle trouver un seul enregistrement parmi des millions de lignes en quelques fractions de seconde ?
Par défaut, lorsque nous demandons à SELECT * FROM customers WHERE city = 'Milano', Postgres doit effectuer une analyse séquentielle : il lit physiquement la table entière du disque ligne par ligne, en comparant chaque ville avec "Milano". Lorsqu'une table contient des millions d'enregistrements, ce processus paralyse les performances.
La principale réponse structurelle à ces goulots d'étranglement sont les index.
Index (B-Trees)
Les index fonctionnent exactement comme l’index analytique situé à la fin d’un grand livre. Au lieu de lire tout le livre pour trouver où il est question de « Milan », nous cherchons les dernières pages où le mot « Milan » nous redirige vers [p. 45, 114, 230].
Les bases de données, en coulisses, construisent cette structure de données distincte et très compacte (souvent un "B-Tree") ordonnée par la ou les colonnes en question, contenant des pointeurs directs vers le stockage physique des lignes correspondantes.
-- Creare un indice semplice
CREATE INDEX idx_customers_city ON customers(city);Cette seule opération déclenche le moteur Postgres, qui extraira et indexera toutes les valeurs city en arrière-plan. Une fois terminé, tous les SELECT ... WHERE city = '...' suivants voleront !
Clés primaires et unicité
Saviez-vous que les règles de clé primaire (PRIMARY KEY) et de clé UNIQUE utilisent secrètement des index sous le capot ? Étant donné que la base de données doit garantir qu'un e-mail ou un identifiant n'est jamais dupliqué lors de l'insertion ou de la modification de lignes, PostgreSQL crée automatiquement un index B-Tree implicite par-dessus : de cette façon, les contrôles d'unicité prennent des microsecondes.
C'est pourquoi les jointures sur le champ ID sont incroyablement performantes.
Supprimer un index
Si vous réalisez que des requêtes lourdes ont changé et qu'un index n'est jamais consulté, il est judicieux de le détruire et de récupérer de l'espace vital :
DROP INDEX idx_customers_city;A ton tour
Dans notre e-commerce nous remarquons à travers les tableaux de bord que nous filtrons très souvent les clients par nationalité (la colonne pays dans clients). Créez un nouvel index de base sur la colonne country et nommez-le 'idx_customers_country'.
Afficher l'indice
Utilisez CREATE INDEX index_name ON table_name(column_name);
Solution disponible après 3 tentatives
Explorer les index uniques secondaires
Un index B-Tree peut également être utilisé pour simuler une contrainte UNIQUE au moment de la création et accélérer les recherches, tout en rendant l'intention logique explicite et appliquée. Créez un nouvel index dans lequel vous déclarez le mot-clé UNIQUE. Définissez-le pour qu'il bloque toute inscription indésirable en double d'autres utilisateurs, forçant l'unicité de la colonne « email » de « clients », et nommez-le « idx_unique_email_customer ».
Afficher l'indice
Écrivez CREATE UNIQUE INDEX index_name ON table(column);
Solution disponible après 3 tentatives