[MySQL] EXPLAIN으로 쿼리 성능 분석하기

데이터베이스 쿼리의 성능은 서비스 속도와 직결되는 중요한 요소입니다!

MySQL의 `EXPLAIN`은 쿼리 실행 계획을 확인하고 병목이 되는 부분을 찾아 최적화할 수 있게 도와주는 도구입니다.

데이터가 많은 테이블에서 비효율적인 쿼리가 실행되면 성능이 급격히 저하되어 응답 시간이 길어지고 시스템 리소스가 낭비될 수 있습니다. 특히 고트래픽 환경에서는 작은 쿼리 최적화가 큰 차이를 만들 수 있습니다.

 

이 글에서는 `EXPLAIN`의 필드와 활용 방법을 알아보겠습니다. 👀

 

 

EXPLAIN 사용법 

`EXPLANIN`을 사용하려면 원하는 쿼리 앞에 `EXPLANIN`을 붙이기만 하면 됩니다.

EXPLAIN
SELECT e.employee_id, e.first_name, d.department_name 
FROM local_test.employees e 
JOIN local_test.departments d ON e.department_id = d.department_id 
WHERE e.salary > 5000;

위의 예처럼 사용하면, `employees` 테이블에서 특정 조건을 만족하는 데이터를 검색할 때 MySQL이 어떤 방식으로 쿼리를 실행하는지 알 수 있습니다.

 

EXPLAIN 결과 필드 설명

결과 분석

• id
  각 쿼리 단계의 고유 식별자입니다. 값이 클수록 쿼리 실행 순서가 뒤에 위치합니다.
• select_type
  쿼리의 유형을 나타내며 주로 주로 `SIMPLE`, `PRIMARY`, `SUBQUERY` 등이 사용됩니다.
  • SIMPLE: 하위 쿼리를 포함하지 않은 간단한 쿼리.
  • PRIMARY: 가장 바깥쪽의 메인 쿼리.
  • SUBQUERY: SELECT 절 안에 포함된 하위 쿼리.
  • DEPENDENT SUBQUERY: 상위 쿼리의 값을 참조하는 하위 쿼리.
• table
  쿼리가 참조하는 테이블입니다.
• type
  조인 방식으로 쿼리 성능에 큰 영향을 미칩니다.
• possible_keys
  쿼리에서 사용 가능한 인덱스 목록입니다.
  (여기에 인덱스가 나타나지 않으면 해당 컬럼에 적절한 인덱스를 추가하는 것을 고려해야 합니다.)
• key
  실제로 사용된 인덱스 목록입니다.
• key_len
  사용된 인덱스의 길이를 나타내며, 인덱스의 효율성을 판단할 수 있습니다.
• ref
  조인 조건에서 어떤 컬럼이나 상수가 사용되는지 나타냅니다.
• rows
  MySQL이 해당 단계에서 조회할 것으로 예상하는 행 수입니다.
  (이 값이 클수록 성능이 저하될 가능성이 큽니다. 필요한 인덱스를 추가해 조회 범위를 줄이는 것이 좋습니다!)
• Extra
  추가 정보로, 쿼리 최적화에 중요한 힌트를 제공합니다.
  
  • Using where: 조건 필터링이 이루어졌음을 의미합니다. 인덱스가 적절히 활용되면 성능에 긍정적입니다.
  • Using index: 인덱스만으로 쿼리가 해결될 때 나타납니다. 성능이 우수하지만, 너무 많은 컬럼이 포함된 인덱스는 주의가 필요합니다.
  • Using temporary: 쿼리 결과를 임시 테이블에 저장할 때 나타나며, 디스크 I/O를 증가시켜 성능 저하를 일으킬 수 있습니다.
  • Using filesort: 정렬 작업을 위해 추가적인 정렬 알고리즘이 필요할 때 나타납니다. ORDER BY나 GROUP BY 절에서 발생할 수 있으며, 성능에 영향을 줄 수 있습니다.

 

type에 나올 수 있는 값

`type`은 쿼리 성능에 큰 영향을 미치며 값에 따라 쿼리의 효율성을 파악할 수 있습니다.

가장 성능이 좋은 `const`부터 성능이 낮은 `ALL`까지 다양한 옵션이 있습니다.

 

• system: 테이블이 한 행만 가지고 있을 때 사용됩니다. 매우 빠릅니다.
• const: 기본 키나 유니크 인덱스를 사용하여 단 한 행만 조회하는 경우입니다.
• eq_ref: 조인에서 기본 키나 유니크 인덱스를 사용하여 각 행을 정확히 하나의 행과 매칭할 때 사용됩니다.
• ref: 조인에서 인덱스를 사용하여 다수의 행을 참조할 때 사용됩니다.
• fulltext: FULLTEXT 인덱스를 사용한 검색에 사용됩니다.
• ref_or_null: ref와 비슷하지만 NULL 값도 함께 조회할 때 사용됩니다.
• index_merge: 두 개 이상의 인덱스를 병합하여 사용할 때 사용됩니다.
• range: 인덱스 범위를 지정하여 특정 범위의 행을 조회할 때 사용됩니다. BETWEEN, <, >, IN 등의 조건을 포함합니다.
• index: 테이블의 인덱스 전체를 스캔할 때 사용됩니다.
• ALL: 테이블의 전체 행을 스캔하는 방식으로, 성능상 가장 비효율적입니다.

const, eq_ref, ref, range 값에 가까울수록 효율적입니다. ALL이나 index 유형이 나오면 성능이 저하될 수 있으므로 필요한 인덱스를 추가하거나 쿼리 조건을 개선하는 것이 좋습니다.

 

 

 

💡 최적화가 필요한 쿼리에서 `EXPLAIN`을 활용해 성능을 개선하고 시스템 리소스를 효율적으로 사용해야 합니다!_!