Ключевой столбец индекса против включенного столбца индекса

может ли кто - нибудь объяснить этот Двухиндексный ключевой столбец против включенного в индекс столбца?

В настоящее время у меня есть индекс, который имеет 4 ключевых столбца индекса и 0 включенных столбцов.

спасибо

4 56
tsql sql-server-2005

4 ответа:

ключевые столбцы индекса являются частью 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 пункт.

97

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

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

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

пример:

Создать Скрипт Таблицы

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

сценарий хранимой процедуры

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 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, 1000+@currentRow,@Details,@Details,@Details,@Details)
set @currentRow +=1;
END

SET NOCOUNT OFF
END

GO

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

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

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

скрипт

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.

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

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' в таблице - ДБО.Профиль ' имеет тип, недопустимый для использования в качестве ключевого столбца в индексе.

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

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

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.
12

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

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

6

включенные столбцы не являются частью ключа для индекса, но они существуют на индекс. По существу значения будут дублироваться Ниже Возьмите два типа индексов с примером столбца

CREATE clustered INDEX NC_index1 ON tableName (column1, column1, column1,column4)
CREATE clustered INDEX NC_index2 ON tableName (column1) INCLUDE (column2, column3,column4)

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

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

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

SELECT column1, column2 FROM tableName WHERE column1 = a

поскольку SQL Server не сможет позволило создать указатель на тип данных (например, XML,текст и т. д.)

0