EXPLAIN 쿼리 튜닝

Notice

Recent Posts

Recent Comments

Link

« 2026/04 »
일	월	화	수	목	금	토
			1	2	3	4
5	6	7	8	9	10	11
12	13	14	15	16	17	18
19	20	21	22	23	24	25
26	27	28	29	30

Tags more

Archives

Today

Total

관리 메뉴

croissant_code

EXPLAIN 쿼리 튜닝 본문

EXPLAIN 쿼리 튜닝

crossfit_wod 2024. 11. 27. 15:05

실무에서 기존에 있던 쿼리를 개선하면서 EXPLAIN을 사용하면서 성능 개선을 보고 싶었다. 그래서 SQL 성능 튜닝에 관심이 가면서 어떻게 분석하는지 알아보기로 했다. 먼저 EXPLAIN에 대해서 알아보자.

EXPLAIN

EXPLAIN은 쿼리 실행 계획을 보여줘서 SQL 성능을 튜닝할 때 필수적인 도구
쿼리가 실행되기 전에 SQL 엔진이 어떻게 실행할 것인지에 대한 계획을 보여줌
- 어떤 테이블이 어떤 순서로 액세스되는지
- 사용된 인덱스와 조인 방법
- 테이블에서 몇 개의 행이 검색되는지
- 병목 지점을 식별하여 최적화할 수 있는 부분

예제

-- 테이블 선언
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100),
    created_at DATETIME,
    INDEX (created_at)
);
CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    order_date DATETIME,
    amount DECIMAL(10, 2),
    INDEX (user_id),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- 데이터 넣기
INSERT INTO users (name, email, created_at)
VALUES 
    ('Alice', 'alice@example.com', NOW()),
    ('Bob', 'bob@example.com', NOW() - INTERVAL 1 DAY),
    ('Charlie', 'charlie@example.com', NOW() - INTERVAL 2 DAY);
INSERT INTO orders (user_id, order_date, amount)
VALUES
    (1, NOW(), 100.50),
    (1, NOW() - INTERVAL 1 DAY, 150.00),
    (2, NOW(), 200.75);

EXPLAIN 실행 및 결과

EXPLAIN SELECT 
	u.name, 
    o.amount, 
    o.order_date
FROM 
	users AS u
INNER JOIN 
	orders AS o 
    ON u.id = o.user_id
WHERE 
	u.created_at >= NOW() - INTERVAL 3 DAY
ORDER BY 
	o.order_date DESC;

+----+-------------+-------+------------+-------+------------------+---------+---------+-----------------------------+-------+
| id | select_type | table | partitions | type  | possible_keys    | key     | key_len | ref                         | rows  |
+----+-------------+-------+------------+-------+------------------+---------+---------+-----------------------------+-------+
|  1 | SIMPLE      | u     | NULL       | range | created_at       | created_at | 5    | NULL                        | 3     |
|  1 | SIMPLE      | o     | NULL       | ref   | user_id          | user_id | 4       | test_db.u.id                | 2     |
+----+-------------+-------+------------+-------+------------------+---------+---------+-----------------------------+-------+

id : 단계 번호를 의미, SELECT문에서는 모두 1
select_type : SIMPLE은 하위 쿼리가 없는 단일 SELECT
table : 실행 중인 테이블
type : 테이블 엑세스 방식
- range : WHERE 조건의 범위를 만족하는 행을 검색(효율성이 좋음)
- ref : 조인 시 인덱스를 통해 검색
- EX) users 테이블은 range 방식, order 테이블은 ref 방식을 사용
possible_keys : 인덱스의 가능성이 있는 컬럼
- users 테이블은 created_at 인덱스가 사용될 수 있음
- orders 테이블은 user_id 인덱스가 사용될 수 있음
key : 실제로 사용된 인덱스
- users : created_at
- orders : user_id
rows : 쿼리가 스캔하는 예상 행 수
- users : 3개 행
- orders : 2개 행

성능 문제 식별

type 필드 확인
- ALL 또는 index 효율적이지 않기 때문에, range 또는 ref와 같은 효율적인 엑세스 방식인지 확인
인덱스 사용 유무 확인
- key에 값이 NULL이면 인덱스가 사용되지 않음
- 필요한 필드에 인덱스를 추가 필요
rows 필드 확인
- 스캔 행 수가 많다면 조건을 최적화 또는 필터링을 더 엄격히 설정
extra 필드 확인
- Using temporary 또는 Using filesort가 있으면 성능 문제 가능성

개선

문제 : orders 테이블에서 order by가 Using filesort로 동작
원인 : order_date에 인덱스가 없어서 정렬이 비효율적
해결 : orders 테이블에 복합 인덱스 추가

