SQL Tuning 3
SQL 튜닝
인덱스 튜닝
테이블 액세스 최소화
인덱스 ROWID는 논리적 주소.
디스크 상에서 테이블 레코드를 찾아가기 위한 위치 정보를 담는다.
블록을 읽을때 버퍼캐시 탐색-> 해시 체인 탐색-> 버퍼 헤더 탐색-> 버퍼 블록 방문
버퍼에 데이터가 없을 경우 디스크에서 메모리에 적재
매번 DBA해싱과 래치 획득과정을 반복하며, 동시 액세스를 심할 때는 캐시버퍼 체인 래치와 버퍼 LOCK에 대한 경합까지 발생.
디스크 DB를 ROWID를 우편주소에, 메인 메모리 DB가 사용하는 포인터를 전화번호에 비유할 수 있다.
하나의 레코드를 찾아가는 데 있어 가장 빠르다고 알려진 ROWID에 의한 테이블 액세스가 고비용 연산임을 이해하자.
클러스터링 팩터(CF) : 특정 컬럼 기준으로 같은 값을 갖는 데이터가 서로 모여있는 정도.
인덱스 클러스터링 팩터가 높을 수록 인덱스 레코드 정렬 순서와 테이블 레코드 정렬순서가 일치한다.
버퍼 pinning: 테이블 블록에 대한 포인터를 바로 해제하지 않고 일단 유지하는것.
인덱스를 이용한 테이블 액세스가 풀스캔 보다 느려지게 하는 핵심적인 이유
-
테이블 풀 스캔은 시퀀셜 액세스, 인덱스 이용시 랜덤 액세스
-
테이블 풀 스캔은 멀티블록 I/O, 인덱스 이용시 싱글I/O
만 건만 넘어도 테이블 풀 스캔 방식이 더 빠를 수 있다.
대량데이터를 읽고 갱신하는 배치 프로그램은 인덱스와 NL조인 보다 Full Scan, 해시 조인이 유리하다.
성능 측면에서 보았을 때, 테이블을 파티셔닝하는 이유는 결국 Full Scan을 빠르게 처리하기 위해서다.
인덱스만 읽어서 처리하는 Covered 쿼리
그 쿼리가 사용한 인덱스를 Covered 인덱스
인덱스 구조 테이블
- Oracle: IOT(Index-Organized Table)
- MS-SQL: 클러스터형 인덱스
Create 문 맨 뒤에 organization Index 명시
IOT는 인위적으로 CF를 좋게 만듬.
넓은 범위로 읽을 때 유리. 데이터 입력과 조회패턴이 서로 다른 테이블에도 유용.
부분범위 처리 활용
부분 범위 처리: 전체 쿼리 결과집합을 쉼 없이 연속적으로 전송하지 않고 사용자로부터 Fetch Call이 있을 때마다 일정량씩 나누어 전송하는 것.
정렬 조건이 있을 때,
정렬 컬럼 인덱스가 있다면 부분범위 처리가 가능하지만,
그렇지 않다면 모든 데이터를 읽어 Sort Area, Temp 테이블 스페이스까지 이용해 데이터 정렬까지 마치고 나서야 데이터 전송이 가능하다.
대량 데이터를 파일로 내려받는다면 Array size를 최대한 크게
앞쪽 일부 데이터만 Fetch 하다가 멈추는 프로그램이라면 Array Size를 작게 설정하는 것이 유리.
(불필요하게 많은 데이터를 전송하고 버리는 비효율을 줄일 수 있음.)
부분범위 처리 문제는 앞쪽 일부만 출력하고 멈출 수 있는가이다.
배치I/O기능 : 테이블 블록에 대한 디스크 I/O Call을 미뤘다가 일정량 쌓이면 한꺼번에 처리.
배치 I/O기능이 작동하면 인덱스를 이용해서 출력하는 데이터 정렬 순서가 매번 다를 수 있다.
원하는 정렬 순서를 보장하려면 ORDER BY를 생략하지 말아야한다.
(혹은, 배치I/O기능을 끄거나…)
인덱스 스캔 효율화
IOT, 클러스터, 파티션은 테이블 랜덤 액세스를 최소화 하는 데 매우 효과적이지만, 운영 환경에 적용하려면 성능 검증을 위한 테스트가 필수적이며 어려움이 따름.
인덱스 선행컬럼이 조건 절에 없거나 ‘=’조건이 아니면 인덱스 스캔 과정에 비효율이 발생한다.
- 인덱스 액세스 조건: 인덱스 스캔 범위를 결정하는 조건절
- 인덱스 필터 조건: 테이블로 액세스할지를 결정하는 조건절
- 테이블 필터 조건 : 쿼리 수행 다음 단계로 전달하거나 최종 결과 집합에 포함할지를 결정
인덱스를 이용한 테이블 액세스 비용 =
인덱스 수직적 탐색 비용 + 인덱스 수평적 탐색 비용 + 테이블 랜덤액세스 비용
선행컬럼이 모두 ‘=’조건인 상태에서 첫번째 나타나는 범위 검색 조건까지만 만족하는 인덱스 레코드는 모두 연속해서 모여 있지만, 그 이하 조건까지 만족하는 레코드는 비교 연산자 종류에 상관 없이 흩어진다.
그러면 흩어진 조건부터 인덱스 필터조건이 된다.
- 좌변 컬럼을 가공한 조건절
- 왼쪽 % 또는 양쪽 % 기호를 사용한 Like 조건절
- 같은 컬럼에 대한 조건절이 두 개 이상일 때, 인덱스 액세스 조건으로 선택되지 못한 조건절
- OR EXPANSION 또는 INLIST ITERATOR로 선택되지 못한 OR 또는 IN 조건절
=> 인덱스 스캔 범위를 줄이는데 큰 기여를 못함. 혹은 인덱스를 사용하지 않을 수도 있음.
INLIST ITERATOR 유도시 쿼리를 조건절 값 별로 쪼개서 UNIONALL시키는 효과가 있음.
조건절 값이 적다는 전제하에 좋음. 또, 찾는 값이 인덱스 수평 거리로 멀리 떨어져 있어야 좋음. 아니라면 BETWEEN을 INLIST로 바꾸는 것은 곤란.
->조인문이나 서브 쿼리로 구현.
INLIST보다 INDEX SKIP SCAN(index_ss)을 우선적으로 고려하는 것도 좋음.
IN != ‘=’
IN은 IN-LIST ITERATOR 방식으로 풀려야만 ‘=’와 동등해짐. 하지만 옵티마이저의 판단하에 위의 방식으로 풀리지 않고, 필터 조건이 되기도 함.
쿼리가 찾는 값이 인덱스 리프블록에서 특히, 한 블록에 뭉쳐 있다면, INLIST방식이 아닌 단순 필터방식으로 실행하는게 더 나아 보임.
즉, IN조건은 INLIST로 풀어내는게 항상 유용하지 않다.
LIKE보다 BETWEEN이 더 낫다.
범위검색 조건의 남용은 인덱스 스캔 효율을 고려할 때 좋지 못하다.
OR조건
- 인덱스 액세스 조건으로 사용 불가
- 인덱스 필터 조건으로도 사용 불가
- 테이블 필터 조건으로만 사용 가능 => OR조건을 이용한 옵션 조건 처리는 가급적 사용하지 않아야 한다.
단, 다음과 같은 형태는 사용 가능
~
(조건1 AND 조건2)
OR
(조건3 AND 조건4)
OR-EXPANSION을 통해 인덱스 사용이 가능하기 때문.
LIKE/BETWEEN 조건 4가지 경우 필수 점검
- 인덱스 선두 컬럼
- NULL 허용 컬럼
- 숫자형 컬럼
- 가변길이 컬럼
인덱스 설계
인덱스가 많으면 생기는 문제점
- DML 성능 저하(TPS 저하)
- 데이터베이스 사이즈 증가
- 데이터베이스 관리 및 운영 비용 상승
운영중인 데이터베이스에 인덱스 구조 변경은 매우 어려움.
기존의 SQL에 대한 영향성 평가가 필요.
인덱스 구성시 중요 기준
- 조건절에 항상 사용하거나, 자주 사용하는 컬럼 선정
- ’=’ 조건으로 자주 조회하는 컬럼을 선두 컬럼으로 선정
이외에 수행 빈도, 업무상 중요도, 클러스터링 팩터, 데이터량 등 고려.
NL 조인시 Inner 테이블의 인덱스가 비효율적이라면, 조인과정에서 비효율이 매우 크게 증가함.
일자 조건이 선두 컬럼에 올 수 있는 이유
- 일자 조회구간이 길지 않으면, 인덱스 스캔 비효율이 성능에 미치는 영향은 크지 않다는 점
- 인덱스 스캔 효율보다 테이블 액세스가 더 큰 부하요소라는 점
인덱스 생성 여부를 결정할 때는 선택도가 매우 중요하지만, 컬럼 간 순서를 결정할 때는 각 컬럼의 선택도보다 필수 조건 여부, 연산자 형태 가 더 중요한 판단 기준이다.