인덱스 정리 (종류, 고려해야 할 부분, 사용할 수 없는 경우, 대처)

RDB에서 사용하는 인데스는 구조에 따라 세 가지로 분류할 수 있다. 

- B-tree 인덱스

- 비트맵 인덱스 : 비트 플래그로 변환해서 저장하는 형태, 카디널리티가 낮은 필드에 대해 효과적 

- 해시 인덱스 : 키를 해시 분산해서 등가 검색을 고속으로 실행하고자 만들어진 인덱스 (거의 사용 X) 

 

만능형 : B-tree 

- 데이터를 트리 구조로 저장하는 형태의 인덱스이다.
- 균형잡힌 뛰어난 범용성의 인덱스로 가장 많이 사용된다. 
- 특별히 앞에 이름을 붙이지 않으면 B-tree 인덱스를 지칭하는 것 = default

사실은 대부분의 데이터베이스는 리프 노드에만 키값을 저장하는 B+tree, B-tree의 수정 버전을 사용한다. (오라클, PostgreSQL, MySQL 등), 이는 보다 검생을 효율적으로 만든 알고리즘으로 DB 외에 파일 시스템에서도 사용된다. 

 

B+tree특징 
- 루트와 리프의 거리를 가능한 일정하게 유지 
- 검색 성능이 안정적 
- 트리의 깊이도 대개 3~4 정도의 수준으로 일정 
- 데이터가 정렬 상태를 유지 (사실 RDB는 데이터의 순서가 없어야하지만, 대부분 그렇지 못하다. ) -> 이분 검색을 통해 검색 비용 절감 가능 

 

인덱스를 잘 활용하려면 몇 가지 포인트를 고려해야한다. 

1. 카디널리티와 선택률 

카디널리디란?? 값의 균형을 나타내는 개념 

중복도가 ‘낮으면’ 카디널리티가 ‘높다’고 표현한다.

중복도가 ‘높으면’ 카디널리티가 ‘낮다’고 표현한다.

즉 카디널리티는 전체 행에 대한 특정 컬럼의 중복 수치를 나타내는 지표이다.
=> 서로 다른 값만 들어가는 PK 필드가 가장 카디널리티가 높은 필드라고 볼 수 있다. 

 

선택률은 특정 필드값을 지정했을 때 테이블 전체에서 몇 개의 레코드가 선택 되는지를 나타내는 개념이다. 

 

인덱스는 테이블의 특정 필드 집합에 대해 만든다. 이때 어떤 필드에 대해 인덱스를 작성할 것인지 기준이 되는 요소가 필드의 카디널리티와 선택률이다. 

Tip. 클러스터링 팩터 

인덱스 성능을 결정하는 요인으로 클러스터링 팩터라는 개념도 있다. 이는 저장소에 같은 값이 어느 정도 물리적으로 뭉쳐 존재하는지를 나타내는 지표로, 높은수록 분산되어 있고 낮을수록 뭉쳐있다는 뜻이다. 
인덱스로 접근할 때는 특정 값에만 접근하는 경우가 많으므로 보통 클러스터링 팩터가 낮을수록 접근할 데이터양이 적어져 좋다.

 

클러스터링이 낮다

1	1	2	2	3	3
1	1	2	2	3	3

 

클러스터링이 높다 

1	1	2	3	3	2
2	3	1	2	1	3

 

2. 인덱스를 사용하는 것이 좋은지 판단하려면 

1. 카디널리티가 높을 것, 즉 값의 평균치에서 많이 흩어져 있을수록 좋은 인덱스 후보 

2. 선택률이 낮을 것, 즉 한 번의 선택으로 레코드가 조금만 선택되는 것이 좋은 후보 (5~10 프로 이하가 좋다. 10프로 이상이면 풀 스캔하는 편이 더 빠를 수 있다.) 

 

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

Data의 규모가 커질수록 DB의 성능 확보는 어려워진다. 이때 중요한 것이 인덱스 설계이다. 

 

하지만 좋지 않은 인덱스 설계를 살펴보자 

1. 압축 조건이 존재하지 않음 

SQL 구문에 데이터를 압축할 수 있는 조건이 없을 경우. -> WHERE 가 없는 경우 

 

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

압축 조건이 있기는 하지만 레코드를 제대로 압축하지 못하는 SQL 구문

FLAG

200만건

500만건

500만건

500만건

8000만건

이라고 했을 때 FLAG 5를 선택하면 선택률이 절반 이상이나 된다. 이것을 인덱스로 만든다면 풀 스캔을 할 때보다 가능성이 크다. 

인덱스가 제대로 작동하려면 어디까지나 '레코드를 크게 압축할 수 있는 검색 조건'이 있어야 한다. 

 

