[SQL] 2장 인덱스 기본

* 해당 내용은 친절한 SQL 튜닝 책을 보고 정리한 게시글 입니다.

 

목차

2.1. 인덱스 구조 및 탐색

2.2. 인덱스 기본 사용법

2.1 인덱스 구조 및 탐색

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

인덱스 튜닝의 2 가지 핵심요소

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

• 인덱스 스캔 과정에서 발생하는 비효율을 줄이는 것(인덱스 스캔 효율화 튜닝)
• 예) 어떤 컬럼을 인덱스로 활용해야 스캔량이 줄어들까?

2. 랜덤 액세스 최소화 튜닝

• 테이블 액세스 횟수를 줄이는 것(랜덤 엑세스 최소화 튜닝)
• 1번보다 2번이 성능에 미치는 영향이 더크므로 중요하다
• 즉, SQL 튜닝은 랜덤 I/O 와의 전쟁이다.
  • 데이터베이스 성능이 느린 이유는 디스크 I/O 때문

 

인덱스 스캔과 랜덤 엑세스

2.1.2 인덱스 구조

• 인덱스는 대용량 테이블에서 필요한 데이터만 빠르게 효율적으로 액세스하기 위해 사용되는 오브젝트
• 데이터베이스에서 인덱스를 이용하면 범위스캔(Range Scan)이 가능
• DBMS는 일반적으로 B*Tree 인덱스를 사용

B*Tree 인덱스 구조

• 루트, 브랜치 블록에 있는 각 레코드는 하위 블록에 대한 주소값을 가진다.
• LMC(Leftmost Child) : 자식 노드 중 가장 왼쪽 끝에 위치한 블록을 가리킨다.
• 리프 블록에 저장된 각 레코드는 키값 순으로 정렬되어 있고, 테이블 레코드를 가리키는 주소값, 즉 ROWID 를 갖는다.
• 인덱스 키값이 같으면 ROWID 순으로 정렬된다.
  • ROWID = 데이터 블록 주소 + 로우 번호
  • 데이터 블록 주소 = 데이터 파일 번호 + 블록 번호
  • 블록 번호 : 데이터 파일 내에서 부여한 상대적 순번
  • 로우 번호 : 블록 내 순번
• 인덱스 과정은 수직적 탐색과 수평적 탐색으로 나뉜다.
  • 수직적 탐색 : 인덱스 스캔 시작지점을 찾는 과정
  • 수평적 탐색 : 데이터를 찾는 과정

 

2.1.3 인덱스 수직적 탐색

• 수직적 탐색 : 조건을 만족하는 첫번째 레코드를 찾는 과정(=인덱스 스캔 시작지점을 찾는 과정)

수직적 탐색은 '조건을 만족하는 레코드' 를 찾는 과정이 아니라 '조건을 만족하는 첫번째 레코드'를 찾는 과정

 

2.1.4 인덱스 수평적 탐색

수평적 탐색 : 찾고자 하는 데이터가 더 안 나타날때까지 인덱스 리프 블록을 수평적으로 스캔한다.

• 인덱스 리프 블록끼리는 서로 앞뒤 블록에 대한 주소값을 갖는다. (양방향 연결 리스트 구조)
  좌 → 우, 우 → 좌 수평적 탐색이 가능하다
• 인덱스 수평적 탐색의 이유
  1. 조건절을 만족하는 데이터를 모두 찾기 위해서
  2. ROWID를 얻기 위해서
     (인덱스만 스캔하고 끝나는 경우도 있지만, 일반적으로 인덱스를 스캔하고서 테이블로 액세스한다. 이 때, ROWID가 필요하다)

 

2.1.5 결합 인덱스 구조와 탐색

create index 고객_N1
on 고객(성별, 고객명);

select * from 고객
where 성별 = '남'
and 고객명 = '이재희'

• 수직적 탐색을 거쳐 찾은 인덱스 스캔 시작점은 성별='남'인 첫번째 레코드가 아닌 성별='남'이면서 고객명='이재희'인 레코드이다
• 인덱스 선두 컬럼을 모두 "=" 조건으로 검색할 때는 어느 컬럼을 인덱스 앞쪽에 블록 I/O 개수가 같으므로 성능도 같다.

제목 : 결합인덱스 생성 시 컬럼 배치 순서

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

