IT

MySQL 쿼리 성능 최적화 기법 - EXPLAIN

lilililililiiii 2025. 5. 4. 20:46

효율적인 인덱스 설계는 쿼리 성능 향상의 기반이지만, 그것만으로는 충분하지 않을 수 있습니다. 작성된 SQL 쿼리가 실제로 데이터베이스 내부에서 어떻게 처리되는지 이해하고, 병목 지점을 찾아 개선하는 과정이 필수적입니다. 이때 가장 강력하고 기본적인 도구가 바로 EXPLAIN 명령어입니다.

EXPLAIN은 MySQL 옵티마이저가 특정 SQL 문(주로 SELECT, INSERT, UPDATE, DELETE, REPLACE)을 어떻게 실행할 것인지에 대한 실행 계획(Execution Plan)을 보여줍니다. 개발자와 DBA는 이 실행 계획을 분석하여 쿼리가 인덱스를 제대로 활용하는지, 불필요한 작업을 수행하지는 않는지, 어떤 방식으로 테이블에 접근하고 조인하는지 등을 파악하고 성능 개선의 단서를 얻을 수 있습니다.

EXPLAIN의 사용법부터 시작하여, 실행 계획 결과로 나타나는 각 컬럼(id, select_type, table, type, key, rows, Extra 등)의 의미를 심층적으로 분석하고, 이를 통해 쿼리가 인덱스를 효과적으로 활용하는지, 불필요한 작업(Full Table Scan, Filesort 등)을 수행하지는 않는지 등을 파악하여 실질적인 성능 튜닝으로 이어갈 수 있는 방법을 자세히 알아보고자 합니다.

 

EXPLAIN 실행 계획 상세 분석 방법

EXPLAIN 사용법

EXPLAIN은 분석하고자 하는 SQL 문 앞에 붙여서 사용합니다.

EXPLAIN SELECT c.customer_id, c.first_name, COUNT(o.order_id) AS order_count
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE c.city = 'Seoul'
GROUP BY c.customer_id
ORDER BY order_count DESC
LIMIT 10;

MySQL 8.0.18부터는 EXPLAIN ANALYZE를 사용하여 실제 쿼리를 실행하고 각 단계별 예상 비용 및 실제 실행 시간, 반환된 행 수 등의 추가 정보를 얻을 수도 있습니다. 이는 예상 실행 계획과 실제 실행 간의 차이를 비교하는 데 유용합니다.

EXPLAIN ANALYZE SELECT ... ; -- 실제 쿼리가 실행되므로 주의 필요

EXPLAIN 결과 컬럼 해부

