[DB] MySQL EXPLAIN 쿼리 실행 계획 분석 방법

💡발단

프로젝트의 필수 구현이 마무리되고, 리팩토링을 하면서 쿼리를 최적화할 일이 생겼습니다.

경험적으로, 감으로 하기보다는 체계적으로 최적화할 방법을 고민하던중에 EXPLAIN(쿼리 실행 계획 분석)을 알게 되었습니다.

그 과정에서 EXPLAIN을 공부하게 된 내용을 서술합니다.

 

EXPLAIN

🤔 그게 뭔데?

MySQL에서는 최적의 쿼리를 실행하기 위해 각 테이블의 데이터가 어떤 분포로 저장되어 있는지를 참조하고, 해당 데이터를 기반으로 최적의 실행 계획을 수립해주는 쿼리 옵티마이저가 내부적으로 동작한다.

EXPLAIN이란 여기서 MySQL 옵티마이저가 생성하는 실행 계획을 테이블(엑셀에 가까운) 형태로 볼 수 있게 하는 명령이다.

https://coderpad.io/blog/development/optimize-query-performance-mysql/

사용 방법

간단하다.

SQL 앞에 EXPLAIN을 명령을 넣고 실행하면 된다.

이를 통해 내가 짜고 있는 쿼리가 효율적인지 아닌지를 파악할 수 있다.

EXPLAIN
select *
from comment;

 

EXPLAIN
select *
from comment
where comment_id < 30;

IN 절을 쓰거나 비교 연산을 하면 type은 range가 된다.

rows는 옵티마이저를 타고 나온 대상 레코드의 개수이다. 여기선 3 .

 

 

아래는 유저가 차단한 사용자가 작성한 것을 제외한 게시글들을 조회하는 쿼리다.

 

EXPLAIN
select *
from spot s1_0
where s1_0.user_id not in (select u2_0.blocked_user_id
                           from user_block_user u2_0
                           where u2_0.blocker_user_id = 49)

 

계속 보다보면 감이 올것도 같다.

select_type = PRIMARY라는건 1번 쿼리가 메인 쿼리라는거고, SUBQUERY라는건 2번 쿼리가 서브 쿼리라는 것.

table은 대상 테이블이고 key는 인덱스.

 

 

실행 계획 내용을 알아보자

 

필드	의미
id	SELECT 쿼리 별 부여되는 식별자 값 (1,2, ...)
select_type	각 SELECT 쿼리가 어떤 타입의 쿼리인지 표시되는 칼럼  (PRIMARY, SUBQUERY, ...)
table	접근하고 있는 테이블
partitions	테이블에 파티셔닝이 되어 있는 경우 사용되는 필드
type	각 테이블의 레코드에 대한 접근 방식 대상 테이블로의 접근이 효율적인지 여부를 판단하는데 아주 중요한 항목
possible_keys	후보 인덱스들
key	최종 선택된 실행 계획에서 사용되는 인덱스
key_len	선택된 인덱스의 길이를 의미
ref	접근 방법이 ref면 참조 조건(equal 비교 조건)으로 어떤 값이 제공됐는지 표시
rows	예측했던 접근 레코드 건수를 보여준다. (정확하지는 않음)
filtered	필터링되고 남은 레코드의 비율 (정확하지는 않음)
Extra	옵티마이저 동작 방식에 대해 알려주는 힌트 값

 

 

뭐가 많지만, 쿼리 최적화에 있어 가장 유용한 정보는 type, key, rows, Extra 인듯 하다. 

 

쿼리 최적화를 하면서 INDEX에 신경을 많이 쓰는중인데, key와 Extra가 결정적인 정보를 알려준다.

 

 

절대적으로 빠른 쿼리는 없기도 하고, 경험이 부족하기 때문에 지금으로썬 시행착오를 겪으면서 내 나름의 체계를 만들어야겠다.

생각보다 쿼리 최적화에 대한 자료가 많이 없다. 

 

 

 

참고

• 대규모 서비스를 지탱하는 기술 - 이토 나오야, 다나카 신지
• 이것이 MySQL이다 - 우재남
• https://wildeveloperetrain.tistory.com/203