DB index

jaeyoung0509·2022년 1월 15일
0

db

목록 보기
1/2

index

회사에서 인턴생활 하면서 query들을 들여다 볼 시간이 생겼다.
그래서 오라클의 explain plan 을 통해 어떤 방식으로 인덱스를 참조되는지 살펴보고 다큐먼트와 다른 블로그 글들을 통해 정리해봤다.

  • 인덱스는 테이블에 대한 동작 속도를 높히기 위해 사용되는 오브젝트
  • 전체탐색 경우엔 , 인덱스보다 전체탐색이 더 빠름
  • 테이블에 데이터가 많은데 반환되는 데이터가 적은 경우, 인덱스 선택

인덱스 탐색 방식

index unique scan

  • 수직적 탐색만으로 데이터를 찾는 스캔 방식 ,
  • 결과로 하나의 건이 반환될때 , 칼럼이 유일한 값으로 구성
  • primary key 에 사용된 index
  • 동등 조건 "="
  • singble block

index range scan

  • 가장 일반적인 index 탐색 방식
  • index를 수직 탐색 후 필요한 범위까지만 탐색
  • 인덱스 데이터는 정렬된 상태로 저장, 범위에 대한 탐색 가능
  • explain plan에 index range sanc이 나온다고 해서 안심해두 되는거 아님
  • 인덱스를 스캔하는 범위를 얼마만큼 줄일수 있느냐 ,테이블 엑세스 횟수를 얼마만큼 줄일 수 있느냐

index full scan

  • 수직적 탐색 없이,인덱스 리프 블록을 처음부터 끝까지 수평적으로 탐색
  • 데이터 검색을 위한 최적의 인덱스가 없을 때 차선
  • 주로 테이블에서 table full scan 부담이 크거나 정렬작업을 생략하기 위해 , 전체 테이블 탐색보다 index를 사용하는것이 유리
  • 인덱스의 모든 블록을 읽음
  • 정렬 작업을 생략하기 위해 사용
  • not null 조건으로 인덱스 탐색 가능

index fast full scan

  • index full scan보다 빠름 , multi-block read가 가능해서
  • 하지만 정렬된 상태로 데이터 받을 수 없음

Index 사용하지 못하는 경우

  • 인덱스 컬럼에 변형이 일어난 경우
    WHERE TO_CHAR(HIREDATE,'YYYYMMDD') = '19980518';
  • 내부적인 변형이 일어난 경우(EMP_ID 는 varchar , 200383은 숫자)
    WHERE EMP_ID = 200383;
  • null을 비교할 경우
    WHERE JOB IS NULL WHERE JOB NOT IN ( 'INSTRUCTOR','STAFF');
    • Oracle의 경우 일반적으로 index를 생성할 때 null값은 index항목에 넣지 않는다.
      따라서 null은 index에 없기 때문에 null조건을 준다면 그것은 index를 탈수 없다.
      따라서 위와 같은 경우 반드시 index를 타려거든 job컬럼을 NOT NULL로 설정하고 NUL대신 특정값 (예를 들면 : ‘NOT ASSIGN’ ) 으로 설정하고 QUERY를 아래와 같이 수정한다면 인덱스를 탈수 있을 것이다
  • 부정형으로 조건을 기술할 경우
    • <, > , BETWEEN조건에 만 인덱스를 탈수 있고 부정형으로 비교했을때는 인덱스를 탈수 없기때문이다.

Index가 항상 좋은건가?

  • disk io에 있어 그렇지 않다 , full scan은 한번에 여러 io하기 때문에 많은 양 데이터를 가져올경우엔 fulls scan이유리

Index 생성시 고려사항

카디널리티(Cardinality)

  • 카디널리티가 높을 수록 인덱스 설정에 좋은 컬럼
    => 한 컬럼이 갖고 있는 값의 중복 정도가 낮을 수록 좋음
    => 예를 들어) 학번의 경우엔 고유하지만 ,이름의 경우엔 중복 될 확률이 상대적으로 높음

선택도(Selectivity)

  • 5 ~ 10%가 적당
    1. 학번의 경우엔 모두 고유 1/10 * 100 = 10%
    2. 이름(2명씩 중복)의 경우 2/10 * 100 = 20%
    3. 성별(50:50) 의 경우 5/10 * 100 = 50%
  • 데이터에서 특정 값을 얼마나 잘 선택할 수 있는지에 대한 지표

활용도

  • 해당 컬림이 실제 작업에서 얼마나 잘 활용되는지
  • WHERE , 수동 쿼리 조회 등 서비스에서 쿼리를 날릴 때 얼마나 잘 활용 되는지

중복도

  • 중복도가 없을 수록 인덱스 설정에 좋은 컬럼
  • 인덱스의 성능에 대한 고려없이 마구잡이 설정
    분리된 요청으로 같은 컬럼에 대해 인덱스가 중복으로 생성 된 경우
  • 인덱스도 속성을 가지며 , 테이블 형태로 생성 , 이 속성이 다를 때 같은 컬럼에 대해 중복으로 인덱스 설정이 가능
  • 인덱스도 메모리의 일부 ,필요없는 항목은 삭제

Index 종류

B-tree 인덱스

  • dbms에서 가장 일반적인 인덱스로 ,트리 구조 최상위에 하나의 root block node , 그 하위에 자식 block node가 있는 형태
  • 가장 하위에 있는 노드를 leaf node라 하며 , 나머지를 branch block
  • 인덱스의 leaf block은 항상 실제 데이터 레코드를 찾아가기 위한 주소값을 가지고 있다

    특징

    • 인덱스 데이터 순서대로 정렬
    • branch block 중에서 가장 상위 블록을 root block
    • branch block은 분기를 목적으로 하는 블록

참고

profile
매일 한걸음씩 걷자

0개의 댓글