인덱스 생성/변경/삭제

growdeveloper ㅣ 2021. 4. 27. 17:49

테이블 또는 뷰에 관계 인덱스를 만듭니다. 이 인덱스는 클러스터형 또는 비클러스터형 B-트리 인덱스이므로 rowstore 인덱스라고도 합니다. 테이블에 데이터가 채워지기 전에 rowstore 인덱스를 만들 수 있습니다. 특히 쿼리가 특정 열에서 값을 선택하거나 값을 특정 순서로 정렬해야 하는 경우 rowstore 인덱스를 사용하여 쿼리 성능을 개선할 수 있습니다.

구문

SQL Server 및 Azure SQL Database에 대한 구문

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
    ON <object> ( column [ ASC | DESC ] [ ,...n ] )
    [ INCLUDE ( column_name [ ,...n ] ) ]
    [ WHERE <filter_predicate> ]
    [ WITH ( <relational_index_option> [ ,...n ] ) ]
    [ ON { partition_scheme_name ( column_name )
         | filegroup_name
         | default
         }
    ]
    [ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ]
  
[ ; ]
  
<object> ::=
{ database_name.schema_name.table_or_view_name | schema_name.table_or_view_name | table_or_view_name }

<relational_index_option> ::=
{
    PAD_INDEX = { ON | OFF }
  | FILLFACTOR = fillfactor
  | SORT_IN_TEMPDB = { ON | OFF }
  | IGNORE_DUP_KEY = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF }
  | STATISTICS_INCREMENTAL = { ON | OFF }
  | DROP_EXISTING = { ON | OFF }
  | ONLINE = { ON | OFF }
  | RESUMABLE = { ON | OFF }
  | MAX_DURATION = <time> [MINUTES]
  | ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS = { ON | OFF }
  | OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF }
  | MAXDOP = max_degree_of_parallelism
  | DATA_COMPRESSION = { NONE | ROW | PAGE }
     [ ON PARTITIONS ( { <partition_number_expression> | <range> }
     [ , ...n ] ) ]
}

<filter_predicate> ::=
    <conjunct> [ AND <conjunct> ]

<conjunct> ::=
    <disjunct> | <comparison>

<disjunct> ::=
        column_name IN (constant ,...n)

<comparison> ::=
        column_name <comparison_op> constant

<comparison_op> ::=
    { IS | IS NOT | = | <> | != | > | >= | !> | < | <= | !< }

<range> ::=
<partition_number_expression> TO <partition_number_expression>

UNIQUE
테이블 또는 뷰에 고유 인덱스를 만듭니다. 고유 인덱스는 두 개의 행에 동일한 인덱스 키 값을 가질 수 없습니다. 뷰의 클러스터형 인덱스는 고유해야 합니다.

CLUSTERED

키 값의 논리적 순서가 테이블에 있는 해당 행의 물리적 순서를 결정하는 인덱스를 만듭니다. 클러스터형 인덱스의 최하위 수준인 리프 수준에는 테이블의 실제 데이터 행이 있습니다. 테이블 또는 뷰는 한 번에 클러스터형 인덱스 하나만 허용합니다.

고유 클러스터형 인덱스가 있는 뷰를 인덱싱된 뷰라고 합니다. 뷰에서 고유 클러스터형 인덱스를 만들면 물리적으로 뷰를 구체화합니다. 같은 뷰에 다른 인덱스를 정의하려면 먼저 고유 클러스터형 인덱스를 만들어야 합니다.

비클러스터형 인덱스를 만들기 전에 항상 클러스터형 인덱스를 만듭니다. 테이블에 있는 기존의 비클러스터형 인덱스는 클러스터형 인덱스를 만들 때 다시 작성됩니다.

CLUSTERED를 지정하지 않으면 비클러스터형 인덱스가 만들어집니다.

NONCLUSTERED
테이블의 논리적 순서를 지정하는 인덱스를 만듭니다. 비클러스터형 인덱스에서는 데이터 행의 물리적 순서가 인덱싱된 순서와 다릅니다.

각 테이블에는 비클러스터형 인덱스를 PRIMARY KEY와 UNIQUE 제약 조건을 사용하여 암시적으로 만들거나 CREATE INDEX를 사용하여 명시적으로 만드는 방법에 관계없이 999개까지 만들 수 있습니다.

