Database

MySQL 인덱스 정리 및 설계 원칙

MySQLIndex
목차

데이터베이스를 공부하다 보면 가장 자주 듣는 단어 중 하나가 인덱스(Index)입니다. 인덱스는 조회 성능을 높이기 위한 대표적인 수단이지만, 단순히 “조회가 빨라진다” 정도로만 이해하면 실제 설계에서는 자주 실수하게 됩니다.

이번 글에서는 MySQL의 B-Tree 인덱스를 기준으로, 인덱스의 개념과 구조, 설계 원칙, 그리고 사용할 때 주의해야 할 점들을 순서대로 정리해보겠습니다. 실무 팁 위주로 흩어져 있는 내용이 아니라, 가능한 한 개념과 원리를 중심으로 설명하겠습니다.

1. 인덱스란 무엇인가

인덱스는 테이블의 특정 컬럼 값을 기준으로 정렬된 탐색 구조를 별도로 유지하여, 원하는 데이터를 더 빠르게 찾을 수 있도록 하는 자료구조입니다.

책의 맨 뒤에 있는 색인(index)을 떠올리면 이해하기 쉽습니다. 책 전체를 처음부터 끝까지 읽지 않아도, 찾고 싶은 단어가 어느 페이지에 있는지 빠르게 찾을 수 있습니다. 데이터베이스의 인덱스도 같은 역할을 합니다.

인덱스를 사용하면 SELECT 성능은 대체로 향상됩니다. 반면 INSERT, UPDATE, DELETE는 데이터를 변경할 때 인덱스도 함께 갱신해야 하므로 추가 비용이 발생합니다.

즉, 인덱스는 다음과 같은 trade-off를 가집니다.

  • 조회 성능은 좋아진다
  • 쓰기 성능은 일정 부분 희생된다
  • 저장 공간이 추가로 필요하다

따라서 인덱스는 많을수록 좋은 것이 아니라, 자주 사용되는 조회 패턴에 맞게 필요한 만큼만 설계하는 것이 중요합니다.

2. B-Tree 인덱스의 기본 구조

MySQL의 InnoDB에서 일반적으로 사용하는 인덱스는 B-Tree 계열 구조입니다. 이 구조는 대체로 Root, Branch, Leaf 형태로 이루어집니다.

탐색 과정은 보통 다음 순서로 진행됩니다.

  1. Root 페이지에서 시작한다
  2. 조건에 맞는 Branch 페이지를 찾는다
  3. Leaf 페이지에서 원하는 키를 찾는다
  4. 필요한 경우 실제 데이터 페이지에 접근한다

이 구조의 핵심은 “정렬된 상태를 유지한다”는 점입니다. 그래서 전체를 순차 탐색하지 않고도 특정 범위를 빠르게 좁혀갈 수 있습니다.

결국 인덱스 성능은 다음과 연결됩니다.

  • 탐색 범위를 얼마나 빨리 줄일 수 있는가
  • 디스크 페이지 접근 횟수를 얼마나 줄일 수 있는가

메모리 접근보다 디스크 접근이 훨씬 느리기 때문에, 인덱스의 본질은 결국 불필요한 디스크 읽기를 줄이는 데 있다고 볼 수 있습니다.

3. 클러스터드 인덱스와 세컨더리 인덱스

InnoDB를 기준으로 보면, 인덱스는 크게 클러스터드 인덱스(Clustered Index)와 세컨더리 인덱스(Secondary Index)로 나누어 이해하는 것이 좋습니다.

클러스터드 인덱스는 테이블의 실제 데이터가 어떤 기준으로 정렬되어 저장되는지를 결정하는 인덱스입니다. InnoDB에서는 기본 키(PK)를 기준으로 데이터가 정렬되어 저장되므로, 실무에서는 보통 “PK가 곧 클러스터드 인덱스”라고 설명합니다.

InnoDB 클러스터드 인덱스의 Leaf 페이지에는 인덱스 키뿐 아니라 실제 행 데이터 자체가 함께 저장됩니다. 그래서 기본 키로 조회하는 경우에는 Leaf 단계에서 바로 실제 데이터에 도달할 수 있습니다.

반면 세컨더리 인덱스는 기본 키 이외의 컬럼에 생성되는 인덱스입니다. InnoDB의 세컨더리 인덱스 Leaf에는 실제 레코드 위치 자체가 직접 저장되는 것이 아니라, 해당 행의 기본 키 값이 함께 저장됩니다.

