'실행계획에서 무엇을 중요하게 볼 것인가?' - 개인적인 통찰

Real MySQL 8.0 10장 실행계획을 150만개의 더미 데이터를 활용해 공부하며 작성한 글입니다.
"실행 계획을 어떻게 읽어야 하는지, 무엇을 읽어야 하는지?" 에 대해 제 개인적인 의견이 들어갔음을 밝히며, 부족한 부분이 있음을 미리 양해 구합니다.

1. 인덱스를 설정할 때 고려하는 기준

저는 인덱스를 설정할 컬럼을 선택할 때 다음 3가지 기준을 주로 고려합니다.

* 추가로, 인덱스 설정 시 주의할 점들은 해당 목차 범위를 벗어남으로 생략하겠습니다!

 

ⓐ 카디널리티(Cardinality)가 높은 컬럼인가?

• 카디널리티는 해당 컬럼의 고유 값 수를 의미합니다.
• InnoDB 가 레코드를 읽을 때, 카디널리티가 높을수록 빠르게 레코드를 검색할 수 있습니다. 따라서, 카디널리티가 높은 컬럼은 인덱스를 설정할 때 우선 고려합니다.

ⓑ UPDATE/DELETE 쿼리의 WHERE 절에 자주 사용되는 컬럼인가?

• UPDATE나 DELETE 쿼리에서 WHERE 절에 사용되는 컬럼에 인덱스가 없으면 InnoDB가 레코드를 찾으면서 불필요한 레코드에 배타 락이 걸게 됩니다.
• 따라서, WHERE 절에서 조건으로 사용되는 컬럼이라면 인덱스를 설정하여 불필요한 레코드에 락이 걸리는 것을 방지해야 합니다.

ⓒ 멀티 컬럼 인덱스 설정 시, 동등 조건으로 비교되는 컬럼을 앞 순서에 설정합니다.

• 멀티 컬럼 인덱스를 설정할 때는 동등 조건(= 비교, IN절)으로 자주 비교되는 컬럼을 앞 순서에 설정하는 것이 중요합니다.
• 예를 들어, 멀티 컬럼 인덱스이 WHERE 절의 조건으로 사용될 경우, 동등 조건에서 인덱스는 범위 결정 조건으로 작동합니다. 반면, 범위 조건이 먼저 나오고 그 이후에 동등 조건이 오는 경우, 동등 조건은 단순한 필터링 역할에 그치게 됩니다.

이처럼 인덱스를 설정 할 때 여러 기준을 고려하지만, 실제 쿼리 성능은 실행 계획을 통해 직접 확인해야 합니다.
실행 계획을 분석하면 설정한 인덱스가 제대로 활용되는지, 쿼리가 최적화되어 있는지를 명확하게 알 수 있기 때문입니다.

 

2. 실험 데이터 생성 및 실험 소개

이러한 이유로 실행 계획에 대해 공부하게 되었고, 실제 실행 계획을 읽고 해석하는 방법, 그리고 실행 계획에서 무엇을 중점적으로 봐야 하는지에 대해 더미 데이터를 활용하여 직접 분석했고, 제가 느낀 점과 깨달은 내용을 공유하고자 합니다.

 

테스트를 위해 letter 테이블에는 약 150만 건의 레코드를, reply 테이블에는 약 10만 건의 레코드를 더미 데이터로 생성하여 실험을 진행했습니다.

letter 테이블의 레코드 개수

reply 테이블의 레코드 개수

 

조회 대상은 특정 유저가 지난 한 달 동안 작성한 편지 중 발행된 편지이며, 결과는 생성일(created_at) 기준으로 내림차순 정렬하여 최신 3개의 레코드만 가져옵니다.

SELECT l.*
FROM letter l
JOIN reply r ON l.letter_id = r.letter_id
WHERE l.created_at BETWEEN '2024-10-01 00:00:18.000000' AND '2024-11-01 00:00:18.000000'
    AND l.published = true 
    AND l.user_id = UUID_TO_BIN('6f296420-23c1-4e0d-a9cb-a7cb85b1c76c')
ORDER BY l.created_at DESC
LIMIT 3;

 

 

MySQL의 옵티마이저는 통계 정보를 바탕으로 실행 계획을 수립합니다. 옵티마이저가 변경된 데이터의 최신 통계 정보를 알 수 있도록 두 테이블의 통계 정보를 최신화 해주겠습니다.

ANALYZE TABLE letter;
ANALYZE TABLE reply;

 

