[22/06/08] MSSQL 인덱스

인덱스 생성 삭제 조회

CREATE CLUSTERED INDEX idx_t1 ON [dbo].['강의 데이터$'] ([재생목록코드] ASC);

SELECT * FROM [dbo].['강의 데이터$']

EXEC SP_HELPINDEX ['강의 데이터$']

DROP INDEX [dbo].['강의 데이터$'].[idx_t1]

출처 : https://datalibrary.tistory.com/63

▶ 인덱스의 의미

아직 인덱스가 무엇을 가리키는 말인지 헷갈리는 분들은 사전에서 볼 수 있는 목차나 분류를 생각하시면 좋습니다.
방대한 양의 정보를 담고 있는 책에서 원하는 정보에 빠르게 접근할 수 있도록 도와주는 역할을 합니다.

만약에 책에 이런 인덱스가 없다면 원하는 내용을 찾기 위해서 한참을 책 속을 돌아다녀야 하겠지요. 데이터베이스도 마찬가지입니다. 만약 인덱스를 사용하지 않는 테이블의 데이터를 조회하려고 하는 경우에는 테이블의 모든 내용을 샅샅이 찾고 난 뒤에 원하는 데이터를 사용할 수 있게 됩니다. 데이터를 찾는데 오랜 시간이 걸릴수록 작업에 소모되는 리소스가 많이 발생하게 되고 리소스의 과도한 사용량은 성능이 저하되는 현상으로 이어집니다. 그렇기 때문에 인덱스를 설정해서 데이터를 찾는 시간을 단축시키고 사용되는 리소스를 줄이도록 하는 것이 인덱스의 목적입니다.

▶ 기본 키(PK: Primary Key)와 인덱스의 관계

키는 테이블 내의 고유한 값을 의미하며 데이터베이스의 무결성을 유지하기 위한 제약조건으로서의 역할을 수행합니다(키 ≠ 인덱스).기본적으로 SQL Server에서는 PK를 생성하면 Clustered 인덱스를 자동으로 생성합니다. 하지만 앞서 언급한 것처럼 키는 제약조건으로서의 역할을 하는 것이기 때문에 키를 생성한다고 해서 반드시 인덱스가 동일하게 필요하다는 것은 아닐 수 있습니다. 인덱스를 생성할 때는 반드시 신중해야 합니다.

▶ SQL Server에서 사용할 수 있는 인덱스

1. Clustered 인덱스
   Clustered 인덱스를 설정하게 되면 인덱스 키로 설정한 값 기준으로 정렬해서 데이터를 테이블 내에 저장해두기 때문에 이 인덱스를 사용할 수 있는 쿼리에 대해서 조회 속도가 향상될 수 있습니다. 데이터가 정렬되는 순서는 테이블에 하나만 존재할 수 있기 때문에 이 인덱스는 테이블에 하나만 생성이 가능합니다.

2. Non-Clustered 인덱스
   한 테이블에 여러개 설정할 수 있는 인덱스로 일종의 포인터 역할을 합니다. 실제 데이터가 정렬이 되는 Clustered Index와 달리 인덱스 노드 안에서만 정렬이 되고 실제 데이터 페이지는 정렬이 되어 있지 않는 구조를 가집니다. 그렇기 때문에 Clustered Index와 달리 테이블에 여러 개의 Non Clustered Index를 생성할 수 있지만, 과도한 인덱스 생성은 오히려 데이터베이스의 성능을 저하하는 요인이 될 수 있기 때문에 신중하게 생성해야 합니다.

3. Unique 인덱스
   각각의 레코드가 고유하게 가지고 있는 인덱스입니다. 이 인덱스에 사용되는 키는 테이블 내에서 절대 중복되는 값을 가지고 있지 않습니다. Unique 설정으로 인덱스를 생성하게 되면 중복되는 값이 더 이상 없을 때까지 데이터 조회가 실행되지 않아도 되기 때문에(옵티마이저가 이미 값이 유일하다는 것을 알고 있기 때문에) 쿼리 성능이 향상 될 수 있습니다.

4. Included 인덱스
   Non Clustered Index에 생성할 수 있습니다. 인덱스 키로 사용하는 열 이외에 다른 열을 인덱스 페이지에 포함하는 인덱스입니다. Included 인덱스 사용을 고려해야 하는 경우는 인덱스 키로 사용하지는 않는 칼럼 중 자주 조회해야 하는 칼럼이 있는 경우입니다. 예를 들어 직원 ID를 사용해서 직원의 이름을 자주 조회해야 하는 경우가 해당됩니다. 직원 ID 이외에 직원 이름을 인덱스 페이지에 포함시키게 되면 Key Lookup이나 RID Lookup과 같이 데이터 노드까지 내려가서 데이터를 조회해야 하는 동작이 발생할 필요 없이 인덱스 페이지 내에서 필요한 데이터를 모두 찾을 수 있기 때문에 조회 성능이 향상될 수 있습니다.

• 인덱스 페이지 내에서 쿼리가 완료될 수 있는 쿼리를 Covering Query, 그 때 사용되는 인덱스를 Covered Index라고 합니다.

• 조회에 사용하는 모든 열을 인덱스 키로 사용하지 않고 Included 인덱스로 사용하는 이유는 우선 인덱스에 사용되는 저장공간을 줄이기 위해서이고, SQL Server가 현재 지원하는 인덱스에 담을 수 있는 키의 개수가 16개, 전체 인덱스 크기는 900byte(Clustered Index: 900byte, Non-Clustered Index: 1700byte)로 한정되어 있기 때문입니다. 게다가 인덱스 키로 설정할 수 없는 nvarchar(max)나 xml 같은 데이터 타입의 열도 Included 인덱스로 설정하면 사용할 수 있기 때문입니다.

5. Full-text 인덱스
   문자열 데이터의 조회 성능 향상을 위한 특별한 기능을 담당하는 인덱스로, Microsoft Full-Text Engine이라고 하는 SQL Server의 특별한 엔진에 의해서 생성되고 만들어집니다.

6. Spatial 인덱스
   Geometry 타입의 열에 대한 성능 향상을 위한 특별한 기능을 담당하는 인덱스입니다.

7. Filtered 인덱스
   Non Clustered Index에 설정할 수 있고 테이블 전체에 대한 인덱스가 생성되는 것이 비효율적이라고 판단되었을 때 일부 데이터에 대해서만 범위를 설정해서 인덱스를 생성할 수 있습니다. 자주 조회하는 데이터의 분량이 테이블의 일부로 제한되어 있는 경우에 사용하면 조회 성능을 향상 시킬 수 있습니다.

8. XML 인덱스
   XML 타입의 열의 성능 향상을 위한 인덱스입니다.

9. Columnstore 인덱스
   행 단위 기반의 일반적인 인덱스와 달리 열 단위로 저장되는 인덱스입니다. 대용량의 데이터를 적재하는 환경에서 사용합니다.

참고하기 : https://m.blog.naver.com/islove8587/220605402338