친절한 SQL 튜닝 3장 정리

테이블 액세스 최소화

SQL튜닝은 랜덤 I/O와의 전쟁이다.

인덱스를 스캔하는 이유는, 검색 조건을 만족하는 소량의 데이터를 인덱스에서 빨리 찾고 ROWID를 얻기 위함

ROWID는 물리적 주소보다 논리적 주소에 가깝다.

ROWID는 포인터가 아니다.

ROWID는 디스크 상에서 테이블 레코드를 찾아가기 위한 위치 정보를 갖는다. ( 레코드와 물리적으로 연결된 구조는 아니다. ), 버퍼캐시에서 먼저 찾고 없으면 디스크 블록을 읽는다.( 읽을때도 버퍼캐시에 먼저 적재한 후에 읽는다. )

오라클은 테이블 블록이 수시로 버퍼캐시에서 밀려났다가 다시 캐싱된다.

디스크주소정보(DBA를 )를 이용해서 해시 알고리즘으로 버퍼 블록을 찾는다.

I/O성능을 높이려면 버퍼캐시를 활용해야 한다.

디스크주소정보(DBA) -> 해시 함수 입력 -> 해시 체인 -> 버퍼 헤더

테이블 레코드를 얻기 위해 매번 DBA해싱과 래치 흭득 과정을 반복해야 한다.

ROWID == 우편주소

TABLE ACCESS BY INDEX ROWID => 고비용 연산

Table Full Scan
1. 성능이 일정하다.
2. 시퀀셜 액세스
3. 멀티블록 I/O
=> 순서대로 일정하게 많이많이

Index Range Scan
1. 고비용이고 빠르지만, 데이터가 일정량을 넘으면 풀스캔보다 느리다.
2. 랜덤 액세스 방식
3. 싱글블록 I/O
=> 소량을 빠르게

만 건만 넘어도 풀 스캔 방식이 빠를 수 있다.

OLTP => 인덱스 레인지 스캔
배치 => 풀 스캔, 해시 조인이 유리, 파티션 활용 전략, 병렬 처리시 금상첨화

NL조인은 인덱스를 이용하는 조인 방식이다.

인덱스를 이용해서 소트 연산을 생략할 수 있다.

튜닝 기법은 인덱스에 컬럼을 추가하는 것이다.

인덱스를 잘 설정하면 좋지만 무분별하게 만들면 관리 비용 증가와 DML부하에 따른 트랜잭션 성능 저하가 생길 수 있다.

필터 조건에 의해 버려지는 레코드가 많을 경우 인덱스에 컬럼을 추가함으로써 성능을 개선시킬 수 있다.

인덱스 구조 테이블(IOT) = 랜덤 액세스가 발생하지 않도록 테이블을 인덱스 구조로 생성하는 것. BETWEEN, 부등호 조건으로 넓은 범위을 읽을 때 유리

MS-SQL => 클러스터형 인덱스
ORACLE => IOT

테이블 블록에 있어야 할 데이터를 인덱스 리프 블록에 모두 저장하는 방식

일반 테이블은 힙 구조 테이블이라고 함.

힙 구조 테이블 = 랜덤 방식

---

인덱스 클러스터링 팩터

군집성 계수로 번역할 수 있는 용어다.

열 명의 자녀가 한 동네에 모여산다. -> 군집성이 좋다.

열 명의 자녀가 뿔뿔이 흩어져 산다. -> 군집성이 안좋다.

CF(클러스트링 팩터)가 좋다 안좋다로 얘기함

CF가 좋은 컬럼에 생성한 인덱스는 검색 효율이 좋다.

모여있다 == 정렬돼있다 == 찾기 쉽다

CF가 안좋은 인덱스를 사용하면 테이블 액세스 횟수만큼 블로 I/O가 발생한다.

클러스터 테이블
1. 인덱스 클러스터
=> 키값이 같은 레코드를 한 블록에 모아서 저장하는 구조, 한 블록에 다 못담으면 새로운 블록에 할당하고 클러스터 체인으로 연결

2. 해시 클러스터
=> 인덱스를 사용하지 않고 해시 알고리즘을 사용해 클러스터를 찾아간다.

---

부분범위 처리 활용

전체 쿼리 결과집합을 쉼 없이 연속적으로 전송하지 않고 사용자로부터 Fetch Call이 있을 때마다 일정량씩 나누어 전송하는 것

그래서 쿼리 툴(토드나 오렌지)에서 조회해보면 최초 200건만 조회하고 데이터를 더 확인하려고 드래그 하면 추가로 Fetch Call이 진행되는 것을 확인할 수 있다.

최초 Fetch Call 설정은 쿼리 툴에서 적용 가능

부분범위 처리의 핵심은 앞쪽 일부만 출력하고 멈춰야 한다는 것인데, DB서버 사이에 WAS, AP서버 등이 존재하는 N-TIER 아키텍처에서 클라이언트가 DB커넥션을 독점할 수 없기 때문에 SQL수행 결과를 모두 클라이언트에게 전송하고 커서를 닫아야 한다.

ORDER BY를 생략하는 개발 패턴은 사용하지 않아야 한다.

---

인덱스 스캔 효율화

운영 환경에서 가능한 일반적인 튜닝 기법은 인덱스 컬럼 추가다.

수직적 탐색은 스캔 시작점을 찾는 과정이다.

인덱스 선행 컬럼이 조건절에 없거나 '=' 조건이 아니면 인덱스 스캔 과정에 비효율이 생긴다.

인덱스 스캔 단계에서 처리하는 조건절은 2가지로 나뉜다.
1. 액세스 조건
=> 인덱스 스캔 범위 결정

