[Database] mysql explain 이해

오늘은 explain plan에 대해 배워보도록 하겠다. 

1. explain의 정의 

explain Plan란  SQL을 수행하기전 데이터를 어떻게 가져올 건지에 대한 실행계획을 의미하며 

데이터 performance를 확인 하고자 할때 explain Plan 명령어를 사용한다.   

2. 사용방법

2.1 )  SELECT 에서 explain 사용하기 

select explain을 사용하려면 SELECT 키워드 앞에 explain을 붙여주면된다. 간단하게 user라는 테이블과 author이라는 테이블이 있다라고 할때 두 테이블을 조인한 테이블에 대하여 explain을 주었다.

아래는 두 테이블에 대한 explain 결과이다. 

[그림 1]

 

EXPLAIN SELECT user.user_name, author.authority_seq 

                ㅠ FROM tb_user_m user JOIN tb_authority_user author ON user.user_seq = author.user_seq ; 

explain은 쿼리에 있는 테이블 하나당 한 행씩 출력이 된다. 

위에 [그림1] 또한 두개의 테이블을 조인한 결과이기 때문에 두개의 행이 출력되었다. 

* 여기서 말하는 테이블은 서브쿼리 일수도 있고, union 결과일 수도 있다. 

2.2) SELECT가 아닌 쿼리구문에서 explain 사용하기 

select 쿼리에서는  앞에 explain만 붙여줌으로써 실행계획을 확인할 수 있지만 INSERT,UPDATE,DELETE와 같이 입력,수정,삭제와 같은 것들은 실행계획을 확인 할 수 없다.

그렇기때문에 INSERT,UPDATE, DELETE로 작성된 쿼리에서 사용된 칼럼으로 SELECT문으로 재구성시켜줘야한다. 

이해하기 좀 어려울 듯해서 아래에 예시를 들어보겠다. 

UPDATE tb_user_m SET user_name='najung' ; 

라는 update 쿼리가 있다. 

위에 나오는 update쿼리구문을 

EXPLAIN SELECT user_name FROM tb_user_m ; 

으로 바꿔서 실행계획을 확인하는 것이다. 

3. explain 컬럼  

위 [그림1]을 하나씩 쪼개서 설명해보겠다. 

3.1) id 칼럼 

 

         [그림2]

id칼럼은 구문에 따라 순차적으로 번호가 부여된다. 현재 JOIN같은 경우 하나의 쿼리에서 두 테이블이 동시에 실행됬기때문에 모든 행에 1이라는 값이 부여됬지만 union이나 서브쿼리가 구문에 들어있다면 위 예제는 달라진다. 

아래는 union을 이용한 쿼리이다. 

EXPLAIN 

SELECT user_name,user_id FROM tb_user_m UNION SELECT result_code, register_time 

FROM tb_login_h

이러한 쿼리를 통해 explain했을 때 결과 값은 

[그림3]

 

        [그림4]  

처음에 나온 1이  tb_user_m에 대한 쿼리 열이고 두번째 2는 tb_login_h에 대한 쿼리열이다.  

여기서 뒤에 select type 칼럼을 설명하면서 한번 더 이야기 하겠지만, 

mysql은 select 쿼리를 간단한 타입과 복잡한 타입으로 설정한다. 

복잡한 타입은 유도된 테이블(sub쿼리), union으로서 복잡한 쿼리를 실행할 경우 id가 복잡해진다. 

또한 [그림4] union 경우 3.null부분은 mysql에서 union결과가 임시테이블에 저장되었다가 다시 읽혀지는데 임시테이블은 sql에서 나와있지 않아 컬럼이 null을 가진다. 

3.2) select_type 칼럼 

select_type은 간단한 쿼리인지 복잡한 쿼리인지 를 나타낸다. 

[그림 1] 같이 union이나 서브쿼리가 없을 경우 SIMPLE 

[그림 3] 같이 union이나 서브쿼리가 있을 경우에는 

가장 밖에 있는 부분은 PRIMARY로 표시되고 

나머지는 다음과 같다. 

3.2.1) SUBQUERY : 쿼리문장의 FROM절이 아닌 SELECT절에 나타나는 서브쿼리인 경우 SUBQUERY라 한다. 

