DB 옵티마이저와 실행계획

옵티마이저(Optimizer)란?

가장 효율적인 방법으로 SQL을 수행할 최적의 처리 경로를 생성해주는 DBMS의 핵심 엔진입니다.

컴퓨터의 두뇌가 CPU라면 DBMS의 두뇌는 옵티마이저입니다.

 

만약 개발자가 SQL을 작성하고 실행하면 옵티마이저는 쿼리문을 어떻게 실행시키기 위하여 실행계획을 세우게 됩니다.

 

실행계획을 세운 뒤 시스템 통계정보를 활용하여 각 실행계획의 예상 비용을 산정한 후 각 실행계획을 비교해서 최고의 효율을 가지고 있는 실행계획을 판별한 후 해당 쿼리를 수행합니다.

 

옵티마이저가 하는 일을 요약하자면 다음과 같습니다.

1. 여러 가지 실행계획을 세운다.

2. 실행계획들을 비교하여 최적의 상황을 찾는다.

 

(DB 실행과정)https://zangzangs.tistory.com/103

 

규칙 기반 옵티마이저

인덱스의 유무, SQL에서 사용하는 연산자 등을 고려하여 우선순위가 높은 규칙에 해당하는 실행계획을 세웁니다.

 

비용 기반 옵티마이저

단순한 몇 개의 규칙으로 현실의 모든 사항을 예측할 수 없기 때문에 SQL문을 처리하는데 필요한 비용이 가장 적은 실행계획을 선택하는 방식

 

정확한 통계정보를 유지하는 것은 비용기반 최적화에서 가장 중요한 요소

 

 

대부분의 RDBMS가 비용 기반 옵티마이저를 채택하고 있으며 MySQL 역시 비용 기반 옵티마이저를 사용합니다.

---

실행계획(Excution Plan)이란?

동일한 SQL이더라도 어떻게 실행하는지에 따라 성능이 달라질 수 있습니다.

SQL에서 요구한 사항을 처리하기 위한 절차와 방법을 의미합니다.

실행계획의 형태는 조인 순서, 조인 기법, 액세스 기법, 최적화 정보, 연산 등이 있습니다.

 

MySQL에서는 EXPLAIN이라는 구문을 활용하여 실행계획을 볼 수 있습니다.

explain select * from board;

다음은 select * from board의 실행계획입니다.

explain select * from board;

id : 쿼리 내의 select 문의 순서

 

select_type : select문의 유형(여기서 SIMPLE은 단순 select로 union이나 subquery를 사용하지 않음을 의미합니다)

 

table : 참조하고 있는 테이블명

 

type : 조인 타입이며 속도와 아주 밀접한 항목 (여기서 ALL은 풀스 캔을 의미합니다 만약 where 절을 추가하면 range가 나타나며 인덱스를 사용하여 범위 내의 행들만 추출함을 의미합니다)

 

possible_keys : 해당 테이블 검색에 사용할 수 있는 인덱스를 나타냅니다.

 

key : 실제 사용한 index를 나타냄

 

key_len : 사용한 인덱스의 길이

 

ref : 키와 함께 사용된 칼럼이나 상수값을 의미

 

rows : 쿼리 수행에 MySQL이 찾아야 하는 데이터 행 수의 예상 값

 

filtered : 조건에 의해 필터링될 테이블 행의 예상 비율

 

Extra : MySQL이 쿼리를 어떻게 해석하는지

 

다음은 where 절을 활용했을 때 예시입니다.

explain select * from board; where articleno > 10;

---

시스템 통계 정보란?

실행 계획 도출 시 DBMS에서 제공하는 통계 정보를 사용합니다.

 

주요 통계 정보들은 다음과 같습니다.

• 테이블 : 테이블의 전체 행의 개수, 테이블의 행들이 가지고 있는 평균 길이 등
• 칼럼 : 컬럼 값의 종류, 컬럼 값의 평균 길이, NULL 값의 분포도 등
• 인덱스 :  인덱스 트리의 LEVEL(깊이) 정보, LEAF NODE의 개수 등
• 시스템 : I/O, CPU 성능 및 사용률

 

 

 

 

 

 

 

출처

https://gradle.tistory.com/4

[MySQL] 실행계획 (explain) 보는법

https://juntcom.tistory.com/15

MySql 옵티마이져 실행 개요

https://coding-factory.tistory.com/743

[DB] 데이터베이스 옵티마이저(Optimizer)에 대하여