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

2장 인덱스 기본

인덱스 구조 및 탐색

  - 인덱스 탐색 과정이 수직적 탐색과 수평적 탐색, 두 단계로 이루어진다는 사실이다.

  - 인덱스 튜닝의 두 가지 핵심 (인덱스 스캔 효율화 튜닝, 랜덤 액세스 최소화 튜닝)

  - 인덱스 스캔 효율화 튜닝 : 인덱스 스캔 과정에서 발생하는 비효율을 줄이는 것

  - 랜덤 액세스 최소화 튜닝 : 테이블 액세스 횟수를 줄이는 것 → 성능에 미치는 영향이 크기 때문에 더 중요

  - SQL 튜닝은 랜덤 I/O와의 전쟁

  - IOT, 클러스터, 파티션에서부터 테이블 Prefetch, Batch I/O 기능의 본질은 랜덤 I/O 줄이는 것

→ 랜덤 액세스와 테이블 Full Scan에 대한 개념이 명확히 잡히지 않았음

 

인덱스 구조

  - 루트와 브랜치 블록에 있는 각 레코드는 하위 블록에 대한 주소값을 갖는다. 키값은 하위 블록에 저장된 키값의 범위를 나타낸다.

  - 리프 블록에 저장된 각 레코드는 키값 순으로 정렬돼 있을 뿐만 아니라 테이블 레코드를 가리키는 주소값, 즉 ROWID를 갖는다.

  - 인덱스를 스캔하는 이유는, 검색 조검을 만족하는 소량의 데이터를 빨리 찾고 거기서 ROWID를 얻기 위해서다. ROWID는 데이터 블록주소(DBA)와 로우 번호로 구성되므로 이 값을 알면 테이블 레코드를 찾아갈 수 있다.

 

인덱스 수직적 탐색

  - 수직적 탐색은 조건을 만족하는 레코드를 찾는 과정이 아니라 조건을 만족하는 첫 번째 레코드를 찾는 과정임을 반드시 기억

→ 조건 만족 레코드를 바로 찾아 가는 것이 아닌 조건 만족하는 블록의 첫 번째 레코드 찾는 것 인지하기

 

인덱스 수평적 탐색

  - 인덱스 리프 블록끼리는 서로 앞뒤 블록에 대한 주소값을 갖는다. 양방향 연결 리스트 구조(double linked list)

  - 인덱스 수평적 탐색 이유 첫째, 조건절을 만족하는 데이터를 모두 찾기 위해서고 둘째, ROWID를 얻기 위해서

 

결합 인덱스 구조와 탐색

  - 인덱스를 "고객명+성별"로 구성하든, "성별+고객명"으로 구성하든 읽는 인덱스 블록 개수는 똑같다.

  - 인덱스 선두 컬럼을 모두 "=" 조건으로 검색할 때는 어느 컬럼을 인덱스 앞쪽에 두든 블록 I/O 개수가 같으므로 성능도 똑같다.

  - 인덱스 불균형 문제는 B*Tree 인덱스에서 발생 할 수 없다. → 루트로부터 모든 리프 블록까지의 높이는 항상 같다

 

인덱스를 Range Scan 할 수 없는 이유

  - 인덱스 컬럼을 가공했을 때 인덱스를 정상적으로 사용할 수 없는 이유는 인덱스 스캔 시작점을 찾을 수 없기 때문이다

→ 그 외 NULL 검색, 묵시적 형변환, 부정검색도 인덱스 Range Scan 할 수 없다

 

더 중요한 인덱스 사용 조건

  - 인덱스 선두 컬럼이 가공되지 않은 상태로 조건절에 있으면 인덱스 Range Scan은 무조건 가능하다

  - 테이블과 달리 인덱스는 정렬돼 있다. 우리가 인덱스를 사용하는 이유다.

  

자동 형변환

  - 숫자형 컬럼 기준으로 문자형 컬럼을 변환한다. LIKE 일 경우는 반대 (오라클)

  - 형변환 기능에 의존하지 말고, 인덱스 컬럼 기준으로 반대편 컬럼 또는 값을 정확히 형변환해 주어야 한다

  - 개발자가 형변환 함수를 생략해도 옵티마이저가 자동으로 생성한다

 

인덱스 확장기능 사용법

  - Index Range Scan, Index Full Scan, Index Unique Scan, Index Skip Scan, Index Fast Full Scan

  - 성능은 인덱스 스캔 범위, 테이블 액세스 횟수로 얼마나 줄일 수 있느냐로 결정

  - Index Range Scan  하려면 선두 컬럼을 가공하지 않은 상태로 조건절 사용

  - Index Full Scan : 최적의 인덱스가 없을 때 차선으로 선택

  - Index Unique Scan : Unique 인덱스를 '=' 조건으로 탐색하는 경우

  - Index Skip Scan : 인덱스 선두 컬럼의 Distinct Value 개수가 적고 후행 컬럼의 Distinct Value 개수가 많을 때 유용

  - 인덱스는 기본적으로 최적의 Index Range Scan을 목표로 설계해야 하며, 수행 횟수가 적은 SQL을 위해 인덱스를 추가하는 것이 비효율적일 때 이들 스캔 방식을 차선책으로 활용하는 전략이 바람직하다

  - Index Fast Full Scan : 논리적인 인덱스 트리 구조를 무시하고 인덱스 세그먼트 전체를 Multiblock I/O 방식으로 스캔

  - Index Fast Full Scan은 인덱스가 파티션 돼 있지 않더라도 병렬 쿼리가 가능한 것도 중요한 특징 중 하나다

  - Index Range Scan Descending : 인덱스를 뒤에서 부터 앞쪽으로 스캔하기 때문에 내림차순으로 정렬된 결과집합 얻음