데이터베이스 옵티마이저와 쿼리 실행 계획의 선택 원리

SQL 성능은 실행 계획에서 갈린다

같은 결과를 반환하는 SQL이라도 어떤 실행 계획을 택하느냐에 따라 성능이 수백~수천 배까지 달라질 수 있다. 이 선택을 맡는 구성요소가 DBMS의 옵티마이저다.

옵티마이저는 입력된 SQL을 분석해 데이터를 읽을 경로와 연산 순서를 정한다. 목표는 시간과 자원 비용을 최소화하면서 필요한 결과를 반환하는 것이다. 수백 GB나 수 TB 규모의 데이터를 다루는 환경에서는 이 선택이 개별 쿼리를 넘어 시스템 전체 성능에 영향을 준다.

실행 전에 만들어지는 쿼리 실행 계획

QEP(Query Execution Plan)는 SQL을 수행하기 위한 단계별 계획이다. 옵티마이저는 이 계획에서 테이블 접근 방식, 조인 알고리즘과 순서, 집계 및 정렬 처리 방식을 결정한다.

접근 방법: 전체 스캔, 인덱스 스캔 등 테이블 데이터를 읽는 방식
조인 방법: Nested Loop, Hash Join 등 여러 테이블을 결합하는 방식
조인 순서: 여러 테이블을 어떤 순서로 처리할지에 대한 결정
집계 처리: GROUP BY 같은 집계 연산의 처리 방식
정렬 처리: ORDER BY 같은 정렬 작업의 처리 방식

⤢✕

규칙과 비용으로 나뉘는 계획 선택 방식

규칙 기반 옵티마이저(RBO: Rule-Based Optimizer)는 미리 정한 휴리스틱으로 실행 계획을 만든다. 테이블 크기나 데이터 분포 같은 통계 정보는 고려하지 않으며, 단순하고 예측 가능한 계획을 생성한다. 현대 DBMS에서는 거의 사용하지 않는다.

비용 기반 옵티마이저(CBO: Cost-Based Optimizer)는 테이블과 인덱스의 통계 정보를 바탕으로 비용을 계산해 실행 계획을 고른다. 테이블 크기, 로우 수, 칼럼 값 분포, 인덱스 구성 등이 판단 재료가 된다. 현대 대부분의 DBMS가 이 방식을 채택하며, 통계 정보가 정확할수록 더 효율적인 계획을 수립할 수 있다.

⤢✕

변환부터 조인 순서까지의 판단 과정

옵티마이저는 SQL을 내부적으로 다루기 쉬운 형태로 바꾸는 단계부터 시작한다. 뷰 머징(View Merging)으로 뷰를 정의로 대체하고, 서브쿼리 병합(Subquery Flattening)으로 가능한 서브쿼리를 메인 쿼리에 합친다. 조건절 이행(Predicate Transitive)으로 추가 필터 조건을 만들고, 불필요한 조건 제거(Predicate Elimination)로 중복 조건을 없앤다.

그다음 각 테이블에 접근할 수 있는 경로를 평가한다. 전체 테이블 스캔(Full Table Scan), 범위 스캔과 유니크 스캔 등을 포함한 인덱스 스캔(Index Scan), 샘플링 스캔(Sample Scan)이 후보가 된다.

조인을 처리할 때는 데이터 특성에 맞는 알고리즘을 고른다.

중첩 루프 조인(Nested Loop Join)은 작은 데이터셋에 유리하다.
해시 조인(Hash Join)은 큰 데이터셋과 동등 조인에 유리하다.
소트 머지 조인(Sort Merge Join)은 정렬된 데이터에 유리하다.

여러 테이블을 결합하는 쿼리에서는 조인 순서도 성능에 직접 영향을 준다. 소규모 쿼리에서는 가능한 조인 순서를 모두 평가할 수 있고, 대규모 쿼리에서는 휴리스틱 알고리즘으로 근사치를 계산한다.

통계 정보가 계획의 기반이 된다

비용 기반 판단은 통계 정보의 품질에 좌우된다. 테이블 통계에는 블록 수, 로우 수, 평균 로우 길이가 포함된다. 칼럼 통계에서는 유니크 값 개수(Cardinality), 값 분포(Histogram), NULL 값 비율을 살핀다. 인덱스 통계는 인덱스 높이(Height), 리프 블록 수, 클러스터링 팩터(Clustering Factor)로 구성된다.

데이터 변경이 많은 환경일수록 통계 정보 갱신 주기를 더 짧게 가져가야 한다. 오래된 통계는 실제 데이터 상태와 다른 비용 추정으로 이어질 수 있다.

