(DB실무_SQLD 범위 제외) Part80-SQL 최적화 기본
2026년 11월 8일 공유
SQL 최적화 기본
개요
- 콘텐츠 유형: 강의
- SQL 문이 요구하는 결과를 얻기 위해, 실제로 데이터를 어떤 순서와 방법으로 처리할지 결정하는 SQL 최적화의 기본 원리를 다룬다.
- 주요 주제는 옵티마이저, 인덱스, 조인 수행 방식이다.
- SQL이 “무엇을 조회할지”를 표현한다면, 최적화는 “어떻게 효율적으로 조회할지”를 결정한다.
핵심 개념
옵티마이저
옵티마이저는 SQL 실행 전에 가능한 실행 계획을 검토하고, 비용을 비교해 적절한 계획을 선택한다. 실행 계획에는 접근 방식, 조인 순서와 기법 등이 포함된다.
- 규칙 기반 옵티마이저(RBO): 미리 정해진 규칙과 우선순위에 따라 계획을 선택한다. 단순하지만 모든 데이터 상황에 최적이라고 보장할 수 없다.
- 비용 기반 옵티마이저(CBO): 테이블·인덱스·컬럼의 통계 정보를 바탕으로 예상 비용을 계산하고 계획을 고른다. 통계가 부정확하면 선택도 부정확해질 수 있다.
- 비용 기반 옵티마이저는 실행 가능한 모든 계획을 검토하지 않고 후보 계획을 추려 평가하므로, 드물게 최적 계획을 놓칠 수 있다.
- 실행 계획의 비용과 행 수 등은 실제 실행 결과가 아니라 통계에 기반한 예상치다. 비용은 상대적 지표이며, 일반적으로 작을수록 유리하다.
인덱스와 스캔
- 인덱스는 데이터 검색을 빠르게 하지만, 지나치게 많이 만들면 저장 공간과 갱신 비용이 늘어난다.
- B-트리 인덱스는 동등 검색과 범위 검색에 널리 사용된다.
- 브랜치 노드: 검색 경로를 안내한다.
- 리프 노드: 인덱스 컬럼 값과 해당 행의 위치를 가리키는 식별자를 저장한다.
- 리프 노드 간 연결을 이용해 범위 검색을 수행할 수 있다.
- 유니크 스캔: 유일한 인덱스를 사용하고 인덱스 구성 컬럼이 모두 동등 조건인 경우 한 행을 찾는다.
- 레인지 스캔: 여러 행을 찾거나 범위 조건을 검색하는 경우에 사용된다.
- 풀 테이블 스캔: 테이블의 모든 블록을 읽는다. 대체로 많은 데이터를 읽지만, 조건에 맞는 행이 많거나 병렬 처리가 유리한 경우 옵티마이저가 선택할 수 있다. 조건이 없거나 조건 컬럼에 적절한 인덱스가 없는 경우에도 발생할 수 있다.
규칙 기반 옵티마이저의 인덱스 우선순위
- 강의에서는 규칙 기반 옵티마이저의 우선순위 중 ROWID 접근이 가장 빠른 방식이며, 그 뒤에 인덱스 접근 방식이 이어지고 풀 테이블 스캔은 최후의 방식으로 설명한다.
- 복합 인덱스 비교 시에는 인덱스 컬럼 순서와 조건을 고려한다.
- 인덱스 매칭률: 전체 구성 컬럼 중, 선두 컬럼부터 연속해서 동등 조건으로 사용된 컬럼의 비율이다.
- 매칭률이 높은 인덱스를 우선하며, 같으면 구성 컬럼이 더 많은 인덱스를 선택한다.
- 조건은 대체로 동등 조건, 경계가 정해진 범위 조건, 경계가 정해지지 않은 범위 조건 순으로 유리하게 평가된다. 실제 선택은 옵티마이저의 방식과 데이터 상황에 따라 달라질 수 있다.
조인 수행 방식
조인의 종류(예: 내부 조인, 외부 조인)는 어떤 결과를 반환할지를 정한다. 조인 기법은 그 결과를 어떤 방식으로 계산할지를 정한다.
- 중첩 루프 조인(NL 조인):
- 선행 테이블에서 조건에 맞는 행을 찾고, 각 행에 대해 후행 테이블을 조회한다.
- 조건을 만족하는 행이 적은 테이블을 선행 테이블로 두면 유리하다.
- 소규모 데이터에 적합하다.
- 정렬 병합 조인:
- 조인 기준 컬럼으로 양쪽 데이터를 정렬한 뒤 병합한다.
- 정렬 비용이 발생하지만, 인덱스 없이도 가능하고 비동등 조인에도 사용할 수 있다.
- 해시 조인:
- 한쪽 데이터를 해시 테이블로 만들고, 다른 쪽 데이터를 해시값으로 탐색한다.
- 대량 데이터의 동등 조인에 적합하며, 해시 계산 및 메모리 사용이 필요하다.
- 범위 조인에는 적합하지 않다.
- 옵티마이저는 조인 단계마다 서로 다른 기법을 선택할 수 있다.
상세 정리
옵티마이저가 실행 계획을 고르는 과정
- SQL 문을 해석한다.
- 질의를 변환하고 후보 실행 계획을 생성한다.
- 규칙 또는 통계 정보에 따라 후보 계획의 비용을 평가한다.
- 선택된 계획에 맞춰 데이터 접근과 조인을 수행한다.
조인 기법 비교
조인 기법 | 적합한 상황 | 주요 특징 및 고려 사항 |
|---|---|---|
중첩 루프 조인 | 소규모 데이터, 조건을 만족하는 행이 적은 경우 | 선행 테이블의 결과 행 수가 적을수록 유리 |
정렬 병합 조인 | 정렬 후 병합이 적합한 경우 | 정렬 비용이 들며 비동등 조인도 가능 |
해시 조인 | 대량 데이터의 동등 조인 | 해시 테이블을 메모리에 만들며 범위 조인에는 적합하지 않음 |
최종 요점
- SQL 최적화의 핵심은 동일한 결과를 더 효율적인 실행 계획으로 얻는 것이다.
- 옵티마이저의 선택은 통계 정보와 데이터 분포에 영향을 받는다.
- 인덱스가 항상 풀 테이블 스캔보다 빠른 것은 아니며, 실제 상황에 따라 풀 스캔이 더 효율적일 수 있다.
- 조인 방식은 데이터 규모와 조건에 따라 달라지며, 최적화의 기본은 각 방식의 특징과 비용을 이해하는 것이다.