mysql 실행계획 확인하기

MYSQL 에서는 EXPLAIN [ANALYZE] 명령어로 실행계획 확인이 가능함.
쿼리 처리에 사용되는 각 반복자에 대해 TREE 형식의 SELECT 문 실행에 대한 확장 정보를 제공 한다.

1) 결과 컬럼들의 의미 

 - id : SELECT 쿼리별로 부여되는 식별자
 - select_type : 각 단위 SELECT 쿼리의 타입. ex) SIMPLE(단순 SELECT), PRIMARY(가장 바깥쪽 쿼리), UNION
 - table : 해당 행이 어떤 테이블에 대한 것인지를 나타냅니다[1].
 - type : 테이블에서 레코드를 읽은 방식. ex) system, const, eq_ref, ref, range, index, ALL 등(성능순)
 - possible_keys : 옵티마이저가 고려한 인덱스 목록. 실제 사용과는 무관.
 - key : 실제로 사용된 인덱스. 
 - key_len : 사용된 인덱스의 길이. 복합 인덱스에서 몇 개의 컬럼이 사용되었는지 알 수 있다
 - ref : 인덱스에서 값을 찾기 위해 사용된 컬럼이나 상수
 - rows : 쿼리를 처리하기 위해 검색하거나 조회한 레코드 수의 예측값
 - filtered : WHERE 조건으로 필터링되고 남은 레코드의 비율
 - Extra : 옵티마이저의 동작에 대한 추가적인 정보

* 추가 키워드
explain [extended] , [partitions] => 는 8.0 에서 사라짐. 
explain format=json -> json 형태로 출력
explain format=tree -> tree 형태로 출력
 
  


2. 실행계획 확인을 위한 테스트 스크립트  


-- 테이블 생성
CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100)
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    total_amount DECIMAL(10, 2)
);

-- 인덱스 생성
CREATE INDEX idx_customer_order ON orders (customer_id, order_date);

-- 고객 데이터 생성
INSERT INTO customers (customer_id, name, email)
SELECT 
    n,
    CONCAT('Customer ', n),
    CONCAT('customer', n, '@example.com')
FROM (
    SELECT @row := @row + 1 AS n
    FROM (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) t1,
         (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) t2,
         (SELECT @row:=0) r
) numbers
WHERE n <= 1000;

-- 주문 데이터 생성
INSERT INTO orders (order_id, customer_id, order_date, total_amount)
SELECT 
    n,
    1 + FLOOR(RAND() * 1000),
    DATE_ADD('2025-01-01', INTERVAL FLOOR(RAND() * 365) DAY),
    ROUND(50 + RAND() * 950, 2)
FROM (
    SELECT @row := @row + 1 AS n
    FROM (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) t1,
         (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) t2,
         (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) t3,
         (SELECT @row:=0) r
) numbers
WHERE n <= 10000;


EXPLAIN ANALYZE
SELECT c.name, o.order_id, o.order_date, o.total_amount
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE o.customer_id = 1;

 

-> Nested loop inner join  (cost=0.70 rows=2) (actual time=0.123..0.128 rows=2 loops=1)
    -> Index lookup on c using PRIMARY (customer_id=1)  (cost=0.35 rows=1) (actual time=0.089..0.091 rows=1 loops=1)
    -> Index lookup on o using idx_customer_order (customer_id=c.customer_id)  (cost=0.35 rows=2) (actual time=0.031..0.033 rows=2 loops=1)


 * 실행계획 결과의 주요 부분 
 - cost: 옵티마이저가 예상한 비용. ex) cost=451.00은 전체 쿼리의 예상 비용
 - rows: 옵티마이저가 예상한 결과 행 수 ex) rows=1000은 1000개의 행이 반환될 것으로 예상됨.
 - actual time: 실제 실행 시간. ex) actual time=0.308..4.170은 첫 행을 가져오는 데 0.308 ms, 모든 행을 가져오는 데 4.170 ms
 - rows: 실제로 반환된 행 수 ex) rows=1000 은 실제로 1000개의 행이 반환
 - loops: 해당 작업이 반복된 횟수 ex) loops=1은 이 작업이 한 번 실행되었음을 의미
 
 * 실행 계획 추가 주목할 점
 - idx_customer_order 인덱스가 사용됨. (customer_id, order_date) 복합 인덱스
 - 인덱스의 선두 컬럼인 customer_id만 조건으로 사용. order_date는 사용되지 않음 
 - Index lookup on o using idx_customer_order (customer_id=c.customer_id)는 
    이 인덱스를 사용해 조인 조건을 만족하는 행을 찾는다는 것을 나타냄.
 - actual time 과 rows 를 보면, 인덱스를 효과적으로 사용하였음