[Real MySQL 8.0 1권] 10. 실행 계획 (2)

유혁·2026년 6월 6일

Real MySQL 8.0

목록 보기

21/22

본 포스트는 Real MySQL 8.0 1권을 읽은 뒤 정리하는 글입니다.

10.3 실행 계획 분석

10.3.1 `id` 칼럼

하나의 SELECT 문장은 1개 이상의 하위 SELECT 문장을 포함할 수 있으며, 이는 다시 SELECT 키워드 단위로 구분하여 나타낼 수 있다. 이를 단위(SELECT) 쿼리라고 표현하겠다.

mysql> SELECT ...
		FROM (SELECT ... FROM tb_test1) tb1, tb_test2 tb2
        WHERE tb1.id=tb2.id);
        
mysql> SELECT ... FROM tb_test1;
mysql> SELECT ... FROM tb1, tb_test2 tb2 WHERE tb1.id=tb2.id;

실행 계획에서 가장 왼쪽에 표시되는 id 칼럼은 단위 SELECT 쿼리별로 부여되는 식별자 값이다. 하나의 SELECT 문장 안에서 여러 개의 테이블을 조인하면 테이블 개수만큼 실행 계획 레코드가 출력되지만 같은 id값이 부여된다.

조인이 사용된 경우

mysql> 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;

단위 SELECT 쿼리로 구성되어 있는 경우

mysql> EXPLAIN FORMAT=TRADITIONAL
		SELECT 
   		( (SELECT COUNT(*) FROM employees) + (SELECT COUNT(*) 
        	FROM departments) ) AS total_count;

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

10.3.2 `select_type` 칼럼

select_type 칼럼은 각 단위 SELECT 쿼리가 어떤 타입의 쿼리인지 표시되는 칼럼으로, 표시될 수 있는 값은 다음과 같다.

"SIMPLE"

UNION이나 서브쿼리를 사용하지 않은 단순한 SELECT 쿼리인 경우 "SIMPLE"로 표시된다.

"PRIMARY"

UNION이나 서브쿼리를 가지는 SELECT 쿼리의 실행 계획에서 가장 바깥쪽에 있는 단위 쿼리는 "PRIMARY"로 표시된다.

"UNION"

UNION으로 결합하는 단위 SELECT 쿼리 가운데 첫 번째를 제외한 두 번째 이후 단위 SELECT 쿼리는 "UNION"으로 표시된다.

UNION의 첫 번째 단위 SELECT는UNION되는 쿼리 결과들을 모아서 저장하는 임시 테이블로, "DEREIVED"로 표시된다.

"DEPENDENT UNION"

"DEPENDENT UNION"은 UNION이나 UNION ALL로 집합을 결합하는 쿼리에서 표시되며, "DEPENDENT"는 결합된 단위 쿼리가 외부 쿼리에 의해 영향을 받는 것을 의미한다.

"UNION RESULT"

"UNION RESULT"는 UNION 결과를 담아두는 테이블을 의미한다. "UNION RESULT"는 실제 쿼리에서 단위 쿼리가 아니기 때문에 별도의 id 값은 부여되지 않는다.

mysql> EXPLAIN
		SELECT emp_no FROM salaries WHERE salary>100000
    	UNION DISTINCT
    	SELECT emp_no FROM dept_emp WHERE from_date>'2001-01-01';

위 쿼리에 대한 실행 계획의 마지막 "UNION RESULT" 라인의 table 칼럼은 "<union 1,2>"로 표시돼있는데, 이것은 id값이 1인 단위 쿼리의 조회 결과와 id값이 2인 단위 쿼리의 조회 결과를 UNION 했다는 것을 의미한다.

MySQL 8.0 버전부터는 UNION ALL의 경우 임시 테이블을 사용하지 않지만, UNION, UNION DISTINCT는 여전히 임시 테이블에 결과를 버퍼링한다.

"SUBQUERY"

select_type 칼럼의 "SUBQUERY"는 FROM 절 이외에서 사용되는 서브쿼리만을 의미한다.

"DEPENDENT SUBQUERY"

서브쿼리가 바깥쪽 SELECT 쿼리에서 정의된 칼럼을 사용하는 경우, select_type에 "DEPENDENT SUBQUERY"라고 표시된다.

mysql> EXPLAIN FORMAT=TRADITIONAL
		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';

