SQL 옵티마이저

옵티마이저란

옵티마이저는 SQL을 어떤 순서와 방법으로 실행할지 결정하는 DBMS의 핵심 구성요소입니다. 사용자는 원하는 결과를 SQL로 선언하고, 옵티마이저는 그 결과를 얻는 방법을 고릅니다.

SELECT e.ename, d.dname
FROM emp e
JOIN dept d ON d.deptno = e.deptno
WHERE e.sal >= 3000;

이 SQL에 대해 옵티마이저는 emp를 먼저 읽을지, dept를 먼저 읽을지, 인덱스를 사용할지, 어떤 조인 방식을 쓸지 결정합니다.

규칙 기반과 비용 기반

방식	의미
규칙 기반	정해진 우선순위 규칙으로 실행 방법 선택
비용 기반	통계 정보를 이용해 예상 비용이 낮은 계획 선택

현대 DBMS는 대부분 비용 기반 옵티마이저를 사용합니다. 비용 기반에서는 통계 정보가 매우 중요합니다.

통계 정보

통계 정보는 테이블과 인덱스의 데이터 분포를 요약한 정보입니다.

• 테이블 행 수
• 블록 수
• 컬럼의 값 종류 수
• NULL 개수
• 값 분포
• 인덱스 높이와 선택도

통계가 오래되었거나 실제 데이터 분포를 잘 반영하지 못하면 옵티마이저가 잘못된 계획을 고를 수 있습니다.

카디널리티와 선택도

카디널리티는 조건을 적용한 뒤 예상되는 행 수입니다. 선택도는 전체 중 얼마나 선택되는지를 나타내는 비율입니다.

WHERE empno = 7788

이 조건은 보통 한 행만 선택하므로 카디널리티가 작고 선택도가 높습니다.

WHERE gender = 'M'

이 조건은 전체의 상당 부분을 선택할 수 있으므로 단독 인덱스 효과가 제한적일 수 있습니다.

힌트

힌트는 옵티마이저에게 실행 계획 방향을 제안하는 문법입니다.

SELECT /*+ USE_NL(e d) */ e.ename, d.dname
FROM emp e
JOIN dept d ON d.deptno = e.deptno;

힌트는 강력하지만 남용하면 영향이 큽니다. 데이터 양이나 분포가 바뀌었는데 힌트가 고정되어 있으면 오히려 나쁜 계획을 강제할 수 있습니다.

옵티마이저 문제를 의심할 때

• 예상 행 수와 실제 행 수가 크게 다릅니다.
• 작은 결과라고 예상하고 NL 조인을 골랐는데 실제 결과가 매우 큽니다.
• 인덱스가 있는데도 전체 스캔을 선택했습니다.
• 반대로 전체 스캔이 나을 상황인데 인덱스 반복 접근을 합니다.

한 문장 요약