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

유혁·2026년 6월 8일

CS Database Real MySQL 8.0

Real MySQL 8.0

목록 보기

22/22

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

10.3.12 `Extra` 칼럼

const row not found

쿼리의 실행 계획에서 const 접근 방법으로 테이블을 읽었지만 실제로 해당 테이블에 레코드가 1건도 존재하지 않으면 Extra 칼럼에 "const row not found" 문구가 표시된다.

Deleting all rows

MyISAM 스토리지 엔진과 같이 스토리지 엔진의 핸들러 차원에서 테이블의 모든 레코드를 삭제하는 기능을 제공하는 스토리지 엔진 테이블인 경우 Extra 칼럼에 "Deleting all rows" 문구가 표시된다.

이 문구는 WHERE 조건절이 없는 DELETE 문장의 실행 계획에서 자주 표시되며, 테이블의 모든 레코드를 삭제하는 핸들러 기능(API)를 한번 호출함으로써 처리됐다는 것을 의미한다.

Distinct

다음은 Extra 칼럼에 DISTINCT 키워드가 표시되는 예제 쿼리이다.

mysql> EXPLAIN
		SELECT DISTINCT d.dept_no
        FROM departments d, dept_emp de WHERE de.dept_no=d.dept_no;

위 쿼리에서 조회하려는 값은 dept_no로, 두 테이블에 모두 존재하는 dept_no만 중복 없이 유니크하게 가져오기 위한 쿼리이다. 따라서 두 테이블을 조인한 후 결과에 다시 DISTINCT 처리를 넣은 것이다.

위 이미지는 실행 계획의 Extra 칼럼에 "Distinct"가 표시되는 경우 어떻게 처리되는지 보여준다. 쿼리의 DISTINCT를 처리하기 위해 조인하지 않아도 되는 항목은 모두 무시하고 필요한 것만 조인한다.

FirstMatch

세미 조인의 여러 최적화 중에서 FirstMatch 전력이 사용되면 실행 계획의 Extra 칼럼에 "FirtstMatch(table_name)" 메시지를 출력하며, 메시지의 테이블명은 기준 테이블을 의미한다.

Full scan on NULL key

이 처리는 col1 IN (SELECT col2 FROM ...)과 같은 조건을 가진 쿼리에서 자주 발생할 수 있는데, col1의 값이 NULL이 된다면 조건은 NULL IN (SELECT col2 FROM ...)과 같이 바뀐다.

SQL 표준에서는 NULL을 "알 수 없는 값"으로 정의하고 있으며, NULL에 대한 연산 규칙을 정의하고 있다. 그 정의대로 연산을 수행하기 위해 이 조건은 다음과 같이 비교돼야 한다.

서브쿼리가 1건이라도 결과 레코드를 가진다면 최종 비교 결과는 NULL
서브쿼리가 1건도 결과 레코드를 가지지 않는다면 최종 비교 결과는 FALSE

이 비교 과정에서 co1이 NULL이면 서브쿼리에 사용된 테이블에 대해서 풀 테이블 스캔(Full scan)을 해야만 결과를 알아낼 수 있다.

Extra 칼럼의 "Full scan on NULL key"는 MySQL 서버가 쿼리를 실행하는 중 col1이 NULL을 만나면 차선책으로 서브쿼리 테이블에 대해서 풀 테이블 스캔을 사용할 것이라는 사실을 알려주는 문구다.

Impossible HAVING

쿼리에 사용된 HAVING 절의 조건을 만족하는 레코드가 없을 때 실행 계획의 Extra 칼럼에는 "Impossible HAVING" 키워드가 표시된다.

Impossible WHERE

쿼리의 WHERE 조건이 항상 FALSE가 될 수밖에 없는 경우 "Impossible WHERE"가 표시된다.

LooseScan

세미 조인 최적화 중에서 LooseScan 최적화 전략이 사용되면 실행 계획의 Extra 칼럼에 "LooseScan" 문구가 표시된다.

No matching min/max row

MIN()이나 MAX()와 같은 집합 함수가 있는 쿼리의 조건절에 일치하는 레코드가 한 건도 없을 때 Extra 칼럼에 "No matching min/max row" 메시지가 출력되며,MIN() 혹은 MAX()의 결과로 NULL이 반환된다.

no matching row in const table

쿼리의 조인에 사용된 테이블에서 const 방법으로 접근할 때 일치하는 레코드가 없다면 "no matching row in const table" 메시지를 표시한다.

No matching rows after partition pruning

"No matching rows after partition pruning" 메시지는 파티션된 테이블에 대한 UPDATE 또는 DELETE 명령의 실행 계획에서 표시될 수 있는데, 해당 파티션에서 UPDATE하거나 DELETE할 레코드가 없을 때 표시된다.