3.2.2) DERIVED : FROM절에 있는 서브쿼리를 표시하는데 사용된다. 

3.2.3) UNION : UNION을 사용한 쿼리절일 경우 사용된다. 

3.2.4) UNION_RESULT : UNION의 결과 값이 임시테이블에 저장되며, 그 임시테이블을 표현할 때 사용된다. 

3.3) table 칼럼 

table 칼럼은 어떤 테이블에 접근하는지 보여준다.  대부분의 경우 테이블의 이름이나 sql에서 지정해준 ( ex : AS user ) 값을 가진다. 

table 컬럼은 간단한 예제는 이해하기 쉬우나 , 복잡한 쿼리같은 경우 이해하기 어렵다. 

아래의 예시를 보자 

EXPLAIN 

SELECT * 

  FROM 

       (SELECT cast(@rnum := @rnum+1 as unsigned) AS seq, 

              union_result.* 

         FROM (SELECT @rnum := 0) r, 

              ( 

                     (SELECT article_h.regist_time as time , 

                            article_h.user_id as id, 

                            article_h.user_institution as institution, 

                            article_h.user_position as position, 

                            article_h.user_name as name, 

                            article_h.register_ip as ip, 

                            concat(article_h.module_name, ' | ', article_h.article_name, ' | ', article_h.action_name) as action_name 

                       FROM tb_article_h article_h 

                     ) 

                    UNION ALL 

                     (SELECT login_h.register_time as time, 

                            login_h.user_id as id, 

                            user.user_institution as institution, 

                            code2.code_name AS position, 

                            login_h.user_name as name, 

                            login_h.register_ip as ip, 

                            code.code_name as action 

                       FROM tb_common_code code 

                          INNER JOIN tb_login_h login_h 

                              ON code.code=login_h.result_code, 

                            tb_common_code code2 

                          INNER JOIN tb_user_m user 

                              ON code2.code=user.user_position_code 

                      WHERE user.user_id = login_h.user_id 

                     ) 

                   ) 

                   union_result 

             WHERE 1=1  

          ORDER BY time ASC 

            ) result 

      WHERE seq BETWEEN 0 AND 10 

   ORDER BY seq DESC

[그림 5]

[그림 5]에 대한 테이블을 설정하자면 이러하다. 

1. id 값이 1인 table명은 derived2 이다. 이러한 테이블 명은 id값이 2이고 select_type이 DERIVED인 테이블을 참조하는 테이블이라고 이해하면 된다.  빨간색으로 표시한 부분이 derived2이다. 

2. id값이 2인 table명은 derived3 과 derived4 이다.   

이하 동문 ... 

* 마지막 10행이 이해가 가지않음 

3.4) type 칼럼 

mysql 메뉴얼에는 조인 방식이라고 나타내고 있으나 테이블에서 행을 어떻게 찾는지 라고 이해하는게 더 쉽다. 

type칼럼 종류를 나쁜 방식에서 좋은 방식 순으로 설명하도록 하겠다. 

3.4.1) ALL  : mysql이 행을 찾기 위해 처음부터 끝까지 스캔해야한다는 것을 의미한다. 

3.4.2) INDEX : 인덱스 순서로 스캔 한다는 것을 제외하면ALL과같다. 일반적인 경우 인덱스가 테이블보다 사이즈가 작기 때문에,ALL보다는 빠를 가능성이 높다. (이해가 잘 되지 않음 ) 

3.4.3) RANGE : 제한된 형태의 인덱스 스캔이다. RANGE스캔경우 인덱스 특정부분에서 시작해서 특정범위에 있는 값을 가지는 행을 반환한다. 키컬럼이 상수와 =, <>, >, >=, <, <=, IS NULL,<=>, BETWEEN 또는 IN 연산에 사용될 때 적용된다. 

SELECT * FROM tb_user_m  WHERE user_seq = 10;
 
SELECT * FROM tb_user_m  WHERE user_seq BETWEEN 10 and 20;
 
SELECT * FROM tb_user_m  WHERE user_seq IN (10);
 
SELECT * FROM tb_user_m  WHERE user_seq= 10 AND user_seq IN (20);

