[데이터베이스] 인덱스 + 심화 (커버링 인덱스, 실행 계획, 인덱스 스캔)

인덱스

인덱스란?

인덱스는 데이터베이스 테이블의 하나 이상의 컬럼을 기반으로 생성된다.

인덱스는 컬럼의 값과 레코드 주소를 key - value로 가지고 있는 정보이다.

인덱스를 통해 데이터베이스의 레코드를 빠르게 조회할 수 있다.

 

인덱스는 빠른 조회를 위해 내부적으로 트리 구조(B-Tree, B+Tree)를 사용하여 키 값을 정렬된 상태로 유지한다.

새로운 레코드가 삽입되거나 기존 레코드가 수정될 경우 인덱스도 그에 맞춰 업데이트되어야한다.

이 과정에서 추가적인 연산이 발생하므로, 이로 인해 레코드의 삽입, 수정, 삭제 성능이 저하될 수 있다.

 

인덱스 동작 방식

B-Tree 인덱스를 기준으로, 인덱스가 걸린 컬럼이 조건으로 들어왔을 경우를 가정한다.

루트 노드 -> 브랜치 노드(내부 노드) -> 리프 노드 순으로 노드를 찾고, 리프 노드에 있는 레코드의 실제 주소값으로 레코드를 조회한다.

 

인덱스의 특징

• 인덱스는 테이블에서 한 개 이상의 속성을 이용하여 생성한다.
• 빠른 검색과 함께 효율적인 레코드 접근이 가능하다.
• 순서대로 정렬된 속성과 데이터의 위치만 보유하므로 테이블보다 작은 공간을 차지한다.
• 저장된 값들은 테이블의 부분집합이 된다.
• 일반적으로 B-tree 구조를 가진다.
• 데이터의 수정, 삭제 등의 변경이 발생하면 인덱스의 재구성이 필요하다.

 

B-Tree

B-Tree는 데이터의 검색 시간을 단축시키기 위해 바이어에 의해 고안되었다.

B-Tree의 각 노드는 키 값과 포인터를 가진다.
키 값은 오름차순이며, 키 좌우에 있는 포인터는 각각 키 값보다 작은 값과 큰 값에 대한 다음 노드를 가리킨다. => 키 값을 비교하여 다음 단계의 노드를 쉽게 찾을 수 있다.

• 검색 : 모든 노드는 루트 노드에서부터 시작하여 내부 노드를 지나 리프 노드까지 내려가면서 이루어진다.
• 키 값이 새로 추가되거나 삭제될 경우 동적으로 노드의 분할 및 통합이 이루어져 트리가 항상 균형 상태를 유지한다.

 

B-Tree에서 검색 예

• 리프 노드에는 해당 데이터의 저장 위치에 대응하는 레코드번호(RID = Record IDentify, 실제 테이블의 대상 행의 논리적 위치)를 가지고 있어 찾고자 하는 행을 바로 찾을 수 있다.
• B-tree는 데이터를 검색할때 특유의 트리 구조를 이용하기 때문에 한 번 검색할때마다 검색 대상이 줄어 접근 시간이 적게 걸린다. 이러한 특징 때문에 주요 DBMS에서는 인덱스의 기본 구조로 B-tree를 많이 사용한다.
• 데이터의 변경이나 추가가 잦을 경우 B-tree의 모양을 유지하기 위해 노드의 분할 및 이동이 자주 발생하는 문제가 있다.

 

인덱스의 종류

클러스터 인덱스

연속된 키 값의 레코드를 묶어서 같은 블록에 저장하는 방법
- 테이블 당 하나만 생성할 수 있으며, 리프 노드에 데이터의 저장 위치에 대응하는 RID를 저장하는 대신, 테이블의 열 자체가 저장된다.
- 클러스터 인덱스는 리프 노드에 테이블 자체가 저장되기 때문에 특별하게 테이블의 해당 열을 찾기 위한 RID를 가질 필요가 없다.
- 키 값에 의한 동등 검색 및 범위 검색(BETWEEN)에 모두 유리하다.
- 인덱스 페이지가 단순해 인덱스를 저장하는데 차지하는 공간도 작다.
- 테이블 생성시 기본키(PK)를 생성하면 자동으로 생성된다.