No tables used

FROM 절이 없는 쿼리 문장이나 FROM DUAL 형태의 쿼리 실행 계획에서는 Extra 칼럼에 "No tables used"라는 메시지가 출력된다.

Not exists

A 테이블에는 존재하지만 B 테이블에는 없는 값을 조회해야 하는 쿼리에서 NOT IN (subquery) 형태 혹은 NOT EXISTS 연산자를 주로 사용하며, 이런 형태의 조인을 안티-조인(ANTI-JOIN)이라고 한다.

레코드의 건수가 많을 때는 아우터 조인(LEFT OUTER JOIN)을 이용해 빠른 성능으로 똑같은 처리를 구현할 수 있다.

다음 예제 쿼리는 departments 테이블을 아우터 조인해서 ON 절이 아닌 WHERE 절에 아우터 테이블(departments)의 dept_no 칼럼이 NULL인 레코드만 체크해서 가져온다. 즉, 안티-조인은 일반 조인을 했을 때 나오지 않는 결과만 가져오는 방법이다.

mysql> EXPLAIN
		SELECT *
        FROM dept_emp de
        	LEFT JOIN departments d ON de.dept_no=d.dept_no
        WHERE d.dept_no IS NULL;

아우터 조인을 이용해 안티-조인을 수행하는 쿼리에서는 실행 계획의 Extra 칼럼에 "Not exists" 메시지가 표시되며, 이는 테이블에 조인 조건에 일치하는 레코드가 여러 건이 있다고 하더라도 딱 1건만 조회해보고 처리를 완료하는 최적화를 의미한다.

Plan isn't ready yet

MySQL 8.0 버전에서는 SHOW PROCESSLIST 명령으로 실행 중인 커넥션id를 확인한 뒤, EXPLAIN FOR CONNECTION "id" 명령을 통해 해당 커넥션에서 실행하고 있는 쿼리의 실행 계획을 확인할 수 있다.

EXPLAIN FOR CONNCETION 명령을 실행했을 때 Extra 칼럼에 "Plan is not ready yet" 메시지가 표시되는 경우는 해당 커넥션에서 아직 쿼리의 실행 계획을 수립하지 못한 상태에서 EXPLAIN FOR CONNCETION 명령이 실행된 것을 의미한다.

Range checked for each record (index map: N)

다음 두 개의 테이블을 조인하는 쿼리를 살펴보자.

mysql> EXPLAIN
		SELECT *
        FROM employees e1, employees e2
        WHERE e2.emp_no >= e1.emp_no;

조인 조건에 상수가 없이 둘 다 변수(e1.emp_no, e2.emp_no)인 경우 e1 테이블의 레코드를 하나씩 읽을 때마다 e1.emp_no의 값이 달라져 쿼리 비용 계산을 위한 기준 값이 계속 변화하게 되어 MySQL 옵티마이저는 e2 테이블을 읽을 때 어떤 접근 방법이 효율적일지 판단할 수 없다.

emp_no가 1번부터 1억 번까지 있다고 가정하면, e1 테이블을 처음부터 끝까지 스캔하며 e2 테이블에서 조건을 만족하는 레코드를 찾아야한다.

e1.emp_no=1인 최악의 경우에는 e2 테이블의 1억 건 전부를 읽어야 하지만, e1.emp_no=10000000인 경우에는 e2 테이블을 한 건만 읽으면 된다.

따라서 e1 테이블의 emp_no가 작을 때는 e2 테이블을 풀 테이블 스캔으로, 큰 값일 때는 인덱스 레인지 스캔으로 접근하는 것이 최적의 조인 방식이다.

이는 레코드마다 인덱스 레인지 스캔을 체크하는 것으로, Extra 표시되는 "Range checked for each record"의 의미다.

아래 이미지는 예제의 "Range checked for each record" 실행 계획 및 처리 시나리오를 나타낸 것이다.

실행 계획의 Extra 칼럼 출력 내용 중 "(index map: 0x1)"은 사용할지 말지를 판단하는 후보 인덱스의 순서를 나타낸다. "index map"은 16진수로 표현되며, "0x1"은 이진수로 바꿔도 1이다.

따라서 이 쿼리는 e2(employees) 테이블의 첫 번째 인덱스를 사용할지, 테이블을 풀 스캔할지를 매 레코드 단위로 결정하면서 처리된다.

여기서 첫 번째 인덱스는 SHOW CREATE TABLE employees 명령으로 테이블의 구조를 조회했을 때 제일 먼저 출력되는 인덱스를 의미한다.

