Comment fonctionne un index de base de données, et quand en ajouter un aggrave-t-il les choses ?

Un index est une structure triée séparée, en général un arbre B, qui associe des valeurs de colonne à des emplacements de lignes pour que le moteur puisse chercher directement au lieu de tout parcourir. Il accélère les lectures qui filtrent ou trient sur ces colonnes. Il vous coûte à l'écriture, puisque chaque insertion, mise à jour et suppression doit le maintenir, et il coûte du stockage. Les colonnes peu sélectives et les index que rien n'interroge sont en général une perte nette.

Un index est essentiellement une copie triée d'une ou plusieurs colonnes avec des pointeurs vers les lignes, en général un arbre B, pour que le moteur puisse faire une recherche dichotomique au lieu de lire chaque page. Ça transforme un filtre sur une grosse table d'un parcours séquentiel en une poignée de lectures de pages. Le coût, c'est que chaque écriture doit mettre l'index à jour aussi, donc une table avec huit index paie huit fois à l'insertion, et les chargements en masse ralentissent nettement. L'autre chose que je surveille, c'est la sélectivité. Si une colonne est un booléen vrai pour quatre-vingt-dix pour cent des lignes, le planificateur va en général simplement parcourir, parce qu'un accès aléatoire à autant de lignes coûte plus cher qu'une lecture séquentielle. J'ai eu un cas où quelqu'un avait ajouté un index sur une colonne de statut à trois valeurs et rien ne s'est amélioré. Ce qui a réellement corrigé le problème, c'est un index composite sur tenant id et created at, correspondant à la fois au filtre et au tri, pour que la base puisse chercher directement puis lire dans l'ordre sans étape de tri séparée.