MySQL 인덱스 설계 가이드: 컬럼보다 쿼리 패턴부터 봐야 하는 이유
DB
마지막 업데이트

MySQL 인덱스 설계 가이드: 컬럼보다 쿼리 패턴부터 봐야 하는 이유

MySQL 성능을 조금만 다뤄도 곧바로 “인덱스를 추가하자”는 이야기가 나옵니다. 문제는 여기서 많은 팀이 컬럼 단위로 생각을 멈춘다는 점입니다. 운영하던 주문 조회 API가 피크 타임에 3초를 넘기길래 EXPLAIN을 찍어 봤더니, user_id, status, created_at에 단일 인덱스가 각각 있는데 옵티마이저가 user_id 인덱스만 타고 나머지는 filesort로 처리하고 있었습니다. (user_id, status, created_at) 복합 인덱스 하나로 교체하니 rows가 12만에서 20으로 떨어지고 응답 시간이 80ms로 줄었습니다. user_id에 인덱스가 있나, status에도 하나 만들까, created_at도 붙일까 같은 식입니다.

실제 성능 문제는 그렇게 단순하지 않습니다. 인덱스는 컬럼 하나에 체크하는 옵션이 아니라, MySQL이 어떤 순서로 어떤 범위를 읽을지 설계하는 접근 경로의 일부입니다. 그래서 인덱스 설계는 “어떤 컬럼에 붙일까”보다 “어떤 쿼리 패턴을 어떻게 짧게 읽게 만들까”에서 시작해야 합니다.

이 글에서는 아래를 한 번에 정리합니다.

  • 인덱스를 컬럼이 아니라 쿼리 기준으로 봐야 하는 이유
  • WHERE, JOIN, ORDER BY, GROUP BY를 어떻게 함께 생각해야 하는지
  • 복합 인덱스 순서를 어떻게 잡는지
  • leftmost prefix를 왜 이해해야 하는지
  • 낮은 선택도, 중복 인덱스, 과한 인덱스가 왜 문제인지
  • 인덱스를 만든 뒤 EXPLAIN 으로 무엇을 확인해야 하는지

한 줄 요약은 이렇습니다. 좋은 인덱스는 “컬럼을 많이 덮는 인덱스”가 아니라, 실제로 자주 실행되는 쿼리를 더 적은 row로 끝내게 만드는 인덱스입니다.

Quick answer

실무에서는 아래 순서로 인덱스를 설계하는 편이 가장 안전합니다.

  1. 느리거나 자주 실행되는 실제 쿼리를 먼저 고릅니다.
  2. 그 쿼리가 WHERE, JOIN, ORDER BY를 어떤 조합으로 쓰는지 봅니다.
  3. equality 조건, range 조건, 정렬 조건의 순서를 분리해서 생각합니다.
  4. 복합 인덱스가 필요하다면 leading column이 가장 자주 좁혀주는 패턴에 맞는지 봅니다.
  5. 기존 인덱스와 중복되거나 거의 같은 역할을 하는 인덱스가 없는지 확인합니다.
  6. 읽기 성능뿐 아니라 INSERT, UPDATE, DELETE 비용도 같이 봅니다.
  7. 마지막으로 EXPLAIN 과 실제 latency로 전후를 검증합니다.

즉 인덱스 설계의 핵심은 “컬럼 후보를 나열하는 일”이 아니라 읽는 양, 정렬 비용, 조인 비용을 줄이는 하나의 쿼리 경로를 설계하는 일입니다.

1. 인덱스는 왜 중요한가

인덱스가 없으면 MySQL은 원하는 row를 찾기 위해 훨씬 넓은 범위를 읽어야 할 수 있습니다. 인덱스가 적절하면 읽는 시작점과 범위를 더 빨리 좁힐 수 있습니다.

중요한 건 인덱스의 목적을 “검색 속도 향상” 정도로만 보면 약간 아쉽다는 점입니다. 인덱스의 더 정확한 역할은 보통 아래에 가깝습니다.

  • 불필요하게 읽는 row 수를 줄인다
  • 정렬 비용을 줄이거나 피하게 만든다
  • 조인 시 상대 테이블을 더 빠르게 찾게 만든다
  • 때로는 테이블 본문 접근까지 줄인다

즉 인덱스는 단순히 빠른 lookup용 기능이 아니라, 쿼리 전체 비용 구조를 바꾸는 도구입니다.

2. 인덱스는 컬럼이 아니라 쿼리 패턴 기준으로 설계해야 한다