Extra 칼럼에 "Range checked for each record"가 표시되면 type 칼럼에는 ALL로 표시되어 풀 테이블 스캔으로 처리된 것으로 해석할 수 있지만, "index map"에 표시된 후보 인덱스를 사용할지 여부를 검토해 후보 인덱스가 도움이 되지 않는다면 최종적으로 풀 테이블 스캔을 사용하기 때문에 ALL로 표시된 것이다.

Recursive

MySQL 8.0 버전부터는 CTE(Common Table Expression)을 이용해 재귀 쿼리를 작성할 수 있게 되었으며, 이를 이용한 재귀 쿼리의 실행 계획은 Extra 칼럼에 "Recursive" 구문이 표시된다.

Rematerialize

MySQL 8.0 버전부터는 레터럴 조인(LATERAL JOIN) 기능이 추가됐는데, 이 경우 레터럴로 조인되는 테이블은 선행 테이블의 레코드별로 서브 쿼리를 실행해서 그 결과를 임시 테이블에 저장한다.

이 과정을 Rematerializing이라고 하며, Extra 칼럼에는 "Rematerialize" 문구가 표시된다.

Select tables optimized away

MIN() 또는 MAX()만 SELECT 절에 사용되거나 GROUP BY로 MIN(), MAX()를 조회하는 쿼리가 인덱스를 오름차순 또는 내림차순으로 1건만 읽는 형태의 최적화가 적용된다면 Extra 칼럼에 "Select tables optimized away"가 표시된다.

Start temporary, End temporary

세미 조인 최적화 중 Duplicate Weed-Out 최적화 전략이 사용되면 MySQL 옵티마이저는 Extra 칼럼에 "Start temporary", "End temporary" 문구를 표시하게 된다.

Duplicate Weed-Out 최적화 전략은 불필요한 중복 건을 제거하기 위해서 내부 임시 테이블을 사용하는데, 이때 조인되어 내부 임시 테이블에 저장되는 테이블을 식별할 수 있게 조인의 첫 번째 테이블이 "Start temporary" 문구를, 조인이 끝나는 부분에 "End temporary" 문구를 표시한다.

unique row not found

두 개의 테이블이 각각 유니크(프라이머리 키 포함) 칼럼으로 아우터 조인을 수행하는 쿼리에서 아우터 테이블에 일치하는 레코드가 존재하지 않을 때 Extra 칼럼에 이 코멘트가 표시된다.

Using filesort

ORDER BY 처리가 인덱스를 사용하지 못할 때 실행 계획의 Extra 칼럼에 "Using filesort" 코멘트가 표시되며, 이는 조회된 레코드를 정렬용 메모리 버퍼에 복사해 퀵 소트 호는 힙 소트 알고리즘을 이용해 정렬을 수행하게 된다는 의미다.

실행 계획에 Extra 칼럼에 "Using filesort"가 출력되는 쿼리는 많은 부하를 일으키므로 가능하다면 쿼리를 튜닝하거나 인덱스를 생성하는 것이 좋다.

Using index(커버링 인덱스)

데이터 파일을 전혀 읽지 않고 인덱스만 읽어서 쿼리를 모두 처리할 수 있을 때 Extra 칼럼에 "Using Index"가 표시되며, 이렇게 인덱스만으로 처리되는 것을 커버링 인덱스(Covering index)라고 한다.

레코드 건수에 따른 차이가 있겠지만 쿼리를 커버링 인덱스로 처리할 수 있을 때와 못할 때의 성능 차이는 수십 배에서 수백 배까지 날 수 있으나, 무조건 커버링 인덱스로 처리하려고 인덱스에 많은 칼럼을 추가하면 인덱스의 크기가 커져 메모리 낭비가 심해지고 레코드를 저장하거나 변경하는 작업이 매우 느려지므로 주의하자.

접근 방법 중 인덱스를 사용하는 실행 계획에서는 모두 Extra 칼럼에 "Using index"가 표시될 수 있다. 즉, 인덱스 레인지 스캔(eq_ref, ref, range, index_merge 등)을 사용할 때만 커버링 인덱스로 처리되는 것이 아니라, 인덱스 풀 스캔(index)을 실행할 때도 처리가 가능하다.

Using index condition

MySQL 옵티마이저가 인덱스 컨디션 푸시다운(Index condition Pushdown) 최적화를 사용하면 Extra 칼럼에 "Using index condition" 메시지가 표시된다.

Using index for group-by

GROUP BY 처리를 인덱스를 이용해 레코드의 정렬 없이 인덱스의 필요한 부분만을 읽는 루스 인덱스 스캔을 사용하면 쿼리의 실행 계획에서 Extra 칼럼에 "Using index for group-by" 메시지가 표시된다.

