- 옵티마이저 -
옵티마이저는 SQL 명령을 수행할 때 최적 경로를 찾아주는 역할을 하는 DBMS의 핵심 엔진을 말한다. SQL 실행 시 옵티마이저는 여러가지의 실행계획 을 세우고 이 실행계획들의 예상 비용을 정리해서 제일 효율적인 실행계획에 따라서 쿼리를 수행한다.
옵티마이저의 종류
옵티마이저는 실행계획을 세우는 방식에 따라서 크게 규칙 기반 옵티마이저와 비용 기반 옵티마이저 두 가지로 나뉜다.
1. 규칙 기반 옵티마이저(RBO, Rule-Based Optimizer)
규칙 기반 옵티마이저는 아래 테이블과 같이 실행 속도가 빠른 순으로 규칙들을 세워두고 우선순위가 높은 앞서는 방법을 채택한다. 이 방식은 과거 예상 비용을 계산하는 능력이 그리 좋지 않아서 사용했었던 방식이다.
| 순위 | 설명 |
|---|---|
| 1 | ROWID를 사용한 단일 행인 경우 |
| 2 | 클러스터 조인에 의한 단일 행인 경우 |
| 3 | 유일하거나 기본키(Primary Key)를 가진 해시 클러스터 키에 의한 단일 행인 경우 |
| 4 | 유일하거나 기본키(Primary Key)에 의한 단일 행인 경우 |
| 5 | 클러스터 조인인 경우 |
| 6 | 해시 클러스터 조인인 경우 |
| 7 | 인덱스 클러스터 키인 경우 |
| 8 | 복합 칼럼 인덱스인 경우 |
| 9 | 단일 칼럼 인덱스인 경우 |
| 10 | 인덱스가 구성된 칼럼에서 제한된 범위를 검색하는 경우 |
| 11 | 인덱스가 구성된 칼럼에서 무제한 범위를 검색하는 경우 |
| 12 | 정렬-병합(Sort-Merge) 조인인 경우 |
| 13 | 인덱스가 구성된 칼럼에서 MAX 혹은 MIN을 구하는 경우 |
| 14 | 인덱스가 구성된 칼럼에서 ORDER BY를 실행하는 경우 |
| 15 | 전체 테이블을 스캔(FULL TABLE SCAN)하는 경우 |
위 테이블을 이해하고 있으면 쿼리문을 작성할 때 보다 효율적으로 작성할 수 있다. 하지만 테이블에 row가 적은 경우에는 FULL TABLE SCAN(순위:15)를 하는게 더 빠를 수 있는데 INDEX 를 사용한다거나 하는 비효율적인 실행계획이 도출될 수도 있다는 단점도 있다.
또 옵티마이저의 실행계획을 유도하는 힌트나 HASH JOIN는 규칙 기반 옵티마이저 이후에 나온 개념이므로 여기서는 사용할 수 없다는 단점이 있다.
2. 비용 기반 옵티마이저(CBO, Cost-Based Optimizer)
비용 기반 옵티마이저는 쿼리를 수행하는데 소요되는 일의 양 혹은 시간을 기준으로 최적화를 수행한다. 다양한 객체 통계정보(테이블, 컬럼, 인덱스)와 시스템 통계정보(CPU, 디스크 I/O 속도 등)를 기반으로 최대 2000 개의 실행계획을 세우고 최소 비용의 실행계획을 수행한다. 통계정보가 없는 경우에는 비효율적인 실행계획을 수립할 수 있기 때문에 통계정보를 정확하게 유지할 수 있도록 해야한다.
SQL 최적화 과정
- Parser
- SQL을 파싱해서 파싱 트리(내부적인 구조체)를 만듣나. 문법적 오류나 의미상 오류를 체크한다
- Optimizer
- Query Transformer : 파싱된 SQL을 좀 더 일반적이고 표준적인 형태로 변환한다.
- Estimator : 객체, 시스템 통계정보를 활용해 쿼리 수행 각 단계의 선택도, 카디널리티, 비용을 계산하고 실행계획 전체에 대한 총 비용을 계산한다.
- Plan Generator : 하나의 쿼리를 수행하는데 있어서 후보군이 될만한 실행계획을 생성해낸다.
- Row-Source Generation
- 옵티마이저가 생성한 실행계획을 SQL 엔진이 실제로 실행할 수 있는 코드(또는 프로시저) 형태로 변환한다
- SQL Engine
- SQL을 실행한다.
옵티마이저 행동에 영향을 미치는 요소
- SQL과 연산자 형태
- SQL의 형태에 따라서 다른 선택을 하고 쿼리 성능에 영향을 미친다
- 옵티마이징 팩터
- 쿼리가 같아도 인덱스, 클러스터링, 파티셔닝 등의 구성 형태에 따라 실행계획과 성능이 달라진다
- DBMS 제약 설정
- PK, FK, NOT NULL 과 같은 제약 설정 기능은 쿼리 성능 최적화에 중요한 정보를 제공한다. 예를 들어서 인덱스 칼럼에 NOT NULL 이 설정되어 있으면 옵티마이저는 전체 개수를 구하는 COUNT 쿼리에 이 인덱스를 활용할 수 있다.
- 옵티마이저 힌트
- 사용자가 지정한 힌트를 우선한다.
- 통계정보
- CBO의 모든 판단 기준은 통계정보에서 기인한다.
- 옵티마이저 관련 파라미터
- SQL, 데이터, 통계정보, H/W 등의 모든 환경에 동일해도 DBMS 버전을 업데이트하면 옵티마이저가 다르게 작동할 수 있다. 옵티마이저 관련 파라미터가 변경되기 때문이다.
- DBMS 버전과 종류
- 옵티마이저 관련 파라미터가 같아도 버전에 따라서 실행계획이 달라질 수 있다.
- 실행계획 -
SCAN의 종류와 속도
- FULL TABLE SCAN
- 테이블 전체 데이터를 읽어서 조건에 맞는 데이터를 추출하는 방식
- ROWID SCAN
- ROW ID 를 기준으로 데이터를 추출하고 단일 행에 접근하는 방식 중에선 가장 빠름
- INDEX SCAN
- 인덱스를 활용해서 원하는 데이터를 추출하는 방식
보통 FULL TABLE SCAN 과 INDEX SCAN 을 손 쉽게 유도할 수 있다. 보통 데이터가 적은 테이블에서는 INDEX 를 타면서 시간을 소요할 필요가 없이 FULL TABLE SCAN을 하는게 낫고, 테이블의 크기가 크면 INDEX SCAN을 하는 것이 좋다.
FULL TABLE SCAN 을 타는 상황
- 조건 절에서 비교한 컬럼에 인덱스가 없는 경우
- 조건 절에서 비교한 컬럼에 최적화된 인덱스는 있지만 조건을 만족하는 데이터가 테이블의 많은 양을 차지해서 FULL TABLE SCAN이 낫다고 옵티마이저가 판단하는 경우
- 인덱스가 존재하지만 테이블 데이터 양 자체가 적어서 FULL TABLE SCAN이 낫다고 옵티마이저가 판단하는 경우
- 테이블 생성 시 DEGREE 속성 값이 크게 설정되어 있는 경우 (DEGREE, ATTRIBUTE의 개수)
당연한 말이지만 INDEX SCAN이 유리한데도 옵티마이저가 FULL TABLE SCAN을 탄다면 INDEX를 생성하는 것이 좋다. 물론 INDEX를 필요할 때마다 만드는 것은 UPDATE, DELETE 등의 속도를 저해하기 때문에 마냥 좋은 것은 아니다.
ROWID SCAN 을 타는 상황
- 조건 절에서 ROWID를 직접 명시하는 경우
- INDEX SCAN을 통해서 ROWID를 추출한 후 테이블에 접근하는 경우
ROWID SCAN은 단일 행 접근이 매우 빠르기 때문에 ROWID SCAN이 유리하다고 판단되는 경우는 대부분 ROWID SCAN을 타는 것이 가장 유리하다.
INDEX SCAN 을 타는 상황
다양한 상황에서 다양한 종류의 INDEX SCAN 이 사용된다.
- INDEX UNIQUE SCAN
- UNIQUE INDEX 를 구성하는 모든 컬럼이 조건에
=로 명시된 경우
- UNIQUE INDEX 를 구성하는 모든 컬럼이 조건에
- INDEX RANGE SCAN
- UNIQUE 성격의 결한 인덱스의 선두 컬럼이 WHERE 절에서 사용되는 경우
- 일반 인덱스의 컬럼이 WHERE 절에 존재하는 경우
- INDEX RANGE SCAN DESCENDING
- INDEX RANGE SCAN 을 수행함과 동시에 ORDER BY DESC 절을 만족하는 경우
- INDEX SKIP SCAN
- 결합 인덱스의 선행 컬럼이 WHERE 절에 있는 경우
- 옵티마이저가 INDEX SKIP SCAN 이 FULL TABLE SCAN 보다 낫다고 판단하는 경우
- INDEX FULL SCAN
- ORDER BY/ GROUP BY 의 모든 컬럼이 인덱스 전체 또는 일부로 정의된 경우
- 정렬이 필요한 명령에서 INDEX ENTRY 를 순차적으로 읽는 방식으로 처리된 경우
- INDEX FULL SCAN DESCENDING
- INDEX FULL SCAN 을 수행함과 동시 ORDER BY DESC 절을 만족하는 경우
- INDEX FAST FULL SCAN
- FULL TABLE SCAN 을 하지 않고도 INDEX FAST FULL TABLE SCAN 으로 원하는 데이터를 추출할 수 있고, 추출된 데이터의 정렬이 필요 없으며, 결합 인덱스를 구성하는 컬럼 중 최소 한 개 이상은 NOT NULL 인 경우
- INDEX JOIN
- 추출하고자 하는 데이터가 조인하는 인덱스에 모두 포함되어 있으며 추출하는 데이터의 정렬이 필요없는 경우
- 인덱스 -
인덱스는 별도의 TABLE을 만들어 대상 TABLE 의 데이터를 정렬하고 해당 데이터의 물리적 위치를 VALUE로 갖는다. 대부분의 DB 속도 저하가 SELECT 문 특히 조건 검색 시 발생하기 때문에 가장 먼저 대안으로 INDEX를 생각해볼 수도 있다.
인덱스 단편화
1. INDEX SKEW
Index Skew는 인덱스 엔트리가 왼쪽 또는 오른쪽에 치우치는 현상을 말한다.
예를 들어, 대량의 delete 작업을 마치고 나면 위 그림처럼 인덱스 왼쪽에 있는 리프 블록들은 텅 비는 반면 오른쪽은 꽉 찬 상태가 된다
Oracle의 경우 텅 빈 인덱스 블록은 커밋하는 순간 freelist로 반환되지만 인덱스 구조 상에는 그대로 남는다. 상위 브랜치에서 해당 리프 블록을 가리키는 엔트리가 그대로 남아 있어 인덱스 정렬 순서상 그 곳에 입력될 새로운 값이 들어오면 언제든 재사용될 수 있다. 문제는 다시 채워질 때까지 인덱스 스캔 효율이 낮다는 데에 있다. SQL Server에선 Index Skew 현상이 발생하지 않는다. 주기적으로 B+Tree 인덱스를 체크함으로써 지워진 레코드와 페이지를 정리해 주는 메커니즘을 갖기 때문이다. 인덱스 레코드를 지우면 표시해뒀다가 별도 쓰레드에 의해 비동기 방식으로 제거되는데, 그 과정에서 텅 빈 페이지가 발견되면 인덱스 구조에서 제거된다.
2. Index Sparse
Index Sparse는 인덱스 블록 전반에 걸쳐 밀도(density)가 떨어지는 현상을 말한다.
지워진 자리에 인덱스 정렬 순서에 따라 새로운 값이 입력되면 그 공간은 재사용되지만 위와 같은 대량의 delete 작업이 있고 난 후 한동안 인덱스 스캔 효율이 낮다는 데에 문제가 생긴다. 왼쪽, 오른쪽, 중간 어디든 Index Skew처럼 블록이 아예 텅 비면 곧바로 freelist로 반환돼 언제든 재사용되지만, Index Sparse는 지워진 자리에 새로운 값이 입력되지 않으면 영영 재사용되지 않을 수도 있다. 총 레코드 건수가 일정한데도 인덱스 공간 사용량이 계속 커지는 것은 대개 이런 현상에 기인한다.
3. 인덱스 재생성
Fragmentation 때문에 인덱스 크기가 계속 증가하고 스캔 효율이 나빠지면 인덱스를 재생성하거나 DBMS가 제공하는 명령어를 이용해 빈 공간을 제거하는 것이 유용할 수 있다. 하지만 일반적으로 인덱스 블록에는 어느 정도 공간을 남겨두는 것이 좋다. 왜냐하면, 빈 공간을 제거해 인덱스 구조를 슬림(slim)화하면 저장 효율이나 스캔 효율엔 좋겠지만 인덱스 분할이 자주 발생해 DML 성능이 나빠질 수 있기 때문이다. 인덱스 분할에 의한 경합을 줄일 목적으로, 초기부터 빈 공간을 남기도록 옵션을 주고 인덱스를 재성성할 수도 있다. 하지만 그 효과는 일시적이다. 언젠가 빈 공간이 다시 채워지기 때문이며, 결국 적당한 시점마다 재생성 작업을 반복하지 않는 한 근본적인 해결책이 되지는 못한다. 인덱스를 재생성하는 데 걸리는 시간과 부하도 무시할 수 없다. 따라서 인덱스의 주기적인 재생성 작업은 아래와 같이 예상효과가 확실할 때만 시행하는 것이 바람직하다.
- 인덱스 분할에 의한 경합이 현저히 높을 때
- 자주 사용되는 인덱스 스캔 효율을 높이고자 할 때. 특히 인덱스 높이가 증가했을 때
- 대량의 delete 작업을 수행한 이후 다시 레코드가 입력되기까지 오랜 기간이 소요될 때
- 총 레코드 수가 일정한데도 인덱스가 계속 커질 때
결합 인덱스
결합 인덱스란 두 개 이상의 컬럼을 묶어서 인덱스로 만들어진 인덱스를 말한다. AND 조건절에 많이 사용되는 컬럼들을 묶어서 테이블을 생성한다. 결합 인덱스는 컬럼의 순서에 따라서 성능에 많은 영향을 받게 된다. 특히 첫번째 컬럼이 많은 역할을 한다.
컬럼 설정 우선 순위
- WHERE절 조건에 많이 사용되는 컬럼이 우선시
- 첫번째 컬럼을 조건에서 사용하지 않으면 그 인덱스는 사용되지 않는 경우가 대부분이기 때문에 많은 쿼리에서 공통적으로 사용된 조건절의 컬럼을 선행 컬럼으로 사용해야 한다.
- Equal('=')로 사용되는 컬럼 우선
- 선행 컬럼이
=가 아니라면 후행 컬럼 조건에서=를 사용해도 처리범위는 줄어들지 않는다.
- 분포도가 좋은 컬럼을 우선
- 분포도가 좋은 컬럼은 처리 범위를 줄여주기 때문에 첫 컬럼(선행컬럼)으로 해줘야 한다.
- 자주 이용되는 순서대로 결합 인덱스 컬럼의 순서 결정
출처