EXPLAIN의 결과는 테이블 형태로 여러 컬럼 정보를 보여줍니다. 각 컬럼의 의미를 정확히 이해하는 것이 중요합니다.

  1. id: 실행 계획의 각 단계를 식별하는 번호입니다.
    • 일반적으로 SELECT 쿼리 하나당 하나의 id가 부여됩니다.
    • 값이 같으면 동일한 실행 단계에 속하며, 위에서 아래로 함께 실행됩니다.
    • 값이 다르면 번호가 높은 단계부터 먼저 실행됩니다 (예: 서브쿼리).
    • UNION의 경우, 각 SELECT 문과 최종 결과를 합치는 UNION RESULT에 별도의 id가 부여될 수 있습니다.
  2. select_type: 해당 SELECT 쿼리의 유형을 나타냅니다.
    • SIMPLE: UNION이나 서브쿼리가 없는 단순 SELECT.
    • PRIMARY: 가장 바깥쪽(Outer) SELECT (UNION이나 서브쿼리 포함 시).
    • SUBQUERY: FROM 절 외의 위치(예: SELECT 절, WHERE 절)에 있는 서브쿼리.
    • DERIVED: FROM 절에 사용된 서브쿼리 (임시 테이블 생성).
    • UNION: UNION 구문의 두 번째 이후 SELECT.
    • UNION RESULT: UNION 결과를 합치는 단계.
    • DEPENDENT SUBQUERY: 바깥쪽 쿼리에 의존하는 서브쿼리 (성능 저하 가능성 높음).
    • DEPENDENT UNION: UNION 구문에서 두 번째 이후 SELECT가 바깥쪽 쿼리에 의존.
    • MATERIALIZED: 서브쿼리가 최적화되어 내부 임시 테이블로 구체화(Materialize)된 경우.
  3. table: 해당 단계에서 접근하는 테이블의 이름 또는 별칭(Alias)입니다.
    • <derivedN>: id가 N인 파생 테이블(Derived Table).
    • <unionN,M>: id가 N과 M인 SELECT 결과를 합친 UNION 결과.
    • <subqueryN>: id가 N인 구체화된 서브쿼리 결과.
  4. partitions (MySQL 5.7+): 쿼리가 접근하는 파티션의 목록입니다. 파티셔닝된 테이블에서 성능 분석 시 유용합니다.
  5. type: (매우 중요) 테이블에 어떻게 접근하는지(Join Type)를 나타냅니다. 성능에 가장 큰 영향을 미치는 지표 중 하나이며, 아래로 갈수록 성능이 좋습니다.
    • system: 테이블에 행이 1개뿐인 경우 (MyISAM의 특수한 경우). 가장 빠름.
    • const: Primary Key 또는 Unique Index를 사용하여 정확히 하나의 행만 일치하는 경우. 매우 빠름.
    • eq_ref: 조인 시 Primary Key 또는 Unique NOT NULL Index를 사용하여 이전 테이블로부터 전달받은 값으로 정확히 하나의 행만 일치하는 경우. 빠름.
    • ref: 조인 시 Key가 Primary Key나 Unique Index가 아니거나(= 조건 사용), Unique Index라도 여러 행과 일치할 수 있는 경우 (예: NULL 허용 Unique Index). eq_ref보다 느림.
    • fulltext: 전문(Full-Text) 인덱스를 사용하여 검색.
    • ref_or_null: ref와 유사하지만, NULL 값에 대한 추가 검색이 있는 경우.
    • index_merge: 인덱스 머지 최적화가 사용된 경우.
    • unique_subquery: IN 절의 서브쿼리에서 Primary Key나 Unique Index를 사용하는 경우.
    • index_subquery: IN 절의 서브쿼리에서 중복되지 않는 인덱스를 사용하는 경우.
    • range: 인덱스를 사용하여 특정 범위(Range)의 행을 검색하는 경우 (예: <, >, <=, >=, BETWEEN, IN, LIKE 'prefix%').
    • index: 인덱스 전체를 스캔하는 경우 (Index Full Scan). 테이블 전체를 스캔하는 것보다는 빠르지만, 인덱스 크기가 크면 느릴 수 있습니다. 커버링 인덱스일 때 Extra에 Using index가 표시됩니다.
    • ALL: 테이블 전체를 스캔하는 경우 (Full Table Scan). 가장 느린 방식이며, 대용량 테이블에서는 반드시 피해야 합니다. 인덱스를 사용하지 못하거나, 옵티마이저가 테이블 스캔이 더 빠르다고 판단한 경우 발생합니다.
  6. possible_keys: 쿼리에서 사용할 수 있었던 인덱스 후보 목록입니다.
  7. key: 옵티마이저가 실제로 사용하기로 결정한 인덱스입니다. NULL이면 인덱스를 사용하지 않았음을 의미합니다.
  8. key_len: 사용된 인덱스의 길이(바이트 단위)입니다. 복합 인덱스의 경우, 이 길이를 통해 인덱스의 몇 번째 컬럼까지 활용되었는지 추측할 수 있습니다. 짧을수록 좋습니다.
  9. ref: type이 const, eq_ref, ref, ref_or_null, unique_subquery, index_subquery일 때, 인덱스 검색 조건으로 사용된 컬럼이나 상수를 나타냅니다. 조인 조건이나 상수 값을 확인할 수 있습니다.
  10. rows: 옵티마이저가 쿼리를 처리하기 위해 읽을 것으로 예측하는 행의 수입니다. 실제 결과 행 수가 아니라 처리 과정에서 접근해야 할 행의 추정치이며, 정확하지 않을 수 있지만 비용 예측에 중요한 참고 자료가 됩니다. 이 값이 너무 크면 성능 문제를 의심해 볼 수 있습니다.
  11. filtered (MySQL 5.1+): rows에서 읽은 행 중에서 테이블 조건(WHERE 절 등)에 의해 필터링되고 남을 것으로 예측되는 행의 비율(%)입니다. 예를 들어 rows가 1000이고 filtered가 10.00이면, 1000개의 행을 읽어서 100개(1000 * 10%) 정도가 다음 단계로 전달될 것으로 예측한다는 의미입니다. 조인 성능 예측에 유용합니다.
  12. Extra: (매우 중요) 쿼리 실행에 대한 추가적인 중요 정보들을 담고 있습니다. 여러 정보가 함께 표시될 수 있습니다.
    • Using index: (좋음) 커버링 인덱스를 사용하여 테이블 접근 없이 인덱스만으로 데이터를 반환했음을 의미합니다. (매우 효율적)
    • Using where: 스토리지 엔진에서 데이터를 읽어온 후, MySQL 서버 레벨에서 WHERE 절 조건으로 필터링을 수행했음을 의미합니다. (일반적으로 발생)
    • Using index condition: (Index Condition Pushdown, ICP) 인덱스를 사용하여 행을 찾은 후, 테이블 접근 전에 인덱스에 포함된 다른 컬럼 조건을 먼저 필터링하여 불필요한 테이블 접근을 줄였음을 의미합니다. (성능 개선)
    • Using temporary: (주의 필요) 쿼리 처리 중 중간 결과를 저장하기 위해 내부 임시 테이블을 사용했음을 의미합니다. ORDER BY와 GROUP BY 컬럼이 다르거나, UNION 등에서 발생할 수 있습니다. 메모리 임시 테이블이면 비교적 괜찮지만, 디스크 기반 임시 테이블이 사용되면 성능이 크게 저하될 수 있습니다.
    • Using filesort: (주의 필요) ORDER BY 처리를 위해 인덱스를 사용하지 못하고, 별도의 정렬 작업을 수행했음을 의미합니다. 데이터 양이 많으면 매우 느려질 수 있습니다. 정렬 대상 컬럼에 인덱스를 추가하거나 쿼리를 변경하여 개선할 수 있습니다.
    • Using join buffer (Block Nested Loop), Using join buffer (Batched Key Access), Using join buffer (Hash Join): 조인 시 인덱스를 효율적으로 사용하지 못하여 조인 버퍼를 사용했음을 의미합니다. 조인 컬럼에 인덱스가 없거나 타입이 맞지 않는 경우 발생할 수 있습니다. (성능 저하 가능성)
    • Select tables optimized away: MIN(), MAX() 함수 등을 사용할 때 인덱스만으로 결과를 바로 얻을 수 있거나, COUNT(*) 쿼리에서 WHERE 절이 없는 경우 등 테이블 접근 없이 결과를 얻었음을 의미합니다. (매우 효율적)
    • Impossible WHERE: WHERE 절 조건이 항상 거짓(False)이 되는 경우. (쿼리 오류 가능성)

