인덱스(Index)란 무엇인가?

배경

프로젝트를 진행하며, 쿼리의 성능이 떨어지는 것을 느껴서 확인해본 결과 Full Table Scan을 하고있었습니다. 이를 해결하기위해서 인덱스(Index)라는 개념을 사용해서 해결할 수 있습니다. 오늘은 인덱스에 대해서 알아보겠습니다.

---

목차

1. 인덱스란 무엇인가?

2. 인덱스의 내부 구조 (B-Tree)

3. 인덱스의 종류

4. 인덱스 생성 전략

5. 인덱스 성능 최적화 팁

6. 실전 예제와 성능 비교

7. 인덱스 사용 시 주의사항

---

1. 인덱스란 무엇인가?

인덱스(Index)는 데이터베이스 테이블의 검색 속도를 향상시키기 위한 자료구조입니다. 책의 색인처럼, 데이터의 위치를 빠르게 찾을 수 있도록 도와주는 역할을 합니다.

 

인덱스가 없다면?

-- 100만 건의 데이터에서 특정 사용자 찾기
SELECT * FROM users WHERE email = 'user@example.com';

인덱스가 없으면 DB는 Full Table Scan을 수행합니다.

Row 1: customer_id = 1    → 아님, 다음
Row 2: customer_id = 2    → 아님, 다음
Row 3: customer_id = 3    → 아님, 다음
...
Row 100,234: customer_id = 5000 → 찾았다!
...
Row 1,000,000: customer_id = 9999 → 끝까지 확인

 

찾았어도 끝까지 모두 확인해야 합니다.

---

2. 인덱스의 내부 구조(B-Tree)

InnoDB는 기본적으로 B-Tree(Balanced Tree) 구조를 사용합니다.

 

B-Tree의 특징:

                 [50]               ← Root Node
            /           \
        [20, 30]      [70, 90]      ← Branch Nodes
       /   |   \      /   |   \
    [10] [25] [40] [60] [80] [100]  ← Leaf Nodes

핵심 특징:

1. 균형 잡힌 트리: 모든 리프 노드가 같은 깊이
2. 정렬된 상태 유지: 항상 오름차순 정렬
3. 다중 분기: 이진 트리가 아닌 다중 자식 구조
4. 효율적 탐색: O(log n) 시간 복잡도

값 25를 찾는 과정:

1단계: Root [50]에서 시작
        → 25 < 50 이므로 왼쪽으로

2단계: Branch [20, 30]에 도착
        → 20 < 25 < 30 이므로 중간 자식으로

3단계: Leaf [25]에서 발견!

인덱스가 있다면 100만 건의 데이터도 약 3~4번의 비교만으로 찾을 수 있습니다.

---

3. 인덱스의 종류

3.1 클러스터드 인덱스(Clustered Index)

가장 중요한 인덱스입니다. InnoDB에서 Primary Key는 자동으로 Clustered Index가 됩니다.

CREATE TABLE users (
    id INT PRIMARY KEY,  -- Clustered Index
    email VARCHAR(100),
    name VARCHAR(50)
);

특징:

• 테이블당 1개만 존재
• 실제 데이터가 인덱스 순서대로 물리적으로 저장됨
• PK가 없으면 Unique NOT NULL 컬럼이 Clustered Index가 됨

3.2 보조 인덱스(Secondary Index)

Clustered Index가 아닌 모든 인덱스를 Secondary Index 라고 합니다.

CREATE INDEX idx_email ON users(email);  -- Secondary Index
CREATE INDEX idx_name ON users(name);    -- Secondary Index

특징:

• 테이블당 여러 개 생성 가능
• 리프 노드에 Primary Key 값을 저장
• 실제 데이터를 찾기 위해 PK를 통해 Clustered Index를 한번 더 탐색

3.3 Unique Index

중복을 허용하지 않는 인덱스입니다.

CREATE UNIQUE INDEX idx_unique_email ON users(email);
-- 또는
ALTER TABLE users ADD UNIQUE KEY(email);

3.4 복합 인덱스(Composite Index)

여러 컬럼을 조합한 인덱스 입니다.

CREATE INDEX idx_name_age ON users(name, age);

중요: 인덱스 컬럼 순서가 성능에 큰 영향을 미칩니다.(뒤에서 예제 코드를 사용해서 설명할 예정입니다.)

 

인덱스 종류 비교 정리