인덱싱된 뷰의 경우 비클러스터형 인덱스는 이미 고유 클러스터형 인덱스가 정의되어 있는 뷰에서만 만들 수 있습니다.

달리 지정하지 않으면 기본 인덱스 유형은 NONCLUSTERED입니다.

INCLUDE ( column [ , ... n ] )
비클러스터형 인덱스의 리프 수준에 키가 아닌 열을 추가하도록 지정합니다. 비클러스터형 인덱스는 고유하거나 고유하지 않을 수 있습니다.

WHERE <filter_predicate>
인덱스에 포함할 행을 지정하여 필터링된 인덱스를 만듭니다. 필터링된 인덱스는 테이블의 비클러스터형 인덱스여야 합니다. 필터링된 인덱스의 데이터 행에 대한 필터링된 통계를 만듭니다.

필터 조건자는 간단한 비교 논리를 사용하며 계산 열, UDT 열, 공간 데이터 형식 열 또는 HierarchyID 데이터 형식 열을 참조할 수 없습니다. 비교 연산자에는 NULL 리터럴을 사용한 비교를 사용할 수 없습니다. 대신 IS NULL 및 IS NOT NULL 연산자를 사용합니다.

ON 파일 그룹 옵션은 인덱스를 별도의 파일 그룹에 저장하게 된다. 이렇게 하면 디스크 입출력을 분산시켜서 성능에 도움이 될 수 있다. 이를 '분할 인덱스'라 부른다.

<relational_index_option>::=
인덱스를 만들 때 사용할 옵션을 지정합니다.

<relational_index_option> ::=
{
    PAD_INDEX = { ON | OFF }
  | FILLFACTOR = fillfactor
  | SORT_IN_TEMPDB = { ON | OFF }
  | IGNORE_DUP_KEY = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF }
  | STATISTICS_INCREMENTAL = { ON | OFF }
  | DROP_EXISTING = { ON | OFF }
  | ONLINE = { ON | OFF }
  | RESUMABLE = { ON | OFF }
  | MAX_DURATION = <time> [MINUTES]
  | ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS = { ON | OFF }
  | OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF }
  | MAXDOP = max_degree_of_parallelism
  | DATA_COMPRESSION = { NONE | ROW | PAGE }
     [ ON PARTITIONS ( { <partition_number_expression> | <range> }
     [ , ...n ] ) ]
}

PAD_INDEX FILLFACTOR

PAD_INDEX의 기본값은 OFF이며, 만약 ON으로 설정되었다면 FILLFACTOR에 설정된 값이 의미를 갖게 된다. 간단히 말하면 이는 인덱스 페이지를 생성할 대 얼마만큼의 여유를 두겠냐는 의미다.
기본적으로 SQL Server는 인덱스 생성시에 인덱스 페이지는 두 개의 레코드를 입력할 공간만 남겨 놓고 꽉 채운다. 그런데 그럴 경우에 인덱스의 갱신이 많이 발생되는 테이블의 경우에는 인덱스 페이지 분할이 자주 발생될 수밖에 없다.
그럴 경우에 이 FILLFACTOR을 높여주면 인덱스를 생성할 때 빈 공간의 비율을 높여서 페이지 분할이 일어날 확률은 낮춰주는 효과를 줄 수 있다.
FILLFACTOR를 50으로 설정하면 50%만 채우고 나머지 50%는 비워놓겠다는 의미가 된다.
즉, 인덱스를 생성하기 위한 공간은 2배가 필요해진다. 또한, 데이터 검색에 읽어올 페이지가 늘어나므로 검색이 기존보다 느려진다.
특별한 경우가 아니라면 이것을 설정할 필요는 없지만, 데이터가 자주 입력되는 경우라면 이 값을 지정해주는 것도 성능 향상에 도움을 줄 수 있다.

PAD_INDEX와 FILLFACTOR 옵션은, 오라클의 PCTFREE 및 PCTUSED 등과 비슷한 역할을 하는 옵션이다. 하지만, 최근 버전의 SQL Server나 오라클은 이러한 것을 자동으로 잘 설정해주므로, 일부러 값을 지정하는 경우가 드물어졌다.

SORT_IN_TEMPDB

SORT_IN_TEMPDB를 ON으로 설정하면, 디스크의 분리 효과가 발생해 인덱스를 생성하는데 드는 시간을 줄일 수가 있다.
디폴트는 OFF이며 인덱스도 생성할 때 필요로 하는 임시 파일의 읽기/쓰기 작업을 tempdb에서 수행하겠다는 의미다. 그러기 위해서는 우선 tempdb가 별도의 물리적으로 독립된 디스크여야 의미가 있으며 tempdb의 공간도 충분히 확보되어 있어야 할 것이다.