위 쿼리의 경우 안쪽의 서브쿼리 결과가 바깥쪽 SELECT 쿼리의 칼럼에 의존적이기 때문에 "DEPENDENT" 키워드가 붙는다.

"DEPENDENT UNION"과 "DEPENDENT SUBQUERY" 모두 외부 쿼리가 먼저 수행된 후 내부 쿼리가 실행돼야 하므로 일반 서브쿼리보다는 처리 속도가 느릴 때가 많다.

"DERIVED"

MySQL 서버의 실행 계획에서 FROM 절에 사용된 서브쿼리는 select_type이 "DERIVED"로 표시된다.

"DERIVED"는 단위 SELECT 쿼리의 실행 결과로 메모리나 디스크에 임시 테이블을 생성하는 것을 의미하며, select_type이 "DERIVED"인 경우에 생성되는 임시 테이블을 파생 테이블이라고도 한다.

MySQL 5.6 버전부터는 옵티마이저 옵션(optimizer_switch 시스템 변수)에 따라 FROM 절의 서브쿼리를 외부 쿼리와 통합하는 형태의 최적화가 수행되기도 하며, 쿼리 특성에 맞게 임시 테이블에도 인덱스를 추가해서 만들 수 있게 최적화됐다.

쿼리를 튜닝에서 가장 먼저 select_type 칼럼의 값이 "DERIVED"인 것이 있는지 확인하고, 서브쿼리는 최대한 조인을 사용하도록 변경하는 것을 권장한다.

"DEPENDENT DERIVED"

MySQL 8.0 버전부터는 래터럴 조인(LATERAL JOIN) 기능이 추가되면서 FROM 절의 서브쿼리에서도 외부 칼럼을 참조할 수 있게 됐다. 다음은 래터럴 조인 활용 예제 및 실행 계획이다.

mysql> 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;

select_type 칼럼의 "DEPENDENT DERIVED" 키워드는 해당 테이블이 래터럴 조인으로 사용된 것을 의미한다.

"UNCACHEABLE SUBQUERY"

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

"SUBQUERY" 와 "DEPENDENT SUBQUERY"가 캐시를 사용하는 방법은 아래과 같다.

"SUBQUERY"는 바깥쪽의 영향을 받지 않으므로 처음 한 번만 실행해서 그 결과를 캐시하고 필요할 때 캐시된 결과를 이용한다.
"DEPENDENT SUBQUERY"는 의존하는 바깥쪽 쿼리의 칼럼의 값 단위로 캐시해두고 사용한다.

아래 이미지는 select_type이 "SUBQUERY"인 경우 캐시를 사용하는 방법을 표현한 것이다.

서브쿼리에 포함된 요소에 의해 캐시 자체가 불가능할 수 있는데, 그럴 경우 select_type이 "UNCACHEABLE SUBQUERY"로 표시되며, 캐시를 사용하지 못하게 하는 대표적인 요소는 다음과 같다.

사용자 변수가 서브쿼리에 사용된 경우
NOT-DETERMINISTIC 속성의 스토어드 루틴이 서브쿼리 내에 사용된 경우
UUID()나 RAND()와 같이 결괏값이 호출할 때마다 달라지는 함수가 서브쿼리에 사용된 경우

"UNCACHEABLE UNION"

"UNCACHEABLE UNION"은 "UNION"과 "UNCACHEABLE" 두 개 키워드의 속성이 혼합된 select_type이다.

"MATERIALIZED"

MySQL 5.6 버전부터 도입된 select_type으로, 주로 FROM 절이나 IN (subquery) 형태의 쿼리에 사용된 서브쿼리 부분이 임시 테이블로 구체화된 경우 select_type이 "MATERIALIZED" 키워드로 표시된다.

10.3.3 `table` 칼럼

MySQL 서버의 실행 계획은 단위 SELECT 쿼리 기준이 아닌 테이블 기준으로 표시되며, 테이블 이름에 별칭이 부여된 경우 별칭이 표시된다.

별도의 테이블을 사용하지 않는 SELECT 쿼리인 경우에는 table 칼럼에 NULL이 표시된다.

table 칼럼에 <derived N>, <union M, N>, 또는 <subquery N>과 같이 "<>"로 둘러싸인 이름이 명시되는 경우는 임시 테이블을 의미하며, "<>"안에 항상 표시되는 숫자는 단위 SELECT 쿼리의 id 값을 지칭한다.

