실행 계획 읽기

EXPLAIN 한 줄에서 무엇을 읽어야 하나

쿼리 앞에 EXPLAIN을 붙이면 DB가 그 쿼리를 어떻게 실행할지 세운 계획을 보여 줍니다. 한 줄에 칸이 여러 개인데, type과 key와 rows와 Extra 네 칸만 읽을 줄 알아도 느린 쿼리의 원인을 대부분 찾을 수 있습니다.

실행으로 대조 · MySQL 8.4에서 쉰 가지 조합을 EXPLAIN으로 실행해 type, possible_keys, key, Extra를 대조 (rows는 크기만)

이 실습에 나오는 개념

실행 계획(EXPLAIN): 데이터베이스가 쿼리를 어떤 방법으로 실행할지 정한 계획이다. 어떤 인덱스를 쓰는지, 테이블을 어떤 순서로 조인하는지, 몇 행을 읽을 것으로 예상하는지를 보여 준다. 쿼리 앞에 EXPLAIN을 붙여 확인하며, 쿼리가 느릴 때 원인을 찾는 출발점이다.
EXPLAIN의 type: MySQL의 EXPLAIN 결과에서 그 테이블을 어떤 방법으로 읽는지를 나타내는 칸이다. 읽는 양이 적은 것부터 대략 const(기본 키나 유니크 인덱스로 한 행), eq_ref(조인에서 앞 테이블의 행마다 한 행), ref(유니크가 아닌 인덱스에서 값이 같은 행들), range(인덱스의 한 구간), index(인덱스 전체), ALL(테이블 전체) 순이다. index는 이름과 달리 인덱스를 처음부터 끝까지 읽는다는 뜻이므로 ALL과 마찬가지로 전부 읽는 접근이다. 어떤 값이 나오는지는 옵티마이저가 통계를 보고 정하므로, 같은 쿼리라도 데이터의 양과 분포에 따라 달라질 수 있다.
EXPLAIN의 Extra: MySQL의 EXPLAIN 결과에서 읽는 방법 말고 덧붙여 하는 일을 적어 주는 칸이다. Using where는 읽어 온 행을 조건으로 다시 걸러낸다는 뜻이고, Using index는 테이블에 가지 않고 인덱스만 읽어 끝낸다는 뜻이다. Using filesort는 ORDER BY를 위해 따로 정렬한다는 뜻이며, Using temporary는 GROUP BY 등을 위해 임시 테이블을 만든다는 뜻이다. 비어 있으면 덧붙은 작업이 없다는 것이다. Using filesort와 Using temporary는 읽는 행이 많을 때 느려지는 원인이 되므로, rows 칸의 값과 함께 본다.
풀 테이블 스캔: 조건에 맞는 행을 찾기 위해 테이블의 모든 행을 처음부터 끝까지 읽는 방식이다. 조건에 쓸 인덱스가 없거나, 인덱스가 있어도 조건에 해당하는 행이 너무 많아 전체를 읽는 편이 낫다고 옵티마이저가 판단할 때 선택된다. 큰 테이블에서 소수의 행을 찾는 데 이 방식이 쓰이면 성능 문제의 원인이 된다.
커버링 인덱스: 쿼리가 필요로 하는 모든 컬럼이 인덱스에 포함되어 있어, 테이블의 행을 읽지 않고 인덱스만으로 결과를 만들 수 있는 경우를 말한다. 테이블 접근이 생략되어 조회가 빨라진다. MySQL의 실행 계획에서는 Extra 항목에 Using index로 표시된다.

이렇게 바꿔 보세요

처음 설정으로 — 인덱스가 있는 컬럼인데 함수를 씌웠더니 type이 ALL입니다.
같은 뜻을 LIKE로 쓰면 — LIKE 'user001%'. type이 range로 바뀝니다.
기본 키로 한 행 — type const. 가장 적게 읽는 경우입니다.
앞에 %가 붙은 LIKE, 인덱스 컬럼만 조회 — type index. 이름과 달리 인덱스 전체를 읽습니다.
찾기는 인덱스로, 정렬은 따로 — type ref인데 Extra에 Using filesort가 붙습니다.
GROUP BY — Extra에 Using temporary가 나옵니다.
조인 — 두 줄이 나오고, 둘째 줄의 type이 eq_ref입니다.

처음 설정으로 실행하면