ONLINE

ONLINE의 디폴트는 OFF이다. ON으로 설정하면 인덱스 생성 중에도 기본 테이블에 쿼리가 가능하다. 잠시라도 시스템이 중단되어서는 안 되는 시스템에서는 유용하게 사용될 수 있다.

MAXDOP

MAXDOP는 인덱스 생성 시에 사용할 CPU의 개수를 강제로 지정하는 것이다. 최대 64까지 지정할 수 있다.
인덱스 생성 작업은 디스크 및 CPU의 사용량이 높으므로, 적절히 지정한다면 인덱스의 생성시간을 줄일 수 있다.
디폴트는 0이며 이는 시스템이 알아서 설정해 준다는 의미이다.
특별한 경우가 아니라면 이 옵션을 지정하지 않아도 시스템이 적절한 CPU의 개수를 사용한다.

DATA_COMPRESIION

DATA_COMPRESSION은 테이블의 압축과 동일한 개념으로 인덱스를 압출할 것인지를 지정할 수 있다.

다양한 인덱스 만들기

PRIMARY KEY, UNIQUE 제약을 지정해 만들기

PRIMARY KEY 나 UNIQUE 제약을 설정할 때 해당 열에 인덱스가 자동으로 만들어진다. PRIMARY KEY나 UNIQUE 제약은 해당 열에 중복된 데이터를 저장하지 못하게 막아주는 역할을 담당하며, 이러한 기능은 고유 한 인덱스(Unique Index)를 이용해 물리적으로 구현된다. PRIMARY KEY나 PRIMARY KEY CLUSTERED로 설정해서 고유한 클러스터형 인덱스를 만들 수 있고, UNIQUE CLUSTERED로 설정해서 고유한 클러스터형 인덱스를 만들 수 있다. 단순히 UNIQUE로 설정하면 고유한 비클 러스터형 인덱스를 만든다.

/*primary key 제약 지정해 만들기*/
CREATE TABLE TABLE1 (
	Col1 int primary key clustered,
	col2 int
)

/*UNIQUE 제약 지정해 만들기*/
CREATE TABLE TABLE2 (
	Col1 int primary key nonclustered,
	col2 int unique clustered
)

CREATE INDEX 문으로 만들기

CREATE CLUSTERED INDEX 문을 사용해서 클러스터형 인덱스를 만든다. CLUSTERED 옵션을 생략하면 NONCLUSTERED 옵션이 설정된 것으로 간주한다. 우리가 만드는 인덱스 대부분은 비클러스터형 인덱스이므로 기본값이 NONCLUSTERED가 아닐까 싶다.

/*단일 열에 클러스터형 인덱스 만들기*/
CREATE CLUSTERED INDEX CL_Col1
	on TABLE1(Col1)
go

/*복합 열에 클러스터형 인덱스 만들기*/
CREATE CLUSTERED INDEX CL_COL1_COL2
	ON TABLE1 (Col,Col2)
	go

/*고유한 클러스터형 인덱스 만들기*/
Create UNIQUE CLUSTERED INDEX UCL_COL1
ON TABLE1(COL1)
GO

비클러스터형 인덱스

PRIMARY KEY, UNIQUE 제약을 지정해 만들기

PRIMARY KEY NONCLUSTERED로 설정해서 고유한 비 클러스터형 인덱스를 만들 수 있고, UNIQUE나 UNIQUE NONCLUSTERED로 설정해서 고유한 비클러스터형 인덱스를 만들 수 있다. 단순히 PRIMARY KEY로 설정하면 고유한 클러스터형 인덱스를 만든다.

/*primary key 제약 지정해 만들기*/
CREATE TABLE TABLE1 (
	Col1 int primary key NONclustered,
	col2 int
)

/*UNIQUE 제약 지정해 만들기*/
CREATE TABLE TABLE2 (
	Col1 int primary key ,
	col2 int unique nonclustered
)

CREATE INDEX문으로 만들기

CREATE INDEX문이나 CREATE NONCLUSTERED INDEX 문을 사용해서 비클러스터형 인덱스를 만든다. NONCLUSTERED 옵션을 생략할 수 있다.

