MySQL 실행 계획 : 실행 계획 분석(1)
MySQL에서 쿼리의 실행 계획을 확인하려면 EXPLAIN 명령을 사용하면 된다. 아무런 옵션 없이 EXPLAIN 명령만 사용하면 기본적인 쿼리 실행 계획만 보인다. 하지만 EXPLAIN EXTENDED나 EXPLAIN PARTITIONS 명령을 이용해 더 상세한 실행 계획을 확인할 수도 있다. 추가 옵션을 사용하는 경우에는 기본적인 실행 계획에 추가로 정보가 1개씩 더 표시된다.
우선 기본 실행 계획을 제대로 이해할 수 있어야 하므로 옵션이 없는 "EXPLAIN" 명령으로 조회하는 실행 계획을 자세히 살펴보겠다. 그리고 마지막에 PARTITONS나 EXTENDED 옵션의 실행 계획을 확인하는 방법을 설명하겠다.
EXPLAIN 명령은 다음과 같이 EXPLAIN 키워드 뒤에 확인하고 싶은 SELECT 쿼리 문장을 적으면 된다. 실행 계획이 결과로 여러 가지 정보가 표 형태로 표시된다. 실행계획 중에는 possible_keys 항목과 같이 내용은 길지만 거의 쓸모가 없는 항목도 있다. 이 책에서는 실행 계획의 여러 결과 중 꼭 필요한 경우를 제외하고는 모두 생략하고 표시했다. 또한 실행 계획에서 NULL 값이 출력되는 부분은 모두 공백으로 표시했다.
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;EXPLAIN을 실행하면 쿼리 문장의 특성에 따라 표 형태로 된 1줄 이상의 결과가 표시된다. 표의 각 라인(레코드)은 쿼리 문장에서 사용된 테이블(서브 쿼리로 임시 테이블을 생성한 경우 그 임시 테이블까지 포함)의 개수만큼 출력된다. 실행 순서는 위에서 아래로 순서대로 표시된다(UNION이나 상관 서브 쿼리와 같은 경우 순서대로 표시되지 않을 수도 있다). 출력된 실행 계획에서 위쪽에 출력된 결과일수록(id 칼럼의 값이 작을수록) 쿼리의 바깥(Outer) 부분이거나 먼저 접근한 테이블이고, 아래쪽에 출력된 결과일수록(id 칼럼의 값이 클수록) 쿼리의 안쪽(Inner) 부분 또는 나중에 접근한 테이블에 해당된다. 하지만 쿼리 문장과 직접 비교해 가면서 실행 계획의 위쪽부터 테이블과 매칭해서 비교하는 편이 더 쉽게 이해될 것이다.
| id | select_type | table | type | key | key_len | ref | rows | Extra |
| 1 | SIMPLE | e | index | ix_firstname | 44 | 300584 | Using index | |
| 1 | SIMPLE | s | ref | PRIMARY | 4 | employees. e.emp_no |
4 |
다른 DBMS와는 달리 MySQL에서는 필요에 따라 실행 계획을 산출하기 위해 쿼리의 일부분을 직접 실행할 때도 있다. 때문에 쿼리 자체가 상당히 복잡하고 무거운 쿼리인 경우에는 실행 계획의 조회 또한 느려질 가능성이 있다. 그리고 UPDATE나 INSERT, DELETE 문장에 대해서는 실행 계획을 확인할 방법이 없다. UPDATE나 INSERT, DELETE 문장의 실행 계획을 확인하려면 WHERE 조건절만 같은 SELECT 문장을 만들어서 대략적으로 계획을 확인해 볼 수 있다.
이제부터는 실행 계획에 표시되는 각 칼럼이 어떤 것을 의미하는지, 그리고 각 칼럼에 어떤 값들이 출력될 수 있는지 하나씩 자세히 살펴보자.
1. id 칼럼
하나의 SELECT 문장은 다시 1개 이상의 하위(SUB) SELECT 문장을 포함할 수 있다. 다음 쿼리를 살펴보자.
SELECT ...
FROM (SELECT ... FROM tb_test1) tb1,
tb_test2 tb2
WHERE tb1.id=tb2.id;위의 쿼리 문장에 있는 각 SELECT를 다음과 같이 분리해서 생각해볼 수 있다. 이렇게 SELECT 키워드 단위로 구분한 것을 "단위(SELECT) 쿼리"라고 표현하겠다.
SELECT ... FROM tb_test1;
SELECT ... FROM tb1, tb_test2 tb2 WHERE tb1.id=tb2.id;실행 계획에서 가장 왼쪽에 표시되는 id 칼럼은 단위 SELECT 쿼리별로 부여되는 식별자 값이다. 이 예제 쿼리의 경우, 실행 계획에서는 최소 2개의 id 값이 표시될 것이다.
만약 하나의 SELECT 문장 안에서 여러 개의 테이블을 조인하면 조인되는 테이블의 개수만큼 실행 계획 레코드가 출력되지만 같은 id가 부여된다. 다음 예제에서처럼 SELECT 문장은 하나인데 여러 개의 테이블이 조인되는 경우에는 id 값이 증가되지 않고 같은 id가 부여된다.
EXPLAIN
SELECT e.emp_no, e.first_name, s.from_date, s.salary
FROM empmloyees e, salaries s
WHERE e.emp_no=s.emp_no
LIMIT 10;| id | select_type | table | type | key | key_len | ref | rows | Extra |
| 1 | SIMPLE | e | index | ix_firstname | 44 | 300584 | Using index | |
| 1 | SIMPLE | s | ref | PRIMARY | 4 | employees. e.emp_no |
4 |
반대로 다음 쿼리의 실행 계획에서는 쿼리 문장이 3개의 단위 SELECT 쿼리로 구성돼 있으므로 실행 계획의 각 레코드가 각기 다른 id를 지닌 것을 확인할 수 있다.
EXPLAIN
SELECT
( (SELECT COUNT(*) FROM employees) + (SELECT COUNT(*) FROM departments) ) AS total_count;| id | select_type | table | type | key | key_len | ref | rows | Extra |
| 1 | PRIMARY | No tables used | ||||||
| 3 | SUBQUERY | departments | index | ux_deptname | 123 | 9 | Using index | |
| 2 | SUBQUERY | employees | index | ix_hiredate | 3 | 300584 | Using index |
2. select_type 칼럼
각 단위 SELECT 쿼리가 어떤 타입의 쿼리인지 표시되는 칼럼이다. select_type 칼럼에 표시될 수 있는 값은 다음과 같다.
SIMPLE
UNION이나 서브 쿼리를 사용하지 않는 단순한 SELECT 쿼리인 경우, 해당 쿼리 문장의 select_type은 SIMPLE로 표시된다(쿼리에 조인이 포함된 경우에도 마찬가지다). 쿼리 문장이 아무리 복잡하더라도 실행 계획에서 select_type이 SIMPLE인 단위 쿼리는 반드시 하나만 존재한다. 일반적으로 제일 바깥 SELECT 쿼리의 select_type이 SIMPLE로 표시된다.
PRIMARY
UNION이나 서브 쿼리가 포함된 SELECT 쿼리의 실행 계획에서 가장 바깥쪽(Outer)에 있는 단위 쿼리는 select_type이 PRIMARY로 표시된다. SIMPLE과 마찬가지로 select_type이 PRIMARY인 단위 SELECT 쿼리는 하나만 존재하며, 쿼리의 제일 바깥 쪽에 있는 SELECT 단위 쿼리가 PRIMARY로 표시된다.
UNION
UNION으로 결합하는 단위 SELECT 쿼리 가운데 첫 번째를 제외된 두 번째 이후 단위 SELECT 쿼리의 select_type은 UNION으로 표시된다. UNION의 첫 번째 단위 SELECT는 select_type이 UNION이 아니라 UNION 쿼리로 결합된 전체 집합의 select_type이 표시된다.
EXPLIAN
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;위 쿼리의 실행 계획은 다음과 같다. UNION이 되는 단위 SELECT 쿼리 3개 중에서 첫 번째(e1 테이블)만 UNION이 아니고, 나머지 2개는 모두 UNION으로 표시돼 있다. 대신 UNION의 첫 번째 쿼리는 전체 UNION의 결과를 대표하는 select_type으로 설정됐다. 여기서는 세 개의 서브 쿼리로 조회된 결과를 UNION_ALL로 결합해 임시 테이블을 만들어서 사용하고 있으므로 UNION ALL의 첫 번째 쿼리는 DERIVED라는 select_type을 갖는 것이다.
| id | select_type | table | type | key | key_len | ref | rows | Extra |
| 1 | PRIMARY | <derived2> | ALL | 30 | ||||
| 2 | DERIVED | e1 | index | ix_hiredate | 3 | 300584 | Using index | |
| 3 | UNION | e2 | index | ix_hiredate | 3 | 300584 | Using index | |
| 4 | UNION | e3 | index | ix_hiredate | 3 | 300584 | Using index | |
| UNION RESULT | <union2,3,4> | ALL |
DEPENDENT UNION
DEPENDENT UNION select_type 또한 UNION select_type과 같이 쿼리에 UNION이나 UNION ALL로 집합을 결합하는 쿼리에서 표시된다. 그리고 여기서 DEPENDENT는 UNION이나 UNION ALL로 결합된 단위 쿼리가 외부에 영향에 의해 영향을 받는 것을 의미한다. 다음의 예제 쿼리를 보면 두 개의 SELECT 쿼리가 UNION으로 결합됐으므로 select_type에 UNION이 표시된 것이다. 그런데 UNION로 결합되는 각 쿼리를 보면 이 서브 쿼리의 외부(Outer)에서 정의된 employees 테이블의 emp_no 칼럼을 사용하고 있음을 알 수 있다. 이렇게 내부 쿼리가 외부의 값을 참조해서 처리될 때 DEPENDENT 키워드가 select_type에 표시된다.
EXPLAIN
SELECT
e.first_name,
( SELECT CONCAT('Salary change count : ', COUNT(*) AS message
FROM salaries s WHERE s.emp_no=e.emp_no
UNION
SELECT CONCAT('Department change count : ', COUNT(*) AS message
FROM dept_emp de WHERE de.emp_no=emp_no
) AS message
FROM employees e
WHERE e.emp_no=10001;위 예제는 조금 억지스럽긴 하지만 UNION에 사용된 SELECT 쿼리에 아우터에 정의된 employees 테이블의 emp_no 칼럼이 사용됐기 때문에 DEPENDENT UNION이라 select_type에 표시된 것이다.
| id | select_type | table | type | key | key_len | ref | rows | Extra |
| 1 | PRIMARY | e | const | PRIMARY | 4 | const | 1 | |
| 2 | DEPENDENT SUBQUERY |
s | ref | PRIMARY | 4 | const | 17 | Using index |
| 3 | DEPENDENT UNION |
de | ref | ix_empno _fromdate |
3 | 1 | Using where; Using index |
|
| UNION RESULT | <union2,3> | ALL |
하나의 단위 SELECT 쿼리가 다른 단위 SELECT를 포함하고 있으면 이를 서브 쿼리라고 표현한다. 이처럼 서브 쿼리가 사용된 경우에는 외부(Outer) 쿼리보다 서브 쿼리가 먼저 실행되는 것이 일반적이며, 대부분 이 방식이 반대의 경우보다 빠르게 처리된다. 하지만 select_type에 "DEPENDENT" 키워드를 포함하는 서브 쿼리에는 외부 쿼리에 의존적이므로 절대 외부 쿼리보다 먼저 실행될 수가 없다. 그래서 select_type에 "DEPENDENT" 키워드가 포함된 서버 쿼리는 비효율적인 경우가 많다.
UNION RESULT
UNION 결과를 담아두는 테이블을 의미한다. MySQL에서 UNION ALL이나 UNION (DISTINCT) 쿼리는 모두 UNION의 결과를 임시 테이블로 생성하게 되는데, 실행 계획상에서 이 임시 테이블을 가리키는 라인의 select_type이 UNION RESULT다. UNION RESULT는 실제 쿼리에서 단위 쿼리가 아니기 때문에 별도로 id 값은 부여되지 않는다.
EXPLAIN
SELECT emp_no FROM salaries WHERE salary>100000
UNION ALL
SELECT emp_no FROM dept_emp WHERE from_date>'2001-01-01';| id | select_type | table | Type | key | key_len | ref | rows | Extra |
| 1 | PRIMARY | salaries | Range | ix_salary | 4 | 171094 | Using where; Using index |
|
| 2 | UNION | dept_emp | Range | ix_fromdate | 3 | 10458 | Using where; Using index |
|
| UNION RESULT | <union1,2> | ALL |
위 실행 계획의 마지막 "UNION RESULT" 라인의 table 칼럼은 "<union1,2>"로 표시돼 있는데, 아래 그림과 같이 id가 1번인 단위 쿼리의 조회 결과와 id가 2번인 단위 쿼리의 조회 결과를 UNION 했다는 것을 의미한다.
SUBQUERY
일반적으로 서브 쿼리라고 하면 여러 가지를 통틀어서 이야기할 때가 많은데, 여기서 SUBQUERY라고 하는 것은 FROM 절 외에서 사용되는 서브 쿼리만을 의미한다.
EXPLAIN
SELECT
e.first_name,
(SELECT COUNT(*) FROM dept_emp de, dept_manager dm WHERE dept_no=de.dpet_no) AS cnt
FROM employees e
WHERE e.emp_no=10001;| id | select_type | table | type | key | key_len | ref | rows | Extra |
| 1 | PRIMARY | e | const | PRIMARY | 4 | const | 1 | |
| 2 | SUBQUERY | dm | index | PRIMARY | 16 | 24 | Using index | |
| 2 | SUBQUERY | de | ref | PRIMARY | 12 | employees.dm .dept_no |
18603 | Using index |
MySQL의 실행 계획에서 FROM 절에 사용된 서브 쿼리는 select_type이 DERIVED라고 표시되고, 그 밖의 위치에서 사용된 서브 쿼리는 전부 SUBQUERY라고 표시된다. 그리고 MySQL 매뉴얼에서 사용하는 "파생 테이블"이라는 단어는 DERIVED와 같은 의미로 이해하면 된다.
서브 쿼리는 사용되는 위치와 따라 각각 다른 이름을 지니고 있다.
- 중첩된 쿼리(Nested Query)
SELECT 되는 칼럼에 사용된 서브 쿼리를 네스티드 쿼리라고 한다.- 서브 쿼리(Sub Query)
WHERE 절에 사용된 경우에는 일반적으로 그냥 서브 쿼리라고 한다.- 파생 테이블(Derived)
FROM 절에 사용된 서브 쿼리를 MySQL에서는 파생 테이블이라고 하며, 일반적으로 RDBMS 전체적으로 인라인 뷰(Inline View) 또는 서브 셀렉트(Sub Select)라고 부르기도 한다.
또한 서브 쿼리가 반환하는 값의 특성에 따라 다음과 같이 구분하기도 한다.
- 스칼라 서브 쿼리(Scalar SubQuery)
하나의 값만(칼럼이 단 하나인 레코드 1건만) 반환하는 쿼리- 로우 서브 쿼리(Row Sub Query)
칼럼의 개수에 관계없이 하나의 레코드만 반환하는 쿼리
DEPENDENT SUBQUERY
서브 쿼리가 바깥쪽(Outer) SELECT 쿼리에서 정의된 칼럼을 사용하는 경우를 DEPENDENT SUBQUERY라고 표현한다. 다음의 예제 쿼리를 한번 살펴보자.
EXPLAIN
SELECT e.first_name,
(SELECT COUNT(*)
FROM dept_emp de, dept_manager dm
WHERE dm.dept_n=de.dept_no AND emp_no=e.emp_no) AS cnt
FROM employees e
WHERE e.emp_no=10001;이럴 때는 안쪽(Inner)의 서브 쿼리 결과가 바깥쪽(Outer) SELECT 쿼리의 칼럼에 의존적이라서 DEPENDENT라는 키워드가 붙는다. 또한 DEPENDENT UNION과 같이 DEPENDENT SUBQUERY 또한 외부 쿼리가 먼저 수행된 후 내부 쿼리(서브 쿼리)가 실행돼야 하므로 (DEPENDENT 키워드가 없는) 일반 서브 쿼리보다는 처리 속도가 느릴 때가 많다.
| id | select_type | table | type | key | key_len | ref | rows | Extra |
| 1 | PRIMARY | e | const | PRIMARY | 4 | const | 1 | |
| 2 | DEPENDENT SUBQUERY |
de | ref | ix_empno _fromdate |
4 | 1 | Using index |
|
| 2 | DEPENDENT SUBQUERY | dm | ref | PRIMARY | 12 | de.dept_no | 1 | Using index |
DERIVED
서브 쿼리가 FROM 절에 사용된 경우 MySQL은 항상 select_type이 DERIVED인 실행 계획을 만든다. DERIVED는 단위 SELECT 쿼리의 실행 결과를 메모리나 디스크에 임시 테이블을 생성하는 것을 의미한다. select_type이 DERIVED인 경우에 생성되는 임시 테이블을 파생 테이블이라고도 한다. 안타깝게도 MySQL은 FROM 절에 사용된 서브 쿼리를 제대로 최적화하지 못할 때가 대부분이다. 파생 테이블에는 인덱스가 전혀 없으므로 다른 테이블과 조인할 때 성능상 불리할 때가 많다.
EXPLAIN
SELECT *
FROM
(SELECT de.emp_no FROM dept_emp de) tb,
employees e
WHERE e.emp_no=tb.emp_no;사실 위의 쿼리는 FROM 절의 서브 쿼리를 간단히 제거하고 조인으로 처리할 수 있는 형태다. 실제로 다른 DBMS에서는 이렇게 쿼리를 재작성하는 형태의 최적화 기능도 제공한다. 하지만 다음 실행 계획을 보면 알 수 있듯이 MySQL에서는 FROM 절의 서브 쿼리를 임시 테이블로 만들어서 처리한다.
| id | select_type | table | type | key | key_len | ref | rows | Extra |
| 1 | PRIMARY | <derived2 | ALL | 331603 | ||||
| 1 | PRIMARY | e | eq_ref | PRIMARY | 4 | tb.emp_no | 1 | |
| 2 | DERIVED | de | index | ix_fromdate | 3 | 334868 | Using index |
MySQL 6.0 이상 버전부터는 FROM 절의 서브 쿼리에 대한 최적화 부분이 많이 개선될 것으로 알려졌으며 다들 많이 기대하는 상태다. 하지만 그전까지는 FROM 절의 서브 쿼리는 상당히 신경 써서 개발하고 튜닝해야 한다. 현재 많이 사용되는 MySQL 5.0, 5.1 버전에서는 조인이 상당히 최적화돼 있는 편이다. 가능하다면 DERIVED 형태에 실행 계획을 조인으로 해결할 수 있게 바꿔주는 것이 좋다.
쿼리를 튜닝하기 위해 실행 계획을 확인할 때 가장 먼저 select_type 칼럼의 값이 DERIVED인 것이 있는지 확인해야 한다. 다른 방법이 없어서 서브 쿼리를 사용하는 것은 피할 수 없다. 하지만 조인으로 해결할 수 있는 경우라면 서브 쿼리보다는 조인을 사용할 것을 강력히 권장한다. 실제로 기능을 조금씩 단계적으로 추가하는 형태로 쿼리를 개발한다. 이러한 개발 과정 때문에 대부분의 쿼리가 조인이 아니라 서브 쿼리 형태로 작성되는 것이다. 믈론 이런 절차로 개발하는 것이 생산성은 높겠지만 쿼리의 성능은 더 떨어진다. 쿼리를 서브 쿼리 형태로 작성하는 것이 편한다면 반드시 마지막에는 서브 쿼리를 조인으로 풀어서 고쳐 쓰는 습관을 들이자. 그러면 어느 순간에는 서브 쿼리로 작성하는 단계 없이 바로 조인으로 복잡한 쿼리를 개발할 수 있을 것이다.
UNCACHEABLE SUBQUERY
하나의 쿼리 문장에서 서브 쿼리가 하나만 있더라도 실제 그 서브 쿼리가 한 번만 실행되는 것은 아니다. 그런데 조건이 똑같은 서브 쿼리가 실행될 때는 다시 실행하지 않고 이전의 실행 결과를 그대로 사용할 수 있게 서브 쿼리의 결과를 내부적인 캐시 공간에 담아둔다. 여기서 언급하는 서브 쿼리 캐시는 쿼리 캐시나 파생 테이블(DERIVED)와는 전혀 무관한 기능이므로 혼동하지 않도록 주의하자. 간단히 SUBQUERY와 DEPENDENT SUBQUERY가 캐시를 사용하는 방법을 비교해 보자.
- SUBQUERY는 바깥쪽(Outer)의 영향을 받지 않으므로 처음 한 번만 실행해서 그 결과를 캐시하고 필요할 때 캐시된 결과를 이용한다.
- DEPENDENT SUBQUERY는 의존하는 바깥쪽(Outer) 쿼리의 칼럼의 값 단위로 캐시해두고 사용한다.
위의 그림은 select_type이 SUBQUERY인 경우 캐시를 사용하는 방법을 표현한 것이다. 위의 그림에서는 캐시가 처음 한 번만 생성된다는 것을 알 수 있다. 하지만 DEPENDENT SUBQUERY는 서브 쿼리 결과가 딱 한 번만 캐시되는 것이 아니라 외부(Outer) 쿼리의 값 단위로 캐시가 만들어지는(즉, 위의 그림이 차례대로 반복되는 구조) 방식으로 처리된다.
select_type이 SUBQUERY인 경우와 "UNCACHEABLE SUBQUERY"는 이 캐시를 사용할 수 있느냐 없느냐에 차이가 있다. 서브 쿼리에 포함된 요소에 의해 캐시 자체가 불가능할 수가 있는데, 이 경우 select_type이 UNCACHEABLE SUBQUERY로 표시된다. 캐시를 사용하지 못하도록 하는 요소로는 대표적으로 다음과 같은 것들이 있다.
- 사용자 변수가 서브 쿼리에 사용된 경우
- NOT-DETERMINISTIC 속성의 스토어드 루틴이 서브 쿼리에 사용된 경우
- UUID()나 RAND()와 같이 결과값이 호출될 때마다 달라지는 함수가 서브 쿼리에 사용된 경우
다음은 사용자 변수(@status)가 사용된 쿼리 예제다. 이 경우 WHERE 절에 사용된 단위 쿼리의 select_type은 UNCACHEABLE SUBQUERY로 표시되는 것을 알 수 있다.
EXPLAIN
SELECT *
FROM employees e
WHERE e.emp_no (
SELECT @status FROM dept_emp de WHERE de.dept_no='d005'
);| id | select_type | table | type | key | key_len | ref | rows | Extra |
| 1 | PRIMARY | e | ALL | 300584 | Using where | |||
| 2 | UNCACHEABLE SUBQUERY | de | ref | PRIMARY | 12 | const | 53288 | Using where; Using index |
UNCACHEABLE UNION
UNCACHEABLE UNION이란 UNION과 UNCACHEABLE 이 두 개의 키워드의 속성이 혼합된 select_type을 의미한다. UNCACHEABLE UNION은 MySQL 5.0에서는 표시되지 않으며, MySQL 5.1부터 추가된 select_type이다.
참고
- Real MySQL