그래서 세컨더리 인덱스를 이용한 조회는 보통 다음 순서로 진행됩니다.

  1. 세컨더리 인덱스에서 조건에 맞는 키를 찾는다
  2. Leaf에 저장된 기본 키 값을 확인한다
  3. 기본 키를 이용해 클러스터드 인덱스를 다시 조회한다

이 과정을 흔히 “한 번 더 찾아간다”는 의미에서 추가 탐색 비용으로 설명합니다.

따라서 InnoDB에서 인덱스를 이해할 때는 다음 구분이 중요합니다.

  • 기본 키 조회: 비교적 직접적으로 데이터에 접근한다
  • 세컨더리 인덱스 조회: 필요에 따라 기본 키를 통한 추가 조회가 발생할 수 있다

이 차이는 뒤에서 설명할 커버링 인덱스를 이해할 때도 중요한 배경이 됩니다.

4. 인덱스는 왜 쓰기 성능에 부담이 되는가

인덱스가 있는 컬럼에 새로운 값이 들어오면, 데이터베이스는 테이블에 행만 추가하는 것이 아니라 인덱스 구조에도 그 값을 반영해야 합니다.

예를 들어 다음과 같은 일이 발생합니다.

  • INSERT: 새 키를 인덱스에 삽입해야 한다
  • UPDATE: 기존 키를 제거하고 새 키를 다시 반영해야 할 수 있다
  • DELETE: 인덱스에서도 해당 키를 제거해야 한다

따라서 인덱스는 조회를 빠르게 만들어주지만, 데이터 변경이 잦은 테이블에서는 오히려 부담이 될 수 있습니다.

이 때문에 인덱스 설계에서는 다음 질문이 항상 필요합니다.

  • 이 인덱스가 실제로 자주 사용되는가
  • 조회 이점이 쓰기 비용보다 충분히 큰가
  • 중복되거나 불필요한 인덱스는 아닌가

5. 인덱스 키 크기와 성능의 관계

InnoDB는 데이터를 페이지(Page) 단위로 관리합니다. 페이지 크기는 기본적으로 16KB입니다. 인덱스 역시 페이지 단위로 저장됩니다.

여기서 중요한 점은 인덱스 키가 길어질수록, 하나의 페이지에 저장할 수 있는 엔트리 수가 줄어든다는 것입니다.

예를 들어 같은 16KB 페이지라도

  • 키가 짧으면 더 많은 엔트리를 담을 수 있고
  • 키가 길면 더 적은 엔트리만 담을 수 있습니다

이 차이는 곧 탐색 효율 차이로 이어집니다. 같은 수의 데이터를 찾더라도 더 많은 페이지를 읽어야 할 가능성이 높아지기 때문입니다.

따라서 인덱스 컬럼은 가능하면 다음 조건을 만족하는 것이 좋습니다.

  • 지나치게 길지 않을 것
  • 불필요하게 큰 문자열 컬럼을 직접 인덱싱하지 않을 것
  • 꼭 필요한 경우가 아니라면 키 크기를 최소화할 것

즉, 인덱스 키는 “찾기 좋은 값”이어야 할 뿐 아니라 “저장 효율이 좋은 값”이어야 합니다.

6. 카디널리티와 선택도

인덱스를 설명할 때 자주 등장하는 개념이 카디널리티(Cardinality)입니다.

카디널리티는 일반적으로 어떤 컬럼이 가질 수 있는 서로 다른 값의 수, 또는 값의 다양성을 의미합니다.

예를 들어

  • 성별: 서로 다른 값이 매우 적다 → 카디널리티가 낮다
  • 주민등록번호, 이메일: 서로 다른 값이 매우 많다 → 카디널리티가 높다

단일 컬럼 인덱스를 고려할 때 카디널리티가 높은 컬럼이 유리한 경우가 많습니다. 왜냐하면 조건을 걸었을 때 더 많은 행을 걸러낼 가능성이 크기 때문입니다.

다만 여기서 함께 알아야 할 개념이 선택도(Selectivity)입니다.

선택도는 특정 조건이 전체 행 중 얼마나 적은 결과만 남기는지를 의미합니다. 즉, 실제 조회에서 얼마나 잘 걸러지느냐에 더 가까운 개념입니다.

카디널리티와 선택도는 관련은 있지만 항상 같지는 않습니다.

예를 들어 어떤 컬럼이 서로 다른 값의 수는 많더라도, 특정 값 하나에 데이터가 과도하게 몰려 있다면 그 값에 대한 조회 선택도는 낮을 수 있습니다.

