[MySQL] EXPLAIN을 이해하여 쿼리 성능 최적화하기 [MySQL 인덱스 타는지 확인하는 방법]

---

- EXPLAIN을 이해하여 쿼리 성능 최적화하기

1. EXPLAIN이란?

EXPLAIN은 MySQL 쿼리의 실행 계획을 분석하는 데 사용되는 명령어이며, 쿼리를 실행하는 방식과 각 단계에서 어떤 작업을 수행하는지 볼 수 있다. (Explain을 사용하면 쿼리의 성능을 평가하고 최적화할 수 있다.)

EXPLAIN SELECT * FROM table_name WHERE condition;

 

위 쿼리를 실행하면 아래와 같이 해당 쿼리의 실행 계획을 출력한다.

Explain을 적용한 쿼리문

---

- EXPLAIN 출력 컬럼 별 의미

• id: 쿼리 내 단계의 순서
• select_type: 쿼리의 유형 (단순 SELECT, UNION 등)
• table: 접근하는 테이블의 이름
• type: 각 테이블에 대한 데이터 액세스 방법 (예: ALL, index 등)
• possible_keys: 쿼리에서 사용할 수 있는 인덱스 목록
• key: 실제로 사용된 인덱스
• key_len: 사용된 인덱스의 길이
• rows: 각 단계에서 검색된 행의 수
• extra: 추가 정보 (예: Using where, Using index 등)

---

2. 실행 계획 분석: type과 type의 우선순위

실행 계획을 분석 시, 중요한 부분 중 하나는 쿼리에서 사용된 type이다.

type은 쿼리가 데이터를 읽는 방법을 설명하며, 쿼리의 성능에 중대한 영향을 미치며, type은 성능에 따라 우선 순위가 지정되어 있다. (type을 통해 index를 타는지 확인할 수 있다.)

---

1. system: 단일 행 쿼리로, 테이블에 단 한 건의 행만을 반환하는 경우. (가장 빠른 액세스 방법)
2. const: 단일 행 쿼리이며, 인덱스를 사용하여 상수 조건으로 특정 행을 찾는 경우. (PK나 UNIQUE 인덱스를 사용한 조건 검색)
3. eq_ref: 각 조인에서 const 타입으로 결합된 모든 테이블에 대해 인덱스를 사용하여 한 개의 행만을 읽는 경우. (이너 조인의 경우에만 발생)
4. ref: 인덱스를 사용하여 단일 테이블에서 여러 행을 읽는 경우. 인덱스는 유니크하지 않아도 되지만, 레인지 조건이 없어야 함.
5. range: 인덱스에서 범위를 스캔하는 경우. 주로 BETWEEN 및 IN() 연산자와 함께 사용
6. index: 인덱스의 전체 범위를 스캔 하는 경우. 일반적으로 인덱스를 읽는 것보다 더 효율적임
7. all: 테이블 전체 스캔 하는 경우. (가장 느린 액세스 방법)

 

type은 성능에 따라 우선 순위가 있으며, system이 가장 빠르고 all이 가장 느리다. 실행 계획을 분석할 때, 가능한 한 빠른 type을 사용하는 것이 중요하다.

---

3. 쿼리 최적화

EXPLAIN의 결과를 분석하여 성능 향상을 위한 조치를 취할 수 있다.
예를 들어 type이 all인 경우, 인덱스를 추가하거나 쿼리를 최적화하여 성능을 향상시킬 수 있다.

• 인덱스 사용: 가능한 경우 적절한 인덱스를 만들어 쿼리 성능을 향상시킨다.
• 조인 최적화: 조인 순서를 변경하거나 조인 유형을 조정하여 성능을 최적화한다.
• 부하 분산: 쿼리의 부하를 분산하여 전체 시스템 성능을 향상시킨다.

---