: 추가적인 쓰기 작업과 저장 공간을 활용하여 DB 테이블의 검색 속도를 향상시키기 위한 자료구조
"책의 앞 부분에 있는 목차!"
=> 목차가 없다면, 찾으려고 하는 내용을 만날 때까지 책을 한 장씩 모두 넘겨야 한다.
=> 목차가 있기 때문에, 책의 어느 위치에 원하는 내용이 있는지 쉽게 찾을 수 있다!
=> 데이터를 조회할 때 테이블의 모든 데이터를 하나하나 읽는 것보다 더 적은 페이지를 읽음으로써,
발생하는 Disk I/O를 줄일 수 있다.
But, 인덱스 역시 Page로써 존재하므로 물리적인 공간을 차지한다.
SELECT의 성능은 향상시킬 수 있지만, UPDATE,INSERT,DELETE와 같은 데이터 변경 작업의 경우, 모든 인덱스에도 동일하게 작업이 수행되어야 하기 때문에 오히려 성능을 저하시킬 수 있다!
SQL Server상에서의 테이블의 존재 형태는 크게 힙(Heap)과 클러스터형 인덱스가 부여된 클러스터형 테이블로 나눌 수 있다.
MSSQL의 테이블과 인덱스
힙(Heap)이란?
정렬의 기준이 없이 저장된 테이블의 형태(=클러스터형 인덱스가 없는 테이블)
데이터 페이지(Page) 내의 행(Row)들 간 순서가 없고, 페이지들 간에도 순서가 없음
장점) INSERT 수행에 용이. 새로운 행(Row)을 기존 페이지(Page)의 빈 곳에 추가하면 되고, 빈 공간이 없으면 새로운 페이지에 추가하면 됨.
단점) SELECT 문에 취약. 데이터가 극히 적으면 괜찮지만, 데이터가 많은 경우 전체적으로 모든 테이블을 스캔(Scan)해야 하기 때문에 데이터를 찾기가 어려움.
클러스터형 인덱스(Clustered Index)란?
특정 열(Column) 또는 여러개의 열들을 기준으로 데이터가 정렬 가능하게 하는 자료구조
테이블 자체를 인덱스로 만드는 형태이므로, 테이블당 하나의 클러스터형 인덱스를 설정할 수 있음.
레코드의 물리적 순서가 인덱스의 엔트리 순서와 일치하게끔 유지시킴.
데이터가 많고, Select 문 수행이 많은 경우효과적. => 데이터의 변경이 잦은 컬럼의 경우, 데이터 변경 때마다 데이터 정렬 연산이 이루어져야 하므로, DB 서버의 부하를 유발.
자주 사용하는 쿼리에 사용되는 컬럼 또는 고유한 값을 많이 가지고 있는 컬럼에 생성하는 것이 효율적.
Between, <, <=와 같이 범위 값을 반환하는 쿼리에 효율적. 클러스터형 인덱스 특성상, 데이터가 정렬되어 있기 때문에, 하나의 데이터를 찾으면 해당 데이터와 인접한 데이터는 아주 빠르게 조회 가능. (=> ORDER BY / GROUP BY에 사용하는 것도 효율적.)
JOIN 조건에 사용되는 경우 클러스터형 인덱스를 활용하면 효율적.
테이블을 생성할 때 Primary Key 조건을 사용하면 고유(Unique) 인덱스가 자동으로 생성되는데, 기본적으로 이 인덱스가 클러스터형 인덱스이다.
클러스터형 인덱스의 동작 방식(Seek vs Scan)
Clustered Index Seek : B-Tree 구조상의 Root 페이지에서 Leaf Level까지 클러스터형 인덱스 키값을 통해 조건을 탐색하여 검색 경로를 따라 수행되는 방법으로, 인덱스를 타고 특정 범위만을 읽어내므로 매우 빠름.
Clustered Index Scan : 클러스터형 인덱스가 존재하나, 인덱스 키로 지정된 열을 탐색의 조건으로 사용할 수 없을 때 사용되는 방식이다. Leaf Level의 첫번째 페이지(Page)부터 차례로 모든 페이지를 읽는 방법으로, 사실상 힙에서 사용했던 방식인 Table Scan과 다를바 없음.
비클러스터형 인덱스(Non-Clustered Index)란?
리프(Leaf) 페이지가 데이터 페이지가 아니므로, 인덱스와 실제 데이터의 정렬 상태와는 별도. 비클러스터형 인덱스의 구조는 데이터 행으로부터 독립적이다.
클러스터형 인덱스보다 검색(SELECT) 속도는 느리지만, 비교적 데이터의 변경 면에서는 더 빠르다.
클러스터형 인덱스와 다르게, 한 테이블당 여러 개의 비클러스터형 인덱스 설정이 가능함.
힙 구조에 비클러스터형 인덱스를 적용시킨 것과, 클러스터형 테이블에 비클러스터형 인덱스를 적용시켰을 때의 데이터를 찾는 방식이 다름.
힙 구조 힙(Heap) 구조에 비클러스터형 인덱스 적용
- 힙 구조에서의 비클러스터형 인덱스는 [인덱스 키 값 + RID(해당 Row의 주소)]로 이루어짐. - RID는 거의 변하지 않으며 크기가 크지 않고, RID를 통해 해당 데이터를 한 번에 찾아갈 수 있음. - Non-Clustered Index Scan : 구문에서 요구하는 열들이 비 클러스터형 인덱스에 모두 포함되어 있으나, 키로 정의된 열이 탐색의 조건으로 사용될 수 없는 경우에 모든 행을 읽어내면서 조회하는 방식. 주로 조건절이 인덱스의 키로 정의된 열에서 가공된 형태로 주어지면 실행되는 조회 방식. - Non-Clustered Index Seek : 구문에서 요구하는 열들이 비 클러스터형 인덱스에 모두 포함되어 있으면서, 키로 지정한 열이 탐색의 조건으로 사용된 경우에 수행되는 방식. 조건으로 사용된 열이 인덱스 키와 동일하기 때문에 특정 범위만을 탐색하게 되므로 매우 빠름. - RID Lookup(Non-Clustered Index + Heap Table) : 비 클러스터형 인덱스를 읽은 후에, 참조해야 하는 열 데이터가 부족하여 해당 테이블을 조인하는 과정이다. 인덱스 키 값과 매칭되는 RID를 통해 해당 데이터(Row)에 빠르게 접근. * 아래 RID Lookup 예시 SQL구문 참조
클러스터형 테이블 클러스터형 테이블에 비클러스터형 인덱스 적용
- 클러스터형 테이블에서의 비클러스터형 인덱스는 [인덱스 키 값 + 클러스터형 인덱스 키 값] 으로 이루어짐. - Key Lookup(Non-Clustered Index + Clustered Index) : 비클러스터형 인덱스에 필요한 열 데이터가 부족하여 조인을 통해 열을 가져오는 과정으로 RID Lookup과 매커니즘은 비슷해 보이나, RID 값이 아닌 클러스터형 인덱스의 Key 값을 포함하고 있다는 점에서 차이가 있다. * 아래 Key Lookup 예시 SQL 참조
EX)
# RID Lookup 작동 설명 위한 예시 SQL
SELECT EName
FROM Employee
WHERE ID='C02'
# 위 구문에서 요구하는 결과는 [ID] = 'C02' 조건에 만족하는 행의 [EName] 열이다.
# Non-Clustered Index인 [ID]값을 기준으로 빠르게 탐색 후, 인덱스에 존재하지 않는 [EName] 열을 가져오기 위해,
# RID Lookup을 수행하여 [ID]와 매칭되는 [RID] 값으로 해당 로우가 있는 테이블을 조인하여 [EName] 값을 가져온다.
# Key Lookup 작동 설명 위한 예시 SQL
SELECT EName
FROM Employee
WHERE ID='C02'
# Non-Clustered Index로 [ID] 열을 탐색하고, 인덱스에 존재하지 않는 [EName]열은 Clustered Index로부터 가져온다.
# 이때 Non-Clustered Index에 포함되어 있는 Clustered Index의 Key값을 통해 Lookup을 수행하여
# Clustered Index를 탐색하고 최종 [EName] 열을 출력한다.
최근댓글