이제 EXPLAIN 명령어를 통해 실행 계획을 확인하고, 각 컬럼이 의미하는 바를 해석하면서 쿼리를 최적화할 방법을 고민해 보겠습니다.

테이블 형태의 실행 계획 결과

 

실행 계획을 공부한 결과, 실행 계획에서 중요하게 봐야하는 컬럼은 type / key / key_len / rows / filtered / Extra 입니다. 그 이유에 대해 설명해보겠습니다.

3. Table 형태의 실행 계획 분석

▶ type 컬럼: 각 테이블의 레코드를 어떤 방식으로 읽었는지

• letter 테이블 (table 에서 l) : ref
  • ref 는 조인의 순서와 인덱스의 종류에 관계없이 동등 조건으로 검색했다는 것으로 효율적입니다.
• reply 테이블 (table 에서 r) : eq_ref
  • eq_ref 는 조인되는 쿼리의 실행 계획에서만 표시되며, 첫 번째 읽은 테이블의 컬럼 값을 이용해 두 번째 테이블을 PK나 유니크 키로 동등 조건 검색할 때 표시됩니다.
  • eq_ref 에서 두 번째 테이블은 반드시 1건의 레코드만 반환되므로 매우 효율적입니다.

Real MySQL 에서는 일반적으로 다음과 같은 순으로 성능이 빠르다고 합니다.(뒤로 갈수록 느려짐)

const → eq_ref → ref → fulltext → ref_or_null → unique_subquery → index_subquery → range → index_merge → index → ALL

 

여기서, 가장 마지막에 있는 ALL 이 인덱스를 활용하지 못한 테이블 풀 스캔이며이고, index 가 인덱스 풀 스캔을 의미합니다. 실행 계획이 해당 값을 가진다면 인덱스 및 쿼리 수정이 필요하다고 생각합니다.

 

▶ key 컬럼: 레코드를 읽을 때 사용된 인덱스

MySQL에서는 테이블에 여러 개의 인덱스가 있어도 한 번의 쿼리 실행 시 단 하나의 인덱스만 사용합니다. 그래서, key 컬럼은 항상 1개의 key 만이 표시됩니다.

 

이번 쿼리에서는 다음 인덱스가 사용되었습니다.

• letter 테이블: user_id (외래 키)
• reply 테이블: letter_id (유니크 키이자 외래 키)

key 컬럼을 통해 의도된 인덱스가 쓰였는지 확인할 수 있습니다.

 

▶ key_len 컬럼: 멀티 컬럼 인덱스에서 몇 개의 컬럼이 사용되었는지

• 이번 쿼리에서 사용된 letter 테이블의 FK(user_id)와 reply 테이블의 UK(letter_id)는 UUID 타입으로 16바이트입니다.
• 따라서, key_len 값이 16으로 나타나며, UUID 컬럼 전체가 인덱스 조건으로 사용되었음을 의미합니다.

key_len 컬럼을 통해 멀티 컬럼 인덱스에서 사용되지 않은 컬럼을 구분해서 불필요한 컬럼을 멀티 컬럼 인덱스에서 제거할 수 있습니다.

 

▶ rows 컬럼: InnoDB 가 쿼리를 처리하기 위해 얼마나 많은 행을 읽어야 하는지

• 이번 쿼리에서 옵티마이저는 letter 테이블에서 17개 행을 읽어야 한다고 예측했습니다.

 

▶ filtered 컬럼: MySQL엔진이 InnoDB가 읽은 레코드 중 인덱스를 활용하지 않는 컬럼에 의해 필터링된 레코드의 비율

letter 테이블에서 쿼리를 처리하기 위해 읽어야 할 행의 수(rows)는 17개입니다.

• 이 중 l.created_at 조건과 l.published = true 조건을 통해 필터링되어 reply 테이블에 JOIN 될 행의 개수는 17 * 0.056 인 약 1개로 예측됩니다.

※ rows와 filtered의 중요성

이번 쿼리에서는 rows와 filtered 값이 문제가 없어 보입니다. 그러나 제가 이 두 컬럼을 중요하게 보는 이유는 다음과 같습니다.

• rows 값이 크고, filtered 값이 낮은 경우는 불필요하게 많은 레코드를 읽었다는 의미이기 때문입니다.

예를 들어 rows = 97,000, filtered = 10 이 나왔다고 가정합니다.

• InnoDB는 쿼리를 처리하기 위해 97,000건의 레코드를 읽었습니다.
• 그러나 WHERE 조건을 통해 필터링된 레코드는 9,700건(97,000 * 0.1)에 불과합니다.

