Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

  1. #1
    Expert éminent sénior
    Pourquoi faut-il faire attention aux index SQL ? Utilisation de JMeter et Benerator
    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.
    Vous avez envie de contribuer au sein du Club Developpez.com ?

    Vous êtes passionné, vous souhaitez partager vos connaissances en informatique, vous souhaitez faire partie de la rédaction.
    Il suffit de vous porter volontaire et de nous faire part de vos envies de contributions :
    Rédaction d'articles/cours/tutoriels, Traduction, Contribution dans la FAQ, Rédaction de news, interviews et témoignages, Organisation de défis, de débats et de sondages, Relecture technique, Modération, Correction orthographique, ...etc.
    Vous avez d'autres propositions de contributions à nous faire ? Vous souhaitez en savoir davantage ? N'hésitez pas à nous approcher.

  2. #2
    Membre confirmé
    bravo
    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 !

  3. #3
    Membre du Club
    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.

  4. #4
    Membre confirmé
    Citation Envoyé par chinagirl Voir le message
    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).

  5. #5
    Membre expérimenté
    Citation Envoyé par MichaelREMY Voir le message

    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".

    Citation Envoyé par MichaelREMY Voir le message

    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é.

  6. #6
    Modérateur

    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.
    N'oubliez pas de consulter les FAQ Java et les cours et tutoriels Java

  7. #7
    Membre confirmé
    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.

  8. #8
    Membre expérimenté
    Citation Envoyé par MichaelREMY Voir le message
    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...

  9. #9
    Membre confirmé
    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

  10. #10
    Membre expérimenté
    Citation Envoyé par MichaelREMY Voir le message
    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é.

###raw>template_hook.ano_emploi###