따라서 실무에서는 다음처럼 이해하는 것이 더 정확합니다.

  • 카디널리티는 컬럼의 다양성
  • 선택도는 조건의 필터링 효율
  • 인덱스 효율은 둘 다 영향을 받는다

7. 단일 컬럼 인덱스의 기준

하나의 컬럼에만 인덱스를 걸어야 한다면, 일반적으로는 선택도가 좋고 값의 다양성이 큰 컬럼이 우선 검토 대상이 됩니다.

예를 들어

  • gender = 'M'은 전체 데이터의 절반 가까이를 반환할 수 있습니다
  • email = 'abc@company.com'은 거의 1건만 반환할 가능성이 높습니다

이 경우 후자가 인덱스를 통해 더 큰 효과를 볼 가능성이 큽니다.

하지만 이것을 절대 규칙처럼 받아들이면 안 됩니다. 실제 인덱스 설계는 컬럼 자체보다도 “어떤 쿼리가 얼마나 자주 실행되는가”에 더 크게 좌우됩니다.

즉, 좋은 인덱스란 단순히 카디널리티가 높은 인덱스가 아니라, 자주 실행되는 조건절을 효율적으로 지원하는 인덱스입니다.

8. 복합 인덱스와 컬럼 순서

복합 인덱스는 두 개 이상의 컬럼을 하나의 인덱스로 구성한 것입니다.

예를 들어 다음과 같은 인덱스를 생각해볼 수 있습니다.

INDEX (group_no, from_date, is_bonus)

이 인덱스는 단순히 세 컬럼을 묶어둔 것이 아니라, 다음과 같은 순서로 정렬된 구조를 갖습니다.

  1. 먼저 group_no
  2. 같은 group_no 안에서 from_date
  3. 같은 group_no, from_date 안에서 is_bonus

즉, 복합 인덱스에서는 컬럼 순서가 매우 중요합니다.

여기서 흔히 알려진 원칙은 “카디널리티가 높은 컬럼을 앞에 둔다”는 것입니다. 이 원칙은 종종 유효하지만, 언제나 정답은 아닙니다.

복합 인덱스 순서를 정할 때는 보통 다음 요소를 함께 고려해야 합니다.

  • 자주 사용되는 WHERE 조건
  • = 조건이 걸리는 컬럼
  • 범위 조건이 걸리는 컬럼
  • ORDER BY / GROUP BY에 사용되는 컬럼
  • 실제 데이터 분포

즉, 복합 인덱스는 단순히 “중복이 적은 컬럼부터”가 아니라, “실제 쿼리 패턴에 가장 잘 맞는 순서”로 설계해야 합니다.

9. 선행 컬럼과 Leftmost Prefix 원칙

복합 인덱스에서 가장 중요한 개념 중 하나는 선행 컬럼입니다.

예를 들어 인덱스가 다음과 같다고 가정하겠습니다.

INDEX (group_no, from_date, is_bonus)

이 경우 데이터베이스는 앞쪽부터 정렬된 구조를 이용합니다. 그래서 보통 다음과 같은 조건은 효율적으로 사용할 수 있습니다.

  • group_no
  • group_no, from_date
  • group_no, from_date, is_bonus

반면 맨 앞 컬럼인 group_no 없이 뒤쪽 컬럼만 사용하는 조건은 효율이 크게 떨어질 수 있습니다.

즉, 복합 인덱스는 “앞에서부터 이어지는 조건”일수록 활용도가 높습니다. 이 원칙을 흔히 Leftmost Prefix Rule이라고 설명합니다.

실무에서 복합 인덱스가 기대만큼 성능을 내지 못하는 이유도 대부분 여기에 있습니다. 인덱스는 만들어졌지만, 실제 쿼리가 그 순서를 제대로 활용하지 못하는 것입니다.

10. 범위 조건이 들어가면 왜 뒤 컬럼 활용이 제한되는가

복합 인덱스에서 =, IN 같은 동등 비교 조건은 다음 컬럼까지 비교적 안정적으로 이어질 수 있습니다. 하지만 BETWEEN, LIKE 'abc%', <, > 같은 범위 조건이 등장하면 이야기가 달라집니다.

예를 들어 인덱스가 다음과 같다고 해보겠습니다.

INDEX (group_no, from_date, is_bonus)

그리고 쿼리가 다음과 같다면

WHERE group_no = 'A'
  AND from_date > '2024-01-01'
  AND is_bonus = 1

