June

index 개념과 올바른 index 설정법

September 27, 2023

index 기본개념

RDS 에서 사용하는 인덱스눈 구조에 따라 크게 3가지로 분류 가능

1. B-tree 인덱스(B+tree)

  • B-tree 인덱스는 이름 그대로 데이터를 트리 구조로 저장하는 형태의 인덱스

  • 균형잡힌 뛰어난 범용성을 인정받아 가장 많이 사용된다.

    • 보통 인덱스라고 말하면, 대부분 B-tree 인덱스를 지칭하는 것이다.
    • 실제로 특별한 수식 붙이지 않은 채 CREATE INDEX 구문을 실행하면, 모든 DBMS 에서 암묵적으로 B-tree 인덱스가 만들어짐
  • 검색 알고리즘이 가장 뛰어나게 성능이 좋은 것은 아니지만, 균형이 잘 잡혀있기 때문.
  • 사실 대부분의 데이터베이스는 트리의 리프 노드에만 키값을 저장하는 B+tree라는, B-tree의 수정 버전을 채택한다.(Mysql 포함)
  • 이는 B-tree에 비해 검색을 보다 효율적으로 만든 알고리즘으로, 데이터베이스 외에 파일 시스템 등에서도 사용된다.

  • B+tree 검색 성능이 뛰어난 이유

    • 루트와 리프의 거리를 가능한 일정하게 유지하려 한다. 따라서 균형이 잘 잡혀 검색 성능이 안정적
    • 트리의 깊이도 대개 3-4정도의 수준으로 일정할 뿐 아니라, 데이터가 정렬 상태를 유지하므로 이분 탐색을 통해 검색 비용을 크게 줄일 수 있다.
    • 데이터가 정렬되어 있는 만큼 잘만 활용하면 집약 함수 등에서도 요구되는 정렬을 하지 않은 채 넘어갈 수도 있다.

2. 비트맵 인덱스

  • 데이터를 비트 플래그로 변환해서 저장하는 형태의 인덱스로, 카디널리티가 낮은 필드에 대해 효과를 발휘한다.

  • 하지만, 갱신할 때 오버헤드가 너무 크기 때문에 빈번한 갱신이 일어나지 않는 BI/DWH 용도로 사용된다.

    3. 해시 인덱스

  • 키를 해시 분산해서 등가 검색을 고속으로 시랭하고자 만들어진 인덱스
  • 하지만 등가 검색 외에는 효과가 거의 없고 범위 검색을 할 수 없다는 점 때문에 거의 사용되지 않는다.

인덱스 활용법(B+tree)

  • B+tree의 장점은 범용성이다.

    • 키값 사이에 검색 속도의 불균형이 거의 없으므로 데이터양이 증가해도 검색 속도가 갑자기 약화하는 일이 없다.
    • 등호(=) 뿐만 아니라 부등호(>, <, >=, <=) 를 사용한 검색 조건에서도 사용할 수 있다.

1. 카디널리티와 선택률

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

  • 카디널리티

    • 값의 균형.
    • 카디널리티가 가장 높다 -> 모든 레코드에 다른 값이 들어가있는 경우. 유일 키 필드.
    • 카디널리티가 가장 낮다 -> 모든 레코드에 같은 값이 들어가있는 경우.

  • 선택률

    • 특정 필드값을 지정했을 경우 테이블 전체에서 몇 개의 레코드가 선택되는지 나타내는 개념.
    • 예를 들어, 100개의 레코드를 가진 테이블에서 유일키로 ‘pkey=1’ 처럼 등호를 지정한다면 한 개의 레코드가 선택될 것이므로 1/100 = 0.01로 선택률은 1%

  • 클러스터링 팩터(참고만)

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

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

  • 인덱스를 작성하는 필드 집합의 조건은 두 가지 지표로 판단한다.

  • 카디널리티가 높을 것.

    • 값이 평균치에서 많이 흩어져있을수록 좋은 후보

  • 선택률이 낮을 것.

    • 한 번의 선택으로 레코드가 조금만 선택되는 것이 좋은 후보
    • 최근 DBMS에서는 대체로 5~10% 이하가 기준.
    • 따라서 5% 미만이라면 해당 필드 집합은 인덱스를 작성할 가치가 있다.
    • 선택률이 10%보다 높다면 테이블 풀 스캔을 하는 편이 더 빠를 가능성이 커진다.

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

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

  • 1억건의 데이터가 있는 Orders 테이블이 있다고 하자.

    SELECT order_id, receive_date