3.4.4) ref : 어떤 값 하나에 매치되는 행들을 반환해주는 인덱스 접근방식이다.  인덱스에 매치되는 값이 많지 않은 경우 나쁘지 않다. PRIMARY KEY 또는 UNIQUE 인덱스가 아닐 경우에 ref가 사용되며  = 또는 <=> 연산자를 사용해서 비교되는 인덱스된 컬럼에 대해 사용될 수 있다. 

SELECT * FROM tb_user_m WHERE user_name='손화정';

 

SELECT * FROM tb_user_m user, tb_login_h login where user.user_id = login.user_id ; 

* 기울림된 쿼리 두개의 차이가 이해가 가지 않음 

3.4.5) eq_ref : 테이블에서 찾은 값중 단 하나의 값만 해당 테이블에 존재하는 경우  이런 인덱스 탐색법이 사용된다. 기본키 혹은 unique 인덱스에 비교할 떄 이런 접근 방법을 많이 사용한다.  eq_ref는 = 연산자를 사용해서 비교되는 인덱스된 컬럼용으로 사용될 수 있다.

SELECT * FROM tb_authority_user author JOIN tb_user_m user ON user.user_seq = author.user_seq ; 

3.4.6) const : 쿼리의 일부를 상수로 대체시킬 수 있을 때 사용한다. const 테이블은 한번 밖에 읽혀지지 않기 때문에 매우 빠르다.const는 PRIMARY KEY 또는 UNIQUE 인덱스의 모든 부분을 상수 값(constant value)과 비교를 할 때 사용된다.

SELECT * FROM tb_user_m WHERE user_seq= 7

3.4.7) system : 무조건 하나의 열만을 가지고 있는 테이블 . 이것은 const의 특별한 경우이다.

3.5) possible_key 칼럼 

possible_keys 컬럼은 이 테이블에서 열을 찾기 위해 MySQL이 선택한 인덱스를 가리킨다. 이 possible_key컬럼은 최적화단계에서 시작하기 떄문에 최적화가 끝나는 단계가 진행됨에 따라 쓸모가 없어질 수도 있다. 

만일 이 컬럼 값이 NULL이라면, 연관된 인덱스가 존재하지 않게 된다. 이와 같은 경우, 여러분은 WHERE 구문을 검사해서, 이 구문이 인덱스 하기에 적당한 컬럼을 참조하고 있는지 여부를 알아 봄으로써 쿼리 속도를 개선 시킬 수가 있게 된다. 

3.6) key칼럼 

key 컬럼은 MySQL이 실제로 사용할 예정인 키 (인덱스)를 가리킨다 . 

3.7) key_len 컬럼 

인덱스 필드가 가질 수 있는 최대의 길이를 출력한다. 

3.8) ref 칼럼 

키 칼럼에 나와 있는 인덱스에서 찾기 위한 선행 테이블의 어떤 칼럼이 사용되었는지 나타낸다. 

3.9) row칼럼 

row칼럼 원하는 행을 찾기 위해 얼마나 많은 행을 읽어야 할지 예측값을 의미한다. 

tb_user_m에 50명의 회원이 있을 때 tb_user_m에 대한 테이블의 row칼럼은 50이 될 것이다. 

3.10) extra칼럼 

3.10.1)using index : mysql 테이블에 접근하지 않도록 커버링 인덱스를 사용한다는 것을 알려준다. 

*커버링 인덱스란 , 쿼리를 실행시키기 위해 필요한 데이터가 모두 포함된 인덱스를 말한다.  

3.10.2)using where : mysql서버가 값을 가져온 뒤 행을 필터링 한다는 것을 의미한다. 

3.10.3)using temporary :  mysql이 쿼리결과를 정렬하기위해 임시테이블을 사용한다는 것을 의미한다. 

3.10.4)using filesort : mysql이 결과의 순서를 맞추기 위해 인덱스 순서로 테이블을 읽는 것이 아니라 외부 정렬을 사용해야한다는 것을 의미한다. 

3.10.5)range checked for each record (index map:N) : 적합한 인덱스가 없으므로 각 레코드 조인에서 각 엔덱스들을 재평가한다는 것을 의미한다.