인덱스 종류	개수 제한	중복 허용	특징
Clustered Index	테이블당 1개	❌ (허용 안 함)	실제 데이터를 정렬된 형태로 저장, PK 생성 시 자동 생성됨
Secondary (Non-Clustered) Index	여러 개 생성 가능	⭕ (허용)	인덱스에 PK(또는 RID) 저장, 조회 시 2번 탐색 필요 (인덱스 → 테이블)
Unique Index	여러 개 생성 가능	❌ (허용 안 함)	중복값을 허용하지 않음, 단 NULL은 여러 개 가능
Composite Index	여러 개 생성 가능	⭕ (허용)	여러 컬럼 조합 기반 인덱스, 컬럼 순서가 성능에 영향

 

그 외에도, 검색 기능 향상을 위한 Full  Text Index도 있습니다.

---

4. 테스트 예제: 100만 건으로 입증하기

테스트 환경 구축

-- 1. 테스트 테이블 생성
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    status VARCHAR(20) NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL
);

-- 2. 100만 건 데이터 삽입 (최적화된 방법)
-- 크로스 조인으로 약 30초 내 완료
INSERT INTO orders (customer_id, order_date, status, total_amount)
SELECT 
    1 + (a.n + b.n*10 + c.n*100 + d.n*1000) % 10000 as customer_id,
    DATE_ADD('2023-01-01', 
        INTERVAL (a.n + b.n*10 + c.n*100 + d.n*1000 + e.n*10000 + f.n*100000) % 730 DAY
    ) as order_date,
    CASE (a.n + b.n*10) % 3
        WHEN 0 THEN 'pending'
        WHEN 1 THEN 'completed'
        ELSE 'cancelled'
    END as status,
    ROUND(1000 + (RAND() * 499000), 2) as total_amount
FROM 
    numbers a, numbers b, numbers c, numbers d, numbers e, numbers f
LIMIT 1000000;

데이터 분포:

• 고객 수: 10,000명
• 기간: 2023-01-01 ~ 2024-12-31
• 주문 상태: pending, completed, cancelled
• 총 데이터: 1,000,000건

테스트 쿼리

-- 자주 사용되는 쿼리 패턴
-- "특정 고객의 최근 6개월 주문 내역 조회"
SELECT * FROM orders 
WHERE customer_id = 5000 
  AND order_date >= '2024-06-01';

 

5.1 인덱스가 없을 경우 (Full Table Scan)

EXPLAIN 
SELECT * FROM orders 
WHERE customer_id = 5000 
  AND order_date >= '2024-06-01';

 

분석:

• type: ALL -> Full Table Scan!
• rows: 997166 -> 모든 행을 검사!
• key: NULL -> 인덱스 사용 안 함!

실행 결과:

EXPLAIN ANALYZE 
SELECT * FROM orders 
WHERE customer_id = 5000 
  AND order_date >= '2024-06-01';

-> Filter: ((orders.customer_id = 5000) and (orders.order_date >= DATE'2024-06-01'))  
   (cost=100477.85 rows=33236) (actual time=1.850..413.070 rows=30 loops=1)
     -> Table scan on orders  
        (cost=100477.85 rows=997166) (actual time=0.051..351.078 rows=1000000 loops=1)

 

 

결과 분석:

• 실행 방식: Table scan -> 인덱스 없이 테이블 전체를 순차 스캔
• 예상 스캔 행: 997166 -> 거의 모든 행을 읽어야 함
• 실제  스캔 행: 1000000 -> 실제로 100만 건 전체 스캔
• 테이블 첫 번째 행: 0.051ms
• 100만 건 전부 읽기: 351.078ms
• 조건에 맞는 첫 번째 행 발견: 1850ms
• 전체 필터링 + 결과 반환 완료: 413.070ms
• 총 실행 시간: 약 413ms

5.2 인덱스가 있는 경우

-- 1. 올바른 순서의 인덱스 생성
CREATE INDEX idx_customer_date ON orders(customer_id, order_date);

SHOW INDEX FROM orders;

 

EXPLAIN ANALYZE 
SELECT * FROM orders 
WHERE customer_id = 5000 
  AND order_date >= '2024-06-01'

-> Index range scan on orders using idx_customer_date 
   over (customer_id = 5000 AND '2024-06-01' <= order_date), 
   with index condition: ((orders.customer_id = 5000) and (orders.order_date >= DATE'2024-06-01'))  
   (cost=13.76 rows=30) (actual time=0.123..0.336 rows=30 loops=1)

실행 결과:

 

• 실행 방식: Index range scan → 인덱스를 사용한 효율적인 범위 스캔
• 사용 인덱스: idx_customer_date → 복합 인덱스의 두 컬럼 모두 활용
• 예상 스캔 행: rows=30 → 정확한 예측
• 실제 스캔 행: rows=30 → 필요한 행만 정확히 스캔
• 조건에 맞는 첫 번째 행 발견: 0.123ms
• 전체 스캔 + 결과 반환 완료: 0.336ms
• 총 실행 시간: 약 0.3ms

