utilisation du mot include dans un index non cluster

Version imprimable

18/07/2014, 12h48
joujousagem2006

utilisation du mot include dans un index non cluster

Bonjour a tous

Dans quel cas je doit utiliser L'option "include" pour inclure un telle colonne dans la création d'un nouveau index non cluster et quel est la la différence d’efficacité entre un index sans des colonne incluse et des index qui ont des colonnse inclus et selon quel caractère je fait le chois de ce colonne???
18/07/2014, 13h33
aieeeuuuuu
Bonjour,

Le fait d'inclure des colonnes dans les index permet d'éviter, lorsque l'index est utilisé, de devoir aller chercher des données complémentaires dans la table sous jacente.
Comme un exemple vaut mieux qu'un long discours :

Prenons une table de personnes
Code:

1 2 3 4 5 6 CREATE TABLE PERSONNE( per_id int not null primary key ,per_nom varchar(50) ,per_prenom varchar(50) )
Si vous effectuez une recherche par le nom, un index sur cette colonne pourra être utile :
Code:

1 2 CREATE INDEX IX_PERSONNE_NOM ON PERSONNE(per_nom)
Ansi, une requete telle que
Code:

1 2 3 4 SELECT COUNT(*) FROM PERSONNE WHERE per_nom = 'Dupont'
Utilisera l'index

En revanche, si la requête devient :
Code:

1 2 3 4 SELECT per_nom, per_prenom FROM PERSONNE WHERE per_nom = 'Dupont'
L'index pourrait encore etre utilisé pour trouver les lignes qui vérifient la condition de filtre, mais il faudra ensuite ouvrir la table pour y trouver les prénoms. Passé un certain ratio de cardinalités, ces aller-retour deviennent trop couteux, et le moteur préférera scanner la table complète, mais une seule fois.

Vous pouvez remplacer votre index par celui-ci :
Code:

1 2 CREATE INDEX IX_PERSONNE_NOM ON PERSONNE(per_nom, per_prenom)
Mais le tri sur la colonne prénom est inutile dans votre cas. Vous pouvez donc simplement mettre la colonne prenom en colonne incluse à l'index
Code:

1 2 CREATE INDEX IX_PERSONNE_NOM ON PERSONNE(per_nom) INCLUDE (per_prenom)
Ainsi, l'index couvre votre requête, et il n'est plus nécessaire de lire des données dans la table.

Bonjour && merci pour ces info

j'ai cette procédure qui contiens 5 variable est ce que ce type index peuvent couvrir cette procédure

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
USE [suivi]
GO
 
/****** Object:  StoredProcedure [dbo].[spSGM_GetMvtfabr]    Script Date: 07/18/2014 14:31:23 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
 
 
/****** Objet :  Procédure stockée dbo.spSGM_GetMvtfabr    Date du script : 18/04/2003 11:03:50 ******/
CREATE PROCEDURE [dbo].[spSGM_GetMvtfabr] 
@ni_num	VARCHAR(23),
@pt_num	VARCHAR(18)=NULL,
@mf_trans	VARCHAR(25)=NULL,
@mf_statut	VARCHAR(5)= NULL,
@pr_num	INT=NULL,
@nbMax	INT=1
 
AS
 
DECLARE @iTypeRequete INT
 
-- Test des paramètres d'entrée pour orientier vers la bonne requêtes
SET @iTypeRequete =
	CASE
		-- ni_num, pt_num, mf_trans , mf_statut , pr_num renseignés
		WHEN (@ni_num IS NOT NULL) AND (@pt_num IS NOT NULL) AND (@mf_trans IS NOT NULL) AND (@mf_statut IS NOT NULL) AND (@pr_num IS NOT NULL)
			THEN 0	
		-- ni_num, pt_num,  mf_trans , mf_statut renseigné
		WHEN (@ni_num IS NOT NULL) AND (@pt_num IS NOT NULL) AND (@mf_trans IS NOT NULL) AND (@mf_statut IS NOT NULL)
			THEN 1		-- Requête sur le n° de série et son code produit , la transaction et son statut 
		-- ni_num, pt_num,  mf_trans  renseigné
		WHEN (@ni_num IS NOT NULL) AND (@pt_num IS NOT NULL) AND (@mf_trans IS NOT NULL)
			THEN 2		-- Requête sur le n° de série et son code produit et la transaction 
		-- ni_num, pt_num renseigné
		WHEN (@ni_num IS NOT NULL) AND (@pt_num IS NOT NULL)
			THEN 3		-- Requête sur le n° de série et son code produit		
		-- ni_num renseigné
		WHEN (@ni_num IS NOT NULL) AND (@pt_num IS NULL)
			THEN 4 	-- Requête sur le n° de série seul						
 
		ELSE -1
	END
-- on ne renvoie que le dernier par défaut ou un max spécifié
SET ROWCOUNT @nbMax
IF @iTypeRequete = 0
BEGIN
	SELECT pt_num,mf_dmvtdeb,ni_num,mf_trans,pr_num,mf_statut,mf_obs,mf_qte
	FROM mvtfabr
	WHERE  ni_num = @ni_num AND pt_num = @pt_num AND mf_trans = @mf_trans AND mf_statut = @mf_statut AND pr_num = @pr_num
	ORDER BY mf_dmvtdeb DESC
