[친절한 SQL 튜닝 정리] 2장. 인덱스 기본

SQLP 준비하면서 친절한 SQL 튜닝을 읽고 정리한 포스팅입니다.

2장 인덱스 기본을 학습하고 정리하였습니다.

 

[목차]

1. 인덱스 구조 및 탐색
2. 인덱스 기본 사용법
3. 인덱스 확장기능 사용법

 

[이전 포스팅]

1. [친절한 SQL 튜닝 정리] 1장. SQL 처리 과정과 I/O

---

1. 인덱스 구조 및 탐색

 

1) 미리 보는 인덱스 튜닝

• 학생명부 예시
  • 이름/학년-반-번호로 구성
  • 이게 인덱스
  • 학년-반-번호가 ROWID 역할
• DB 테이블에서 데이터 찾는 법
  • 테이블 전체 스캔
  • 인덱스 이용
• 인덱스는 큰 테이블에서 소량데이터 검색시 유용(OLTP 시스템)
• 인덱스 튜닝 핵심 방법
  • 인덱스 스캔 효율화 튜닝
  • 테이블 엑세스 횟수 줄이기(인덱스 랜덤I/O)
• 랜덤I/O를 줄이는 것이 핵심이며 파티션, IOT, 클러스터 등 I/O를 줄이기 위한 노력들

 

2) 인덱스 구조

• 인덱스는 책 뒤 색인과 같은 역할
• 범위스캔(Range Scan)이 가능한 이유는 인덱스(색인)이 정렬되어 있기 때문
• DBMS는 B*Tree 인덱스를 기본적으로 사용
  • 루트 블록,브랜치 블록,리프 블록 구성
  • 루트, 브랜치에 키값 안 갖는 특별한 레코드: LMC(Leftmost Child)
    • 자식 노드 중 가장 왼쪽 끝에 위치한 블록 가리킴
  • 리프 블록에 저장된 레코드는 키값 순으로 정렬 + 테이블레코드를 가리키는 rowid 주소값 가짐
  • 인덱스는 키값 같으면 rowid 순 정렬
• ROWID = 테이블 블록 주소(데이터 파일 번호 + 블록 번호) + 로우 번호
• 인덱스 탐색 과정
  • 수직적 탐색(인덱스 스캔 시작지점을 찾는 과정)
  • 수평적 탐색(데이터를 찾는 과정)

 

3) 인덱스 수직적 탐색

• 정렬된 인덱스 레코드 중 조건 만족하는 '첫번째' 레코드를 찾는 과정
• 인덱스 스캔 시작지점 찾는 과정
• 루트(Root) 블록에서 시작
  • '홍길동'을 검색할 때, 루트블록에 홍길동과 같거나 큰 값이 없으면 이전 레코드(박길동)이 가리키는 하위블록(>=박) 이동
  • 박길동이 포함된 브랜치 블록에서 '홍길구'보다 큰 홍달동 찾으면 그 이전 레코드(홍길구)에 연결된 리프블록 찾아가서 첫번째 엑세스 조건 확인 가능
• 주의점
  • 루트 블록, 브랜치 블록에는 실제 데이터저장 안 됨(키값과 포인터만 갖고 있음)
  • LMC를 제외하고 큰 값만 찾아가는 방향

 

4) 인덱스 수평적 탐색

• 수직적 탐색으로 스캔 시작점 찾았으면 수평적 탐색으로 찾고자 하는 모든 데이터를 찾는 과정
• 양방향 링크드 리스트 구조라서 좌,우 수평 탐색 가능
• 일반적으로 인덱스 엑세스 후 테이블 엑세스도 하기 때문에 리프 블록에서 데이터와 함께 ROWID도 찾아야 함

 

5) 결합 인덱스 구조와 탐색

• 두개 이상 컬럼 결합하여 인덱스 만들 수 있음
• 인덱스 구성(성별,고객명) / 성별='남' 고객명='홍길동'을 찾는다면?
  • 선별='남'을 먼저 찾은 후 고객명='홍길동'을 찾는 것이 아님
  • 수직적 탐색으로 찾은 인덱스 스캔 시작점은 성별='남'인 첫번째 레코드가 아닌 성별='남' & 고객명='이재희' 레코드
  • 인덱스를 (고객명,성별)로 하더라도 읽는 인덱스 블록 개수 동일(블록I/O개수가 동일하므로 성능도 동일)
  • 엑셀 필터처럼 동작하는 것이 아님 

 