CREATE INDEX idx_user_order_date ON orders (user_id, order_date);

EXPLAIN SELECT 
	u.name, 
    o.amount, 
    o.order_date
FROM 
	users u
INNER JOIN 
	orders o 
    ON u.id = o.user_id
WHERE 
	u.created_at >= NOW() - INTERVAL 3 DAY
ORDER BY 
	o.order_date DESC;

+----+-------------+-------+------------+-------+------------------+------------------+---------+--------------------+-------+
| id | select_type | table | partitions | type  | possible_keys    | key              | key_len | ref                | rows  |
+----+-------------+-------+------------+-------+------------------+------------------+---------+--------------------+-------+
|  1 | SIMPLE      | u     | NULL       | range | created_at       | created_at       | 5       | NULL               | 3     |
|  1 | SIMPLE      | o     | NULL       | ref   | idx_user_order_date | idx_user_order_date | 8   | test_db.u.id       | 2     |
+----+-------------+-------+------------+-------+------------------+------------------+---------+--------------------+-------+

다른 예제

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    department_id INT,
    salary DECIMAL(10, 2),
    hire_date DATE,
    INDEX (department_id),
    INDEX (hire_date)
);
CREATE TABLE departments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    manager_id INT
);
INSERT INTO employees (name, department_id, salary, hire_date)
VALUES 
    ('Alice', 1, 50000, '2023-01-10'),
    ('Bob', 2, 60000, '2022-06-15'),
    ('Charlie', 1, 70000, '2023-03-01'),
    ('Daisy', 3, 40000, '2022-11-20');

INSERT INTO departments (name, manager_id)
VALUES
    ('HR', 10),
    ('Engineering', 11),
    ('Marketing', 12);

예제 1번

EXPLAIN SELECT * 
FROM 
	employees
WHERE 
	department_id = 1 
    AND hire_date > '2023-01-01';

+----+-------------+-----------+------------+-------+-----------------+---------------+---------+-------+-------+
| id | select_type | table     | partitions | type  | possible_keys   | key           | key_len | ref   | rows  |
+----+-------------+-----------+------------+-------+-----------------+---------------+---------+-------+-------+
|  1 | SIMPLE      | employees | NULL       | range | department_id, hire_date | hire_date | 3       | NULL  | 2     |
+----+-------------+-----------+------------+-------+-----------------+---------------+---------+-------+-------+

type : range, 인덱스를 사용해 조건 범위(hire_date > '2023-01-01')를 만족하는 행을 검색
key : hire_date 인덱스 사용
rows : 2개의 행이 검색됨
최적화 필요 없음 : 인덱스가 효과적으로 사용

예제 2번

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

+----+-------------+------------+------------+-------+-------------------+---------------+---------+------------------+-------+
| id | select_type | table      | partitions | type  | possible_keys     | key           | key_len | ref              | rows  |
+----+-------------+------------+------------+-------+-------------------+---------------+---------+------------------+-------+
|  1 | SIMPLE      | e          | NULL       | range | department_id     | NULL          | NULL    | NULL             | 2     |
|  1 | SIMPLE      | d          | NULL       | eq_ref| PRIMARY           | PRIMARY       | 4       | test.e.department_id | 1     |
+----+-------------+------------+------------+-------+-------------------+---------------+---------+------------------+-------+

employees 테이블
- type : range, salary > 50000 조건으로 범위 검색
- key : 인덱스가 사용되지 않음, salary에 인덱스가 없기 때문
departments 테이블
- type : eq_ref, 조인 조건에서 PRIMARY 키가 사용되어 정확히 1행을 검색
최적화
- employees.salary에 인덱스를 추가

CREATE INDEX idx_salary ON employees (salary);

+----+-------------+------------+------------+-------+-------------------+---------------+---------+-------+-------+
| id | select_type | table      | partitions | type  | possible_keys     | key           | key_len | ref   | rows  |
+----+-------------+------------+------------+-------+-------------------+---------------+---------+-------+-------+
|  1 | SIMPLE      | e          | NULL       | range | department_id, salary | salary      | 5       | NULL  | 2     |
|  1 | SIMPLE      | d          | NULL       | eq_ref| PRIMARY           | PRIMARY       | 4       | test.e.department_id | 1     |
+----+-------------+------------+------------+-------+-------------------+---------------+---------+-------+-------+

예제 3번

EXPLAIN SELECT 
	department_id, 
    COUNT(*) AS num_employees
FROM 
	employees
GROUP BY 
	department_id
ORDER BY 
	num_employees DESC;

