Colonne de clé d’index VS Index colonne incluse

Quelqu’un peut-il expliquer ces deux – Index Key Column VS Index Column Inclus?

Actuellement, j’ai un index qui comporte 4 colonnes de clés d’index et 0 colonnes incluses.

Merci

Les colonnes de clé d’index font partie de l’arborescence de l’index. Les colonnes incluses ne le sont pas.

Prenez deux index:

CREATE INDEX index1 ON table1 (col1, col2, col3) CREATE INDEX index2 ON table1 (col1) INCLUDE (col2, col3) 

index1 convient mieux à ce type de requête:

 SELECT * FROM table1 WHERE col1 = x AND col2 = y AND col3 = z 

Alors que index2 convient mieux à ce type de requête:

 SELECT col2, col3 FROM table1 WHERE col1 = x 

Dans la première requête, index1 fournit un mécanisme permettant d’identifier rapidement les lignes d’intérêt. La requête s’exécutera (probablement) en tant que recherche d’index, suivie d’une recherche de signets pour récupérer la ou les lignes complètes.

Dans la deuxième requête, index2 agit comme un index de couverture. SQL Server n’a pas besoin d’accéder à la table de base car l’index fournit toutes les données nécessaires pour répondre à la requête. index1 pourrait également servir d’indice de couverture dans ce cas.

Si vous voulez un index de couverture, mais que vous ne voulez pas append toutes les colonnes à l’arborescence parce que vous ne les recherchez pas ou que vous ne pouvez pas les utiliser parce qu’elles ne sont pas autorisées (par exemple, XML), utilisez le Clause INCLUDE.

Réfléchissons au livre. Chaque page du livre a le numéro de page. Toutes les informations contenues dans ce livre sont présentées de manière séquentielle en fonction de ce numéro de page. Dans les termes de la firebase database, le numéro de page est l’index clusterisé. Pensez maintenant au glossaire à la fin du livre. Ceci est dans l’ordre alphabétique et vous permet de trouver rapidement le terme de glossaire spécifique à un numéro de page. Cela représente un index non clusterisé avec un terme de glossaire comme colonne clé.

En supposant maintenant que chaque page affiche également le titre “chapitre” en haut. Si vous voulez trouver dans quel chapitre se trouve le terme du glossaire, vous devez rechercher la page qui décrit le terme du glossaire, ensuite ouvrir la page correspondante et voir le titre du chapitre sur la page. Cela représente clairement la recherche de clés – lorsque vous devez rechercher les données d’une colonne non indexée, vous devez rechercher les enregistrements de données réels (index cluster) et regarder cette valeur de colonne. La colonne Incluse aide en termes de performance – pensez au glossaire où chaque titre de chapitre comprend, outre le terme du glossaire. Si vous avez besoin de savoir à quel chapitre appartient le terme du glossaire – vous n’avez pas besoin d’ouvrir la page réelle – vous pouvez l’obtenir lorsque vous recherchez le terme du glossaire.

Donc, les colonnes incluses sont comme ces titres de chapitre. L’index non clusterisé (glossaire) possède un atsortingbut supplémentaire dans l’index non clusterisé. Index n’est pas sortingé par colonnes incluses – il ne s’agit que d’atsortingbuts supplémentaires permettant d’accélérer la recherche (par exemple, vous n’avez pas besoin d’ouvrir la page réelle car les informations figurent déjà dans l’index du glossaire) .

Exemple:

