Voir le flux RSS

hmira

SQL Server - L'importance de l'ordre des colonnes d'un Index

Noter ce billet
par , 29/12/2014 à 16h43 (2323 Affichages)
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+

Envoyer le billet « SQL Server - L'importance de l'ordre des colonnes d'un Index » dans le blog Viadeo Envoyer le billet « SQL Server - L'importance de l'ordre des colonnes d'un Index » dans le blog Twitter Envoyer le billet « SQL Server - L'importance de l'ordre des colonnes d'un Index » dans le blog Google Envoyer le billet « SQL Server - L'importance de l'ordre des colonnes d'un Index » dans le blog Facebook Envoyer le billet « SQL Server - L'importance de l'ordre des colonnes d'un Index » dans le blog Digg Envoyer le billet « SQL Server - L'importance de l'ordre des colonnes d'un Index » dans le blog Delicious Envoyer le billet « SQL Server - L'importance de l'ordre des colonnes d'un Index » dans le blog MySpace Envoyer le billet « SQL Server - L'importance de l'ordre des colonnes d'un Index » dans le blog Yahoo

Mis à jour 29/12/2014 à 17h30 par hmira

Catégories
SQL Server

Commentaires

  1. Avatar de dsr57
    • |
    • permalink
    Merci pour ce billet qui est intéressant et précis.

    Je me pose une question, dans le billet, tu précises :

    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
    Dans le cas d'une clause WHERE avec les colonnes suivantes DateNaissance, Sex ; l'index est utilisé ?

    Merci par avance, bon dev

    Dsr57. V - Formet.
  2. Avatar de hmira
    • |
    • permalink
    Citation Envoyé par dsr57
    Dans le cas d'une clause WHERE avec les colonnes suivantes DateNaissance, Sex ; l'index est utilisé ?
    La réponse est OUI. L'index pourra éventuellement être retenu par l'optimiseur. En fait, ce qui importe, c'est la première colonne de l'index. Dès lors que la première colonne de l'index, en l'occurrence DateNaissance, est utilisée dans la clause WHERE, bien entendu dans une expression SARG, l'index est susceptible d'être utilisé par l'optimiseur de requête. Cela ne veut pas dire qu'il le sera forcément, mais il a toutes ses chances à ce qu'il soit retenu par l'optimiseur.

    A+
  3. Avatar de Tidus159
    • |
    • permalink
    Bonjour Hamid,
    tombé par hasard sur ton article intéressant, j'aimerais approfondir la stratégie de mise en place des index.

    Dans ton exemple où l'index n'est pas pris en compte, une personne logique se dirait "bon je vais donc en rajouter un autre (vu que je fais aussi souvent cette restriction)".
    Il crée donc un autre index. Plus tard, une nouvelle colonne apparait ainsi que de nouvelles requêtes, donc la personne ajoute encore un index...

    Mes questions sont donc plutôt généralistes tout en restant pratiques :
    - Quand doit-on véritablement considérer la mise en place d'un index ? (faut-il tester 10 requêtes et comparer les moyenne des temps d'exécution avec-sans index, sur le papier cela semble OK mais en réalité c'est assez long ! La mise en place d'un index pour une seule requête peut-elle être justifiée ?)
    - A partir de quand, trop d'index peut diminuer les performances ? Y-a-t-il des moyens de le savoir ?

    (Bien que je ne sois pas réellement dans l'administration de bd, je m'instruis un peu :-))
    Merci,
    A+,
    Emilien.
    Mis à jour 15/01/2015 à 12h52 par Tidus159