[MySQL] EXPLAIN 사용하기

EXPLAIN이란?

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

 

실행 코드는 매우 간단하다

EXPLAIN [EXTENDED] SELECT ... FROM ... WHERE ...

 

이렇게 명령어를 입력해준 다음, 실행 결과를 분석하여 성능을 최적화할 수 있다.

 

 

MYSQL EXPLAIN 사용 이유

DATABASE 스키마를 작성하고 쿼리를 날렸을 때 속도가 저하되는 부분이 보이면 스키마를 수정했을 때 모든 쿼리에 영향을 줄 수 있기 때문에 스키마를 수정하기보다는 쿼리를 먼저 수정해야 한다.
쿼리를 수정할려면 MYSQL 이 어떻게 데이터를 찾고 있는지 알아야하고 여기서 MYSQL EXAPLIN을 사용한다.

 

 

 

 

각 필드의 의미

id

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

select type

• select 문의 유형
• SIMPLE, PRIMARY, SUBQUERY, DERIVED, UNION 등

table

• 참조하는 테이블 이름 (또는 별칭)

type

• 매우 중요함
• 테이블에서 어떻게 행의 데이터를 가져오는지에 대한 데이터 접근 방식을 나타내는 필드
• 성능 좋은 순
  • system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
• ALL
  • Full Table Scan
  • 가장 안 좋은 경우
  • 인덱스를 전혀 사용하지 않고 테이블 전체를 스캔
• index
  • Full Index Scan
  • 인덱스 전체를 스캔
• range
  • 인덱스를 사용하여 특정 범위의 행을 스캔
  • BETWEEN, >, <
• ref
  • 인덱스를 사용하여 특정 값과 일치하는 행을 찾는다
  • Non-unique index
• eq_ref
  • JOIN 시 Primary Key 또는 Unique Index를 사용하여 정확히 하나의 행만 읽는다
• system
  • 단일 행 쿼리
  • 테이블에 단 한 건의 행만 반환하는 경우 (가장 빠른 엑세스 방법)
• const
  • 단일 행 쿼리
  • 인덱스를 사용하여 상수 조건으로 특정 행을 찾는 경우 (PK나 UNIQUE index 사용한 조건 검색)

possible_keys

• MySQL이 사용할 수 있다고 판단한 인덱스 목록

key

• 실제 옵티마이저가 최적의 실행 계획에 사용된 인덱스를 나타내는 필드
• NULL이면 인덱스를 사용하지 않은 것

key_len

• 인덱스의 길이
• 짧을 수록 좋다

ref

• key column의 인덱스에서 값을 찾기 위해 테이블의 어떤 column이 비교되었는지 표시

rows

• 실행 결과의 행의 예측 값을 의미

filtered

• WHERE 조건에 의해 필터링된 후 남은 행의 비율(%)
• 값이 낮다면 인덱스로 많은 행을 가져온 후 WHERE 절에서 대부분 걸러낸다는 의미이므로 비효율적일 수 있다

Extra

• 쿼리 실행에 대한 추가 정보
• Using Index
  • Covering Index 사용
  • 테이블에 접근하지 않고 인덱스만으로 결과 반환
  • 매우 좋음
• Using where
  • 인덱스를 사용하여 행을 가져온 후, 스토리지 엔진 레벨에서 필터링 할 수 없는 나머지 WHERE 조건으로 MYSQL 서버 레벨에서 추가 필터링을 수행
  • 일반적으로 나타나는 결과
• Using temporary
  • 정렬이나 그룹화를 위해 임시 테이블을 사용했음을 의미
  • 성능 저하 요인
• Using filesort
  • ORDER BY 절을 처리하기 위해 인덱스를 사용하지 못하고 별도의 정렬 작업(filesort)를 수행했음을 의미
  • 성능 저하 요인
• Using join buffer
  • 조인 시 인덱스를 제대로 활용하지 못해 join buffer 사용했다는 걸 의미

 

 

 

 

실행 계획 분석하기

• type 분석하기
  • 쿼리가 데이터를 읽는 방법 설명
  • 쿼리의 성능에 중대한 영향을 미침
  • type은 성능에 따라 우선 순위가 지정되어 있다
• extra 분석하기
  • Using fileosrt, Using temporary 등 쿼리 실행에 대한 추가 정보를 보고 어느 부분에서 최적화를 할 수 있는지 파악할 수 있다.

이 두 가지를 보고 성능 최적화할 부분을 고민해 복합 인덱스를 사용하거나 조인을 하는 등 개선 방안을 계획할 수 있다.