/*단일 열에 클러스터형 인덱스 만들기*/
CREATE CLUSTERED INDEX CL_Col1
	on TABLE1(Col1)
go

/*복합 열에 클러스터형 인덱스 만들기*/
CREATE CLUSTERED INDEX CL_COL1_COL2
	ON TABLE1 (Col,Col2)
	go

/*고유한 클러스터형 인덱스 만들기*/
Create UNIQUE CLUSTERED INDEX UCL_COL1
ON TABLE1(COL1)
GO

포함된 인덱스(included indexes)

비클러스터형 인덱스를 만들 때 INCLUDE 절을 사용하면, 비클러스터형 인덱스 ㅡ리프 페이지에 인덱스 키 열이 아닌 다른 열을 저장할 수 있다. 리프 페이지에 인덱스 키가 아닌 다른 열을 저장하는 이유는 쿼리문이 찾는 열을 인덱스에 포함해서 RID LOOKUP이나 KEY LOOKUP으로 인한 성능 저하는 막기 위함이다.

CREATE NONCLUSTERED INDEX NCL_COL1
	ON TABL1(COL1)
	INCLUDE(CO2,CO3)
GO

쿼리문이 찾는 데이터를 모두 가지고 있는 인덱스를 '커버링 인덱스(Covering Index)'라 하고, 인덱스 에서 찾던 데이터를 다 찾은 쿼리를 '커버드 쿼리(Covered Query)'라 한다.

필터된 인덱스(Filtered Indexeds)

CREATE NONCLUSTERED INDEX NCL_Col1
	On TABLE1(Col1)
    Where (Col2 is NOT NULL)

WHERE 절 조건을 만족하는 데이터에 대해서만 만들어진 비클러스터형 인덱스를 '필터된 인덱스'라 한다. 전체 데이터가 아닌 자주 사용되는 일부 데이터에 대해서만 인덱스를 만들게 되니, 인덱스를 만드는 시간이 단축되고, 크기가 작아서 인덱스 찾기가 빨라지니 쿼리문이 빠르게 수행된다. 게다가 물리적인 저장 공간도 적게 차지한다. 인덱스를 만들 때 만들어진 통계도 일부 데이터만을 가지고 만들게 되니, 통계가 더욱 정확하다. 또한. 정확한 통계를 기반으로 믿을만한 실행 계획이 만들어 진다.

인덱스 변경

ALTER INDEX(Transact-SQL)

인덱스를 사용하지 않도록 설정, 다시 빌드 또는 다시 구성하거나 인덱스에 대한 옵션을 설정하여 기존 테이블 또는 뷰 인덱스 (rowstore, columnstore, 또는 XML)를 수정합니다.

-- Syntax for SQL Server and Azure SQL Database
  
ALTER INDEX { index_name | ALL } ON <object>  
{  
      REBUILD {  
            [ PARTITION = ALL ] [ WITH ( <rebuild_index_option> [ ,...n ] ) ]   
          | [ PARTITION = partition_number [ WITH ( <single_partition_rebuild_index_option> ) [ ,...n ] ]  
      }  
    | DISABLE  
    | REORGANIZE  [ PARTITION = partition_number ] [ WITH ( <reorganize_option>  ) ]  
    | SET ( <set_index_option> [ ,...n ] )   
    | RESUME [WITH (<resumable_index_options>,[...n])]
    | PAUSE
    | ABORT
}  
[ ; ]  
  
<object> ::=   
{  
    { database_name.schema_name.table_or_view_name | schema_name.table_or_view_name | table_or_view_name }  
}  
  
<rebuild_index_option > ::=  
{  
      PAD_INDEX = { ON | OFF }  
    | FILLFACTOR = fillfactor   
    | SORT_IN_TEMPDB = { ON | OFF }  
    | IGNORE_DUP_KEY = { ON | OFF }  
    | STATISTICS_NORECOMPUTE = { ON | OFF }  
    | STATISTICS_INCREMENTAL = { ON | OFF }  
    | ONLINE = {   
          ON [ ( <low_priority_lock_wait> ) ]   
        | OFF } 
    | RESUMABLE = { ON | OFF } 
    | MAX_DURATION = <time> [MINUTES}     
    | ALLOW_ROW_LOCKS = { ON | OFF }  
    | ALLOW_PAGE_LOCKS = { ON | OFF }  
    | MAXDOP = max_degree_of_parallelism  
    | DATA_COMPRESSION = { NONE | ROW | PAGE | COLUMNSTORE | COLUMNSTORE_ARCHIVE }   
        [ ON PARTITIONS ( {<partition_number> [ TO <partition_number>] } [ , ...n ] ) ]  
}  
  
<single_partition_rebuild_index_option> ::=  
{  
      SORT_IN_TEMPDB = { ON | OFF }  
    | MAXDOP = max_degree_of_parallelism  
    | RESUMABLE = { ON | OFF } 
    | MAX_DURATION = <time> [MINUTES}     
    | DATA_COMPRESSION = { NONE | ROW | PAGE | COLUMNSTORE | COLUMNSTORE_ARCHIVE } }  
    | ONLINE = { ON [ ( <low_priority_lock_wait> ) ] | OFF }  
}  
  