FROM Orders;
  • 위의 쿼리는 실행 계획 없어도 테이블 풀 스캔이라는 것을 알 수 있다.
  • 레코드를 압축하는 WHERE 구가 애시당초 없으므로 인덱스를 작성할만한 필드도 존재하지 않는다.
  • 그런데 실무에서는 이런 극단적인 경우가 잘 없다.

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

  • 압축조건이 있기는 하지만 레코드를 압축하지 못하는 경우

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

  • Orders 테이블에서 process_flg의 분포가 다음과 같다고 가정해보자.

    • 1(주문단계) : 200만 건
    • 2(주문완료) : 500만 건
    • 3(재고확인중) : 500만 건
    • 4(배송준비중) : 500만 건
    • 5(배송완료) : 8,300만 건
  • process_flag = '5' 라는 검색 조건은 존재하지만, 이 조건만으로 레코드 절반 이상이 선택된다. 선택률이 83%로 매우 높은 수치.
  • 이 상태에서 process_flg 필드에 인덱스를 만들면, 설령 그것을 사용하더라도 풀 스캔을 할 때보다 느려질 가능성이 높다. 결국 역효과만 발생하는 것.
  • 인덱스가 제대로 작동하려면 어디까지나 레코드를 크게 압축할 수 있는 조건 이 있어야 한다.
  • 이처럼 필드명이 _flg 또는 _status 가 붙은 필드는 특정 종류만을 지정하는 경우가 많다.(종류의 수가 적으므로). 인덱스로 만들기에 적절하지 않다.

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

  • 예를 들어 기간 검색과 같은 경우

    SELECT order_id
FROM Orders
WHERE receive_date BETWEEN :start_date AND :end_date;
  • 위의 매개변수에 따라 선택률이 낮아지거나 높아진다.

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

  • 주문받은 점포를 검색 기준으로 입력할 경우

    SELECT COUNT(*)
FROM Orders
WHERE shop_id = :sid;

  • 점포의 규모에 따라 큰 차이가 날 것.

    • 대규모라면 1000만건, 소규모라면 10만건
  • 전자의 경우 선택률이 10%, 후자의 경우 선택률이 0.01%
  • 전자의 경우만을 생각한다면 인덱스 스캔보다 테이블 풀 스캔이 나을 것. 후자는 인덱스 스캔이 더 나을 것
  • 중요한 점은, shop_id 필드에 인덱스가 존재할 경우 전자일 때는 오히려 성능 악화를 일으킨다는 사실. 결국 인덱스를 사용하면 빨라질 것이라는 기대에 어긋남
  • 옵티마이저가 전자에 대해서는 풀 스캔을 수행, 후자에 대해서는 인덱스 스캔을 선택해주면 좋겠지만 그러지 못하는 경우가 많다.

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

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

3.1 중간 일치, 후방 일치의 LIKE 연산자

SELECT order_id
FROM Orders
WHERE shop_name LIKE '%대공원%';
  • 예를 들어, ‘대공원 입구 지점’ 이라던지 ‘대공원역 지점’ 등을 결과에 포함하는 조건.
  • 선택되는 레코드 수가 5000개라면, 0.005%로 압축 자체는 매우 좋다. 하지만 효율적인 검색이 안된다.
  • ')에만 적용할 수 있다.
  • 현재 예제처럼 중간 일치(‘%대공원%’) 또는 후방일치(‘%대공원’)는 인덱스를 사용할 수 없다.
  • 따라서 이 정도로 선택률이 좋은 검색 조건이라고 해도 풀 스캔을 사용할 수 밖에 없다.

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

SELECT *
FROM SomeTable
WHERE col_1 * 1.1 > 100;
  • 색인 필드로 연산하는 경우, 즉 위처럼 사용하면 인덱스를 사용할 수 없다.

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

    WHERE COL_1 > 100/1.1

3.3 IS NULL을 사용하는 경우

  • IS NULL을 사용하는 경우에도 인덱스를 사용할 수 없다.

  • NULL과 관련한 검색 조건에서 인덱스가 사용되지 않는 것은 일반적으로 색인 필드의 데이터에 NULL이 존재하지 않기 때문이다.

    SELECT *
FROM SomeTable
WHERE col_1 IS NULL;

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

  • 색인 필드에 함수를 사용하면 인덱스가 적용되지 않는 이유는 ‘색인 필드에 연산을 하는 경우’와 동일하다.

  • 인덱스 내부에 존재하는 값은 어디까지나 col1이지 LENGTH(col1) 이 아니기 떄문이다.

    SELECT *
FROM SomeTable
WHERE LENGTH(col_1) = 10;

3.5 부정형을 사용하는 경우

SELECT *
FROM SomeTable
WHERE col_1 <> 100;
  • 부정형(<>, !=, NOT IN)은 인덱스를 사용할 수 없다.

참고문헌 : DB 성능 최적화를 위한 SQL 실전 가이드 SQL 레벨업 / 미크 지음, 윤인성 옮김 / 한빛미디어

Written by@[June]
Backend Developer