즉, 약 87,300건의 레코드를 불필요하게 읽은 것이므로, 이 경우에는 쿼리 튜닝 또는 인덱스 수정이 필요하다고 판단할 수 있습니다.

 

▶ Extra 컬럼: 쿼리의 성능과 관련된 추가 정보를 제공

먼저 letter 테이블의 Extra 컬럼을 해석해보겠습니다.

 

Using index condition;

• 이 값은 인덱스에 포함된 컬럼이라면 InnoDB(스토리지 엔진)으로 전달하여 최대한 InnoDB에서 레코드를 걸러서 MySQL 엔진에 주도록 최적화된 기능으로 MySQL 은 5.6 기준으로 추가된 기능입니다.
• 즉, 필터링 작업을 MySQL 엔진에서 수행하지 않고, InnoDB에서 처리함으로써 디스크 I/O를 줄이고 성능을 향상시키는 것입니다.

예를 들겠습니다.

// 인덱스
INDEX full_name (last_name, first_name)

// 쿼리
WHERE last_name = "종혁" and first_name LIKE "%안";

처리 과정

• 이 쿼리에서 InnoDB는 last_name = '종혁' 조건을 인덱스(full_name)를 통해 빠르게 검색할 수 있습니다.
• 그러나 first_name LIKE '%안' 조건은 인덱스를 사용할 수 없기 때문에 기존 MySQL 5.5 버전 이하에서는 MySQL 엔진이 직접 필터링을 수행했습니다.
• 이 경우, InnoDB가 반환한 레코드들을 MySQL 엔진이 다시 디스크 I/O를 통해 필터링해야 하므로 비효율적이었습니다.

인덱스 컨디션 푸시다운(MySQL 5.6 이상 최적화)

• 하지만, InnoDB 가 last_name 을 읽기 위해 full_name 인덱스를 읽으며 first_name 도 같이 필터링 하는 것이 더 효율적일 것입니다. 어차피 full_name 인덱스를 읽을테니까요.
• 즉, 인덱스에 포함된 필드라면 InnoDB 로 전달해서 최대한 InnoDB 가 걸러낼 수 있도록 인덱스 컨디션 푸시다운 기능을 이용해서 인덱스 조건을 내리게 됩니다. 이를 통해 디스크I/O 를 줄이게 되었습니다.

 

Using where;

이 값은 MySQL엔진에서 필터링 작업을 처리할 경우 나타납니다. 이 Using where 이 성능상 좋은지는 filtered 컬럼을 같이 보는 것입니다. 여기서는 filtered 가 5%이니, 17(rows) * 0.05 를 곱한 값인 약 1건이 WHERE 조건(필터링 작업)의 결과입니다. 즉, InnoDB 은 불필요하게 레코드를 읽지 않음을 나타냅니다.

 

Using filesort;

이 값은 ORDER BY 가 인덱스를 사용하지 못할 때 표시되며, ORDER BY가 사용된 쿼리에서만 나타납니다. Real MySQL 에서는 ORDER BY를 하는 과정에서 추가 CPU연산과 메모리 사용량, 디스크 입출력을 증가시키기므로 쿼리를 튜닝하거나 인덱스를 생성하는 것이 좋다고 합니다. 

 

결국, Using filesort 는 Extra 컬럼에서 제거해주면 좋을 것 같습니다.

 

4. Table 형태의 실행 계획 분석에 대한 결론

여기까지 테이블 형태의 실행 계획을 보며 옵티마이저가 어떻게 레코드를 읽고, 효율적으로 읽는지 알 수 있었습니다.

• key 와 key_len 을 통해 의도된 인덱스가 쓰이는지
• type 을 통해 인덱스가 어떻게 쓰이는지
• rows 와 filtered 를 통해 불필요한 행을 많이 읽지는 않는지
• Extra 의 추가 정보를 쿼리의 추가적인 최적화가 가능한지

따라서, 테이블 형태의 실행 계획에서는 이 주요 컬럼들을 주의 깊게 분석하는 것이 중요하다고 생각합니다.

 

하지만, 여기서 중요한 점은 실행 계획은 어디까지나 옵티마이저의 추측일 뿐이라는 것입니다. MySQL 옵티마이저가 예상한 쿼리 수행 방식과 실제 실행 시의 성능은 다를 수 있습니다. 따라서, 쿼리가 실제로 어떻게 실행되는지 확인하려면 반드시 EXPLAIN ANALYZE를 사용하여 실제 성능에 미치는 영향을 분석해야 합니다. 이를 통해, filesort가 실제로 많은 부하를 일으키는지 직접 확인해 보겠습니다

 