+----+-------------+-----------+------------+-------+------------------+---------------+---------+-------+-------+
| id | select_type | table     | partitions | type  | possible_keys    | key           | key_len | ref   | rows  |
+----+-------------+-----------+------------+-------+------------------+---------------+---------+-------+-------+
|  1 | SIMPLE      | employees | NULL       | index | NULL             | department_id | 4       | NULL  | 4     |
+----+-------------+-----------+------------+-------+------------------+---------------+---------+-------+-------+

type : index, 인덱스를 스캔하여 데이터를 가져옴
Extra : Using index, 인덱스를 활용하여 직접 데이터를 가져오므로 성능이 좋음
최적화 필요 없음

예제 4번

EXPLAIN SELECT 
	name, 
    salary
FROM 
	employees
WHERE 
	salary > (
        SELECT 
        	AVG(salary)
        FROM 
        	employees
	);

+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+-------+
| id | select_type | table     | partitions | type  | possible_keys | key     | key_len | ref   | rows  |
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+-------+
|  1 | PRIMARY     | employees | NULL       | ALL   | NULL          | NULL    | NULL    | NULL  | 4     |
|  2 | SUBQUERY    | employees | NULL       | ALL   | NULL          | NULL    | NULL    | NULL  | 4     |
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+-------+

type : ALL로 모든 행을 스캔하는 풀 테이블 스캔 발생
최적화 필요
- 서브쿼리를 JOIN으로 변환하면 효율성이 증가

EXPLAIN SELECT 
	e.name, 
    e.salary
FROM 
	employees e
JOIN 
	(SELECT AVG(salary) AS avg_salary FROM employees) sub
	ON e.salary > sub.avg_salary;

+----+-------------+------------+------------+-------+---------------+---------+---------+-------+-------+
| id | select_type | table      | partitions | type  | possible_keys | key     | key_len | ref   | rows  |
+----+-------------+------------+------------+-------+---------------+---------+---------+-------+-------+
|  1 | PRIMARY     | employees  | NULL       | ALL   | NULL          | NULL    | NULL    | NULL  | 4     |
|  2 | DERIVED     | NULL       | NULL       | NULL  | NULL          | NULL    | NULL    | NULL  | 1     |
+----+-------------+------------+------------+-------+---------------+---------+---------+-------+-------+

실전 쿼리

