개요

테이블 검색 성능을 끌어올리는 1차 수단은 인덱스 구축임 인덱스는 단일 컬럼 또는 다중 컬럼 기준으로 생성 가능하며, 다음과 같은 기본 생성 규칙이 작동함

  • Primary Key 지정 시 클러스터드 인덱스 생성
  • Unique 제약은 보조 인덱스(논클러스터드 인덱스, InnoDB에선 Secondary Index)로 생성

MySQL의 InnoDB는 B+Tree 기반 인덱스를 사용하며, 데이터 저장 구조와 접근 패턴이 인덱스 유형별로 상이함

핵심 개념 정리

  • 클러스터드 인덱스

    • 테이블 데이터가 인덱스 키 순서로 정리되는 구조
    • 테이블당 하나만 존재
    • 리프 노드가 실제 레코드(전체 컬럼)를 보유
    • InnoDB에서 Primary Key가 곧 클러스터드 인덱스가 됨
    • PK가 없으면 첫 번째 유니크 not null 인덱스를 사용, 그것도 없으면 내부적으로 보이지 않는 6바이트 Row ID를 생성해 클러스터링 키로 사용

  • 논클러스터드 인덱스(보조 인덱스, Secondary Index)

    • 테이블에 여러 개 생성 가능
    • 리프 노드가 인덱스 키와 함께 클러스터드 인덱스 키(PK 또는 내부 Row ID)를 보유
    • 보조 인덱스로 찾은 키로 다시 클러스터드 인덱스를 탐색하는 백투테이블 단계 발생

  • 인덱스는 조회 성능 향상을 위해 쓰되, 인덱스가 늘어날수록 CUD 비용과 저장 공간이 증가하는 트레이드오프 존재

동작 원리와 구조

  • B+Tree 탐색 흐름

    • 수직 탐색에서 루트 → 브랜치 → 리프 순으로 키 범위를 좁힘
    • 보조 인덱스 사용 시 리프에서 얻은 클러스터드 키로 한 번 더 수직 탐색 수행
    • 수평 탐색으로 리프 레벨에서 연속 키 스캔 가능

  • 커버링 인덱스

    • 쿼리에 필요한 컬럼이 인덱스에 모두 포함된 경우 백투테이블 없이 인덱스만으로 결과 생성
    • 추가 I/O 회피로 레이턴시 절감

  • 접근 패턴과 I/O 특성

    • 시퀀셜 액세스: 물리적으로 인접한 페이지를 연속 읽기, 멀티 블록 I/O 활용, 대량 읽기에 유리
    • 랜덤 액세스: 물리적으로 떨어진 페이지에 임의 접근, 단일 블록 I/O 빈번, 보조 인덱스 사용 시 다수 발생
    • SSD 환경에서도 무작위 접근 비용은 존재, 범위 스캔 대량 발생 시 체감 가능

  • InnoDB에서의 물리 정렬에 대한 오해 정리

    • 클러스터드 인덱스는 페이지 단위로 키 순서를 유지하도록 관리되지만, 디스크 상에서 완전한 순차 배치가 되는 것은 아님
    • 랜덤 삽입이나 페이지 분할이 반복되면 물리적 연속성이 낮아질 수 있음
    • 단조 증가 PK(auto increment 등) 사용 시 페이지 분할과 랜덤 쓰기 감소 효과 기대

MySQL 실행 흐름과 옵티마이저 관점

  • 쿼리 캐시

    • MySQL 8.0에서 제거됨

  • 파싱과 전처리

    • 문법 검증 및 의미 분석 수행

  • 최적화 단계

    • 실행계획 생성
    • 조건 절 분류, 인덱스 선택, 조인 순서 결정
    • 통계 정보와 비용 모델 기반으로 테이블 풀 스캔과 인덱스 스캔 중 비용이 낮은 경로 선택

  • 스토리지 엔진 핸들러

    • InnoDB가 페이지 읽기와 버퍼풀 관리를 담당
    • MySQL 엔진은 반환된 레코드로 조인, 정렬, 집계를 수행

  • 실행 계획 확인

    • EXPLAIN으로 실제 접근 경로 점검 권장

