TIL #51 Explain

1. 개요

데이터베이스 쿼리 최적화는 애플리케이션 성능을 극대화하는 데 중요한 역할을 한다.
잘못 작성된 쿼리는 성능 저하를 초래할 수 있으며, 특히 대규모 데이터셋을 처리할 때 더욱 그렇다.
MySQL의 EXPLAIN 명령어는 쿼리 실행 계획을 분석하고 최적화할 수 있는 강력한 도구이다.
이번 글에서는 EXPLAIN 명령어의 개념과 사용법, 쿼리 최적화 방법을 살펴보려고 한다.

2. 개념과 필요성

2.1. 개념

EXPLAIN 명령어는 MySQL에서 쿼리 실행 계획을 보여주는 도구이다.
쿼리가 실행될 때 MySQL이 사용하는 접근 방법과 조인 방식, 그리고 인덱스 사용 여부 등을 설명해준다.
이를 통해 쿼리 성능을 분석하고, 비효율적인 부분을 식별하여 최적화할 수 있다.

2.2. 필요성

• 성능 분석 : 쿼리 실행 계획을 통해 성능 병목 지점을 파악할 수 있다.
• 인덱스 사용 여부 확인 : 인덱스가 적절히 사용되는지 확인하여 쿼리 성능을 최적화할 수 있다.
• 조인 방식 확인 : 테이블 간의 조인 방식과 순서를 확인하여 효율적인 조인을 구성할 수 있다.
• 쿼리 재작성 : 비효율적인 쿼리를 재작성하여 성능을 개선할 수 있다.

3. 사용법

3.1. 기본 사용법

EXPLAIN 명령어는 SELECT 쿼리 앞에 추가하여 실행 계획을 확인할 수 있다.

EXPLAIN SELECT * FROM employees WHERE department = 'Sales';

위 쿼리는 employees 테이블에서 department가 'Sales'인 레코드를 조회하는 쿼리의 실행 계획을 보여준다.

3.2. 출력 설명

EXPLAIN 명령어의 출력 결과는 여러 열(column)로 구성되며,
각 열은 쿼리 실행 계획의 특정 부분을 설명한다.

1. id: 쿼리 실행 단계 식별자
2. select_type: 쿼리 유형 (예: SIMPLE, PRIMARY, SUBQUERY 등)
3. table: 쿼리에서 액세스되는 테이블
4. type: 조인 유형 (예: ALL, index, range, ref, eq_ref, const, system, NULL)
5. possible_keys: 쿼리에서 사용할 수 있는 인덱스
6. key: 실제로 사용된 인덱스
7. key_len: 사용된 인덱스 키의 길이
8. ref: 인덱스와 비교된 컬럼
9. rows: 쿼리 실행 시 읽을 것으로 예상되는 행 수
10. Extra: 추가 정보 (예: Using index, Using where, Using temporary, Using filesort 등)

4. 쿼리 최적화 방법

1. 적절한 인덱스 사용
   인덱스는 쿼리 성능을 크게 향상시킬 수 있는 중요한 요소이다.
   EXPLAIN 명령어를 통해 인덱스가 적절히 사용되지 않는 경우,
   인덱스를 추가하거나 기존 인덱스를 수정하여 성능을 개선할 수 있다.

   CREATE INDEX idx_department ON employees(department);

   위 예제는 department 컬럼에 인덱스를 생성하여, 해당 컬럼을 기준으로 한 검색 성능을 향상시킨다.

2. 조인 최적화
   복잡한 조인 쿼리에서는 조인 순서와 방식이 중요하다. EXPLAIN을 통해 조인 계획을 확인하고, 필요한 경우 쿼리를 재작성하거나 인덱스를 추가하여 성능을 개선할 수 있다

   EXPLAIN SELECT e.name, d.name
   FROM employees e
   JOIN departments d ON e.department_id = d.id
   WHERE e.salary > 50000;

   위 쿼리는 employees 테이블과 departments 테이블을 조인하여,
   salary가 50000 이상인 직원과 그들의 부서를 조회한다.

3. 서브쿼리 최적화
   서브쿼리는 성능을 저하시키는 원인이 될 수 있다. 가능하다면 서브쿼리를 JOIN으로 변경하여 성능을 개선할 수 있다.

   -- 서브쿼리 사용 예제
   EXPLAIN SELECT name
   FROM employees
   WHERE department_id IN (SELECT id FROM departments WHERE location = 'New York');
   
   -- JOIN 사용 예제
   EXPLAIN SELECT e.name
   FROM employees e
   JOIN departments d ON e.department_id = d.id
   WHERE d.location = 'New York';

   JOIN을 사용한 쿼리는 서브쿼리보다 성능이 우수할 수 있다.
   

4. LIMIT 사용
   대량의 데이터를 조회할 때는 LIMIT 절을 사용하여 필요한 데이터만 조회하도록 한다. 이는 쿼리 성능을 개선하고, 불필요한 데이터를 처리하는 비용을 줄일 수 있다.

   EXPLAIN SELECT * FROM employees ORDER BY hire_date DESC LIMIT 10;

   위 쿼리는 최근 입사한 10명의 직원 정보를 조회한다.