WHERE문이 사용된 SQL문 튜닝

WHERE 문의 부등호, IN, BETWEEN, LIKE 와 같은 곳에 사용되는 컬럼을 인덱스로 사용하면 효율이 좋다.

그래서 조회 조건의 날짜를 인덱스로 사용해 튜닝했다.

# 기존 테이블 삭제
DROP TABLE IF EXISTS users; 

# 테이블 생성
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

# 재귀 설정
SET SESSION cte_max_recursion_depth = 1000000; 

# 더미 생성
INSERT INTO users (name, department, 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,
    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,
    TIMESTAMP(DATE_SUB(NOW(), INTERVAL FLOOR(RAND() * 3650) DAY) + INTERVAL FLOOR(RAND() * 86400) SECOND) AS created_at -- 최근 10년 내의 임의의 날짜와 시간 생성
FROM cte;

# 테이블 확인
SELECT COUNT(*) FROM users;
SELECT * FROM users LIMIT 10;

# 0.078초
SELECT * FROM users
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY);

# 996,810 row
EXPLAIN SELECT * FROM users
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY); 

# 인덱스 생성
CREATE INDEX idx_created_at ON users (created_at);

SHOW INDEX FROM users;

# 0.016초
SELECT * FROM users
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY); 

# range / 1,109 row
EXPLAIN SELECT * FROM users
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY); 

# 조건 추가
EXPLAIN SELECT * FROM users
WHERE department = 'Sales'
AND created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY);

# 실행 계획 세부 내용
EXPLAIN ANALYZE SELECT * FROM users
WHERE department = 'Sales'
AND created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY);

 

WHERE에서 날짜 외 다른 조건이 추가하면 인덱스를 추가하는 방법이 3가지된다.

1. CREATED_AT 컬럼을 기준으로 인덱스 생성

2. DEPARTMENT 컬럼을 기준으로 인덱스 생성

3. DEPARTMENT, CREATED_AT 둘 다 인텍스 생성

# 기존 테이블 삭제
DROP TABLE IF EXISTS users; 

# 테이블 생성
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

# 재귀 설정
SET SESSION cte_max_recursion_depth = 1000000; 

# 더미 생성
INSERT INTO users (name, department, 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,  
    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,  
    TIMESTAMP(DATE_SUB(NOW(), INTERVAL FLOOR(RAND() * 3650) DAY) + INTERVAL FLOOR(RAND() * 86400) SECOND) AS created_at -- 최근 10년 내의 임의의 날짜와 시간 생성
FROM cte;

# 테이블 확인
SELECT COUNT(*) FROM users;
SELECT * FROM users LIMIT 10;

# 0.281초
SELECT * FROM users
WHERE department = 'Sales'
AND created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY);

# ALL 996,810 rows
EXPLAIN SELECT * FROM users
WHERE department = 'Sales'
AND created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY);

 

CREATED_AT 컬럼을 기준으로 인덱스 생성함

# 인덱스
CREATE INDEX idx_created_at ON users (created_at);

# 성능 측정 0.016초
SELECT * FROM users
WHERE department = 'Sales'
AND created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY);

# 실행 계획 range / 1,092 rows
EXPLAIN SELECT * FROM users
WHERE department = 'Sales'
AND created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY);

# 실행 계획 세부 내용
EXPLAIN ANALYZE SELECT * FROM users
WHERE department = 'Sales'
AND created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY);

 

idx_created_at 인덱스를 활용해 인덱스를 스캔해 date 조건의 데이터를 조회했다.

그 후 department에 부합한 데이터를 필터링했다.

-> Filter: (users.department = 'Sales')  (cost=491 rows=109) (actual time=0.39..3.62 rows=101 loops=1)
    -> Index range scan on users using idx_created_at over ('2025-06-28 16:29:48' <= created_at), with index condition: (users.created_at >= <cache>((now() - interval 3 day)))  (cost=491 rows=1091) (actual time=0.384..3.55 rows=1091 loops=1)

 

이번엔 DEPARTMENT 기준으로 인덱스를 생성해서 테스트한다.

# 기존 created_at 인덱스 삭제
ALTER TABLE users DROP INDEX idx_created_at; 

# 새로운 인덱스 생성
CREATE INDEX idx_department ON users (department);

# 실행 계획
EXPLAIN SELECT * FROM users
WHERE department = 'Sales'
AND created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY);

# 실행 계획 세부 내용
EXPLAIN ANALYZE SELECT * FROM users
WHERE department = 'Sales'
AND created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY);

 

이번엔 데이터 rows가 191,314로 앞선 인덱스보다 많다.

 

왜냐하면 department 인덱스를 활용에 조건에 부합한 데이터가 created_at보다 많았다.

-> Filter: (users.created_at >= <cache>((now() - interval 3 day)))  (cost=8900 rows=63765) (actual time=0.773..270 rows=101 loops=1)
    -> Index lookup on users using idx_department (department='Sales')  (cost=8900 rows=191314) (actual time=0.206..257 rows=100000 loops=1)

 

각각 인덱스가 공존하는 경우

# created_at 인덱스 추가
CREATE INDEX idx_created_at ON users (created_at); 

# 실행 계획
EXPLAIN SELECT * FROM users
WHERE department = 'Sales'
AND created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY);

# 실행 계획 세부 내용
EXPLAIN ANALYZE SELECT * FROM users
WHERE department = 'Sales'
AND created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY);

 

key를 보면 활용한 인덱스를 idx_created_at을 활용하고 있다.

그 이유는 MySQL이 보기에도 created_at을 활용하는게 빠르다고 판단한 것이다.

 

이를 통해 알게 된 것은 데이터 row를 줄이고 싶다면 중복이 적은 컬럼을 인덱스로 활용해야한다.

 

---

 

JSCODE, MySQL 성능 최적화

https://inf.run/DzjSq