1. 들어가며
인덱스는 단순히 많이 만든다고 해서 성능이 보장되는 것이 아니다. 잘못 설계된 인덱스는 쿼리 성능을 개선하지 못할 뿐만 아니라, 데이터 삽입 및 수정 시 불필요한 오버헤드를 발생시키고 저장 공간을 낭비하게 만든다. 따라서 "어떤 컬럼에, 어떤 순서로 인덱스를 구성할 것인가"라는 실무적인 질문에 답할 수 있어야 한다. 본 챕터에서는 효율적인 인덱스 설계를 위한 핵심 개념인 카디널리티와 복합 인덱스 전략을 다룬다.
2. 단일 인덱스의 구조
특정 컬럼 하나에 대해 인덱스를 생성하는 것을 단일 인덱스라 한다. 데이터베이스는 원본 테이블과 별개로, 해당 컬럼을 기준으로 정렬된 인덱스 테이블을 생성하여 관리한다.
2.1. 원본 테이블 (book)
| book_id | category | is_adult |
| 1 | 만화 | ☐ |
| 2 | 에세이 | ✅ |
| 3 | 개발 | ✅ |
| 4 | 에세이 | ✅ |
| 5 | 개발 | ✅ |
| 6 | 만화 | ✅ |
| 7 | 에세이 | ☐ |
| 8 | 에세이 | ✅ |
2.2. 인덱스 테이블 (category 기준 정렬)
| category | book_id |
| 개발 | 3 |
| 개발 | 5 |
| 만화 | 1 |
| 만화 | 6 |
| 에세이 | 2 |
| 에세이 | 4 |
| 에세이 | 7 |
| 에세이 | 8 |
위와 같이 category를 인덱스로 설정하면, 데이터베이스는 전체 데이터를 뒤지는 대신 정렬된 인덱스 테이블에서 원하는 카테고리의 위치를 즉시 찾아낼 수 있다.
3. 복합 인덱스와 컬럼 순서의 중요성
만약 사용자들이 "카테고리"와 "성인용 여부" 두 가지 조건으로 검색하는 경우가 늘어났다면 어떻게 해야 할까? 즉, “카테고리가 만화이면서 성인용인 책을 찾아줘”라는 검색 패턴이라면 어떨까? 이 경우에는 `category`만으로는 충분하지 않다. 따라서 두 컬럼을 묶어 하나의 인덱스로 만들어야 한다. 이를 복합 인덱스(Composite Index)라고 하는데, 복합 인덱스는 컬럼의 순서가 매우 중요하다.
3.1. 잘못된 순서: book_index(is_adult, category)
성인 여부(is_adult)는 ✅와 ☐ 두 가지 값만 존재한다. 만약 이 컬럼을 앞에 배치하면, 인덱스의 첫 번째 층에서 탐색 범위가 절반 정도밖에 줄어들지 않는다. 즉, 수많은 데이터를 일단 다 훑은 뒤에야 카테고리를 비교하게 되므로 탐색 효율이 떨어진다.
| is_adult | category | book_id |
| ✅ | 개발 | 3 |
| ✅ | 개발 | 5 |
| ✅ | 에세이 | 2 |
| ✅ | 에세이 | 4 |
| ✅ | 에세이 | 8 |
| ✅ | 만화 | 6 |
| ☐ | 만화 | 1 |
| ☐ | 에세이 | 7 |
3.2. 올바른 순서: book_index(category, is_adult)
반면, 값의 종류가 더 많은 category를 앞에 배치하면 상황이 달라진다. '만화'라는 카테고리로 범위를 좁히는 순간, 전체 데이터 중 극히 일부만 남게 된다. 그 좁아진 범위 안에서 성인 여부를 확인하므로 탐색 속도는 비약적으로 향상된다.
| category | is_adult | book_id |
| 개발 | ✅ | 3 |
| 개발 | ✅ | 5 |
| 만화 | ✅ | 6 |
| 만화 | ☐ | 1 |
| 에세이 | ✅ | 2 |
| 에세이 | ✅ | 4 |
| 에세이 | ✅ | 8 |
| 에세이 | ☐ | 7 |
4. 카디널리티(Cardinality)란 무엇인가?
- 인덱스 설계 시 컬럼 순서를 결정하는 핵심 기준은 카디널리티(Cardinality)이다. 이는 특정 컬럼이 가진 '값의 종류' 또는 '변별력'을 의미한다.
- 카디널리티가 높다: 중복되는 값이 적고 값의 종류가 많다. (예: 주민등록번호, 사용자 ID, 이메일)
- 카디널리티가 낮다: 중복되는 값이 많고 값의 종류가 적다. (예: 성별, 성인 여부, 결제 상태 코드)
5. 인덱스 설계의 핵심 원칙 (⭐⭐)
효율적인 복합 인덱스 설계를 위해 다음의 원칙을 반드시 준수해야 한다.
- 카디널리티가 높은 컬럼을 전진 배치하라: 탐색의 초기 단계에서 최대한 많은 데이터를 걸러내야 리프 노드 접근을 최소화할 수 있다.
- 조회 빈도가 높은 컬럼을 우선하라: 아무리 변별력이 좋아도 실제 검색 조건으로 사용되지 않는다면 무용지물이다.
- 수정이 잦은 컬럼은 피하라: 인덱스는 정렬 상태를 유지해야 하므로, 값이 자주 바뀌는 컬럼에 인덱스를 걸면 매번 인덱스를 재구성하는 비용이 발생한다.
결국 인덱스 설계는 데이터의 구조와 실제 서비스의 조회 패턴을 깊이 있게 이해하는 것에서 시작된다. 적절한 위치에 배치된 인덱스 하나가 수천만 건의 데이터를 다루는 시스템의 생존을 결정한다.
6. 동적 쿼리와 인덱스 선택 기준 (⭐)
실무 환경에서는 사용자가 직군(Position), 지역(Location), 생성일(CreatedAt) 등 여러 필터를 선택적으로 적용하는 '동적 쿼리' 상황이 빈번하다. 모든 조합에 대비해 인덱스를 만드는 것은 불가능하므로, 다음의 기준에 따라 전략적으로 인덱스를 생성해야 한다.
6.1. 왼쪽 접두어 원칙 (Leftmost Prefix Rule)
복합 인덱스의 핵심은 "데이터가 인덱스 컬럼 순서대로 정렬되어 저장된다"는 점이다. 따라서 인덱스의 첫 번째 컬럼(접두어)이 조건절에 없으면 정렬 순서를 활용할 수 없어 인덱스가 효율적으로 작동하지 않는다. 인덱스를 (A, B, C) 순서로 생성했다면, 데이터는 아래와 같은 우선순위로 정렬된다.
- A를 기준으로 먼저 정렬
- A가 같을 때만 B를 기준으로 정렬
- A와 B가 모두 같을 때만 C를 기준으로 정렬
6.1.1. ✅ 인덱스를 타는 경우 (순서 준수)
`WHERE A = ?`
A 기준으로 이미 정렬되어 있으므로 바로 찾을 수 있다.
` WHERE A = ? AND B = ? `
A를 찾은 후, 그 안에서 정렬된 B를 찾으므로 매우 빠르다.
` WHERE A = ? AND B = ? AND C = ? `
A, B, C 순서대로 정렬된 길을 완벽하게 따라간다.
6.1.2. ❌ 인덱스를 타지 못하는 경우 (순서 위반)
` WHERE B = ? `
A 없이 B만으로는 정렬되어 있지 않다. (B는 A라는 그룹 안에서만 정렬된 상태)
결국 인덱스를 타지 못하고 전체를 뒤져야 한다(Full Scan).
` WHERE C = ? `
A와 B 없이 C만으로는 정렬 순서를 전혀 알 수 없다.
결론: 동적 쿼리 환경에서는 "검색 조건에 가장 빈번하게, 필수적으로 등장하는 컬럼"을 인덱스의 맨 앞(왼쪽)에 배치해야 효율을 극대화할 수 있다.
6.2. 어떤 인덱스를 우선적으로 만들 것인가?
인덱스 과남용을 막기 위한 세 가지 선택 기준은 다음과 같다.
- 필수 조건 우선: 검색 시 사용자가 반드시 선택해야 하거나, 비즈니스 로직상 항상 포함되는 조건(예: is_deleted = false)을 인덱스 앞단에 배치한다.
- 자주 조합되는 핵심 경로: 80% 이상의 사용자가 사용하는 주요 필터 조합을 분석하여 복합 인덱스를 구성한다. 나머지 20%의 마이너한 검색 조건은 인덱스 없이 풀 스캔을 감수하거나, 다른 인덱스를 활용하도록 유도한다.
- 등치(=)는 앞쪽에, 범위(>, <)는 뒤쪽에: 복합 인덱스에서 특정 컬럼을 범위 검색(BETWEEN, > 등)으로 사용하는 순간, 그 뒤에 위치한 컬럼들은 인덱스의 혜택을 거의 받지 못한다. 따라서 정확히 일치하는 값을 찾는 컬럼을 앞순위에 배치해야 한다.
6.3. 동적 쿼리 대응 전략 요약
- 전략 1: 가장 많이 쓰이는 컬럼 하나를 단일 인덱스로 잡는다. (범용성 높음)
- 전략 2: 가장 자주 조합되는 2~3개 컬럼을 묶어 복합 인덱스를 만든다. (성능 높음)
- 전략 3: 인덱스가 타지 않는 조건은 검색 엔진(Elasticsearch 등) 도입을 검토하거나, 쿼리 옵티마이저가 최적의 판단을 내릴 수 있도록 통계 데이터를 관리한다.
7. 실습
7.1. 테스트 데이터 설정
아래와 같이 총 10,000건의 Book 데이터를 만들어보자.
create database IF NOT EXISTS school;
use school;
SET SESSION cte_max_recursion_depth = 1000000;
CREATE TABLE IF NOT EXISTS book (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
category VARCHAR(50) NOT NULL,
price INT NOT NULL,
is_adult BOOLEAN NOT NULL,
published_at DATE NOT NULL
);
-- 100만 개의 책 데이터 생성 및 삽입
INSERT INTO book (name, category, price, is_adult, published_at)
WITH RECURSIVE numbers AS (
SELECT 1 AS n
UNION ALL
SELECT n + 1 FROM numbers WHERE n < 1000000
)
SELECT
CONCAT('Book ', n), -- 책 이름
CASE MOD(n, 5)
WHEN 0 THEN 'Fiction'
WHEN 1 THEN 'Non-fiction'
WHEN 2 THEN 'Science'
WHEN 3 THEN 'History'
ELSE 'Art'
END, -- 카테고리
FLOOR(9000 + (RAND() * 100000)), -- 가격
CASE MOD(n, 2)
WHEN 0 THEN TRUE
ELSE FALSE
END, -- 성인 여부
DATE_ADD('2000-01-01', INTERVAL FLOOR(RAND() * 9000) DAY) -- 출판일
FROM numbers;
7.2. 단일 인덱스 테스트
7.2.1. 인덱스가 없는 경우: Full Table Scan (O(N))
이때, SELECT * FROM book WHERE name = "Book 100"; 명렁어를 실행해보면 0.671s(671ms)가 소요된것을 확인할 수 있다. 하나의 데이터를 조회하는데 671ms 소요되었다면, 나중에 1000명의 사용자가 조회했을 때, 여러 개의 데이터를 조회했을 때의 성능은 매우 안좋아진다는 것을 알 수 있다.
SELECT * FROM book WHERE name = "Book 100";- 원리: 데이터가 입력된 순서대로(혹은 무작위로) 저장되어 있어, DB 엔진은 "Book 100"이라는 데이터가 테이블의 어느 위치에 있는지 전혀 알지 못한다. 따라서 첫 번째 레코드부터 마지막 레코드까지 전체를 대조하는 Full Table Scan을 수행한다.
- 성능: 데이터가 N개일 때 N번의 비교를 수행하므로, 데이터 양에 비례하여 선형적으로 속도가 느려진다.
7.2.2. 인덱스가 있는 경우: B-Tree Index Scan (O(log N))
여기에 INDEX를 적용하면 어떻게 될까? CREATE INDEX name_index_to_book ON book(name); 명령어를 통해 이름에 대한 인덱스를 생성하였다. 이후에 다시한번 SELECT * FROM book WHERE name = "Book 100"; 쿼리를 실행해보면 0.137s로 대폭 감소되었음을 알 수 있다.
CREATE INDEX name_index_to_book ON book(name);
SELECT * FROM book WHERE name = "Book 100";7.2.3. 왜 동등(=) 조회에서도 인덱스가 의미가 있는가?
단순히 "이거랑 똑같은 거 찾아줘"라고 요청할 때도 인덱스가 강력한 이유는 인덱스가 '정렬된 구조'이기 때문이다.
- 정렬을 통한 탐색 범위 축소: 인덱스는 name 컬럼의 값들을 사전순으로 정렬하여 저장한다. 정렬되어 있다는 것은 이진 탐색(Binary Search)과 유사한 원리로 탐색 범위를 절반씩 줄여나갈 수 있음을 의미한다.
- B-Tree의 계층 구조: 인덱스는 루트 노드부터 리프 노드까지 나무 형태로 구조화되어 있다. "Book 100"을 찾을 때, DB는 "B로 시작하는 데이터는 왼쪽 가지에 있다"는 식의 정보를 따라가며 단 몇 번의 이동(O(\log N))만으로 데이터의 정확한 위치를 찾아낸다.
- 데이터 위치 정보(Pointer): 인덱스는 (정렬된 값, 실제 데이터 주소)의 쌍으로 구성된다. 이름으로 정렬된 인덱스에서 "Book 100"의 위치를 찾으면, 그 옆에 적힌 '실제 데이터 주소'를 타고 본 테이블의 데이터를 즉시 가져올 수 있다.
O(1)이 아닌 이유)
해시 인덱스는 정확한 값 일치 시 O(1)을 보장하지만, 범위 검색(LIKE, >, <)이 불가능하다는 치명적인 단점이 있다. 반면 B-Tree 인덱스는 정렬된 상태를 유지하므로 동등 조회와 범위 검색 모두에서 강력한 성능(O(log N))을 보이기 때문에 실무 표준으로 사용된다.
✅ 요약 및 결론
인덱스는 단순한 포스트잇이 아니라, 조회하려는 컬럼을 기준으로 데이터를 미리 정렬해둔 요약본이다. name에 인덱스를 거는 순간, DB는 100만 장의 페이지를 넘기는 대신 잘 정렬된 사전에서 단어를 찾듯 논리적으로 설계된 구조를 따라가기 때문에 조회 성능이 수백 배 이상 빨라지는 것이다.
7.3. 복합 인덱스 테스트
7.3.1. 인덱스 없는 경우
인덱스 없이 SELECT * FROM book WHERE category = "Fiction" AND is_adult = true; 명령어를 실행시켜보면 2.523s가 소요된것을 확인할 수 있다.
SELECT * FROM book WHERE category = "Fiction" AND is_adult = true;7.3.2. (is_adult, category) 인덱스 생성
오히려 실행시간이 6.948s로 늘어났음을 알 수 있다.
CREATE INDEX is_adult_and_category_index_to_book ON book (is_adult, category);
SELECT * FROM book WHERE category = "Fiction" AND is_adult = true;7.3.3. (category, is_adult) 인덱스 생성
다시 다시 아까 입력했던 쿼리, SELECT * FROM book WHERE category = "Fiction" and is_adult = True;를 입력해보면 아래와 같이 1.813s로 감소한것을 알 수 있다. 성능에 엄청난 이점이 있을 줄 알았는데 그닥 큰 변화는 아니다. 왜 이런 결과가 나왔을까?
DROP INDEX is_adult_and_category_index_to_book ON book;
CREATE INDEX category_and_is_adult_index_to_book ON book (category, is_adult);
SELECT * FROM book WHERE category = "Fiction" AND is_adult = true;7.3.4. 복합 인덱스의 성능 고찰
두 인덱스 모두 성능 차이가 미미한 이유는 다음과 같다.
- 등치 조건에서의 옵티마이저 동작: 현재 쿼리에서는 category와 is_adult 모두 '=' 연산자를 사용하는 등치(equality) 조건이다. MySQL의 쿼리 옵티마이저는 이런 단순한 등치 조건에서는 인덱스 순서에 크게 구애받지 않고 효율적으로 작동할 수 있다.
- 실제 데이터 분포와 필터링 효과: Fiction 카테고리는 데이터의 약 1/5(20%)를 차지하고, is_adult=true는 약 50%를 차지한다. 이런 상황에서 어느 조건을 먼저 적용하든 결과적으로 비슷한 양의 데이터를 필터링하게 된다.
- 테이블 풀 스캔(Full Table Scan) 방지: 두 인덱스 모두 전체 테이블 스캔을 방지하는 역할을 하지만, 결국 많은 양의 데이터(10만 건)를 반환하기 때문에 병목현상이 발생한다.
인덱스 순서의 차이는 아래와 같은 상황에서 더 극명하게 드러난다.
1. 범위 조건을 사용할 때 (BETWEEN, >, <)
2. 일부 컬럼만으로 필터링할 때
3. ORDER BY 절을 사용할 때
4. 결과 집합이 작을 때
'Database > MySQL' 카테고리의 다른 글
| [Index-6][Optimization] 실전 분석 (2) - 실행 계획 타입(Type) (0) | 2025.12.24 |
|---|---|
| [Index-5][Optimization] 실전 분석 (1) - 실행 계획의 이해 (0) | 2025.12.23 |
| [Index-4][Optimization] 심화 이론 (2) - 물리적 저장 구조 (0) | 2025.12.23 |
| [Index-3][Optimization] 심화 이론 (1) - B-Tree와 B+Tree (0) | 2025.12.23 |
| [Index-1][Optimization] 인덱스의 기초와 존재 이유 (0) | 2025.09.04 |