짧) 인덱스 특성을 통한 DB 성능 향상

 

인덱스는  B트리형 구조를 가지고 있다는 것을 다들 아실 겁니다. 하지만 우리가 내부 알고리즘까지 알 필요는 없지만 그 구조를 기반으로 데이터베이스를 사용하기 때문에 특징 정도는 알자

 

테이블에 발생되는 트랜잭션의 조회 패턴에 따라 PK/FK 칼럼의 순서를 고려하자.

 

PK가 여러 개의 속성으로 구성된 복합 식별자일 경우에는 인덱스의 PK순서를 반드시 고려해야 한다.

 

예를 들어 다음 복합키로 구성된 엔티티가 있다.

 

주문 엔티티

주문ID	주문날짜	배달FK
1	20210423	1
2	20210424	2

인덱스는 다음과 같이 구성했다.

CREATE UNIQUE INDEX ORDER_INDEX ON 주문
(
주문ID ASC,
주문날짜 ASC
);

 

만약 데이터가 1억 건 정도 있다고 가정했을 때 

 

다음과 같이 조회한다.

 

SELECT * FROM 주문 WHERE 주문 날짜 = 20210423;

이경우는 인덱스 효과를 보기 어렵다 맨 앞의 칼럼이 제외된 상태이기 때문에 데이터를 비교하는 범위 자체가 넓어져 성능 저하를 유발한다. 옵티마이저는 어차피 인덱스 활용 못하니까 대상 테이블 가서 풀스 캔을 떠버린다.

 

 

다음과 같은 경우도 비슷하다.

SELECT * FROM 주문 WHERE 주문 날짜 = 20210423 AND 주문 ID <100;

이경우는 인덱스를 이용하지만 최적화된 인덱스를 사용하지 않은 경우이다. 범위 자체가 달라진다.

 

따라서 다음과 같이 인덱스를 구성했다면 다음과 같은 SQL문을 기대한다.

SELECT * FROM 주문 WHERE 주문ID = 10;

보통  PK제약조건을 걸면 자동으로 PK인덱스가 생성되는 이유다. 자주 조건절에 사용될 것이라는 믿음 때문이 이다.

 

 

 

즉 인덱스를 복합키로 생성한 경우 앞쪽에 위치한 칼럼 인덱스가 비 교자(=)로 있어야 좋은 효율을 나타낼 수 있다.

(=) 아니라면 최소한 범위 (BETWEEN, < , >)을 사용해야 한다. 아니면 FULL SCAN의 지옥

 

물리적 테이블에 FK제약을 걸었을 때는 기본정책으로 FK인덱스를 생성하고 거의 사용되지 않을 때 FK인덱스를 삭제하자. 비 교자로 WHERE절에 걸리지 않았다 해도 JOIN문에서 자주 사용되기 때문에 FK인덱스는 성능적으로 이점을 얻을 수 있는 부분이다.

 

이건 보통 1:N관계에서 조인을 걸면  N 쪽 테이블을 스캔한다. 이경우 FK인덱스가 생성되어있고 비교를 해당 FK로 한다면 풀스 캔이 아닌 인덱스를 활용해 좋은 성능으로 조회를 할 수 있다.