인덱스(INDEX)

1. 인덱스란?

DBMS에서 인덱스는 데이터의 저장 성능을 희생하고 그 대신 데이터의 읽기 속도를 높이는 기능이다.

인덱스를 역할별로 구분하면 PK와 Secondary Key로 구분해볼 수 있다.

• PK : 레코드를 대표하는 컬럼의 값으로 만들어진 인덱스
• Secondary Index : PK를 제외한 나머지 모든 인덱스

데이터의 중복 허용 여부로 구분하면 유니크 인덱스와 유니크하지 않은 인덱스로 구분할 수 있다. 유니크 여부는 실제 DBMS 쿼리를 실행해야 하는 옵티마이저에게는 상당히 중요한 문제이다.

유니크 인덱스에 대해 동등 조건으로 검색한다는 것은 항상 1건의 레코드만 찾으면 더 찾지 않아도 된다는 것을 옵티마이저에게 알려주기 때문이다.

2. 인덱스의 장점과 단점

장점

• 테이블을 조회하는 속도와 그에 따른 성능을 향상시킬 수 있다.
• 전반적인 시스템의 부하를 줄일 수 있다.

단점

• 인덱스를 관리하기 위해 DB의 약 10%에 해당하는 저장공간이 필요하다.
• 인덱스를 관리하기 위해 추가 작업이 필요하다.
• 인덱스를 잘못 사용할 경우 오히려 성능이 저하되는 역효과가 발생할 수 있다. (CREATE, DELETE, UPDATE가 빈번한 속성에 인덱스를 걸게 되면 인덱스의 크기가 비대해져서 성능이 오히려 저하되는 역효과가 발생할 수 있음)

3. 인덱스를 사용하면 좋은 경우

• 규모가 작지 않은 테이블
• INSERT, UPDATE, DELETE가 자주 발생하지 않는 컬럼
• JOIN이나 WHERE 또는 ORDER BY에 자주 사용되는 컬럼
• 중복되는 데이터가 최소한인 컬럼
• 항상 = 으로 비교되는 컬럼

4. 인덱스의 자료구조

a. B-Tree

B-Tree는 루트 노드 - 브랜치 노드 - 리프 노드 - 데이터 파일 구성을 가지고 있으며, (데이터 레코드 건수가 작은 경우 브랜치 노드가 없는 경우도 있을 수 있음) 인덱스의 키 값은 모두 정렬되어 있지만 데이터 파일의 레코드는 정렬되어 있지 않고 임의의 순서대로 저장되어 있다.

인덱스는 테이블의 키 컬럼만 가지고 있으므로 나머지 컬럼을 읽으려면 데이터 파일에서 해당 레코드를 찾아야 한다. 이를 위해 인덱스의 ‘리프 노드’는 데이터 파일에 저장된 레코드의 주소를 가지게 된다.

인덱스를 검색하는 작업은 B-Tree의 루트 노드부터 시작해 브랜치 노드를 거쳐 최종 리프 노드까지 이동하면서 비교 작업을 수행하는데, 이 과정을 ‘트리 탐색’ 이라고 한다. 인덱스 트리 탐색은 SELECT에서만 사용하는 것이 아니라 UPDATE나 DELETE를 처리하기 위해 항상 해당 레코드를 먼저 검색해야 할 경우에도 인덱스가 있다면 빠른 검색이 가능하다.

DB 인덱스로 B-Tree를 많이 사용하는 이유

• 항상 정렬된 상태로 특정 값보다 크고 작은 부등호 연산에 문제가 없다.
• 참조 포인터가 적어 방대한 데이터 양에도 빠른 메모리 접근이 가능하다.
• 데이터 탐색뿐 아니라, 저장, 수정, 삭제에도 항상 O(logN)의 시간 복잡도를 가진다.

 

b. Hash 테이블

 

Hash 테이블은 (Key, Value)로 데이터를 저장하는 자료구조 중 하나로 빠르게 데이터를 검색할 수 있는 자료구조이다. 해시 테이블이 빠른 검색 속도를 제공하는 이유는 내부적으로 배열(버킷)을 사용하여 데이터를 저장하기 때문이다.

