Столбец указателя индекса VS Индекс включен Колонка

Ответ 1

Столбцы индексных ключей являются частью b-дерева индекса. Включенные столбцы отсутствуют.

Возьмите два индекса:

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

index1 лучше подходит для такого рода запросов:

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

В то время как index2 лучше подходит для такого рода запросов:

SELECT col2, col3 FROM table1 WHERE col1 = x

В первом запросе index1 предоставляет механизм для быстрого определения интересующих строк. Запрос будет (вероятно) выполняться как поиск индекса, а затем поиск по закладкам для получения полной строки (строк).

Во втором запросе index2 действует как индекс покрытия. SQL Server вообще не нужно ударять по базовой таблице, поскольку индекс предоставляет все данные, необходимые для выполнения запроса. index1 также может выступать в качестве индекса покрытия в этом случае.

Если вам нужен индекс покрытия, но не хотите добавлять все столбцы в b-дерево, потому что вы не ищете их или не можете, потому что они не являются допустимым типом данных (например, XML), используйте предложение INCLUDE.

Ответ 2

Подумайте о книге. На каждой странице книги указан номер страницы. Вся информация в этой книге представлена ​​последовательно на основе этого номера страницы. Говоря в терминах базы данных, номер страницы является кластеризованным индексом. Теперь подумайте о глоссарии в конце книги. Это находится в алфавитном порядке и позволяет быстро найти номер глоссария, соответствующий конкретному номеру страницы. Это некластеризованный индекс с термином глоссария в качестве ключевого столбца.

Теперь, полагая, что каждая страница также отображает заголовок "глава" вверху. Если вы хотите найти в какой главе термин глоссарий, вам нужно найти, какая страница # описывает термин глоссария, затем - открыть соответствующую страницу и увидеть заголовок главы на странице. Это явно представляет собой ключевой поиск - когда вам нужно найти данные из неиндексированного столбца, вам нужно найти фактическую запись данных (кластерный индекс) и посмотреть на это значение столбца. Включенная колонка помогает с точки зрения производительности - подумайте о глоссарии, в котором каждое название главы включает помимо термина глоссария. Если вам нужно выяснить, какую главу принадлежит термину глоссария, вам не нужно открывать фактическую страницу - вы можете получить ее, когда найдете глоссарий.

Так включенный столбец подобен этим главам. Non clustered Index (глоссарий) имеет атрибут добавления как часть некластеризованного индекса. Индекс не сортируется по включенным столбцам - это просто дополнительные атрибуты, которые помогают ускорить поиск (например, вам не нужно открывать фактическую страницу, потому что информация уже находится в индексе глоссария).

Пример:

Создать таблицу Script

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,
    [District] [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

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' 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 attribute as part of the non-clustered index. Index is not sorted by included columns - it just additional attributes that helps to speed up the lookup (e.g. 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, [email protected],@Details,@Details,@Details,@Details)
set @currentRow +=1;
END

SET NOCOUNT OFF
END

GO

Используя указанный выше SP, вы можете вставить 200000 записей за один раз.

Вы можете видеть, что в столбце "EnrollMentId" есть кластерный индекс.

Теперь создайте некластеризованный индекс в столбце "UIDNO".

Script

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

Теперь запустите следующий запрос

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

Запрос 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.

Теперь отбросьте указанный выше некластеризованный индекс и заново создайте с помощью следующего Script

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

Он выдает следующую ошибку

Msg 1919, уровень 16, состояние 1, строка 1 Столбец "Detail1" в таблице "dbo.Profile" имеет тип, который недопустим для использования в качестве ключевого столбца в индексе.

Поскольку мы не можем использовать тип данных varchar (Max) в качестве ключевого столбца.

Теперь создайте некластерный индекс с включенными столбцами, используя следующий Script

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

Теперь запустите следующий запрос

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

Запрос 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.

Ответ 3

Включенные столбцы не являются частью ключа для индекса, но они существуют в индексе. По сути, значения будут дублироваться, поэтому накладные расходы на хранение, но есть большая вероятность того, что ваш индекс покроет (то есть будет выбран оптимизатором запросов для) более запросов. Это дублирование также повышает производительность при запросе, поскольку механизм базы данных может вернуть значение без необходимости просмотра самой таблицы.

Только некластеризованные индексы могут включать столбцы, потому что в кластерном индексе каждый столбец эффективно включен.