[SQL튜닝/인덱스와 조인] 인덱스 스캔 효율2 (인덱스매칭도(드라이빙), Between/In-List, Index_ss/In-List, 범위조건남용, 같은컬럼조회, Between/Like, 선분이력)

🖥️ 들어가며

📌 인덱스 스캔 효율
- 인덱스 매칭도 (드라이빙)
- Between / In-list Iterator
- Index Skip Scan / In-list
- 범위조건 남용
- 같은 컬럼 조회
- Between / Like
- 선분이력

 

---

 

✏️ 1. 인덱스 선행컬럼의 등치(=) 조건

• 인덱스 컬럼 중 조건절이 생략되거나, = 조건이 아니라도 뒤쪽 컬럼이라면 → 비효율이 없습니다.
• 아래와 같은 경우 Between 조건 이후로는 체크 조건으로 읽힙니다.
  즉, 조건을 만족하지 않는 레코드까지 스캔 후 버리는 비효율이 발생합니다.

-- 인덱스: 매물 + 시세코드 + 평형 + 타입
WHERE 시세코드 = '1111'
AND 평형 = '36'
AND 타입 = 'A'
AND 매물 BETWEEN '1' AND '3';

 

✏️ 2. Between / In-list Iterator

• 위 코드에서 Between을 IN-list로 변경하는 경우, IN 컬럼 개수만큼 UNION ALL한 효과가 발생합니다.
• 체크조건 앞 컬럼들이 변별력이 좋아 검색 구간을 줄였다면 Between 조건이 오히려 유리할 수 있습니다.
• Between 조건을 In-list로 대체할 때 주의할 점
  • In-list의 개수가 많지 않아야 함
  • 수직적 탐색*이 In-list 횟수만큼 발생
    *수직적 탐색에서는 테이블 Random Access가 발생합니다. 
  • 인덱스 높이가 높을 때 비효율 증가

-- 인덱스: 매물 + 시세코드 + 평형 + 타입
WHERE 시세코드 = '1111'
AND 평형 = '36'
AND 타입 = 'A'
AND 매물 IN ('1','2','3');

-- 위와 아래 쿼리문은 동일한 효과
WHERE 시세코드 = '1111'
AND 평형 = '36'
AND 타입 = 'A'
AND 매물 = '1'
UNION ALL
...
AND 매물 = '2'
UNION ALL
...
AND 매물 = '3';

 

✏️ 3. Index Skip Scan

• 선행 컬럼의 카디널리티가 낮을 때(ex: 성별, 결혼여부, Boolean 등), 선행 컬럼이 조건절에 없을 때, 범위 검색 조건일 때
• In-list 대신 Index Skip Scan(+Between)을 사용하는 것이 근소하게 유리할 수 있습니다.
  (수직적 탐색 시, Root까지 가지 않고 Leaf Block 내에서 선택적으로 액세스하기 때문)

SELECT /*+ INDEX_SS (T 월별집계_IDX2)*/ COUNT(*)
FROM   월별집계 T
WHERE  구분='A'
AND    판매월 BETWEEN '240501' AND '240531';

✏️ 4. 범위조건 남용

• 주문일자 조건을 Like로 처리하는 경우, 다음과 같은 비효율이 발생합니다.
  • 주문일자 컬럼의 값이 O_DATE로 시작하는 모든 레코드를 찾음
  • Like 연산자와 와일드카드(%)를 사용하면 주문일자 컬럼에 대한 인덱스를 활용하지 못함

-- 인덱스: 상품코드 + 주문일자
SELECT 상품코드, 주문일자, 주문유형
FROM   주문
WHERE  상품코드 = :P_CODE
AND    주문일자 = LIKE:O_DATE || '%'
AND    주문유형 = :O_TYPE;

• Nullable이라면
  • 지역 조건 참여 여부에 따라 SQL을 분기해야 합니다.

SELECT 상품코드, 주문일자, 주문유형
FROM   주문
WHERE  :O_DT IS NOT NULL
AND    상품코드 = :P_CODE
AND    주문일자 = :O_DATE
AND    주문유형 = :O_TYPE
UNION ALL
SELECT 상품코드, 주문일자, 주문유형
FROM   주문
WHERE  :O_DT IS NULL
AND    상품코드 = :P_CODE
AND    주문유형 = :O_TYPE

• Not Null이라면
  • OR Expansion를 활용할 수 있습니다.
    (Nullable에서 OR Expansion 활용 시 잘못된 데이터가 나올 수 있음, null = null은 false로 취급되기 때문)
  • NVL(expr1, expr2): expr1이 NULL이면 expr2 반환하고, 그렇지 않으면 expr1 반환합니다.

SELECT 상품코드, 주문일자, 주문유형
FROM   주문
WHERE  상품코드 = :P_CODE
AND    주문일자 = NVL(:O_DATE, 주문일자)
AND    주문유형 = :O_TYPE;

 

✏️ 5. 같은 컬럼 조회

• 기존 실행계획이 access("번호" <= 10000), filter("번호"<=10)인 경우,
  다음과 같이 access("번호" <= 10)가 되도록 변경합니다.
• 체크 조건으로 만들고 싶은 조건절의 컬럼을 TRIM*으로 감쌉니다.
  *TRIM(): 문자열 데이터 내에서 특정 문자를 지우기 위해 사용합니다.(삭제할 문자가 없을 땐 공백 제거)
