[MYSQL] 옵티마이저와 실행 계획

컴퓨터의 두뇌가 CPU이듯 DBMS의 두뇌는 옵티마이저(Optimizer)다. 개발자가 SQL을 작성하고 실행하면 즉시 실행되는 게 아니라 옵티마이저가 "이 쿼리문을 어떻게 실행시키겠다"라는 다수의 실행계획을 먼저 세운다. 그리고 시스템 통계정보를 활용해 각 실행계획의 예상 비용을 산정한 뒤 최고의 효율을 가지고 있는 실행계획에 따라 쿼리를 수행한다.

 

https://zangzangs.tistory.com/103

 

 

옵티마이저는 실행 계획을 세우는 방식에 따라 규칙 기반과 비용 기반으로 나뉜다.

 

먼저 규칙 기반은 사전에 정의된 규칙을 기반으로 우선순위가 높은 규칙에 해당하는 실행계획을 생성한다. 반면 비용 기반은 각 실행 계획에 사용되는 비용(Cost)을 계산하고 최소비용에 해당하는 실행계획을 수립하는 방식이다. 요즘엔 비용 기반이 더욱 효율적으로 여겨져 대부분은 비용 기반 옵티마이저를 사용한다.

 

물론 옵티마이저가 통계 정보에 따라 최적의 실행 계획을 세운다. 그러나 현업에서 제공되는 통계 정보를 신뢰하기 어렵다는 문제가 있다. 테이블 자체가 고정돼 있지 않기 때문이다. 새로운 데이터가 수시로 들어오고 삭제되고 업데이트돼 정확한 통계 정보를 갖추기 어렵다. 따라서 퀴리 작성자의 판단에 따라 옵티마이저에 규칙을 줘야 한다.

 

MySQL 기준 옵티마이저가 수립하는 실행 계획은 다음과 같이 나뉜다.

 

[실행 계획의 항목]

id	SELECT를 구분하는 번호. 실행 순서를 표시하는 숫자
table	참조하는 테이블 항목
select_type	SELECT 문의 유형을 출력하는 항목
type	테이블의 데이터를 어떻게 찾을지에 관한 정보를 제공하는 항목. 조인 혹은 조회 타입
possible_keys	데이터를 조회할 때 DB에 서사용할 수 있는 인덱스 리스트. 옵티마이저가 SQL문을 최적화하고자 사용할 수 있는 인덱스 목록 출력
key	실제로 사용할 덱스. 옵티마이저가 SQL문을 최적화하고자 사용한 기본키(PK) 또는 인덱스명
key_len	실제로 사용할 인덱스의 길이
ref	reference의 약자. 테이블 조인시 어떤 조건으로 해당 테이블에 액세스 됐는지 알려주는 정보. Key 안의 인덱스와 비교하는 컬럼(상수)
rows	쿼리 실행시 조사하는 행 갯수
filtered	어느정도의 비율로 데이터를 제거했는지 의미하는 항목
extra	SQL문을 어떻게 수행할 것인지에 대한 추가 정보를 보여주는 항목

 

 

 

해당 유형을 샘플 데이터로 확인해보자.

EXPLAIN
SELECT *
FROM 사원
WHERE 사원번호 BETWEEN 11000 AND 11009;

 

 

순서대로 해석해보면 id는 쿼리에서 쓰인 SELECT의 갯수이자 실행 순서다. SELECT 타입은 UNION이나 서브쿼리가 없는 단순 SELECT문이며, 쿼리가 실행된 테이블은 '사원'이다. TYPE이 RANGE이므로 BETWEEN 함수를 통한 범위 조회다.

 

DB가 사용 가능한 인덱스는 PRIMARY고, 옵티마이저가 실제로 사용할 인덱스이자 기본키 역시 PRIMARY다. 그리고 해당 키의 길이는 4BYTE다. 쿼리가 조건절을 거쳐 실제로 DB에서 끌어온 ROWS는 10개이고 그 중 100%가 필터(데이터 제거비율 0%)됐다. 마지막으로 WHERE을 사용했다는 사실을 확인할 수 있다.