자격증/SQLP

[SQLP 친절한 SQL 튜닝 정리] 3장- 인덱스 튜닝(부분범위 처리 구현, 인덱스 스캔 효율화)

90만식 2025. 2. 9. 11:05
SQLP를 준비하며 "친절한 sql 튜닝" 책 3장 SQL 인덱스 튜닝(부분범위 처리 구현, 인덱스 스캔 효율화) 부분을 정리한 내용 입니다.
친절한SQL튜닝 도서

3장-인덱스 튜닝

3.2 부분범위 처리 활용

3.2.1 부분범위 처리

  • DBMS는 데이터를 모두 읽어 한 번에 전송하지 않고 먼저 읽는 데이터부터 일정량을 전송하고 멈춘다.
  • 전체 쿼리 결과집합을 연속적으로 전송하지 않고 사용자로부터 Fetch Call이 있을 때마다 일정량씩 나누어 전송하는 것을 "부분범위 처리"라고 한다.

정렬조건이 있을 때 부분범위 처리

  • Order By가 있을때 DB 서버는 "모든" 데이터를 다 읽어 정렬을 마치고서야 클라이언트에게 데이터를 전송한다.
    즉, 전체범위 처리한다.
  • 부분범위 처리를 하기 위해서는 order by 컬럼이 선두 인덱스에 존재 해야 한다.
    인덱스는 항상 정렬된 상태를 유지하기 때문에 정렬된 상태의 결과 집합을 바로 전송할 수 있다.

Array Size 조정을 통한 Fetch Call 최소화

  • 대량 데이터 파일을 내려 받는다면 데이터 모두 전송해야 하므로 Array Size를 조절해서 Fetch Call 횟수를 줄일 수 있다.(전송해야 할 총량은 변하지 않는다)
  • 앞쪽 일부 데이터만 Fetch하다가 멈추는 프로그램이라면 Array Size를 작게 설정하는 것이 유리하다

 

3.2.2 부분범위 처리 구현

배치 I/O

  • 테이블 블록에 대한 디스크 I/O Call을 미뤘다가 읽을 블록이 일정량 쌓이면 한꺼번에 처리하는 기능.
  • 인덱스를 이용해서 출력하는 데이터 정렬 순서가 매번 다를 수 있다.

  • batch_table_access_by_rowid 힌트를 사용해 배치 I/O를 작동 시킬 수 있다.
  • 배치 I/O가 작동되면 실행계획에 BATCHED가 추가 된다.
  • ORDER BY가 생략된 쿼리에서 정렬 순서를 보장할 수 없으므로 비활성화 하는 경우가 종종 있다.

3.3 인덱스 스캔 효율화

LMC(Leftmost Child) 레코드

  • 루트 블록에 키 값을 갖지 않는 특별한 레코드, 자식 노드 중 가장 왼쪽 끝에 위치한 블록을 가리킨다.

선두 컬럼

  • 인덱스 구성상 "맨 앞쪽"에 있는 컬럼을 지칭

선행 컬럼

  • 어떤 컬럼보다 "상대적으로 앞쪽"에 놓인 컬럼을 지칭

인덱스 스캔 효율성 측정

  • 위 실행계획으로 스캔 비효율을 확인해보자.
  • 인덱스를 스캔하고 얻은 레코드(Rows)는 10개인데 그 과정에 7,463개 블록(cr)을 읽었다.
  • 인덱스 리프 블록 당 평균 500개 레코드가 담긴다고 가정하면 10개의 row를 읽기 위해 3,731500(=7,463*500) 레코드를 읽었다.

3.3.3 엑세스 조건과 필터조건

  • 인덱스 엑세스 조건 : 인덱스 스캔 범위를 결정하는 조건절
  • 인덱스 필터 조건 : 테이블로 엑세스할지를 결정하는 조건절
  • 인덱스를 이용하든, 테이블을 Full Scan 하든, 테이블 엑세스 단계에서 처리되는 조건절은 모두 필터 조건
  • 테이블 필터 조건은 쿼리 수행 다음 단계로 전달하거나 최종 결과집합에 포함할지를 결정
  • 첫번째 나타나는 범위검색 조건까지가 인덱스 엑세스 조건, 나머지는 필터 조건으로 이해

