SQL 인덱스 구조 및 탐색, 기본 사용법

2.1 인덱스 구조 및 탐색

출처 : 친절한 SQL 튜닝

 

데이터베이스 테이블에서 데이터를 찾는 방법:

1. 테이블에서 전체 스캔

2. 인덱스 이용

 

 

인덱스는 큰 테이블에서 소량 데이터 검색 시 사용.

온라인 트랜잭션 처리(Online Transaction Processing, OLTP)시스템에서는 소량 데이터를 주로 검색하므로 인덱스 튜닝이 중요

 

세부 인덱스 튜닝 방법

1. 인덱스 스캔 효율화 튜닝

2. 랜덤 액세스 최소화 튜닝 -> 성능에 미치는 영향이 크다

                                        : 인덱스 스캔 후, 테이블 레코드를 액세스할 때, 랜덤 I/O 방식 사용

 ex) 학생 리스트를 이름과 시력 순으로 정렬해 두었다면, 특정 이름, 특정 시력을 가진 학생을 찾기 쉽다.

    시력과 이름 순으로 정렬해두었다면 효율성 떨어진다.

 

데이터 베이스 성능이 느린 이유는 디스크 I/O 때문이다.

읽어야 할 데이터량이 많고, 그 과정에 디스크 I/O가 많이 발생할 때 느리다.

인덱스를 많이 사용하는 OLTP 시스템이라면 디스크 I/O 중에서도 랜덤 I/O가 특히 중요

 

 

<인덱스 구조>

인덱스 : 대용량 테이블에서 필요한 데이터만 빠르게 효율적으로 액세스 하기 위해 사용

데이터를 검색할 때, 일부만 읽고 멈추는, 범위 스캔(Range Scan)이 가능하다 : 인덱스가 정렬 되어있기 때문

 

DBMS는 일반적으로 B*Tree 인덱스를 사용한다.

 ** B*Tree : 나무를 거꾸로 뒤집은 모양이어서 뿌리(Root)가 위쪽에 있고, 가지(Branch)를 거쳐 맨 아래 입사귀(Leaf)

    ( B*Tree 의 B는 Balanced : delete 작업 떄문에, 인덱스가 불균형 상태에 놓을 수도 있다고 설명하지만,

    이런 현상은 없다.

    'Balanced'는 어떤 값으로 탐색하여도 인덱스 루트에서 리프 블록에 도달하기 까지 읽는 블록 수 같음

    즉, 루트로부터 모든 리프 블록까지의 높이는 항상 같다.)

 

루트와 브랜치 블록에 있는 각 레코드는 하위 블록에 대한 주소값을 갖는다.

루트와 브랜치 블록에는 키값을 갖지 않는 특별한 레코드가 하나 있다 : LMC(Leftmost Child)

 ** LMC?

   자식 노드 중 가장 왼쪽 끝에 위치한 블록

리프 블록에 저장된 각 레코드는 키값 순으로 정렬돼 있고, 테이블 레코드를 가리키는 주소값(ROWID)를 갖는다.

인덱스 키값이 같으면 ROWID 순으로 정렬

 

ROWID는 데이터 블록 주소(DBA, Data Block Address)와 로우 번호로 구성되므로,

위의 값을 알면, 테이블 레코드를 찾을 수 있다.

• ROWID = 데이터 블록 주소 + 로우 번호
• 데이터 블록 주소 = 데이터 파일 번호 + 블록 번호
• 블록 번호 : 데이터파일 내에서 부여한 상대적 순번
• 로우 번호 : 블록 내 순번

인덱스 탐색 과정은 수직적 탐색과 수평적 탐색으로 나눌 수 있다.

• 수직적 탐색 : 인덱스 스캔 시작지점을 찾는 과정
• 수평적 탐색 : 데이터를 찾는 과정

 

<인덱스 수직적 탐색>

인덱스 스캔 시작지점을 찾는 과정

 

루트 블록에서부터 시작한다.

루트를 포함해 브랜치 블록에 저장된 각 인덱스 레코드는 하위 블록에 대한 주소값을 갖는다. :

    루트에서 시작해 리프 블록까지 수직적 탐색이 가능한 이유

 

