DB를 설계할 때 인덱스를 설정해야하는 상황이 있었습니다.
그래서 저는 2개의 컬럼을 각자 인덱스(Index)를 시켰습니다. 하지만, 2개의 조건을 같이 검색을 할 때 각자 인덱스(Index)를 지정해주는 것보다 결합인덱스로 지정해주는것이 더 효과적이라는 것을 알게 되어 단일인덱스를 복합인덱스로 바꾸어 설계하였습니다.우선, 복합인덱스에 대해 들어가기전에 인덱스(Index)에 대해 개념을 알아보겠습니다.
1. 인덱스(Index)란?
인덱스(Index)는 테이블에 대한 검색 속도를 향상시켜주는 자료구조 입니다.
예시로 책에 있는 목차나 색인이라고 생각하시면 됩니다. 만약 책에서 원하는 내용을 찾고자 한다면, 책의 모든 페이지를 찾는 것은 시간이 오래걸립니다. 이처럼 인덱스(Index)는 원하는 내용을 빨리 찾을 수 있게해주는 자료구조입니다.
출처: https://mangkyu.tistory.com/96
2. 인덱스(Index) 장/단점
- 장점
- 테이블을 조회하는 속도와 그에 따른 성능을 향상시킬 수 있다.
- 전반적인 시스템의 부하를 줄일 수 있다.
- 단점
- 인덱스(Index)를 관리하기 위해 DB의 약 10%에 해당하는 저장공간이 필요하다.
- 인덱스를 관리하기 위해 추가 작업이 필요하다.
- 인덱스를 잘못 사용할 경우 오히려 성능이 저하되는 역효과가 발생할 수 있다.
만약 CREATE, DELETE, UPDATE가 자주 일어나는 컬럼에 인덱스를 걸게되면 오히려 성능이 저하됩니다.
위와 같은 CREATE, DELETE, UPDATE는 기존의 인덱스를 삭제하지 않고 사용하지 않음으로 남겨두기 때문에 작업을 할 때 실제 데이터에 비해 인덱스(Index)가 과도하게 커지는 문제점이 발생할 수 있습니다.
3. 인덱스(Index)를 사용하면 좋은 경우
- 규모가 큰 테이블
- INSERT, UPDATE, DELETE 작업이 자주 발생하지 않는 컬럼
- WHERE, ORDER BY, JOIN 등 자주 사용되는 컬럼
- 데이터 중복도가 낮은 컬럼
4. 인덱스(Index)의 자료구조
1. Hash Table
해시 테이블은 (Key, Value)의 구조로 데이터를 저장하는 자료구조입니다. 시간 복잡도가 O(1)로 매우 빠른 데이터를 탐색 할 수 있습니다.
탐색은 빠르지만 실제로 인덱스로 잘 사용되지는 않습니다. 그 이유는 해시 테이블은 1이라도 달라지면 완전히 다른 해시 값을 생성합니다. 다른 말로는 등호(=) 연산에 최적화 되어있습니다. 하지만 DB 탐색할 때 범위 탐색(부등호(<,>)를 사용하는 탐색)을 자주 사용하기 때문에 정렬되어 있지 않은 해시테이블은 잘 맞지 않습니다.
2. B+Tree
B+Tree는 DB의 인덱스(Index)를 위해 자식 노드가 2개 이상인 B-Tree를 개선시킨 자료구조입니다.
B+Tree 특징
- 리프노드(데이터노드)만 인덱스(Index)와 함께 데이터를 가지고 있고, 나머지 노드(인덱스노드)들은 데이터를 위한 인덱스 key만을 가지고 있습니다.
- 리프노드들은 LinkedList로 연결되어 있습니다.
- 데이터노드 크기는 인덱스노드의 크기와 같지 않아도 됩니다.
위에서 말했듯이 DB 탐색할 때 부등호를 이용한 순차 검색이 자주 발생합니다. 이에 맞게 BTree의 리프노드들을 LinkedList로 연결시키므로써 보완하여 B+Tree를 만들어 인덱스에 맞게 최적화되었습니다.
- B+Tree는 무조건 리프노드까지 탐색해야한다는 단점이 있고, O(log2n)의 시간복잡도를 갖습니다.
지금까지 인덱스(Index)에 대해서 알아봤습니다. 이제 본 문제였던 결합인덱스에 대해서 알아보겠습니다.
1. 복합 인덱스란?
2개 이상의 컬럼을 합쳐서 인덱스를 만드는 것입니다. 하나의 컬럼으로보다 여러개의 컬럼을 합쳤을 때 효율이 좋은 경우 사용하며, WHERE절에서 OR조건이 아니라 AND 조건일 때 사용하는 것이 좋습니다.
2. 복합 인덱스 컬럼 선택
- WHERE절에서 AND 조건으로 결합되어 사용되면서 각각 사용할 때보다 여러개 사용했을 때 더 효율적인 컬럼들
- JOIN의 연결고리로 자주 사용되는 컬럼
- ORDER BY에 자주 사용되는 컬럼
- 단일 키 컬럼 조건이 아닌 한개 이상 키 컬럼조건으로 자주 조회되는 컬럼
3. 복합 인덱스의 컬럼 순서 결정
결합 인덱스에서 순서가 중요합니다. 순서에 따라 성능이 좋아질수도 오히려 나빠질 수도 있습니다. 비슷하게 저도 결합 인덱스를 설정했는데 인덱스가 안되는 경우가 있었습니다.
예를 들어, 결합 인덱스로 (A,B,C)를 걸었을 때 순차적으로 A 필터링, B 필터링, C 필터링을 받아야 좋은 결합인덱스라 할 수 있는데 저는 A를 무시한체 B와 C에 대해서만 검색을 했었습니다. 이럴 때 인덱스가 적용되지 않았습니다. 결합인덱스가 적용되려면 설정했던 선행 컬럼부터 사용해야합니다.
그래서 결합 인덱스를 설정할 때 순서가 중요한다는 것을 알게되었습니다. 그래서 아래와 같은 고려하여 우선순위를 설정하셔야합니다.
- WHERE절 조건에 많이 사용되는 컬럼
- "="로 사용되는 컬럼
- 분포도가 좋은 컬럼
- 자주 이용되는 순서
*인덱스(Index) 생성 방법
단일 인덱스 생성방법:
create index "인덱스명" on "테이블명"(컬럼)
복합 인덱스 생성방법:
create index "인덱스명" on "테이블명"(컬럼1, 컬럼2, 컬럼3,...)위와 같이 복합 인덱스를 생성하려면 단일 인덱스 생성방법에서 컬럼만 추가해주시면 됩니다.
참고
https://mangkyu.tistory.com/96
https://rebro.kr/167
https://coding-factory.tistory.com/755
맞지 않은 내용이 있으면 말씀해주세요. 수정하겠습니다 ^&^
