10.3.1~3 실행 계획 분석

EXPLAIN 에 아무런 옵션을 주지 않으면 테이블 포맷으로 출력이 됨.

EXPLAIN
SELECT *
FROM EMPLOYEES E
JOIN SALARIES S
    ON E.EMP_NO = S.EMP_NO
WHERE FIRST_NAME = 'ABC';

각 라인은 쿼리에서 사용된 테이블의 개수 만큼 출력

실행 순서는 위에서 아래로 순서대로

(union 이나 상관 서브쿼리 같은 경우 순서대로 표시되지 않을 수 있음)

위쪽에 출력된 결과일수록(id 컬럼의 값이 작을수록) 쿼리의 바깥 쪽이거나 먼저 접근한 테이블이고

아래쪽에 출력된 결과일수록(id 컬럼의 값이 클수록) 쿼리의 안쪽 부분 또는 나중에 접근한 테이블에 해당

10.3.1 id 컬럼

가장 왼쪽에 표시되는 id 컬럼은 단위 select 쿼리별로 부여되는 식별자 값

하나의 쿼리안에 여러개의 테이블으 조인하면 조인되는 테이블의 개수만큼 레코드가 출력되지만 같은 id 값이 부여된다.

explain
select e.emp_no, e.first_name, s.from_date, s.salary
from employees e, salaries s
where e.emp_no = s.emp_no limit 10;

반대로 다음 쿼리의 실행계획에서는 쿼리 문장이 3개의 단위 select 쿼리로 구성되어 있어서

실행계획의 각 레코드가 각기 다른 id 값을 지니 것을 확인할 수 있다.

explain
select
    ((select count(*) from employees) + (select count(*) from departments)) as total_count;

실행곟획의 id 컬럼이 테이블의 접근 순서를 의미하지는 않는다.

explain -- format=tree
select * from dept_emp de
where de.emp_no = (select e.emp_no
                    from employees e
                    where e.first_name = 'Georgi'
                    and e.last_name = 'Facello' limit 1);