5. Tree 형태의 실행 계획 분석

1. -> Limit: 3 row(s)  (cost=24.6 rows=3) (actual time=1.27..1.28 rows=3 loops=1)
2.     -> Nested loop inner join  (cost=24.6 rows=20) (actual time=1.27..1.28 rows=3 loops=1)
3.         -> Sort: l.created_at DESC  (cost=17.6 rows=20) (actual time=1.24..1.24 rows=3 loops=1)
4.             -> Filter: ((l.published = true) and (l.created_at between '2024-10-01 00:00:18.000000' and '2024-11-01 00:00:18.000000'))  (cost=17.6 rows=20) (actual time=1.19..1.21 rows=17 loops=1)
5.                 -> Index lookup on l using FK9tgknmmyr2kafs9bvfw5k9n84 (user_id=uuid_to_bin('95c2eeac-62fe-4d8e-9cb5-5f05be6d7df7')), with index condition: (l.user_id = <cache>(uuid_to_bin('95c2eeac-62fe-4d8e-9cb5-5f05be6d7df7')))  (cost=17.6 rows=20) (actual time=1.17..1.18 rows=20 loops=1)
6.         -> Single-row covering index lookup on r using UK6ln872eudt3ku5rk8vlj43f0r (letter_id=l.letter_id)  (cost=1.02 rows=1) (actual time=0.0117..0.0117 rows=1 loops=3)

 

explain analyze 의 TREE 포맷의 실행 순서는 다음 기준으로 읽습니다.

• 들여쓰기가 같은 레벨에서는 상단에 위치한 라인이 먼저 실행
• 들여쓰기가 다른 레벨에서는 가장 안쪽에 위치한 라인이 먼저 실행

따라서, 위 실행 계획은 5 → 4 → 3 → 6 → 2 → 1 순서로 실행됩니다.

 

▶ Tree 포맷의 주요 지표 설명

actual time / rows / loops 에 대한 개념을 6번 라인과 함께 알아보겠습니다.

 

actual time (실제 소요된 시간, ms)

• actual time=0.0117..0.0117 에서 첫 번째 값(0.0117ms)은 첫 번째 레코드를 가져오는데 걸린 평균 시간, 두 번째 값(0.0117ms)은 마지막 레코드를 가져오는데 걸린 평균 시간입니다.

rows (처리한 레코드 건수)

• rows=1 은 해당 라인을 실행하고 처리한 테이블의 평균 레코드 건수

loops (반복 횟수)

• 테이블의 레코드를 찾는 작업이 반복된 횟수입니다. letter_id=l.letter_id 를 통해 레코드를 찾는 작업이 3번 반복되었습니다.

여기서 actual time과 rows를 평균 시간과 평균 레코드 수로 설명한 이유는 loops 값이 1 이상이기 때문입니다.

예를 들어, reply 테이블에서 l.letter_id와 일치하는 레코드를 찾는 작업이 총 3번 반복되었고, 매번 첫 번째 레코드를 가져오는 데 평균 0.0117ms가 소요되었으며, 마지막 레코드를 가져오는 데도 평균 0.0117ms가 걸린 것입니다.

따라서, 반복 실행(loops)이 있을 때는 actual time과 rows 값이 각 반복의 평균을 나타낸다는 점을 이해해야 합니다.

 

▶ Tree 포맷에서 Using filesort 분석

이제 실행 계획의 3번 라인에서 Using filesort 가 발생한 부분을 살펴보겠습니다.

Sort: l.created_at DESC  (cost=12.1 rows=17) (actual time=0.349..0.35 rows=11 loops=1)

 해당 라인을 보니 정렬하는데 0.3ms 가 걸렸습니다. 많은 부하가 발생하지 않았는데요. 그 원인에 대해 분석해봤습니다.

 

쿼리에 ORDER BY 가 사용되면 반드시 3가지 처리 방법 중 하나로 정렬됩니다.

• 인덱스를 사용한 정렬 → Extra 별도 표기 없음
  • 반드시 ORDER BY에 명시된 컬럼이 제일 먼저 읽는 테이블에 속하고, ORDER BY 순서대로 생성된 인덱스가 있어야 합니다.
  • 첫 번째로 읽는 테이블의 컬럼에 대한 WHERE 조건이 있다면, 그 조건과 ORDER BY는 같은 인덱스를 사용해야 합니다.
  • B-Tree 계열이 아닌 R-Tree(공간 인덱스) / 전문 검색 인덱스 / 해시 인덱스에서는 이 방법 사용 불가합니다.
