Discussion :

[Francis Walter]

Bonjour,

Je vous présente un tutoriel de Antonio Gomes-Rodrigues sur les index SQL : Pourquoi faut-il faire attention aux index SQL ?

Une solution simple pour augmenter les performances au niveau base de données est de maîtriser la gestion des index. Vous avez sûrement lu ou déjà expérimenté l'impact de l'ajout d'un index sur les performances d'une requête.
Malheureusement, il faut bien être sensibilisé sur le nombre d'index sur une table, car ils ont un coût, et en particulier pour les opérations UPDATE, DELETE et INSERT INTO.
Afin de démontrer ce coût, nous allons utiliser le « couteau suisse » de JMeter associé à Benerator.
Avant de commencer, je vous conseille de réviser vos cours sur la gestion des index (comment créer un index, comment il marche…). Attention, car chaque moteur de base de données a ses particularités.

[MichaelREMY]

bravo, ce genre de ptit court façon TP est primordiale pour tout "dba master" à en devenir...

J'ajouterai un bémol quand-même :

Vous avez sûrement lu ou déjà expérimenté l'impact de l'ajout d'un index sur les performances d'une requête.

Malheureusement, il faut bien être sensibilisé sur le nombre d'index sur une table

Plus que l'ajout et le nombres d'indexes, c'est surtout le type d'index (voire le sgbd lui-même) qui va être déterminant.