2. 인덱스 기본 사용법

 

1) 인덱스를 사용한다는 것

• 기본은 Range Scan을 한다는 것
• 책의 색인
• 가공한 값이나 중간값으로는 스캔 시작점을 찾을 수 없음
  • 색인을 쓸 수도 있으나 시작점을 모르기때문에 range scan은 안 됨 (색인 전체 스캔)
• 인덱스 컬럼 중 선두 컬럼을 가공하지 않아야 Range Scan이 가능

 

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

• 초등학생 생년월일(yyyymmdd)순으로 정렬했다고 가정
  • 년도로 찾으면 시작점을 찾을 수 있으나, 월로 찾으면 시작점을 찾을 수 없기 때문에 전체 스캔 필요
• OR 조건 역시 Range Scan 불가능하지만 옵티마이저에 의해 UNION ALL로 쪼개지고 각 조건에 선두컬럼이 인덱스 걸려있으면 Range Scan 가능(OR Expansion)
  • use_concat 힌트를 이용해서 가능(실행계획에 CONCATENATION)
• IN 조건도 옵티마이저에 의해 쪼개져서 IN-List Iterator 방식으로 사용되어 Range Scan이 가능할 수 있음
  • 실행계획에 INLIST_ITERATOR

 

3) 더 중요한 인덱스 사용 조건

• 인덱스 선두 컬럼이 조건절에 있어야 Range Scan
  • 중간컬럼은 가공되어도 됨
• but, 인덱스 Range Scan이 항상 성능 좋은 것은 아님
• 주문일자, 상품번호 인덱스일 때, 매일 100만건 데이터 쌓인다고 가정
  • 상품번호를 like로 찾거나 가공하면 스캔 범위 줄이는 역할 못함
  • 주문일자로 100만건 조회되므로 효율성에서 문제 가능

 

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

• 인덱스가 정렬되어 있기 때문에 소트연산생략 효과 얻을 수 있음
• 고객테이블에 고객명, 성별, 고객전화번호로 구성된 인덱스가 있다고 가정
  • 고객명, 성별이 조건이고 고객전화번호를 ORDER BY에 사용하면?
  • 고객명, 성별조건으로 검색하면 고객전화번호는 자동정렬이므로 ORDER BY 생략 가능
  • 내림차순도 자동으로 생략 가능(리프블록의 양방향 연결 리스트)

 

5) ORDER BY 절에서 컬럼 가공

• ORDER BY, SELECT-LIST에서 컬럼 가공으로 인덱스 정상 사용할 수 없는 경우 존재
• ORDER BY에 인덱스에 포함된 컬럼 2개가 포함되었다고 가정
  • ORDER BY 컬럼1, 컬럼2 (인덱스 정상 사용 가능)
  • ORDER BY 컬럼1 | 컬럼2 (가공되었으므로 정상 사용 불가능)

 

6) SELECT-LIST에서 컬럼 가공

• 고객테이블 인덱스 고객명,성별,고객전화번호 구성일 경우
• 아래 테이블 MIN, MAX 쿼리 정렬 수행 안 함(리프노드에서 가장 왼쪽, 오른쪽만 찾으면 되기 때문)

/* 실행 계획 FIRST ROW, INDEX RANGE SCAN(MIN/MAX) 확인 가능 */

SELECT MIN(고객전화번호)
FROM 고객
WHERE 고객명 = '홍길동';

SELECT MAX(고객전화번호)
FROM 고객
WHERE 고객명 = '홍길동';

 

7) 자동 형변환

• 생년월일이 선두 컬럼인 인덱스가 있을 때, 생년월일컬럼이 문자형인데 숫자값으로 검색을 한다면?
  • 문자형 vs 숫자형은 숫자형이 이기기 때문에 문자형 컬럼이 TO_NUMBER로 가공되어 인덱스 사용 안 됨
