옵티마이저(Optimizer)

옵티마이저란, SQL을 가장 빠르고 효율적으로 수행할 최적(최저비용)의 처리 경로를 생성해주는 데이터베이스의 핵심엔진이다.

여기서 사용한 DB는 MariaDB이다.

옵티마이저 엔진

• Parser : SQL 문장의 각 요소를 파싱해서 파싱트리를 만듦(문법 검사, 구문 분석)
• Query Transformer : SQL문을 효율적으로 실행하기 위해 더 일반적이고 표준적인 형태로 옵티마이저 변환
• Estimator : 시스템 통계정보를 사용해서 SQL 실행비용을 계산
• Plan Generator : SQL을 실행할 계획들을 수립
• Row-Source Generator : 옵티마이저가 생성한 계획을 SQL Engine이 실행 가능한 코드로 포맷
• SQL Engine : SQL 실행

컴퓨터의 핵심이 CPU인 것처럼 DBMS의 핵심은 옵티마이저라고 할 수 있다. 우리가 SQL을 작성하고 실행하면 소프트웨어 실행파일처럼 즉시 실행되는 것이 아니라 옵티마이저(Optimizer)라는 곳에서 어떤 동작으로 실행할지 여러 가지 실행계획을 세우게 된다. 이렇게 실행계획을 세운 뒤 시스템 통계정보를 활용하여 각 실행계획의 예상 비용을 산정한 후 각 실행계획을 비교해서 최고의 효율을 가지고 있는 실행계획을 판별한 후 그 실행계획에 따라 쿼리를 수행하게 되는 것입니다.

옵티마이저가 선택한 실행 방법의 적절성 여부는 쿼리 수행 속도에 가장 큰 영향을 미친다.

옵티마이저 종류

규칙기반 옵티마이저

미리 정해놓은 규칙(액세스 경로별 우선순위)에 따라 액세스 경로를 평가하고 실행계획을 선택함.

비용기반 옵티마이저

예상되는 비용(쿼리 수행에 필요한 시간)을 기반으로 최적화를 수행한다. 미리 구한 테이블과 인덱스에 대한 통계정보를 기초로 각 오퍼레이션 단계별 예상 비용을 산정하고, 총비용이 가장 낮은 계획을 선택한다.(부적절한 통계정보의 경우 성능 저하 우려)

우리는 이 비용기반 옵티마이저를 주로 사용할 것 같다.

옵티마이저 특징

• 서론에서 말했듯이, 시스템 통계 정보를 사용해서 예상되는 비용을 산정하고 최저비용 가지고 있는 계획을 선택해서 SQL을 실행한다.
• 옵티마이저는 자동으로 하다보면 비효율적으로 실행 계획을 구성할 수가 있는데 사용자는 힌트(HINT)를 줌으로써 실행 계획을 변경할 수 있다.

실행계획 확인하는 방법

실행계획을 확인하는 것은 explain을 쿼리문 앞에 붙여주면 된다.
explain select * from posts 를 실행해 보았다.

실행계획은 이렇게 나온다.

id컬럼은 쿼리별로 부여되는 식별자.

select_type컬럼 은 기본 SELECT를 실행하면 SIMPLE이 나오는데
PRIMARY는 UNION이나 서브쿼리가 포함된 SELECT 쿼리의 실행 계획중 가장 바깥의 단위 쿼리는 PRIMARY로 나온다.

type컬럼은 SQL서버가 테이블의 레코드를 어떻게 읽었는가를 알려주는 지표이다. 방식은 사용자 정의 인덱스를 활용하여 읽었는지, (기본값)테이블 풀 스캔으로 읽었는지에 대한 결과가 나온다.

• All : 다른 접근방법으로 처리할 수 없을 때 사용하는 마지막 선택이기 때문에 가장 비효율적 방법이다.
• index : 인덱스를 활용해 읽었을 경우 표시됨. 테이블 풀 스캔보다 빠르다.

이 옵티마이저를 튜닝하려고 할 때 인덱스를 많이 사용하는데 필요한 곳에만 인덱스를 걸어주도록 하자.
무의미하게 인덱스를 걸면 오히려 역효과를 초래할 수가 있다.

인덱스를 줄 때는 카디널리티가 높은 칼럼을 매칭하여 인덱스를 할당해야 한다.
카디널리티가 높다는 것은 총 row수 * 선택도이다.
그러니까 다시말하자면, 고를 수 있는 조건이 많은 칼럼을 주는 것이다.

...

이런식으로 테이블이 있다고 가정할 때 선택도가 가장 많은 칼럼은 당연히 주민번호 일 것이다. 왜냐면 고유한 정보이므로 카디널리티가 다른 칼럼보다 월등히 높다. 이런 칼럼에 인덱스를 부여하면 옵티마이저를 튜닝함으로써 쿼리문을 엄청 빠르게 수행할 수 있게 도와줄 것이다.