SQL 레벨업 Ch.10 — 인덱스 사용

“가장 일반적이면서 중요한 인덱스 방법은 B-Tree다. 모든 애플리케이션을 만족시킬 수 있는 최적의 메모리 구조란 존재하지 않지만, 그래도 하나를 선택해야 한다면 B-Tree를 선택할 것이라는 뜻이다.” — Christopher J. Date, 『데이터베이스 시스템 6판』

인덱스와 B-Tree

인덱스는 구조에 따라 B-Tree, 비트맵, 해시로 나뉜다.

B-Tree

• 데이터를 트리 구조로 저장하는 인덱스다.
• 범용성이 뛰어나 가장 많이 사용되며, 대부분의 DBMS에서 CREATE INDEX를 실행하면 기본으로 B-Tree 인덱스가 만들어진다.
• 검색 알고리즘으로서 성능이 가장 뛰어난 것은 아니지만 균형이 잘 잡혀 있다.

대부분의 데이터베이스는 리프 노드에만 키값을 저장하도록 B-Tree를 수정한 B+Tree를 채택한다. B+Tree의 검색 성능이 좋은 이유는 다음과 같다.

• 루트와 리프의 거리를 최대한 일정하게 유지해 검색 성능이 안정적이다.
• 트리의 깊이가 대개 3~4 수준으로 일정하다.
• 데이터가 정렬 상태를 유지하므로 이분 탐색으로 검색 비용을 크게 줄일 수 있다.
• 집약 함수 등에서 필요한 정렬을 생략할 수 있다.

비트맵 인덱스와 해시 인덱스

비트맵 인덱스

• 데이터를 비트 플래그로 변환해 저장한다.
• 카디널리티가 낮은 필드에서 효과를 발휘한다.
• 갱신 시 오버헤드가 커서 갱신이 빈번하지 않을 때 사용한다.

해시 인덱스

• 키를 해시 분산해 등가 검색을 빠르게 하려고 만든 인덱스다.
• 등가 검색 외에는 효과가 거의 없고 범위 검색을 할 수 없다. 거의 사용되지 않으며 지원하는 구현도 일부다.

인덱스를 활용하는 기준

카디널리티와 선택률

어떤 필드에 인덱스를 만들지 판단하는 기준이다.

• 카디널리티: 값의 분산도. 카디널리티가 높다는 것은 중복도가 낮다는 뜻이다. 모든 레코드의 값이 다른 유일 키 필드가 가장 높다.
• 선택률: 특정 필드값을 지정했을 때 테이블 전체에서 몇 개의 레코드가 선택되는지의 비율

인덱스를 만들 가치가 있는 조건은 두 가지다.

1. 카디널리티가 높을 것
2. 선택률이 낮을 것. 대체로 5~10% 이하가 기준이고, 5% 미만이면 인덱스를 만들 가치가 있다. 10%보다 높으면 테이블 풀 스캔이 더 빠를 가능성이 크다.

클러스터링 팩터

저장소에 같은 값이 물리적으로 얼마나 뭉쳐 있는지를 나타내는 지표다. 높을수록 분산되어 있고 낮을수록 뭉쳐 있다. 인덱스로 접근할 때는 특정 값에만 접근하는 경우가 많으므로, 클러스터링 팩터가 낮을수록 접근할 데이터양이 적어 유리하다.

인덱스로 성능 향상이 어려운 경우

인덱스 설계는 테이블 정의와 SQL만 보고 할 수 있는 작업이 아니다. 검색 조건과 결합 조건 중 레코드를 효율적으로 압축할 수 있는 조건을 찾아야 하므로, SQL 구문과 함께 검색 키 필드의 카디널리티를 알아야 한다.

압축 조건이 없는 경우

SELECT order_id, receive_date
  FROM Orders;

레코드를 압축하는 WHERE 구가 없으므로 인덱스를 만들 만한 필드도 없다. 실행 계획을 보지 않아도 테이블 풀 스캔이다.

레코드를 제대로 압축하지 못하는 경우

SELECT order_id, receive_date
  FROM Orders
 WHERE process_flg = '5';

process_flg의 분포가 다음과 같다고 하자.

