Query Plan과 옵티마이저

💡 Query Plan이란?

SQL을 처리하는 최저비용의 경로를 생성해주는 DBMS 내부 핵심엔진인 쿼리 옵티마이저가 쿼리를 수행할 때 생성한 최적의 처리경로를 말한다.

쿼리 실행계획을 통해서 문제가 발생하고 있는 쿼리를 볼 수 있을뿐더러 최적화하는 방법까지 고민할 수 있다.

Query Plan 실행 순서는 아래와 같다.

• SQL 문법 해석 및 분석 : 우리가 적은 쿼리를 해석하고 분석

• 최적화 : 옵티마이저가 여러 계획 중에서 테이블 정보, 인덱스, 조인 등을 여러 정보를 통해 가장 최적의 계획을 선택

• 실행 계획 수립 : 선택된 계획으로 실제 데이터에 접근

• 실행 : 최종적으로 쿼리를 실행

💡 옵티마이저란?

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

DBMS의 두뇌라고 생각하면되는데 개발자가 SQL을 작성하면 즉시 실행되는것이 아닌 옵티마이저가 어떤식으로 실행을 시킬지 계획을 세우게 된다.

옵티마어지는 아래와 같이 크게 2가지 종류가 있다.

• 비용 기반 최적화 (Cost-Based Optimizer, CBO)

  • 최근 대부분 DBMS가 이용
  • 쿼리를 처리하기 위해 여러 방법을 만들고 각 작업에 대한 비용과 테이블의 통계 정보를 이용해서 비용을 산출 후 적은 걸 선택
  • MySQL 5.7부터 InnoDB 스토리지 엔진에서 비용 기반을 선택
  • 실행 계획을 세운 뒤(최대 2천개까지) 비용이 최소한으로 나온 실행 계획을 수행

비용기반 옵티마이저의 모드는 여러개가 있는데 비용을 구하는 방식이 조금씩 다르다.

• 규칙 기반 최적화 (Rule-Based Optimizer, RBO)
  • 예전 버전 DBMS에서 사용되었지만 지금은 사용 안 함
  • 대상 테이블의 레코드 건수나 선택 등을 고려하지 않은 채 Optimizer에 내장된 우선순위로 실행 계획을 정함
  • 실행 속도가 빠른 순으로 규칙을 세워두고 우선순위가 앞서는 방법을 선택
    • 과거에는 옵티마이저의 능력이 좋지않았기에 이러한 방식을 사용

규칙 기반 최적화의 우선순위는 아래와 같다.

💡 Query Plan 사용하기

MYSQL에서 작성할 쿼리문앞에 EXPLAN 키워드를 사용하여 실행계획을 확인할 수 있다.

EXPLAIN SELECT * FROM MEMBER;

위의 쿼리문을 입력하면 실행계획이 나오게되는데 실행계획의 상세 내용은 아래와 같다.

쿼리 실행 계획 - 항목별 상세 내용

중요하게 봐야할 부분은 select type, type, ref이다.

select type 종류

type 종류
type은 데이터를 조회할 때 키와 인덱스를 어떻게 활용하는지를 알려준다.

Extra