옵티마이저의 비용 계산 원리

비용 = 인덱스 수직적 탐색 비용 + 인덱스 수평적 탐색 비용 + 테이블 랜덤 엑세스 비용
        = 인덱스 루트와 브랜치 레벨에서 읽은 블록 수 + 
           인덱스 리프 블록을 스캔하는 과정에서 읽은 블록 수 + 
           테이블 엑세스 과정에 읽는 블록 수

 

3.3.4 비교 연산자 종류와 컬럼 순서에 따른 군집성

선행 컬럼이 모두 '=' 조건인 상태에서 첫 번째 나타나는 범위검색 조건까지만 만족하는 인덱스 레코드는 모두 연속해서 모여 있지만, 그 이하 조건까지 만족하는 레코드는 비교 연산자 종류에 상관없이 흩어진다

  • 선행 컬럼이 모두 '='이고 마지막 컬럼만 범위검색 조건(부등호, BETWEEN, LIKE)일 때 조건을 만족하는 레코드가 서로 모여 있다.(5~10번).
  • 인덱스 엑세스 조건 : C1, C2, C3, C4
  • 인덱스 필터 조건 : X

  • 세번째 컬럼 C3가 범위검색 조건인 경우 C1부터 C3까지 세 조건을 만족하는 인덱스 레코드는 서로 모여 있지만(2~12번), C4 조건까지 만족하는 레코드는 흩어지게 된다(2,3,5,6,7,11번)
  • 인덱스 엑세스 조건 : C1, C2, C3
  • 인덱스 필터 조건 : C4

  • 선두 C1컬럼이 범위검색 조건이면 C1 조건을 만족하는 레코드는 서로 모여 있고(2~19번), 나머지 조건까지 만족하는 레코드는 비교 연산자 종류에 상관없이 흩어지게 된다(5,6,7,18번)
  • 인덱스 엑세스 조건 : C1
  • 인덱스 필터 조건 : C2, C3, C4

3.3.5 인덱스 선행 컬럼이 등치(=) 조건이 아닐 때 생기는 비효율

인덱스 선행 컬럼이 모두 '=' 조건일 때 필요한 범위만 스캔하고 멈출 수 있는 것은, 조건을 만족하는 레코드가 모두 한데 모여 있기 때문이다.

  • 인덱스 구성 : 인터넷 매물 + 아파트시세코드 + 평형 + 평형타입
  • 인터넷 매물이 BETWEEN 연산자를 사용하면 나머지 조건들을 만족하는 레코드들이 인터넷 매물값(0,1,2,3)별로 뿔뿔히 흩어져 스캔 비효율이 발생한다.

 

3.3.6 BETWEEN을 IN-List로 전환

  • BETWEEN 조건을 아래와 같이 IN-List로 바꿔주면 큰 효과를 얻는 경우가 있다.
  • IN-List로 변경 되면서 수직적 탐색이 세번 발생 된다.
  • IN-List 개수만큼 UNION ALL 브랜치가 생성되고 각 브랜치마다 모든 컬럼을 '=' 조건으로 검색하므로 앞서 선두 컬럼에 BETWEEN을 사용할 때와 같은 비효율이 사라진다.

BETWEEN 조건을 IN-List로 전환할 때 주의 사항

  • IN-List 개수가 많을 경우 수직적 탐색이 많이 발생되어, BETWEEN 조건 때문에 리프 블록을 많이 스캔하는 비효율보다 IN-List 개수만큼 브랜치 블록을 반복 탐색하는 비효율이 더 클 수 있다.
  • 인덱스 스캔 과정에 선택되는 레코드들이 서로 멀리 떨어져 있을 때만 유용하다.

