쿼리 실행 계획 조회

테스트 환경 구축

이전까지의 포스팅은 모두 핵심만 정리하고자 간단한 형식으로 작성했지만, 여기서부터는 부가 설명이 필요할 것 같다. 우선 다양한 쿼리문을 테스트해보기 위해 로컬 MySQL 서버에 다음과 같이 테이블과 데이터를 생성해주었다. 귀찮아서 온라인 쿼리 사이트들도 사용해보려 했는데, 출력 결과 양식이 제대로 나오지 않아서(analyze 결과가..) 결국 MySQL Workbench를 사용했다.

스키마 구성은 제품 정보를 담는 product, 사용자 정보를 담는 customer, 그리고 주문 기록을 담는 orders 테이블로 간단히 구성했다.

더보기 
-- product 테이블 생성
CREATE TABLE product (
  product_id INT AUTO_INCREMENT PRIMARY KEY,
  product_nm VARCHAR(100),
  description VARCHAR(255)
);

INSERT INTO product (product_nm, description) VALUES 
  ('Entity Framework Extensions', 'Use <a href="https://entityframework-extensions.net/" target="_blank">Entity Framework Extensions</a> to extend your DbContext with high-performance bulk operations.'),
  ('Dapper Plus', 'Use <a href="https://dapper-plus.net/" target="_blank">Dapper Plus</a> to extend your IDbConnection with high-performance bulk operations.'),
  ('C# Eval Expression', 'Use <a href="https://eval-expression.net/" target="_blank">C# Eval Expression</a> to compile and execute C# code at runtime.');

-- customer 테이블 생성
CREATE TABLE customer (
  customer_id INT AUTO_INCREMENT PRIMARY KEY,
  customer_nm VARCHAR(10)
);

INSERT INTO customer (customer_nm) VALUES 
  ('kate'),
  ('gia');

-- orders 테이블 생성
CREATE TABLE orders (
  order_id INT AUTO_INCREMENT PRIMARY KEY,
  customer_id INT,
  product_id INT,
  order_cnt INT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (customer_id) REFERENCES customer(customer_id),
  FOREIGN KEY (product_id) REFERENCES product(product_id)
);

INSERT INTO orders (customer_id, product_id, order_cnt, created_at) VALUES 
  (1, 1, 2, '2025-01-01 09:00:00'),
  (1, 2, 4, '2025-01-15 11:30:00'),
  (2, 3, 1, '2025-02-01 15:45:00'),
  (2, 2, 5, '2025-02-01 17:30:00'),
  (2, 2, 5, '2025-02-11 14:00:00');

 

Workbench Visual Explain

workbench에서 쿼리를 실행하고 Result Grid 대신 Execution Plain 을 선택하면 아래와 같이 그림으로 실행 계획을 보여준다. 가장 직관적으로 확인할 수 있는 방법이다.

 

MySQL EXPLAIN 출력 형식

MySQL의 실행 계획을 보기 위해서는 쿼리문 앞에 EXPLAIN | DESCRIBE | DESC 명령어를 붙이면 된다. 3가지 모두 동일한 결과 출력한다. 추가로, MySQL 8부터 출력 형식을 단순 Table, JSON, Tree 형태로 지정 가능하다. json 형식은 기존의 표 형식보다 다양한 정보를 제공한다.

아래는 단순 출력 포맷을 보여주기 위한 예시로, 자세한 내용은 아직 몰라도 된다. 그리고, 정확히 무슨 기준인지는 모르겠지만 쿼리와 스키마가 모두 동일함에도 불구하고 explain/analyze 결과가 종종 달라지는 것 같다.. 그렇기 때문에 포스팅의 내용은 참고만 해주길! 이 부분에 대해서도 나중에 다룰 수 있다면 좋겠다.

-- 일반 테이블 형태
EXPLAIN 
SELECT * FROM customer;

-- JSON 형태
EXPLAIN FORMAT = JSON
SELECT * FROM customer;

-- TREE 형태
EXPLAIN FORMAT = TREE 
SELECT c.customer_nm, p.product_nm
FROM orders o, customer c, product p
WHERE o.customer_id = c.customer_id
	AND o.product_id = p.product_id;