Créer un script de table

 CREATE TABLE [dbo].[Profile]( [EnrollMentId] [int] IDENTITY(1,1) NOT NULL, [FName] [varchar](50) NULL, [MName] [varchar](50) NULL, [LName] [varchar](50) NULL, [NickName] [varchar](50) NULL, [DOB] [date] NULL, [Qualification] [varchar](50) NULL, [Profession] [varchar](50) NULL, [MaritalStatus] [int] NULL, [CurrentCity] [varchar](50) NULL, [NativePlace] [varchar](50) NULL, [Dissortingct] [varchar](50) NULL, [State] [varchar](50) NULL, [Country] [varchar](50) NULL, [UIDNO] [int] NOT NULL, [Detail1] [varchar](max) NULL, [Detail2] [varchar](max) NULL, [Detail3] [varchar](max) NULL, [Detail4] [varchar](max) NULL, PRIMARY KEY CLUSTERED ( [EnrollMentId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO SET ANSI_PADDING OFF GO 

Script de procédure stockée

 CREATE Proc [dbo].[InsertIntoProfileTable] As BEGIN SET NOCOUNT ON Declare @currentRow int Declare @Details varchar(Max) Declare @dob Date set @currentRow =1; set @Details ='Let''s think about the book. Every page in the book has the page number. All information in this book is presented sequentially based on this page number. Speaking in the database terms, page number is the clustered index. Now think about the glossary at the end of the book. This is in alphabetical order and allow you to quickly find the page number specific glossary term belongs to. This represents non-clustered index with glossary term as the key column. Now assuming that every page also shows "chapter" title at the top. If you want to find in what chapter is the glossary term, you have to lookup what page # describes glossary term, next - open corresponding page and see the chapter title on the page. This clearly represents key lookup - when you need to find the data from non-indexed column, you have to find actual data record (clustered index) and look at this column value. Included column helps in terms of performance - think about glossary where each chapter title includes in addition to glossary term. If you need to find out what chapter the glossary term belongs - you don''t need to open actual page - you can get it when you lookup the glossary term. So included column are like those chapter titles. Non clustered Index (glossary) has addition atsortingbute as part of the non-clustered index. Index is not sorted by included columns - it just additional atsortingbutes that helps to speed up the lookup (eg you don''t need to open actual page because information is already in the glossary index).' while(@currentRow <=200000) BEGIN insert into dbo.Profile values( 'FName'+ Cast(@currentRow as varchar), 'MName' + Cast(@currentRow as varchar), 'MName' + Cast(@currentRow as varchar), 'NickName' + Cast(@currentRow as varchar), DATEADD(DAY, ROUND(10000*RAND(),0),'01-01-1980'),NULL, NULL, @currentRow%3, NULL,NULL,NULL,NULL,NULL, 1000+@currentRow,@Details,@Details,@Details,@Details) set @currentRow +=1; END SET NOCOUNT OFF END GO 

En utilisant le SP ci-dessus, vous pouvez insérer 200 000 enregistrements à la fois.

Vous pouvez voir qu'il existe un index clusterisé dans la colonne «EnrollMentId».

Maintenant, créez un index non clusterisé sur la colonne «UIDNO».

Scénario

 CREATE NONCLUSTERED INDEX [NonClusteredIndex-20140216-223309] ON [dbo].[Profile] ( [UIDNO] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO 

Exécutez maintenant la requête suivante

 select UIDNO,FName,DOB, MaritalStatus, Detail1 from dbo.Profile --Takes about 30-50 seconds and return 200,000 results. 

Requête 2

 select UIDNO,FName,DOB, MaritalStatus, Detail1 from dbo.Profile where DOB between '01-01-1980' and '01-01-1985' --Takes about 10-15 seconds and return 36,479 records. 

Maintenant, supprimez l'index non clusterisé ci-dessus et recréez avec le script suivant

 CREATE NONCLUSTERED INDEX [NonClusteredIndex-20140216-231011] ON [dbo].[Profile] ( [UIDNO] ASC, [FName] ASC, [DOB] ASC, [MaritalStatus] ASC, [Detail1] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO 

Il va lancer l'erreur suivante

Msg 1919, Niveau 16, État 1, Ligne 1 La colonne 'Detail1' de la table 'dbo.Profile' est d'un type non valide pour une utilisation en tant que colonne clé dans un index.

Parce que nous ne pouvons pas utiliser le type de données varchar (Max) comme colonne clé.

Maintenant, créez un index non clusterisé avec des colonnes incluses en utilisant le script suivant

 CREATE NONCLUSTERED INDEX [NonClusteredIndex-20140216-231811] ON [dbo].[Profile] ( [UIDNO] ASC ) INCLUDE ( [FName], [DOB], [MaritalStatus], [Detail1]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO 

Exécutez maintenant la requête suivante

 select UIDNO,FName,DOB, MaritalStatus, Detail1 from dbo.Profile --Takes about 20-30 seconds and return 200,000 results. 

Requête 2

 select UIDNO,FName,DOB, MaritalStatus, Detail1 from dbo.Profile where DOB between '01-01-1980' and '01-01-1985' --Takes about 3-5 seconds and return 36,479 records. 

Les colonnes incluses ne font pas partie de la clé pour l’index, mais elles existent dans l’index. Essentiellement, les valeurs seront dupliquées, il y a donc une surcharge de stockage, mais il y a plus de chance que votre index couvre (c.-à-d. Qu’il soit sélectionné par l’optimiseur de requêtes) plus de requêtes. Cette duplication améliore également les performances lors de l’interrogation, car le moteur de firebase database peut renvoyer la valeur sans avoir à consulter la table elle-même.

Seuls les index non clusterisés peuvent inclure des colonnes, car dans un index clusterisé, chaque colonne est effectivement incluse.

Les colonnes incluses ne font pas partie de la clé pour l’index, mais elles existent dans l’index. Essentiellement, les valeurs seront dupliquées ci-dessous. Prenez deux types d’index avec la colonne exemple.

 CREATE clustered INDEX NC_index1 ON nomTable (colonne1, colonne1, colonne1, colonne4)
 CREATE clustered INDEX NC_index2 ON nomTable (colonne1) INCLUDE (colonne2, colonne3, colonne4)

NC_index1 convient mieux à ce type de requête:

 SELECT * FROM tableName WHERE column1 = x AND column1 = y AND column1 = z and column4=n 

Alors que NC_index2 convient mieux à ce type de requête:

 SELECT column1, column2 FROM tableName WHERE column1 = a 

car SQL Server ne peut pas créer d’index sur le type de données (par exemple, XML, texte, etc.)