3.3.8 IN 조건은 '=' 인가

IN 조건이 '='이 되려면 IN-List Iterator 방식으로 풀려아한다.

IN-List Iterator방식으로 풀리지 않으면 IN 조건은 필터 조건이다.

  • 고객번호 = 1234 조건을 만족하는 레코드가 서로 멀리 떨어져 있기 때문에 IN-List 방식으로 풀리는 것이 효과적이다.
  • 상품 ID를 IN-List방식으로 풀릴 경우 상품ID, 고객번호 둘다 인덱스 엑세스 조건으로 사용된다.

  • 상품ID 조건절을 IN-List 방식으로 풀면, 인덱스를 수직적으로 세 번 탐색하는 과정에 아홉 개 블록을 읽는다.
  • 상품ID 조건절을 필터로 처리하게 되면, 고객번호만 엑세스 조건이므로 고객번호 = 123인 레코드를 모두 스캔한다.
  • 같은 고객은 한 블록(또는 연속된 두 블록)에 모여 있으므로 블록 I/O는 수직적 탐색 과정을 포함해 총 세 개(또는 네개)만 발생한다.

NUM_INDEX KEYS 힌트 활용

 

IN-List를 엑세스 조건 또는 필터 조건으로 유도하는 방법

 

1.힌트를 사용하는 방법

  • 인덱스 : 고객번호 + 상품ID
  • number_index_keys 힌트를 사용한다.
    • num_index_key(a 고객별가입상품_X1 1)
    • 세번째 인자 1은 인덱스 첫번째 컬럼까지만 엑세스 조건으로 사용하라는 의미

2.인덱스 컬럼을 가공하는 방법

 

 

3.3.9 BETWEEN과 LIKE 스캔 범위 비교

 

LIKE보다 BETWEEN을 사용하는 것이 좋다

 

  • 인덱스 : 판매월 + 판매구분
  • 판매구분으로 A,B 값만 존재하고, A 90%, B 10% 비중을 차지
  • LIKE(조건절 2)는 판매월 = '201901'인 첫 번째 레코드에서 스캔을 시작
  • BETWEEN(조건절 1)은 판매월 = '201901' 이고 판매구분 = 'B' 인 첫 번째 레코드에서 스캔을 시작

  • A 10%, B 90% 비중을 차지
  • LIKE(조건절4)는 판매월 = '201912'인 레코드를 모두 스캔하고 멈춘다.
  • BETWEEN(조건절3)은 판매월 = '201912'이고 판매구분 = 'B'인 첫 번째 레코드를 만나는 순간 스캔을 멈춘다.

 

3.3.11 다양한 옵션 조건 처리 방식의 장단점 비교

 

OR 조건 활용

OR 조건은 인덱스를 타지 않기 때문에 가급적 사용하지 않아야 한다.

 

사용해도 무방한 경우

  • 인덱스 엑세스 조건으로 사용 불가
  • 인덱스 필터 조건으로도 사용 불가
  • 테이블 필터 조건으로만 사용 가능

  • OR조건 안에 인덱스 컬럼이 포함된 조건절이 있을때는 OR-Expansion 통해 인덱스 사용이 가능

 

LIKE/BETWEEN 조건 활용

 

LIKE/BETWEEN 패턴을 사용하지 말아야 하는 경우(BETWEEN 조건은 1,2번 조건에 해당하는지만 점검)

1. 인덱스 선두 컬럼

  • 인덱스 구성 : 고객ID + 거래일자
  • 고객ID에 값을 입력하지 않으면, 인덱스에서 '모든'거래 데이터를 스캔하면서 거래일자를 필터링 하는 비효율이 발생

2. NULL 허용 컬럼

  • NULL허용 컬럼에 LIKE 문을 사용할 경우 변수에 NULL을 입력하면 LIKE '%'와 같은 형태가 되어, 결과 집합에 NULL 값이 누락 된다.(BETWEEN도 동일)