• 동일하게 날짜형과 문자형이 만나면 날짜형이 이김
• 문자형 vs 숫자형은 기본적으로 숫자형이 이기지만 LIKE '문자열' 비교 연산 사용시에는 숫자형컬럼이 문자형으로 변환됨
  • 숫자형 컬럼을 LIKE 조건으로 검색하면 자동형변환이 발생하므로 주의
• 또한 문자형,숫자형 컬럼 비교시 숫자형 컬럼에 문자값이 포함될 경우, 쿼리 수행 오류도 발생 가능
  • where number_column = varchar_column; --수치 부적합 오류
• 결론?
  • TO_CHAR, TO_DATE 등을 생략한다고 성능향상에 큰 도움 안 됨
  • 성능에는 블록 I/O 줄이는 것이 중요하기 때문
  • 인덱스를 안 타면 결국 블록 I/O 횟수가 늘기 때문에 정확한 형변환 필요

 

 

3. 인덱스 확장 기능 사용법

• index range scan
  • 가장 기본적인 인덱스 수행 방식
  • 루트 -> 리프까지 수직적 탐색 후 필요 범위(range) 스캔
  • 선두컬럼 가공 안 해야 함
• index full scan
  • 데이터 검색을 위한 최적 인덱스가 없을 때 차선 선택
  • 선두컬럼이 조건절에 없을 경우 range scan 불가능하나, 뒷쪽 컬럼이 인덱스에 있을 경우 선택
  • 옵티마이저는 이럴 경우 Table Full Scan부터 고려. 근데 대용량 테이블이라면 인덱스 전략 다시 고려
  • 해당 방식도 소팅생략 가능
  • 옵티마이저가 Table Full Scan이 더 효율적인데 Index Full Scan을 선택할 경우
    • 힌트로 first_rows를 줬다면?
    • 처음일부를 빠르게 찾을 수 있기 때문에 Index Full Scan 채택 가능(부붐범위 처리)
    • first_rows: 처음 n개 로우를 빠르게 가져오는 쪽으로 실행계획 최적화
•  index unique scan
  • 수직적 탐색만으로 데이터 찾는 방식
  • pk 찾기. 1건만 찾으면 되므로 수직적 탐색으로 1건 찾으면 끝
• index skip scan
  • 인덱스 선두 컬럼 조건절에 사용 안 하면
    • Table Full Scan 또는 Index Full Scan 고려
  • 9i 버전부터 인덱스 선두컬럼이 조건절에 없어도 인덱스 활용하는 새로운 스캔 방식이 index skip scan
  • 조건절에 빠진 인덱스 선두컬럼의 DV(Distinct Value) 개수가 적고 후행 컬럼의 DV 개수가 많을 때 유용
    • 선두컬럼 성별, 뒷컬럼 고객번호
  • 루트, 브랜치 블록에서 읽은 컬럼 값 정보를 이용해 조건절에 부함하는 레코드를 포함할 '가능성 있는' 리프 블록만 골라 엑세스하는 방식
  • 중간컬럼에 대한 조건절이 없거나 선두컬럼이 like, between 등 범위검색 조건일 때도 사용 가능
• index fast full scan
  • index full scan보다는 빠름
  • 논리적인 인덱스 트리 구조 무시하고 인덱스 세그먼트 전체를 멀티블록 I/O방식으로 스캔
  • 물리적으로 디스크에 저장된 순서대로 인덱스 리프블록들을 읽음
  • 멀티블록 I/O라서 대량 인덱스 블록 읽을때 효과
  • 리프 노드 연결리스트 구조 무시하고 읽어서 인덱스 키순서대로 정렬 안 됨
  • 인덱스가 파티션 되어있지 않더라도 병렬 쿼리가 가능
    • 병렬 쿼리시 Direct Path I/O 방식으로 더 빠름
  • 관련힌트 index_ffs, no_index_ffs
• index range scan descending
  • index range scan과 기본적으로 동일(뒤에서부터 스캔)
  • index_desc로 유도 가능

 

---

 

📖 출처: 『친절한 SQLP튜닝』, 조시형 저, DBian, 2025