더보기 
{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "0.45"
    },
    "table": {
      "table_name": "customer",
      "access_type": "ALL",
      "rows_examined_per_scan": 2,
      "rows_produced_per_join": 2,
      "filtered": "100.00",
      "cost_info": {
        "read_cost": "0.25",
        "eval_cost": "0.20",
        "prefix_cost": "0.45",
        "data_read_per_join": "80"
      },
      "used_columns": [
        "customer_id",
        "customer_nm"
      ]
    }
  }
}

 

EXPLAIN ANALYZE 출력 형식

위에서 본 단순 EXPLAIN 결과는 MySQL 서버가 가지고 있는 통계 정보들을 활용한 예측 결과이다. 만약 실제 실행된 소요 시간과 실행 계획 정보를 출력하고 싶다면 ANALYZE 키워드를 사용해야 한다. 이 경우 Tree 형식으로 출력되며, 들여쓰기가 같은 레벨에서는 상단에 위치한 라인이 먼저 실행되고, 들여쓰기가 다른 레벨에서는 가장 안쪽에 위치한 라인이 먼저 실행된다.

EXPLAIN ANALYZE 
SELECT c.customer_nm, p.product_nm
FROM orders o, customer c, product p
WHERE o.customer_id = c.customer_id
	AND o.product_id = p.product_id;

 

EXPLAIN

필드 설명

이제 EXPLAIN 실행 결과 나오는 속성들에 대해 간단히 알아보겠다. 각 필드별 상세 옵션을 정리하기 보다는 간단한 정의를 알아보고, 실제 쿼리와 대조해보며 결과를 분석하는 방식을 익히는 데 초점을 맞추려고 한다.

사진은 아래 EXPLAIN 명령을 실행 시킨 결과이다. 이 중 rows와 filtered 속성은 MySQL의 통계 정보를 기반으로 한 것이며, 정확하지는 않은 값이다. 쿼리는 일부러 다양한 결과를 포함하고자 만든 것이므로 무의미하며 이해할 필요는 없다. 단순히 로직을 결과와 대조하며 이해하는 데 참고하길 바란다.

EXPLAIN 
(
  SELECT *  -- id = 1(row1~3)
  FROM orders o
  JOIN customer c ON o.customer_id = c.customer_id -- row2
  JOIN product p ON o.product_id = p.product_id -- row3
  WHERE c.customer_nm = 'gia' -- row1
  
  UNION ALL

  SELECT *  -- id = 2(row4~6)
  FROM orders o -- row 4
  JOIN customer c ON o.customer_id = c.customer_id -- row 5
  JOIN product p ON o.product_id = p.product_id -- row 6
  WHERE o.order_cnt > (
	SELECT COUNT(*)  -- id = 3(row 7)
    FROM orders
    WHERE created_at BETWEEN '2025-01-01' AND '2025-02-01'
  )
);

  • id: select를 기준으로 쿼리가 실행되는 순서를 나타낸다. MySQL에서는 조인을 하나의 단위로 실행하기 때문에 조인만 사용하는 쿼리라면 항상 1 값으로 나올 것이다. 위 예시에서는 union을 사용했고, 서브 쿼리까지 있기 때문에 id가 1~3까지 나온 것을 볼 수 있다.
  • select_type: select문의 유형을 나타낸다. 서브 쿼리나 union이 없으면 SIMPLE로 출력된다. union이 포함된 SQL 중 첫 번째 select의 타입은 PRIMARY, 아래 select는 UNION이다. SUBQUERY는 말 그대로 서브 쿼리에 해당한다.
  • table: 사용되는 테이블의 이름이나 alias를 출력한다. 서브쿼리나 임시 테이블을 만들 때에는 <subquery#> 나 <derived#>라고 출력된다. 만약 <derived2> 라면 id=2가 먼저 실행되고 그 결과인 임시 테이블을 참조하는 것이다.
더보기

