💡 MYSQL EXPLAIN 이란?
MYSQL EXPLAIN 은 MYSQL DATABASE 가 데이터를 어떻게 찾을 것인가에 관련해 EXPLAIN 해주는 명령어입니다.
💡 MYSQL EXPLAIN을 왜 쓰는 걸까?
DATABASE 스키마를 작성하고 쿼리를 날렸을 때 속도가 저하되는 부분이 보이면 스키마를 수정했을 때 모든 쿼리에 영향을 줄 수 있기 때문에 스키마를 수정하기보다는 쿼리를 먼저 수정합니다.
쿼리를 수정할려면 MYSQL 이 어떻게 데이터를 찾고 있는지 알아야하고 여기서 MYSQL EXAPLIN을 사용합니다.
💡 MYSQL 실행 순서
MYSQL 에서 개발자가 이해할 수 있는 언어로 구성된 SQL을 실행하면 MYSQL 은 파서에서 SQL 구문을 분석을 하고 옵티마이징(내부적으로 가장 비용이 적은 방법을 찾아서 택하는 것)을 거쳐 실제로 데이터를 담아서 리턴해준다.
💡 MYSQL EXPLAIN 사용 방법
EXPLAIN [EXTENDED] SELECT ... FROM ... WHERE ...
EX)
EXPLAIN
SELECT m.*, o.* FROM member m
INNER JOIN orders o ON m.id = o.member_idEXPLAIN UPDATE board SET title = 'test' WHERE seq = 91;이렇게 쿼리는 날리면 다음과 같은 결과가 나옵니다.
📌 id : 행이 어떤 SELECT 구문을 나타내는 지를 알려주는 것으로 구문에 서브 쿼리나 UNION이 없다면 SELECT 는 하나밖에 없기 때문에 모든 행에 대해 1이란 값이 부여되지만, 이외의 경우에는 순서에 따라 각 SELECT 구문들에 순차적으로 번호가 부여됩니다.
📌 TABLE : 테이블 명(별명)을 의미합니다.
📌 SELECT_TYPE :
- SIMPLE : 단순 SELECT (Union이나 SubQuery 가 없는 select 문)
- PRIMARY : Sub Query 또는 Union 을 사용할 경우 Sub Query 또는 Union의 첫번째 쿼리
- UNION : UNION 쿼리에서 Primary를 제외한 나머지 SELECT
- DEPENDENT_UNION : UNION과 동일하나 외부쿼리에 의존적임 (값을 공급 받음)
- UNION_RESULT : UNION 쿼리의 결과물
- SUBQUERY : Sub Query 또는 Sub Query를 구성하는 여러 쿼리 중 첫번째 SELECT 문
- DEPENDENT_SUBQUERY : Sub Query 와 동일하나, 외부쿼리에 의존적임 (값을 공급 받음)
- DERIVED : SELECT로 추출된 테이블 (FROM 절에서의 서브쿼리 또는 inline view)
- UNCACHEABLE SUBQUERY : Sub Query 와 동일하지만 공급되는 모든 값에 대해 Sub Query를 재처리. 외부쿼리에서 공급되는 값이 동일하더라도 Cache 된 결과를 사용할 수 없음.
- UNCACHEABLE UNION : UNION 과 동일하지만 공급되는 모든 값에 대하여 UNION 쿼리를 재처리
📌 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 의 경우 쿼리 튜닝이 필요한 시점이다.
💡 Reference
https://nomadlee.com/mysql-explain-sql/
https://cheese10yun.github.io/mysql-explian/
https://denodo1.tistory.com/306
