[RDB] Index 효과적으로 설정하는 법

Index의 개념

• Index란 DB의 테이블에 대한 검색 속도를 향상 시켜주는 자료구조.
• RDB는 데이터를 저장할 때는 내부적으로 아무런 순서없이 저장하는데 이때, 데이터 저장영역은 Heap 이라고 한다. Heap에서는 인덱스가 없는 테이블의 데이터를 찾을 때
  전체 데이터 페이지의 처음 레코드부터 끝 페이지 마지막 레코드까지 모두 조회하게 된다.이러한 검색 방식을 풀 스캔(Full Scan) 또는 테이블 스캔(Table Scan)이라고 한다.

Index의 장점

1. 테이블을 검색하는 속도와 성능이 향상된다.(ORDER문이나 MIN/MAX도 마찬가지)
2. Index는 WHERE절에서 효과가 있다.

Index의 단점

1. 인덱스를 관리하기 위한 추가 작업 필요
2. 추가 저장공간 필요
3. 잘못 상용하는 경우 검색 성능 저하

• INSERT : 새로운 데이터에 대한 인덱스를 추가

• DELETE : 삭제하는 데이터의 인덱스를 사용하지 않는다는 작업 수행

• UPDATE : 기존의 인덱스를 사용하지 않음 처리, 갱신된 데이터에 대한 인덱스 추가

  이처럼 인덱스의 수정도 추가적으로 필요하기 때문에 데이터의 수정이 잦은 경우 성능이 낮아진다. 또 데이터의 인덱스를 제거하는 것이 아니라 '사용하지 않음'으로 처리하고 남겨두기 때문에 수정 작업이 많은 경우 실제 데이터에 비해 인덱스가 과도하게 커지는 문제점이 발생할 수 있다. 별도의 메모리 공간에 저장되기 때문에 추가 저장 공간도 많이 필요하게 된다. 

  또한 인덱스는 전체 데이터의 10 ~ 15% 이상의 데이터를 처리하거나, 데이터의 형식에 따라 오히려 성능이 낮아질 수 있다.예를 들어 나이나 성별과 같이 값의 range가 적은 컬럼인 경우, 인덱스를 읽고 나서 다시 많은 데이터를 조회해야 하기 때문에 비효율적이다. 

Index 설정 기준

앞서 설명한 단점으로 인해 마구잡이식의 인덱스 설정보다는 퍼포먼스를 극대화할 수 있는 설정 기준이 필요하다.

1. 카디널리티(Cardinality)가 높은 컬럼
   카디널리티가 높다 = 한 컬럼이 가지고 있는 값의 중복도가 낮음(값들이 대부분 다른 값을 가짐)
   카디널리티가 낮다 = 한 컬럼이 갖고 있는 값의 중복도가 높음(값들이 거의 같은 값을 가짐)

2. 선택도(Selectivity)가 낮은 컬럼
   선택도가 5 ~ 10%가 적정
   선택도가 높다 = 한 컬럼이 가지고 있는 값 하나로 여러 row가 찾아진다
   선택도가 낮다 = 한 컬럼이 가지고 있는 값 하나로 적은 row가 찾아진다

    선택도 계산법 
    (= 컬럼의 특정 값의 row 수 / 테이블의 총 row 수 * 100)
    ex) 10개의 데이터에서 고유한 학번(grade) 컬럼, 2명씩 같은 이름(name) 컬럼, 5명씩 같은 나이(age) 컬럼인 경우
    ① 학번(grade) 컬럼 선택도: 1 / 10 = 10%
    ② 이름(name) 컬럼 선택도: 2 / 10 = 20%
    ③ 나이(age) 컬럼 선택도: 5 / 10 = 50%

3. 활용도가 높은 컬럼
   WHERE, ORDER BY, JOIN에 자주 사용되는 컬럼에 사용

4. 수정빈도가 낮은 컬럼

Index를 타지 않는 쿼리

1. LIKE 연산자를 사용한 경우
- LIKE 연산자를 이용하여 검색할 경우 %를 뒤에 넣어 사용
- 가능하면 INSTR을 사용하는 것도 괜찮다.

SELECT name FROM table WHERE name LIKE '%n%'
-> SELECT name FROM table WHERE name LIKE 'n%'
-> SELECT name FROM table WHERE INSTR(name, 'n') > 0

2. 수식이나 함수 등으로 인덱스 컬럼을 변형하였을 경우

3. 내부적으로 데이터 형 변환이 일어난 경우

SELECT name FROM table WHERE name = '20200101'
-> SELECT name FROM table WHERE name = TO_DATE('20200101', 'YYYYMMDD')

4. 조건절에 NULL 또는 NOT NULL을 사용하는 경우
- 인덱스를 구성한 컬럼값이 전부 NULL이라면 인덱스는 이런 값을 저장하지 않음

5. 부정형으로 조건을 사용한 경우

SELECT name FROM table WHERE number != 10
-> SELECT name FROM table WHERE number < 10 AND number > 10
-> SELECT name FROM table WHERE NOT EXISTS (SELECT name FROM table WHERE number = 10)

6. OR 조건 사용
- UNION 절 활용

SELECT t1.name FROM table t1, table2 t2 WHERE t1.name = t2.name OR t1.age = t2.age
-> SELECT t1.name FROM table t1, table2 t2 WHERE t1.name = t2.name 
UNION ALL
SELECT t1.name FROM table t1, table2 t2 WHERE t1.age = t2.age 

결합 Index(Composite Index)

결합인덱스란 복수의 컬럼으로 구성된 인덱스

//결합 인덱스 생성 구문 예시
CREATE INDEX idx_composite_index
ON Test(id, name, date)

결합 Index의 컬럼 정하기

1. 조회조건에 항상 포함되는 경우
   - 결합인덱스이 첫번째 컬럼이 조건에 사용되지 않으면 그 결합인덱스는 선택되지 않음.
2. 첫번째 컬럼으로 최대한 많이 필터링할 수 있는 경우
   - 결합인덱스의 첫번째 컬럼 처리 범위를 산정하기 때문에 최대한 많은 필터링을 하도록 결정하는 것이 좋음.
3. '=' 조건으로 사용되는 경우
   - 결합인덱스의 첫번째 컬럼이 '=' 조건이 아니라면 이후의 조건에서 '='을 사용하더라도 처리범위는 줄어들지 않음.
4. AND 조건으로 검색되는 경우
   - OR로 사용되는 경우는 결합인덱스를 사용하면 안됨.