Dans quels cas il est préférable de ne pas utiliser d'index sur les tables ?

[challenger84]

Salut à tous,
Voilà je me suis frotté à cette question lors d'un entretien:
"Dans quels cas il est préférable de ne pas utiliser d'index sur les tables ?".
Quelqu'un a une réponse pertinente qu'on pour répondre à une telle question?

[gregory.broissard]

des tables avec très peu de lignes.

en effet, le temps qu'il balaie le btree, il a plus vite fait de faire un fetch sur la table ou un TableScan.

Ou si toutes les requetes qui vont attaquer ta table ne sont pas suffisamment discriminantes alors l'index ne sert à rien. Qui plus est, l'optimiseur du SGBD peut décider de ne pas utiliser l'index meme si il est present...

[challenger84]

si toutes les requetes qui vont attaquer ta table ne sont pas suffisamment discriminantes alors l'index ne sert à rien

Pourrais-tu m'expliquer plus clairement ce que tu entends par suffisamment discriminantes?

Pour les tables de petites tailles je comprends que l'index n'est pas pertinente mais la question qu'on m'a posé porte plutôt sur la nature des requêtes sur la table.

[elbj]

Bonjour

Je dirai qu'il est préférable de limiter les index sur les tables fortement modifiées (INSERT, UPDATE, DELETE) pour éviter le ralentissement des traitements. Maintenant, l'indexage est une véritable étude qui ne s'improvise pas et il faut étudier au cas par cas en s'aidant des statistiques et plans de requête.

Maintenant, tu es peut-être tombé sur quelqu'un qui croit savoir alors qu'il se trompe et qui considèrera ta réponse comme erronée même si elle est vraie.

Cordialement

Christophe B.

[fsmrel]

A propos des requêtes discriminantes.

On peut interpréter "requête discriminante" comme "requête filtrante", ce qui conduit à la notion de facteur de filtrage.

Supposons que vous utilisiez une table T de un million de lignes, dotée d’une clé primaire K comportant donc autant de valeurs et faisant l’objet d’un index de type "unique". Le facteur de filtrage est optimal et le temps de réponse pour un accès en fonction d’une valeur donnée de cette clé est de l’ordre de dix à vingt millisecondes : deux ou trois accès au disque pour l’index et un accès pour les données de la table.

Supposons maintenant qu’une des colonnes de T, disons C comporte les seules valeurs 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, avec une répartition homogène de ces valeurs.
Si la table est indexée sur C et si vous utilisez la requête suivante :

Select *
From T
Where C = 10

=> Le SGBD va consulter ses statistiques concernant la table T et la colonne C et décidera sans doute d’utiliser l’index associé (10% seulement de lignes à consulter, mais un pourcentage plus élevé de pages de données, en fonction du nombre de lignes par page). Le facteur de filtrage est nettement moins bon, mais sera peut-être acceptable.

Si maintenant la requête est la suivante :

Select *
From T
Where C < 10

Le facteur de filtrage devient très mauvais. Le SGBD comprenant qu’il faut récupérer en gros 90% des lignes de la table, il se passera des services de l’index afin de réduire le nombre des entrées-sorties. En effet, s’il s’en sert, on peut se douter qu’il "balaiera" pratiquement toutes les lignes de la table et toutes les lignes de l’index, alors qu’en shuntant ce dernier, ça sera toujours ça d’économisé.

Dans le même sens, si la colonne C ne comporte que deux valeurs (en proportions égales), disons 1 et 2, on peut prendre les paris qu’un index sur C ne sera jamais utilisé, quelle que soit la requête.

[SQLpro]

Dans quels cas il est préférable de ne pas utiliser d'index sur les tables ?"

La personne qui vous à posé cette question retarde d'au moins 5 ou 10 ans. En effet cette question n'a aucun sens avec les moteurs relationnels actuels :
1) si le cout de recherche dans l'index est plus élevé que la lecture ligne à ligne, l moteur est capable de le voir et lira la table sans tenir compte de l'index
2) le coût d'insertion dans un index est aujourd'hui très minime compte tenu des algorithmes de gestion des structures des SGBDR actuels
3) il convient d'éviter de spécifier à un SGBDR la manière de faire notamment en lui intimant ou en lui interdisant d'utiliser tel ou tel index.

Bref, les recruteurs qui vous ont posés ce genre de questions ne méritent vraisemblableement pas votre personne, puisque vous avez eu la curiosité de tenter d'essayer de comprendre quelque chose qu'ils n'ont jamais remis en cause depuis des lustes dans leur entreprise !

A +

[vdumont]

On m'a toujours dit qu'il fallait éviter les index lorsqu'il s'agit d'une table avec peu d'enregistrements, comme mentionné ci-haut.

Cependant, comme SQLpro l'a mentionné, les SGBD ont beaucoup évolué de nos jours et la plupart des SGBD utilisent des optimizers pour optimisé les requêtes et donc des choix comme ceci ne sont plus vraiment nécessaires.

