close
프로필 배경
프로필 로고

고은별의 기술 공유 연구소

Database/SQL · 2023. 7. 19. fullscreen 넓게보기

SQL - 인덱스의 개념, 특징과 종류, 차이점, 데이터 액세스 기법(전체 테이블 스캔과 인덱스 스캔)

반응형

 

인덱스의 개념

  • 원하는 데이터를 쉽게 찾을 수 있도록 책의 찾아보기와 유사한 개념
  • 테이블을 기반으로 선택적으로 생성할 수 있는 구조
  • 테이블에 인덱스를 생성하지 않아도 되고 여러 개를 생성 가능
  • 기본적인 목적은 검색 성능의 최적화
    • 즉, 검색 조건을 만족하는 데이터를 인덱스를 통해 효과적으로 찾도록 함
  • 단, DML 작업은 테이블과 인덱스를 함께 변경해야 하기 때문에 오히려 느려지는 단점 존재
    • Insert, Update, Delete 등

인덱스의 종류

트리 기반 인덱스 (B-Tree Index)

<B-트리 인덱스 구조, 출처 한국데이터산업진흥원>

특징

  • B-트리 인덱스는 DBMS에서 가장 일반적인 인덱스로 사용
  • 일치 검색과 범위 검색에 모두 적합한 구조를 가짐
    • 일치(Exact Match) 검색 : '='로 검색
    • 범위(Range) 검색: 'BETWEEN', '>' 등과 같은 연산자로 검색 
  • 인덱스 생성 시 동일 칼럼으로 구성된 인덱스를 중복해서 생성할 수 없음
    • 단, 구성 칼럼은 동일하지말 칼럼의 순서가 다르면 서로 다른 인덱스로 생성 가능
  • 브랜치 블록(Branch Block)과 리프 블록(Leaf Block)으로 구성
  • 브랜치 블록
    • 분기를 목적으로 하는 블록
    • 다음 단계의 블록을 가리키는 포인터를 지님
    • 브랜치 블록 중 가장 상위에 있는 블록을 루트 블록(Root Block)이라고 칭함
  • 리프 블록
    • 트리의 가장 아래 단계에 존재
    • 인덱스를 구성하는 칼럼의 데이터와 해당 데이터를 가지고 있는 행의 위치를 가리키는 레코드 식별자(RID, Record Identifier/Rowid)로 구성
    • 인덱스 데이터는 인덱스를 구성하는 칼럼의 값으로 정렬
      • 단, 인덱스 데이터의 값 동일 시 레코드 식별자 순서로 저장
    • 양방향 링크(Double Link)를 가지고 있어서 오름 차순(Ascending Order)과 내림 차순(Descending Order) 검색을 쉽게 할 수 있음

 

+ 추가

Oracle에서는 다른 종류의 인덱스 등이 존재

  • 비트맵 인덱스(Bitmap Index)
  • 리버스 키 인덱스 (Reverse Key Index)
  • 함수기반 인덱스 (FBI, Function-Based Index)

 

검색 절차

  1. 브랜치 블록의 가장 왼쪽 값이 찾고자 하는 값보다 작거나 같으면 왼쪽 포인터로 이동
  2. 찾고자 하는 값이 브랜치 블록의 값 사이에 존재하면 가운데 포인터로 이동
  3. 오른쪽에 있는 값보다 크면 오른쪽 포인터로 이동
  4. 리프 블록을 찾을 때까지 1-3 절차 반복
    • 리프 블록에서 찾고자 하는 값 존재 시 해당 값을 찾음
    • 해당 값이 없으면 검색 실패

예시) B-트리 인덱스에서 37을 검색하기 위한 절차

<B-트리 인덱스 검색 예시, 출처 한국데이터산업진흥원>

SQL Server의 클러스터형 인덱스

저장 구조에 따라 클러스터형(clustered) 인덱스와 비클러스터형(nonclustered) 인덱스로 구분하지만, 포스팅에서는 클러스터형 인덱스만 다룹니다.

 

특징

  1. 인덱스의 리프 페이지가 곧 데이터 페이지가 됨
    • 테이블 탐색에 필요한 레코드 식별자가 리프 페이지에 존재하지 않음
    • 인덱스 키 컬럼과 나머지 컬럼을 리프 페이지에 같이 저장하기 때문에 테이블을 랜덤 액세스 할 필요 없음
    • 리프 페이지를 탐색하면 해당 테이블의 모든 칼럼 값을 곧바로 얻을 수 있음
  2. 리프 페이지의 모든 행(데이터)은 인덱스 키 칼럼 순으로 물리적으로 정렬되어 저장 됨
    • 테이블 행은 물리적으로 한 가지 순서로만 정렬될 수 있음
    • 즉, 클러스터형 인덱스는 테이블당 한 개만 생성 가능

 

표현 예시

  • Employees 테이블
    • Employee ID 컬럼에 기반한 클러스터형 인덱스 생성
    • Employee ID, Last Name, First Name, Hire Date로 구성

<Employees 테이블의 클러스터형 인덱스 구조 예시, 출처 한국데이터산업진흥원>

  • 예시에서 확인 가능한 것처럼, 리프 블록(리프 페이지)에 인덱스 키 컬럼 외에도 테이블의 나머지 모든 컬럼의 값이 함께 있음

B-트리 인덱스와 클러스터형 인덱스 간단 비교

이해하기 쉽게 비유하자면

  • SQL Server 클러스터형 인덱스
    • 영한사전
    • 알파벳 순으로 정렬되어 있고 각 단어별 바로 옆에 한글 설명이 있음
  • B-트리 기반 인덱스
    • 전문서적
    • 끝 부분에 있는 찾아보기(=색인)가 페이지 번호만 적혀있음

