MySQL에서 EXPLAIN을 사용하여 실행 계획을 확인 방법

MySQL에서 실행 계획을 분석하여 성능을 평가할 때는, EXPLAIN 또는 EXPLAIN ANALYZE 명령을 통해 출력되는 여러 항목을 살펴보아야 합니다. 각 항목은 쿼리 실행 방식, 데이터 접근 방법, 인덱스 사용 여부 등을 나타내며, 이들을 종합적으로 분석하여 성능을 개선할 수 있는 부분을 찾아낼 수 있습니다.
아래는 실행 계획에서 확인해야 할 주요 항목과 성능을 알 수 있는 방법을 설명합니다:

 

1. id

 

• 의미: 쿼리 내에서 실행되는 단계나 서브쿼리의 식별자입니다.
• 성능 분석 포인트:
  여러 개의 id가 있을 경우, 서브쿼리나 조인 순서를 의미합니다. id 값이 클수록 우선 실행되는 쿼리를 나타냅니다.
  일반적으로 id가 1인 쿼리가 가장 중요한(외부) 쿼리입니다.
  서브쿼리나 복잡한 쿼리가 있으면 id가 다른 값으로 나뉘어 실행됩니다.

2. select_type

 

• 의미: 쿼리의 유형을 나타냅니다. (예: PRIMARY, SUBQUERY, DEPENDENT SUBQUERY, UNION, SIMPLE 등)
• 성능 분석 포인트:
  PRIMARY는 외부 쿼리이고, SIMPLE은 단순한 테이블 조회를 의미합니다.
  DEPENDENT SUBQUERY는 외부 쿼리에 의존적인 서브쿼리로, 성능에 부정적인 영향을 줄 수 있습니다. 가능한 한 의존적인 서브쿼리를 피하거나 조인으로 바꾸는 것이 좋습니다.
  UNION이나 UNION ALL은 결과가 중복을 포함하는지 확인하고, 필요시 DISTINCT를 사용하여 결과를 필터링할 수 있습니다.

3. table

 

• 의미: 쿼리에서 사용되는 테이블의 이름을 나타냅니다.
• 성능 분석 포인트:
  쿼리가 어떤 테이블을 조회하는지 확인하고, 이 테이블에 적절한 인덱스가 사용되고 있는지 살펴봅니다.
  derived가 출력되면 서브쿼리 결과가 임시 테이블로 처리되고 있음을 의미합니다. 이 경우, 임시 테이블의 크기와 효율성도 성능에 영향을 미칩니다.

4. type

 

• 의미: 테이블 접근 방식입니다. 가장 중요한 성능 지표 중 하나로, ALL, index, range, ref, eq_ref, const 등이 있습니다.
• 성능 분석 포인트:
  ALL: 테이블을 전체 스캔하는 방식입니다. 성능에 매우 나쁜 영향을 미칠 수 있습니다. ALL이 표시된다면 인덱스를 추가하거나 쿼리 조건을 개선해야 합니다.
  index: 인덱스를 사용하지만, 전체 인덱스를 스캔합니다. ALL보다는 나쁘지 않지만, 보다 효율적인 인덱스를 활용할 수 있습니다.
  range: 특정 범위의 데이터만 조회합니다. 인덱스가 잘 사용된 경우입니다.
  ref: 다른 테이블의 인덱스를 참조하여 데이터를 찾습니다. 효율적인 접근 방식입니다.
  eq_ref: 주로 조인에서 사용하는 매우 효율적인 접근 방식입니다. 한 행만 일치하는 경우 사용됩니다.
  const: 상수값을 사용해 빠르게 데이터를 찾습니다. 매우 효율적입니다.

5. possible_keys

 

• 의미: 쿼리에서 사용할 수 있는 인덱스를 나타냅니다.
• 성능 분석 포인트:
  이 필드에 표시된 인덱스들 중 실제로 사용되는 인덱스가 key 필드에 나타나야 합니다.
  쿼리가 인덱스를 잘 활용하고 있는지 확인하고, possible_keys가 표시되지 않는다면 인덱스를 추가하거나 쿼리 구조를 개선해야 할 필요가 있습니다.

