한달동안 인덱스 스캔 효율화를 벗어나지 못 한 것 같은데요...
아무리 봐도 안 되겠다 싶어서 정리하면서 다시 복습해보겠습니다! 💪
참고로 인덱스 스캔 효율화는 튜닝 기법이 매우 다양합니다 그래서 이렇게 읽히지도 않고 어려운지...
인덱스 스캔 효율화
인덱스 설계 공식의 핵심 원리를 코드로 구현하는 일!
오늘의 세줄 요약
- 선행 컬럼은 되도록 = 로, 범위 컬럼은 뒤쪽으로
- 운영에서 인덱스를 못 바꿀 땐 BETWEEN → IN (...) 으로 쪼개 뒤 컬럼까지 인덱스 접근을 살리기
- 효과가 큰 상황: 범위 컬럼이 앞이고, 뒤의 = 조건들이 선택도가 높은 경우(또는 Skip Scan이 잘 잡힐 때)
1️⃣ 인덱스 탐색
먼저 “어디서 시작할지”(수직), 다음 “어디까지 볼지”(수평)를 정한다
- 수직 탐색: B-Tree를 타고 시작 지점을 찾는다.
- 수평 탐색: 시작 지점에서 연속된 레코드를 스캔한다(끝 지점까지).
2️⃣ 인덱스 스캔 비효율 발생 조건
선행 컬럼이 빠지거나, 선행 컬럼이 =이 아니면 멀리~ 많이~ 읽는다
- 인덱스 선행 컬럼이 조건절(WHERE)에 없는 경우
- 인덱스 선행 컬럼이 '='조건이 아닌 경우(예: >, <, BETWEEN, LIKE 'ab%' 등)
3️⃣ 액세스 조건과 필터 조건
액세스는 “찾는 데” 쓰고, 필터는 “거르는 데” 쓴다
- 인덱스 액세스 조건: 시작/끝 위치를 찾는 데 쓰임 → 스캔 범위 자체를 줄임
- 인덱스 필터 조건: 인덱스에서 보고 거름 → 스캔은 했고, 그 안에서 탈락
- 테이블 필터 조건: 테이블(ROWID 이후)에서 보고 거름 → 가장 비쌈
4️⃣ 비교 연산자 종류와 컬럼 순서에 따른 군집성
앞에서부터 =로 조일수록 “한 덩어리”만 읽는다
- 인덱스는 같은 값이 모여 저장
- 선행 컬럼이 '='로 이어지면 조건에 맞는 레코드가 붙어 있어 필요한 범위만 짧게 스캔하고 멈춤
- 중간을 건너뛰거나 선행에서 범위(> < BETWEEN LIKE)를 쓰면 흩어져서 많~이 읽음
📊 예시 [인덱스: (성, 이름, 나이)]
-- Case 1: 군집 유지(좋음)
WHERE 성 = '김'
AND 이름 = '민수'
-- Case 2: 중간 컬럼 누락(흩어짐)
WHERE 성 = '김'
AND 나이 = 30
-- Case 3: 선두 범위 조건 → 그 뒤는 필터 처리
WHERE 성 > '김'
AND 이름 = '민수'
5️⃣ 인덱스 선행 컬럼이 등치 조건이 아닐 때 생기는 비효율
첫 범위 조건이 나오면 “그 뒤 조건”은 액세스로 못 쓰고 전부 필터로 사용한다
- 인덱스 탐색 범위가 넓어진다
- 후행 컬럼 조건은 인덱스에서 쓸 수 없다.(전부 필터 처리)
💡 핵심 규칙: 복합 인덱스에서 ‘첫 범위 조건’까지 인덱스 액세스 조건, 그 뒤는 모두 필터 조건
6️⃣ BETWEEN을 IN-LIST로 전환
범위를 여러 개의 = 로 쪼개면 뒤 컬럼까지 인덱스 접근에 태울 수 있다
범위 컬럼을 맨 뒤로 가도록 인덱스를 구성하면 좋겠지만 운영에서는 사실상 인덱스 구성을 바꾸기 어렵다 🥲
이럴 때 범위 조건인 BETWEEN을 IN_List로 바꾸면 큰 효과를 얻는 경우가 있다!😄
1. 효과가 있는 이유는 무엇일까?
BETWEEN을 IN-LIST로 바꾼 다는 것은 아래처럼 쿼리를 변경하는 것
As-Is
WHERE SALE_DATE BETWEEN DATE '2025-08-01' AND DATE '2025-08-07
AND ORG_ID = 'A'
AND ITEM_ID = :'123';
To-Be
WHERE SALE_DATE IN (DATE '2025-08-01', DATE '2025-08-02', …, DATE '2025-08-07')
AND ORG_ID = 'A'
AND ITEM_ID = :'123';- To-Be 쿼리는 SALE_DATE도 동등 조건으로 사용한다
- 즉, (SALE_DATE='2025-08-01') + (ORG_ID='A') + (ITEM_ID='123') 조합으로 쿼리가 여러 번 정확하게 자르고 합쳐진다
- 💡 핵심은 동등 조건이기 때문에 뒤 컬럼까지 인덱스로 제대로 활용 가능하다는 것!
- 💡 인덱스 뒤 컬럼을 살리고 싶다면, 범위를 동등 조건의 집합으로 바꿔라!
1-1. 여기서 잠깐! In-List Iterator란? (초간단)
- 정의: IN(a,b,c)를 a,b,c 각각 =로 나눠 여러 번 인덱스 탐색하고 결과를 합치는 최적화
- 언제 유리? : IN 값이 적고(대략 수십 개 이하), 선행 컬럼에 걸리며, 선택도 높을 때
- 체크 포인트: 실행계획에 INLIST ITERATOR가 보이면 이 방식
- 주의: IN 값이 너무 많거나 값들이 가까이 몰려 있으면 이득 X, 그냥 필터가 나을 수 있음 (DB마다 IN 항목 개수 한도도 있음)
2. 👍 언제 잘 먹힐까?
- 날짜/월/코드처럼 불연속 값으로 쪼개기 쉽고 개수가 적은 범위
- 범위 컬럼이 인덱스 앞쪽이고 뒤에 있는 조건(동등)이 선택도가 높을 때
- 조건이 선택도가 높다는 뜻은
해당 조건이 데이터가 거의 안 남을 정도로 빡세게 거르는 경우를 말합니다!
3. ⚠️ 주의할 점
- 값이 너무 많아지는 IN 리스트(수백~수천 개) 는 오히려 느림
- 범위 값이 연속 실수/금액처럼 셈하기 애매한 컬럼은 효과가 제한적
7️⃣ Index Skip Scan 활용
그렇다면 항상 BETWEEN을 IN-LIST로 조건절을 바꿔야 하는 것일까?
→ 정답은 아니다!
선두 범위 + 후행 동등 조합이어도 후행의 선택도가 높다면 Skip Scan이 이득
1. 예시
-- A=SALE_DATE(범위), B=ORG_ID, C=ITEM_ID(=)
SELECT /*+ INDEX_SS(SALES IDX_SALES_DATE_ORG_ITEM) */ ...
FROM SALES
WHERE SALE_DATE BETWEEN :s AND :e
AND ORG_ID = :org
AND ITEM_ID = :item;계획에 INDEX SKIP SCAN 보이면 성공 😄
2. 👍 언제 잘 먹힐까?
- 인덱스가 (A, B, C)이고, A는 BETWEEN, B/C는 =
- B/C가 극소수만 남길 정도로 선택적
- SQL을 바꾸기 어렵거나 IN(...)로 쪼개기 애매할 때
3. ⚠️ 주의할 점
- B/C 선택도가 낮음(많이 남음)
- A 범위가 과하게 넓고 결과가 촘촘히 분포
8️⃣ IN 조건은 '='인가
조건부! In-List Iterator로 풀릴 때만 ‘여러 개의 =’(액세스 조건)이고 나머지는 필터 조건이다
1. 언제 ‘=’인가? (액세스 조건)
- 옵티마이저가 In-List Iterator 선택
- 값이 적당히 적음
- 인덱스 선행 컬럼에 IN, 선택도 높음
- 실행계획에 INLIST ITERATOR 표기
2. 언제 그냥 필터가 좋을까?
- IN 목록이 너무 많음(수백~수천)
- 값들이 가까이 몰려 있음(군집성이 높음) → 여러 번 찌르는 이득이 없음
- IN 컬럼이 인덱스 후행이라 선행에서 막힘
3. 어떻게 사용하는 게 좋을까?
- IN은 작게 + 선택적으로, 가능하면 선행 컬럼에 IN
- 레코드가 멀리 흩어질수록 In-List Iterator가 유리, 붙어 있으면 필터가 더 단순·빠름
- 계획에 INLIST ITERATOR 없으면 그대로 두거나, 인덱스 순서/조건식 재검토