10.3.4 `patitions` 칼럼

MySQL 8.0 버전부터는 EXPLAIN 명령으로 파티션 관련 실행 계획을 확인할 수 있게 되었다.

파티션을 참조하는 쿼리(파티션 키 칼럼을 WHERE 조건으로 가진)의 경우 옵티마이저가 쿼리 처리를 위해 필요한 파티션들의 목록만 모아서 partitions 칼럼에 표시해준다.

10.3.5 `type` 칼럼

쿼리의 실행 계획에서 type 칼럼은 MySQL 서버가 각 테이블의 레코드를 어떤 방식으로 읽었는지를 나타낸다.

system

레코드가 1건만 존재하는 테이블 또는 한 건도 존재하지 않는 테이블을 참조하는 형태의 접근 방법을 system이라고 한다.

이 접근 방법은 MyISAM이나 MEMORY 테이블에서만 사용되느 접근 방법이다.

const

테이블의 레코드 건수와 관계없이 쿼리가 프라이머리 키나 유니크 키 칼럼을 이용하는 WHERE 조건절을 가지고 있으며, 반드시 1건을 반환하는 쿼리의 처리 방식을 const라고 한다.

다른 DBMS에서는 이를 유니크 인덱스 스캔(UNIQUE INDEX SCAN)이라고도 표현한다.

다중 칼럼으로 구성된 프라이머리 키나 유니크 키 중에서 인덱스의 일부 칼럼만 조건으로 사용하는 경우 MySQL 엔진이 데이터를 읽어보지 않고서는 레코드가 1건이라는 것을 확신할 수 없으므로 const 타입의 접근 방법을 사용할 수 없으며, const가 아닌 ref로 표시된다.

mysql> EXPLAIN
		SELECT * FROM dept_emp WHERE dept_no='d005';

프라이머리 키나 유니크 인덱스의 모든 칼럼을 동등 조건으로 WHERE 절에 명시하면 다음 예제와 같이 const 접근 방법을 사용한다.

mysql> EXPLAIN
		SELECT * FROM dept_emp WHERE dept_no='d005' AND emp_no=10001;

eq_ref

eq_ref 접근 방법은 여러 테이블이 조인되는 쿼리의 실행 계획에서만 표시된다.

조인에서 처음 읽은 테이블의 칼럼값을 그다음 읽어야 할 테이블의 프라이머리 키나 유니크 키 칼럼의 검색 조건에 사용할 때를 가리켜 eq_ref라고 하며, 두 번째 이후에 읽는 테이블의 type 칼럼에 eq_ref가 표시된다.

두 번째 이후에 읽히는 테이블을 유니크 키로 검색할 때 그 유니크 인덱스는 NOT NULL이어야 하며, 다중 칼럼으로 만들어진 프라이머리 키나 유니크 인덱스라면 인덱스의 모든 칼럼이 비교 조건에 사용돼야만 eq_ref 접근 방법이 사용될 수 있다.

즉, 조인에서 두 번째 이후에 읽는 테이블에서 반드시 1건만 존재한다는 보장이 있어야 사용할 수 있다.

ref

ref 접근 방법은 eq_ref와는 달리 조인의 순서와 관계없이 사용되며, 프라이머리 키나 유니크 키 등의 제약 조건이 없다.

인덱스의 종류와 관게없이 동등 조건으로 검색할 때는 ref 접근 방법이 사용된다.

ref 타입은 반환되는 레코드가 반드시 1건이라는 보장이 없으므로 const나 eq_ref보다는 빠르지 않지만, 동등 조건으로 비교되므로 매우 빠른 레코드 조회 방법의 하나다.

const, eq_ref, ref 세 가지 모두 매우 좋은 방법으로 인덱스의 분포도가 나쁘지 않다면 성능상의 문제를 일으키지 않으므로 쿼리를 튜닝할 때 크게 신경 쓰지 않고 넘어가도 무방하다.

fulltext

fulltext 접근 방법은 MySQL 서버의 전문 검색(Full-text Search) 인덱스를 사용해 레코드를 읽는 접근 방법을 의미한다.