- MySQL의 InnoDB는 기본적으로 클러스터 인덱스로 저장된다.

 

비클러스터 인덱스

그림에서 RID는 페이지 번호 - 페이지 내 행의 위치로 구성, 일반적인 B-tree 인덱스이다.

• 클러스터 인덱스와 달리 비클러스터 인덱스는 테이블과 별도로 구성된다. 따라서 테이블 당 여러 개를 생성할 수 있다.
• 그러나 테이블과 인덱스가 별도의 페이지에 저장되므로 많은 저장공간이 요구된다.
• 특정 값을 찾는 검색의 경우 성능을 보장할 수 있지만, 범위 검색은 테이블 페이지의 값이 저장된 순서에 따라 원하는 만큼의 효과를 보지 못할 수도 있다.

그림은 bookid 속성으로 비 클러스터 인덱스를 생성한 경우이다.
리프 노드에 실제 데이터 값이 아닌 테이블에 데이터가 위치한 행번호인 RID를 가지고 있다.
그리고 저장된 값들이 bookid 기준으로 정렬되어 있지 않고 무작위로 저장되어 있는 것을 알 수 있다.
일반적으로 테이블의 데이터가 갱신 또는 삭제되면 최종적으로 그림과 같이 무작위로 저장된다.

 

인덱스의 생성

인덱스는 데이터 검색을 빨리 하기 위해 사용하지만 인덱스를 생성한다고 데이터 검색이 무조건 빨라지는 것은 아니다.
데이터의 양이 별로 없는 테이블이거나 열의 데이터 값이 몇 종류 되지 않아 선택도가 높은 경우는 인덱스가 없을 때 보다 더 느려질 수 있다. ex)성별: {남, 여}

선택도 = 1/서로 다른 값의 개수

 

인덱스 생성 고려사항

• 인덱스는 WHERE 절에 자주 사용되는 속성이어야 한다.
• 인덱스는 JOIN에 자주 사용되는 속성이어야 한다.
• 단일 테이블에 인덱스가 많으면 속도가 느려질 수 있다.
• 속성이 가공되는 경우 사용하지 않는다.
• 속성의 선택도가 낮을 때 유리하다.

즉, 검색 조건에 자주 사용되고 카디널리티가 높은 컬럼을 인덱스로 설정하면 좋다.

 

인덱스 생성 방법

CREATE [UNIQUE] [CLUSTERED/NONCLUSTERED] INDEX 인덱스이름
ON 테이블이름 (속성이름 [ASC|DESC][,...n])

• CLUSTERED, NONCLUSTERED 옵션 지정안할 경우 NONCLUSTERED로 지정된다.

Customer 테이블의 name 열을 대상으로 클러스터 인덱스 cix_Customer 생성하기

CREATE CLUSTERED INDEX cix_Customer ON Customer (name);

 

인덱스가 많아질 경우 부작용

인덱스는 그 자체로 저장 공간이 필요하므로 저장 공간을 많이 차지할 수 있다.

또한 인덱스가 많아질 경우 정렬을 유지하는 과정에서 데이터 삽입, 수정, 삭제 성능이 저하될 수 있다.

 

인덱스의 재구성과 삭제

B-tree 인덱스의 경우 데이터의 수정, 삭제, 삽입이 잦으면 노드의 갱신이 주기적으로 일어나 단편화 현상이 일어난다. 단편화가 일어날 경우 검색 시 성능 저하로 이어진다.

단편화 : 삭제된 레코드의 인덱스 값 자리가 비게 되는 상태
=> 단편화 발생시 인덱스를 다시 생성한다.(ALTER)

 

인덱스 재구성

