[MYSQL] MYSQL explain 알아보기
시작하며
프로젝트를 진행하면서 DB에 대한 기본적인 지식이 부족하다는 걸 너무 많이 느꼈으며, DB에서 어떻게 쿼리를 생성하고 실행하는지에 대한 의문점이 생겼습니다. 그러던 와중 MYSQL explain(실행계획)을 알게 되었고, explain을 어떻게 사용하는지를 기록하려 합니다.
MYSQL EXPLAIN 이란?
MYSQL EXPLAIN 은 MYSQL DATABASE가 어떻게 데이터를 찾을 것인가에 관련해 EXPLAIN 해주는 명령어이며, DB가 데이터를 찾아가는 과정을 알아보기 쉽게 DB결과 셋으로 보여주는 것입니다.
MYSQL EXPLAIN을 쓰는 이유
DB스키마를 생성 후, 쿼리를 날렸을 때 성능상 문제가 있거나 속도가 저하되는 부분이 있다면 스키마를 수정할 수 있다. 하지만 스키마를 수정했을 때, 모든 쿼리에 영향을 줄 수 있기 때문에 스키마를 수정하기보다는 쿼리를 먼저 수정하는 것이 더 바람직합니다.
그렇다면 쿼리를 수정할 때 DB가 어떻게 데이터를 찾고 있는지 알아야만 합니다. 이때, EXPLAIN을 사용합니다.
MYSQL 실행 순서
MYSQL에서 개발자가 이해할 수 있는 언어로 구성된 SQL을 실행하면 MYSQL은 파서에서 SQL을 분석 후, 옵티마이징(내부적으로 가장 비용이 적은 방법을 찾아서 택하는 것)을 거쳐 실제 데이터를 담아서 리턴해 줍니다.
- 파서(Parser) : 파서는 사용자의 요청으로 들어온 쿼리 문장을 토큰으로 분리해 트리 형태의 구조로 만들어 내는 작업을 의미합니다, 쿼리의 기본 문법 오류는 이 단계에서 발견되며 사용자에게 오류 메시지를 통보합니다.
- 옵티마이져(Optimizer) : 사용자가 요청한 쿼리를 가장 낮은 비용으로 빠르게 처리할지 결정하는 역할을 담당하는데, DB의 두뇌 역할을 하며 매우 중요한 역할을 합니다. 대부분의 MYSQL의 내용은 옵티마이져가 선택한 방법, 최적의 값을 실행하게 유도하기 위해서입니다.
MYSQL EXPLAIN 사용 방법
EXPLAIN
SELECT o.*, oi.*
FROM order_item oi
INNER JOIN linkfriends_local.order o ON o.id = oi.orderId(위 SQL은 진행 중인 프로젝트에서 EXPLAIN사용의 이해를 돕기 위해 임의로 만들었습니다.)
위 SQL을 실행했을 때, 결과물은 아래와 같습니다.
위 결과를 보고 해석을 하기 위해서 해당 칼럼들이 의미하는 것들을 알아봅시다.
id : 행이 어떤 SELECT 구문을 나타내는 지를 알려주는 것으로 구문에서 서브쿼리나 UNION이 없다면 SELECT는 하나밖에 없기 때문에 모든 행에 대해 1이란 값이 부여됩니다. 이외의 경우에는 순서에 따라 각 SELECT구문들에 순차적으로 번호가 부여됩니다.
select_type :
- SIMPLE : 단순 SELECT (Union이나 SubQuery 가 없는 select 문)
- PRIMARY : Sub Query 또는 UNION을 사용할 경우 Sub Query 또는 Union의 첫 번째 쿼리
- UNION : UNION 쿼리에서 PRIMARY를 제외한 나머지 SELECT문
- DEPENDENT_UNION : UNION과 동일하나 바깥 쿼리에 의존성을 가진 Union의 SELECT문
- UNION_RESULT : UNION 쿼리의 결과물
- SUBQUERY : 가장 밖에 있는 SELECT문의 Sub QuerySub Query 또는 Sub Query를 구성하는 여러 쿼리 중 첫 번째 SELECT문
- DEPENDENT_SUBQUERY : SUBQUERY와 동일하나 바깥 쿼리에 의존성을 가진 Sub Query의 SELECT문
- DERIVED : SELECT로 추출된 테이블 (FROM 절에서의 서브쿼리 또는 inline view)
- UNCACHEABLE SUBQUERY : Sub Query 와 동일하지만 공급되는 모든 값에 대해 Sub Query를 재처리. 외부쿼리에서 공급되는 값이 동일하더라도 Cache 된 결과를 사용할 수 없음.
- UNCACHEABLE UNION : UNION 과 동일하지만 공급되는 모든 값에 대하여 UNION 쿼리를 재처리
table : 테이블 명(별명)을 의미합니다.
type :
- system : 테이블에 단 한개의 데이터만 있는 경우
- const : SELECT 에서 조건을 만족하는 row가 하나일 때
- eq_ref : 조인을 할 때 Primary key
- ref : 조인을 할 때 primary key 혹은 unique key 가 아닌 key로 매칭하는 경우
- ref_or_null : ref와 같지만 null 이 추가되어 검색되는 경우
- index_merge : 두 개의 인덱스가 병합되어 검색이 이루어지는 경우
- unique_subquery : in 절 안에 서브쿼리에서 Primary key가 오는 특수한 경우(select * from test from where t1 in (select test2 from t2);
- index_subquery : unique_subquery와 비슷하나 Primary key 가 오는 특수한 경우
- range : 특정 범위 내에서 인덱스를 사용하여 원하는 데이터를 추출하는 경우로, 데이터가 방대하지 않다면 단순 SELECT에서 나쁘지 않음
- index : 인덱스를 처음부터 끝까지 찾아서 검색하는 경우로, 일반적으로 인덱스 풀스캔이라고 함
- all : 테이블을 처음부터 끝까지 검색하는 경우로, 일반적으로 테이블 풀스캔이라고 함
possible_keys : 쿼리에서 접근하는 칼럼들과 사용된 비교 연산자들을 바탕으로 어떤 인덱스를 사용할 수 있는지를 표시해 준다.
key : 테이블에 접근하는 방법을 최적화하기 위해 어떤 인덱스를 사용하기로 결정했는지를 나타낸다.
key_len : MYSQL 이 인덱스에 얼마나 많은 바이트를 사용하고 있는지를 보여준다. MYSQL에서 인덱스에 있는 칼럼들 중 일부만 사용한다면 이 값을 통해 어떤 칼럼들이 사용되는 지를 계산할 수 있다.
ref : 키 칼럼에 나와 있는 인덱스에서 값을 찾기 위해 선행 테이블의 어떤 칼럼이 사용되었는 지를 나타낸다.
rows : 원하는 행을 찾기 위해 얼마나 많은 행을 읽어야 할 지에 대한 예측값을 의미한다.
extra :
- using index : 커버링 인덱스라고 하며 인덱스 자료구조를 이용해서 데이터를 추출
- using where : where 조건으로 데이터를 추출. type 이 ALL 혹은 Index 타입과 함께 표현되면 성능이 좋지 않다는 의미(위 그림처럼)
- using filesort : 데이터 정렬이 필요한 경우로 메모리 혹은 디스크상에서의 정렬을 모두 포함. 결과 데이터가 많은 경우 성능에 직접적인 영향을 줍니다.
- using temporary : 쿼리 처리 시 내부적으로 temporary table 이 사용되는 경우를 의미함
만약 데이터가 많은 경우라면 filesort와 temporary의 경우 쿼리 튜닝이 필요한 시점이다.
추가 사항 :
explain format = json => MySQL 5.6 버전부터는 위 예시와 같이 EXPLAIN 결과를 JSON 형태로도 출력할 수 있게 되었습니다. JSON 형태의 결과는 기본적인 EXPLAIN 보다 제공되는 데이터가 몇 가지 더 있습니다.
explain analyze => 8.0.18 버전부터 추가된 EXPLAIN ANALYZE 기능은 쿼리 실행에 소비되는 시간과 수행되는 rows 그리고 측정한 결과 건수 등에 대해 수행된 SQL의 Actual rows 정보를 추가로 보여주게 됩니다.
마치며
데이터베이스를 직접적으로 사용하는 백엔드개발자라면 누구보다 DB의 기본적인 사용방법과 실행원리를 알아야 한다고 생각합니다.
개발을 하면서 기본적인 것을 많이 생각하지 못하고 있다고 느꼈습니다. 기본, 기초를 가장 중요한 가치로 생각하며 성장하는 개발자가 되고 싶습니다.