[MySQL] explain 이해 > db

본문 바로가기

db

[MySQL] explain 이해

페이지 정보

작성자 서방님 댓글 0건 조회 84회 작성일 13-07-31 14:29

본문

1. explain의 정의 


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

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


2. 사용방법


2.1 )  SELECT 에서 explain 사용하기 


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


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


0142184450DBFF9418DFC1

[그림 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 칼럼 


192AE44E50DC08A52B1186  

         [그림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했을 때 결과 값은 



125EBD3750DC10A11B73A2

[그림3]


202A5D4250DC0C4C083A77 

        [그림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




2652A34A50DC319D322989

[그림 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) : 적합한 인덱스가 없으므로 각 레코드 조인에서 각 엔덱스들을 재평가한다는 것을 의미한다. 

댓글목록

등록된 댓글이 없습니다.

Total 447건 1 페이지
게시물 검색

회원로그인

접속자집계

오늘
299
어제
446
최대
592
전체
38,817

그누보드5
Copyright © seobangnim.com All rights reserved.
자바스크립트를 활성화 하세요![ 브라우저에서 자바스크립트를 활성화하는 방법 ]