실행 계획을 확인하는 명령

DBMS마다 실행 계획을 확인하는 방법은 다르지만, 쿼리가 어떤 경로를 선택했는지 검토하는 출발점이라는 점은 같다.

Oracle에서는 EXPLAIN PLAN 명령어 또는 AUTOTRACE 기능을 사용한다.

EXPLAIN PLAN FOR
SELECT * FROM employees WHERE department_id = 10;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

MySQL에서는 EXPLAIN 명령어를 사용한다.

EXPLAIN SELECT * FROM employees WHERE department_id = 10;

SQL Server에서는 SET SHOWPLAN_ALL 또는 실행 계획 표시 기능을 사용한다.

SET SHOWPLAN_ALL ON;
GO
SELECT * FROM employees WHERE department_id = 10;
GO
SET SHOWPLAN_ALL OFF;

힌트는 계획을 고정하는 도구다

옵티마이저가 선택한 실행 계획이 적절하지 않을 때 개발자나 DBA는 힌트로 특정 계획을 유도할 수 있다.

Oracle 힌트 예시는 다음과 같다.

SELECT /*+ INDEX(employees emp_dept_idx) */ *
FROM employees
WHERE department_id = 10;

MySQL에서는 다음과 같이 인덱스 사용을 지정할 수 있다.

SELECT * FROM employees USE INDEX (emp_dept_idx)
WHERE department_id = 10;

SQL Server의 인덱스 힌트는 다음과 같다.

SELECT * FROM employees WITH (INDEX = emp_dept_idx)
WHERE department_id = 10;

힌트는 데이터 변화에 따라 오히려 성능 저하를 일으킬 수 있다. 옵티마이저의 판단이 잘못된 경우에 한정해 사용하고, 적용 후에도 효용성을 정기적으로 검토해야 한다.

성능 문제를 다루는 운영 지점

튜닝은 하나의 설정으로 끝나지 않는다. 통계 정보를 정기적으로 갱신하고, 대량 데이터 변경 뒤에는 즉시 갱신할 필요가 있다. 쿼리 패턴을 분석해 인덱스를 구성하되, 불필요한 인덱스는 제거해 DML 성능을 높인다.

SQL은 필요한 칼럼만 조회하고, 인덱스를 활용할 수 있는 조건절을 작성하며, 조인 조건을 명확히 해야 한다. 대용량 테이블에서는 파티셔닝으로 접근 범위를 줄이고 파티션 프루닝(Partition Pruning)을 유도할 수 있다. 힌트는 이 과정으로도 옵티마이저의 판단을 바로잡기 어려울 때 제한적으로 검토한다.

통계와 접근 경로를 바로잡은 사례

대형 전자상거래 사이트의 주문 이력 조회는 30초 이상 걸렸고, 주문량이 많은 고객에게서는 타임아웃이 발생했다. 실행 계획을 확인하자 3천만 건 이상의 주문 테이블을 전체 스캔하고 있었으며, 고객 ID 인덱스는 존재하지만 사용되지 않았다. 통계 정보도 2년 이상 갱신되지 않은 상태였다.

통계 정보를 갱신하고 고객 ID 기준 파티셔닝을 도입한 뒤 인덱스를 재구성했다. 그 결과 쿼리 응답 시간은 30초에서 0.5초로 단축됐고, 60배 성능 향상과 시스템 전체 CPU 사용률 30% 감소가 나타났다.

금융 기관의 일일 배치 작업은 8시간 이상 걸렸으며, 고객 자산 집계 쿼리가 병목이었다. 실행 계획에서는 비효율적인 해시 조인과 대량 정렬에 따른 임시 영역 과도 사용이 확인됐고, 칼럼 통계 정보의 왜곡도 발견됐다.

칼럼 히스토그램을 생성해 데이터 분포 정보를 정확하게 만들고, 조인 순서 조정 힌트를 적용했으며, 파티션 테이블을 도입했다. 배치 작업 시간은 8시간에서 2시간으로 단축됐고, 75% 시간 절감과 디스크 I/O 60% 감소가 이어졌다.

실행 계획을 운영 대상으로 다루기

옵티마이저를 이해하면 같은 하드웨어 환경에서도 수십 배의 성능 향상을 이끌어낼 수 있다. 정확한 통계 정보, 적절한 인덱스 설계와 관리, 효율적인 SQL 작성, 필요한 경우의 제한적인 힌트 사용을 함께 관리해야 한다. 데이터베이스 성능 튜닝에서 옵티마이저는 투자 대비 효과를 크게 기대할 수 있는 영역이다.