Discussion :

[Kropernic]

Bonjour,

Malgré plusieurs lectures de l'article de sqlpro sur les indexes, je n'arrive toujours pas à déterminer les indexes appropriés à créer sur une table. Je ne parviens pas à saisir la logique de réflexion qui amène à la création des indexes.

J'en profite pour poser une autre question qui est en plein de le sujet : Faut-il créer des index sur les foreign key? J'ai déjà lu un article de sqlpro sur la question mais j'avoue que jusqu'ici, ça reste flou. Dans certains cas la réponse est oui et non dans d'autres.

[Rei Ichido]

Concrètement, la logique derrière la création d'index, c'est "est-ce qu'on en a besoin ?", ce qui dépend totalement des requêtes qui feront appel à la table.

À partir de là, il faut avoir une vue un peu synthétique des requêtes qui pourront être passées sur la table - et faire des audits de performance de temps en temps pour prévenir les problèmes. Concrètement, on arrive souvent à avoir une idée des index : il y a toujours des colonnes "importantes" outre les clés primaires : typiquement des colonnes du genre statut, date de clôture, clés étrangères vers un critère de recherche [utilisateur, société, etc.].

La principale difficulté réside dans la synthèse des besoins, et la création d'index sur plusieurs colonnes. Beaucoup de débutants pensent que si on a un critère sur deux colonnes, créer deux index (un par colonne) répond au besoin, alors qu'en fait l'optimal consiste souvent dans ce cas à créer un index sur les deux colonnes (si on ne considère que la requête en question). Ensuite vient la question de l'ordre des colonnes dans l'index, notamment pour pouvoir utiliser un seul des deux critères ... et/ou éventuellement avoir un résultat ordonné. Et puis bien sûr, le fait qu'on ne va pas faire un index par requête, et donc qu'on essaye de mutualiser. Je pense que tout ça doit être bien expliqué par SQLPro dans son article.

[Kropernic]

Bin la théorie (ce que tu expliques), ça j'ai bien compris. C'est pour appliquer que ça pose problème...

Je vais tenter de créer un exemple...

Soit les tables :
- A avec les colonnes A1, A2, A3, A4 et A5 où A1 est la clef primaire
- B avec les colonnes B1, B2, B3 et A1 ou B1 est la clef primaire et A1 une clef étrangère.
Aucun index crée sur ces deux tables mis à part les indexes clusters dûs au clefs primaires.

Avec la requête suivante :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
 
SELECT
    A.A1,A2,A3,B2
FROM
    A INNER JOIN B ON A.A1 = B.B1
WHERE
    B.B2 = @B2

Quel serait le ou les indexes adéquat pour cette requête ?

[Sergejack]

La grande majorité du temps avec des indexes sur les clés étrangères est utile.

Si la volumétrie de vos tables DB est très faible, la probabilité que vos indexes servent est quasi nul.
Donc, ne faites pas trop de tests et ne tirez pas trop de conclusion sur de petites tables.

Par exemple, sur 100 lignes au total, SQL Server va probablement décider de faire des full scan sur la table 100% stockée en ram plutôt que de "chipoter" avec vos indexes.

[Sergejack]

Avec la requête suivante :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
 
SELECT
    A.A1,A2,A3,B2
FROM
    A INNER JOIN B ON A.A1 = B.B1
WHERE
    B.B2 = @B2

Quel serait le ou les indexes adéquat pour cette requête ?

Si la volumétrie est suffisament importante :

INDEX sur B(B2)
et
INDEX sur A(A1)

Ayez à l'esprit que SQL Server ne donne pas de priorité à la "recherche" dans une table en fonction de l'ordre d'apparition de cette table quand le même query peut être réecrit avec un ordre différent (ce qui est clairement le cas avec les INNER JOIN) *.

* Sauf si vous utilisez FORCE ORDER

[SQLpro]

Ne vous embêtez pas, créez toujours des index sous toutes les FK. Si jamais cela ne sert pas (cas rare) SQL Server est capable de le déterminer (vue sys.dm_db_index_physical_stats). Vous aurez donc tout loisir de les désactiver éventuellement.

A +

[Rei Ichido]

- A avec les colonnes A1, A2, A3, A4 et A5 où A1 est la clef primaire
- B avec les colonnes B1, B2, B3 et A1 ou B1 est la clef primaire et A1 une clef étrangère.

La requête met une égalité entre A.A1 et B.B1, c'est une faute de frappe je suppose ? Elle devrait être entre A.A1 et B.A1 ?

Si c'est bien le cas, il faut considérer comment VOUS feriez le boulot si vous étiez à la place du SGBD.

CAS 1 : très faibles volumétries. Pourquoi s'embêter à avoir un index, on a deux feuilles avec 5 valeurs sur chaque, on lit les feuilles et on fait le rapprochement de tête.

CAS 2 : très faible volumétrie sur B. Pareil en fait : le critère sur B2 sera vite évalué, et comme A1 est la clé primaire de A, on ira vite récupérer les lignes de A à partir des valeurs trouvées dans B.

CAS 3 : Forte volumétrie sur B. Il faut alors considérer la densité des valeurs non nulles sur B.A1 (y a-t-il beaucoup de correspondances).
- S'il y a peu de correspondances (mettons, il y a 1 million de lignes dans B, mais seulement 1000 valeurs non nulles sur B.A1), alors c'est rentable de lire un index sur B(A1) en ne considérant que les valeurs non nulles, puis accéder à la table B, faire le filtre sur B2, et pour les valeurs qui restent aller chercher les valeurs de A. On pourrait même accélerer le process avec un index sur B (A1) INCLUDE (B2), pour éviter d'aller cherche dans la table (pour un cas intermédiaire).
- Sinon, ou si on est dans un cas très favorable sur B2, on va poser un index sur B(B2). La question est de savoir si le critère pour lequel on va faire la recherche est discriminant sur B2.
> Si on reste sur une table à 1 million de lignes, que B2 représente un statut, que 950 000 lignes sont au statut 1, et qu'on fait une recherche sur le statut 1, alors un bête index sur B2 est inutil : le SGBDR l'ignorera, car il devrait faire la jointure entre l'index et la table elle-même pour aller chercher l'information sur B.A1. Par contre, un index sur B (B2) INCLUDE (A1) serait utilisé - le seul intérêt dans ce cas étant de restreindre la taille des informations à considérer (l'index est plus petit que la table).

> Si B2 est au contraire très discriminant (voire, valeur unique), alors l'index peut se poser simplement sur B (B2). On peut toujours rajouter le include, mais ce n'est pas très utile en terme de performance : une fois qu'on a trouvé 3 lignes correspondantes dans B, c'est rapide d'aller les rechercher dans la table pour en extraire A1.

[Kropernic]

Merci à tous pour vos explications.

Va falloir que je me fasse des p'tits dessins pour représenter concrètement la dernière mais sinon, cela me semble bien plus clair maintenant.

Merci bien !