DROP TABLE IF EXISTS users;
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
age INT,
department VARCHAR(100),
salary INT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
100만 건의 랜덤 데이터 삽입
-- 높은 재귀(반복) 횟수를 허용하도록 설정
-- (아래에서 생성할 더미 데이터의 개수와 맞춰서 작성하면 된다.)
SET SESSION cte_max_recursion_depth = 1000000;
-- 더미 데이터 삽입 쿼리
INSERT INTO users (name, age, department, salary, created_at)
WITH RECURSIVE cte (n) AS
(
SELECT 1
UNION ALL
SELECT n + 1 FROM cte WHERE n < 1000000 -- 생성하고 싶은 더미 데이터의 개수
)
SELECT
CONCAT('User', LPAD(n, 7, '0')) AS name, -- 'User' 다음에 7자리 숫자로 구성된 이름 생성
FLOOR(1 + RAND() * 100) AS age, -- 1부터 100 사이의 난수로 생성
CASE
WHEN n % 10 = 1 THEN 'Engineering'
WHEN n % 10 = 2 THEN 'Marketing'
WHEN n % 10 = 3 THEN 'Sales'
WHEN n % 10 = 4 THEN 'Finance'
WHEN n % 10 = 5 THEN 'HR'
WHEN n % 10 = 6 THEN 'Operations'
WHEN n % 10 = 7 THEN 'IT'
WHEN n % 10 = 8 THEN 'Customer Service'
WHEN n % 10 = 9 THEN 'Research and Development'
ELSE 'Product Management'
END AS department, -- 의미 있는 단어 조합으로 부서 이름 생성
FLOOR(1 + RAND() * 1000000) AS salary, -- 1부터 1000000 사이의 난수로 생성
TIMESTAMP(DATE_SUB(NOW(), INTERVAL FLOOR(RAND() * 3650) DAY) + INTERVAL FLOOR(RAND() * 86400) SECOND) AS created_at -- 최근 10년 내의 임의의 날짜와 시간 생성
FROM cte;
인덱스 생성하기
CREATE INDEX idx_age ON users (age);
데이터 조회해서 성능 측정하기
SELECT age, MAX(salary) FROM users
GROUP BY age
HAVING age >= 20 AND age < 30;
약 800ms 정도의 시간이 걸린다.
실행 계획 조회해보기
[실행 계획]
EXPLAIN SELECT age, MAX(salary) FROM users
GROUP BY age
HAVING age >= 20 AND age < 30;
type이 index인걸로 봐서 인덱스 풀 스캔을 하고 있다.
[세부 실행 계획]
EXPLAIN ANALYZE SELECT age, MAX(salary) FROM users
GROUP BY age
HAVING age >= 20 AND age < 30;
-> Filter: ((users.age >= 20) and (users.age < 30)) (cost=200263 rows=101) (actual time=208..882 rows=10 loops=1)
-> Group aggregate: max(users.salary) (cost=200263 rows=101) (actual time=38.4..882 rows=100 loops=1)
-> Index scan on users using idx_age (cost=100624 rows=996389) (actual time=1.53..850 rows=1e+6 loops=1)
성능 개선하기
SELECT age, MAX(salary) FROM users
WHERE age >= 20 AND age < 30
GROUP BY age;
약 150ms 정도 시간이 걸린다.
실행 계획 조회해보기
EXPLAIN SELECT age, MAX(salary) FROM users
WHERE age >= 20 AND age < 30
GROUP BY age;
EXPLAIN ANALYZE SELECT age, MAX(salary) FROM users
WHERE age >= 20 AND age < 30
GROUP BY age;
-> Group aggregate: max(users.salary) (cost=111397 rows=101) (actual time=75.7..198 rows=10 loops=1)
-> Index range scan on users using idx_age over (20 <= age < 30),
with index condition: ((users.age >= 20) and (users.age < 30)) (cost=91143 rows=202540) (actual time=0.582..193 rows=99990 loops=1)
HAVING 대신에 WHERE문을 사용함으로써 GROUP BY를 처리하기 전에 데이터를 필터링했다. 그런 뒤에 필터링 된 데이터를 기반으로 GROUP BY를 진행했다.
⭐
[이것만은 꼭 기억해두자!]
- HAVING문 대신에 WHERE문을 쓸 수 있는 지 체크해보자.
(어쩔 수 없이 HAVING을 쓸 수 밖에 없는 경우도 존재한다.)