-> Filter: (de.emp_no = (select #2))  (cost=1.10 rows=1)
    -> Index lookup on de using ix_empno_fromdate (emp_no=(select #2))  (cost=1.10 rows=1)
    -> Select #2 (subquery in condition; run only once)
        -> Limit: 1 row(s)
            -> Filter: (e.last_name = 'Facello')  (cost=209.48 rows=25)
                -> Index lookup on e using ix_firstname (first_name='Georgi')  (cost=209.48 rows=253)

10.3.2 select_type 컬럼

각 단위의 select 쿼리가 어떤 타입의 쿼리인지 표시되는 컬럼

10.3.2.1 SIMPLE

단순한 SELECT 쿼리인 경우

쿼리가 복잡해도 실행 계획에서 simple 인 쿼리는 하나만 존재

일반적으로 제일 바깐 select 쿼리가 simple 임

10.3.2.2 PRIMARY

union 이나 서브쿼리를 가지는 select 쿼리의 실행 계획에서 가장 바깥쪽에 있는 쿼리

쿼리에서 하나만 존재

10.3.2.3 UNION

union 으로 결합하는 쿼리 가운데 첫번째를 제외한 쿼리가 union 으로 표시

union의 첫번째 단위 쿼리는 derived(임시 테이) 가 표시

explain
select * from(
    (select emp_no from employees e1 limit 10) union all
    (select emp_no from employees e2 limit 10) union all
    (select emp_no from employees e3 limit 10 )
) tb;

10.3.2.4 DEPENDENT UNION

union 과 union all 로 집합을 결합하는 쿼리에서 표시됨.

외부 쿼리에 의해 영향 받는 것을 의미함.

explain
select *
from employees e1
where e1.emp_no in (
    select e2.emp_no from employees e2 where e2.first_name = 'Matt'
    union
    select e3.emp_no from employees e3 where e3.last_name = 'Matt'
);

-> Filter: <in_optimizer>(e1.emp_no,<exists>(select #2))  (cost=30688.92 rows=299290)
    -> Table scan on e1  (cost=30688.92 rows=299290)
    -> Select #2 (subquery in condition; dependent)
        -> Limit: 1 row(s)
            -> Table scan on <union temporary>  (cost=2.50 rows=0)
                -> Union materialize with deduplication
                    -> Limit table size: 1 unique row(s)
                        -> Limit: 1 row(s)
                            -> Filter: (e2.first_name = 'Matt')  (cost=0.82 rows=0)
                                -> Single-row index lookup on e2 using PRIMARY (emp_no=<cache>(e1.emp_no))  (cost=0.82 rows=1)
                    -> Limit table size: 1 unique row(s)
                        -> Limit: 1 row(s)
                            -> Filter: (e3.last_name = 'Matt')  (cost=0.83 rows=0)
                                -> Single-row index lookup on e3 using PRIMARY (emp_no=<cache>(e1.emp_no))  (cost=0.83 rows=1)

in 내부의 서브 쿼리를 먼저 처리하지 않고

외부의 employees 테이블의 먼저 읽은 다음 서브 쿼리를 실행하는데

이때 employees 테이블읭 컬럼값이 서브쿼리에 영향을 줌.

10.3.2.5 UNION RESULT

union 결과를 담아두는 테이블을 의미

8.0 전에는 union 이나 union all 모두 결과를 임시 테이블로 생성했는데

8.0 부터 union all 은 임시 테이블을 사용하지 않도록 기능이 개선됨

실행계획 상 이 임시테이블을 가리키는 라인의 select_type 이 union result

실제 쿼리에서 단위 쿼리가 아니기 때문에 별도의 id 가 부여되지 않음.

explain
select emp_no from salaries where salary > 100000
union distinct
select emp_no from dept_emp where from_date > '2001-01-01';

table 컬럼의 union 1,2 는 id 1과 2가 합쳐졌다는 의미

10.3.2.6 SUBQUERY

select_type 에서 subquery는 from 절 이외에서 사용되는 서브쿼리만 의

explain
select e.first_name,
        (select count(*)
        from dept_emp de, dept_manager dm
        where dm.dept_no = de.dept_no) as cnt
from employees e
where e.emp_no = 10001;

from 절에 사용되는 서브쿼리는 DERIVED 로 표시됨.

10.3.2.7 DEPENDENT SUBQUERY

서브쿼리가 바깥쪽 쿼리엣 정의된 컬럼을 사용하는 경우

explain
select e.first_name,
        (select count(*)
        from dept_emp de, dept_manager dm
        where dm.dept_no = de.dept_no
        and de.emp_no = e.emp_no) as cnt
from employees e
where e.first_name = 'Matt';

외부 쿼리가 수행된 후 내부 쿼리가 실행돼야 하므로 일반 서브쿼리보다는 처리속도가 느릴때가 많다.

10.3.2.8 DERIVED

단위 쿼리의 실행 결과로 메모리나 디스크에 임시 테이블을 생성하는 것을 의미

5.6 전까지는 파생 테이블에는 인덱스가 전혀 없어 조인할 때 성능상 불리했지만

5.6 부터 옵티마이저 옵션에 따라 쿼리 특성에 맞게 인덱스를 추가해서 만들수 있게 최적화됨.

explain
select *
from (select de.emp_no from dept_emp de group by de.emp_no) tb,
    employees e
where e.emp_no = tb.emp_no;

쿼리 튜닝을 위해 select_type 이 DERIVED 인 것이 있다면 join으로 최대한 해결하는 것을 적극 권장

10.3.2.9 DEPENDENT DERIVED

8.0 전에는 from 절의 서브쿼리는 외부 컬럼을 사용할 수 없었는데

8.0부터 래터럴 조인(LATERAL JOIN) 기능이 추가되면서 from 절의 서브쿼리에서도 외부 컬럼을 참조할 수 있게 됨.

explain
select *
from employees e
left join lateral
    (select *
    from salaries s
    where s.emp_no = e.emp_no
    order by s.from_date desc limit 2) as s2
    on s2.emp_no = e.emp_no;

10.3.2.10 UNCACHEABLE SUBQUERY

쿼리안의 서브쿼리는 여러번 실행될 수 있음.

조건이 똑같은 서브쿼리가 실행 될 때는 다시 실행하지 않고 이전의 결과를 그대로 사용할 수 있게 캐시공간에 담아둠.

하지만 서브쿼리에 포함된 요소에 의해 캐시 자체가 불가능한 경우 UNCACHEABLE SUBQUERY이 표시됨.

• 사용자 변수가 서브쿼리에 사용된 경우
• not-deterministic 속성의 스토어드 루틴이 서브쿼리 내에 사용된 경우
• uuid(), rand() 같이 결과값이 호출할 때마다 달라지는 함수가 서브쿼리에 사용된 경우

explain
select *
from employees e 
where e.emp_no = (select @status from dept_emp de where de.dept_no = 'd005');

10.3.2.11 UNCACHEABLE UNION

UNCACHEABLE 과 UNION 속성이 혼합된 타입

10.3.2.12 MATERIALIZED

explain
select *
from employees e
where e.emp_no in (select emp_no from salaries where salary between 100 and 1000);

5.6 까지는 employees 테이블을 읽어 레코드 마다 서브쿼리를 실행하는 형태로 실행했는데

5.7부터는 서브쿼리의 내용을 임시 테이블로 구체화(materialization)한 후 조인하는 형태로 최적회 처리됨

10.3.3 table 컬럼

테이블이 표시되고 별칭이 있는 경우 별칭이 표시됨.

<> 안에 표시되는 것은 임시테이블의 별칭 또는 쿼리의 id 값임.

연습

explain
select *
from (select de.emp_no from dept_emp de group by de.emp_no) tb,
    employees e
where e.emp_no = tb.emp_no;