GitHubAboutDocsTech-Log

Optimizer(옵티마이저)

옵티마이저는 사용자가 작성한 SQL 쿼리를 가장 효율적인 방법으로 실행하기 위한 최적의 실행 계획(Execution Plan)을 수립하는 데이터베이스 서버의 핵심 엔진이다.

옵티마이저의 종류

옵티마이저는 실행 계획을 수립할 때 사용하는 기준에 따라 규칙 기반과 비용 기반으로 나뉜다.

  • 비용 기반 최적화(Cost-based Optimizer)

    • 쿼리를 실행할 수 있는 모든 가능한 실행 계획을 후보로 선정하고, 각 계획의 예상 비용(Cost)을 산출하여 그중 가장 비용이 낮은 계획을 최종적으로 선택

    • 비용은 쿼리 실행에 소요될 시간이나 자원의 추정치이며, 통계 정보를 바탕으로 계산

    • 통계 정보에는 테이블의 전체 레코드 수, 인덱스의 유니크한 값 개수(Cardinality), 값의 분포도(Histogram) 등이 포함

  • 규칙 기반 최적화(Rule-based Optimizer)

    • 옵티마이저 내부에 내장된 규칙(예: '인덱스가 있으면 항상 테이블 풀 스캔보다 우선한다')의 우선순위에 따라 실행 계획을 생성

    • 데이터의 실제 분포나 통계 정보를 전혀 고려하지 않으므로, 데이터의 양이나 상태가 달라져도 항상 동일한 실행 계획 생성

최근 거의 모든 관계형 데이터베이스는 비용 기반 옵티마이저를 사용한다.

비용 기반 최적화의 동작 과정

MySQL의 비용 기반 옵티마이저는 다음과 같은 과정을 통해 최적의 실행 계획을 찾는다.

1. 쿼리 변환(Query Transformation)

옵티마이저는 비용을 계산하기 전에, 주어진 SQL 문장을 더 효율적으로 실행될 수 있는 동등한 형태의 다른 SQL 문장으로 변환하는 작업을 먼저 수행한다.

  • IN 조건을 여러 개의 OR 조건으로 변환

  • 서브쿼리를 JOIN 형태로 변환

  • 불필요한 JOIN 제거 또는 JOIN 순서 조정

  • 상수 표현식의 사전 계산 (WHERE col = 10 + 20 -> WHERE col = 30)

2. 최적화 목표

쿼리 변환 후, 옵티마이저는 최소 비용을 목표로 다음과 같은 주요 사항들을 결정한다.

  • 접근 방식(Access Method): 단일 테이블에 접근할 때 어떤 방식으로 데이터를 읽을지 결정

    • 인덱스 레인지 스캔 (Index Range Scan)

    • 인덱스 풀 스캔 (Index Full Scan)

    • 풀 테이블 스캔 (Full Table Scan)

    • 사용 가능한 여러 인덱스 중 어떤 인덱스를 사용하는 것이 가장 비용이 낮은지 판단

  • 조인 순서(Join Order): 여러 테이블을 조인할 때 어떤 순서로 조인할지를 결정

  • 조인 방식(Join Method): 조인 순서가 결정되면, 각 조인을 어떤 방식으로 수행할지 결정

    • Nested-Loop Join

    • Block Nested-Loop Join

    • Hash Join

3. 비용 계산

옵티마이저는 각 실행 계획의 예상 비용을 계산할 때 크게 I/O 비용과 CPU 비용을 함께 고려한다.

  • I/O 비용: 디스크의 데이터나 인덱스 페이지를 메모리로 읽어오는 데 드는 비용

    • 일반적으로 쿼리 성능에 가장 큰 영향을 미치는 요소

    • 옵티마이저는 통계 정보를 바탕으로 얼마나 많은 페이지를 읽어야 할지 예측

  • CPU 비용: 메모리로 읽어온 데이터를 정렬하거나, 조건에 맞는지 비교하는 등 연산에 드는 비용

옵티마이저는 이 두 비용을 종합하여 각 실행 계획의 총비용을 산출하고, 이 값이 가장 낮은 계획을 최종 실행 계획으로 채택한다.

통계 정보의 중요성

비용 기반 옵티마이저는 전적으로 통계 정보에 의존하여 비용을 계산한다.

  • 테이블의 데이터가 대량으로 변경(INSERT, UPDATE, DELETE)된 후에는 통계 정보가 실제 데이터 분포를 반영하지 못할 수 있음

  • ANALYZE TABLE table_name; 명령어를 통해 수동으로 통계 정보를 갱신하여 옵티마이저가 더 정확한 판단을 내리도록 유도 가능

옵티마이저 제어

대부분의 경우 옵티마이저의 결정은 신뢰할 수 있지만, 개발자가 직접 실행 계획에 개입해서 최적화를 유도할 수 있다.

  • 옵티마이저 힌트(Optimizer Hint): 특정 인덱스를 사용하도록 강제(USE INDEX, FORCE INDEX)하거나, 조인 순서를 지정하는 등 옵티마이저의 동작 유도 가능

  • 옵티마이저 스위치(Optimizer Switch): optimizer_switch 시스템 변수를 통해 특정 최적화 기법을 활성화 또는 비활성화 가능

참고자료

Last updated

Was this helpful?