인덱스 기본

1.인덱스 구조 및 탐색

데이블에서 데이터 탐색 2가지 방법

↳ 1) 테이블 전체를 스캔한다.

↳ 2) 인덱스를 이용한다

[ 인덱스 튜닝의 두 가지 핵심요소 ]

↳ 인덱스는 큰 테이블에서 소량 데이터를 검색할 때 주로 사용한다.

핵심요소 첫 번째, 인덱스 스캔 효율화 튜닝 -> 인덱스 스캔 과정에서 발생하는 비효율을 줄인다.

핵심요소 두 번째 , 랜덤 액세스 최소화 튜닝 -> 테이블 액세스 횟수를 줄인다.

 

[ SQL 튜닝은 랜덤 I/O와의 전쟁 ]

↳ 데이터베이스 성능이 느린 이유는 디스크 I/O 때문이다. 읽어야 할 데이터량이 많고, 그 과정에서 디스크 I/O가 많이 발생할 때 느리다. OLTP 시스템이라면 디스크 I/O중에서 랜덤 I/O가 특히 중요하다.

 

[B Tree 인덱스 구조]

↳ 루트와 브랜치 블록에 있는 각 레코드는 하위 블록에 대한 주소값을 갖는다. 키값은 하위 블록에 저장된 키 값의 범위를 나타낸다.

↳ LMC(Leftmost Child)는 자식 노드 중 가장 왼쪽 끝에 위치한 블로을 가리킨다. 그리고 키값을 가진 첫번째 레코드보다 작거나 같은 레코드가 저장되어 있다. (루트와 브랜치 블록에 키값을 갖지 않은 유일한 레코드)

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

ㆍ  ROWID = 데이터 블록 주소+ 로우 번호

ㆍ  데이터 블록 주소 = 데이터 파일 번호 + 블록 번호 

ㆍ  블록 번호 = 데이터파일 내에서 부여한 상대적 순번

ㆍ  로우 번호 = 블록 내 순번

 

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

1) 수직적 탐색 : 인덱스 스캔 시작지점을 찾는 과정

   ↳ 루트(Root) 블록에서부터 시작한다.

2) 수평적 탐색 : 데이터를 찾는 과정

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

   ↳ 수평적으로 탐색하는 이유

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

    2) ROWID를 얻기 위해서이다.

 

2. 인덱스 기본 사용법

[인덱스 Range Scan]

 ↳  "인덱스 컬럼을 가공하면 인덱스를 정상적으로 사용(Range Scan)할 수 없다"

왜냐하면 인덱스 스캔 시작점을 찾을 수 없기 때문이다.

즉, Index Rang Scan은 인덱스에서 일정 범위를 스캔한다는 뜻이다. 일정 범위를 스캔하려면 '시작지점'과 '끝지점'이 있어야 한다.

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

  ↳ 인덱스를 Range Scan하기 위한 가장 첫 번째 조건은 인덱스 선두 컬럼이 조건절에 있어야한다는 사실이다.(가공하지 않은 상태로)

인덱스 Range Scan = 인덱스를 탄다

SELECT *
FROM 주문상품
WHERE 주문일자 = :ord_dt
AND 상품번호 LIKE '%PING%';

SELECT *
FROM 주문상품
WHERE 주문일자 = :ord_dt
AND SUBSTR(상품번호, 1 ,4) ='PING';

  ↳ 위 SQL에서 상품번호는 스캔 범위를 줄이는 데 전혀 역할을 하지 못한다. 첫번째는 중간 값 검색이기 때문이고 두번째는 컬름을 가오했기 때문이다.  이를 두고 인덱스를 잘 탄다고 성능 문제없다고 생각하면 안된다.

 

[ 인덱스를 이용한 소트 연산 생략 ]

인덱스 : 장비번호 + 변경일자 + 변경순번

  ↳ 인덱스를 Range Scan 할 수 있는 이유는 데이터가 정렬돼 있기 때문이다. 찾고자 하는 데이터가 정렬된 상태로 서로 모여있기 때문에 전체가 아닌 일정 부분만 읽다가 멈출 수 있다.

테이블과 달리 인덱스는 정렬돼 있기 때문에 인덱스를 사용한다. 인덱스가 정렬돼 있어서 Range Scan이 가능하고, 소트 연산 생략 효과도 부수적으로 얻게 된다. 실행계획에 sort order by 연산이 없음을 확인할 수 있다. 만약 정렬 연산이 생략되었다면 ORDER BY  연산을 추가하면 된다.

 

[ SELECT-LIST에서 컬럼 가공 ]

인덱스를 "장비번호+변경일자+변경순번" 순으로 구성하고 아래와 같이 값을 구하면 따로 정렬 연산을 수행하지 않는다.

↳ 수직적 탐색을 통해 조건을 만족하는 가장 왼쪽 지점으로 내려가서 첫 번째 읽는 레코드가 바로 최소값

↳ 수직적 탐색을 통해 조건을 만족하는 가장 오른쪽 지점으로 내려가서 첫 번째 읽는 레코드가 바로 최대값

↳ 인덱스를 이용해 정렬없이 최소 또는 최대값을 빠르게 찾을 때의 실행계획, 인덱스 리프 블록의 왼쪽(MIN) 또는 오른쪽(MAX)에서 레코드 하나(FIRST ROW)만 읽고 멈춘다.

 

[ 자동 형변환 ]

옵티마이저가 SQL을 아래와 같이 변환했고, 결과적으로 인덱스 컬럼이 가공되어서 인덱스를 Range Scan할 수 없었다.

오라클에서 숫자형과 문자형이 만나면 숫자형이 이긴다. 숫자형 컬럼 기준으로 문자형 컬럼을 변환한다는 뜻이다.

하지만 연산자가 LIKE일 때는 다르다. LIKE자체가 문자열 비교 연산자이므로 이때는 문자형 기준으로 숫자형 컬럼이 변환된다.

연산자가 LIKE일때 문자형 기준으로 숫자형 컬럼이 변환된다.

 

출처: 개발자를 위한 SQL 튜닝 입문서 친절한 SQL 튜닝