1. 인덱스를 『성별+이름』 순으로 구성한 경우
  총 사원 50명 중에서 성별 = '여자'인 레코드 25건을 찾고, 거기서 이름을 검사해 최종적으로 2명 출력
→ 25번의 검사

2. 인덱스를 『이름+성별』 순으로 구성한 경우
  총 사원 50명 중에서 이름 = '유관순'인 레코드 2건을 찾고, 거기서 성별을 검사해 최종적으로 2명 출력
→ 2번의 검사

선택도가 낮은 "이름" 컬럼을 앞쪽에 두고 결합인덱스를 생성해야 검사횟수 줄일 수 있어 성능에 유리하다
=> 틀린말이다
인덱스를 [성별 + 이름]으로 하든 [이름+성별] 로 하든 읽는 인덱스 블록 개수가 똑같다.
인덱스를 어떻게 구성하든 블록 I/O개수가 같다면 성능도 같다.
위 예제에서 비교 연산 횟수가 줄어드는 건 사실이지만 성능에서 차이는 없다.

Balanced 의 의미 : B* Tree 의 B 는 Balanced 의 약자이다. Balanced 는 어떤 값으로 탐색하더라도 인덱스 루트에서 리프 블록에 도달하기까지 읽는 블록 수가 같음을 의미한다. 따라서 루트로부터 모든 리프 블록까지의 높이(height) 는 항상 같다.

 

2.2 인덱스 기본 사용법

• 인덱스 컬럼(정확히 말하며, 선두컬럼)을 가공하지 않아야 인덱스를 정상적으로 사용할 수 있다.
• 인덱스를 정상적으로 사용한다는 의미는 Index Range Scan 을 의미한다.
• 인덱스 컬럼을 가공해도 인덱스를 사용할 수 있지만, 스캔 시작점을 찾을 수 없고, 멈출 수 없어 리프 블록 전체를 스캔하는 Index Full Scan 방식으로 작동된다.

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

• 인덱스 스캔 시작점을 찾을 수 없기 때문이다.
• 일정 범위를 스캔하려면 시작지점과 끝지점이 있어야 한다.

select substr(생년월일, 5, 2) = '05'
-- 시작지점을 찾을 수 없다. 스캔 지점도 찾을 수 없다.

where nvl(주문수량, 0) < 100
-- 값이 null 면 0 으로 치환한 값은 시작지점을 찾을 수없다.

where 업체명 like '%대한%'
-- '대한'으로 시작하는 값은 특정 구간에 모여있어 range scan 이 가능하지만
-- '대한'을 포함하는 값은 전체 구간에 걸쳐 흩어져 있어 range scan 불가

where (전화번호 = :tel_no OR 고객명 = :cust_nm)
-- OR족건 또한 어느 한 시작시점을 바로 찾을 수 없다.

OR Expansion
다음과 같이 쿼리하면 고객명 전화번호 인덱스 각각에 대해 Index Range Scan 이 가능하다

select * 
from 고객
where 고객명 = :cust_nm
union all
select * 
from 고객
where 전화번호 = :tel_no
and (고객명 <> :cust_nm or 고객명 is null)

OR 조건식을 SQL 옵티마이저가 위와 같은 형태로 변환하는데 이를 Or Expansion 이라 한다. 오라클에서 use_concat 힌트로 유도가능

where 전화번호 in (:tel_no1, :tel_no2)
-- IN 조건은 OR 조건을 표현하는 다른 방식일 뿐 어느 한 지점을 찾을 수 없다.

select *
from   고객
where 전화번호 = :tel_no1
union all
select *
from   고객
where 전화번호 = :tel_no2
-- UNION ALL 방식으로 작성하면, 각 브랜치 별로 인덱스 스캔 시작점을 찾을 수 있다. Range Scan이 가능

• IN 조건은 SQL 옵티마이저가 IN-List Iterator 방식을 사용한다
  • List 개수만큼 Index Range Scan 반복 = UNION ALL 방식과 동일 효과
• OR 또는 IN 조건절은 옵티마이저의 쿼리변환 기능을 통해 Index Range scan으로 처리되기도 한다.

 

2.2.3 더 중요한 인덱스 사용 조건