타이트 인덱스 스캔(인덱스 스캔)을 통한 `GROUP BY` 처리

인덱스를 이용해 GROUP BY 절을 처리할 수 있더라도 AVG(), SUM(), COUNT() 처럼 조회하려는 값이 모든 인덱스를 다 읽어야 할 때는 필요한 레코드만 읽을 수 없어 루스 인덱스 스캔이라고 하지 않는다. 또한 이런 쿼리의 실행 계획은 "Using index for group-by" 메시지가 출력되지 않는다.

루스 인덱스 스캔을 통한 `GROUP BY` 처리

단일 칼럼으로 구성된 인덱스에서는 그루핑 칼럼 말고는 아무것도 조회하지 않는 쿼리에서 루스 인덱스 스캔을 사용할 수 있다.

또한 다중 칼럼으로 이루어진 인덱스에서는 GROUP BY 절이 인덱스를 사용할 수 있어야 함은 물론이고 MIN()이나 MAX() 같이 조회하는 값이 인덱스의 첫 번째 또는 마지막 레코드만 읽어도 되는 쿼리는 루스 인덱스 스캔이 사용될 수 있다.

GROUP BY에서 인덱스를 사용하려면 위와 같은 요건이 갖춰져야 하지만, 아래와 같은 WHERE 절에서 사용하는 인덱스에 의해서도 인덱스 사용 여부가 영향을 받는다.

WHERE 조건절이 없는 경우
해당 쿼리는 GROUP BY 절의 칼럼과 SELECT로 가져오는 칼럼이 루스 인덱스 스캔을 사용할 수 있는 조건을 갖추면 된다. 그렇지 못한 경우 인덱스 스캔 혹은 별도의 정렬 과정을 통해 처리된다.
WHERE 조건절이 있지만 검색을 위해 인덱스를 사용하지 못하는 경우
GROUP BY를 위해 인덱스를 읽은 후, WHERE 조건 비교를 위해 데이터 레코드를 읽어야만 하므로 루스 인덱스 스캔을 이용할 수 없으며, 타이트 인덱스 스캔 과정을 통해 처리된다.
WHERE 조건절이 있으며, 검색을 위해 인덱스를 사용하는 경우
WHERE 절의 조건과 GROUP BY 처리가 똑같은 인덱스를 공통으로 사용할 수 있을 때만 루스 인덱스 스캔을 사용할 수 있다.

Using index for skip scan

MySQL 옵티마이저가 인덱스 스킵 스캔 최적화를 사용하면 Extra 칼럼에 "Using index for skip scan" 메시지를 표시한다.

Using join buffer(Block Nested Loop), Using join buffer(Batched Key Access), Using join buffer(hash join)

MySQL 옵티마이저는 조인되는 두 테이블에 있는 각 칼럼에서 인덱스를 조사해 인덱스가 없는 테이블을 먼저 읽어 조인을 실행한다. 뒤에 읽는 테이블(드리븐 테이블)은 검색 위주로 사용되기 때문에 인덱스가 없으면 성능에 미치는 영향이 매우 크기 때문이다.

드리븐 테이블에 검색을 위한 적절한 인덱스가 없을 때 MySQL 서버는 블록 네스티드 루프나 해시 조인을 사용하며, 이는 조인 버퍼를 사용한다. 이처럼 실행 계획에서 조인 버퍼가 사용되는 실행 계획의 Extra 칼럼에는 "Using join buffer"라는 메시지와 함께 사용된 조인 알고리즘이 추가로 표시된다.

Using MRR

MySQL 옵티마이저가 MRR 최적화를 사용한 경우 Extra 칼럼에 "Using MRR" 문구가 표시된다.

Using sort_union(...), Using union(...), Using intersect(...)

쿼리가 index_merge 접근 방법으로 실행되는 경우 2개 이상의 인덱스가 동시에 사용될 수 있다. 이때 실행 계획의 Extra 칼럼에는 두 인덱스로부터 읽은 결과를 어떻게 병합했는지 설명하기 위해 다음 3개 중 하나의 메시지를 출력한다.

Using intersect(...)
각각의 인덱스를 사용할 수 있는 조건이 AND로 연결된 경우 각 처리 결과에서 교집합을 추출해내는 작업을 수행했다는 의미다.
Using union(...)
각 인덱스를 사용할 수 있는 조건이 OR로 연결된 경우 각 처리 결과에서 합집합을 추출해내는 작업을 수행했다는 의미다.
Using sort_union(...)
Using union과 같은 작업을 수행하지만 sort_union은 프라이머리 키만 먼저 읽어서 정렬하고 병합한 이후 레코드를 읽어서 반환한다. 상대적으로 대량의 range 조건들이 OR로 연결되어 Using union으로 처리될 수 없는 경우 사용한다.

