[데이터베이스] 인덱스의 선택

1. Workload

인덱스는 양날의 검이다.

탐색 속도를 향상시켜주는 인덱스의 장점이 있지만 잘못된 인덱스의 선택은 오히려 작업량을 늘릴 수 있기 때문에 인덱스의 선택은 상황을 판단하여 신중히 행하여야 한다. 또한 DBMS마다 지원하는 index방식이 다르기 때문에 각각의 DBMS에서 사용하는 방식에 대한 이해가 필수적이다. 

예를 들면 MySQL의 경우 테이블의 기본 키(Primary key)로 자동으로 클러스터화된 인덱스를 생성한다. 

따라서 언클러스터화된 인덱스는 MySQL DBMS에서는 사용이 불가능하다. 

또한 인덱스는 테이블의 갱신 속도를 늦추기 때문에 아무렇게 인덱스를 짜면 안된다. 왜냐하면 인덱스가 정의된 데이터 테이블들은 인덱스에 따라 물리적 또는 논리적으로 정렬을 하기 때문에 삽입, 삭제, 변경이 발생하면 다시 재정렬을 하여야하므로 잦은 입출력이 있는 은행이나 게시물 등에 아무렇게 인덱스를 사용하면 성능을 저하시킬 수 있다. 

2. Clusetered Index

탐색키가 하나인 경우

Clustered index는 테이블에 단 하나만 생성할 수 있으므로 어떤 탐색키를 사용할 지 선택을 신중하게 하여야 한다. 각각의 쿼리와 조건에 따라서 index를 사용한 탐색을 할지 테이블 전체를 스캔할지 선택하여한다.

select E.dno from Emp E where E.age > 40

위의 쿼리에서 E.age를 index로 사용할지 선택하라면 Emp 테이블의 40세 이상의 비율이 어느 정도 인지 확인이 필요하다. 40세 이상의 인원이 적다면 인덱스를 통한 탐색을 매우 유용할수 있다. 하지만 40세 이상의 비율이 대다수이라면 굳이 인덱스를 통한 탐색이 아니라 테이블 전체를 스캔하는 것이 더욱 효율적이다. 왜냐하면 클러스터화된 인덱스의 경우는 모든 데이터 레코드들이 물리적으로 탐색키에 맞게 정렬이 되어 있다. 

# dno는 부서ID
select E.dno, count(*) From Emp E where E.age>10 group by E.dno

위의 쿼리는 각각의 부서별로 사원의 나이가 10세 초과인 사람의 수를 구하는 쿼리다. dno와 age중 어떤 것을 index로 고른다고 한다면 10세 초과의 사원의 수가 전체 데이터에 어느 정도를 차지하는지 알아야할 필요가 있다. 만일 10세이상의 사원이 첫 번째 사례처럼 대다수라면 인덱스로 사용하는 것은 부적절하다. 오히려 dno를 인덱스로 사용한다면 더욱 효율성이 있을 것이다. 데이터들은 dno에 따라 정렬이 되어 있으므로 인덱스를 탐색한 후에 조건에 맞는 것만 세면 되기 때문이다.

두 개 이상의 탐색키 활용

직원의 나이를 나타나는 age와 봉급을 나타나는 sal을 탐색 키로 사용하는 클러스터화된 인덱스가 있다고 하자. 어떤 것을 우선적으로 정렬하는 가에 따라 <age, sal> , <sal, age> 둘 중에 하나를 사용할 수 있다.

1. 만일 age = 30 이고 sal = 4000인 사람을 찾아라 

 <age, sal> 이나 <sal, age> 둘 다 효과적이다. 만일 age 를 단독으로 인덱스로 사용한다면 age가 30인 사람은 금방 찾지만 sal이 4000인 사람은  해당 age 전체를 탐색하여야 한다. 하지만 인덱스로 <age, sal>을 찾는 경우는 모든 데이터 레코드들이 age로 정렬된 후 sal로 정렬하기 때문에 4000인 사람을 쉽게 찾을 수 있다.

2.  age = 30 이고 3000<sal<5000인 직원을 찾아라

<age, sal>이 <sal, age>보다 더욱 효과적이다. age=30를 찾은 다음에 그 후 sal로 정렬된 데이터 레코드를 수평으로 이동하면서 범위 탐색을 할 수 있어서 <age, sal> 가 더 효율적이다.

따라서 <age, sal>, <sal, age>중 선택하는 방법은 둘 중에 어떤 것이 작은 범주에 들어가는냐는 것을 생각을 하고 선택하는 것이 옳다. 

3. Unclustered Index

index-only plan

Unclustered Index의 경우 데이터 엔트리상에 탐색 키로 논리적으로 정렬이 되어있고 데이터 레코드들이 물리적으로 정렬하지 않는다. 따라서 데이터 엔트리에서 데이터 레코드로 직접 탐색하기 전에 데이터 엔트리내에서 찾아서 식별하는 것이 답을 찾아낼 수 있는 적절한 인덱스의 선택이 필요하다.  나머지 인덱스의 사용에 고려할 사항은 위의 클러스터화된 인덱스와 유사하여 생략한다.