실행 계획 분석
MySQL에서는 쿼리 앞에 EXPAIN 키워드를 붙여서 실행하면 해당 쿼리를 어떤 방식으로 수행할 것인지에 대한 정보를 출력해준다. 실행 계획에 표시되는 각 칼럼의 의미는 여러 정보를 전달하며 해당 정보를 해석할 수 있는 능력이 필요하다.
1. ID 칼럼
실행계획에서 가장 왼쪽에 표시되는 id 칼럼은 단위SELECT 쿼리별로 부여되는 식별자 값이다.
아래 쿼리에서는 outer 부분 SELECT와 inner 부분 SELECT로 나뉘어 있으며 각 단위 SELECT는 다른 id값을 부여하여 보여준다. (참고로, 실행계획은 쿼리의 바깥(Outer) 부분부터 쿼리의 안쪽(inner) 부분 순서대로 테이블에 순서대로 나타난다.)
SELECT ...
FROM (SELECT ... FROM tb_test1) tb1, tb_test2 tb2
WHERE tb1.id=tb2.id;2. SELECT_TYPE 칼럼
각 단위 SELECT 쿼리가 어떤 타입의 쿼리인지 표시되는 칼럼이다. select_type 칼럼에 표시될 수 있는값은 다음과 같다.
1. SIMPLE
UNION이나 서브쿼리를 사용하지 않는 단순한 SELECT 쿼리이다. 쿼리가 아무리 복잡해도 select_type이 SIMPLE인 쿼리는 단 하나만 존재한다. (일반적으로, 가장 바깥쪽 쿼리)
2. PRIMARY
UNION이나 서브쿼리를 가지는 SELECT 쿼리의 실행 계획에서 가장 바깥쪽에 있는 쿼리는 PRIMARY로 표시된다. PRIMARY 쿼리도 단 하나만 존재한다.
3. UNION
UNION으로 결합하는 단위 SELECT 쿼리 가운데 첫 번째를 제외한 두번째 이후 단위 SELECT 쿼리의 select_type은 UNION으로 표시된다. UNION의 첫번째 단위 SELECT는 select_type이 UNION이 아니라 쿼리를 모아서 저장하는 임시 테이블(DERIVED)로 표시된다.
EXPLAIN SELECT * FROM ( //PRIMARY (SELECT id FROM employees e1) UNION ALL //DERIVED (SELECT id FROM employees e2) UNION ALL //UNION (SELECT id FROM employees e3) ) tb; //UNION4. DEPENDENT UNION
DEPENDENT UNION 또한 UNION으로 집합을 결합하는 쿼리에서 표시된다. 여기서 DEPENDENT는 내부 쿼리가 외부의 값을 참조해서 처리 될때 표시가 되게 된다. 아래에서는
e2.id=e1.id와e3.id=e1.id라는 조건이 자동으로 추가되어 실행된다.EXPLAIN SELECT * //PRIMARY FROM employees e1 WHERE e1.id IN ( SELECT e2.id FROM employees e2 WHERE e2.name='MATT' //DEPENDENT SUBQUERY UNION SELECT e3.id FROM employees e3 WHERE e3.name='MATT' //DEPENDENT UNION );5. UNION RESULT
UNION 결과를 담아두는 테이블을 의미한다. UNION RESULT는 실제 쿼리에서 단위 쿼리가 아니기 때문에 별도의 id값은 부여되지 않는다.
EXPLAIN SELECT id FROM salaries WHERE salary > 100000 //PRIMARY UNION DISTINCT SELECT id FROM dept_emp WHERE from_date > '2001-01-01'; //UNION // id = NULL, select_type = UNION RESULT 인 ROW 추가됨.6. SUBQUERY
SUBQUERY의 경우 서브쿼리가 FROM절 이외에서 사용되는 경우를 말한다. (FROM절 -> DERIVED)
7. DEPENDENT SUBQUERY
서브쿼리가 바깥쪽(Outer) SELECT 쿼리에서 정의된 칼럼을 사용하는 경우, DEPENDENT SUBQUERY로 표시된다. 외부 쿼리가 먼저 수행된 후 내부 쿼리가 실행되어야 하므로 일반 서브쿼리보다 처리 속도가 느릴 때가 많다.
8. DERIVED
보통 FROM절에 서브쿼리가 사용된 경우를 뜻하지만 옵티마이저 옵션에 따라 FROM절의 서브쿼리를 외부 쿼리와 통합하는 형태의 최적화가 이루어지기도 한다. DERIVED는 단위 SELECT 쿼리의 실행 결과로 메모리나 디스크에 임시테이블을 생성하는 것을 의미한다.
MySQL8.0부터는 FROM절의 서브쿼리에 대한 최적화도 개선되어 불필요한 서브쿼리는 조인으로 쿼리를 재작성해서 처리한다. 이전 버전이라면 서브쿼리를 조인으로 사용할 것을 강력히 권장한다.
(옵티마이저가 서브쿼리보다 조인을 더 잘 최적화해준다.)9. DEPENDENT DERIVED
MySQL8.0부터는 래터럴 조인이 포함되면서 FROM절의 서브쿼리에서 외부 칼럼을 사용할 수 있는데 이때 DEPENDENT DERIVED로 표시된다.
10. UNCACHEABLE SUBQUERY
조건이 똑같은 서브쿼리가 실행될 때는 다시 실행하지 않고 이전의 실행 결과를 그대로 사용할 수 있게 내부 캐시 공간에 담아둔다. 하지만 캐시를 사용하지 못하는 경우가 있는데 그는 다음과 같다.
- 사용자 변수가 서브쿼리에 사용된 경우 - NOT-DETERMINISTIC 속성의 스토어드 루틴이 서브쿼리 내에 사용된 경우 - UUID()나 RAND()와 같이 결과값이 호출때마다 달라지는 함수가 포함된 경우11. UNCACHEABLE UNION
UNION과 UNCACHEABLE 속성이 결합된 select_type이다.
12. MATERIALIZED(구체화)
MySQL5.6 버전부터 도입된 select_type으로 주로 FROM절이나 IN(subquery) 형태의 쿼리에 사용된 서브쿼리의 최적화를 위해 사용된다.
EXPLAIN SELECT * FROM employees e WHERE id IN (SELECT id FROM salaries WHERE salary BETWEEN 100 AND 1000);위 쿼리에서 MySQL5.6 이전에는 employees 레코드마다 서브쿼리가 실행되는 형태였다.
MySQL5.7부터는 서브쿼리 내용을 임시 테이블로 구체화(Materialized)한 뒤 조인하는 형태로 최적화 된다.
3. TABLE 칼럼
EXPLAIN
SELECT NOW(); //NULL위의 예제처럼 테이블을 따로 사용하지 않는 경우 테이블 칼럼은 NULL로 표시된다.
테이블 칼럼에 <derived N>과 같이 <>로 둘러싸인 경우 이 테이블은 임시테이블을 의미한다.
참고로 위의 경우 N에 해당하는 값은 파생 테이블을 만든 실행계획의 ID 번호를 의미한다.
SELECT *
FROM (SELECT de.id FROM dept_emp de GROUP BY de.id) tb,
employees e
WHERE e.id = tb.id위의 경우 FROM절의 서브쿼리는 DERIVED 타입의 임시 테이블을 만들고 OUTER 쿼리에서 e와
<derived 2> 테이블을 조인하여 사용하게 된다.
4. PARTITIONS 칼럼
MySQL5.7 버전까지는 파티션 목록을 EXPLAIN PARTITION 명령을 이용해 확인가능했지만 MySQL8.0부터는 EXPLAIN 명령으로 파티션 관련 실행계획까지 모두 확인할 수 있게 변경되었다.
SELECT *
FROM employee
WHERE hire_date BETWEEN '1999-11-15' AND '2000-01-15';위 쿼리에서 employee 테이블이 hire_date 기준으로 파티셔닝되어 있다고 가정하면 옵티마이저에서는 쿼리의 hire_date 칼럼 조건을 보고, 쿼리에서 필요로하는 데이터가 어느 파티션에 있는지 알아내게 된다. (파티션 프루닝) 그리고 이렇게 찾은 파티션들을 PARTITIONS 칼럼에 노출 시켜준다.