MySQL 서버는 쿼리에서 전문 인덱스를 사용하는 조건과 그 이외의 일반 인덱스를 사용하는 조건을 함께 사용하면 일반 인덱스의 접근 방법이 const, eq_ref, ref가 아니라면 일반적으로 전문 인덱스를 사용하는 조건을 선택해서 처리한다.

전문 검색은 MATCH (...) AGAINST (...) 구문을 사용해 실행하는데, 반드시 해당 테이블에 전문 검색용 인덱스가 준비돼 있어야만 한다.

일반적으로 쿼리에 전문 검색 조건 (MATCH (...) AGAINST(...))을 사용하면 MySQL 서버는 주저 없이 fulltext 접근 방법을 사용하지만,range 접근 방법이 더 빨리 처리되는 경우가 빈번해 조건별로 성능을 확인해 보는 편이 좋다.

ref_or_null

이 접근 방법은 ref 접근 방법 또는 NULL 비교 (IS NULL) 접근 방법을 의미한다.

unique_subquery

WHERE 조건절에 사용될 수 있는 IN (subquery) 형태의 쿼리를 위한 접근 방법으로, 서브쿼리에서 중복되지 않는 유니크한 값만을 반환할 때 사용한다.

index_subquery

IN 연산자의 특성상 IN (subquery) 또는 IN (상수 나열) 형태의 조건은 괄호 안에 있는 값의 목록에서 중복된 값이 먼저 제거돼야 한다.

IN (subquery)에서 서브쿼리가 중복된 값을 반환하는 경우가 존재하는데, 이를 인덱스를 이용해 제거할 수 있을 때 index_subquery 접근 방법이 사용된다.

range

range는 인덱스를 하나의 값이 아닌 범위로 검색하는 경우를 의미하며, 주로 <, >, IS NULL, BETWEEN, IN, LIKE 등의 연산자를 이용해 인덱스를 검색할 때 사용된다.

index_merge

index_merge 접근 방법은 2개 이상의 인덱스를 이용해 각각의 검색 결과를 만들어낸 후, 그 결과를 병합해서 처리하는 방식이다.

index_merge 접근 방법에는 다음과 같은 특징이 있다.

여러 인덱스를 읽어야 하므로 일반적으로 range 접근 방법보다 효율성이 떨어진다.
전문 검색 인덱스를 사용하는 쿼리에서는 index_merge가 적용되지 않는다.
index_merge 접근 방법으로 처리된 결과는 항상 2개 이상의 집합이 되기 때문에 교집합이나 합집합, 또는 중복 제거와 같은 부가적인 작업이 더 필요하다.

index

index 접근 방법은 인덱스를 처음부터 끝까지 읽는 인덱스 풀 스캔을 의미하며, range, const, ref 같은 접근 방법으로 인덱스를 사용하지 못하는 경우와 동시에 아래 두 조건 중 하나를 충족하는 쿼리에서 사용된다.

인덱스에 포함된 칼럼만으로 처리할 수 있는 쿼리인 경우
인덱스를 이용해 정렬이나 그루핑 작업이 가능한 경우

ALL

ALL 접근 방법은 풀 테이블 스캔을 의미하며, 테이블을 처음부터 끝까지 전부 읽어서 불필요한 레코드를 제거하고 반환하므로 위 접근 방법들로 처리할 수 없을 때 가장 마지막에 선택하는 비효율적인 방법이다.

10.3.6 `possible_keys` 칼럼

possible_keys 칼럼에 있는 내용은 옵티마이저가 최적의 실행 계획을 만들기 위해 후보로 선정했던 접근 방법에서 사용되는 인덱스의 목록이다.

말 그대로 "사용될 법했던 인덱스의 목록"으로, possible_keys 칼럼에 인덱스 이름이 나열됐다고 해서 그 인덱스를 사용한다고 판단하지 않도록 주의하자.

10.3.7 `key` 칼럼

key 칼럼에 표시되는 인덱스는 최종 선택된 실행 계획에서 사용하는 인덱스로, 쿼리를 튜닝할 때는 key 칼럼에 의도했던 인덱스가 표시되는지 확인하는 것이 중요하다.

key 칼럼에 표시되는 값이 PRIMARY인 경우에는 프라이머리 키를 사용한다는 의미이며, 이외의 값은 모두 테이블이나 인덱스를 생성할 때 부여했던 고유 이름이다.

