자격증/SQLP

인덱스 튜닝

soohykim 2025. 4. 16. 15:54
728x90
반응형

📁 개념

📌 Index Range Scan

  1. B-Tree 인덱스의 가장 일반적이고 정상적인 형태의 스캔 방식
  2. 인덱스 루트에서 리프 블록까지 수직 탐색한 후에 리프 블록을 수평 탐색 방식
  3. 수평 탐색 범위는 인덱수 구성, 조건절 연산자에 따라 달라짐
  4. 인덱스 선두 컬럼에 대한 '=' 조건은 반드시 있어야 함 (WHERE절에 인덱스 선두컬럼에 대한 조건이 반드시 있어야함)
💡Index Range Scan 불가능한 경우
  
  (1) 중간값 검색 (양쪽 % LIKE 조건)
  (2) 부정형 비교 조건
  (3) 인덱스 컬럼 변형할 경우
  (4) 인덱스 구성 컬럼이 모두 NULL레코드인 경우 IS NULL 조건 사용
  (5) OR 조건 (Concatenation으로 옵티마이저에 대한 UNION ALL 분기 처리시 사용 가능)

 

📌 Index Full Scan

  1. 결과 집합을 모두 출력한다면, 인덱스 리프블록을 처음부터 끝까지 모두 스캔
  2. 인덱스를 Full Scan하면서 테이블 데이터를 액세스하는 방식으로 전체 결과집합 추출해야 하면, 인덱스 필터 조건을 만족하는 데이터가 적을수록 효과적
  3. 인덱스를 Full Scan하면서 테이블 데이터를 액세스하는 방식으로 전체 결과 집합 중 앞 쪽 일부만 스캔하고 멈출 수 있는 (=부분 범위 처리) 상황이라면, 인덱스 필터 조건을 만족하는 데이터가 많을 수록 효과적
  4. INDEX_FS 힌트로 유도 (INDEX FULL SCAN 힌트는 없음)

 

📌 Index Fast Full Scan

  1. MultiBlock I/O 방식 사용
  2. 병렬 스캔 가능
  3. 인덱스에 포함된 컬럼으로만 조회시 사용
  4. 리프블록만 빠르게 스캔하므로 인덱스 정렬 순서대로 결과 집합 출력 (순서 보장X)
  5. SQL에 인덱스 없는 컬럼 포함시 Index Fast Full Scan 불가

 

📌 Index Skip Scan

  1. 인덱스 선두 컬럼이 조건절에 없을 때 사용할 수 있는 스캔 방식
  2. 인덱스 선두 컬럼이 조건절에 있을 때도 사용 가능
  3. INDEX_SS 힌트로 유도함
  4. 조건절에 누락된 인덱스 선두컬럼에 대한 IN 조건절을 옵티마이저가 추가해줌으로써 IN-LIST Iterator 방식으로 인덱스를 스캔하는 방식
  5. BETWEEN, IN 조건절 사용

 

📌 인덱스 액세스 조건과 인덱스 필터 조건

 

 

 

📁 특징

📌 B-Tree 인덱스 구조

  1. 브랜치 블록의 각 로우는 하위 블록에 대한 주소값 가짐
  2. 브랜치 블록 각 로우의 키 값은 하위블록이 갖는 값의 범위
  3. 리프 블록 각 로우의 키 값은 테이블 로우의 키 값과 일치
  4. 리프 블록간에는 이중 연결리스트 구조
  5. 리프 블록의 각 로우와 테이블 로우 간에는 1:1 관계

 

📁 

📌 인덱스 RowID

  1. 테이블 레코드에 대한 포인터로써 물리적으로 직접 연결된 구조
  2. RowID에 포함된 데이터 블록 주소는 데이터파일 상의 블록위치를 가리킴
  3. 데이터 블록 주소로 버퍼 캐시에서 블록을 찾을 때 해시 알고리즘 이용
  4. 버퍼캐시에서 블록을 찾는 과정에서 latch, buffer lock 등 경합 생김
  5. 인덱스 RowID 포맷
    • 제한 RowID : 데이터파일 번호 + 블록 번호 + 블록 내 로우 번호 + 테이블스페이스 번호
    • 확장 RowID : 오브젝트 번호 + 데이터파일 번호 + 블록 번호 + 블록 내 로우 번호

 

📌 인덱스 설계시 고려하는 판단 기준

  1. SQL 수행 빈도
  2. SQL 업무상 중요도
  3. 데이터 양과 DML 발생 빈도 (인덱스 최소화)
  4. SQL 조건절 순서

 

📌 인덱스를 이용한 테이블 랜덤 액세스 부담 줄이는 기능

  1. IOT (Index-Organized Table)
    • 테이블을 인덱스 구조로 관리
    • 지정한 키 값 순으로 정렬 상태 유지
    • 키 값 이외의 컬럼도 모두 인덱스 리프블록에 저장해 테이블 랜덤 액세스 발생X
  2. 클러스터
    • 값이 같은 레코드를 한 블록 (데이터가 많을 경우 연결된 여러 블록) 모아서 저장
  3. 파티션
    • 사용자가 지정한 기준에 따라 데이터를 세그먼트 단위로 모아서 저장
    • 인덱스 랜덤 액세스 (Full Scan 방식으로 빠르게 찾음)

 

📌 인덱스 클러스터링 팩터

  1. 인덱스 컬럼을 기준으로 같은 값을 갖는 데이터가 서로 모여있는 정도
  2. 통계정보로 인덱스 경유해 테이블 전체 로우를 액세스 할 때 읽을 것으로 예상되는 논리적 블록 개수
  3. 인덱스 성능은 클러스터링 팩터가 테이블 수에 가까울수록 좋고, 테이블 레코드 수에 가까울수록 나쁨
  4. 인덱스를 재생성(Rebuild)하면 클러스터링 팩터가 좋아짐 (인덱스 컬럼 순으로 정렬되도록 테이블 재생성)

 

 

728x90
반응형