• 인덱스를 Range Scan 하기 위한 가장 첫 번째 조건은 인덱스 선두 컬럼이 조건절에 있어야 한다(가공하지 않은 상태로)
• 인덱스를 Range Scan 한다고 해서 항상 성능이 좋은것은 아니다
  • 인덱스 리프 블록에서 스캔하는 양을 따져봐야 한다.

TX1234_IX02 인덱스 : 기준년도 + 과세구분코드 + 보고회차 + 실명확인보호

select * from TXA1234
where  기준연도 = :stdr_year
and    substr(과세구분코드, 1, 4) = :txtn_dcd
and    보고회차 = :rpt_tmrd
and    실명확인보호 = :rnm_cnfm_no

Execution Plan
------------------------------------------------------------------------
0      SELECT STATEMENT Optimizer=ALL_ROWS
1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TXA1234'  (TABLE)
2    1      INDEX (RANGE SCAN) OF 'TXA1234_IX02'  (INDEX)

반응형

 

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

• 인덱스는 정렬되어 있다.
• 따라서 소트연산 생략 효과도 부수적으로 얻게 된다.
• 인덱스 활용시 이미 정렬되어 있으므로 ORDER BY 가 있어도 정렬 연산을 따로 사용하지 않는다(실행계획확인가능)
• 만약 정렬 연산을 생략할 수 있게 인덱스가 구성되어 있지 않으면, SORT ORDER BY 연산 단계가 추가된다.
• 내림차순(Desc) 정렬에도 인덱스 활용 가능하다.
• 오름차순(Asc) 정렬 시 조건 만족하는 가장 작은 값을 찾아 좌측에서 수직적 탐색한 후 우측으로 수평적 탐색을 한다.
• 내림차순(Desc) 정렬 시 조건 만족하는 가장 큰 값을 찾아 우측으로 수직적 탐색한 후 좌측으로 수평적 탐색을 한다.

 

2.2.5 ORDER BY 절에서 컬럼 가공

• 인덱스 컬럼을 가공하면 정렬 효과를 누릴 수 없다.
• ORDER BY 를 가공한 값으로 정렬해 달라고 해도 정렬효과를 누릴 수 없다

SELECT *
FROM A
WHERE B='c'
ORDER BY D || E --||:컬럼 합치기

 

2.2.6 SELECT-LIST 에서 컬럼 가공

• 인덱스 활용 시 MIN MAX 를 찾는 속도가 굉장히 빠르다
  • MIN : 리프블록의 왼쪽에서 레코드 1개만 읽고 멈춘다
  • MAX : 리프블록의 오른쪽에서 레코드 하나만 읽고 멈춘다.

 

2.2.7 자동 형변환

• 데이터 타입이 서로 다른 경우 컴파일 시점에 에러를 내거나 자동으로 형변환을 해준다(DB 마다 다르다)
• 오라클의 경우 자동으로 형변환을 해주고 컬럼이 변경되어 Index range scan 이 아닌 table full scan 이 발생할 수 있다.
• 저자는 가능하면 자동형 변환 기능에 의존하지 않고 인덱스 컬럼 기준으로 반대편 컬럼 또는 값을 정확히 형변환 하는 것을 추천한다.
• 간혹 형변환 함수의 연산 횟수 증가로 인한 성능 저하를 우려하지만 SQL 성능은 블록 I/O 를 줄이는 것이 최우선적이다.
• 또한 형변환 함수를 생략해도 옵티마이저가 자동으로 생성하기 때문에 차이가 없다.

SQL 성능 원리를 잘 모르는 개발자는 TO_CHAR, TO_DATE, TO_NUMBER 같은 형변환 함수를 의도적으로 생략하곤 한다. 이들 함수를 생략하면 연산횟수가 줄어 성능이 더 좋지 않을까라고 생각하기 때문이다. SQL 성능은 그런 데서 결정되는 게 아니라 블록 I/O를 줄일 수 있으냐 없느냐에서 결정된다. 형변환 함수를 생략한다고 연산 횟수가 주는 것도 아니다. 앞서 확인했듯, 개발자가 형변환 함수를 생략해도 옵티마이저가 자동으로 생성한다.

 

2.3 인덱스 확장기능 사용법

 

2.3.1 Index Range Scan

2.3.2 Index Full Scan

2.3.3 Index Unique Scan

2.3.4 Index Skip Scan

2.3.5 Index Fast Full Scan

2.3.6 Index Range Scan Descending