7장 SQL 옵티마이저

친절한 SQL 튜닝 7장

SQL 옵티마이저란?

사용자가 작성한 SQL을 가장 효율적인 실행계획으로 변환하는 데이터베이스의 핵심 엔진이다. 쿼리를 그대로 실행하지 않고 옵티마이저가 여러 실행 방법 중 가장 빠를 것으로 예상되는 계획을 선택한다.

옵티마이저의 종류

규칙 기반 옵티마이저 (RBO, Rule-Based Optimizer)

• 오래된 방식, 미리 정해진 우선순위에 따라 실행 계획을 결정함.

• ex. 인덱스를 쓰는 것이 항상 테이블 풀 스캔보다 먼저

비용 기반 옵티마이저 (CBO, Cost-Based Optimizer)

• 통계 정보를 기반으로 각 실행 계획의 비용을 계산하여 가장 낮은 비용을 선택

• 요즘 DB는 대부분 CBO 사용 중

• 통계 정보(테이블 건수, 인덱스 카디널리티 등)가 매우 중요함

옵티마이저의 핵심 요소

1. 통계 정보: 테이블/인덱스 건수, 데이터 분포, 컬럼 값 범위 등 사용

2. 비용 모델: 실행 계획마다 예상되는 리소스 사용량(디스크 i/o, cpu 소모 등)을 숫자로 계산하여 가장 낮은 비용의 실행 계획 선택

3. 힌트: 옵티마이저의 선택을 강제할 수 있는 수단

왜 SQL 튜닝에서 옵티마이저를 알아야 할까?

• 실행 계획은 SQL 성능에 직결됨.

• 옵티마이저가 잘못된 계획을 세우는 경우(통계 불일치, 편향된 데이터 분포 등)가 많음.

• 따라서 SQL 튜닝 시

  1. 옵티마이저가 어떤 기준으로 판단했는지

  2. 통계 정보가 정확한지

  3. 힌트나 구조 변경이 필요한지

를 판단해야 한다.

통계 불일치가 일어나는 경우가 있을까?

데이터베이스에서 자동으로 통계를 수집하는건 맞지만, 다음과 같은 상황에서 통계가 부정확해질 수 있다.

• 대량의 데이터 변경 후 통계가 아직 갱신되지 않은 경우

• 데이터 분포가 특이한 경우

이 경우 통계를 갱신할 수 있는데, ANALYZE TABLE 또는 DBMS_STATS 명령어로 갱신할 수 있다.

• ANALYZE TABLE: 인덱스 키 분포를 분석하고, 통계 정보를 업데이트한다. (MySQL)

ANALYZE TABLE table_name;

갑자기 슬로우 쿼리가 발생하는 데에는 여러 가지 이유가 있을 수 있다.

그중 하나는 통계가 갱신되지 않아 옵티마이저가 정상적으로 동작하지 못하는 경우다. 백엔드 개발자도 이러한 상황이 발생할 수 있음을 알아두는 것이 좋겠다.

옵티마이저도 완벽하지 않다 (그래서 개발자가 필요하다)

최적의 실행 계획을 계산하기 위해 완벽한 통계 정보를 얻는 것은 힘들다. 옵티마이저는 결국 사람이 만든 로직이므로 개발자의 개입이 필요하다.

개발자가 할 수 있는 최적화 팁

• 필요한 최소 블록만 읽도록 SQL 작성 (불필요한 함수/조인/컬럼 접근 최소화)

• 조건절 기준으로 인덱스 전략적으로 구성 (파티션, 클러스터, IOT 활용)

• 필요하면 힌트 명시

튜닝 전문가 되는 공부 방법

보통 아래 세 가지로 나뉜다.

• SQL 튜닝: I/O 최적화, DB Call 최적화, SQL 파싱 최적화

• DB 설계: 논리적 데이터 구조 설계, 물리적 저장 구조 설계

• 인스턴스 튜닝: Lock/Latch 모니터링 및 해소, 메모리 설정, 프로세스 설정 등

튜닝 전문가가 되려면 SQL을 깊이 이해하고, 좋은 데이터 구조를 설계하는 것이 가장 중요하다.

정리

• 옵티마이저는 SQL 성능의 핵심: 어떤 실행 계획을 선택하는가?

• 통계 정보가 정확해야 옵티마이저가 올바른 선택을 한다.

• 옵티마이저의 의사결정 요소(비용, 조인 순서, 인덱스 사용)를 이해하면 SQL 튜닝이 쉬워짐.