데이터 검색 방식 (데이터 액세스 기법)

전체 테이블 스캔

  • 테이블에 존재하는 모든 데이터를 읽어 조건에 맞는 결과를 추출하고, 조건에 맞지 않으면 버리는 방식으로 검색
  • 즉, 검색 시 모든 블록의 데이터를 읽으므로 결과를 찾을 때까지 시간이 오래 걸릴 수 있음
  • 전체 테이블 스캔 방식으로 읽은 모든 블록들은 재사용성이 떨어지므로 메모리에서 곧 제거될 수 있도록 관리 됨

옵티마이저가 전체 테이블 스캔 방식을 선택하는 이유

  1. SQL문에 조건이 존재하지 않는 경우
    • 조건이 존재하지 않으면 테이블에 존재하는 모든 데이터가 답이 된다는 것을 의미
  2. SQL문의 주어진 조건에 사용 가능한 인덱스가 존재하지 않는 경우
    • 사용 가능한 인덱스가 없을 경우 데이터를 액세스할 방법은 테이블의 모든 데이터를 읽으면서 주어진 조건을 만족하는지를 검사하는 방법뿐이기 때문
    • 주어진 조건에 사용 가능한 인덱스가 존재하여도 함수를 사용하여 인덱스 컬럼을 변형한 경우에도 인덱스를 사용할 수 없음
  3. 옵티마이저의 취사 선택
    • 조건을 만족하는 데이터가 많을 경우, 결과를 추출하기 위해 테이블의 대부분의 블록을 액세스해야 한다고 판단되면 사용 가능한 인덱스가 존재해도 전체 테이블 스캔 방식으로 읽을 수 있음
  4. 그 밖의 경우
    • 병렬처리 방식으로 처리하는 경우
    • 전체 테이블 스캔 방식의 힌트를 사용한 경우

인덱스 스캔

이 포스팅에서는 데이터베이스에서 주로 사용되는 트리 기반 인덱스를 중심으로 설명합니다.

 

  • 인덱스를 구성하는 컬럼의 값을 기반으로 데이터를 추출하는 액세스 기법
  • 인덱스의 리프 블록은 인덱스를 구성하는 컬럼과 레코드 식별자로 구성
    • 검색을 위해 인덱스의 리프 블록을 읽으면 인덱스 구성 컬럼의 값과 테이블의 레코드 식별자를 알 수 있음
  • 인덱스 구성 컬럼의 순서로 정렬되며 인덱스를 경유하여 데이터를 읽으면 결과 또한 정렬되어 반환
    • 즉, 인덱스의 순서와 동일한 정렬 순서를 사용자가 원할 경우 정렬 작업을 하지 않아도 됨

 

인덱스 스캔의 종류

인덱스 스캔 중 자주 사용되는 인덱스는 다음과 같으며, 제공되는 인덱스 스캔은 데이터베이스마다 다를 수 있습니다.

  • 인덱스 유일 스캔 (Index Unique Scan)
    • 유일 인덱스는 중복을 허락하지 않는 인덱스를 의미
    • 즉, 단 하나의 데이터를 추출하는 방식
    • 모두 '='로 값이 주어진 경우에만 가능한 인덱스 스캔 방식
    • 이는 유일 인덱스 구성 컬럼에 모두 '='로 값이 주어지면 결과는 최대 1건이 되기 때문
  • 인덱스 범위 스캔 (Index Range Scan)
    • 한 건 이상의 데이터를 추출하는 방식
    • 유일 인덱스의 구성 컬럼에 모두 '='로 값이 주어지지 않을 경우 가능한 인덱스 스캔 방식
    • 비유일 인덱스(Non-Unique Index)를 이용하는 모든 액세스 방식은 인덱스 범위 스캔 방식으로 데이터를 액세스
  • 인덱스 역순 범위 스캔 (Index Range Scan Descending)
    • 인덱스 범위 스캔과 같으나 스캔의 순서가 역순으로 진행되는 방식을 말함
    • 이 방식을 이용하여 최대값(Max Value)을 쉽게 찾을 수 있음
  • 그 외
    • 인덱스 전체 스캔(Index Full Scan)
    • 인덱스 고속 전체 스캔(Fast Full Index Scan)
    • 인덱스 스킵 스캔(Index Skip Scan)
    • 등등..

<인덱스 오름/내림 차순 범위 스캔 구조, 출처 한국데이터산업진흥원>

전체 테이블 스캔과 인덱스 스캔 방식의 간단 비교

  • 전체 테이블 스캔
    • 방식 : 테이블의 전체 데이터를 모두 읽으면서 데이터를 추출
      • 한번의 요청으로 여러 블록을 한꺼번에 읽음
    • 장점 : 인덱스의 존재 유무와 상관없이 항상 이용 가능
    • 단점 : 모든 데이터를 읽으면서 데이터를 찾으므로 검색 속도 느림
    • 활용 측면 : 테이블의 대부분의 데이터를 찾을 때 유리
  • 인덱스 스캔
    • 방식 : 인덱스를 경유해서 데이터를 읽음
      • 한번의 요청으로 한 블록씩 데이터를 읽음
    • 장점 : 불필요하게 다른 블록을 더 읽을 필요가 없으므로 검색 속도 빠름
    • 단점 : 적절한 인덱스 존재할 때에만 이용 가능
    • 활용 측면 : 대용량 데이터 중 극히 일부의 데이터를 찾을 때 유리

참고

반응형
저작자표시 동일조건
Database/SQL 관련 글
더 보기

티스토리툴바