[MySQL] 인덱스 설계 가이드: 실무에서 실패하지 않는 인덱스 전략 🛠️

MySQL 인덱스는 성능을 좌우하는 핵심 요소다. 하지만 무조건 많이 만든다고 성능이 좋아지는 것은 절대 아니다.
오히려 잘못된 인덱스는 성능 저하, 디스크 낭비, 유지보수 비용 증가로 이어질 수 있다.

🎯 인덱스를 설계하는 이유부터 다시 생각하자

• WHERE 절의 조건으로 자주 사용되는 컬럼인가?
• JOIN에 사용되는 컬럼인가?
• ORDER BY나 GROUP BY로 정렬이나 집계를 자주 하는가?

단순히 자주 조회된다고 인덱스를 만드는 것이 아니라, 실제 쿼리 수행 방식과 목적에 맞게 설계하는 것이 핵심이다.

반응형

🔍 WHERE 조건에 최적화된 인덱스 전략

WHERE 절에서 자주 사용하는 조건을 고려하여 인덱스를 만든다.

SELECT * FROM users WHERE email = 'test@example.com';

→ email 컬럼에 단일 인덱스를 추가하면 좋다.

하지만 아래처럼 다중 컬럼 조건일 경우엔 복합 인덱스를 고려해야 한다.

SELECT * FROM orders WHERE user_id = 1 AND order_date = '2024-01-01';

→ CREATE INDEX idx_user_date ON orders(user_id, order_date);

중요한 점

• 복합 인덱스는 선두 컬럼이 우선이다. order_date 단독 검색은 위 인덱스로는 불리하다.

🧩 SELECTivity(선택도)를 고려하라

선택도란?
: 인덱스 컬럼에서 중복이 적고 다양한 값을 가지는 컬럼일수록 좋다.

예시)

• 성별처럼 값이 'M', 'F'로만 구성된 컬럼은 인덱스 효용이 낮다.
• 이메일, 전화번호처럼 고유 값이 많은 컬럼은 인덱스 효율이 높다.

실전 팁

SHOW INDEX FROM 테이블명;
ANALYZE TABLE 테이블명;

을 통해 인덱스 통계와 효율을 파악하자.

반응형

⚙️ 단일 vs 복합 인덱스 전략

단일 인덱스

CREATE INDEX idx_email ON users(email);

단일 인덱스는 단일 조건 쿼리에 유리하다.

복합 인덱스

CREATE INDEX idx_name_email ON users(name, email);

복합 인덱스는 다중 조건을 AND로 연결할 때 강력하다.
단, WHERE 절의 컬럼 순서가 인덱스 순서와 같아야 효과적이다.

WHERE 조건	idx_name_email 인덱스 활용 여부
name = ?	✅ 가능
email = ?	❌ 불가능
name = ? AND email = ?	✅ 가능

🌀 정렬과 GROUP BY 전략

SELECT * FROM logs ORDER BY created_at DESC LIMIT 100;

→ created_at에 인덱스가 없으면 전체 테이블 정렬 필요

해결법

CREATE INDEX idx_created_at ON logs(created_at DESC);

ORDER BY, GROUP BY는 정렬 키에 맞는 인덱스가 없을 경우 MySQL이 임시 테이블을 만들기 때문에 느려진다.

🛡️ 중복 인덱스를 피하라

아래 두 인덱스는 사실상 같은 역할을 하기 때문에 둘 중 하나만 있어야 한다.

CREATE INDEX idx_name_email ON users(name, email);
CREATE INDEX idx_name ON users(name);

→ idx_name_email이 이미 name 조건 검색을 지원하므로 idx_name은 불필요

🚧 인덱스가 무시되는 패턴 주의

• LIKE '%abc' (앞에 와일드카드 사용)
• IS NULL, IS NOT NULL 조건
• 인덱스 컬럼에 함수 사용 (YEAR(created_at) 등)
• 형변환 (CAST(age AS CHAR) = '20')

이런 쿼리는 인덱스를 무시하고 전체 테이블을 스캔하게 만든다.

반응형

🧠 EXPLAIN으로 실행 계획 검토하기

EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

→ 결과의 key, rows, type, possible_keys 등을 분석하면
해당 인덱스가 실제로 사용되었는지 확인 가능하다.

ALL이 뜨면 풀스캔(Full Table Scan)이므로 인덱스를 적용해야 할 가능성이 높다.

🧰 유지관리 팁: 오래된 인덱스 정리하자

다음 명령어로 인덱스 사용 빈도를 체크하고 정리가 가능하다.

SHOW INDEX FROM 테이블명;
SHOW TABLE STATUS;

불필요한 인덱스는 삭제하고 필요한 인덱스를 재설계하면 성능 향상에 도움된다.

DROP INDEX idx_name ON users;

🧾 결론: 인덱스는 '설계'다

• 쿼리를 분석하지 않고 만들면 실제 사용되지 않는 인덱스가 된다.
• 너무 많아도 느려지고, 없어도 느려진다.
• 패턴 파악 → 빈도 분석 → 선택도 고려 → 정기 점검까지 진행해야 한다.

정답은 없다. 하지만 위 원칙을 기반으로 인덱스를 설계하면 성능은 분명히 개선된다.

📎 참고 공식문서 링크

MySQL 인덱스 설계 가이드
https://dev.mysql.com/doc/refman/8.0/en/mysql-indexes.html