실행 계획 분석 핵심 포인트

  • type 컬럼 확인: ALL이나 index가 있는지 확인하고, 있다면 range, ref, eq_ref, const 순으로 개선할 방법을 찾습니다.
  • key 컬럼 확인: 적절한 인덱스를 사용하고 있는지, possible_keys에 후보는 있지만 key가 NULL은 아닌지 확인합니다.
  • rows 및 filtered 컬럼 확인: 예상 처리 행 수가 비정상적으로 많은지, 필터링 비율이 너무 낮은지 확인합니다.
  • Extra 컬럼 확인: Using temporary, Using filesort, Using join buffer 등 성능 저하를 유발할 수 있는 메시지가 있는지 집중적으로 확인하고, Using index나 Using index condition과 같은 긍정적인 메시지가 나타나도록 유도합니다.
  • 복잡한 쿼리는 단계별 분석: id 값을 기준으로 실행 순서를 파악하고, 각 단계의 실행 계획을 개별적으로 분석합니다.

EXPLAIN은 MySQL 성능 튜닝의 시작점이자 가장 기본적인 도구입니다. 실행 계획을 꾸준히 분석하는 습관을 들이면 쿼리가 내부적으로 어떻게 동작하는지 이해하고, 잠재적인 성능 병목을 식별하여 개선하는 능력을 키울 수 있습니다.

 

지금까지 MySQL의 EXPLAIN 명령어를 활용하여 쿼리 실행 계획을 분석하는 방법에 대해 상세히 살펴보았습니다. EXPLAIN은 SQL 쿼리가 내부적으로 어떻게 처리될 것인지에 대한 옵티마이저의 청사진을 제공하며, 그 결과에 포함된 id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra 와 같은 다양한 컬럼들은 쿼리 성능을 진단하는 데 결정적인 단서들을 제공합니다.

특히, 테이블 접근 방식(type 컬럼)이 ALL이나 index인지, 예상 처리 행 수(rows)가 과도하게 많은지, 그리고 Extra 컬럼에 Using temporary, Using filesort와 같은 성능 저하 유발 요소가 나타나는지 등을 면밀히 확인함으로써 비효율적인 부분을 식별할 수 있습니다. 반대로 Using index나 Using index condition과 같은 긍정적인 신호를 통해 쿼리가 인덱스를 잘 활용하고 있음을 확인할 수도 있습니다. 더 나아가 MySQL 8.0부터 제공되는 EXPLAIN ANALYZE는 실제 실행 통계를 제공하여 예측과 실제 간의 차이를 파악하는 데 도움을 줍니다.

결론적으로, EXPLAIN은 막연한 추측이 아닌, 데이터에 기반한 과학적인 접근으로 MySQL 성능 튜닝을 시작할 수 있게 하는 가장 기본적이고 필수적인 도구입니다. 실행 계획을 꾸준히 분석하고 이해하려는 노력은 잠재적인 성능 병목 현상을 사전에 감지하고 효과적으로 해결하는 능력을 배양하며, 이는 결국 안정적이고 빠른 데이터베이스 시스템을 구축하고 운영하는 데 핵심적인 역량이 될 것입니다.