[SQL 2008] Indexation de table

Citation:

Envoyé par Ptit_Dje

Soit si l'on a une colonne selective en premier, d'après moi, se sera plus efficace pour la recherche d'éléments distincts.
Si l'on choisis l'autre ordre d'index, avec les colonnes les plus selectives à la fin, d'après moi toujours, se sera plus efficace pour des requètes d'aggrégation car l'on pourra prendre directement un "range" de l'index, sans avoir à parcourir chaque clef selective jusque la fin de l'index.

Absolument pas, ce sera toujours beaucoup moins efficace que la première car le volume des données sera plus grand car la sélectivité est moins bonne d'entrée de jeu !

De plus certains requêtes n'utiliserons pas l'index car le volume estimé sera trop important.

Petite explication :

Si j'ai deux colonnes, l'une contenant 5 valeurs distribuées comme ceci :
1 5%
2 5%
3 10%
4 40%
5 40%
L'autre contenant 10 valeurs distribuées comme ceci :
0 5%
1 5%
2 5%
3 5%
4 10%
5 10%
6 10%
7 10%
8 20 %
9 30 %

Sachant que le seuil pour utiliser l'index ou la table étant de 25 %,
le premier index composé de C1, C2, utilisera à 60% l'index (3 valeur sur 5), tandis que l'autre l'utilisera à 90% (9 valeurs sur 10).

A +

J'essayais d'expliquer la différence que j'ai pu constater entre 2 littératures.
Je pense que le mieux va être de tester, chose que je n'ai pas encore faite.

Merci pour ton avis et ton explication!

Hello,

Je viens d'effectuer un test:

Soit la table:
Code:

1 2 3 4 5 6 CREATE TABLE [dbo].[Test_Index]( [C1] [varchar](30) NULL, [C2] [varchar](30) NULL, [id] [int] IDENTITY(1,1) NOT NULL ) ON [PRIMARY]
Avec une répartition de données comme ceci:
c1 Nbr_Rows
Belladone 100000
Catapulte 50000
Dupont 400000
Surprise 50000
Tartuffe 400000

c2 Nbr_Rows
Bi d'joué m'colleau 250000
Bienvenue chez vous 300000
Carolingien 100000
Comment se fesse ? 50000
Crouperites 50000
Lulupulus 50000
Oufti vin'djeu ! 100000
Pompolom pom polom 50000
Valorbe baudour 50000

Table sur laquelle ont été placé 2 index:
Code:

1 2 create nonclustered index IDX_Selective_Nonselective on Test_Index (C2,C1) create nonclustered index IDX_Nonselective_Selective on Test_Index (C1,C2)
Requètes et statistiques:
-- Catapule - 5% de données C1
Code:

1 2 3 select distinct c2 from dbo.Test_Index with (index = IDX_Selective_Nonselective) where C1 = 'Catapulte'
Table 'Test_Index'. Scan count 1, logical reads 5493, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Code:

1 2 3 select distinct c2 from dbo.Test_Index with (index = IDX_Nonselective_Selective) where C1 = 'Catapulte'
Table 'Test_Index'. Scan count 1, logical reads 289, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

-- Tartuffe - 40% de données C1
Code:

1 2 3 select distinct c2 from dbo.Test_Index with (index = IDX_Selective_Nonselective) where C1 = 'Tartuffe'
Table 'Test_Index'. Scan count 1, logical reads 5493, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Code:

1 2 3 select distinct c2 from dbo.Test_Index with (index = IDX_Nonselective_Selective) where C1 = 'Tartuffe'
Table 'Test_Index'. Scan count 1, logical reads 2230, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Dans ce cas, l'index le plus performant est l'index constitué des colonnes C1,C2.
NB: C2 ayant une plus grande dispertion des données

-- Lulupulus - 5% de données C2
Code:

1 2 3 select distinct c1 from dbo.Test_Index with (index = IDX_Selective_Nonselective) where C2 = 'Lulupulus'
Table 'Test_Index'. Scan count 1, logical reads 230, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Code:

1 2 3 select distinct c1 from dbo.Test_Index with (index = IDX_Nonselective_Selective) where C2 = 'Lulupulus'
Table 'Test_Index'. Scan count 1, logical reads 5493, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

-- Bienvenue chez vous - 30% de données C2
Code:

1 2 3 select distinct c1 from dbo.Test_Index with (index = IDX_Selective_Nonselective) where C2 = 'Bienvenue chez vous'
Table 'Test_Index'. Scan count 1, logical reads 1739, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Code:

1 2 3 select distinct c1 from dbo.Test_Index with (index = IDX_Nonselective_Selective) where C2 = 'Bienvenue chez vous'
Table 'Test_Index'. Scan count 1, logical reads 5493, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Dans ce cas, l'index le plus performant est l'index constitué des colonnes C2,C1.
Code:

1 2 3 4 select c1, count(*) from dbo.Test_Index with (index = IDX_Selective_Nonselective) where C2 = 'Bienvenue chez vous' and C1 = 'Tartuffe' group by c1
Table 'Test_Index'. Scan count 1, logical reads 706, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Code:

1 2 3 4 select c1, count(*) from dbo.Test_Index with (index = IDX_Nonselective_Selective) where C2 = 'Bienvenue chez vous' and C1 = 'Tartuffe' group by c1
Table 'Test_Index'. Scan count 1, logical reads 705, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Dans ce cas, l'index le plus performant (différence vraiment minime) est celui dont les colonnes sont C1,C2
Code:

1 2 3 4 select c2, count(*) from dbo.Test_Index with (index = IDX_Selective_Nonselective) where C2 = 'Bienvenue chez vous' and C1 = 'Tartuffe' group by c2
Table 'Test_Index'. Scan count 1, logical reads 706, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Code:

1 2 3 4 select c2, count(*) from dbo.Test_Index with (index = IDX_Nonselective_Selective) where C2 = 'Bienvenue chez vous' and C1 = 'Tartuffe' group by c2
Table 'Test_Index'. Scan count 1, logical reads 705, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Dans ce cas, l'index le plus performant (différence vraiment minime) est celui dont les colonnes sont C1,C2

On remarque que la performance des index est fortement liée aux requêtes effectuées sur ceux-ci.
On remarque qu'une colonne à plus faible dispertion placée en première position donne une meilleure performance sur des requètes d'aggrégation (même si la différence est faible). Est-ce une généralité ? Je ne sais pas... Il faudrait tester :)