DB에는 인덱스(Index)라는 개념이 있습니다. 주로 쓰는 비유로는 DB 테이블을 "책"이라고 했을 때, 인덱스는 "색인"이 됩니다. 알아보고 싶은 정보의 키워드를 이용해 색인을 검색하면 더욱 빠르게 정보를 찾아낼 수 있지요. 인덱스의 개념은 구루비 DB 스터디 - 인덱스의 선정(BY-NC-SA)에서 확인해 보세요. 이번 포스팅의 정보도 해당 게시글에서 부분 차용 되었습니다. 이번 포스팅은 인덱스 중에서도 결합 인덱스(Composite Index)에 대해 알아보려고 합니다. 결합 인덱스는 단일 인덱스로도 성능이
나오지 않게 되는 경우 만들어봄직 합니다. 분포도가 나쁜 컬럼들을 결합 할 때 분포도가 좋아지게 되면 결합 인덱스가 되는 경우도 있을겁니다. 그리고 결합 인덱스는 생성도 중요하지만 결합할 컬럼들간의 순서도 매우 많은 영향을 끼칩니다. 컬럼간의 우선 순위를 지정할 때 고려해야할 사항을 알아봅시다. 앞에 나오는 사항일수록 우선 순위가 높은 사항입니다. 가장 중요 할 수도 있는 사항인데 항상 사용하는가?에 대한 여부입니다. 사용하지도 않는 컬럼을 결합 인덱스로 만들어선 곤란하겠습니다. 가능하면 항상 사용할 것 같은 컬럼을 선택합니다. 또한
결합인덱스의 첫번째 컬럼을 사용하지 않으면 인덱스는 사용 되지 않습니다. SQL 쿼리 작성시에 '='로 지정할 수 있는 컬럼이 선행이 되어야 합니다. 예를 들자면 아래와 같은 SQL 쿼리가 있다고 가정합니다. 위와 같은 쿼리에서 col1, col2를 결합 인덱스로 만든다고 가정한다면 col2가 선행이 되어야 합니다. (어디까지나 예시입니다. ㅜㅜ) 결합 인덱스의 선행 컬럼이 '=' 비교를 하지 않는 경우 후행 컬럼이 '=' 비교일지라도 처리할 범위는 줄어 들지 않습니다. 따라서 위의 쿼리처럼 col1(Range 비교), col2(Point 비교) 순으로 잦은 비교를 한다고 가정시 결합 인덱스 생성을 col1, col2 순서로 하면 유리한 점이 없습니다. 분포도가
좋은(분포도 값이 낮은) 컬럼이 선행 되어야 합니다. 자주 정렬 되는 컬럼이라면 선행이 되어야 합니다. 말 그대로 부가적으로 추가할 컬럼이 있다면 추가합니다. 오묘한 DB의 세계입니다. 공부 더 열심히 해야겠어요.
4.1. 인덱스의 선정 기준<새로운 인덱스 생성의 의미>
[VLDB: 인덱스 선정을 통한 엑세스 형태의 효율화를 위한 조언 ]
4.1.1. 테이블 형태별 적용기준- 적은 데이터를 가진 소형 테이블 가) 적은 데이터를 가진 소형 테이블 - 한번의 다중 블록 I/O Call로 모든 내용을 읽을 수 있으므로, 인덱스를 통한 스캔과 거의 차이가 없음. 그럼에도 불구하고 생성해야 하는가? - 권고 나) 주로 참조되는 역할을 하는 중대형 테이블 다) 업무의 구체적인 행위를 관리하는 중대형 테이블 라) 저장용 대형 테이블 - 권고 4.1.2. 분포도와 손익 분기점- 인덱스 생성의 목적: 전체 집합에서 범위를 줄여 선별된 부분만 엑세스 하고자 함 - 인덱스의 분포도가 높으면 무조건 인덱스를 생성하지 않는 것이 유리한가? 4.1.3. 인덱스 머지와 결합 인덱스 비교- 인덱스 머지(Index Merge): 여러 인덱스가 협력하여 같이 엑세스를 주관 - 결합 인덱스: 여러 컬럼을 모아 하나의 인덱스로 만드는 방식. - Index Merge 방식과 결합 인덱스 방식의 연산 차이 4.1.4. 결합 인덱스의 특징< 결합 인덱스의 특징 >
가) 분포도와 결합 순서의 상관 관계 ex) 구성
- 엑세스 방식에 따른 구분
* 분포도가 좋지 않은 COL1 컬럼을 복합인덱스의 선행 컬럼으로 설정한 경우 처리 과정(왼쪽 그림) 1. col1='A' AND col2=112 인 데이터를 B-Tree 방식으로 바로 찾음 2. ROWID를 이용하여 테이블 로우 엑세스 3. 다음 로우를 탐색하여 조건에 만족하면 다시 테이블의 로우를 엑세스하고 그렇지 않으면 스캔 종료(이 경우, 한 번의 랜덤, 2개의 인덱스 로우 스캔) * 분포도가 좋은 COL2 컬럼을 복합인덱스의 선행 컬럼으로 설정한 경우 처리 과정(오른쪽 그림) 1. col2=112 AND col1='A' 인 데이터를 B-Tree 방식으로 바로 찾음 2. ROWID를 이용하여 테이블 로우 엑세스 3. 다음 로우를 탐색하여 조건에 만족하면 다시 테이블의 로우를 엑세스하고 그렇지 않으면 스캔 종료(이 경우, 한 번의 랜덤, 2개의 인덱스 로우 스캔) * 결론: 인덱스의 모든 컬럼을 Equal 연산으로 처리한 경우 처리량에는 차이가 없음. 단지 Equal 연산만 사용했으므로 필요한 데이터만 읽을 수 있게 되었기 때문. 나) 이퀄(=)이 결합순서에 미치는 영향 - 실제 실무에서는 Equal 연산만으로 처리되지 않고, 다양한 범위 처리 연산(LIKE, BETWEEN, < > 등)이 사용됨 ex) 구성
- 엑세스 방식에 따른 구분
* 분포도가 좋지 않은 COL1 컬럼을 복합인덱스의 선행 컬럼으로 설정한 경우 처리 과정(왼쪽 그림) * 분포도가 좋은 COL2 컬럼을 복합인덱스의 선행 컬럼으로 설정한 경우 처리 과정(오른쪽 그림) - 결론: 인덱스의 처리 범위를 고려해야 하며 컬럼의 분포도를 고려한 인덱스 컬럼 선정 방식은 맞지 않다. 다) IN 연산자를 이용한 징검다리 효과 - 적용 예 - 사용 예제 * 기존의 between 연산을 이용한 경우(왼쪽 그림) * IN 연산을 이용한 경우(오른쪽 그림) * 위 IN 리스트를 사용한 쿼리는 다음의 OR를 사용한 쿼리와 거의 동일한 수행방식을 가진다.
라) 처리 범위에 직접적인 영향을 주지 못하는 컬럼의 추가 기준 - 결합 인덱스를 사용하는 쿼리 내부에 equal 연산을 사용하지 않는 컬럼 이후로는 범위를 줄여주지 못함.
- 사용 예제
* (A+B) 컬럼에 복합인덱스를 만든 경우(왼쪽 그림) * (A+B+C) 컬럼에 복합인덱스를 만든 경우(오른쪽 그림) 4.1.5. 결합 인덱스의 컬럼 순서 결정 기준< 컬럼 순서를 결정하는 우선 순위 >
[VLDB: 상황 가정 ] : C1, C2, C3, C4 컬럼들에 대해 복합인덱스 컬럼 순서를 어떻게 결정할 것인가? ▶ 1단계: 항상 사용하는가? ▶ 2단계: 항상 '='로 사용하는가? ▶ 3단계: 어느 것이 더 좋은 분포도를 가지는가? ▶ 4단계: 자주 정렬되는 순서는 무엇인가? ▶ 5단계: 부가적으로 추가시킬 컬럼은 어떤 것으로 할 것인가? 4.1.6. 인덱스 선정 절차가) 테이블의 엑세스 형태를 최대한 수집 ▶ 개발 단계에서의 엑세스 형태 수집 ▶ 운영 단계에서의 엑세스 형태 수집 ▶ 수집된 SQL을 테이블 별로 출력하여 엑세스 형태 기록 나) 인덱스 대상 컬럼의 선정 및 분포도 조사 1) 인덱스 대상 컬럼 선정 및 분포도 분석 - 인덱스 대상 컬럼 기준
2) 현행 인덱스 존재 시, 조사한 엑세스 형태가 어떤 인덱스를 타는지 조사 다) 특수한 엑세스 형태에 대한 인덱스 선정 라) 클러스터링 검토 마) 결합 인덱스 구성 및 순서의 결정 바) 시험 생성 및 테스트 사) 수정이 필요한 애플리케이션 조사 및 선정 문서에 대하여
문서정보
|