END
IF @iTypeRequete = 1
BEGIN
	SELECT pt_num,mf_dmvtdeb,ni_num,mf_trans,pr_num,mf_statut,mf_obs,mf_qte
	FROM mvtfabr
	WHERE  ni_num = @ni_num AND pt_num = @pt_num AND mf_trans = @mf_trans AND mf_statut = @mf_statut 
	ORDER BY mf_dmvtdeb DESC
END
IF @iTypeRequete = 2
BEGIN
	SELECT pt_num,mf_dmvtdeb,ni_num,mf_trans,pr_num,mf_statut,mf_obs,mf_qte
	FROM mvtfabr
	WHERE  ni_num = @ni_num AND pt_num = @pt_num AND mf_trans = @mf_trans 
	ORDER BY mf_dmvtdeb DESC
END
IF @iTypeRequete = 3
BEGIN
	SELECT pt_num,mf_dmvtdeb,ni_num,mf_trans,pr_num,mf_statut,mf_obs,mf_qte
	FROM mvtfabr
	WHERE  ni_num = @ni_num AND pt_num = @pt_num 
	ORDER BY mf_dmvtdeb DESC
END
IF @iTypeRequete = 4
BEGIN
	SELECT pt_num,mf_dmvtdeb,ni_num,mf_trans,pr_num,mf_statut,mf_obs,mf_qte
	FROM mvtfabr
	WHERE  ni_num = @ni_num
	ORDER BY mf_dmvtdeb DESC
END
SET ROWCOUNT 0
 
RETURN(0)
 
 
GO

structure d'index

Code:

1
2
3
4
5
6
7
8
9
10
11
12
USE [suivi]
GO
 
/****** Object:  Index [missing_index_11001]    Script Date: 07/18/2014 14:34:07 ******/
CREATE NONCLUSTERED INDEX [missing_index_11001] ON [dbo].[mvtFabr] 
(
	[pt_num] ASC,
	[mf_trans] ASC,
	[mf_dmvtdeb] ASC
)
INCLUDE ( [ni_num]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90)
GO

18/07/2014, 15h56
aieeeuuuuu

Couvrir, non (car certaines colonnes manquent encore), mais aider oui : mettre cette colonne dans l'index permettra de réduire le nombre de lignes pour lesquelles il faudra aller chercher les données complémentaire.

mais si c'est une valeur suffisamment discriminante, vous pourriez la mettre dans l'index.

Difficile de vous dire quel serait le meilleur index sans connaitre tous les tenants et aboutissants (autre processus pénalisés,...)

Globalement, comme vous lancez des requêtes avec des critères différents, il faut que les critères apparaissant dans le plus de requêtes soient en premier dans votre index.
globalement, ça donnerait ça :
ni_num, pt_num , mf_trans , mf_statut , pr_num
Il faudrait ajouter les autres colonnes (présentes dans le SELECT mais pas dans la clause WHERE) en tant que colonnes incluses pour que l'index soit réellement couvrant.

Mais, en théorie, il est préférable de placer les colonnes les plus discriminantes en premier, ce qui pourrait suggérer un ordre différent.

A vous donc de trouver le meilleur compromis en fonction des différentes requêtes effectuées sur la table, en fonction de vos données, en fonction de vos besoins, et de tout un tas d'autres critères
18/07/2014, 22h08
hmira

Pour mieux comprendre l’impact en terme de performance d'un index couvrant et d’un index non couvrant, il faut analyser et comparer les plans d’exécution des requêtes avec index couvrant puis avec index non couvrant.

Lorsque les données ne se trouvent pas directement dans l’index que ce dernier soit couvrant ou non, une opérations supplémentaire de type Key Lookup ou bien de type RID Lookup est effectuée ; elle consiste comme l'a si bien dit aieeeuuuuu à "aller chercher des données complémentaires dans la table sous jacente".

L’opération supplémentaire Key Lookup survient lorsque la table est organisée en Cluster, alors que l’opération RID Lookup survient losque la table n’est pas organisée en cluster.

En outre, vous ne verrez jamais une opération Key Lookup causée par un index Cluster lors d’un Seek ou lors d’un Scan. En effet, l’index cluster est la table elle-même et donc, par essence, il ne peut pas y avoir de colonnes manquantes dans l’index cluster ; si la colonne ne fait pas partie de l’index cluster (c.à.d. ne fait partie de la définition de l’index cluster) , celle-ci se trouve forcément au niveau des feuilles (index leaf-level pages).

Attention : Cela ne signifie pas qu’il faille utiliser à outrance les indexes couvrant sous prétexte que cela améliore les performances à tous les coups ! Ce n’est pas vrai. La maintenance des indexes a également un coût qui peut parfois contrebalancer en négatif le gain apporté par l’index. Donc comme l’a si bien expliqué aieeeuuuuu cela nécessite une analyse approfondie, " ...connaitre tous les tenants et aboutissants (autre processus pénalisés,...). ".

A+