ALTER INDEX {인덱스이름|ALL}
ON 테이블이름 {REBUILD | DISABLE | REORGANIZE}

• REBUILD : 인덱스를 다시 생성하여 단편화가 많은 인덱스를 버린다.
• REORGANIZE : 기존 인덱스의 페이지들을 합병하거나 단편화를 제거한다.
• DISABLE : 해당 인덱스의 사용을 중지한다.

비클러스터 인덱스 ix_Book 재생성하기

ALTER INDEX ix_Book ON Book REBUILD;

 

인덱스 삭제

DROP INDEX 인덱스이름
    ON 테이블명

비클러스터 인덱스 ix_Book 삭제하기

DROP INDEX ix_Book ON Book;

 

 

Reference

책 : SQL Server로 배우는 데이터베이스 개론과 실습

https://m.yes24.com/Goods/Detail/97538787

SQL Server로 배우는 데이터베이스 개론과 실습 - 예스24

 

 

+ 추가 정리

 

랜덤 I/O vs 순차 I/O

랜덤 I/O : 임의의 위치에 있는 데이터를 읽고 쓰는 작업

순차 I/O : 연속적인 위치에 있는 데이터를 읽고 쓰는 작업

순차 I/O는 많은 양의 데이터를 순차적으로 읽을 때 사용되며,
인덱스 레인지 스캔과 인덱스 풀 스캔에서 사용됩니다.

 

랜덤 I/O는 인덱스 사용시 키 값을 기반으로 인덱스 트리를 탐색할때 사용됩니다.
또한 인덱스를 통해 식별된 데이터의 실제 위치를 알게 된 후 데이터를 읽을 때도 랜덤 I/O가 발생됩니다.

 

✅ HHD나 SSD에서 랜덤 I/O는 순차 I/O에 비해 헤더의 이동이 많으므로 느립니다. (탐색 시간과 회전 지연시간 추가 발생)

 

B-Tree vs B+Tree

모두 Balanced Tree 자료구조로 구현된 인덱스 저장 방식입니다.

 

B-tree는 key,value값을 모든 노드에 저장할 수 있어 탐색 시 리프노드까지 가지 않아도 됩니다.

 

B+tree는 리프노드를 제외하고 데이터를 가지고 있지않기 때문에 더 많은 포인터(노드)를 저장할 수 있고, Full Scan이나 부등호를 이용한 순차 검색시 리프노드만 읽으면 되므로 시간이 단축됩니다.

 

인덱스 스캔 방식

인덱스 레인지 스캔과 인덱스 풀 스캔이 있습니다.

 

인덱스 레인지 스캔은 검색 해야할 인덱스의 범위가 결정 됐을때 사용하는 방식입니다. (루트 -> 브랜치 -> 시작 리프노드 ~ 종료 리프노드)
인덱스 풀 스캔은 인덱스의 처음부터 끝까지 모두 스캔하는 방식입니다.

인덱스만 읽을 경우 인덱스 풀스캔(순차 I/O)이 좋지만, 랜덤 I/O가 발생할 경우(테이블에서 인덱스 외 추가적인 컬럼 정보를 가져와야할 경우) 테이블 풀 스캔(순차 I/O를 사용하여 더 빠름)이 효율적입니다.

 

예시

예를 들어 a,b,c 컬럼에 복합 인덱스가 걸려있을 경우

• 쿼리의 조건 절에 b, c 컬럼 순으로 들어가 있을 때 : a가 없어서 복합 인덱스를 타지 못하므로 인덱스 풀 스캔 작동(어쩔 수 없이 처음부터 끝까지 찾기..)
• 쿼리의 조건 절에 a, b 컬럼 순으로 들어가 있을 때 : 복합 인덱스를 타서 인덱스 레인지 스캔 작동

- 복합 인덱스는 선행하는 인덱스 순서와 컬럼 순서가 동일해야 탈 수 있습니다.

 

인덱스 사용시 주의해야할 점