▶입력 매개변수에 따라 선택률이 변동하는 경우 (사용자가 입력하는 매개변수에 따라 선택률이 변동)

- 인덱스 사용이 오히려 성능 악화를 일으키게 될 수도 있다. 

 

3. 인덱스를 사용하지 않는 검색 조건 

압축할 조건이 있으면서도 인덱스를 사용할 수 없는 타입의 경우

 

▶ 중간 일치, 후방 일치의 LIKE 연산자 -> 풀스캔하므로 조심하자. 
LIKE 연산자에서 인덱스는 전방 일치에만 적용할 수 있다. 

 

▶ 색인 필드로 연산하는 경우 

색인 필드로 연산하는 경우엔 인덱스를 사용할 수 없다. 

하지만 검색 조건의 우변에 식을 사용할 때는 인덱스가 사용된다. 

WHERE col_1 * 1.1 > 100;    -> 이 경우 인덱스로 사용 불가
WHERE col_1 100/1.1;         -> 인덱스로 사용 가능 

 

▶ IS NULL을 사용하는 경우에도 인덱스를 사용할 수 없다. 
일반적으로 색인 필드의 데이터에 NULL이 존재하지 않기 때문이다. (RDB에서는 NULL을 사용하지 않는 것이 좋다.)

 

▶색인 필드에 함수를 사용하는 경우에 인덱스가 사용되지 않는다. 

'색인 필드에 연산을 하는 경우'와 같은 이유로 적용되지 않는다. 인덱스 내부에 존재하는 값은 col_1이지 함수(col_1)이 아니기 때문이다.

 

▶부정형을 사용하는 경우 인덱스를 사용할 수 없다. 

 

인덱스를 사용할 수 없는 경우 대처법 

1. 애플리케이션에서의 설정으로 처리하는 방법

2. 인덱스 온리 스캔 

 

1. 외부 설정으로 처리 

▶ UI 설계로 처리 

처음부터 인덱스 사용할 수 없는 경우를 제한을 하는 것이 가장 간단한 해결방법이다. 즉 데이터를 입력할 때 입력 조건을 넣어주는 방법이다. 예를 들면 책을 주문할 때 책ID를 입력하면 주문일도 함께 입력해야 검색 버튼을 누를 수 있게 하는 입력 제한을 두면 인덱스를 잘 사용할 가능성이 커진다. 

 

이러한 방법은 사용자 입력에 대해 실무 요건을 고려해야 한다. 즉 사용자와 엔지니어가 함께 생각해 작성해야한다. 

 

2. 외부설정을 사용한 대처 방법의 주의 점 

성능과 사용성의 트레이드오프를 통해 타협점을 찾는 것이 DB 엔지니어가 해야하는 일이다. 

DBA와 FE, BE 엔지니어는 완전 분업을 하기 때문에 커뮤니케이션에 단절이 일어나기 쉽다. 하지만 전체적으로 조감해주지 않는다면 성능을 최적화하기 어렵다. 
-> 프로젝트를 시작하는 단계에서 개발자 간의 소통과 개발자와 사용자와 합의가 중요하다. 

 

3. 데이터 마트로 대처 

외부 설정에 영향을 받지 않는 방법 중 하나가 데이터 마트이다. (개요 테이블) 
데이터 마트는 특정한 쿼리에서 필요한 데이터만을 저장하는 상대적으로 작은 크기의 테이블을 의미한다. (테이블의 부분 집합 or 서브셋)

접근 대상 테이블의 크기를 작게 해서 I/O 양을 줄이는 것이 데이터 마트의 목적이다. 

 

-- 데이터 마트
CREATE TABLE OrderMART 
(order_id CHAR(4) NOT NULL,
receive_date DATE NOT NULL);

-- 압축 조건이 존재하지 않는 경우에도 성능 보장 
SELECT order_id, receive_date FROM OrderMart;

특징

- 원본 테이블의 부분적인 복사본 
- 테이블의 크기를 작게 해 I/O양을 줄임 

 

4. 데이터 마트의 주의점

1) 데이터 신선도 
- 데이터 동기 시점의 문제 : 데이터 마트는 원래 테이블의 부분 복사본이기 때문에 특정한 시점마다 원본 테이블에서 데이터를 동기화 해야한다. 이 동기 사이클이 짧으면 신선도는 높지만 성능적으로 문제가 생길 수 있다. 데이터의 신선도가 중요한 경우라면 이러한 방법은 채택하기 어렵다. 

 

2) 데이터 마트 크기

