자격증/SQLP
[SQLP 친절한 SQL 튜닝 정리] 2장- 인덱스 구조 및 탐색
90만식
2025. 2. 9. 10:19
728x90
SQLP를 준비하며 "친절한 sql 튜닝" 책 2장 인덱스 구조 및 탐색 부분을 정리한 내용 입니다.
친절한SQL튜닝 도서
2장-인덱스 기본
2.1 인덱스 구조 및 탐색
인덱스 탐색 과정
- 인덱스 탐색과정은 수직적, 수평적 탐색 2가지로 이루어져 있음.
- 수직적탐색
- 인덱스 스캔 시작점을 찾는 과정으로 루트 > 브랜치 > 리프 블록까지 수직적으로 탐색 한다.
- 수평적 탐색
- 데이터를 찾는 과정으로 수직적 탐색을 통해 찾은 시작점으로, 찾고자 하는 데이터가 더 나오지 않을때까지 인덱스 리프 브록을 수평적으로 스캔 한다.
인덱스 튜닝의 핵심요소
- 인덱스 튜닝의 핵심요소는 인덱스 스캔 효율화 튜닝, 랜덤 엑세스 최소화 튜닝으로 분류 된다.
- 인덱스 스캔 효율화 튜닝
- 이름과 시력 순으로 정렬 했을 때 3건의 스캔이 발생하며, 시력과 이름순으로 정렬 했을때 6번의 스캔이 발생한다.
- 랜덤 엑세스 최소화 튜닝(테이블 엑세스 최소화)
- 테이블의 엑세스 횟수를 줄이도록 튜닝하는 것
- SQL 튜닝은 랜덤 I/O와의 전쟁
- 예시
- 학생명부에 시력이 1.0~1.5인 홍길동 학생을 찾는 경우
- 시력이 1.0~1.5인 학생은 50명, 이름이 홍길동인 학생은 5명, 시력이 1.0~1.5인 홍길동 학생은 2명
- 이름, 시력 만으로 정렬된 인덱스가 있다면 둘중 어느쪽이 효율적인가
- 시력순으로 정렬한 인덱스 사용 시 교실을 50번 찾아간다.
- 이름으로 정렬된 인덱스 사용 시 교실을 5번만 찾아가면 된다.
- 어떤 인덱스를 사용하느냐에 따라 교실 방문 횟수가 다름
- 인덱스 스캔 효율화 < 랜덤 엑세스
- 인덱스 스캔과정에도 비효율이 있지만, 정보를 얻기 위해 직접 테이블을 찾아가는 부담이 더 크다.
2.1.2 인덱스 구조
인덱스는 대용량 테이블에서 필요한 데이터만 빠르고 효율적으로 엑세스 하기 위해 사용하는 오브젝트로 책의 색인 같은 역할이다.
B*Tree
- DBMS는 일반적으로 B*Tree 인덱스를 사용
- 나무(Tree)를 거꾸로 뒤집은 모양으로 뿌리(루트, Root)가 위쪽에 있고, 가지(브랜치, Branch)를 거쳐 맨 앞에 잎사귀(리프, Leaf)가 있다.
- LMC : 자식 노드 중 가장 왼쪽에 위치한 블록으로 루트, 브랜치 블록에서 키 값을 갖지 않는 특별한 레코드
인덱스 리프블록에서 테이블 레코드를 찾아가는 방법
- 수평적 탐색을 통해 얻은 ROWID(데이터 블록주소+로우번호)를 통해 테이블 레코드를 찾아갈 수 있음
- 데이터 블록 주소 = 데이터 파일 번호 + 블록 번호
- 로우 번호 : 블록 내 순번
- 블록 번호 : 블록 내 순번
- ROWID = 데이터 파일 번호 + 블록 번호 + 로우번호
2.1.3 인덱스 수직적 탐색
- 인덱스 스캔 시작지점을 찾는 과정
- 시작점을 어떻게 찾을까?
- 루트, 브런치 블록에 저장된 각 인덱스 레코드는 하위 블록에 대한 주소값을 가지고 있음
- 수직적 탐색 과정에 찾고자 하는 값보다 크거나 같은 값을 만나면, 직전 레코드가 가르키는 하위 블록에 대한 주소값으로 하위 블록으로 이동하게 됨
2.1.4 인덱스 수평적 탐색
- 수직적 탐색으로 찾은 리프 블록에서 찾고자 하는 데이터가 더 이상 나오지 않을때까지 수평적으로 스캔
- 수평적으로 스캔이 가능한 이유
- 인덱스 리프 블록 끼리는 서로 앞 뒤 블록에 대한 주소값을 가지고 있다 (양방향 연결 리스트 구조)
2.1.5 결합 인덱스 구조와 탐색
- 결합 인덱스는 인덱스를 어떻게 구성하던 블록I/O 개수가 동일하다. 즉 성능이 같다.
2.1 인덱스 구조 및 탐색
2.2.1 인덱스를 사용 한다는 것
- 인덱스 리프 블록 스캔 시작점을 찾아(수직적 탐색) 찾는 데이터가 나오지 않을 때 까지 스캔하다 멈추는것(수평적 탐색)
- 위 그림에서 '글로벌'로 시작하는 단어를 쉽게 찾을 수 있는 이유는 가나다 순으로 정렬 되어 있기 때문. 즉 우리가 찾고자 하는 단어가 모여있기 때문이다.
- 4~6번째 문자가 '테이블' 과 같이 단어가 가공되거나 중간 값을 찾을 때는 색인 전체를 스캔해야 찾을 수 있다.
2.2.2 인덱스 Range Scan을 할 수 없는 경우
- 인덱스 컬럼을 가공할 경우
- 일정 범위를 스캔 하려면 '시작점'과 '끝지점'이 필요한데, 컬럼을 가공하면 시작점을 찾을 수 없기 때문
인덱스 컬럼을 가공했을 경우
인덱스 컬럼을 가공하지 않았을 경우
- ex ) 2007년 1월에 태어난 학생을 찾는 경우
- 위 인덱스 컬럼을 가공하지 않았을 경우 그림을 보면 2027년 1월1일 이후 태어난 첫번째 학생을(스캔 시작점) 찾고 2007년 2월 1일 이후에 태어난 첫번째 학생(스캔 끝지점)을 찾는 순간 멈춘다.
- SQL로 표현해보면 WHERE 생년월일 BETWEEN '20070101' AND '20070131'
- ex) 년도와 상관 없이 5월에 태어난 학생을 찾는 경우
- 위 인덱스 컬럼을 가공했을 경우 그림을 보면 스캔 시작점과 끝지점을 알 수 없기 때문에 전체 스캔 하게 된다.
- SQL로 표현해 보면 WHERE SUBSTR(생년월일, 5,2) = '05'
- ex ) 2007년 1월에 태어난 학생을 찾는 경우
- OR 조건 같은 경우 옵티마이저가 판단 해 UNION ALL 형태로 변환되면 Range Scan 가능하다(OR EXPANSION)
- 힌트로 표현하면 /*+ USE_CONCAT */
OR EXPANSION - IN 조건 같은 경우 옵티마이저가 판단 해 UNION ALL 형태로 변환 되면 Range Scan이 가능하다(In-List Iterator)
- 일정 범위를 스캔 하려면 '시작점'과 '끝지점'이 필요한데, 컬럼을 가공하면 시작점을 찾을 수 없기 때문
2.2.3 더 중요한 인덱스
인덱스 Range Scan의 첫 번째 조건은 인덱스 선두 컬럼이 조건절에 가공하지 않은 상태로 있어야 한다.
SELECT
사원번호
,소속팀
,연령
,입사일자
,전화번호
FROM 사원
WHERE 사원명 = '홍길동'- 인덱스 구성이 [소속팀 + 사원명 + 연령] 이고 쿼리가 위와 같을 경우
- 사원명이 같은 사원이라도 소속팀이 다르면 멀리 떨어지기 때문에 인덱스 시작점, 끝지점을 찾을 수 없다.
- 그렇기 때문에 정상적으로 Range Scan이 불가능하고 리프 블록을 Full Scan 하게 된다.
- 위 그림에서 과세구분 컬럼은 가공되었다. 그런데 어떻게 Range Scan이 가능했을까?
- 선두 컬럼인 기준연도가 가공 되지 않았기 때문에 Index Ragne Scan이 가능하다. 하지만 Index Range Scan을 한다고 해서 성능이 항상 좋은건 아니다.
SELECT *
FROM 주문상품
WHERE 주문일자 = :ord_dt
AND 상품번호 LIKE '%PING%';
SELECT *
FROM 주문상품
WHERE 주문일자 = :ord_dt
AND SUBSTR(상품번호, 1, 4) = 'PING';- [주문일자 + 상품순]으로 인덱스가 구성 되어 있고, 주문 상품 데이터량이 하루 평균 100만건이라면
- 위 두 쿼리의 조건절을 처리할 때, 선투컬럼이 가공되지 않았기 때문에 Index RangeScan이 가능하지만 인덱스에서 스캔하는 데이터량은 주문일자 조건을 만족하는 100만건이다.
2.2.4 인덱스를 이용한 소트 연산
인덱스는 데이터가 정렬되어 있기 때문에 Sort 연산 생략이 가능하다.
SELECT *
FROM 상태변경이력
WHERE 장비번호 = 'C'
AND 변경일자 = '20180316'
ORDER BY 변경순번- [장비번호 + 변경일자 + 변경순번]으로 인덱스 구성
- 위 쿼리를 실행하면 실행계획에 SORT(ORDER BY)가 생략 된다.
- 인덱스 리프 블록은 양방향 연결 리스트 구조이기 때문에 오름차순/내림차순 둘다 Sort 생략 가능
- 오름차순(Asc) 정렬일 때는 조건을 만족하는 가장 작은 값을 찾아 좌측으로 수직적 탐색 후 우측으로 수평적 탐색
- 내림차순(Desc) 정렬일 때는 조건을 만족하는 가장 큰 값을 찾아 우측으로 수직적 탐색한 후 좌측으로 수평적 탐색
2.2.5 ORDER BY 절에서 컬럼 가공
SELECT
*
FROM (
SELECT TO_CHAR(A.주문번호, 'FM000000') AS 주문번호, A.업체번호, A.주문금액
FROM 주문 A
WHERE A.주문일자 = :DT
AND A.주문번호 > NVL(:next_ord_no, 0)
ORDER BY 주문번호
) WHERE ROWNUM <= 30
- 위 쿼리에서 SORT ORDER BY가 생략되지 않은 이유는 무엇일까.
- [주문일자+주문번호]로 인덱스 구성
- ORDER BY 절의 주문번호는 주문 테이블의 A.주문번호가 아닌 TO_CHAR(A.주문번호, 'FM000000')로 가공된 ALIAS의 주문번호이기 때문
- 아래 쿼리처럼 주문테이블의 주문번호를 명시 해주면 된다.
SELECT
*
FROM (
SELECT TO_CHAR(A.주문번호, 'FM000000') AS 주문번호, A.업체번호, A.주문금액
FROM 주문 A
WHERE A.주문일자 = :DT
AND A.주문번호 > NVL(:next_ord_no, 0)
ORDER BY A.주문번호
) WHERE ROWNUM <= 30
2.2.6 SELECT-LIST에서 컬럼 가공
인덱스를 이용하면 옵티마이저는 정렬 연산 없이 최소/최대 값을 빠르게 찾을 수 있다.
SELECT MIN(변경순번)
FROM 상태변경이력
WHERE 장비번호 = 'C'
AND 변경일자 = '20180316'
SELECT MAX(변경순번)
FROM 상태변경이력
WHERE 장비번호 = 'C'
AND 변경일자 = '20180316'- [장비번호 + 변경일자 + 변경순번]으로 인덱스 구성
- 수직적 탐색을 통해 조건을 만족하는 가장 왼쪽/오른쪽 지점으로 내려가서 첫번째 읽는 레코드가 최소/최대 값이기 때문에 정렬연산을 수행하지 않는다.
- 정렬 연산이 생략된 실행계획은 아래와 같다.
- 인덱스 리프블록의 왼쪽(MIN)/오른쪽(MAX)에서 레코드 하나(FIRST ROW)만 읽고 멈춘다.
2.2.7 자동 형변환
오라클은 컬럼의 형태에 따라 자동 형변환이 일어날 수 있다.
-- 숫자형과 문자형이 만났을 경우
-- 생년월일은 문자형일 경우
-- TO_NUMBER(생년월일) = 19821225로 옵티마이저가 자동 형변환 한다.
SELECT *
FROM 고객
WHERE 생년월일 = 19821225
-- LIKE문에서 숫자형과 문자형이 만났을 경우
-- 고객번호가 숫자형일 경우
-- LIKE 자체가 문자열 비교 연산자이므로 문자형 기준으로 숫자형 컬럼이 변환
-- TO_CHAR(고객번호) LIKE '%9410%'으로 옵티마이저가 자동 형변환 한다.
SELECT
*
FROM 고객
WHERE 고객번호 LIKE '%9410%'- 숫자형과 문자형이 만나면 숫자형으로 형변환 된다.
- LIKE문 자체가 문자열 비교 연산자이기 때문에 문자형으로 형변환(예외 케이스)
- 날짜형과 문자형이 만나면 날짜형으로 형변환 된다.
728x90