인덱스 키 값이 너무 길어지면 안됩니다.
인덱스 키 값이 늘어날 수록 하나의 디스크 페이지에 저장할 수 있는 인덱스가 줄어든다.
그러므로 모든 인덱스를 저장하려면 더 많은 페이지가 필요하고, 디스크에 더 많은 페이지를 로딩(불러와야)하므로 랜덤 I/O가 증가합니다.
즉 속도가 감소됩니다.

 

 

또한 인덱스를 통해 테이블의 20-25% 이상 읽어와야한다면, 인덱스를 사용하지 않는 것이 좋습니다.

인덱스를 통해 데이터를 찾을 경우 발생하는 랜덤 액세스의 비용이 순차 액세스보다 높습니다.

또한 인덱스를 통해 대량의 데이터를 조회하면, 인덱스 자체를 탐색하는 데에도 비용이 들고 각 데이터에 대한 포인터를 따라가는 데에도 랜덤 액세스가 발생합니다.

만약 조회해야하는 데이터가 테이블에 상당 부분을 차지한다면, 순차 액세스인 풀 테이블 스캔을 이용하는 것이 더 효율적일 수 있습니다.

 

인덱스 적용 확인 방법

인덱스가 잘 동작하는지 확인하기 위해서는 실행 계획을 보거나, 실제로 쿼리를 날려보며 조회 속도를 측정하여 확인합니다.

select * from sys.schema_unused_indexes

위 쿼리로 사용하지 않는 인덱스가 있는지 확인할 수 있습니다.

 

쿼리 실행 계획

옵티마이저가 SQL을 어떻게 실행할 지에 대한 계획입니다.

EXPLAIN 키워드를 실행할 쿼리와 함께 작성하면 실행 계획을 볼 수 있습니다.

 

우선순위 기반의 규칙 기반 옵티마이저와 소요시간을 비용으로 산출하여 계산하는 비용 기반 옵티마이저가 있습니다.

 

힌트(Hint)

성능 최적화나 실행 계획을 수정하고자 할때 옵티마이저에게 특정 실행 계획을 사용하도록 지시하는 역할을 합니다.

힌트를 사용하여 옵티마이저가 쿼리를 처리하는 방식을 직접 제어할 수 있습니다.

 

커버링 인덱스(Covering index)

쿼리에 필요한 모든 데이터를 포함하는 인덱스입니다.
커버링 인덱스로 처리되는 쿼리는 테이블 스캔 없이 인덱스만 사용하여 데이터를 찾을 수 있습니다.

 

장점

장점으로는 성능 향상이 있습니다.

테이블이 클수록 전체 데이터를 스캔하는데 걸리는 시간이 늘어나는데, 커버링 인덱스를 사용하면 인덱스 크기만큼만 접근하고 그 정보를 바탕으로 필요한 데이터에 접근하므로 I/O 비용이 줄고 성능이 향상됩니다.

 

단점

단점으로는 후속 쿼리에서 원하는 컬럼을 join으로 불러와야합니다.

 

복합 인덱스(다중 컬럼 인덱스)

복합 인덱스는 다중 컬럼 인덱스라고도 하며, 2개 이상의 컬럼으로 이루어진 인덱스입니다.

인덱스가 구성된 순서에 영향을 받으며, 인덱스가 a, b, c 순일때 a에 의해 b가 정렬되며, c도 a, b 정렬의 영향을 받아 순서가 결정됩니다.

 

컬럼 순서가 선행하는 인덱스 순서와 동일해야 사용이 가능합니다.

즉, 일부만 포함할 경우 첫번째 인덱스부터 차례대로 포함해야합니다.

ex) 복합 인덱스 a,b,c일 경우 인덱스가 걸리려면 group by a 또는 group by a, b 또는 group by a, b, c

 

해시 인덱스

컬럼의 값을 해시값으로 계산하여 인덱스를 사용합니다.

매우 빠른 접근이 가능하지만, 값을 변형하여 인덱스로 사용하므로 prefix 일치 검색 및 범위 검색이 불가능합니다.