MySQL 단일 인덱스 vs 복합 인덱스: 인덱스 최적화 전략

쉬운코드 님의 CS가 개발에 중요한 이유 방송을 보면서 복합인덱스 관련 이야기가 나와서 혼자서 구글링 서칭하다가 알게된 점을 정리해서 생성형 AI한테 이야기하니 정리를 참 잘해준다. 몇번을 물어봐도 짜증 하나도 내지 않는 나의 최고의 선생님 감사합니다.

1. 개요

• 데이터베이스에서 인덱스(Index) 는 검색 성능을 최적화하는 중요한 요소다.
• 인덱스에는 단일 인덱스(각 컬럼별 개별 인덱스)와 복합 인덱스(여러 컬럼을 하나의 인덱스로 생성) 가 있으며, 각 방식은 장단점이 있다.
• 또한, 복합 인덱스를 사용할 때 컬럼 순서를 어떻게 배치하는지가 성능에 큰 영향을 미친다.
• 이 글에서는 단일 인덱스 vs 복합 인덱스 차이점과 복합 인덱스의 최적화 전략을 다룬다.

---

2. 단일 인덱스 vs 복합 인덱스 차이점

(1) 단일 인덱스

• 단일 컬럼에 대해 개별적으로 생성하는 인덱스.
• 컬럼 단독으로 검색하는 경우 활용할 수 있다.

예제

CREATE INDEX idx_a ON my_table(A);
CREATE INDEX idx_b ON my_table(B);

활용 가능한 쿼리

SELECT * FROM my_table WHERE A = '1';   -- idx_a 사용
SELECT * FROM my_table WHERE B = '2';   -- idx_b 사용
SELECT * FROM my_table WHERE A = '1' AND B = '2';   -- 선택도 높은 인덱스 하나만 사용

단점

• WHERE A='1' AND B='2' 같은 복합 조건에서는 선택도가 높은 하나의 인덱스만 사용하고, 나머지는 추가 필터링이 필요하다.
• 단일 인덱스 두 개를 병합하여 동시에 사용하는 경우는 제한적이다.

---

(2) 복합 인덱스

• 여러 개의 컬럼을 하나의 인덱스로 생성하는 방식.
• WHERE 조건이 특정 컬럼 조합으로 자주 사용될 경우 성능 향상 가능.

예제

CREATE INDEX idx_ab ON my_table(A, B);

활용 가능한 쿼리

SELECT * FROM my_table WHERE A = '1';   -- idx_ab 사용 (A를 이용한 검색 가능)
SELECT * FROM my_table WHERE A = '1' AND B = '2';   -- idx_ab 사용 (A → B 순으로 검색)

단점

• WHERE B='2' 와 같이 인덱스의 첫 번째 컬럼(A)이 없는 조건문에서는 사용되지 않음.

---

3. 복합 인덱스의 장점

(1) 선택도를 높여 검색 성능 향상

• WHERE A='1' AND B='2' 조건에서, 단일 인덱스는 선택도가 높은 한 개만 사용되지만 복합 인덱스는 A와 B를 함께 활용하여 검색 성능을 최적화할 수 있다.

(2) 고유성(Unique Constraint) 보장

• 두 컬럼의 조합이 항상 유일해야 하는 경우, UNIQUE INDEX를 통해 중복을 방지할 수 있다.

CREATE UNIQUE INDEX unique_ab ON my_table(A, B);

(3) 인덱스 커버링(커버드 인덱스)

• MySQL에서는 인덱스에 포함된 컬럼만으로 SELECT 결과를 반환할 수 있으면 테이블 조회를 생략할 수 있다.
• 예를 들어, 아래 쿼리는 idx_ab(A, B)가 있다면 테이블을 직접 조회하지 않고 인덱스에서 결과를 가져올 수 있다.

SELECT A, B FROM my_table WHERE A = '1';

---

4. 복합 인덱스 생성 전략 (Equal → Range 순서 적용)

• 복합 인덱스의 컬럼 순서는 성능에 직접적인 영향을 미친다.
• =(Equality) 조건 → 범위(Range) 조건 순서로 인덱스를 생성하는 것이 일반적으로 더 효율적이다.

(1) 왜 Equal 조건이 먼저 와야 하는가?

• 인덱스는 왼쪽에서 오른쪽으로 순차적으로 탐색하기 때문에, = 조건을 만족하는 데이터 범위를 먼저 좁히는 것이 중요하다.
• 이후 범위 검색(>, <, BETWEEN 등)은 좁혀진 데이터에서 수행되는 것이 성능적으로 더 유리하다.

(2) created_at과 user_id 복합 인덱스 비교

Case 1: (created_at, user_id) 순서

CREATE INDEX idx_created_user ON my_table(created_at, user_id);

• WHERE created_at > '2025-02-01' AND user_id = '1004'
  • created_at > '2025-02-01'이 범위 조건이므로 인덱스 탐색이 여기서 멈춘다.
  • user_id 조건이 제대로 활용되지 못할 가능성이 크다.

Case 2: (user_id, created_at) 순서 (추천)

CREATE INDEX idx_user_created ON my_table(user_id, created_at);

• WHERE user_id = '1004' AND created_at > '2025-02-01'
  • user_id = '1004' 를 먼저 찾아서 필터링 후, created_at 범위 조건을 적용.
  • 검색 범위가 좁아지므로 범위 스캔 비용이 줄어든다.

(3) 실행 계획 (EXPLAIN)을 활용한 검증

EXPLAIN SELECT * FROM my_table WHERE user_id = '1004' AND created_at > '2025-02-01';

• idx_user_created (user_id, created_at) 사용 시 → ref 타입 조회로 최적화됨.
• idx_created_user (created_at, user_id) 사용 시 → range 검색이 먼저 실행되어 비효율적일 가능성이 높음.

---

5. 단일 인덱스와 복합 인덱스 비교 요약

유형 장점 단점 활용 사례

단일 인덱스	여러 쿼리에서 재사용 가능	WHERE A='1' AND B='2'에서는 하나만 선택됨	A 또는 B 단독 검색이 많을 때
복합 인덱스	선택도 증가, 커버드 인덱스 활용 가능	첫 번째 컬럼이 WHERE에 없으면 사용 불가	A, B 조합 검색이 많을 때

---

6. MySQL에서만 해당되는 개념인가?

• 이 개념은 MySQL뿐만 아니라 PostgreSQL, Oracle, SQL Server 등 대부분의 관계형 데이터베이스에서도 동일하게 적용된다.
• 다만, 각 DBMS의 실행 계획 최적화 방식이 다를 수 있으므로 EXPLAIN을 활용하여 성능을 분석하는 것이 중요하다.

---

7. 결론

• 복합 인덱스의 컬럼 순서는 =(Equality) → 범위(Range) 순서로 배치하는 것이 일반적으로 더 효율적이다.
• user_id와 created_at 같이 사용되는 경우, user_id를 앞에 두는 것이 검색 성능을 높이는 데 유리하다.
• 단일 인덱스는 다양한 쿼리에 재사용될 수 있지만, 복합 검색에서 성능이 떨어질 수 있다.
• 인덱스 전략은 MySQL뿐만 아니라 대부분의 관계형 데이터베이스에서 동일하게 적용된다.
• EXPLAIN을 활용하여 실제 실행 계획을 분석하고 인덱스를 최적화하는 것이 중요하다.

도움

https://okky.kr/questions/1378747

mysql 단일인덱스 2개 와 결합(복합)인덱스 1개를 생성함에 있어서의 차이점 문의드립니다. | OKKY Q&