[SQLpro]

de fmsrel :

Dans le même sens, si la colonne C ne comporte que deux valeurs (en proportions égales), disons 1 et 2, on peut prendre les paris qu’un index sur C ne sera jamais utilisé, quelle que soit la requête.

Faux : si le déséquilibre est important par exemple 90% de 1 et 10% de 2 alors qune requête sur 2 utilisera l'index en recherche. De plus si l'index est couvrant alors il est moins couteux d'utiliser la lecture de l'index en recherche même si c'est 90%...

Exemple :
TABLE des personnes avec ID, NOM, PRENOM, SEXE
Clef primaire ID => index
Index (secondaire ) posé sur SEXE.
Distribution des données : 90% M et 10% F sur sexe.
Les requêtes suivantes vont toutes les deux utiliser l'idnex :

Code :

1
2
3
SELECT *
FROM personnes
WHERE SEXE = 'F'

recherche dans l'index puis lecture des lignes de la table.

Code :

1
2
3
SELECT ID
FROM personnes
WHERE SEXE = 'M'

lecture par balayage de l'index, parce que moins couteux que la lecture de la table...

Pourquoi ?
1) l'index sur sexe est bien moins volumineux que la table et il est ordonnée par sexe. On ne lire donc que 90% du volume de kl'idex.
2) la clef de la table sert de références aux lignes. Elle est donc aussi présente dans l'index secondaire SEXE afin de permettre de retrouver la ligne originale.

Mais ceci n'est pas valable sur certains SGBDR qui fonctionne avec une notion de référence de ligne idépendante de la clef...

A +

[fsmrel]

De SQLPro :

de fmsrel :
Citation:
Dans le même sens, si la colonne C ne comporte que deux valeurs (en proportions égales), disons 1 et 2, on peut prendre les paris qu’un index sur C ne sera jamais utilisé, quelle que soit la requête.
Faux : si le déséquilibre est important par exemple 90% de 1 et 10% de 2 alors qune requête sur 2 utilisera l'index en recherche. De plus si l'index est couvrant alors il est moins couteux d'utiliser la lecture de l'index en recherche même si c'est 90%...

Allons ! Ne soyez pas si péremptoire, SQLPro, relisez bien ce que j’ai écrit : "en proportions égales", c'est-à-dire que dans 50% des cas, la colonne indexée (colonne C dans mon exemple) prend la valeur 1 et dans 50% des cas elle prend la valeur 2. Maintenant, en changeant ces proportions, il est évident que si la valeur 2 représente 10% de l’ensemble, le facteur de filtrage devient bien meilleur et le SGBD pourra préférer utiliser l’index pour traiter les requêtes dans lesquelles on a écrit :

SELECT * FROM T WHERE C = 2

Dans le même ordre d’idées, je n’ai pas parlé d’index "couvrant" (je suppose que vous faites allusion à un index comportant toutes les données nécessaires pour la résolution des requêtes), mais puisque vous y tenez, parlons-en : là encore, il est évident que, pour traiter les requêtes concernées, si le SGBD estime le coût inférieur au balayage de la table, il préférera utiliser la technique du "non matching index scan" (balayage séquentiel des feuilles), surtout s’il y a un Order By et si l’arbre est un B+ (chaînage des feuilles dans l’ordre des clés). Pour une transaction qui attaque une table de cent millions de lignes ça n’est pas génial, mais pour un batch cela peut être utile : balayer de l’ordre de cent mille à cinq cent mille pages au lieu de dix millions, à (disons) quatre millisecondes la page, c’est toujours ça de pris, surtout pour les batchs quotidiens...

[voyageur]

Microsoft donne quelques règles generales ou l'utilisation d'index n'est pas neccessaire :

Dans certaines situations, il est préférable de ne pas utiliser d'index, notamment :

• Si l'index n'est jamais utilisé par l'optimiseur.

• Si plus de 10 à 20 % des lignes doivent être renvoyées.

• Si la colonne contient une, deux ou trois valeurs uniques (faible sélectivité).

• Si la colonne à indexer est longue (> 20 octets).

• Si l'intendance de maintenance de l'index est supérieure aux avantages obtenus

• Si la table est très petite.

[ylarvor]

Hier soir, j'ai regardé une conférence microsoft trés intéressante sur la société WAISSO.
http://www.microsoft.com/france/visi...7-cb2944d5ee86

Monsieur UNMAR précisait que SQL Serveur crée, comme vous le savez sans doute, un index cluster sur la clé primaire automatiquement.

Dans certains cas, il est intéressant de supprimer cet index cluster si par exemple la clé primaire n'est pas utilisée.

En effet, sur une grosse table, un gros index cluster peut consommer des ressources inutilement ( processeur notamment ).

[Luc Orient]

Envoyé par ylarvor