EXPLAIN을 붙여 실행합니다: 쿼리를 실제로 실행하지 않고 실행 계획만 돌려줍니다. 테이블 하나를 읽는 쿼리라 한 줄이 나옵니다. member 테이블은 2만 행이고 이 조건에 실제로 맞는 행은 200행입니다.
type 칸은 ALL입니다: 테이블의 모든 행을 처음부터 끝까지 읽습니다(풀 테이블 스캔). 행이 많은 테이블에서 몇 행만 찾는데 ALL이 나오면 가장 먼저 의심할 곳입니다. name에 인덱스가 있지만 범위를 좁히는 데 쓰지 못했습니다. 인덱스에는 name의 원래 값이 정렬돼 있을 뿐, LEFT(name, 7)을 계산한 결과는 들어 있지 않습니다. 행마다 함수를 계산해 봐야 하므로 읽을 구간을 정할 수 없습니다.
possible_keys 칸과 key 칸: possible_keys는 조건에 쓸 수 있다고 본 인덱스 후보이고, key는 실제로 고른 인덱스입니다. 둘 다 NULL이므로 인덱스를 쓰지 않습니다. name에 인덱스가 있는데도 후보에조차 오르지 못했습니다.
rows 칸은 약 20,000입니다: 읽어야 할 것으로 예상한 행 수입니다. 실제로 세어 본 값이 아니라 통계로 추정한 값이라 조금씩 다를 수 있습니다. 결과는 200행인데 테이블 전체를 읽는다는 뜻입니다. 읽고 나서 버리는 행이 대부분입니다.
Extra 칸의 Using where: 읽어 온 행을 WHERE 조건으로 하나씩 확인해 걸러낸다는 뜻입니다. 인덱스로 범위를 좁히지 못한 조건이 있다는 표시입니다. type이 ALL인데 이 표시가 있으면 "전부 읽은 뒤 조건에 맞지 않는 행을 버린다"는 뜻입니다.

실행 결과

인덱스가 있는 컬럼이라도 함수나 계산을 씌우면 인덱스로 범위를 좁히지 못합니다. 컬럼은 그대로 두고 비교할 값 쪽을 바꿔 쓰는 것이 원칙입니다. 여기서는 name LIKE 'user001%'가 같은 뜻이면서 type range가 됩니다.
읽는 순서: type으로 얼마나 넓게 읽는지 보고, key로 어떤 인덱스를 쓰는지 확인하고, rows로 읽는 양을 가늠한 뒤, Extra에서 걸러내기, 정렬, 임시 테이블 같은 덧붙은 작업을 봅니다.

코드

-- member 테이블: 2만 행
-- 기본 키 id, 인덱스 idx_name (name)
-- age 컬럼에는 인덱스가 없다

EXPLAIN
SELECT *
  FROM member
 WHERE LEFT(name, 7) = 'user001';

이 모형의 전제

쉰 가지 조합 모두, 화면의 SQL을 이 PC의 MySQL 8.4에서 EXPLAIN으로 실행해 type, possible_keys, key, Extra를 글자 그대로 대조했습니다. 테이블에는 member 2만 행(이름 하나에 두 행)과 orders 2만 행을 넣고 ANALYZE TABLE을 한 뒤에 실행했습니다. rows는 통계로 추정하는 값이라 실행할 때마다 조금씩 다르므로(이 PC에서는 전체 스캔일 때 19,700에서 20,400 사이), 1행인지, 일부인지, 테이블 전체에 가까운지만 대조했습니다. 조건에 실제로 맞는 행 수도 같은 데이터에서 세어 확인했습니다. 실행 계획은 옵티마이저가 비용을 계산해 고르는 것이어서, 행 수나 값의 분포가 다르면 같은 쿼리라도 다른 계획이 나올 수 있습니다. 예를 들어 행이 몇 개뿐인 테이블이나 조건에 맞는 행이 아주 많은 경우에는 인덱스가 있어도 ALL이 선택될 수 있습니다(MySQL 문서의 풀 테이블 스캔 설명). Extra에 표시가 없는 이유를 설명한 문장(한 값으로 정해져 있어 정렬이 필요 없다 등)은 MySQL 문서의 ORDER BY 최적화 설명을 따른 풀이입니다. 화면에서는 id, select_type, partitions, key_len, filtered 칸을 뺐습니다.

앞 실습: 복합·커버링 인덱스 · 다음 실습: 조인 알고리즘

이 실습에 나오는 개념

이 실습에서 보는 것

설정

실행 전에 결과 예측하기

코드

그림

실행 비교