친절한 SQL 튜닝 정리 (3장)

3장 인덱스 튜닝

테이블 랜덤 액세스

  - 인덱스를 스캔하는 이유는, 검색 조건을 만족하는 소량의 데이터를 인덱스에서 빨리 찾고 거기서 테이블 레코드를 찾아가기 위한 주소값, 즉 ROWID를 얻으려는 데 있다
  - 하지만 인덱스 ROWID는 물리적 주소보다 논리적 주소에 가깝다 (테이블 레코드를 찾아가기 위한 논리적 주소 정보 담고 있다)
  - 메모리 주소 정보(포인터)가 아닌 디스크 주소 정보(DBA)를 이용해 해시 알고리즘으로 버퍼 블록을 찾아간다
  - 모든 데이터가 캐싱돼 있더라도 테이블 레코드를 찾기 위해 매번 DBA 해싱과 래치 획득 과정을 반복해야 한다. 동시액세스가 심할 때는 캐시버퍼 체인 래치와 버퍼 Lock에 대한 경합까지 발생한다. 이처럼 인덱스 ROWID을 이용한 테이블 액세스는 생각보다 고비용 구조다.

인덱스 클러스터링 팩터

  - 특정 컬럼을 기준으로 같은 값을 갖는 데이터가 서로 모여있는 정도 의미

인덱스 손익분기점

  - Index Range Scan에 의한 테이블 액세스가 Table Full Scan보다 느려지는 지점을 흔히 ’인덱스 손익분기점‘ 이라 부른다
  - 두가지 요인
  - Table Full Scan은 시퀀셜 액세스인 반면, 인덱스 ROWID을 이용한 테이블 액세스는 랜덤 액세스 방식
  - Table Full Scan은 Multiblock I/O인 반면, 인덱스 ROWID를 이용한 테이블 액세스는 Single Block I/O 방식
  - 대량 데이터를 읽고 갱신하는 배치 프로그램은 항상 전체범위 처리 기준으로 튜닝해야 한다. (Fulll Scan과 해시 조인 유리)

인덱스 구조 테이블

  - 오라클 : IOT, MS-SQL Server : 클러스터형 인덱스
  - 테이블 블록에 있어야 할 데이터를 인덱스 리프 블록에 모두 저장
  - 일반 힙 구조 테이블에 데이터를 입력할 때는 랜덤 방식 사용한다. 즉, Freelist로부터 할당 받은 블록에 정해진 순서 없이 데이터 입력한다. 반면, IOT는 인덱스 구조 테이블이므로 정렬 상태를 유지하며 데이터를 입력한다
  - IOT는 인위적으로 클러스터링 팩터를 좋게 반드는 방법
-> IOT 언제 사용하는지 찾아보기

부분범위 처리 활용

  - 1억 건짜리 테이블인데도 결과를 빨리 출력할 수 있는 이유는, DBMS가 데이터를 모두 읽어 한 번에 전송하지 않고 먼저 읽는 데이터부터 일정량 전송 후 멈추기 떄문
  - 부분범위 처리 : 사용자로부터 Fetch Call이 있을 때마다 일정량씩 나누어 전송
  - 앞쪽 일부만 출력하고 멈출 수 있는 지가 부분범위 처리의 핵심

인덱스 탐색

  - 인덱스 선행 컬럼이 조건절에 없거나 ‘=’ 조건이 아니면 인덱스 스캔 과정에 비효율 발생
  - 인덱스 액세스 조건 : 인덱스 스캔 범위 결정
  - 인덱스 필터 조건 : 테이블로 액세스할지를 결정
  - 테이블 필터 조건 : 쿼리 수행 다음 단계로 전달하거나 최종 결과집합에 포함할지 결정
-> 인덱스 필터와 테이블 필터 조건 내용, 예시 고도화 원리에서 찾아볼 것

BEWTEEN을 IN-List로 전환

  - IN-List 개수만큼 UNION ALL 브랜치가 생성되고 각 브랜치마다 모든 컬럼을 ‘=’ 조건으로 검색하므로 앞서 선두 컬럼에 BETWEEN을 사용할 때와 같은 비효율 사라진다
  - IN-List 개수가 많으면 수직적 탐색이 많이 발생한다

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

  - 인덱스 선두 컬럼에 대한 옵션 조건에 OR 조건을 사용해선 안 된다
  - 인덱스 액세스 조건 사용 불가, 인덱스 필터 조건으로도 사용 불가, 테이블 필터 조건으로만 사용 가능
  - LIKE/BETWEEN 패턴 점검 (인덱스 선두 컬럼, NULL 허용 컬럼, 숫자형 컬럼, 가변 길이 컬럼)
  - 옵션 조건 처리용 NVL/DECODE 함수를 여러 개 사용하면 그 중 변별력이 가장 좋은 컬럼 기준으로 한 번만 OR Expansion 일어남
-> 옵션 조건 처리 후 결과가 어떻게 되는지 복습하기

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

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

인덱스 설계가 어려운 이유

  - DML 성능 저하
  - 데이터베이스 사이즈 증가
  - 데이터베이스 관리 및 운영 비용 상승

인덱스 선정 가장 중요한 두 가지 선택 기준

  - 조건절에 항상 사용하거나, 자주 사용하는 컬럼 선정
  - ‘=’ 조건으로 자주 조회하는 컬럼을 앞쪽에 두어야 한다는 것
  - SQL 튜닝 전문가라면, 열 개 중 최적을 달성해야 할 가장 핵심적인 액세스 경로 한 두개를 전략적으로 선택해서 최적 인덱스로 설계하고, 나머지 액세스 경로는 약간의 비효율이 있더라도 목표 성능을 만족하는 수준으로 인덱스를 구성할 수 있어야 한다

소트 연산을 생략하기 위한 인덱스 구성 순서

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