group_no까지는 명확하게 좁힐 수 있지만, from_date에서 범위 탐색이 시작되기 때문에 그 뒤의 is_bonus는 인덱스 활용도가 크게 제한될 수 있습니다.

이것은 복합 인덱스 설계에서 매우 중요한 포인트입니다. 실무에서는 범위 조건이 자주 등장하는 컬럼의 위치를 신중히 선택해야 합니다.

11. 커버링 인덱스

실무에서 자주 등장하지만 초기에 놓치기 쉬운 개념이 커버링 인덱스(Covering Index)입니다.

커버링 인덱스란, 어떤 조회에 필요한 모든 컬럼이 인덱스 안에만 포함되어 있어서 테이블 본문 데이터까지 다시 읽지 않아도 되는 경우를 말합니다.

예를 들어 다음과 같은 인덱스가 있다고 가정하겠습니다.

INDEX (group_no, from_date, is_bonus)

그리고 쿼리가 다음과 같다면

SELECT group_no, from_date, is_bonus
FROM salaries
WHERE group_no = 'A'
  AND from_date = '2024-01-01';

조회에 필요한 컬럼이 모두 인덱스 안에 있기 때문에, 데이터베이스는 인덱스만 읽고 결과를 반환할 수 있습니다.

반면 아래와 같이 인덱스에 없는 컬럼을 추가로 조회하면

SELECT group_no, from_date, is_bonus, salary
FROM salaries
WHERE group_no = 'A'
  AND from_date = '2024-01-01';

salary 값을 얻기 위해 실제 데이터 페이지에 다시 접근해야 할 수 있습니다.

InnoDB에서 커버링 인덱스가 중요한 이유는 세컨더리 인덱스 조회 시 발생할 수 있는 추가 탐색 비용을 줄여주기 때문입니다.

즉, 커버링 인덱스는 단순히 “인덱스를 사용했다”는 것보다 한 단계 더 나아가, “인덱스만으로 조회를 끝낼 수 있다”는 점에서 의미가 있습니다.

다만 커버링 인덱스를 위해 조회 컬럼을 무조건 많이 인덱스에 넣는 것은 바람직하지 않습니다. 인덱스 크기가 커지고 쓰기 비용도 증가하기 때문입니다.

따라서 커버링 인덱스는 자주 실행되는 핵심 조회를 대상으로, 비용 대비 효과가 충분할 때 선택적으로 고려하는 것이 좋습니다.

12. 인덱스를 못 타게 만드는 대표적인 경우

인덱스가 존재하더라도, 쿼리 작성 방식에 따라 인덱스를 제대로 활용하지 못하는 경우가 있습니다.

대표적인 예는 다음과 같습니다.

12-1. 컬럼에 연산이나 함수를 적용하는 경우

WHERE salary * 10 > 150000

이 경우 데이터베이스는 인덱스에 저장된 원래 값이 아니라 계산 결과를 비교해야 하므로 인덱스를 효율적으로 사용하기 어렵습니다.

가능하면 다음처럼 상수를 변형하는 방식이 낫습니다.

WHERE salary > 15000

12-2. 타입이 맞지 않는 경우

문자열 컬럼인데 숫자로 비교하거나, 숫자 컬럼인데 문자열 형태로 비교하면 내부 변환이 일어나면서 인덱스 사용이 비효율적일 수 있습니다.

12-3. LIKE 검색에서 와일드카드가 앞에 오는 경우

LIKE 'abc%'는 앞부분이 고정되어 있으므로 인덱스를 활용할 수 있지만, LIKE '%abc'는 선행 문자가 정해지지 않아 인덱스를 효율적으로 사용할 수 없습니다.

12-4. OR 조건이 과도한 경우

AND는 대개 결과 범위를 줄이는 방향으로 작동하지만, OR는 비교 범위를 넓혀 전체 스캔 쪽으로 기울게 만들 수 있습니다.

물론 모든 OR가 나쁜 것은 아니지만, 인덱스 활용 측면에서는 주의가 필요합니다.

13. 조회 조건의 순서보다 중요한 것

초기 학습 단계에서는 WHERE절의 컬럼 순서가 인덱스 순서와 일치해야 한다고 오해하기 쉽습니다. 하지만 현대 옵티마이저는 조건식을 재배열할 수 있기 때문에, SQL 문장에 적는 조건 순서 자체가 본질은 아닙니다.

단, 이것은 SQL에 작성하는 조건의 순서 이야기입니다. 인덱스 자체의 컬럼 순서는 옵티마이저가 바꿔주지 않습니다. 앞서 설명한 Leftmost Prefix, 범위 조건 제한 등은 인덱스를 어떤 구조로 설계하느냐의 문제이고, 이 부분은 여전히 중요합니다.