<reorganize_option>::=  
{  
       LOB_COMPACTION = { ON | OFF }  
    |  COMPRESS_ALL_ROW_GROUPS =  { ON | OFF}  
}  
  
<set_index_option>::=  
{  
      ALLOW_ROW_LOCKS = { ON | OFF }  
    | ALLOW_PAGE_LOCKS = { ON | OFF }  
    | OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF }
    | IGNORE_DUP_KEY = { ON | OFF }  
    | STATISTICS_NORECOMPUTE = { ON | OFF }  
    | COMPRESSION_DELAY= { 0 | delay [Minutes] }  
}  

<resumable_index_option> ::=
 { 
    MAXDOP = max_degree_of_parallelism
    | MAX_DURATION =<time> [MINUTES]
    | <low_priority_lock_wait>  
 }
 
<low_priority_lock_wait>::=  
{  
    WAIT_AT_LOW_PRIORITY ( MAX_DURATION = <time> [ MINUTES ] ,   
                          ABORT_AFTER_WAIT = { NONE | SELF | BLOCKERS } )  
}

REBUILD

REBUILD 옵션은 인덱스를 삭제하고 다시 생성하는 효과가 있다. 만약, 비클러스터형 인덱스에 어떠한 이유로 불일치가 생긴다면, 다시 생성해서 문제를 해결할 수도 있다. 대용량 데이터베이스의 경우에는 오프라인 후에 전체 인덱스의 재생성이 오래 걸릴 수도 있다. ONLINE = ON 옵션을 추가해 주면 인덱스 재생성 중에도 시스템이 계속 가동된다. 단, 시스템이 느려질 수는 있다.

REORGANIZE

REORGANIZE. 옵션은 인덱스를 다시 구성해 준다. REBUILD와 달리 인덱스를 삭제하고 다시 생성해 주는 것은 아니다. 이 옵션을 사용하는 경우는 테이블을 오랫동안 사용하면 인덱스가 조각화 되어 있는 것을 모아주는 효과를 내서 시스템 성능에 약간의 도움을 줄 수 있다.

인덱스 제거

Primary Key 제약 조건과, Unique 제약 조건으로 자동 생성된 인덱스는 DROP INDEX로 제거 할 수 없다. 이 경우에는 ALTER TABLE 구문으로 제약 조건을 제거하면 인덱스도 자동으로 제거 된다.

DROP INDEX로 시스템 테이블의 인덱스를 제거할 수 없다.

인덱스를 모두 제거할 떄는 되도록 비클러스터형 인덱스부터 삭제하도록 한다. 클러스터형 인덱스를 먼저 삭제하면 클러스터형 인덱스의 루트 페이지가 없어진다. 비 클러스터형 인덱스를 먼저 삭제하면 클러스터형 인덱스의 루트 페이지가 없어진다.

참조 해야될 사이트

docs.microsoft.com/ko-kr/sql/t-sql/statements/create-index-transact-sql?view=sql-server-ver15&source=docs

docs.microsoft.com/ko-kr/sql/t-sql/statements/alter-index-transact-sql?view=sql-server-ver15

ALTER INDEX(Transact-SQL) - SQL Server

ALTER INDEX(Transact-SQL)

docs.microsoft.com

'데이터베이스' 카테고리의 다른 글

SQL 데이터베이스 함수란? (0)	2021.05.20
저장 프로시저 (0)	2021.04.29
뷰(View) (0)	2021.04.29
인덱스 (0)	2021.04.23
인덱스 (0)	2021.04.22

꼼꼼히 살펴보자