인덱스를 수직적으로 탐색할 때, 루트를 포함한 브랜치 블록은 등산 푯말과 같은 역할

조건을 만족하는 첫 번째 레코드가 목표 지점

 

<인덱스 수평적 탐색>

수직적 탐색을 통해 스캔 시작점을 찾았으면, 찾고자 하는 데이터가 더 안 나타날 때까지 인덱스 리프 블록을 수평적으로 스캔 : 본격적으로 데이터를 찾는 과정

 

인덱스 리프 블록끼리는 서로 앞뒤 블록에 대한 주소값 가짐 : 양방향 연결 리스트(double linked list)

 

수평적으로 탐색하는 이유

1. 조건절을 만족하는 데이터를 모두 찾기 위해서

2. ROWID를 얻기 위해서

    : 필요한 컬럼을 인덱스가 모두 갖고 있어, 인덱스만 스캔하고 끝나는 경우도 있지만,

    일반적으로 인덱스를 스캔하고서 테이블도 액세스함 -> 이 때, ROWID 필요

 

 

<결합 인덱스 구조와 탐색>

ex) select 이름 성별 from 사원 where 성별 = '여자' and 이름 = '유관순'

  1. 인덱스를 성별 + 이름으로 구성한 경우:

    총 사원 50명 중에서 성별 = '여자'인 레코드 25건을 찾고, 거기서 이름을 검사해 최종적으로 2명 출력

    -> 25번의 검사

  2. 인덱스를 이름 + 성별 순으로 구성한 경우

    총 사원 50명 중에서 이름 = '유관순'인 레코드 2건을 찾고, 거기서 성별을 검사해 최족적으로 2명 출력

    -> 2번의 검사

 

 

 

2.2 인덱스 기본 사용법

인덱스 컬럼(선두 컬럼)을 가공하지 않아야 인덱스를 정상적으로 사용할 수 있다.

인덱스를 정상적으로 사용한다 : 리프 블록에서 스캔 시작점을 찾아 거기서부터 스캔하다가 중간에 멈추는것

    리프 블록 일부만 스캔하는 Index Range Scan

 

인덱스 컬럼을 가공해도 인덱스를 사용할 수는 있지만, 스캔 시작점을 찾을 수 없고, 멈출 수도 없어 리프 블록 전체를 스캔해야한다. 즉, 일부가 아닌 전체를 스캔하는 Index Full Scan 방식으로 작동

 

 

<인덱스를 Range Scan 할 수 없는 이유>

인덱스 스캔 시작점을 찾을 수 없기 때문

 

where 생년월일 between '20070101' and '20070131'

스캔 시작 지점과 종료지점을 알 수 없다. 모든 사람을 다 스캔해야한다.

 

데이터베이스에서 조건절을 처리할 때도 같은 문제에 직면한다.

인덱스에는 가공되지 않는 값이 저장돼 있는데, 가공된 값을 기준으로 검색하려면 어디서 스캔을 시작해야할지, 멈춰야할지 모른다.

where substr(생년월일, 5, 2) = '05'

where nvl(주문수량, 0) < 100

    값이 NULL이면 0으로 치환한 값 기준으로 100보다 작은 레코드를 찾아달라고 쿼리를 작성하면,

    인덱스 스캔 시작지점을 찾을 수가 없다.

    그래서 인덱스를 정상적으로 사용할 수 없고, 인덱스를 Range Scan 할 수 없다.

where 업체명 like '%대한%'

    '대한'으로 시작하는 값은 중간에 모여 있으므로 Range Scan이 가능하지만,

    '대한'을 포함하는 값은 전체 구간에 걸쳐 흩어져 있어 Range Scan이 불가능하다.

where (전화번호 = :tel_no OR 고객명 = :cust_nm)

    OR 조건으로 검색할 때, 수직적 탐색을 통해 특정 전화번호와 특정 고객명인 어느 한 시작 지점을 바로 찾을 수 없다

    인덱스를 어떤 방식으로 구성해도 Range Scan 할 수 없다.