2. 필터 조건
=> 테이블로 액세스할지를 결정하는 조건, 인덱스를 이용하든, 테이블 풀스캔을 하든, 테이블 액세스 단계에서 처리되는 조건절은 모두 필터 조건이다.

인덱스 스캔 효율성은 인덱스 컬럼을 조건절에 모두 '=' 조건으로 사용할 때 가장 좋다.

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

BETWEEN문을 IN-LIST로 바꿀 수 있다면 바꾸는게 성능면에서 좋다.

IN-LIST는 개수만큼 UNION ALL브랜치가 생성되고 모든 컬럼을 '=' 조건으로 검색한다.

IN-LIST 항목의 개수가 추가되고 관리하기 힘들다면 코드 테이블을 만들어서 조인문이나 서브쿼리로 구현하면 된다. ( 대신 너무 많은 코드는 탐색에 비효율이 생길 수 있다. 특히 Depth가 깊으면 더더욱 )

따라서 데이터 분포나 수직적 탐색 비용을 고려했을 때 성능이 좋을 것 같은 경우 BETWEEN을 IN-LIST로 바꾸도록 하자.

인덱스 선두 컬럼이 BETWEEN일 경우 성능 속도 순위
1. 인덱스를 잘 구성한다.
2. INDEX SKIP SCAN
3. IN-LIST
4. BETWEEN

선두 컬럼이 BETWEEN이어서 나머지 검색 조건을 만족하는 데이터들이 서로 멀리 떨어져 있을 경우 INDEX SKIP SCAN의 위력이 나타난다.

IN조건은 '='가 아니다.

LIKE보다 BETWEEN을 사용하는게 낫다.

2024년도 해당월을 찾고 싶은데,
LIKE '2024%'를 하면 202400월부터 202413월까지 찾을 수도 있어서 이럴 땐 차라리 BETWEEN 202401 AND 202412 이렇게 쓰는게 효과적이다.

인덱스 선두 컬럼에 대한 옵션 조건에 OR조건을 사용해선 안 된다.

인덱스에 NOT NULL 컬럼이 포함된 경우 18C 버전부터 OR조건도 인덱스 필터로 처리되기 시작했다.

인덱스에 포함되지 않은 컬럼에 대한 옵션 조건은 OR조건을 사용해도 무방하다.

1. 인덱스 액세스 조건으로 사용 불가
2. 인덱스 필터 조건으로도 사용 불가
3. 테이블 필터 조건으로만 사용 가능
4. 18C 부터는 인덱스 구성 컬럼 중 하나 이상이 NOT NULL컬럼일 경우 인덱스 필터 조건 가능

OR 조건을 이용한 옵션 조건 처리는 가급적 사용하지 않는 것을 추천한다.

LIKE/BETWEEN 인덱스 액세스 조건으로 사용 시 효과를 보기위한 유의사항 4가지( BETWEEN은 1,2번만 해당 )
1. 인덱스 선두 컬럼이여야 함
2. NULL 허용 컬럼
3. 숫자형 컬럼 ( 자동 형변환 일어나면서 필터 조건으로 사용됨 )
4. 가변 길이 컬럼 ( 조건에 LENGTH를 정의해줘야 함 )

BETWEEN 조건을 사용할 떄도 컬럼 값이 NULL인 데이터는 결과집합에서 누락된다.

UNION ALL을 활용하면 가장 최적화된 인덱스를 사용할 수 있다.

NVL, DECODE 둘 중 어느 것을 사용하든 실행계획은 똑같다.

PL/SQL 사용자 정의 함수가 느린 이유 3가지
1. 가상머신 상에서 실행되는 인터프리터 언어
2. 호출 시마다 컨텍스트 스위칭 발생
3. 내장 SQL에 대한 RECURSIVE CALL 발생

---

인덱스 설계가 어려운 이유

1. DML 성능 저하( TPS 저하 )
2. 데이터베이스 사이즈 증가(ㅐ 디스크 공간 낭비 )
3. 데이터베이스 관리 및 운영 비용 상승

꼭 필요하지 않은 인덱스를 만들면 디스크 공간을 낭비하고 백업, 복제, 재구성 등을 위한 운영 비용도 상승한다.

개발 단계에서는 인덱스를 변경할 수 있지만, 운영 단계에서는 인덱스 변경이 쉽지 않다. ( 금융권은 더더욱 어렵다. )

따라서 개발 단계에 최적화된 인덱스 설계가 가장 중요함.

인덱스 설계에 가장 중요한 2가지 기준
1. 조건절에 항상 사용되거나, 자주 사용하는 컬럼을 선정한다.
2. '=' 조건으로 자주 조회하는 컬럼을 앞쪽에 둔다.

스캔 효율성 이외의 판단 기준 7가지
1. 수행 빈도
2. 업무상 중요도
3. 클러스터링 팩터
4. 데이터량
5. DML 부하
6. 저장 공간
7. 인덱스 관리 비용 등

데이터량이 적다면 굳이 인덱스를 많이 만들 필요가 없도. FULL SCAN으로도 충분히 빠르기 때문이다.

I/O 최소화 + 소트 연산 생략 공식 3가지
1. '=' 연산자로 사용한 조건절 컬럼 선정
2. ORDER BY절에 기술한 컬럼 추가
3. '=' 연산자가 아닌 조건절 컬럼은 데이터 분포를 고려해 추가 여부 결정

즉, 인덱스 생성할 때
1. '=' 자주쓰는 컬럼 제일 앞
2. ORDER BY 절에 기술된 컬럼 그 다음으로
3. 나머지는 상황에 따라서 추가

즉, 선택도가 낮은 컬럼을 인덱스 선두에 두는 것은 의미없다. ORDER BY를 '=' 다음으로 놓는 것은 정렬된 상태에서 스캔하기 수월하기 때문.

3장 끝~