인덱스 키 열 VS 인덱스 포함 열

인덱스 키 열 VS 인덱스 포함 열

---

누군가이 두 가지-인덱스 키 열 VS 인덱스 포함 열을 설명 할 수 있습니까?

현재 4 개의 Index Key Column과 0 개의 Included Column이있는 인덱스가 있습니다.

감사

---

인덱스 키 열은 인덱스 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 절.

---

책에 대해 생각해 봅시다. 책의 모든 페이지에는 페이지 번호가 있습니다. 이 책의 모든 정보는이 페이지 번호를 기준으로 순차적으로 제공됩니다. 데이터베이스 용어로 말하면 페이지 번호는 클러스터 된 인덱스입니다. 이제 책 끝에있는 용어집을 생각해보십시오. 이것은 알파벳 순서로되어 있으며 특정 용어가 속한 페이지 번호를 빠르게 찾을 수 있습니다. 이는 용어집 용어를 키 열로 사용하는 클러스터되지 않은 인덱스를 나타냅니다.

이제 모든 페이지가 상단에 "장"제목도 표시한다고 가정합니다. 용어집 용어가 어느 장에서 있는지 찾으려면 용어집 용어를 설명하는 페이지 번호를 조회 한 다음 해당 페이지를 열고 페이지의 장 제목을 확인해야합니다. 이는 키 조회를 명확하게 나타냅니다. 인덱싱되지 않은 열에서 데이터를 찾아야하는 경우 실제 데이터 레코드 (클러스터 된 인덱스)를 찾아이 열 값을 확인해야합니다. 포함 된 열은 성능 측면에서 도움이됩니다. 용어집 용어 외에 각 장 제목이 포함 된 용어집을 생각해보십시오. 용어집 용어가 속한 장을 찾아야하는 경우-실제 페이지를 열 필요가 없습니다. 용어집 용어를 조회 할 때 얻을 수 있습니다.

따라서 포함 된 열은 해당 장 제목과 같습니다. 비 클러스터형 인덱스 (용어집)에는 비 클러스터형 인덱스의 일부로 추가 속성이 있습니다. 인덱스는 포함 된 열을 기준으로 정렬되지 않습니다. 검색 속도를 높이는 데 도움이되는 추가 속성 일뿐입니다 (예 : 정보가 이미 용어집 인덱스에 있으므로 실제 페이지를 열 필요가 없습니다) .

예:

테이블 스크립트 생성

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, Level 16, State 1, Line 1 Column 'Detail1' in table 'dbo.Profile' is of a type that is invalid for use as a key column in an index.

Because we can not use varchar(Max) datatype as key column.

Now Create a non-Clustered Index with included columns using following 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

Now Run the following Query

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

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

---

Included columns don't form part of the key for the index, but they do exist on the index. Essentially the values will be duplicated, so there is a storage overhead, but there is a greater chance that your index will cover (i.e. be selected by the query optimizer for) more queries. This duplication also improves performance when querying, since the database engine can return the value without having to look at the table itself.

Only nonclustered indexes can have included columns, because in a clustered index, every column is effectively included.

---

Included columns dont form part of the key for the index, but they do exist on the index. Essentially the values will be duplicated Below Take two type of indexes with example column

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 is better suited for this kind of query:

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

Whereas NC_index2 is better suited for this kind of query:

SELECT column1, column2 FROM tableName WHERE column1 = a

because sql server can't allowed to create index on datatype (eg, XML,text, etc)

참고URL : https://stackoverflow.com/questions/3581294/index-key-column-vs-index-included-column