...
Monsieur UNMAR précisait que SQL Serveur crée, comme vous le savez sans doute, un index cluster sur la clé primaire automatiquement.

Dans certains cas, il est intéressant de supprimer cet index cluster si par exemple la clé primaire n'est pas utilisée.

Il me semble qu'il y a ici confusion ici entre la clé primaire et l'index cluster ...

Ce n'est pas forcément la même chose ...

De plus, sans un index sur la clé primaire comment assurer l'unicité de cette clé ?

[fsmrel]

Changer d'index cluster :

Soit T une table de clé primaire PK, composée des colonnes (C1, C2). Supposons que le SGBD (SQL Server en l'occurrence) ait d'office créé un index "primaire" X1 sur ces mêmes colonnes (C1, C2) et que cet index soit cluster.

Supposons que nous voulions que ce soit un nouvel index, X2, claqué sur les colonnes (C3,C4) qui soit cluster.

On peut soumettre en conséquence les instructions suivantes :

ALTER TABLE T DROP CONSTRAINT PK ;

CREATE CLUSTERED INDEX X2 ON T (C3, C4) ;

CREATE UNIQUE NONCLUSTERED INDEX X1 ON T (C1, C2) ;

ALTER TABLE T ADD CONSTRAINT PK PRIMARY KEY (C1, C2) ;

Evidemment, si PK sert de référence pour une clé étrangère, il y a un peu plus de travail, mais le principe reste le même.

[fsmrel]

Envoyé par ylarvor

sur une grosse table, un gros index cluster peut consommer des ressources inutilement

Je précise : seulement si on ne sait pas s’en servir. En effet, lors des jointures, c’est sans doute le seul moyen efficace (en conjugaison avec le partitionnement) pour éviter l’effet I/O bound quand celui-ci commence à se manifester, ce qui est le cas avec des tables de quelque importance et bien sollicitées (Voyez à ce sujet l’échange avec Igou77). Avec sa lampe magique, Aladin avait droit à 3 vœux, là on n’a droit qu’à un seul. Si on fait le mauvais choix au départ, ça sera très difficile de rattraper le coup une fois les applications en production, avec des tables à 20, 50, 100 — et a fortiori au-delà — millions de lignes.

[fsmrel]

Envoyé par Luc Orient

Il me semble qu'il y a ici confusion ici entre la clé primaire et l'index cluster ...
Ce n'est pas forcément la même chose ...

Je dirais même plus : ça n’a strictement rien à voir. La clé primaire est un concept du niveau relationnel, logique. L’index, qu’il soit "primaire", "unique", cluster", est du niveau physique, rayon épicerie. J'en veux pour preuve que ce concept ne fait pas partie du Modèle Relationnel de Données.

Pour mémoire, jusqu'en 1988, DB2 ne nous permettait pas de déclarer des clés primaires et étrangères. Concernant les premières, nous garantissions l’unicité par le truchement des index de type UNIQUE. Pour les secondes, on faisait comme on pouvait, c'est-à-dire par programme.

En juillet 1988, j’ai disposé de la version 2 de DB2, et enfin, avec l’instruction CREATE TABLE j’ai pu coder les clauses PRIMARY KEY et FOREIGN KEY, ce que nous attendions tous évidemment avec impatience. Mais j’ai pu constater qu’IBM ne s’embarrassait pas, en nous imposant un diktat injustifié consistant en la création obligatoire d’un index de type UNIQUE pour chaque clé primaire (la seule justification que je vois : "ça nous fera toujours du développement en moins"). J’estime que la décision de créer un index est du ressort de l’utilisateur et de lui seul (en l'occurrence le DBA). Si j’ai des tables qui occupent plus de 3 pages de données, je créerai un index. Pour la foultitude des petites tables de référence (du genre Pays, Départements, Catégorie juridique, code APE, titre de civilité, type de ceci, type de cela) qui tiennent en une page, il eut été préférable qu’IBM mît à notre disposition une routine ad-hoc, nous aurions fait l’économie d’une diarrhée de fichiers. En l’occurrence l’intégrité conceptuelle a été violée et nous le payons.

Je cite et traduis Ted Codd, père du Modèle relationnel :

Dans un contexte relationnel, les index sont des outils destinés à l'optimisation des performances et seront utilisés uniquement à cette fin... L'unicité des valeurs pour une colonne doit être spécifiée comme étant une propriété seulement de cette colonne et non pas celle d'un index. (E. F. Codd. The Relational Model for Database Management: Version 2 (Reading, Mass.: Addison-Wesley, 1990)).

Peut être un jour assisterons-nous à l’envoi à la décharge de tous ces index, quand par exemple nous utiliserons le TransRelational Model de Steve Tarin pour optimiser l’accès aux tables. Je vous renvoie à ce sujet à l’ouvrage de C.J. Date : An Introduction to Database Systems, 8th edition. (Pearson: Addison-Wesley (International Edition), 2004).