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

JSCODE 박재성

2025-12-02

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

1. 테이블 생성

DROP TABLE IF EXISTS posts;
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 *
FROM users
WHERE salary = (SELECT MAX(salary) FROM users)
AND department IN ('Sales', 'Marketing', 'IT');



약 280ms 정도 걸린다.

4. 실행 계획 조회해보기



type이 ALL인걸로 봐서 풀 테이블 스캔을 했다. 인덱스를 활용해서 풀 테이블 스캔을 하지 않도록 바꿔보자.

5. 인덱스 추가하기
데이터 액세스 수를 크게 줄일 수 있는 인덱스는 중복 정도가 낮은 컬럼이다. 따라서 salary로 인덱스를 생성하자.

CREATE INDEX idx_salary ON users (salary);

 

2. 성능 측정해보기

SELECT *
FROM users
WHERE salary = (SELECT MAX(salary) FROM users)
AND department IN ('Sales', 'Marketing', 'IT');



280ms에서 25ms 정도로 성능이 향상됐다.
 
실행 계획도 한 번 살펴보자.

EXPLAIN SELECT *
FROM users
WHERE salary = (SELECT MAX(salary) FROM users)
AND department IN ('Sales', 'Marketing', 'IT');



인덱스를 활용해서 데이터를 액세스 했고, 액세스 수도 6개로 확 줄었다.