• 번호 <= 10, 도서명 > :LAST_BOOK_NM을 드라이빙 조건으로 사용하여 인덱스를 더 효율적으로 활용합니다.

-- 기존
SELECT *
FROM   교재
WHERE  번호 <= 10000 -- 드라이빙 조건
AND    번호 <= 10;   -- 체크 조건

-- 변경
SELECT *
FROM   교재
WHERE  TRIM(번호) <= 10000 -- 체크 조건
AND    번호 <= 10;         -- 드라이빙 조건

-- 기존
SELECT *
FROM ( -- 이전 조회와 도서명이 같은 경우
	  SELECT /*+ INDEX(도서 도서명_idx)*/
      		 ROWID RID, 도서번호, 도서명, 가격, 저자, 출판사, ISBN
      FROM   도서
      WHERE  도서명 = :LAST_BOOK_NM
      AND    ROWID > :LAST_RID
      UNION ALL -- 이전 조회보다 도서명이 큰 경우
      SELECT /*+ INDEX(도서 도서명_idx)*/
      		 ROWID RID, 도서번호, 도서명, 가격, 저자, 출판사, ISBN
      FROM   도서
      WHERE  도서명 LIKE :BOOK_NM || '%'      -- 드라이빙 조건
      AND    도서명 > :LAST_BOOK_NM           -- 체크 조건
     )
WHERE ROWNUM <= 10;

-- 변경
...
      WHERE  TRIM(도서명) LIKE :BOOK_NM || '%' -- 체크 조건
      AND    도서명 > :LAST_BOOK_NM            -- 드라이빙 조건
     )
WHERE ROWNUM <= 10;

✏️ 6. Between / Like 스캔 범위

• 판매월 데이터: 202401, 202402, 202403, 2024021, 202404, 202405
• Leaf Node에 존재하는 조건 검색 시
  
  • Between으로 하면 시작, 끝점을 찾는 경우엔 등치(=) 조건과 같은 효과를 냅니다.
    (202401부터 202402까지의 범위를 정확히 찾아낼 수 있음)
  • Like는 통상적으로 Leaf Node에 없는 조건을 검색합니다.
    (Like, 부등호 조건은 '2024021'도 포함할 수 있어 계속 스캔해야 함)
• 만약 [인덱스: 판매구분 + 판매월] 이라면 둘다 무조건 드라이빙 조건으로 조회됩니다.
  이 경우 Between이든 Like든 두 연산자 간 성능 차이는 크지 않습니다.

WHERE 판매월 BETWEEN '202401' AND '202402'
AND   판매구분 = 'A';

✏️ 7. 선분이력

• 최신 데이터를 주로 읽을 때: [인덱스: 종료일자 + 시작일자]
  • 최신 데이터는 종료일자가 NULL이거나 가장 큰 값을 가지는 경우가 대부분입니다.
  • 종료일자 + 시작일자 인덱스를 사용하면, 인덱스의 맨 앞부분(종료일자가 NULL 또는 최대값)에서 바로 최신 데이터를 찾을 수 있습니다.
    (대규모 DB에서 이 차이는 더욱 극명해질 수 있습니다.)
• 과거 데이터를 주로 읽을 때: [인덱스: 시작일자 + 종료일자]
• 인덱스 수정이 불가한 경우, Index_Desc 힌트를 사용합니다.
• 중간 지점을 읽을 때는 어떤 인덱스든 비효율이 발생하지만 rownum <= 1 조건을 활용합니다.
  • rownum <= 1를 하는 경우, 조건을 만족하는 첫 번째 레코드를 찾는 즉시 검색을 중단하기 때문에 평균적으로 훨씬 적은 수의 레코드만 스캔하게 됩니다.

-- 인덱스: 고객번호 + 시작일 + 종료일
SELECT /*+ INDEX_DESC(a idx_x01)*/ *
FROM   고객별연체금액 A
WHERE  고객번호 = 'C101'
AND    '20190831' BETWEEN 시작일 AND 종료일
AND    ROWNUM <= 1;

 

 

---

📒 정리하면

⭐⭐⭐인비스범같이(B)선

1. 인덱스 선행컬럼의 등치(=) 조건 중요, Between 조건 이후로는 체크 조건으로 읽혀 비효율이 발생할 수 있습니다.
2. Between과 In-list Iterator는 상황에 따라 선택적으로 사용해야 하고, In-list는 개수가 적고 인덱스 높이가 낮을 때 유리합니다.
3. Index Skip Scan은 선행 컬럼의 카디널리티가 낮거나 조건절에 없을 때, 범위검색 조건일 때 (In-list보다) Between과 함께 사용하는 게 효과적입니다.
4. 범위조건 사용 시
   Like 연산자 대신 Nullable이라면 SQL 분기, Not Null이라면 OR Expansion를 활용하여 최적화할 수 있습니다.
   Leaf Node에 존재하는 조건 검색 시 Between으로 하면 시작, 끝점을 찾을 때 등치(=) 조건과 같은 효과를 냅니다.
5. 선분이력 테이블에서는
   데이터 접근 패턴에 따라 인덱스를 설계해야 합니다.
   최근 데이터 조회 시 인덱스: [종료일자 + 시작일자] / 과거 데이터 조회 시 [시작일자 + 종료일자] 가 효과적입니다.

---