실행 계획의 type 칼럼이 index_merge가 아닌 경우에는 반드시 테이블 하나당 하나의 인덱스만 이용하며, index_merge 실행 계획이 사용될 때는 2개 이상의 인덱스가 사용되어 인덱스가 ","로 구분되어 표시된다.

type이 ALL일 때와 같이 인덱스를 전혀 사용하지 못하면 key 칼럼은 NULL로 표시된다.

10.3.8 `key_len` 칼럼

key_len 칼럼은 매우 중요한 정보 중 하나로, 쿼리를 처리하기 위해 인덱스의 각 레코드에서 몇 바이트까지 사용했는지 알려주는 값이다.

mysql> EXPLAIN
		SELECT * FROM dept_emp WHERE dept_no='d005';

위 예제는 두 개의 칼럼(dept_no, emp_no)으로 구성된 프라이머리 키를 가지는 dept_emp 테이블을 조회하는 쿼리 및 실행 계획으로, dept_no만 비교에 사용된다.

dept_no의 칼럼의 타입은 CHAR(4)로, 프라이머리 키에서 앞쪽 16바이트만 유효하게 사용되어 key_len 칼럼의 값이 16으로 나타난다.

utf8mb4 문자 집합에서 문자 하나가 차지하는 공간은 1바이트에서 4바이트까지 가변적이나, MySQL 서버가 메모리 공간을 할당해야 할 때는 고정적으로 4바이트로 계산한다.

위와 똑같은 인덱스를 사용하지만 dept_no 칼럼과 emp_no 칼럼에 대해 각각 조건을 하나씩 가지고 있는 경우를 살펴보자.

mysql> EXPLAIN
		SELECT * FROM dept_emp WHERE dept_no='d005' AND emp_no=10001;

emp_no 칼럼 타입은 INTEGER로 4바이트를 차지한다. 따라서 위 쿼리의 key_len 칼럼은 dept_no 칼럼의 길이와 emp_no 칼럼의 길이의 합인 20으로 표시된다.

MySQL에서 NULLABLE 칼럼으로 정의된 경우, NULL 여부를 저장하기 위해 1바이트를 추가로 더 사용한다.

10.3.9 `ref` 칼럼

실행 계획의 ref 칼럼의 내용은 접근 방법이 ref일 때 참조 조건으로 어떤 값이 제공됐는지 보여준다. 상숫값을 지정했다면 const, 다른 테이블의 칼럼값이면 그 테이블명과 칼럼명이 표시된다.

가끔 ref 칼럼의 값이 "func"라고 표시될 때가 있다. 이는 "Function"의 줄임말로 참조용으로 사용되는 값을 그대로 사용한 것이 아닌 콜레이션 변환이나 값 자체의 연산을 거쳐 참조했다는 것을 의미한다.

사용자가 명시적으로 값을 변환할 때뿐만 아니라, MySQL 서버가 내부적으로 값을 변환해야 할 때도 ref 칼럼에 "func"가 출력된다.

10.3.10 `rows` 칼럼

MySQL 실행 계획의 rows 칼럼값은 옵티마이저가 실행 계획의 효율성 판단을 위해 예측했던 레코드 건수를 보여준다.

이 값은 각 스토리지 엔진별로 가지고 있는 통계 정보를 참조해 MySQL 옵티마이저가 산출해 낸 예상값이라서 정확하지 않다.

또한 rows 칼럼에 표시되는 값은 반환하는 레코드의 예측치가 아니라 쿼리를 처리하기 위해 얼마나 많은 레코드를 읽고 체크해야하는 지를 의미하여, 실제 쿼리 결과 반환된 레코드 건수와 일치하지 않는 경우가 많다.

10.3.11 `filtered` 칼럼

대부분의 쿼리에서 WHERE 절에 사용되는 조건이 모두 인덱스를 사용할 수 있는 것은 아니다.

조인이 사용된 경우에는 WHERE 절에서 인덱스를 사용할 수 있는 조건도 중요하지만, 인덱스를 사용하지 못하는 조건에 일치하는 레코드 건수를 파악하는 것도 중요하다.

filtered 칼럼의 값은 조건에 의해 필터링되고 남은 레코드의 비율을 의미한다.

유혁

백엔드 개발자

이전 포스트

[Real MySQL 8.0 1권] 10. 실행 계획 (1)

다음 포스트