더 중요한 것은 다음입니다.

  • 인덱스 컬럼이 조회 조건에 포함되어 있는가
  • 선행 컬럼을 활용하고 있는가
  • 범위 조건이 어디에서 등장하는가
  • 정렬이나 그룹핑과 인덱스 방향이 맞는가

즉, 사람이 SQL을 적는 순서보다 데이터베이스가 활용할 수 있는 인덱스 구조가 더 중요합니다.

14. 인덱스 설계는 규칙 암기가 아니라 검증 과정이다

인덱스에 관한 글을 읽다 보면 다음과 같은 문장을 자주 만나게 됩니다.

  • 카디널리티 높은 컬럼이 좋다
  • 인덱스는 3~4개가 적당하다
  • 첫 번째 컬럼이 가장 중요하다

이 말들은 방향성으로는 도움이 되지만, 절대 법칙은 아닙니다.

실제 인덱스 설계는 항상 다음 과정을 거쳐야 합니다.

  1. 자주 실행되는 쿼리를 파악한다
  2. WHERE / JOIN / ORDER BY / GROUP BY를 확인한다
  3. 후보 인덱스를 설계한다
  4. 실행 계획을 본다
  5. 실제 성능을 측정한다
  6. 유지 비용까지 함께 판단한다

즉, 인덱스는 “이론적으로 좋아 보이는 구조”보다 “내 서비스의 실제 쿼리에서 검증된 구조”가 중요합니다.

실제로 이 검증 과정에서 가장 많이 사용하는 도구가 EXPLAIN입니다. EXPLAIN은 특정 쿼리를 실행했을 때 MySQL이 어떤 방식으로 테이블을 읽을지, 어떤 인덱스를 사용할지, 얼마나 많은 행을 읽을 것으로 예상하는지를 보여줍니다.

예를 들어 다음과 같은 쿼리가 있다고 해보겠습니다.

EXPLAIN
SELECT group_no, from_date, is_bonus
FROM salaries
WHERE group_no = 'A'
  AND from_date = '2024-01-01';

실행 계획에서는 보통 다음 항목들을 먼저 확인합니다.

  • type: 어떤 방식으로 접근하는가
  • key: 어떤 인덱스를 사용했는가
  • rows: 몇 건 정도를 읽을 것으로 예상하는가
  • Extra: 추가 정보가 무엇인가

예를 들어 key에 기대한 인덱스가 잡히는지, rows가 지나치게 크게 잡히지 않는지, Extra에 Using index가 표시되는지를 보면 커버링 인덱스 여부나 조회 효율을 빠르게 가늠할 수 있습니다.

따라서 인덱스 설계는 단순히 DDL을 작성하는 것으로 끝나는 것이 아니라, 반드시 실행 계획과 실제 실행 시간까지 함께 확인하는 과정으로 이어져야 합니다.

마무리

인덱스는 데이터베이스 성능을 좌우하는 핵심 요소이지만, 동시에 가장 쉽게 오해되는 주제이기도 합니다.

정리하면 다음과 같습니다.

  • 인덱스는 조회를 빠르게 하지만 유지 비용이 든다
  • B-Tree 인덱스는 정렬된 구조를 통해 탐색 범위를 줄인다
  • 키 크기가 커질수록 페이지 효율은 떨어진다
  • 단일 인덱스에서는 선택도가 좋은 컬럼이 유리한 경우가 많다
  • 복합 인덱스에서는 컬럼 순서와 선행 컬럼이 중요하다
  • 범위 조건 이후의 컬럼은 활용이 제한될 수 있다
  • 함수 적용, 타입 불일치, 과도한 OR는 인덱스 활용을 방해할 수 있다
  • 결국 좋은 인덱스는 실제 쿼리와 실행 계획으로 검증해야 한다

인덱스를 잘 만든다는 것은 단순히 “많이 만든다”는 뜻이 아닙니다. 자주 사용하는 조회를 정확히 이해하고, 그 흐름에 맞는 탐색 구조를 설계하는 것입니다.

그래서 인덱스 공부는 문법 공부가 아니라, 결국 조회 패턴을 읽는 공부에 가깝습니다.

다음 단계로 더 깊이 들어가고 싶다면, 실행 계획 분석법, 인덱스 힌트의 올바른 사용, 정렬과 그룹핑에서의 인덱스 활용까지 이어서 살펴보는 것이 좋습니다.