hmira

Par exemple, vous constatez que nombre de clauses WHERE sur la table dbo.MaTable utilisent régulièrement les mêmes critères :

Code SQL :

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

1
2
3
4
5
  SELECT ... 
  FROM dbo.MaTable 
  WHERE Col1 = ... 
   AND  Col2 = ... 
   AND  Col3 = ...

et vous décidez de créer un index composite défini sur les 3 colonnes en question { Col1, Col2, Col3 }.

Une des questions très importante et cruciale que vous devez de vous poser est la suivante :
"Ok, je prévois de créer un index sur les colonnes {Col1, Col2, Col3} mais dans quel Ordre ?
Col1, Col2, Col3
ou Col2, Col1, Col3
ou Col3, Col1, Col2
ou .... ?"

En effet, lors de la création d'un index, l'ordre des colonnes est très important et déterminant pour les performances.
Gardez à l'esprit également que l'ordre des colonnes dans la définition de l'index est complètement dé-corrélé de l'ordre de l'apparition des ces mêmes colonnes dans les clauses WHERE. En d'autres termes, ce n'est pas parce que vos clauses WHERE sont presque toujours sous forme :

Code SQL :

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

1
2
3
WHERE Col1 = ... 
   AND Col2 = ... 
   AND  Col3 = ...

que votre index doit être également défini selon le même ordre (Col1, Col2, Col3). Il s'agit d'une grosse erreur ou plutôt d'une idée reçue complètement fausse.

En réalité, l'ordre d'apparition des colonnes dans une clause WHERE n'a pas beaucoup d'importance dès lors que l'optimiseur, en fonction des indexes déjà existants, est en mesure d'élaborer un chemin optimal. En outre, la requête sera transformée dans une forme canonique lors de l'étape de l'algébrisation et l'ordre d'évaluation des opérateurs logiques peut varier en fonction des choix effectués par l'optimiseur de requêtes.

En revanche, lors de la définition d'un index, l'ordre des colonnes a tout son importance. En effet, dans un index, les données sont d'abord triées sur le première colonne et à l'intérieur de la première colonne, les données sont triées sur la deuxième colonne et ainsi de suite jusqu'à la dernière colonne de l'index. Gardez à l'esprit que la première colonne d'un index joue un rôle très important pour des diverses raisons que je ne vais pas détailler ici.

Pour déterminer, en terme de performance, le meilleur ordre de déclaration des colonnes dans un index, vous devez vous intéresser pour chacune des colonnes aux caractéristiques suivantes :
1 - La sélectivité de la colonne
2 - La largeur de la colonne
3 - Le type de donnée de la colonne

Pour déterminer cet ordre, vous devez privilégiez en premier les colonnes les plus sélectives, les plus compactes en ce qui concerne la largeur et le type de donnée .

Prenons un exemple : Soit la requête récurrente (avec les paramètres qui changent bien sûr) ci-dessous :

Code SQL :

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

1
2
3
4
5
SELECT E.*
FROM Employe AS E
WHERE E.SituationMatrimoniale = 'C' -- Célibataire 
AND E.DateNaisance = '1985-06-21'
AND E.Sex = 'F' -- Féminin

Afin de déterminer l'ordre le plus pertinent, nous allons calculer pour chacune des colonne la sélectivité, puis classer celles-ci pat ordre décroissant :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
SELECT A.* FROM 
( SELECT 'SituationMatrimoniale' as NomColonne, COUNT(DISTINCT E.SituationMatrimoniale) AS NombreValeursDistinctes,
COUNT(E.SituationMatrimoniale) AS NombreDeLignes,
(CAST(COUNT(DISTINCT E.SituationMatrimoniale) AS DECIMAL) / CAST(COUNT(E.SituationMatrimoniale) AS DECIMAL)) AS Selectivite,
(1.0/(COUNT(DISTINCT E.SituationMatrimoniale))) AS Densite
FROM dbo.Employe AS E
UNION ALL 
SELECT 'DateNaissance' as NomColonne, COUNT(DISTINCT E.DateNaissance) AS NombreValeursDistinctes,
COUNT(E.DateNaissance) AS NombreDeLignes,
(CAST(COUNT(DISTINCT E.DateNaissance) AS DECIMAL) / CAST(COUNT(E.DateNaissance) AS DECIMAL)) AS Selectivite,
(1.0/(COUNT(DISTINCT E.DateNaissance))) AS Densite
FROM dbo.Employe AS E
UNION ALL 
SELECT 'Sex' as NomColonne, COUNT(DISTINCT E.Sex) AS NombreValeursDistinctes,
COUNT(E.Sex) AS NombreDeLignes,
(CAST(COUNT(DISTINCT E.Sex) AS DECIMAL) / CAST(COUNT(E.Sex) AS DECIMAL)) AS Selectivite,
(1.0/(COUNT(DISTINCT E.Sex))) AS Densite
FROM dbo.Employe AS E
) A 
ORDER BY A.Selectivite DESC

NomColonne	NombreValeursDistinctes	NombreDeLignes	Selectivite	Densite
DateNaissance	279	290	0.9620689655172413793	0.003584229390
SituationMatrimoniale	4	290	0.0137931034482758620	0.250000000000
Sex	2	290	0.0068965517241379310	0.500000000000

D'après le résultat ci-dessus, on peut en déduire que l'index sera plus performant si l'ordre des colonnes est défini comme suit
(DateNaissance, SituationMatrimoniale, Sex)

Code SQL :

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

1
2
CREATE INDEX IX_Employe_DateNaissance_SituationMatrimoniale_Sex ON
 dbo.Employe (DateNaissance, SituationMatrimoniale, Sex);

Autres considération à prendre en compte :
Comme je l'ai mentionné ci-dessus, la première colonne joue un rôle important, en outre seule les requêtes référençant la première colonne de l'index pourront éventuellement utiliser cet index.
Dans notre exemple, les clauses WHERE référençant les colonnes suivantes :
DateNaissance
ou DateNaissance, SituationMatrimoniale
ou DateNaissance, SituationMatrimoniale, Sex

Pourront le cas échéant bénéficier de l'index

A contrario, il est quasiment improbable que la requête ci-dessous :

Code SQL :

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

1
2
3
4
SELECT E.*
FROM Employe AS E
WHERE E.SituationMatrimoniale = 'C' -- Célibataire 
AND E.Sex = 'F' -- Féminin

puisse utiliser l'index basé sur les colonnes (DateNaissance, SituationMatrimoniale, Sex) ; ou alors, si tel est le cas, l'optimiseur utilisera l'index mais en effectuant forcément un full scan de l'index. En effet la première colonne de l'index est absente de la clause WHERE, et un Full Scan de l'index ne présente aucun intérêt !

A+