6. key

 

• 의미: 실제로 사용되는 인덱스입니다.
• 성능 분석 포인트:
  쿼리가 어떤 인덱스를 사용하고 있는지 확인합니다. 사용된 인덱스가 효율적인지, 필요한 모든 컬럼에 대해 인덱스가 적용되는지 확인해야 합니다.
  NULL인 경우 인덱스를 사용하지 않았다는 뜻이므로, 인덱스를 추가하거나 쿼리 구조를 개선해야 할 수 있습니다.

7. key_len

 

• 의미: 사용된 인덱스에서 사용된 키 길이를 나타냅니다.
• 성능 분석 포인트:
  인덱스의 사용 범위를 확인할 수 있습니다. 더 작은 key_len 값은 더 적은 데이터만을 조회하는 것을 의미하며 효율적입니다.

8. ref

 

• 의미: 조인에서 사용되는 조건을 나타냅니다. 예를 들어, 특정 컬럼을 기준으로 다른 테이블을 참조할 때 나타납니다.
• 성능 분석 포인트:
  ref가 const 또는 eq_ref라면 조인 성능이 좋은 경우입니다.
  여러 테이블을 조인할 때 이 필드에 NULL이나 다른 비효율적인 값이 나타나면 성능이 저하될 수 있습니다.

9. rows

 

• 의미: MySQL이 추정한 조회해야 할 행의 수입니다.
• 성능 분석 포인트:
  이 값이 많을수록 성능에 악영향을 미칠 수 있습니다. 인덱스가 잘 활용되면 조회할 행 수가 줄어들어 성능이 향상됩니다.
  실제로 EXPLAIN ANALYZE로 실행했을 때 실제 행 수와 비교하여, 쿼리 최적화가 잘 되었는지 확인합니다.

10. filtered

 

• 의미: 행이 필터링될 확률입니다. 예를 들어, WHERE 절의 조건에 맞는 데이터만 필터링되는 비율을 나타냅니다.
• 성능 분석 포인트:
  값이 낮으면 많은 행이 필터링되지 않고 처리되고 있음을 의미하며, 조건을 최적화하거나 인덱스를 개선해야 할 수 있습니다.

11. Extra

 

• 의미: 쿼리의 추가적인 정보나 경고 메시지입니다. 예를 들어, Using index, Using temporary, Using filesort 등이 있습니다.
• 성능 분석 포인트:
  Using index: 인덱스를 사용하여 쿼리를 실행하고 있다는 긍정적인 신호입니다.
  Using temporary: 임시 테이블을 사용하고 있다는 의미입니다. 임시 테이블이 사용되면 성능이 저하될 수 있습니다.
  Using filesort: 정렬 작업을 위해 filesort를 사용하는 경우입니다. 이는 성능을 크게 저하시킬 수 있으므로, 인덱스를 통해 정렬을 최적화하는 것이 좋습니다.

12. EXPLAIN ANALYZE

 

• 의미: EXPLAIN은 실행 계획만 보여주지만, EXPLAIN ANALYZE는 실제로 쿼리를 실행하여 실제 실행 시간과 행 수 등을 제공합니다.
• 성능 분석 포인트:
  실행 시간이 길고, 예상된 행 수와 실제 행 수가 크게 차이가 나면 인덱스 최적화나 쿼리 리팩토링이 필요할 수 있습니다.

성능 분석 결론
인덱스 사용: key와 possible_keys를 통해 인덱스가 잘 사용되고 있는지 확인합니다.
전체 테이블 스캔: ALL이 나오면, 인덱스를 추가하거나 쿼리 조건을 최적화해야 합니다.
조인 효율성: eq_ref, ref, const 등이 사용되면 효율적이며, dependent나 union 등은 성능 저하를 유발할 수 있습니다.
임시 테이블과 파일정렬: Using temporary, Using filesort 등이 나타나면 성능이 저하될 가능성이 있으므로, 이를 최적화해야 합니다.
실행 계획을 면밀히 분석하여 쿼리의 성능을 최적화할 수 있는 방법을 찾는 것이 중요합니다.