입문자가 가장 자주 빠지는 함정은 “자주 나오는 컬럼마다 하나씩 인덱스를 만든다”는 접근입니다. 그런데 실제 쿼리는 대부분 컬럼 하나만 쓰지 않습니다.

예를 들어 이런 패턴을 보겠습니다.

SELECT id, created_at, total_amount
FROM orders
WHERE user_id = 42
  AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;

이 쿼리를 놓고:

  • user_id 인덱스 하나
  • status 인덱스 하나
  • created_at 인덱스 하나

를 따로 만드는 것이 정답은 아닐 수 있습니다.

이 쿼리의 핵심은:

  • user_id, status 로 범위를 좁히고
  • 그 안에서 created_at DESC 로 정렬하며
  • LIMIT 20 으로 앞부분만 빨리 가져오고 싶다는 것

입니다.

즉 여기서는 “각 컬럼에 인덱스가 있느냐”보다 이 세 조건이 합쳐진 실제 읽기 경로를 잘 지원하느냐가 더 중요합니다.

그래서 인덱스 설계는 거의 항상 이 질문에서 시작해야 합니다.

  • 어떤 쿼리가 실제로 느린가
  • 어떤 쿼리가 자주 호출되는가
  • 그 쿼리는 어떤 조건과 정렬을 같이 쓰는가

느린 쿼리를 좁히는 전체 흐름은 MySQL Query Optimization Checklist와 함께 보면 더 잘 이어집니다.

3. 좋은 인덱스 후보는 어디에서 찾나

후보를 찾을 때는 보통 아래 위치를 먼저 봅니다.

  • WHERE 조건
  • JOIN 조건
  • ORDER BY
  • GROUP BY

하지만 여기서 바로 “그러면 다 인덱스”로 가면 안 됩니다. 같은 위치에 있더라도 효과는 꽤 다릅니다.

예를 들어:

  • WHERE user_id = ? 는 좋은 후보일 수 있습니다
  • WHERE status = 'active' 는 데이터 분포에 따라 별 도움이 안 될 수 있습니다
  • ORDER BY created_at DESC 는 단독으로는 애매하지만, 필터와 결합되면 매우 중요해질 수 있습니다

즉 좋은 후보를 판단할 때는 위치만이 아니라 아래 세 가지를 같이 봐야 합니다.

  • 자주 쓰이는가
  • 충분히 좁혀주는가
  • 다른 조건과 함께 한 쿼리 안에서 반복되는가

4. WHERE 조건에서는 “얼마나 잘 좁혀주는가”가 중요하다

필터 조건에 자주 쓰인다고 다 좋은 인덱스 후보는 아닙니다. 특히 선택도가 낮은 컬럼은 기대만큼 효과가 없을 수 있습니다.

예를 들어 대부분 row가 같은 값을 가진다면:

WHERE status = 'active'

이 조건만으로는 읽는 양이 크게 줄지 않을 수 있습니다.

그래서 WHERE 조건에서 중요한 질문은:

  • 이 조건이 실제로 읽는 범위를 많이 줄이는가
  • 다른 조건과 함께 반복적으로 등장하는가

입니다.

보통은 다음이 더 강한 후보가 됩니다.

  • 사용자별 데이터처럼 잘 나뉘는 값
  • 특정 주문, 특정 테넌트, 특정 계정처럼 범위를 크게 줄이는 값
  • 조인과 정렬에서 반복적으로 재사용되는 값

즉 WHERE 인덱스는 “필터가 있다”보다 필터가 얼마나 강하게 범위를 줄이느냐가 핵심입니다.

5. ORDER BY를 같이 보지 않으면 인덱스가 반쪽짜리가 된다

많은 팀이 WHERE까지만 보고 인덱스를 만듭니다. 그런데 실제 서비스 쿼리는 정렬이 같이 붙는 경우가 매우 많습니다.

예를 들어:

WHERE user_id = ?
ORDER BY created_at DESC
LIMIT 20

같은 쿼리에서는 단순히 user_id 로만 좁히는 것보다, 그 뒤 정렬까지 어떻게 처리되는지가 훨씬 중요할 수 있습니다. 필터는 빨라졌는데 Using filesort 가 크게 남으면 체감 성능이 기대보다 안 올라갈 수 있습니다.

그래서 인덱스는 자주 이런 질문으로 봐야 합니다.

  • 필터 후 정렬이 자주 같이 등장하는가
  • 정렬 기준이 항상 같은가
  • LIMIT 앞부분 몇 건을 빨리 가져오는 패턴인가

