Quand ajouter un index ralentit tout

La table des chiffres quotidiens a une clé : le compteur et la date ensemble. Cinq endroits du code lui posent une autre question — non pas « ce compteur au fil du temps », mais « tous les compteurs à ces dates ». La galerie de la page d'accueil, le classement, le plan du site, un graphique sur 90 jours et les chiffres d'état. Sans index sur la colonne de date, chacune de ces requêtes lit les 114 113 lignes.

Cet index rend la galerie dix fois plus rapide. Il n'a pas été ajouté. Voici la mesure qui en a décidé.

Une requête est devenue bien plus rapide

La galerie demande les compteurs qui ont eu des visites aujourd'hui. Sans index sur la date, elle parcourt toute la table ; avec l'index, elle cherche une seule date et lit 443 lignes. De 61,5 millisecondes on est passé à 6,1. Ce n'est pas un résultat subtil, et si cela avait été la seule chose mesurée, l'index serait dans la base aujourd'hui.

Deux requêtes sont devenues plus lentes

Le plan du site demande quatorze jours et a ralenti de 19 %. Les chiffres d'état demandent une année et ont ralenti de 10 %. Dans les deux cas, le planificateur est passé de la clé primaire au nouvel index et a fait le mauvais choix.

C'est la partie qu'il vaut la peine de comprendre, car ce n'est pas un défaut du planificateur. Lire une plage par un index secondaire, c'est trouver les lignes correspondantes dans l'index puis aller chercher chacune d'elles dans la table. Pour une tranche étroite, c'est une aubaine. Pour quatorze jours sur treize mois, cela fait encore beaucoup de lignes récupérées une par une, et un parcours direct de la table — lue dans l'ordre physique, ce qu'aiment les disques et les caches — l'emporte. Le planificateur ne le sait pas. Il voit une plage et un index, et il le prend.

On ne peut pas dire « n'utilise cet index que pour la galerie ». Un index est offert à toute requête qui touche la colonne, et le planificateur s'en servira partout où ses estimations le lui suggèrent. En ajouter un, c'est modifier toutes les requêtes de cette table, pas seulement celle qu'on avait en tête.

Au total : rien

Sur les cinq réunies, 555 millisecondes sont devenues 524. Une amélioration de 1,1×, qui sur un Raspberry Pi servant de toute façon ces pages depuis un cache ne vaut pas un changement de schéma.

Le graphique sur 90 jours semble avoir gagné 9 %. Ce chiffre n'est pas compté, et il vaut la peine de dire pourquoi. Chaque requête a été mesurée trois fois : sans l'index, avec, puis de nouveau sans. Si la seconde passe sans index ne retombe pas près de la première, la différence venait de la machine et non du changement. Pour le graphique sur 90 jours, les deux passes sans index différaient de 13,8 % — plus que l'effet qu'on prétendrait montrer. Cette ligne ne mesure donc rien, et elle est rapportée comme rien.

Comment cela a été mesuré, et pourquoi cela compte

Pas sur la table en service. Le script en fait une copie, travaille sur la copie et la supprime à la fin. Ajouter un index à une table de production pour voir ce qui se passe est une chose qu'on ne peut faire qu'une fois par surprise.

Les requêtes sont les vraies, reprises du code, jointure sur la table des compteurs comprise. Une version antérieure de ce test utilisait un substitut simplifié et donnait une réponse bien plus enthousiasmante : vingt fois plus rapide. Cette requête simplifiée, rien ne l'exécute.

Le chiffre qu'on aurait obtenu

Si l'on avait mesuré de la façon ordinaire — prendre la requête qui semble lente, la chronométrer avant et après — la réponse aurait été « dix fois plus rapide, on y va ». L'index serait entré, la galerie aurait accéléré, le plan du site et la page d'état auraient ralenti, et personne n'aurait fait le lien, parce que personne ne les regardait.

Voilà la forme générale de la chose. Une modification d'une infrastructure partagée ne s'évalue pas sur le cas qui l'a motivée. La question n'est pas « est-ce que cela aide ce que je regarde » mais « qu'est-ce que cela touche d'autre, et qu'est-ce que cela devient ».

Il existe une version qui pourrait marcher : un index portant les deux colonnes, la date en premier, de sorte que la galerie soit servie par l'index seul, sans retour à la table. Peut-être ne tenterait-il pas non plus le planificateur sur les plages larges. Ce n'est pas mesuré, donc ce n'est pas une recommandation — c'est la prochaine chose à mettre sur la copie.