MySQL `EXPLAIN`의 이해와 활용

MySQL EXPLAIN란?

EXPLAIN은 MySQL에서 제공하는 명령어로, SQL 쿼리가 어떻게 실행될지에 대한 계획을 확인하는 데 사용됩니다. 특히 쿼리의 성능 최적화에 있어서 중요한 도구로 사용됩니다.

EXPLAIN을 사용하면 다음과 같은 정보를 얻을 수 있습니다:

• 어떤 테이블들이 쿼리에 참여하는지
• 각 테이블을 어떤 순서로 스캔하는지
• 어떤 인덱스들이 사용되는지
• 얼마나 많은 행이 각 단계에서 처리되는지

주요 항목의 설명:

1. id: 쿼리의 순서나 단계를 나타냅니다.
2. select_type: 쿼리의 종류입니다 (예: SIMPLE, SUBQUERY, UNION).
3. table: 현재 단계에서 참조되는 테이블 이름.
4. type: 조인 타입 (예: ALL, INDEX, RANGE, REF, EQ_REF, CONST, NULL).
5. possible_keys: 쿼리에서 고려될 수 있는 인덱스들.
6. key: 실제로 사용된 인덱스.
7. key_len: 사용된 인덱스의 길이.
8. ref: 조인에 사용된 열이나 상수.
9. rows: 추정 스캔 행 수.
10. Extra: 추가적인 정보.

예시 시나리오:

우리는 users 테이블과 orders 테이블이 있습니다. 각 사용자는 여러 주문을 할 수 있습니다. 우리는 특정 사용자의 주문을 조회하려 합니다.

-- users 테이블
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL
);

-- orders 테이블
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    item_name VARCHAR(255) NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- 인덱스 추가
CREATE INDEX idx_user_id ON orders(user_id);

쿼리 최적화를 위해 EXPLAIN을 활용하여 쿼리를 분석하려 합니다.

EXPLAIN SELECT * FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.id = 1;

위의 EXPLAIN 결과로 나온 출력은 우리에게 어떤 인덱스가 사용되었는지, 얼마나 많은 행이 스캔되었는지 등의 정보를 제공할 것입니다.

---

EXPLAIN을 사용하면 쿼리의 성능 문제점을 진단하고, 해당 문제점을 해결하기 위한 방법을 찾아내는 데 큰 도움을 받을 수 있습니다. 느린 쿼리를 발견했을 때, EXPLAIN을 사용하여 그 원인을 파악하고, 필요한 인덱스를 추가하거나 쿼리를 재작성함으로써 성능을 개선하는 작업은 데이터베이스 관리의 중요한 부분입니다.