Link Search Menu Expand Document
melodist Dev Records

인덱스 사용

인덱스와 B-tree

만능형: B-tree

  • 데이터를 트리 구조로 저장하는 형태
  • 대부분의 데이터베이스에서는 트리의 리프 노드에만 키값을 저장하는 B+tree 사용
  • 루트와 리프의 거리를 가능한 일정하게 유지하므로 검색 성능이 안정적

    기타 인덱스

  • 비트맵 인덱스
    • 데이터를 비트 플래그로 변환
    • 카디널리티가 낮은 필드에 유효
    • 갱신할 때 오버헤드가 크므로 BI/DWH 용도로 사용
  • 해시 인덱스
    • 키를 해시 분산해서 등가 검색을 고속으로 실행
    • 등가 검색 외에는 효과가 거의 없고 범위 검색을 할 수 없음
    • PostgreSQL, Oracle 등 일부 DB만 지원

      인덱스를 잘 활용하려면

      카디널리티와 선택률

  • 카디널리티: 값의 균형을 나타냄
    • 카디널리티가 높은 필드는 모든 레코드에 다른 값이 들어가 있는 유일 키 필드
    • 카디널리티가 낮은 필드는 모든 레코드에 같은 값이 들어가 있는 필드
  • 선택률: 특정 필드 값을 지정했을 때 테이블 전체에서 몇 개의 레코드가 선택되는지를 나타냄

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

  • 인덱스를 작성하는 필드 집합의 조건
    • 카디널리티가 높음: 값이 평균치에서 많이 흩어져있음
    • 선택률이 낮음: 한 번의 선택으로 적은 갯수의 레코드가 선택됨, 대략 5~10% 이하가 기준

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

      압축 조건이 존재하지 않음

      SELECT order_id, receive_date
FROM Orders
  • 레코드를 압축하는 WHERE 구가 없으므로 인덱스로 작성할 필드도 존재하지 않음

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

    SELECT order_id, receive_date
FROM Orders
 WHERE process_flg = '5'
  • process_flg = 5인 레코드의 선택률이 높을 경우 레코드 갯수가 거의 줄어들지 않으므로 테이블 풀 스캔보다 더 느려질 수도 있음

  • 인덱스가 제대로 작동하려면 레코드를 크게 압축할 수 있어야 함

  • 입력 매개변수에 따라 선택률이 변동하는 경우 - 1
    SELECT order_id
  FROM orders
 WHERE receive_date BETWEEN :start_date AND :end_date
    • 조건이 매개변수화 되어 있을 경우 입력에 따라 선택률이 높아지거나 낮아짐
  • 입력 매개변수에 따라 선택률이 변동하는 경우 - 2
    SELECT COUNT(*)
  FROM orders
 WHERE shop_id = :sid
    • 선택률이 높을 경우 인덱스 스캔보다 테이블 풀 스캔이 나음
    • shop_id 필드에 인덱스가 존재할 경우 선택률이 높은 경우 오히려 성능이 악화됨

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

  • 중간 일치, 후방 일치의 LIKE 연산자
    SELECT order_id
FROM Orders
 WHERE shop_name LIKE '%대공원%'
    • LIKE 연산자를 사용하는 경우 인덱스는 전방 일치(대공원%)에만 적용 가능
  • 색인 필드로 연산하는 경우
    SELECT *
FROM SomeTable
 WHERE col_1 * 1.1 > 100
    • 색인 필드로 연산하는 경우에는 인덱스를 사용할 수 없음
    • 검색 조건의 우변에 식을 사용하도록 수정하면 인덱스를 사용할 수 있음 (WHERE col_1 > 100 / 1.1)
    • 마찬가지로, 색인 필드에 함수를 사용하는 경우에도 인덱스를 사용할 수 없음
  • IS NULL을 사용하는 경우
    • 일반적으로 색인 필드의 데이터에 NULL이 존재하지 않기 때문에 인덱스를 사용할 수 없음
  • 부정형을 사용하는 경우
    • <>, !=, NOT IN은 인덱스를 사용할 수 없음

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

      외부 설정으로 처리 - 깊고 어두운 강 건너기

  • UI 설계로 처리
    • ‘점포 ID’로 검색하면 반드시 ‘주문일’도 함께 입력해야 검색이 가능하도록 제한
    • 기간 검색 최대 1개월까지로 제한

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

  • 시스템을 전체적으로 조감해주는 사람이 없다면 성능을 최적화하기 어려움
  • 테스트 단계에서 성능 문제가 발견될 경우 외부 설정 변경은 사용할 수 없음

    데이터 마트로 대처

  • 데이터 마트란 특정한 쿼리(군)에서 필요한 데이터만을 저장하는 상대적으로 작은 크기의 테이블
    CREATE TABLE OrderMart
(order_id    CHAR(4) NOT NULL,
receive_date DATE NOT NULL)

    데이터 마트를 채택할 시 주의점

  • 데이터 신선도
    • 동기 사이클이 짧으면 성능 이슈 발생, 길면 신선도가 낮아짐
  • 데이터 마트 크기
    • 테이블의 크기를 줄일 수 없으면 데이터 마트를 만들어도 성능 이득을 얻을 수 없음
    • GROUP BY 절을 사용하여 집계를 마치면 효과적
  • 데이터 마트 수
    • 데이터 마트는 ER에 등장하지 않으므로 제대로 관리하기 어려움
    • 사용하지 않는 데이터 마트의 수를 줄이기 위해 데이터 마트에 지나치게 의존하면 안됨
  • 배치 윈도우
    • 데이터 마트의 통계 정보 갱신을 고려한 배치 윈도우와 Job Net 고려 필요

      인덱스 온리 스캔으로 대처

  • 특정한 필드를 커버할 수 있는 커버링 인덱스(Covering Index)를 작성
    CREATE INDEX CoveringIndex ON Orders (order_id, receive_date)
  • 이러한 인덱스를 작성하면 다음과 같은 SELECT문에서 테이블 풀 스캔이 아닌 인덱스 온리 스캔을 사용
    SELECT order_id, receive_date
FROM Orders
  • 데이터 마트를 사용할 때와 마찬가지로 I/O 비용 절감
  • 테이블 필드의 부분 집합만 저장하므로 원래 테이블에 비해 크기가 작음
  • 애플리케이션 수정이 불필요
  • 로우(레코드) 지향 저장소의 DBMS에 유사적으로 컬럼(필드) 기반 저장소를 실현

    인덱스 온리 스캔의 주의점

  • DBMS가 지원해야 사용할 수 있음
  • 한 개의 인덱스에 포함할 수 있는 필드 수에 제한이 있음
  • 갱신 오버헤드 증가
  • 정기적인 인덱스 리빌드 필요
  • SQL 구문에 새로운 필드가 추가된다면 사용할 수 없음