Indexation SQLExplained

31 of 31 rows

Concept↕	Explication↕	Astuce↕
Types d'index
⤢B-tree	Arbre équilibré pour les égalités, les plages et les scans triés. Valeur par défaut dans la plupart des bases.	Utilisez pour =, <, >, BETWEEN, ORDER BY. Évitez pour la recherche plein texte.
⤢Clustered	Index dont le niveau de feuilles est la table elle-même. Les lignes sont stockées dans l'ordre de la clé. Un seul par table.	InnoDB s'organise autour de la clé primaire. Les tables SQL Server sans index clusterisé sont des heaps.
⤢Nonclustered	Structure séparée contenant les clés plus un pointeur vers la table de base. Plusieurs par table.	Sur un heap il stocke un ID de ligne. Sur une table clusterisée il stocke la clé de clustering.
⤢Hash	Table de hachage pour les recherches par égalité uniquement. Correspondance exacte en temps constant.	À utiliser seulement pour les opérations = ; les plages et le tri exigent un B-tree. Le hash memory-optimized de SQL Server demande un bucket count proche du nombre de lignes.
⤢GIN	Generalized Inverted Index. Associe des valeurs à des lignes. Conçu pour les tableaux et le plein texte.	Utilisez pour jsonb, les tableaux, tsvector, la recherche par trigramme. Plus lent à construire qu'un B-tree.
⤢GiST	Generalized Search Tree. Extensible pour les types non scalaires et les requêtes de chevauchement.	Utilisez pour les plages géométriques, kNN, les contraintes d'exclusion. pg_trgm accélère LIKE.
⤢SP-GiST	GiST à partitionnement spatial. Découpe les données en régions sans chevauchement via des arbres radix ou quad.	Utilisez pour les points, les préfixes IP et la correspondance par préfixe. Pas pour le chevauchement ; utilisez GiST.
⤢BRIN	Block Range Index. Stocke min/max par plage de blocs. Empreinte minuscule.	À utiliser sur de grandes tables physiquement triées. Bon marché à construire, faible sélectivité.
⤢Columnstore	Index stocké en segments de colonnes au lieu de lignes. Forte compression, exécution par lots.	Utilisez pour les scans analytiques sur beaucoup de lignes et peu de colonnes. Les lectures ligne à ligne sont lentes.
⤢Full-text	Index inversé de mots dédié dans MySQL et SQL Server. Construit par colonne texte.	Utilisez MATCH AGAINST ou CONTAINS. Le plein texte de Postgres utilise GIN sur tsvector à la place.
⤢Bitmap	Index Oracle stockant un bitmap par valeur distincte. Chaque bit marque une ligne.	À utiliser sur des colonnes de faible cardinalité dans des entrepôts à lecture intensive. Les écritures concurrentes se sérialisent par bitmap.
⤢Descending	Colonne d'index déclarée DESC. Les clés de cette colonne sont stockées en ordre inverse.	Nécessaire pour les tris mixtes comme (a ASC, b DESC). Les tris DESC simples peuvent aussi lire un index normal à l'envers.
⤢Invisible	Index de MySQL 8 ignoré par l'optimiseur. Toujours maintenu à chaque écriture.	Masquez un index avant de le supprimer pour mesurer l'impact. Rendez-le visible à nouveau sans reconstruction.
Conception
⤢Composite	Index multi-colonnes. Les colonnes de tête doivent suivre l'ordre des filtres de la requête.	Ordonnez par égalité d'abord, puis par plage. Deux index simples ne remplacent pas un composite.
⤢Partial	Index sur un prédicat WHERE. SQL Server l'appelle filtered index. Ne couvre que les lignes correspondantes.	Indexez seulement les lignes active='t'. Divise par deux la taille et le coût d'écriture.
⤢Covering (INCLUDE)	B-tree avec des colonnes non-clé ajoutées. Les scans index-only évitent les allers-retours au heap.	Ajoutez les colonnes souvent lues via INCLUDE. Garde l'index triable.
⤢Unique	Index qui interdit les valeurs dupliquées. Rejette les inserts en collision.	Utilisez pour les clés naturelles et les contraintes un-à-un. UNIQUE NULL autorise plusieurs NULL.
⤢Expression	Index construit sur une fonction des colonnes plutôt que sur les colonnes elles-mêmes.	Indexez lower(email) ou date_trunc('day', created_at). La requête doit répéter l'expression exacte.
⤢Opclass	Classe d'opérateurs. Définit comment un index compare et stocke le type d'une colonne.	Réglez varchar_pattern_ops pour LIKE 'abc%' sans repli de casse. Les valeurs par défaut conviennent à l'égalité et aux plages.
⤢Index-only scan	Plan qui répond à une requête depuis les seules pages de l'index. Le heap n'est jamais lu.	Exige chaque colonne lue dans l'index ou la liste INCLUDE. Le vacuum maintient la visibility map à jour.
⤢Foreign keys	Les colonnes de clé étrangère n'obtiennent aucun index automatique sur la table référençante.	Indexez chaque colonne de FK par laquelle vous joignez ou supprimez. Les suppressions en cascade scannent la table enfant sans index.
Maintenance
⤢EXPLAIN	Inspecteur de plan. Montre les scans séquentiels, les choix d'index, les estimations de coût.	Utilisez EXPLAIN (ANALYZE, BUFFERS). Un Seq Scan sur une table de 10M lignes signale un index manquant.
⤢ANALYZE	Échantillonne les colonnes de la table pour construire les statistiques du planificateur. Les histogrammes guident le choix d'index.	Lancez après les chargements massifs. Des stats périmées provoquent des seq scans sur des colonnes indexées.
⤢Over-indexing	Chaque index ajoute un coût d'écriture. Inserts, updates et deletes paient pour chaque index.	Supprimez les index inutilisés. Suivez l'usage dans pg_stat_user_indexes.
⤢pg_stat_user_indexes	Vue Postgres de l'usage par index. Compte les scans et tuples lus depuis la dernière réinitialisation.	Filtrez sur idx_scan = 0 pour trouver les index inutilisés. Réinitialisez les compteurs avec pg_stat_reset().
⤢CREATE INDEX CONCURRENTLY	Construit un index sans bloquer lectures ni écritures. Nécessite deux scans de table.	Impossible dans une transaction. Une construction échouée laisse un index INVALID à supprimer.
⤢DROP INDEX	Supprime un index et libère son stockage. Prend un verrou exclusif par défaut.	Utilisez DROP INDEX CONCURRENTLY sur les tables chargées. Vérifiez les statistiques d'usage avant de retirer.
⤢REINDEX	Reconstruit un index gonflé. Récupère l'espace des tuples morts.	Utilisez REINDEX CONCURRENTLY en production. Sans lui, les verrous bloquent les écritures.
⤢Bloat	Tuples morts et trous de pages qui s'accumulent dans les tables MVCC et leurs index.	Mesurez avec pgstattuple. Vacuum souvent ; REINDEX quand l'espace mort domine.
⤢Fillfactor	Pourcentage d'une page rempli à l'écriture. L'espace libre absorbe les mises à jour futures.	Baissez-le à 70-90 sur les tables à mises à jour fréquentes. Les pages pleines forcent des page splits.
⤢CLUSTER	Réécrit la table physiquement ordonnée selon un index. Opération ponctuelle.	Se combine avec BRIN pour des blocs triés. L'ordre n'est pas maintenu lors des écritures suivantes.