MySQL Explain이란?

1. Explain?

• Explain이란 실행계획으로, DB서버가 어떤 쿼리를 실행할 것인가를 알고싶을 때 사용하는 명령어이다.

• DB에는 옵티마이저라는 엔진이 존재하는데, 가장 효율적인 방법으로 sql을 수행할 최적의 처리 경로를 생성해주는 dbms의 핵심 엔진이다.
  옵티마이저는 개발자가 sql을 작성하면 바로 쿼리문을 실행하는 것이 아닌, 쿼리문을 어떻게 실행시킬지에 대한 여러 실행 계획을 세운 뒤, 예상 비용을 고려해 가장 효율이 좋은 실행 계획으로 쿼리를 실행시키는 방식으로 수행된다.

• Explain은 이런 옵티마이저의 실행 계획이다.

2. Explain 사용해보기

Fruit Entity

@Entity
@Builder
@AllArgsConstructor
@NoArgsConstructor
public class Fruit {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long fruitId;

    private String name;

    private Integer price;

    private String origin;

}

실제 DB 데이터

MySQL explain 쿼리

explain
select *
from fruit f
where f.name = '포도'
  and f.origin = '한국'
  and f.price = '2000'
;

2-1. index 사용전 explain 결과

2-2. explain 사용후 explain 결과

인덱스를 사용할 때와 인덱스를 사용하지 않는 경우 explain의 데이터들이 다른 것을 알 수 있다.

인덱스를 사용하지 않을 때는
1. type = ALL
2. possilbe_keys = null
3. key = null
...

등으로 인덱스를 사용하지 않을 때와 실행결과가 다른데, 아래의 explain의 각 필드의 의미를 보면 어떻게 다른지 이해할 수 있다.

3. explain의 각 필드의 의미

• id : 어떤 select문을 수행했는지 알려주는 번호이다.
  쿼리에 서브쿼리나 union이 없다면, select문은 하나이기에 위의 explain의 경우도 id에 1이 부여된다.
  서브쿼리나 union이 존재하면, 순서에 따라 id값이 부여된다.

• select type : select 문의 유형
  ex) simple : 서브쿼리나 union이 없는 select문

• table : 어떤 테이블에 접근하는지 나타내는 필드이다.
  위의 쿼리는 별칭을 사용했기에 f가 부여된다.

• type : 테이블에서 어떻게 행의 데이터를 가져오는지에 대한 데이터 접근방식을 나타내는 필드이다.
  ex) ALL : 테이블 풀스캔
  ref : 조인할 때, pk나 uk가 아닌 key로 매칭 하는 경우

• possible_keys : 쿼리에 접근하는 컬럼들에 사용된 연산자들을 통해, 어떤 인덱스들을 사용할 수 있는지 나타내는 필드이다.
  위의 경우 name과 idx_origin 인덱스를 사용할 수 있다.

• key : 실제 옵티마어저가 최적의 실행계획에 사용된 인덱스를 나타내는 필드이다.

• key_len : 옵티마이저가 선택한 인덱스의 길이를 의미한다.

• ref : key 컬럼에 나와있는 인덱스에서 값을 찾기 위해 테이블의 어떤 컬럼이 비교되었는지를 표시한다.

• rows : 실행 결과의 행의 예측값을 의미한다.