MySQL EXPLAIN 실행 계획 주요 컬럼 의미와 성능 최적화 고려사항

MySQL 8.4 버전 기준, EXPLAIN 명령어는 SELECT, DELETE, INSERT, REPLACE, UPDATE 문에 대해 실행 계획을 보여준다. 각 테이블별로 한 행씩 결과를 반환하며, 주요 컬럼들의 의미와 성능 최적화 시 고려사항은 다음과 같다.

주요 컬럼 의미

• type (access_type): 조인 유형을 나타내며, 성능에 큰 영향을 미친다. 조인 유형은 최적에서 최악 순으로 system, const, eq_ref, ref, fulltext, ref_or_null, index_merge, unique_subquery, index_subquery, range, index, ALL 등이 있다. 예를 들어, const는 상수 하나를 읽는 매우 빠른 유형이고, ALL은 전체 테이블 스캔으로 성능에 부정적이다.
• possible_keys: MySQL이 해당 테이블에서 사용할 수 있는 인덱스 목록을 보여준다. NULL이면 적절한 인덱스가 없음을 의미하며, 이 경우 WHERE 절을 검토하여 인덱스 생성이 필요할 수 있다.
• key: 실제로 MySQL이 선택하여 사용한 인덱스 이름이다. possible_keys에 없지만 key에 나타날 수 있는데, 이는 선택된 인덱스가 커버링 인덱스 역할을 하여 효율적인 인덱스 스캔을 수행하기 때문이다. NULL이면 인덱스를 사용하지 않은 것이다.
• rows: MySQL이 해당 테이블에서 검사할 것으로 추정하는 행 수이다. InnoDB의 경우 추정치이며 정확하지 않을 수 있다.
• filtered: 테이블 조건에 의해 필터링되는 행의 예상 백분율이다. 100이면 필터링 없음, 낮을수록 필터링이 많음을 의미한다. 실제로 다음 테이블과 조인되는 행 수는 rows × (filtered/100)이다.

성능 최적화 시 고려사항

• 조인 유형(type) 최적화: 가능한 한 system, const, eq_ref, ref 유형을 사용하도록 쿼리와 인덱스를 설계한다. ALL(전체 테이블 스캔)은 피하는 것이 좋다.
• 적절한 인덱스 생성: possible_keys가 NULL일 경우, WHERE 절 조건에 적합한 인덱스를 생성하여 쿼리 성능을 개선할 수 있다.
• 커버링 인덱스 활용: key 컬럼에 나타난 인덱스가 커버링 인덱스인지 확인하여, 인덱스만으로 데이터를 조회할 수 있게 하면 디스크 I/O를 줄일 수 있다.
• rows와 filtered 확인: rows 값이 크고 filtered 값이 높으면 많은 행을 처리하므로, 조건절을 개선하거나 인덱스를 추가하여 처리할 행 수를 줄이는 것이 중요하다.

이와 같이 EXPLAIN의 주요 컬럼을 이해하고, 조인 유형과 인덱스 사용 현황을 분석하여 적절한 인덱스 설계 및 쿼리 튜닝을 수행하면 MySQL 쿼리 성능을 효과적으로 최적화할 수 있다.

참고 자료

• 10.8.2 EXPLAIN Output Format