테이블 풀 스캔 vs 인덱스 레인지 스캔

  • Table Full Scan

    • 테이블 전체를 시퀀셜 액세스로 읽으며 멀티 블록 I/O 활용
    • 큰 테이블에서 대량 결과 반환 시 효율적

  • Index Range Scan

    • 인덱스 트리에서 범위 탐색 후 필요 시 백투테이블 단계 수행
    • 랜덤 액세스와 단일 블록 I/O가 많아지는 경향
    • 작은 결과 집합을 빠르게 찾는 데 유리

  • 손익분기점 관찰치

    • 데이터 규모와 하드웨어, 버퍼풀 히트율에 따라 달라짐
    • 업무 환경에서 5~20% 선택도 구간에서 테이블 풀 스캔이 더 빠르게 전환되는 사례 빈번
    • 빅테이블에서 결과 비율이 높아질수록 인덱스 경로는 점진적으로 불리

인덱스 설계 시 주의와 베스트 프랙티스

  • 모든 WHERE 조건에 인덱스 남발 금지

    • 각 인덱스는 쓰기 비용과 저장 공간 증가를 동반
    • 다수 인덱스는 옵티마이저 선택 혼선을 키울 수 있음

  • PK 설계

    • 짧고 단조 증가하는 키 선호
    • 무작위 UUID v4는 페이지 분할과 랜덤 쓰기 유발 위험, 시간순 UUID 대안 고려

  • 보조 인덱스 구성

    • 선택도 높은 컬럼 우선
    • 결합 인덱스는 좌측 접두사 규칙 고려, 자주 결합되는 조건 묶기
    • 커버링 인덱스가 큰 이득을 주는 빈도 높은 쿼리에 한정 적용

  • SARGable 조건 유지

    • 인덱스 컬럼에 함수 적용, 계산, 캐스팅이 있으면 인덱스 사용 저해 가능성
    • 가능한 원형 비교 유지, 필요한 경우 생성 칼럼과 인덱스 조합 검토

  • 통계와 실행 계획 점검

    • ANALYZE TABLE로 통계 최신화
    • EXPLAIN, 실행 프로파일링으로 계획 검증

  • 저장 공간과 CUD 비용 이해

    • 추가 인덱스마다 페이지 유지와 분할, 리밸런싱 비용 증가
    • 저장 공간 증가는 인덱스 키 길이와 카디널리티에 의존, 대략 테이블 크기의 두 자릿수 퍼센트까지 늘어날 수 있음

간단 예시

  • 인덱스 생성 예

    • 단일 컬럼
      • ALTER TABLE UserTable ADD INDEX idx_closed_at (closed_at)
    • 다중 컬럼
      • ALTER TABLE UserTable ADD INDEX idx_created_lastmod (created_at, last_modified_at)

  • 조회 예

    • 전체 조회는 테이블 풀 스캔 경로 선택 가능성 높음
    • SELECT * FROM EmployeeTable
    • PK로 단건 조회는 클러스터드 인덱스 탐색 경로
    • SELECT * FROM EmployeeTable WHERE employee_id = 1
    • 보조 인덱스 컬럼 조회는 인덱스 레인지 스캔 후 백투테이블
    • SELECT * FROM EmployeeTable WHERE name = 'Kim'

  • 커버링 인덱스 예

    • INDEX(name, created_at)가 있고 SELECT name, created_at FROM EmployeeTable WHERE name = 'Kim' 처럼 필요한 컬럼이 인덱스에 모두 포함된 경우 백투테이블 생략 가능

언제 풀 스캔이 더 나은가

  • 결과 비율이 높아 랜덤 액세스가 폭증하는 경우
  • 조건이 낮은 선택도로 인덱스 이득이 미미한 경우
  • 정렬이나 조인 전략 관점에서 풀 스캔이 총비용이 낮게 추정되는 경우

경계값은 시스템마다 상이하므로 실제 데이터로 벤치마킹하여 결정하는 것이 안전

요약과 결론

  • 클러스터드 인덱스는 테이블 자체의 정렬 구조, 보조 인덱스는 검색 보조 구조
  • 소량 결과를 빠르게 찾을 때는 보조 인덱스 경로, 대량 읽기에는 풀 스캔이 유리한 구간 존재
  • 인덱스는 비용이 있는 도구, 자주 쓰이는 고선택도 조건과 커버링 효과가 큰 쿼리에 집중 투자
  • PK는 짧고 단조 증가하는 키가 안전, 보조 인덱스는 쿼리 패턴 기반으로 최소 집합 유지
  • EXPLAIN과 통계 최신화를 통해 계획을 검증하고, 쿼리와 데이터 특성에 맞춰 지속 조정

참고자료