인덱스를 사용할 수 있는 조건이 OR 연산자로 연결되었을 때, 실제로는 레코드 건수와 관계없이 WHERE 조건에 사용된 비교 조건이 모두 동등 조건이라면 Using union(), 그렇지 않다면 Using sort_union()이 사용된다.

Using temporary

MySQL 서버에서 쿼리를 처리하는 동안 중간 결과를 담아 두기 위해 임시 테이블(Temporary table)을 사용할 수 있다. 이는 메모리 혹은 디스크에서 생성될 수 있다.

쿼리의 실행 계획에서 Extra 칼럼에 "Using temporary" 키워드가 표시되면 임시 테이블을 사용한 것으로, 임시 테이블의 생성 위치는 실행 계획만으로 판단할 수 없다.

Using where

MySQL 서버는 내부적으로 크게 MySQL 엔진과 스토리지 엔진이라는 두 개의 레이어로 나눠 볼 수 있다. 각 스토리지 엔진은 디스크나 메모리 상에서 필요한 레코드를 읽거나 저장하는 역할을, MySQL 엔진은 스토리지 엔진으로부터 받은 레코드를 가공 또는 연산하는 작업을 수행한다.

MySQL 엔진 레이어에서 별도의 가공을 해서 필터링 작업을 처리한 경우에 Extra 칼럼에 "Using where" 코멘트가 표시된다.

"Using where"가 성능상의 문제를 야기할지 적절히 선별하는 능력이 필요한데, 실행 계획의 filtered 칼럼을 통해 이슈 여부를 확인할 수 있다.

Zero limit

MySQL 서버에서 데이터 값이 아닌 쿼리 결괏값의 메타 데이터만 필요한 경우가 존재한다.

위 경우 쿼리 마지막에 LIMIT 0을 사용하면 되는데, MySQL 옵티마이저는 사용자의 의도를 알아채고 실제 테이블의 레코드는 전혀 읽지 않고 결괏값의 메타 정보만 반환한다. 이 경우 실행 계획의 Extra 칼럼에는 "Zero limit" 메시지가 출력된다.

유혁

백엔드 개발자

이전 포스트

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

Real MySQL 8.0

10.3.12 `Extra` 칼럼

const row not found

Deleting all rows

Distinct

FirstMatch

Full scan on NULL key

Impossible HAVING

Impossible WHERE

LooseScan

No matching min/max row

no matching row in const table

No matching rows after partition pruning

No tables used

Not exists

Plan isn't ready yet

Range checked for each record (index map: N)

Recursive

Rematerialize

Select tables optimized away

Start temporary, End temporary

unique row not found

Using filesort

Using index(커버링 인덱스)

Using index condition

Using index for group-by

타이트 인덱스 스캔(인덱스 스캔)을 통한 `GROUP BY` 처리

루스 인덱스 스캔을 통한 `GROUP BY` 처리

Using index for skip scan

Using join buffer(Block Nested Loop), Using join buffer(Batched Key Access), Using join buffer(hash join)

Using MRR

Using sort_union(...), Using union(...), Using intersect(...)

Using temporary

Using where

Zero limit

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

0개의 댓글

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

Real MySQL 8.0

10.3.12 Extra 칼럼

const row not found

Deleting all rows

Distinct

FirstMatch

Full scan on NULL key

Impossible HAVING

Impossible WHERE

LooseScan

No matching min/max row

no matching row in const table

No matching rows after partition pruning

No tables used

Not exists

Plan isn't ready yet

Range checked for each record (index map: N)

Recursive

Rematerialize

Select tables optimized away

Start temporary, End temporary

unique row not found

Using filesort

Using index(커버링 인덱스)

Using index condition

Using index for group-by

타이트 인덱스 스캔(인덱스 스캔)을 통한 GROUP BY 처리

루스 인덱스 스캔을 통한 GROUP BY 처리

Using index for skip scan

Using join buffer(Block Nested Loop), Using join buffer(Batched Key Access), Using join buffer(hash join)

Using MRR

Using sort_union(...), Using union(...), Using intersect(...)

Using temporary

Using where

Zero limit

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

0개의 댓글

10.3.12 `Extra` 칼럼

타이트 인덱스 스캔(인덱스 스캔)을 통한 `GROUP BY` 처리

루스 인덱스 스캔을 통한 `GROUP BY` 처리