MySQL 인덱스 이해 및 검사: 포괄적인 가이드
카테고리
프로그래밍/소프트웨어 개발
서브카테고리
데이터 분석
대상자
- 데이터베이스 개발자 및 DBA
- 중간~고급 수준의 MySQL 사용자
- 인덱스 최적화 및 성능 튜닝에 관심 있는 개발자
핵심 요약
- 인덱스는 MySQL의 쿼리 성능을 극적으로 향상시키지만, 쓰기 성능에 부정적 영향을 줄 수 있음
- BTREE 구조를 기반으로 한 인덱스가 대부분, HASH는 MEMORY 테이블에 한정됨
- SHOW INDEX 명령어를 통해 인덱스 구조를 실시간으로 분석할 수 있음
섹션별 세부 요약
1. 인덱스의 중요성
- 인덱스는 대량의 데이터를 효율적으로 검색할 수 있게 함 (BTREE, B+Tree 등)
- 쿼리 응답 시간을 초 단위에서 밀리초 단위로 줄일 수 있음
- 인덱스는 쓰기 작업 시 데이터 수정, 삭제, 삽입 시 오버헤드 발생
2. 인덱스의 성능 트레이드오프
- InnoDB의 백그라운드 프로세스와 세분화된 락은 쓰기 지연 감소에 도움
- 인덱스 수와 복잡도가 증가할수록 쓰기 성능 저하 발생
- 스토리지 소모: 인덱스는 컬럼의 크기와 타입에 비례하여 디스크 공간 사용
3. 인덱스 유형
- Primary Key: 각 행을 고유 식별 (InnoDB 테이블 필수)
- Unique: 이메일, 사용자 ID 등 고유성 강제
- Full-Text: 대규모 텍스트 필드의 자연어 검색 최적화
- Composite: 여러 컬럼을 결합한 인덱스 (컬럼 순서가 중요)
- Prefix: 긴 문자열 (URL, 파일 경로)의 일부만 인덱스로 사용
4. 인덱스 검사 방법
- SHOW INDEX / SHOW INDEXES 명령어 사용
- information_schema 시스템 테이블을 기반으로 메타데이터 검색
- 테이블이 기본 스키마 외에 존재할 경우:
SHOW INDEX FROM database_name.table_name
5. 인덱스 분석의 주요 고려사항
- Cardinality: 인덱스의 선택성 (값이 높을수록 필터링 성능 향상)
- Composite 인덱스의 Seq_in_index: 쿼리에서 사용 가능한 컬럼 순서 결정
- Key_name과 Non_unique: 고유성 강제 여부 확인
6. 실무적 문제점과 해결 방안
- 불필요한 중복 인덱스, 카드inality 낮은 인덱스 제거 필요
- 쿼리가 인덱스의 왼쪽 최전방 접두사 규칙을 준수하지 않으면 인덱스 무시됨
- SHOW INDEX 결과를 기반으로 인덱스 최적화 전략 수립
결론
- SHOW INDEX 명령어를 정기적으로 사용해 인덱스 구조를 분석하고, 성능 저하 원인을 확인
- Composite 인덱스의 컬럼 순서와 카드inality를 고려해 인덱스 설계
- 불필요한 인덱스는 제거하고, 실제 작업 부하에 맞는 인덱스 전략 수립