process_flg	건수
1	200만 건
2	500만 건
3	500만 건
4	500만 건
5	8,300만 건

선택률이 83%로 매우 높다. 인덱스가 제대로 동작하려면 레코드를 크게 압축할 수 있는 검색 조건이 있어야 한다.

입력 매개변수에 따라 선택률이 변하는 경우

매개변수에 따라 선택률이 0.01%에서 10%까지 달라진다면 성능 향상을 기대하기 어렵다. 10%일 때는 풀 스캔, 0.01%일 때는 인덱스 스캔을 하면 좋겠지만 옵티마이저가 항상 그렇게 골라 주지는 못한다.

인덱스를 사용할 수 없는 검색 조건

압축할 수 있는 검색 조건이 있어도 인덱스가 사용되지 않는 형태가 있다.

중간 일치, 후방 일치의 LIKE

SELECT order_id
  FROM Orders
 WHERE shop_name LIKE '%대공원%';

선택률이 0.005%로 충분히 낮더라도 shop_name의 인덱스는 사용되지 않는다. LIKE에서 인덱스는 전방 일치('대공원%')에만 적용된다.

인덱스 필드로 연산하는 경우

SELECT *
  FROM SomeTable
 WHERE col_1 * 1.1 > 100;

인덱스 필드에 연산을 하면 인덱스를 사용할 수 없다. 식을 우변으로 옮기면 인덱스가 사용된다.

SELECT *
  FROM SomeTable
 WHERE col_1 > 100 / 1.1;

그 외

• IS NULL을 사용하는 경우
• 인덱스 필드에 함수를 적용하는 경우
• 부정형을 사용하는 경우

인덱스를 사용할 수 없을 때의 대처

UI 설계로 처리

점포 ID로 검색할 때는 반드시 주문일도 함께 입력하게 하는 식으로 입력에 제한을 두어, 레코드를 충분히 압축하는 조건이 항상 들어오게 한다.

데이터 마트

특정 쿼리에 필요한 데이터만 저장하는 작은 테이블로, 마트 또는 개요 테이블(Summary Table)이라고도 한다. 원본 테이블의 부분 집합이며, 접근 대상 테이블을 작게 만들어 I/O 양을 줄이는 것이 목적이다.

채택할 때는 세 가지를 주의한다.

• 데이터 신선도: 원본의 복사본이므로 동기화가 필요하다. 동기 사이클이 짧을수록 신선도는 높아지지만 성능 부담이 커진다. 신선도가 중요한 경우에는 데이터 마트를 쓸 수 없다.
• 크기: 원본에서 크기를 딱히 줄일 수 없다면 의미가 없다.
• 개수: 마트가 100개를 넘는 경우도 있다. 어떤 처리에 쓰이는지 파악되지 않아, 더는 쓰지 않는데도 동기화만 계속되는 ’좀비 마트’가 생기거나 관리가 불가능해질 수 있다.

인덱스 온리 스캔

접근 대상의 I/O를 줄인다는 목적은 데이터 마트와 같지만, 인덱스를 일반적인 용도와 다르게 사용하는 방법이다.

SELECT order_id, receive_date
  FROM Orders;

이 쿼리는 풀 스캔을 피할 수 없지만, 스캔 대상을 테이블이 아닌 인덱스로 바꿀 수는 있다.

CREATE INDEX CoveringIndex ON Orders (order_id, receive_date);

order_id와 receive_date는 검색 조건이 아니라 SELECT 구에만 나오는 필드라 보통은 인덱스 후보가 아니다. 하지만 쿼리에 필요한 필드를 모두 포함하는 인덱스가 있으면 테이블 없이 인덱스만 스캔하는 INDEX ONLY SCAN이 가능해진다. 이런 인덱스를 커버링 인덱스(Covering Index)라고 한다.

• 인덱스는 테이블 필드의 부분 집합만 저장하므로 원본 테이블보다 훨씬 작다.
• 데이터 마트는 애플리케이션도 수정해야 하지만, 인덱스는 그럴 필요가 없다.

로우 지향 저장소의 DBMS에서 컬럼 지향 저장소를 유사하게 구현하는 방법이라고 볼 수 있다.