Index 잘쓰는 방법
인덱스를 지정하는 기준 : 카디널리티와 선택률
인덱스는 테이블의 특정 필드를 지정합니다. 이 때 어떤 필드를 지정할 것인지의 기준은 필드의 카디널리티와 선택률입니다.
카디널리티란
카디널리티란 값의 균형을 나타내는 개념입니다. 고유성이라고 생각하면 편합니다. 카디 널리티가 가장 높은 필드는 PK 필드입니다. 반대로 모든 레코드에 같은 값이 들어가 있다면 카디널리티가 낮은 필드입니다.
선택률이란
선택률은 특정 필드값을 지정했을 때 테이블 전체 레코드 중 선택되는 레코드의 비율입니다. 예를 들어서 100개의 레코드를 가진 테이블에서 pk=1을 조건으로 지정한다면 단 한 개의 레코드가 선택됩니다. 때문에 선택률은 1/100=0.01, 1%입니다.
인덱스를 작성하는 필드 집합의 조건은 두 가지 지표로 판단합니다.
첫째, 카디널리티가 높을 것
값이 평균치에서 많이 흩어져 있고, 고유값이 많을 수록 좋은 인덱스 후보입니다.
둘째, 선택률이 낮을 것
한 번의 선택으로 레코드가 가능한한 적게 선택되는 것이 좋습니다. 최근 DMBS환경에서는 5~10%이하가 좋은 선택기준입니다. 5% 미만이라면 해당 필드 집합은 인덱스를 도입할 가치가 있습니다. 선택률이 10%보다 높다면 테이블 풀 스캔이 더 빠를 수도 있습니다. [1]
왜 선택률에 따라 테이블 풀 스캔이 빨라질 수 있을까?
정확하게는 선택되는 데이터의 개수에 따라 달라집니다. 먼저 풀 테이블 스캔과 인덱스 스캔의 작동 원리부터 알아보겠습니다.
인덱스 스캔은 각 레코드를 찾기 위해 인덱스 페이지를 읽고 그 다음 테이블 페이지를 읽습니다. 하지만 테이블 풀 스캔의 경우 디스크 I/O 작업이 순차적으로 이루어지기 때문에 많은 양의 데이터를 읽을 때, 더 효율적일 수 있습니다. 또한 한번에 큰 블록을 읽기 때문에 여러번 인덱스 테이블을 참조하는 것보다 빠를 수 있습니다.
즉, 찾아야하는 데이터의 수가 많아질수록 인덱스를 사용해 각각의 레코드를 찾는 것 보다 대다수의 레코드를 한번에 읽는 것이 빠를 수 있습니다.
셋째, 클러스터링 팩터가 낮을 것
클러스터링 팩터는 인덱스된 컬럼의 순서가 테이블의 물리적 데이터 저장 순서와 얼마나 일치하는지를 나타내는 값입니다. 클러스터링 팩터가 높을수록 데이터가 물리적으로 분산되어 있고, 낮을수록 물리적으로 뭉쳐있습니다.
클러스터링 팩터는 다음과 같이 계산합니다.
1. 인덱스 엔트리를 순차적으로 스캔합니다.
2. 각 인덱스 엔트리가 가리키는 테이블의 데이터 블록을 확인합니다.
3. 인덱스 엔트리가 순차적으로 가리키는 데이터 블록이 이전 엔트리의 데이터 블록과 다를 때마다,
클러스터링 팩터 값을 증가시킵니다.
즉, 클러스터링 팩터가 낮을수록 인덱스된 컬럼의 순서가 테이블의 물리적 저장 순서와 거의 일치합니다. 이는 인덱스를 사용할 때 디스크 I/O가 최소화되어 성능이 최적화됩니다.
클러스터링 팩터는 다음과 같이 확인합니다.
| RDBMS | 필드 |
|---|---|
| DB2 | SYSCAT.INDEXES 뷰의 CLUSTERRATIO 필드 또는 CLUSTERFACTOR 필드 |
| Oracle | DBA_INDEXES 뷰의 CLUSTERING_FACTOR 필드 |
클러스터링 팩터를 최적화하는 방법
인덱스 설계는 테이블 정의와 SQL만 봐서 할 수 있는 작업이 아닙니다. 특정 SQL에 적절한 인덱스를 생성하려면 검색 조건과 결합 조건을 바탕으로 데이터를 효율적으로 압축할 수 있는 조건을 찾아야합니다. 이를 위해서는 SQL 구문과 검색 키 필드의 카디널리티를 알아야합니다.
하지만 압축 조건을 찾지 못한다면 어떻게 해야할까요?
인덱스가 제대로 작동하려면 레코드를 크게 압축할 수 있는 검색조건이 필수입니다. 필드가 상태를 표시할 경우 카디널리티가 작아 인덱스로 만들기 적합하지 않을 수 있습니다.
SELECT order_id, recieve_date FROM Orders WHERE process_flg='5';
현재 테이블에서 process_flg의 분포는 다음과 같다고 가정합니다.
| process_flg | 분포 |
|---|---|
| 1(주문 단계) | 200만건 |
| 2(주문 완료) | 500만건 |
| 3(재고 확인 중) | 500만건 |
| 4(배송 준비 중) | 500만건 |
| 5(배송 완료) | 8,300만건 |
5로 선택하여 검색할 경우 선택률이 83%로 굉장히 높은 수치입니다. 이 상태에서 process_flg 필드에 인덱스를 생성하면 당연히 인덱스를 생성하는데 시간이 매우 오래걸립니다. 또한, 인덱스를 생성하여 사용한다 치더라도 풀 스캔을 할 때보다 느려질 가능성이 큽니다.
SELECT order_id
FROM Orders
WHERE receive_date
BETWEEN :start_date AND :end_date
검색 범위를 하루로 설정할지, 1년으로 설정할지에 따라 선택률이 매우 크게 달라집니다. 1년 내내 주문량이 균등할 경우 1일로 지정할 때의 365배의 레코드가 선택될 확률이 매우큽니다. 다른 쿼리도 하나 더 보겠습니다.
SELECT count(*)
FROM Orders
WHERE shop_id=:sid;
이 쿼리는 점포의 주문량에 따라 선택률이 급하게 변합니다. 소규모 점포에서 10만건이 선택되고 대규모 점포에서는 1000만건이 선택된다고 가정하겠습니다. 소규모 점포에서의 선택률은 0.01%, 대규모 점포에서의 선택률은 무려 10%입니다.
힌트를 사용해볼까?
SELECT /*+ INDEX(Orders shop_id_index) */ count(*)
FROM Orders
WHERE shop_id = :sid;
SELECT /*+ FULL(Orders) */ count(*)
FROM Orders
WHERE shop_id = :sid;
전자에 대해서는 인덱스를 사용하는 것이, 후자에 대해서는 인덱스를 사용하지 않는 것이 좋지만 이처럼 선택률에 따라 실행계획을 다르게 하는 것은 힘듭니다. 힌트를 사용해서 소규모 점포 쿼리에는 인덱스를 사용하게끔, 대규모 점포 쿼리에는 테이블 풀 스캔을 하도록 힌트를 줄 수 있지만 한계가 명확한 방법입니다.
또한 결합에 있어서도 Nested Loops 내부 테이블 결합 필드에 조건으로 히트되는 레코드가 많으면 반복되는 횟수가 늘어나므로 성능 문제가 발생합니다.
압축할 검색 조건이 있지만 인덱스를 쓰지 못하는 쿼리를 작성하는 케이스가 있습니다.
SELECT order_id
FROM Orders
WHERE shop_name LIKE '%대공원%'
중간일치 또는 후방 일치에는 인덱스를 사용할 수 없습니다. 설령 검색 조건의 선택률이 좋다고 하더라도 풀 테이블 스캔을 사용할 수 밖에 없습니다.
인덱스로 지정된 필드로 연산하는 경우에는 인덱스를 사용할 수 없습니다. 인덱스 테이블에 존재하는 값은 index_col1이지 index_col1 '*' 1.1 이 아니기 때문입니다.
SELECT `*`
FROM Practice
WHERE index_col1 `*` 1.1 >100;
하지만 다음과 같은 방식으로 인덱스 자체의 연산을 회피하는 방식으로 인덱스를 사용하게끔 개선할 수 있습니다.
WHERE col_1> 100/1.1
당연히 인덱스가 지정된 필드에 함수 또는 부정형를 사용하는 경우도 인덱스를 사용할 수 없습니다. 함수 색인하는 방법도 있지만, 쓸데 없는 연산 비용이 발생하기 때문에 기본적으로 사용하지 않는 편이 좋습니다.
SELECT `*`
FROM Practice
WHERE LENGTH(index_col1) = 10;
SELECT `*`
FROM Practice
WHERE index_col1 <> 10;
Richard J, Niemiec, Oracle Database 11g Release 2 Performance Tuning Tips & Techniques, Mcgraw-Hill Osborne Media, 2012↩︎