• 조인에서 드라이빙 테이블만 정렬 → Using filesort
  • '인덱스를 사용한 정렬'의 차선책으로 조인에서 첫 번째로 읽히는 테이블의 칼럼만으로 ORDER BY 절을 작성해야 합니다.
• 조인에서 조인 결과를 임시 테이블로 저장 후 정렬 → Using temporary; Using filesort 가 표시
  • 정렬해야 하는 레코드 건수가 가장 많기 때문에 가장 느린 정렬 방법입니다.
  • Using temporary; 는 메모리나 디스크에서 임시테이블을 사용해서 정렬을 수행할 때 나타납니다.

여기서, 저는 2번째 처리 방법인 '조인에서 드라이빙 테이블만 정렬' 이 적용되었습니다. 다행히 최악의 정렬 방법은 아니었습니다. 하지만, 드라이빙 테이블의 결과가 많다면 정렬하는데 시간이 오래 걸릴 것이기 때문에 EXPLAIN ANALYZE 로 실제 쿼리의 실행 시간을 파악하는 것이 중요하다 생각합니다.

6. 결론: 실행 계획에서 무엇을 어떻게 읽어야 하는가?

이번 글에서는 실행 계획을 읽는 방법과 실제 쿼리 실행 결과를 해석하는 방법을 정리해 보았습니다.

간단히 요약하자면, EXPLAIN에서 확인할 주요 컬럼은

• key 와 key_len 을 통해 의도된 인덱스가 쓰이는지
• type 을 통해 인덱스가 어떻게 쓰이는지
• rows 와 filtered 를 통해 불필요한 행을 많이 읽지는 않는지
• Extra 의 추가 정보를 쿼리의 추가적인 최적화가 가능한지

EXPLAIN ANALYZE 의 Tree 형태의 실행 계획을 통해 실제 쿼리가 어느 부분에서 성능이 안나오는지 확인합니다.

 

그리고, 언제 인덱스를 수정하고 쿼리를 튜닝해야 하는지에 대해 다음과 같이 정리할 수 있습니다. 

• type 값이 ALL(테이블 풀 스캔) 일때. (index - 인덱스 풀 스캔일 경우는 고려)
• rows 값이 크고, filtered 값이 낮을 때 (불필요하게 많은 행을 스토리지 엔진이 읽은 것)
• Extra 값이 Using temporary; Using filesort; 함께 등장하면 쿼리의 부하가 높다. 만약, Using filesort 만 나온다면 실제 실행 시간을 보기.

추가적으로 쿼리 튜닝에는 다양한 경우가 존재합니다. 예를 들어, Nested Loop Join이 반복되는 상황에서는 Hash Join을 사용하도록 쿼리를 수정하는 방법도 있습니다.

그러나, 실행 계획을 정확히 읽을 수 있는 것이 쿼리 최적화의 전제 조건이라고 생각합니다. 실행 계획을 해석하지 못한 상태에서 인덱스를 무작정 추가하거나 쿼리를 수정하는 것은 오히려 성능 문제를 악화시킬 수 있기 때문입니다.

 

마지막으로, Extra 컬럼은 매우 다양한 값을 가지기 때문에 모든 값을 미리 외우기보다는 자주 등장하는 값들을 우선적으로 학습하고, 필요할 때마다 새로운 값을 찾아서 익히는 방식이 더 효율적이라고 생각합니다.

 

이번 경험으로 실행 계획을 이론적으로 아는 것이 아니라 실제 적용해보면서 실행 계획에서 무엇을 봐야 하는지, 어떻게 봐야 하는지 정리할 수 있는 경험이었습니다. 이를 통해 컬럼에 인덱스를 걸었을 때, 인덱스가 효율적인지 판단할 수 있는 눈을 가지게 되었다고 생각합니다. 하지만, 쿼리 성능 개선은 인덱스 추가가 아닌 쿼리를 수정하는 것만으로 해결되는 경우도 있기 때문에 앞으로 9장과 11장에 나오는 다양한 최적화 기법을 학습하며, 인덱스를 추가하지 않고도 쿼리를 효율적으로 튜닝하는 방법을 익혀보고자 합니다. 이러한 과정을 통해 쿼리 최적화 역량을 더욱 강화할 수 있도록, 앞으로도 꾸준히 공부하고 정리해 나가겠습니다. 감사합니다.

 

출처

- MySQL (MariaDB) 인덱스 컨디션 푸시다운 

- 10.실행계획 中 Real MySQL 8.0 1권

- Line 기술 블로그 MySQL Workbench의 VISUAL EXPLAIN으로 인덱스 동작 확인하기