3. 숫자형 컬럼

  • 숫자형이면서 인덱스 엑세스 조건으로 사용 가능한 컬럼에 대해 LIKE 방식을 사용하면 안된다.
  • LIKE 문을 만나면서 인덱스 컬럼이 문자형으로 자동 형변환 되므로 필터 조건으로 작동 된다.

4. 가변 길이 컬럼

  • 가변 길이 컬럼에 LIKE 문을 사용하면 결과 집합이 달라질 수 있다.
  • 고객명 컬럼에 김훈, 김훈남이 존재했을때 LIKE :cust_nm || '%'이라고 가정하자.
  • cust_nm의 "김훈"값이 들어가면 김훈, 김훈남 두 고객이 같이 조회 된다.

 

UNION ALL 활용

SELECT * FROM 거래
WHERE :cust_id is null
AND 거래일자 BETWEEN :dt1 AND :dt2
UNION ALL
SELECT * FROM 거래
WHERE :cust_id is not null
AND 고객ID = :cust_id
AND 거래일자 BETWEEN :dt1 and :dt2
  • :cust_id 변수에 값을 입력했는지에 따라 위,아래 SQL중 하나만 실행
  • 값을 입력하지 않았을 경우 위쪽 SQL(UNION ALL 기준)타게 되고, 값을 입력했을 경우 아래 SQL을 타게 된다.

NVL/DECODE 함수 활용

SELECT * FROM 거래
WHERE 고객ID = NVL(:cust_id, 고객ID)
AND 거래일자 BETWEEN :dt1 AND :dt2
;
SELECT * FROM 거래
WHERE 고객ID = DECODE(:cust_id, NULL, 고객ID, :cust_id)
AND 거래일자 BETWEEN :dt1 AND :dt2

  • :cust_id에 값을 입력하면 [고객ID+거래일자] 인덱스를 사용하고, 입력하지 않으면 [거래일자] 인덱스를 사용한다.
  • NVL, DECODE의  실행계획은 같다.
  • 단 NULL허용 컬럼에 사용 하면 NULL 인 레코드가 결과에 누락된다.

3.3.12 함수호출부하 해소를 위한 인덱스 구성

 

PL/SQL 사용자 정의 함수가 느린 이유

  1. 가상머신(VM) 상에서 실행되는 인터프리터 언어
  2. 호출 시마다 컨텍스트 스위칭 발생
  3. 내장 SQL에 대한 Recursive Call 발생

효과적인 인덱스 구성을 통해 함수호출을 최소화 해야 한다.

 

3.4 인덱스 설계

 

결합 인덱스 구성할 때의  선두 컬럼의 기준

  1. 조건절에 항상 사용하거나, 자주 사용하는 컬럼을 선정
  2. '=' 조건으로 자주 조회되는 컬럼을 앞쪽에 둔다.

인덱스 스캔 효율성 외 판단 기준

  • 수행 빈도
  • 업무상 중요도
  • 클러스터링 팩터
  • 데이터량
  • DML부하(= 기존 인덱스 개수, 초당 DML 발생량, 자주 갱신하는 컬럼 포함 여부 등)
  • 저장 공간
  • 인덱스 관리 비용 등

소트 연산 생략 인덱스 구성 공식

  1. '=' 연산자로 사용한 조건절 컬럼 선정
  2. ORDER BY 절에 기술한 컬럼 추가
  3. '=' 연산자가 아닌 조건절 컬럼은 데이터 분포를 고려해 추가 여부 결정

소트 연산에서 IN-List Iterator 방식

  • IN-List Iterator방식에 ORDER BY이 들어가 있으면 소트 연산이 생략되지 않는다.
  • UNION ALL로 풀릴 경우 위쪽 브랜치를 실행 하고, 아래쪽을 실행 했을 때 그 결과가 정렬되어야 하는데 불가능 하기 때문.