리턴하는 값이 COUNT, 즉 스칼라 값이기 때문이다. MySQL이 이를 상수로 취급하여 단순 테이블 필터링으로 변환된 것이다. (by chatGPT) 이렇게 다양한 상황이 존재할 수 있어서 옵티마이저의 결정을 이해하기가 어려운 것 같다.

  • partitions: 해당 쿼리가 사용하고 있는 파티션을 나타낸다. 파티션이란 큰 테이블을 여러 개의 작은 논리적 단위로 나눈 것을 뜻한다. 지금은 사용하고 있지 않기 때문에, 파티션을 활용한다면 더 빨리 처리할 수 있다는 정도만 알고 넘어가자.
  • type: 테이블에서 데이터를 찾는 접근 방식을 나타낸다. 쿼리의 성능에 많은 영향을 미치는 매우 중요한 항목이다. ALL은 풀 스캔, 즉 테이블 전체를 스캔한 것이다. ref와 eq_ref는 비슷하지만, eq_ref는 기본 키나 유니크 키를 사용해서 항상 하나의 행만 반환한 것이고, ref는 일반 인덱스를 사용하기 때문에 여러 개의 행이 반환될 수 있다. 즉, eq_ref가 하나의 행만 찾으면 되기 때문에 더 효율적이다.
더보기

customer_id와 product_id는 둘 다 테이블의 기본 키인데 왜 row2는 ref이고 row3은 eq_ref일까? 이는 기준이 되는 table이 다르기 때문이다. row2의 table은 o로, orders 테이블에서의 customer_id(key)는 기본키가 아니기 때문에 일반 인덱스로 취급되어 ref인 것이다. row3의 table은 p이고, product_id(key)가 product 테이블의 키가 맞기 때문에 eq_ref이다. 그래서 key 필드에 PRIMARY라고 표시된 것이기도 하다.

  • possible_keys: 옵티마이저가 사용할 수 있는 인덱스 목록을 표시한다. 실제 사용된 것이 아니라 사용 가능한 것이다.
  • key: 위 목록 중에서 실제로 사용한 인덱스다. PRIMARY는 해당 테이블의 기본 키가 사용된 것이며, NULL인 경우 인덱스를 사용하지 않은 것이다. 이 경우 key, key_len, ref가 전부 NULL인 것을 확인할 수 있다.
  • key_len: 사용한 인덱스의 바이트 수이다. 복합 인덱스라면 인덱스를 모두 더한 값이 나온다. 인덱스가 길면 비효율적이기 때문에 참고할 수 있다.
  • ref: 조인 시 액세스된 테이블 정보가 표시된다. row2는 o 테이블(table)의 customer_id(key)를 c 테이블의 customer_id(ref)와 비교하여 조인했다는 의미이다.
  • rows: 접근 방식을 통해 접근한 행의 수를 나타낸다.
  • filtered: 스토리지 엔진에서 가져온 데이터를 MySQL 엔진이 필터링하여 제거된 비율을 나타낸다. row1의 경우, 사용자가 2명밖에 없기 때문에 where 조건으로 50% 필터링되었다는 의미이다. row7도 총 5개의 데이터 중 하나만 해당 범위를 벗어나기 때문에 20%가 필터링되었다.
  • Extra: 옵티마이저의 동작에 대한 추가 정보를 나타낸다. 여기서는 WHERE 절을 이용해서 데이터를 필터링 했음을 알 수 있다.

 

ANALYZE

출력 양식 설명

analyze를 사용하면 쿼리의 실행 시간과 각 단계의 실행 계획을 구체적으로 알 수 있다. EXPLAIN이 통계 수치를 기반으로 예상한 값을 알려줬다면, ANALYZE는 실제 데이터를 기반으로 한다. 출력은 트리 구조로 표시되며, 가장 안쪽에 들여쓰기된 작업이 먼저 실행된다. 각 줄은 조인이나 스캔 등의 작업 정보와 실행 시간에 대한 비용 정보로 구성된다.

 

 

비용 정보 형식

(cost=X rows=Y) (actual time=A...B rows=Z loops=D)

  • X: 옵티마이저가 예측한 비용
  • Y: 옵티마이저가 예측한 처리 결과 행의 수
  • A: 첫 번째 행을 반환하는데 걸린 시간(ms)
  • B: 모든 행을 반환하는데 걸린 총 시간(ms)
  • C: 실제 처리된 결과 행의 수
  • D: 해당 작업이 반복된 횟수

 

References

'Computer Science > 데이터베이스' 카테고리의 다른 글

DB 스캔 종류와 mysql explain type 칼럼  (0) 2025.04.28
DB Index란?  (0) 2025.04.24
Mysql 옵티마이저와 통계 정보  (0) 2025.02.15

+ Recent posts

티스토리툴바