해시 테이블은 각각의 Key값에 해시함수를 적용해 배열의 고유한 인덱스를 생성하고, 이 인덱스를 활용해 값을 저장하거나 검색하게 된다. 여기서 실제 값이 저장되는 장소를 버킷 또는 슬롯이라고 한다.

미리 저장된 메모리 공간에 한 번에 접근을 하기 때문에, 탐색 시간이 O(1)로 빠르지만, Hash Table에 저장되는 값들은 정렬되어 있지 않기 때문에 특정 값보다 크거나 작은 값을 찾을 수 없고, 해시가 등호(=) 연산에만 특화되었다.

따라서, SQL 쿼리문에서 특정 범위의 값을 조회하는 경우, 특정 값보다 크거나 작은 값을 찾을 수 없다.

5. B-Tree 인덱스를 통한 데이터 읽기

 

인덱스 레인지 스캔

• 검색해야 할 인덱스 범위가 결정됐을 때 사용하는 방식
• B-Tree의 인덱스 레코드를 타서 리프노드의 일부분만을 읽으며, 레코드에 접근할 때는 한 건 한 건 단위로 랜덤 I/O가 발생한다.

 

인덱스 풀 스캔

• 인덱스의 처음부터 끝까지 모두 읽는 방식
• 쿼리의 조건절에 사용된 칼럼이 인덱스의 첫 번째 칼럼이 아닌 경우 많이 사용
• 인덱스 레인지 스캔보다는 빠르지 않지만, 테이블 풀 스캔보다는 효율적

 

루스 인덱스 스캔

• 느슨하게 또는 듬성듬성 인덱스를 읽는 것, 여러 조건을 만족해야 사용 가능

 

6. 클러스터링 인덱스 / 넌 클러스터링 인덱스

 

클러스터링 인덱스

• 테이블당 1개씩만 허용
• 물리적으로 행을 재배열
• PK설정 시 그 칼럼은 자동으로 클러스터드 인덱스가 만들어진다.
• 인덱스 자체의 리프 페이지가 곧 데이터이다. 즉 테이블 자체가 인덱스이다.
• 데이터 입력, 수정, 삭제 시 항상 정렬 상태를 유지한다.
• 넌 클러스터 인덱스보다 검색 속도는 더 빠르다. 하지만 데이터의 입력, 수정, 삭제는 느리다.
• 30% 이내에서 사용해야 좋은 선택도를 가진다.

 

클러스터링 인덱스의 장점과 단점

• PK로 검색할 때 성능이 매우 빠르다.
• 테이블의 모든 보조 인덱스가 PK를 가지고 있기 때문에 인덱스만으로 처리될 수 있는 경우가 많다.
• 모든 보조 인덱스가 PK를 가지고 있어, 클러스터 키 값의 크기가 클 경우 전체적으로 인덱스의 크기가 커진다.
• 보조 인덱스를 통해 검색할 때 PK로 다시 한번 검색해야 하므로 처리 성능이 조금 느리다.
• INSERT 할 때 PK에 의해 레코드의 저장 위치가 결정되기 때문에 처리 성능이 느리다.
• PK를 변경할 때 레코드를 DELETE 하고 INSERT 하는 작업이 필요해 처리 성능이 느리다.

 

넌 클러스터링 인덱스

• 테이블당 약 240개의 인덱스를 만들 수 있다.
• 인덱스 페이지는 로그 파일에 저장된다.
• 레코드의 원본은 정렬되지 않고, 인덱스 페이지만 정렬된다.
• 인덱스 자체의 리프 페이지는 데이터가 아니라 데이터가 위치하는 포인터이기 때문에 클러스터링 인덱스보다 검색 속도는 더 느리지만 데이터의 입력, 수정, 삭제는 더 빠르다.
• 인덱스를 생성할 때 데이터 페이지는 그냥 둔 상태여서 별도의 인덱스를 따로 만들기 때문에 용량을 더 차지한다.
• 3% 이내에서 사용해야 좋은 선택도를 가진다.