[실습] 부서별 최대 연봉을 가진 사용자들 조회하는 SQL문 튜닝하기

JSCODE 박재성

2025-12-02

✅ 부서별 최대 연봉을 가진 사용자들 조회하기

1. 테이블 생성

DROP TABLE IF EXISTS users; 

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(100),
    salary INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

2. 100만 건의 랜덤 데이터 삽입

-- 높은 재귀(반복) 횟수를 허용하도록 설정
-- (아래에서 생성할 더미 데이터의 개수와 맞춰서 작성하면 된다.)
SET SESSION cte_max_recursion_depth = 1000000; 

-- 더미 데이터 삽입 쿼리
INSERT INTO users (name, 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자리 숫자로 구성된 이름 생성
    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() * 100000) AS salary,    -- 1부터 100000 사이의 난수로 나이 생성
    TIMESTAMP(DATE_SUB(NOW(), INTERVAL FLOOR(RAND() * 3650) DAY) + INTERVAL FLOOR(RAND() * 86400) SECOND) AS created_at -- 최근 10년 내의 임의의 날짜와 시간 생성
FROM cte;

3. 기존 SQL문 성능 체크하기

SELECT u.id, u.name, u.department, u.salary, u.created_at
FROM users u
JOIN (
    SELECT department, MAX(salary) AS max_salary
    FROM users
    GROUP BY department
) d ON u.department = d.department AND u.salary = d.max_salary;



약 600ms 정도 걸린다.

4. 실행 계획 조회해보기

EXPLAIN SELECT u.*
FROM users u
JOIN (
    SELECT department, MAX(salary) AS max_salary
    FROM users
    GROUP BY department
) d ON u.department = d.department AND u.salary = d.max_salary;



JOIN 문 내부에 있는 서브쿼리를 실행시킬 때 풀 테이블 스캔이 이뤄어졌음을 알 수 있다.

5. 성능 개선해보기
GROUP BY department는 department를 기준으로 정렬을 시킨 뒤에 MAX(salary) 값을 구하게 된다. 이 때, MAX(salary)를 구하기 위해 이리저리 찾아다닐 수 밖에 없다.
이를 해결하기 위해 (department, salary)의 멀티 컬럼 인덱스가 있으면 department를 기준으로 정렬을 시키는 작업을 하지 않아도 되고, 심지어 MAX(salary)도 빠르게 찾을 수 있다. 멀티 컬럼 인덱스를 생성해보자.

CREATE INDEX idx_department_salary ON users (department, salary);

 
그러고 성능을 테스트해보자.

SELECT u.*
FROM users u
JOIN (
    SELECT department, MAX(salary) AS max_salary
    FROM users
    GROUP BY department
) d ON u.department = d.department AND u.salary = d.max_salary;



600ms에서 20ms로 30배 정도 성능이 향상됐다.

실행 계획을 조회해보자.

실행 계획을 조회해봐도 인덱스를 잘 활용해서 데이터를 찾고 있고, 접근한 rows 자체도 훨씬 적어졌다.