모든 필드를 검색해야 하는 경우나, 검색 조건의 선택률이 높아 레코드를 제대로 압축하지 못하는 경우 성능적인 개선이 불가능하다. 
-> GROUP BY절을 미리 사용해서 집계를 마치고 데이터 마트를 만들면, 필드 수와 레코드 수를 크게 줄일 수 있으며 GROUP BY에 필요한 정렬 또는 해시 처리도 사전에 끝낼 수 있으므로 굉장히 효과적이다. 

 

3) 데이터 마트 수 
너무 많아지면 관리가 불가능해지는 경우가 생긴다. 데이터 마트는 기능 요건에 의해 만들어지는 엔티티가 아니다. 따라서 ER에도 등장하지 않으므로 제대로 관리하기 어렵다. 
수가 많아 질 경우
1. 저장소 용량을 압박 
2. 백업, 스냅샷을 할 때의 시간이 오래 걸림
그러므로 지나치게 의존하지 말자.

 

4) 배치 윈도우
데이터 마트를 만드는 데도 시간이 걸리므로 배치 윈도우를 압박한다. 데이터 신선도를 위해 갱신을 해야하는데 이때 여유있게 처리하기 위해 배치 윈도우와 Job Net도 고려해야한다. 

 

5.인덱스 온리 스캔으로 대처 

인덱스 온리 스캔은 SQL 구문이 접근하려는 대상의 I/O 감소를 목적으로 한다, 또 데이터 마트에서 문제가 되는 데이터 동기 문제를 해결할 수 있다. 
인덱스 온리 스캔은 인덱스를 사용한 고속화 방법이다. 

SELECT order_id, receive_date FROM OrderMart;

CREATE INDEX Covering_Index ON Orders (order_od, receive_date)

order_od, receive_date라는 2개의 필드는 SELECT 구문에 포함되어 있어 일반적으로 인덱스 필드 후보로 되지 않는다. 그런데 이들 2개의 필드를 커버하는 인덱스가 존재하면 테이블이 아닌 인덱스만을 스캔 대상으로 하는 검색을 사용할 수 있게 된다. 이러한 인덱스는 커버링 인덱스라고 부른다. 
인덱스 온리 스캔은 SQL 구문에서 필요한 필드를 인덱스만으로 커버할 수 있는 경우에 테이블 접근을 생략하는 기술이다. 

장점 

- I/O 비용을 줄일 수 있다. 
- 인덱스는 테이블 필드의 부분 집합만 저장하므로 기존의 테이블에 비해 굉장히 작다

=> 로우(레코드)

지향 저장소의 DBMS에 유사적으로 컬럼(필드) 기반 저장소를 실현하는 것으로 봐도 좋다. 

 

이러한 기술을 특정한 상황에서 검색 성능을 극단적으로 높일 수 있는 기능이다. 이는 컬럼 지향 데이터베이스를 로우 지향 데이터 베이스에서 의사적으로 구현한 방법이라고 할 수 있다. 

6. 인덱스 온리 스캔의 주의사항 

1) DBMS에 따라 사용할 수 없는 경우도 있다. -> 오래된 버전을 사용 중이라면 지원을 안할 수도 있다. 왠만하면 지원 

 

2) 한 개의 인덱스에 포함할 수 있는 필드 수에 제한이 있다. -> 인덱스의 크기는 무제한이 아니다. 이러한 제한은 구현 환경마다 다르므로 매뉴얼을 확인하자. 

 

3) 갱신 오버 헤드가 커진다. -> 커버링 인덱스는 성질상 필연적으로 필드 수가 많아 크기가 큰 인덱스가 되기 쉽다. 즉 검색을 고속으로 만들 수 있는 대신, 갱신 성능이 떨어지는 트레이드 오프가 발생한다. 

 

4) 정기적인 인덱스 리빌드가 필요 -> 검색 성능 자체가 인덱스의 크기에 의존한다는 것이다. 그렇기 때문에 커버링 인덱스의 정기적인 크기 모니터링과 리빌드를 운용에 포함시켜야 한다. 

 

5) SQL 구문에 새로운 필드가 추가된다면 사용할 수 없다. -> 유지 보수 작업에서 쿼리에 새로운 필드가 추가될 수 있다. 그렇게 되면 인덱스 온래 스캔을 사용할 수 없다. 즉 유지보수에 약한 타입의 튜닝이다. 

 

결론 
- B+tree 인덱스는 편리하지만, 카티널리티와 선택률에 따라 성능이 결정 

- 선택률을 제어하려면 UI 설계까지도 변결할 필요가 있음 

- 선택률이 높은 경우에는 인덱스 온래 스캔을 활용 

- 인덱스를 사용한 성능 개선도 I/O 비용을 줄이기 위한 노력