MySQL 인덱스 완벽 가이드: 성능 최적화의 핵심

목차

1. 인덱스란?
2. 인덱스의 종류
3. 인덱스 동작 원리
4. 인덱스 설계 전략
5. 실전 예제
6. 성능 분석
7. 주의사항

인덱스란?

인덱스는 데이터베이스 테이블의 검색 성능을 향상시키기 위한 자료구조입니다. 책의 목차나 색인과 같이, 데이터를 빠르게 찾을 수 있도록 도와줍니다.

인덱스가 필요한 이유

-- 인덱스가 없는 경우: 전체 테이블 스캔
SELECT * FROM users WHERE email = 'user@example.com';

-- 인덱스가 있는 경우: 인덱스를 통한 빠른 검색
CREATE INDEX idx_email ON users(email);
SELECT * FROM users WHERE email = 'user@example.com';

인덱스의 종류

1. 클러스터형 인덱스 (Clustered Index)

• 테이블당 1개만 생성 가능
• 물리적으로 데이터를 정렬
• InnoDB에서는 기본적으로 PK가 클러스터형 인덱스

CREATE TABLE users (
    id INT PRIMARY KEY,  -- 클러스터형 인덱스
    name VARCHAR(50),
    email VARCHAR(100)
);

2. 보조 인덱스 (Secondary Index)

• 여러 개 생성 가능
• 데이터의 물리적 순서와 무관
• 인덱스 페이지와 데이터 페이지가 분리

-- 단일 컬럼 인덱스
CREATE INDEX idx_email ON users(email);

-- 복합 컬럼 인덱스
CREATE INDEX idx_name_email ON users(name, email);

3. 유니크 인덱스

CREATE UNIQUE INDEX idx_email ON users(email);

4. 전문 검색 인덱스 (Fulltext Index)

CREATE FULLTEXT INDEX idx_content ON articles(content);

인덱스 동작 원리

B-Tree 구조

         [Root Node]
        /     |     \
   [Node] [Node] [Node]
   /  |   /  |   /  |
[Leaf][Leaf][Leaf][Leaf]

인덱스 스캔 과정

-- 인덱스 사용 예시
EXPLAIN SELECT * FROM users 
WHERE email = 'user@example.com';

인덱스 설계 전략

1. 선택도(Selectivity) 고려

-- 높은 선택도 (좋은 예)
CREATE INDEX idx_email ON users(email);  -- 이메일은 대부분 고유

-- 낮은 선택도 (나쁜 예)
CREATE INDEX idx_gender ON users(gender);  -- 성별은 값의 종류가 적음

2. 복합 인덱스 순서

-- 좋은 예
CREATE INDEX idx_email_created_at ON users(email, created_at);

-- 나쁜 예
CREATE INDEX idx_created_at_email ON users(created_at, email);

3. 커버링 인덱스

-- 커버링 인덱스 활용
CREATE INDEX idx_name_email ON users(name, email);
SELECT name, email FROM users WHERE name = 'John';  -- 데이터 페이지 접근 불필요

실전 예제

1. 회원 검색 최적화

-- 테이블 구조
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(100),
    name VARCHAR(50),
    status ENUM('active', 'inactive'),
    created_at TIMESTAMP,
    updated_at TIMESTAMP
);

-- 효율적인 인덱스 설계
CREATE INDEX idx_email ON users(email);
CREATE INDEX idx_status_created_at ON users(status, created_at);

2. 주문 조회 시스템

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    order_status VARCHAR(20),
    order_date TIMESTAMP,
    total_amount DECIMAL(10,2)
);

-- 자주 사용되는 조회 패턴에 대한 인덱스
CREATE INDEX idx_user_date ON orders(user_id, order_date);
CREATE INDEX idx_status_date ON orders(order_status, order_date);

성능 분석

1. EXPLAIN 활용

EXPLAIN SELECT * FROM users 
WHERE email = 'test@example.com'
AND created_at > '2024-01-01';

-- 결과 분석
-- type: ref/range/ALL 등을 확인
-- key: 사용된 인덱스 확인
-- rows: 스캔된 행 수 확인

2. 성능 측정

-- 쿼리 실행 시간 측정
SET profiling = 1;
SELECT * FROM users WHERE email = 'test@example.com';
SHOW PROFILES;

3. 인덱스 사용 통계

-- 인덱스 사용 통계 확인
SHOW INDEX FROM users;

주의사항

1. 과도한 인덱스 생성 피하기

-- 불필요한 인덱스 식별
SELECT * FROM information_schema.statistics
WHERE table_schema = 'your_database'
AND table_name = 'users';

-- 사용되지 않는 인덱스 제거
DROP INDEX idx_unused ON users;

2. INSERT/UPDATE 성능 고려

-- 대량 데이터 입력 시 임시로 인덱스 비활성화
ALTER TABLE users DISABLE KEYS;
-- 데이터 입력
ALTER TABLE users ENABLE KEYS;

3. 인덱스 관리

-- 인덱스 재구축
ANALYZE TABLE users;
OPTIMIZE TABLE users;

성능 최적화 사례

1. 로그인 조회 최적화

-- 변경 전
SELECT * FROM users WHERE email = ? AND status = 'active';

-- 변경 후
CREATE INDEX idx_email_status ON users(email, status);

2. 날짜 범위 검색 최적화

-- 변경 전
SELECT * FROM orders 
WHERE created_at BETWEEN ? AND ?
AND status = 'completed';

-- 변경 후
CREATE INDEX idx_status_created ON orders(status, created_at);

모니터링과 유지보수

1. 인덱스 상태 모니터링

-- 인덱스 크기 확인
SELECT 
    table_name,
    index_name,
    stat_value as pages
FROM mysql.innodb_index_stats
WHERE stat_name = 'size'
AND database_name = 'your_database';

2. 미사용 인덱스 식별

SELECT * FROM sys.schema_unused_indexes;

3. 중복 인덱스 확인

SELECT 
    table_schema,
    table_name,
    COUNT(*) as index_count
FROM information_schema.statistics
GROUP BY table_schema, table_name
HAVING COUNT(*) > 1;

마치며

인덱스는 데이터베이스 성능 최적화의 핵심이지만, 무분별한 사용은 오히려 성능 저하를 초래할 수 있습니다. 적절한 인덱스 설계와 지속적인 모니터링이 중요합니다.

핵심 포인트

1. 선택도가 높은 컬럼에 인덱스 생성
2. 복합 인덱스 순서 최적화
3. 커버링 인덱스 활용
4. 정기적인 인덱스 관리
5. 성능 모니터링

추천 자료

• MySQL 공식 문서
• Real MySQL (백은빈, 이성욱 저)
• High Performance MySQL