인덱스 설계 사고방식 2

https://hwk99.tistory.com/214

인덱스 설계 사고방식 1 (GPT 추천 문제)

 

복합 인덱스 설계 사고방식 2

BETWEEN / LIKE / IN 이 들어오면 인덱스는 어떻게 깨지는가

앞선 글에서는
WHERE의 동등 조건(=) + ORDER BY / GROUP BY 기준으로
복합 인덱스 순서를 어떻게 잡아야 하는지를 정리했다.

이번에는 실무에서 인덱스를 망가뜨리는 주범인
BETWEEN, < >, LIKE, IN 이 등장했을 때
인덱스 설계 사고방식이 어떻게 달라지는지를 정리한다.

---

1. 핵심 결론부터

범위 조건(BETWEEN, <, >, LIKE)이 등장하는 순간
인덱스 탐색은 그 컬럼에서 사실상 끝난다.
그 뒤에 오는 컬럼들은 성능에 거의 기여하지 못한다.

그래서 범위 조건 컬럼은 반드시 인덱스의 뒤쪽에 배치해야 한다.

---

2. 복합 인덱스가 깨지는 원리

복합 인덱스는 항상 왼쪽부터 순서대로 탐색된다.

하지만 범위 조건이 나오면 상황이 바뀐다.

• = : 정확히 한 지점으로 점프
• BETWEEN / < / > / LIKE : 범위 전체를 스캔

👉 이 순간부터
👉 뒤 컬럼은 정렬된 상태가 아니게 된다

즉, 인덱스 탐색의 “정밀 구간”이 끝난다.

3. BETWEEN / < / > 가 포함된 경우

WHERE COMPCD = ?
  AND YEARXX = ?
  AND DOCSEQ BETWEEN 100 AND 200
  AND PLCBUS = ?

❌ 잘못된 인덱스 예

(COMPCD, YEARXX, DOCSEQ, PLCBUS)

무슨 일이 벌어지나?

• COMPCD → OK
• YEARXX → OK
• DOCSEQ → 범위 스캔 시작
• PLCBUS → ❌ 인덱스 탐색 불가

👉 PLCBUS 조건은 인덱스 필터가 아니라 후처리가 된다.

---

✅ 올바른 인덱스

(COMPCD, YEARXX, PLCBUS, DOCSEQ)

• = 조건 먼저
• 범위 조건은 항상 마지막

---

4. LIKE는 사실상 범위 조건이다

4-1. LIKE 'ABC%' (앞이 고정된 경우)

WHERE USERID LIKE 'ABC%'

 

 

• 인덱스 사용 가능
• 하지만 내부적으로는 범위 조건으로 처리
• 뒤 컬럼은 탐색 불가

👉 BETWEEN 'ABC' AND 'ABD' 와 거의 동일한 취급

---

4-2. LIKE '%ABC' (앞이 열려 있는 경우)

WHERE USERID LIKE '%ABC'

• 인덱스 거의 사용 불가
• 복합 인덱스 설계로 해결 ❌

이 경우 선택지는:

• 별도 검색 컬럼
• 역문자열 컬럼
• FULLTEXT
• 검색 전용 테이블

---

5. IN 은 애매한 존재다

WHERE PLCBUS IN ('P1', 'P2', 'P3')

IN은 DB 내부에서 보통 이렇게 풀린다.

PLCBUS='P1' OR
PLCBUS='P2' OR
PLCBUS='P3'

실무 기준

• IN 개수 적음(2~5개) → =에 가깝게 동작
• IN 개수 많음(수십~수백) → 사실상 범위 스캔

👉 그래서 인덱스 순서는 보통:

= 조건 → IN(소량) → GROUP BY → 범위 조건

---

6. GROUP BY + BETWEEN 이 섞일 때 (중요)

쿼리

 

SELECT PLCBUS, COUNT(*)
FROM HERGMT
WHERE COMPCD = ?
  AND YEARXX BETWEEN 2020 AND 2024
GROUP BY PLCBUS;

❌ 잘못된 인덱스 예

(COMPCD, YEARXX, PLCBUS)

• YEARXX가 범위
• PLCBUS는 정렬되지 않음
• GROUP BY 시 filesort / temp 발생

---

✅ 올바른 인덱스

(COMPCD, PLCBUS, YEARXX)

GROUP BY 컬럼은
범위 조건보다 반드시 앞에 와야 의미가 있다

---

7. 커서 페이징과 범위 조건의 결합

쿼리

SELECT *
FROM HERGMT
WHERE COMPCD = ?
  AND PLCBUS = ?
  AND YEARXX = ?
  AND DOCSEQ < ?
ORDER BY DOCSEQ DESC
LIMIT 30;

(COMPCD, PLCBUS, YEARXX, DOCSEQ)

• DOCSEQ는 범위 + 정렬
• 인덱스의 마지막 컬럼

이 구조에서 DB는:

• 정확한 위치로 점프
• 아래로 30건만 읽고 종료

---

8. 인덱스 순서 결정 공식 (완성판)

1. WHERE = 조건 (항상 들어오는 것)
2. WHERE IN (소량)
3. GROUP BY 컬럼
4. WHERE BETWEEN / < / > / LIKE
5. ORDER BY / 커서 / LIMIT 컬럼

❗ 4번이 등장하는 순간
그 뒤 컬럼은 “정밀 탐색 불가”

---

9. 한 문장으로 정리

범위 조건은 인덱스 탐색의 종착역이다.
그 뒤에 무엇을 두든, 성능에는 거의 기여하지 못한다.

---

마무리

질문1. between 조건을 아애 제거해도 상관 없는것 아닌지

>> 경우에 따라서는 BETWEEN 컬럼을 인덱스에 아예 안 넣는 게 더 나을 수도 있다.

 

BETWEEN 컬럼을 인덱스에 안 넣어도 되는 경우

1. 앞쪽 = 조건만으로 이미 충분히 좁아질 때

2. BETWEEN 범위가 너무 넓을 때

3. GROUP BY 컬럼이 더 중요한 경우

 

그래도 BETWEEN 컬럼을 넣어야 하는 경우

1.BETWEEN이 “실질적인 필터”일 때

2. 커서 페이징 범위 조건일 때

실무용 의사결정 표

범위가 매우 넓음	❌ 빼도 됨
범위가 사실상 =	✅ 넣음
앞쪽 조건만으로 충분히 좁음	❌ 가능
GROUP BY 대상 줄여야 함	✅ 고려
커서 페이징 범위	✅ 필수

 

질문2. filesort 이란?

filesort는 MySQL/MariaDB에서 말하는 용어로,

인덱스를 이용해서 정렬하지 못해서
DB가 “따로 정렬 작업”을 수행한다는 뜻