[SQL튜닝/인덱스와 조인] 인덱스 스캔 효율1 (Sequential Access 선택도 높이기, =, IN, 드라이빙 조건, 체크 조건)

🖥️ 들어가며

📌 I/O 튜닝의 핵심 원리
- Sequentail Access의 선택도를 높인다.
- Random Access 발생량을 줄인다.

---

👉🏻 드라이빙 조건과 체크 조건

• 드라이빙 조건: 인덱스를 사용하여 데이터에 빠르게 접근합니다.
• 체크 조건: 인덱스를 사용하지 않고 필터링 역할을 합니다.

 

✏️ 1. 인덱스 매칭도

⭐⭐⭐ =, IN 조건이 아닌 조건 이후는 무조건 체크 조건입니다.

SELECT *
FROM 사원
WHERE 입사년월 BETWEEN '2001' AND '2103'
AND 성별 = '남'
AND 관리자 = '52';

• 쿼리문이 위와 같을 때, 가장 적절한 인덱스 구성은?
  • 성별 + 관리자 + 입사년월
    (=, =, BETWEEN)
    (드라이빙, 드라이빙, 드라이빙 조건)
  • 사유: =, IN을 선두컬럼으로 두어 이후에도 드라이빙 조건이 될 수 있도록 합니다.
• 따라서 일반적으로 날짜는 인덱스 순서를 뒤로 보내는 게 좋습니다.

 

✏️ 2. 결합 인덱스 설계 시 우선순위

• 자주 사용되는가?
  • 쿼리에서 가장 자주 사용되는 컬럼을 인덱스 선두 컬럼으로 배치해야 합니다.
  • Index Range Scan을 효과적으로 수행하기 위함입니다.
• =, IN 조건
  • 이후 컬럼도 드라이빙 조건으로 사용될 수 있도록 = 또는 IN 조건으로 사용되는 컬럼을 인덱스 앞쪽에 매치합니다.
• Cardinality(분포도)
  • 고유한 값의 수가 많은(분포도가 높은) 컬럼을 인덱스 앞쪽에 배치합니다.
  • 인덱스 선택성을 높여 검색 효율을 향상시킵니다.
  • 단, 선두컬럼의 카디널리티가 너무 높으면 Index Skip Scan의 효율이 떨어질 수 있습니다.
    (카디널리티가 높음 = 고유한 값이 많음 → Index Skip Scan은 선두 컬럼의 고유 값에 대해 인덱스 스캔 →  고유 값이 많으면 스캔 횟수 증가, 성능 저하)
• 소트연산 대체
  • ORDER BY 절에서 사용되는 컬럼들의 순서를 고려하여 인덱스를 구성합니다.

 

✏️ 3. 실행계획으로 인덱스 스캔 효율 알아보기

 SELECT *
   FROM EMP A, DEPT B
  WHERE A.DEPTNO = B.DEPTNO
    AND A.JOB='SALESMAN'

--------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------
|   1 |  NESTED LOOPS                |         |      1 |      3 |      4 |00:00:00.01 |      14 |
|*  2 |   TABLE ACCESS FULL          | EMP     |      1 |      3 |      4 |00:00:00.01 |       8 |
|   3 |   TABLE ACCESS BY INDEX ROWID| DEPT    |      4 |      1 |      4 |00:00:00.01 |       6 |
|*  4 |    INDEX UNIQUE SCAN         | PK_DEPT |      4 |      1 |      4 |00:00:00.01 |       2 |
--------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("A"."JOB"='SALESMAN')
   4 - access("A"."DEPTNO"="B"."DEPTNO")

• 실행계획에서 Index, Table 접근 방식

구분	INDEX	TABLE
access	드라이빙 조건	-
filter	체크 조건	테이블에서 필터링 수행 (R.A 비효율 발생)

• 실행계획 순서: 4 → 3 → 2 → 1
• *2: TABLE ACCESS FULL
  • EMP 테이블 전체 스캔합니다.
  • filter 내용: EMP 테이블에서 JOB이 'SALESMAN'인 행을 필터링 합니다. (이후 체크 조건)
    => 해결 방법: JOB 컬럼을 인덱스에 추가하면 EMP 테이블의 Full Scan을 피할 수 있습니다.
• 3: TABLE ACCESS BY INDEX ROWID
  • DEPT 테이블은 인덱스 스캔 후 실제 데이터 행(ROWID)를 가져옵니다.
• *4: INDEX UNIQUE SCAN
  • DEPT 테이블의 고유 인덱스(PK_DEPT)에 의해 검색됩니다.
  • access 내용: DEPTNO를 기준으로 두 테이블을 조인합니다.
• 기타 정보
  
  • Starts: 해당 작업이 실행된 횟수입니다. 
  • A-Rows(Actual Rows): 실제로 처리된 행의 수입니다. 수치가 다르면 둘 중 하나만 인덱스한 것입니다. (테이블 R.A 비효율이 있었다는 것)
  • Buffers: 해당 작업에서 사용된 버퍼의 수입니다. 하위 레벨의 버퍼 수를 포함합니다.
    • 2 → 6에서 6은 하위레벨 2개* + 4개
      *2개는 Buffer Pinning 효과로 재사용을 통해 I/O를 줄임

 

---

📒 정리하면

1. I/O 튜닝의 핵심은 Sequential Access의 선택도를 높이고, Random Access 발생량을 줄이는 것입니다.
2. 인덱스 매칭도를 높이기 위해 =, IN 조건을 선두 컬럼으로 두고, 날짜 등의 범위 조건은 뒤로 배치합니다.
3. 결합 인덱스 설계 시 사용빈도, 조건 유형(=, IN), 카디널리티, 정렬 연산 대체 가능성을 고려합니다.
4. 실행 계획에서는 INDEX access는 드라이빙 조건, filter는 체크 조건을 나타냅니다.
   TABLE filter는 R.A 비효율을 발생시킬 수 있습니다.
5. 실행 계획 분석을 통해 인덱스 사용 여부, 조인 방식 등을 파악하고 성능 개선 포인트를 찾을 수 있습니다.

---

• 참고자료
  • http://www.gurubee.net/lecture/2386