Pour avoir pratiqué longuement la gestion d'indexes dans Postgres SQL, j'en sais qqchose.
En changeant le type d'index (btree par exemple), on gagne du temps ou en perd
En changeant le type de la clé primaire (donc en régénérant ensuite l'index), on gagne aussi du temps.

et n'oubliez pas une chose, dès fois on gagne du temps, au détriment de l'espace disque. Nombre de fois, j'ai vu dans ma base postgres des tables d'index énormes, à repruger, ou à programmer à purger....

N'hésitez pas un mettre à jour votre sgdb si dans un changelog, vous voyez une ligne comme "improve/fix the index of type.." et que ça vous concerne.

Pour aller plus loin et comprendre la gestion des indexes, je ne peux que conseiller de comprendre la théorie des arbres binaires, les tris, etc.....etc Tout un programme ou un semestre à perdre !

[chinagirl]

Je ne discute pas le fond de l'article mais le fait d'utiliser JMeter avec un nombre de clients égal à 1.
Ne serait-ce pas plus simple de faire un script Groovy par exemple ? Du moins je pense que pour la lisibilité de l'article cela aurait été mieux.

[MichaelREMY]

Je ne discute pas le fond de l'article mais le fait d'utiliser JMeter avec un nombre de clients égal à 1.

Je ne vois pas où est le problème ? si ça rame/ralentit avec 1, rallentira avec 10.

Par contre l'inverse n'est pas vrai (si c'est rapide avec 1, ça ne veut surtout pas dire que ça sera rapide avec 10).

Après le faire avec x clients virtuels, c'est surtout pour estimer le temps d'accès partagé des index et la mise à disposition par le SGDB, et non pas la performance d'un choix index (ce qui est le but de l'article).

[esperanto]

Plus que l'ajout et le nombres d'indexes, c'est surtout le type d'index (voire le sgbd lui-même) qui va être déterminant.

Oui, et à mettre en relation avec les requêtes qui vont être utilisées le plus souvent, donc avec le code des applications clientes.
Pour prendre un exemple volontairement absurde, inutile de créer un index sur un champ qui n'intervient jamais dans les clauses WHERE.
Au besoin, penser à utiliser la commande EXPLAIN pour vérifier que les index sont correctement utilisés par les requêtes utilisées dans la "vie réelle".

Pour avoir pratiqué longuement la gestion d'indexes dans Postgres SQL, j'en sais qqchose.

Justement, j'ai eu une fois un cas étrange qu'il semble opportun de soumettre ici. J'avais une base contenant des textes, que j'ai voulu indexer pour faire des recherches floues (phrases approchantes). Bizarrement mes EXPLAIN ne mentionnaient jamais l'index... jusqu'à ce que la table dépasse les dix mille lignes, alors seulement ils ont été utilisés!
Comme il s'agissait d'une base destinée à contenir un petit nombre de données mais souvent modifié, au final en production j'ai choisi de ne pas indexer du tout.
Au pire, créer un index sur une table de dix mille lignes prend moins de temps que celui qui est perdu pendant les dix mille INSERT qui ont précédé.

[OButterlin]

Sur le fond, je suis d'accord, ce n'est pas forcément judicieux de créer des indexes. Comme dit, tout dépend du nombre d'indexes, du nombre de mise à jour de la table, etc...
Ça dépend aussi beaucoup du nombre d'enregistrements de la table (ou les tables) liée(s) aux requêtes... à partir d'un certain nombre, il devient judicieux de créer certains indexes...
Ensuite, certaines base de données se comportent mieux que d'autres. Une de mes préférée de ce point de vue est DB2 UDB (ou DB2/400). Là, on peut vraiment laisser le SGBDR se débrouiller, il fait ça très bien.

[MichaelREMY]

Pour prendre un exemple volontairement absurde, inutile de créer un index sur un champ qui n'intervient jamais dans les clauses WHERE.

Si si, ça sert pour les ORDER BY, les GROUP BY, les HAVING , l'absence de WHERE n'exclue absolument pas l'utilisation d'index.

par contre je rejoins ce que tu as dit plus bas sans le faire exprès, le nombre de tuples exige qu'on se demande si c'est utile ou pas de mettre un index sur un champ qui n'est pas clé.

ps : à l'époque, il y avait tout un tas de chose à faire dans postgres pour qu'un index posé en cours d'utilisation d'une base (donc pas au début initial) soit fonctionnel, comme une purge/vacumn, reindexing...Etc. Et ces tâches (si jamais faîtes manuellement par toi - moi je le faisais manuellement avec pgadmin), ne sont excécutés automatiquement qu'au bout d'un certain temps ou au bout d'un certain nombre de tuples (suivant la version et la config de base de postgres que tu as installé). ça explique peut-être ton cas : l'index s'est mis à marcher soudainement après avoir dépassé un seuil de données lui déclenchant une purge/réindex.

[esperanto]

Si si, ça sert pour les ORDER BY, les GROUP BY, les HAVING , l'absence de WHERE n'exclue absolument pas l'utilisation d'index.

OK j'aurais dû écrire "pour un champ qui n'intervient que dans la clause SELECT", mais j'avais peur que certains me parlent alors du INSERT...
[AJOUT]Par exemple dans ma base j'ai des phrases dans un champ et leur traduction dans un autre; je sais que toutes mes requêtes filtreront toujours par la langue source, alors que la traduction ne sert qu'à l'affichage, donc dans le SELECT.

ps : à l'époque, il y avait tout un tas de chose à faire dans postgres pour qu'un index posé en cours d'utilisation d'une base (donc pas au début initial) soit fonctionnel, comme une purge/vacumn, reindexing...Etc. Et ces tâchent (si jamais faîtes manuellement par toi - moi je le fais manuelle avec pgadmin), ne sont excécutés automatiquement qu'au bout d'un certain temps ou au bout d'un certain nombre de tuples (suivant la version et la config de base de postgres que tu as installé). ça explique peut-être ton cas : l'index s'est mis à marcher soudainement après avoir dépassé un seuil de données lui déclenchant une purge/réindex.

Intéressant, ce serait bien d'avoir un lien vers une doc expliquant ce genre de cas.
Précision importante : quand je dis que l'index n'était pas utilisé, je parle bien de ce que me disait le EXPLAIN, je n'ai pas extrapolé à partir d'un benchmark par exemple. Dommage que cette commande n'explique pas les raisons des choix de l'optimiseur...

[MichaelREMY]

même pour un champ qui n'intervient que dans le SELECT, on ne sait jamais ça peut être utile , ça peut être une requête imbriquée dans le select, une fonction programmée (issue d'une procédure stockée), une vue dynamique, qui utilise un champs que tu ne vois pas.

pour revenir à ton problème-cas, je me souviens qu'à l'époque, quand on ajoutait un index dans une relation, si l'exécution était instantannée alors que la tablet était big, c'est que l'index n'était pas encore pris en compte (car ça demande un temps de recharge/vacum/réindex).

aujourd'hui, postgres a bien évolué, peut-etre qu'ils ont ajouté des triggers conditionnels (ce serait une super idée) genre : active l'index dès qu'on dépasse x tuples, ou bien change la méthode btree de l'index Y dès que Z ...etc

[esperanto]

même pour un champ qui n'intervient que dans le SELECT, on ne sait jamais ça peut être utile , ça peut être une requête imbriquée dans le SELECT, une fonction programmée (issue d'une procédure stockée), une vue dynamique, qui utilise un champs que tu ne vois pas.

Un champ qu'on ne voit pas? Si je n'ai pas une vue complète de la base de données ça veut dire que je n'en suis pas l'administrateur et qu'alors ce ne sera pas à moi de décider de créer un index ou pas.
A l'inverse un administrateur peut toujours, à défaut de connaitre le code source des applis clientes, activer un log au niveau de la base pour avoir la liste des ordres qu'elle reçoit pendant un certain laps de temps, et donc en déduire quels champs optimiser en priorité.