본문으로 건너뛰기

Day 4: 인덱스와 쿼리 최적화

  • 인덱스(Index) 기본 개념
    • B-Tree 인덱스, 해시 인덱스
    • 클러스터드/논클러스터드 인덱스 차이
  • 쿼리 실행 계획(Execution Plan) 분석
    • COST 기반 최적화 개념
    • EXPLAIN 사용법(MySQL 등)
  • 쿼리 튜닝 기법
    • 적절한 인덱스 설계
    • JOIN 방식(중첩 루프, 해시, 머지)
    • 뷰(View), 서브쿼리, 파티셔닝(Partitioning)

데이터베이스 성능을 향상시키기 위해서는 적절한 인덱스 활용과 쿼리 최적화가 필수적이다. 인덱스를 잘못 설계하거나, 쿼리를 비효율적으로 작성하면 데이터 검색 속도가 느려지고, 시스템 자원을 과하게 소모하게 된다. 아래에서는 인덱스의 원리와 쿼리 튜닝 핵심 기법을 살펴본다.

1. 인덱스(Index) 기본 개념​

1.1 B-Tree 인덱스​

  • 구조:
    • 계층적 트리 구조로, 최상위 루트 노드부터 리프 노드까지 검색 경로를 내려가며 데이터를 찾는다.
    • 일반적으로 정렬된 상태로 유지되며, 검색·삽입·삭제에서 로그 시간 복잡도를 기대할 수 있다.
  • 장점:
    • 범위 검색(Range Scan)에 유리하며, 일반적인 쿼리(=, >=, <=, BETWEEN, ORDER BY 등)에 모두 적합하다.
    • 검색 성능이 크게 향상된다.
  • 단점:
    • 자주 변경(Insert/Update/Delete)이 발생하는 열에 인덱스를 너무 많이 만들면, 인덱스 재정렬 오버헤드가 증가한다.

1.2 해시(Hash) 인덱스​

  • 구조:
    • 해시 함수를 이용해 키를 해시 값으로 매핑하고, 해당 버킷(Bucket)에서 데이터를 찾는다.
  • 장점:
    • 해시 충돌이 적으면 (=) 조건에서 매우 빠른 검색이 가능.
    • 메모리 기반 저장엔 탁월한 성능 (예: Redis).
  • 단점:
    • 범위 검색, 정렬(ORDER BY), LIKE 연산 등에는 부적합.
    • 해시 충돌이 많이 발생하거나 해시테이블이 커지면 성능 저하가 일어날 수 있다.

1.3 클러스터드(Clustered) vs 논클러스터드(Non-Clustered) 인덱스​

  • 클러스터드 인덱스:
    • 실제 데이터가 인덱스 순서대로 물리적으로 정렬(클러스터링)되어 저장된다.
    • 기본적으로 Primary Key에 적용하는 경우가 많으며, 검색 시 테이블에 바로 접근해 추가 IO가 적음.
    • 단, 물리적 정렬 구조여서 잦은 변경 시 재배치 비용이 커진다.
    • 예: MySQL의 InnoDB에서는 기본 키가 클러스터드 인덱스 형태.
  • 논클러스터드 인덱스:
    • 인덱스 구조와 실제 데이터 물리적 저장이 분리되어 있다.
    • 인덱스에 저장된 참조(ROWID 등)를 통해 실제 데이터가 있는 위치를 찾아가는 간접 접근 방식.
    • 하나의 테이블에 여러 논클러스터드 인덱스를 가질 수 있다.

2. 쿼리 실행 계획(Execution Plan) 분석​

2.1 COST 기반 최적화 개념​

  • COST(비용) 기반 옵티마이저:
    • DBMS가 다양한 쿼리 실행 계획(플랜)을 시뮬레이션하고, 각 플랜별 예상 비용(디스크 IO, CPU 사용량 등)을 계산해 최소 비용의 플랜을 선택한다.
    • 통계 정보(테이블 카디널리티, 인덱스 분포도, 컬럼 평균길이 등)가 정확할수록 옵티마이저가 더 나은 결정을 내린다.

2.2 EXPLAIN 사용법(MySQL 예시)​

  • EXPLAIN (또는 EXPLAIN ANALYZE 등):
    • SELECT 쿼리에 대한 실행 계획을 미리 확인
    • 각 테이블 접근 방식(ALL, range, ref, eq_ref 등), 사용 인덱스, 예상 ROW 수, 키 길이 등을 보여줌
  • 예시: 
    EXPLAIN
    SELECT u.username, p.post_title
    FROM users AS u
    JOIN posts AS p ON u.id = p.user_id
    WHERE u.username = 'alice';

3. 쿼리 튜닝 기법​

3.1 적절한 인덱스 설계​

  1. 선행 열(Column) 선정

    • WHERE 절에 자주 사용되는 열, JOIN 조건에 자주 등장하는 열을 인덱스로 만든다.
    • 다중열 인덱스(Multi-column index) 사용 시, 실제 쿼리에서 자주 등장하는 순서대로 정의해야 한다(Leftmost Prefix Rule).

  2. Selectivity(선택도)

    • 컬럼 값이 고르게 분포(Unique)되어 있을수록 인덱스 효율이 높아진다.
    • 예) 성별(M/F)은 값이 두 종류에 불과해 선택도가 낮아, 인덱스 효과가 적다.

  3. 과도한 인덱스 남발 방지

    • INSERT/UPDATE 시 인덱스 구조도 재정렬해야 하므로 쓰기 성능이 저하된다.
    • 필요한 인덱스만 유지하도록 쿼리 빈도, 성능 요구사항을 꼼꼼히 점검한다.

3.2 JOIN 방식​

  1. 중첩 루프(Nested Loop)

    • 외부 테이블에서 레코드를 하나씩 가져오고, 내부 테이블에서 해당 레코드와 매칭(인덱스로 조회)
    • 소규모 테이블과 대규모 테이블이 있을 때, 소규모 테이블을 외부 테이블로 두면 효율적

  2. 해시(Hash) 조인

    • 한쪽 테이블에서 해시 테이블을 만들고, 다른 테이블 레코드와 해시 매칭
    • 대량 데이터 조인에 유리하지만 해시 테이블 생성 비용이 발생

  3. 머지(Merge) 조인

    • 조인할 양쪽 테이블을 정렬 후, 병합하면서 매칭
    • 정렬 비용이 들지만, 정렬된 상태에서 비교가 빠름

3.3 뷰(View), 서브쿼리, 파티셔닝(Partitioning)​

  1. 뷰(View)

    • 복잡한 쿼리를 재사용하기 위한 가상 테이블
    • 뷰 정의 시, 옵티마이저가 내부 쿼리까지 최적화할 수 있음(물리 뷰(Materialized View)는 DB마다 지원 여부 상이)

  2. 서브쿼리

    • SELECT/FROM/WHERE 절에 다른 SELECT 문을 중첩
    • 때로는 서브쿼리를 JOIN으로 변경하면 성능이 크게 개선되기도 하나, 데이터량·옵티마이저에 따라 달라질 수 있음

  3. 파티셔닝(Partitioning)

    • 대용량 테이블을 특정 기준(날짜, 해시, 리스트 등)으로 나누어 관리
    • 쿼리 범위가 명확할 경우, 필요한 파티션만 접근해 성능 향상
    • 관리 관점에서, 파티션 설계와 핫 파티션(특정 파티션만 트래픽 집중) 문제 등을 주의해야 함