5.3 복합 인덱스에서 컬럼 순서의 중요성

Left-Most Prefix 규칙

CREATE INDEX idx_abc ON orders(customer_id, order_date, status);

 

WHERE 조건	인덱스 사용 여부	설명
customer_id = ?	✅	첫 번째 컬럼 사용 → 인덱스 활용 가능
customer_id = ? AND order_date = ?	✅	첫 번째 컬럼부터 연속된 조건 → 전체 인덱스 활용
order_date = ?	❌	첫 번째 컬럼(customer_id)이 빠져서 인덱스 탐색 불가

---

6. 인덱스 사용 시 주의사항

6.1 인덱스를 타지 않는 경우

함수 사용

-- 인덱스 사용 불가
SELECT * FROM orders WHERE YEAR(order_date) = 2024;

-- 인덱스 사용 가능
SELECT * FROM orders 
WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31';

데이터 타입 불일치

SELECT * FROM orders WHERE customer_id = '5000'; --  문자열
SELECT * FROM orders WHERE customer_id = 5000;   --  숫자

Like 와일드카드가 앞에 있는 경우

SELECT * FROM orders WHERE status LIKE '%eted'; -- ❌
SELECT * FROM orders WHERE status LIKE 'comp%'; -- ✅

OR 조건

-- 비효율적
SELECT * FROM orders WHERE customer_id = 5000 OR status = 'pending';

-- 개선: UNION
SELECT * FROM orders WHERE customer_id = 5000
UNION
SELECT * FROM orders WHERE status = 'pending';

 

---

7. 인덱스의 트레이드오프

인덱스는 만능이 아닙니다. 조회 성능을 높이는 대신 치러야 할 대가가 있습니다.

7.1 저장 공간 차지

-- 인덱스 크기 확인
SELECT 
    index_name,
    ROUND(stat_value * @@innodb_page_size / 1024 / 1024, 2) AS size_mb
FROM mysql.innodb_index_stats
WHERE database_name = 'index_test' 
  AND table_name = 'orders'
  AND stat_name = 'size';

결과:

인덱스가 많을수록 디스크 공간을 더 많이 사용합니다.

 

7.2 쓰기 성능 저하

INSERT INTO orders VALUES (...);

인덱스가 3개 있다면:

1. 테이블에 데이터 삽입
2. PRIMARY KEY 인덱스 업데이트
3. idx_customer_date 인덱스 업데이트
4. 다른 인덱스들도 업데이트

→ 총 4번의 쓰기 작업 발생!

 

7.3 잘못된 인덱스는 오히려 독

-- 카디널리티가 매우 낮은 컬럼에 인덱스 생성
CREATE INDEX idx_status ON orders(status);

문제점:

• status는 3가지 값만 존재 (pending, completed, cancelled)
• 각 값이 전체의 약 33%를 차지
• 인덱스를 타도 33%의 데이터를 스캔해야 함
• 차라리 Full Table Scan이 더 빠를 수 있음

 

 

7.4 트레이드오프 정리

항목	장점	단점
조회(SELECT)	수백~수천 배 빠름	-
쓰기(INSERT/UPDATE/DELETE)	-	2~3배 느려질 수 있음
저장 공간	-	테이블 크기의 30~100% 추가
유지보수	-	인덱스 관리 필요 (재구성, 모니터링)

핵심:

• 조회가 많은 시스템(OLAP, 읽기 중심) → 인덱스 유리
• 쓰기가 많은 시스템(OLTP, 트랜잭션 중심) → 신중하게 선택

---

정리

인덱스 핵심 요약

1. 인덱스는 B-Tree 구조
   • O(log n) 시간 복잡도로 빠른 검색
   • 100만 건도 3~4번 비교로 탐색
2. 복합 인덱스는 컬럼 순서가 중요
   • Left-Most Prefix 규칙 준수
   • 등호 조건을 범위 조건보다 앞에
   • 카디널리티 높은 컬럼 우선
3. EXPLAIN으로 항상 검증
   • type: ALL → Full Table Scan (위험!)
   • type: ref, range → 인덱스 사용 (좋음)
   • EXPLAIN ANALYZE로 실제 성능 측정
4. 인덱스의 트레이드오프 고려
   • 조회 성능 vs 쓰기 성능
   • 저장 공간 vs 검색 속도
   • 적절한 균형점 찾기
5. 주의사항
   • 인덱스 컬럼에 함수 사용 금지
   • 데이터 타입 일치시키기
   • 카디널리티 낮은 컬럼 피하기