이런 경우 복합 인덱스가 단일 인덱스 여러 개보다 훨씬 유리할 수 있습니다.

즉 ORDER BY는 부가 조건이 아니라, 실제 인덱스 순서를 결정하는 핵심 요소일 때가 많습니다.

정렬 비용과 실행 계획 해석은 MySQL EXPLAIN 가이드에서 같이 보면 더 선명해집니다.

6. JOIN에서는 양쪽 접근 경로를 같이 봐야 한다

조인 성능이 나쁠 때 흔한 오해는 “한쪽만 인덱스 있으면 되겠지”입니다. 하지만 조인은 입력 집합이 커질수록 비용이 빠르게 불어나므로, 조인 키와 선행 필터를 같이 봐야 합니다.

특히 아래를 먼저 확인해야 합니다.

  • 조인에 쓰이는 키가 양쪽에서 잘 지원되는가
  • 먼저 읽는 테이블이 너무 큰가
  • 조인 전에 이미 row 수를 충분히 줄였는가

예를 들어 주문과 주문 아이템을 자주 붙인다면:

  • orders.user_id
  • order_items.order_id

같은 축이 자주 중요해집니다.

중요한 건 조인 인덱스가 단순히 “조인 키 하나”에만 있느냐가 아니라, 조인 이전의 필터와 조인 이후의 읽기 흐름 전체가 얼마나 작아지는가입니다.

조인 쿼리의 row 폭발은 MySQL JOIN 성능 가이드와 연결해서 보면 좋습니다.

7. 복합 인덱스는 “컬럼 순서”가 거의 전부다

복합 인덱스는 여러 컬럼을 묶어서 하나의 접근 경로를 설계하는 방식입니다. 그리고 이때 제일 중요한 게 컬럼 순서입니다.

실무에서는 자주 이런 기준으로 생각합니다.

  • equality 조건을 앞에 둘 수 있는가
  • 그 다음 range 조건이 오는가
  • 정렬 조건이 자연스럽게 이어지는가

예를 들어 이런 패턴이 반복된다면:

WHERE user_id = ?
  AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20

(user_id, status, created_at) 같은 구조를 검토해볼 수 있습니다.

물론 정답은 데이터 분포와 쿼리 종류에 따라 달라집니다. 하지만 중요한 건 아래입니다.

  • 자주 같이 등장하는 조건을 묶어야 한다
  • leading column이 충분히 좁혀주지 못하면 뒤 컬럼 효과가 약해질 수 있다
  • 정렬까지 도와줄 수 있다면 filesort 비용을 줄일 수 있다

복합 인덱스는 여러 단일 인덱스를 대신하는 마법이 아니라, 특정 쿼리 형태에 맞춘 정교한 경로 설계라고 보는 편이 좋습니다.

8. leftmost prefix를 이해하지 못하면 복합 인덱스를 자주 낭비한다

복합 인덱스를 만들고도 기대만큼 못 쓰는 이유 중 하나가 leftmost prefix 규칙을 놓치기 때문입니다.

예를 들어 (a, b, c) 인덱스가 있다면, 보통 앞쪽부터 이어지는 패턴에서 더 잘 활용됩니다.

즉 아래는 활용 가능성이 높습니다.

  • a
  • a, b
  • a, b, c

반면 아래는 기대보다 제약이 생길 수 있습니다.

  • b 만 보는 조건
  • c 중심 조건
  • 앞 컬럼을 건너뛴 접근

실전적으로 이해하면 이렇습니다.

  • 복합 인덱스는 뒤쪽 컬럼이 아무 때나 독립적으로 빛나는 구조가 아니다
  • 앞쪽 컬럼을 어떻게 쓰는지가 전체 효율을 좌우한다

그래서 복합 인덱스를 설계할 때는 “이 컬럼들이 자주 같이 나온다”만으로 부족하고, 그 쿼리들이 실제로 어떤 순서로 조건을 쓰는지를 같이 봐야 합니다.

9. 중복 인덱스와 비슷한 인덱스가 왜 위험한가

인덱스 설계를 하다 보면 기능이 비슷한 인덱스가 쌓이기 쉽습니다.

예를 들어:

  • (user_id)
  • (user_id, created_at)
  • (user_id, status, created_at)

처럼 겹치는 인덱스가 여러 개 생길 수 있습니다.

물론 다 의미가 있는 경우도 있지만, 상당수는 아래 문제를 만듭니다.

  • 쓰기 비용 증가
  • 저장 공간 증가
  • 옵티마이저 선택이 더 복잡해짐
  • 팀이 어떤 인덱스가 진짜 필요한지 파악하기 어려워짐

