2. MySQL 실행 계획

1. 실행 계획 수행

1) 기본 실행 계획

기본 실행 계획 수행

EXPLAIN SQL 문;
DESCRIBE SQL 문;
DESC SQL 문;

2) 기본 실행 계획 항목 분석

(1) ID - 실행 순서를 표시

• 조인할 때는 똑같은 ID가 표시된다.
• ID의 숫자가 작을수록 먼저 수행된 것이고 ID가 같은 값이라면 두 개 테이블의 조인 뜻함.

(2) select_type - SELECT문의 유형을 출력

• SIMPLE : UNION이나 내부 쿼리가 없는 SELECT 문 -> 단순한 SELECT 구문

explain select * from 사원 where 사원번호 between 100001 and 200000;

• PRIMARY : 서브쿼리가 포함된 SQL문이 있을 때 첫 번째 SELECT 문에 해당하는 구문에 표시되는 유형, 즉 서브쿼리를 깜싸는 외부쿼리이거나, UNION이 포함된 SQL 문에서 첫 번째로 SELECT 키워드가 작성된 구문에 표시

explain select 사원.사원번호, 사원.이름, 사원.성,
               (select max(부서번호) from 부서사원_매핑 as 매핑 where 매핑.사원번호 = 사원.사원번호) 카운트
               from 사원 where 사원.사원번호 = 10001;

=> 사원 테이블에 우선 접근 하므로 PRIMARY 출력

• SUBQUERY : 독립적으로 수행되는 서브쿼리

explain select (select count(*)
    from 부서사원_매핑 as 매핑
    ) as 카운트,
    (select max(연봉) from 급여) as 급여;

• DERIVED : from 절에 작성된 서브쿼리, 즉 from 절의 별도 임시 테이블인 인라인 뷰를 뜻함.

(3) table - 테이블명을 표시하는 항목

• 서브쿼리나 임시 테이블을 만들어서 별도의 작업 수행 시, subquery#, derived#으로 출력

explain select 사원.사원번호, 급여.연봉
    from 사원,
         (select 사원번호, max(연봉) as 연봉
            from 급여
             where 사원번호 between 10001 and 20000) as 급여
    where 사원.사원번호 = 급여.사원번호;

=> 1, 2행 모두 ID가 같으므로 derived2 테이블과 사원 테이블이 조인했으며, 여기서 derived2는 id가 2인 테이블을 뜻한다. 즉, from 절의 서브쿼리 구문으로 작성된 급여 테이블과 사원 테이블의 조인을 뜻함.

(4) partitions - 실행 계획의 부가 정보로, 데이터가 저장된 논리적인 영역을 표시

(5) type - 실행 계획의 부가 정보로, 데이터가 저장된 논리적인 영역을 표시

• system : 테이블에 데이터가 없거나 한 개만 있는 경우로, 성능상 최상의 type

• const : 조회되는 데이터가 단 1건일 때 출력되는 유형

• eq_ref : 조인이 수행될 때 드리븐 테이블의 데이터에 접근하며 고유 인덱스 또는 기본 키로 단 1건의 데이터를 조회

explain select 매핑.사원번호, 부서.부서번호, 부서.부서명
    from 부서사원_매핑 as 매핑, 부서
    where 매핑.부서번호 = 부서.부서번호
    and 매핑.사원번호 between 100001 and 100010;

-> 드리븐 테이블 조회 시 기본 키나 고유 인덱스 활용하여 1건 검색

• ref : 드리븐 테이블의 데이터 접근 범위가 2개 이상일 경우, 즉 드라이빙 테이블과 드리븐테이블이 조인을 수항해면 일대다 관계가 되므로, 드라이빙 테이블의 1개 값이 드리븐 테이블에서 2개이상의 데이터로 존재

explain select 사원.사원번호, 직급.직급명
    from 사원, 직급
    where 사원.사원번호 = 직급.사원번호
    and 사원.사원번호  between 10001 and 10100;

-> 드리븐 테이블의 데이터양이 많다면 성능 저하의 원인이됨

• range : 테이블 내의 연속된 데이터 범위를 조회하는 유형
  ex) between, in 등

• index : 인덱스 풀 스캔, 즉 물리적인 인덱스 블록을 처음부터 끝까지 훓는 방식

• ALL : 테이블 풀 스캔 방식

(6) key - 옵티마이저가 SQL문을 최적화하고자 사용한 기본 키 또는 인덱스명을 의미
-> 비효율적인 인덱스를 사용했거나 인덱스 자체를 사용하지 않았다면 SQL 튜닝의 대상이 된다.

(7) extra - SQL 문을 어떻게 수행할 것인지에 관한 추가 정보 항목

• Using index : 물리적인 데이터 파일을 읽지 않고 인덱스만을 읽어서 SQL 문의 요청사항을 처리

3) 좋고 나쁨을 판단하는 기준

(1) select_type 항목의 판단 기준

• Best : SIMPLE, PRIMARY, DERIVED
• Worst : DEPENDENT, UNCACHEABLE

(2) type 항목의 판단 기준

• Best : system, const, eq_ref
• Worst : index, all

(3) extra 항목의 판단 기준

• Best : Using index
• Worst : Using filesort, Using temporary

참고 - SQL 튜닝 / 양바른 저