EXPLAIN SELECT 
	z.iOrderDtSeq, 	
	MAX(z.zzicgo_seq) AS zzicgo_seq, 
	z.book_type_idx, 
	b.book_name, 
	b.book_image_path, 
	MAX(complete_date) AS last_completed_date,
	ROUND(SUM(CASE WHEN complete_date IS NOT NULL THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 1) AS completion_rate,
	SUM(CASE WHEN complete_date IS NOT NULL THEN 1 ELSE 0 END) AS completed_count,
	COUNT(*) AS total_count
FROM 
	TB_ZZICGO AS z 
INNER JOIN 
	TB_BOOK_TYPE AS b 
	ON z.book_type_idx = b.book_type_idx
WHERE 
	z.CRS_TYPE_NUM = '18'
	AND delete_flag = 1
	AND z.iMemberSeq = '5621' -- 8473 5621
GROUP BY 
	z.iOrderDtSeq
ORDER BY 
	CASE 
		-- 새롭게 추가된 항목 (완료 항목이 없고, 마지막 완료 날짜가 NULL인 경우)
		WHEN SUM(CASE WHEN z.complete_date IS NOT NULL THEN 1 ELSE 0 END) = 0 
		     AND DATE(MAX(regdate)) = CURRENT_DATE THEN 0
		-- 예전에 추가했지만 완료하지 않은 항목
		WHEN SUM(CASE WHEN z.complete_date IS NOT NULL THEN 1 ELSE 0 END) < COUNT(*) THEN 1
		-- 모든 항목이 완료된 경우
		WHEN SUM(CASE WHEN z.complete_date IS NOT NULL THEN 1 ELSE 0 END) / COUNT(*) = 1 THEN 2
	    END ASC, 
	last_completed_date DESC;

+----+-------------+-------+------------+-------------------+----------------------------+---------+------------------------+------+--------------------------------------------------------------------------------------------+
| id | select_type | table | type       | possible_keys     | key                        | key_len | ref                    | rows | Extra                                                                                      |
+----+-------------+-------+------------+-------------------+----------------------------+---------+------------------------+------+--------------------------------------------------------------------------------------------+
|  1 | SIMPLE      | z     | index_merge| program_type_cd, member_seq | member_seq, program_type_cd | 4, 13  | NULL                   | 536  | Using intersect(member_seq,program_type_cd); Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | b     | eq_ref     | PRIMARY           | PRIMARY                    | 4       | cylab.z.book_type_idx  | 1    | Using where                                                                                 |
+----+-------------+-------+------------+-------------------+----------------------------+---------+------------------------+------+--------------------------------------------------------------------------------------------+

id
- 쿼리 실행 순서
- 모든 값이 1이므로 단일 SELECT 문에서 실행되는 순서
select_type
- 쿼리 유형
- SIMPLE : 단일 SELECT 문이며 하위 쿼리나 유도 테이블(derived table)이 없음
table
- 처리 중인 테이블
- z는 TB_ZZICGO 테이블이고, b는 TB_BOOK_TYPE 테이블
type
- 테이블 액세스 방식으로 쿼리 효율성
- index_merge
  - 두 개 이상의 인덱스를 결합(member_seq와 program_type_cd)하여 데이터를 검색
  - 이는 일부 최적화된 경우에 발생하며, 여전히 개선 여지
- eq_ref
  - 조인에서 기본 키(또는 유일 키)를 사용해 정확히 한 행을 찾음, 효율적
possible_keys
- 사용 가능한 인덱스
- z 테이블은 program_type_cd, member_seq가 가능하며, b 테이블은 PRIMARY 키를 사용
key
- 실제로 사용된 인덱스
- z 테이블은 member_seq와 program_type_cd를 사용해 검색
- b 테이블은 PRIMARY 키를 사용해 정확한 행을 찾음
key_len
- 사용된 인덱스의 길이
- 4,13은 member_seq와 program_type_cd 각각의 데이터 타입 길이
ref
- 어떤 값이 인덱스 조건으로 사용되었는지 표현
- z 테이블은 NULL이며, b 테이블은 z.book_type_idx를 참조
rows
- 예상 검색 행의 수
- z 테이블은 536개의 행을 스캔하며, b 테이블은 1개의 행만 스캔.
Extra
- SQL 엔진이 수행하는 추가 작업
- Using intersect(member_seq, program_type_cd)
  - member_seq와 program_type_cd 인덱스를 결합(intersect)하여 데이터를 검색
- Using where
  - 추가적인 WHERE 필터 조건이 적용됨
- Using temporary
  - 쿼리 실행 중 임시 테이블을 생성하여 데이터를 처리
- Using filesort
  - ORDER BY 처리 시 정렬 작업이 필요하므로 추가 비용 발생

성능 병목

index_merge
- 두 개의 인덱스를 결합하여 데이터를 검색하는 방식은 효율적이지 않음
- 해결 방법 : 단일 복합 인덱스를 추가하여 쿼리 성능을 향상시킬 수 있습니다
Using temporary와 Using filesort
- Using temporary, Using filesort => 정렬(ORDER BY) 및 임시 테이블 작업이 발생
- 해결 방법 : 정렬에 사용되는 컬럼에 적절한 인덱스를 추가하거나, 필요 없는 정렬 조건을 제거

적용

CREATE INDEX idx_member_program ON TB_ZZICGO (member_seq, program_type_cd);
CREATE INDEX idx_complete_date ON TB_ZZICGO (complete_date, regdate);

+----+-------------+-----------+------------+-------+-----------------+--------------------+---------+-------+-------+
| id | select_type | table     | partitions | type  | possible_keys   | key                | key_len | ref   | rows  |
+----+-------------+-----------+------------+-------+-----------------+--------------------+---------+-------+-------+
|  1 | SIMPLE      | z         | NULL       | ref   | idx_member_program | idx_member_program | 8       | NULL  | 100   |
|  1 | SIMPLE      | b         | NULL       | eq_ref| PRIMARY          | PRIMARY            | 4       | z.book_type_idx | 1     |
+----+-------------+-----------+------------+-------+-----------------+--------------------+---------+-------+-------+

'SW' 카테고리의 다른 글

프로그래머스SQL-조건에 부합하는 중고거래 댓글 조회하기 (1)	2024.11.29
프로그래머스-과일로 만든 아이스크림 고르기 (0)	2024.11.28
프로그래머스SQL-평균 일일 대여 요금 구하기 (0)	2024.11.26
프로그래머스-폰켓몬 (1)	2024.11.26
URI와 URL (1)	2024.11.21

'SW' Related Articles

croissant_code

EXPLAIN 쿼리 튜닝 본문

EXPLAIN 쿼리 튜닝

EXPLAIN

예제

EXPLAIN 실행 및 결과

성능 문제 식별

개선

다른 예제

예제 1번

예제 2번

예제 3번

예제 4번

실전 쿼리

성능 병목

적용

'SW' 카테고리의 다른 글

티스토리툴바