그래서 새 인덱스를 추가할 때는 꼭 같이 봐야 합니다.

  • 기존 인덱스로 이미 충분히 커버되는가
  • 새 인덱스가 정말 다른 쿼리 패턴을 지원하는가
  • 읽기 이득이 쓰기 비용을 상쇄하는가

인덱스 설계는 추가만이 아니라 정리와 통합의 작업이기도 합니다.

10. 인덱스는 읽기만 빠르게 하고 끝나는 게 아니다

인덱스는 읽기에는 도움이 되지만, 모든 쓰기 작업의 비용을 키웁니다.

  • INSERT: 새 row와 함께 인덱스 엔트리도 추가해야 함
  • UPDATE: 인덱스 컬럼이 바뀌면 인덱스 구조도 갱신해야 함
  • DELETE: 인덱스 엔트리도 함께 정리해야 함

그래서 트래픽이 쓰기 중심인 시스템에서는 “인덱스가 많을수록 좋다”가 쉽게 깨집니다.

특히 다음은 주의가 필요합니다.

  • 이벤트 로그처럼 insert가 많은 테이블
  • 상태 변경이 잦은 테이블
  • batch update가 많은 테이블

읽기 최적화만 보고 인덱스를 늘리면, 나중에는 write latency와 lock 부담이 커질 수 있습니다. 즉 인덱스는 읽기와 쓰기 사이의 명확한 trade-off 입니다.

lock 영향까지 함께 보려면 MySQL Lock Wait Timeout 가이드와 MySQL Deadlock 가이드가 자연스럽게 이어집니다.

11. 인덱스를 만든 뒤에는 반드시 EXPLAIN 으로 검증해야 한다

인덱스를 만들었다고 해서 MySQL이 꼭 그 인덱스를 기대한 방식으로 써준다는 보장은 없습니다.

그래서 추가 후에는 최소한 아래를 확인해야 합니다.

  • key 에 기대한 인덱스가 잡혔는가
  • rows 가 실제로 줄었는가
  • type 이 더 나아졌는가
  • Extra 의 Using filesort, Using temporary 같은 신호가 줄었는가

예상 인덱스를 만들었는데도 안 타는 경우는 꽤 흔합니다.

  • leading column 선택이 애매했거나
  • 선택도가 낮거나
  • 쿼리 패턴과 정렬 순서가 맞지 않거나
  • 더 넓은 다른 인덱스를 옵티마이저가 택했을 수 있습니다

즉 인덱스 설계의 마지막 단계는 생성이 아니라 검증입니다.

실행 계획 자체를 읽는 순서는 MySQL EXPLAIN 가이드에서 더 자세히 이어집니다.

12. 자주 하는 오해

1. 자주 쓰는 컬럼이면 무조건 인덱스가 필요하다

선택도가 낮거나, 실제 느린 쿼리의 핵심 병목이 정렬과 조인이라면 효과가 약할 수 있습니다.

2. 단일 인덱스 여러 개면 복합 인덱스와 비슷하다

실제 실행 경로는 전혀 다를 수 있습니다. 특히 정렬과 leading column 활용에서는 차이가 큽니다.

3. 인덱스가 많을수록 안전하다

읽기에는 좋아 보여도 쓰기 비용, 저장 공간, 운영 복잡도가 커집니다.

4. 인덱스를 만들었는데 key에 잡혔으니 끝이다

여전히 너무 많은 row를 읽을 수 있고, 정렬이나 임시 작업 비용이 남아 있을 수 있습니다.

FAQ

Q. 인덱스 설계는 어디서 시작하면 가장 좋을까요?

가장 느리거나 가장 자주 호출되는 실제 쿼리부터 시작하는 것이 좋습니다. 테이블 스키마만 보고 미리 많이 만드는 방식은 효율이 떨어지기 쉽습니다.

Q. 복합 인덱스 순서는 어떻게 정하나요?

보통 equality 조건, range 조건, 정렬 조건의 조합을 보고 leading column이 가장 자주 범위를 줄이는 쪽으로 검토합니다. 결국 실제 쿼리 패턴이 기준입니다.

Q. 인덱스가 도움이 되는지 어떻게 확인하나요?

EXPLAIN 으로 type, key, rows, Extra 를 비교하고, 실제 latency와 row read 변화까지 같이 보는 것이 좋습니다.

먼저 읽어볼 가이드

검색 유입이 많은 핵심 글부터 이어서 보세요.

광고

관련 글

이 카테고리의 다른 글