[MySQL] SQL문 최적화

오늘의 주제는 "SQL 최적화" 입니다.

최근 지인으로부터 쿼리 튜닝 요청을 받았습니다.
15초 걸리던 쿼리를 0.6초로 줄이는 과정에서,
많은 엔지니어들이 놓치기 쉬운 SQL 최적화 포인트들을 정리해보았습니다.

---

쿼리 소개

※ 내부 보안상 쿼리 내부의 테이블명 / 컬럼명은 존재하지 않는 가상의 명칭을 사용하였음을 알려드립니다!

1️⃣ 회원가입 7일, 14일된 A지역 회원 조회 쿼리

특이사항: 일자는 서버에서 변수로 넘겨줄 예정이며, 7일차 14일차 따로따로 쿼리 수행 예정

SELECT user_id, name
  FROM user
 WHERE area = 'A'
   AND DATE(created_at) = @created_at
; -- 454건 / 556ms

---

2️⃣ 회원가입 30일, 90일된 A지역 회원 중, 서비스를 한번도 이용하지 않은 회원 조회 쿼리

특이사항: 일자는 서버에서 변수로 넘겨줄 예정이며, 30일차 90일차 따로따로 쿼리 수행 예정

SELECT a.user_id, a.user_name
 FROM user a
 LEFT JOIN service b ON a.user_id = b.user_id
WHERE a.area = 'A'
  AND DATE(a.created_at) = @created_at
  AND b.user_id IS NULL
; -- 9건 / 378ms

---

3️⃣ 미로그인 45일차 A지역 회원 중, 무료 서비스 플랜을 이용하거나 서비스를 한번도 이용하지 않은 회원 조회 쿼리

SELECT DISTINCT a.email, a.user_name
FROM user a
LEFT JOIN service b ON a.user_id = b.user_id
WHERE a.area = 'A'
  AND DATE(a.last_login_time) = @last_login_time
  AND (b.user_id IS NULL OR b.plan = 'free')
; -- 23건 / 14.786s

---

(2), (3) 번 SQL문에서 Anti-Join 도 잘 작성했으며 이대로 배포한다면 서비스에는 전혀 문제가 없습니다.

그러나, (3) 번 SQL 문의 경우 23건을 조회하는 과정에서 약 15초 가까이 시간이 발생했는데
과연 고객은 15초라는 시간을 어떻게 생각할까요 ??

이처럼 고객이 겪을 불편함을 최소화 시키면서, 데이터베이스의 부담을 최소화 하기 위해서는 쿼리 튜닝 작업이 필수적으로 선행되어야 합니다.

---

📊 테이블 구조 확인

쿼리 튜닝을 진행하기 전, 조회에 필요한 테이블의 구조를 확인해보겠습니다.

▶ user 테이블

SHOW CREATE TABLE user;

CREATE TABLE user(
  user_id INT(11) NOT NULL AUTO_INCREMENT,
  user_name VARCHAR(100) NOT NULL,
  email VARCHAR(100) NOT NULL,
  area VARCHAR(10) NOT NULL,
  last_login_time DATETIME DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(user_id),
  KEY idx_user_name (user_name),
  KEY idx_last_login_time (last_login_time)
);

▶ service 테이블

SHOW CREATE TABLE service;

CREATE TABLE service(
  service_id INT(11) NOT NULL AUTO_INCREMENT,
  user_id INT(11) NOT NULL,
  plan VARCHAR(10) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(service_id),
  UNIQUE KEY uk_user_id (user_id),
  KEY idx_created_at (created_at)
);

---

⚠️ SQL문 문제점 분석

위의 테이블 구조를 바탕으로 작성된 SQL문의 문제점을 정리하여 적어보았습니다.

🔍 주요 문제점

1. DATE() 함수 사용으로 인한 인덱스 미사용

WHERE DATE(created_at) = @created_at

컬럼에 함수를 적용하면 MySQL 옵티마이저는 해당 컬럼의 인덱스를 활용할 수 없습니다.

왜 인덱스를 사용하지 못할까요?

인덱스는 원본 컬럼 값을 기준으로 정렬되어 저장됩니다. 하지만 DATE() 함수를 사용하면

• created_at 컬럼의 원본 값(DATETIME)이 변환되어
• 인덱스에 저장된 값과 비교할 수 없게 됩니다
• 결과적으로 MySQL은 Full Table Scan을 수행하게 됩니다

예시

-- 인덱스 사용 불가 ❌
WHERE DATE(created_at) = '2025-01-01'

-- 인덱스 사용 가능 ✅
WHERE created_at >= '2025-01-01' 
  AND created_at < '2025-01-02'

실행계획으로 확인하면

-- DATE() 함수 사용 시
EXPLAIN SELECT * FROM user WHERE DATE(created_at) = '2025-01-01';
-- type: ALL (전체 테이블 스캔)
-- key: NULL (인덱스 미사용)

-- 범위 조건 사용 시
EXPLAIN SELECT * FROM user 
WHERE created_at >= '2025-01-01' 
  AND created_at < '2025-01-02';
-- type: range (범위 스캔)
-- key: idx_created_at (인덱스 사용)

2. BETWEEN 사용 시 주의사항

BETWEEN은 그 자체로 문제가 되는 것은 아닙니다. 다만 사용 방식에 따라 성능이 달라질 수 있습니다:

-- ✅ 인덱스 활용 가능
WHERE created_at BETWEEN '2025-01-01' AND '2025-01-31'

-- ⚠️ 타입이 일치하지 않으면 묵시적 형변환 발생 가능
WHERE created_at BETWEEN 20250101 AND 20250131  -- 숫자형과 DATETIME 비교

권장 방식: 명시적인 범위 조건 사용

WHERE created_at >= '2025-01-01' 
  AND created_at < DATE_ADD('2025-02-01', INTERVAL 1 DAY)

3. LIKE 사용 시 인덱스 활용 여부

• LIKE 'keyword%' (앞 일치): 인덱스 Range Scan 가능 ✅
• LIKE '%keyword' (뒷 일치): Full Table Scan 발생 ❌
• LIKE '%keyword%' (중간 일치): Full Table Scan 발생 ❌

---

🔧 쿼리 튜닝 결과

1️⃣ 회원가입 7일, 14일된 A지역 회원 조회 쿼리

AS-IS

SELECT user_id, user_name
FROM user
WHERE area = 'A'
  AND DATE(created_at) = @created_at
; -- 454건 / 556ms

TO-BE

-- 인덱스 추가
ALTER TABLE user ADD KEY idx_created_at (created_at);
-- 154ms

-- 개선된 쿼리
SELECT user_id, user_name
FROM user
WHERE area = 'A'
  AND created_at >= @created_at
  AND created_at < DATE_ADD(@created_at, INTERVAL 1 DAY)
; -- 454건 / 132ms

---

2️⃣ 회원가입 30일, 90일된 A지역 회원 중, 서비스를 한번도 이용하지 않은 회원 조회 쿼리

AS-IS

SELECT a.user_id, a.user_name
FROM user a
LEFT JOIN service b ON a.user_id = b.user_id
WHERE a.area = 'A'
  AND DATE(a.created_at) = @created_at
  AND b.user_id IS NULL
; -- 9건 / 378ms

TO-BE

SELECT a.user_id, a.user_name
FROM user a
LEFT JOIN service b ON a.user_id = b.user_id
WHERE a.area = 'A'
  AND a.created_at >= @created_at
  AND a.created_at < DATE_ADD(@created_at, INTERVAL 1 DAY)
  AND b.user_id IS NULL
; -- 9건 / 62ms

---

3️⃣ 미로그인 45일차 A지역 회원 중, 무료 서비스 플랜을 이용하거나 서비스를 한번도 이용하지 않은 회원 조회 쿼리

AS-IS

SELECT DISTINCT a.email, a.user_name
FROM user a
LEFT JOIN service b ON a.user_id = b.user_id
WHERE a.area = 'A'
  AND DATE(a.last_login_time) = @last_login_time
  AND (b.user_id IS NULL OR b.plan = 'free')
; -- 23건 / 14.786s

TO-BE

SELECT a.email, a.user_name
FROM user a
LEFT JOIN service b ON a.user_id = b.user_id
WHERE a.area = 'A'
  AND a.last_login_time >= @last_login_time
  AND a.last_login_time < DATE_ADD(@last_login_time, INTERVAL 1 DAY)
  AND (b.user_id IS NULL OR b.plan = 'free')
GROUP BY a.email, a.user_name
; -- 23건 / 664ms

---

📈 성능 개선 결과

SQL문	AS-IS	TO-BE	개선율
1번	556ms	132ms	76.3% ⬇
2번	378ms	62ms	83.6% ⬇
3번	14.786s	664ms	95.5% ⬇

---

⚠️ 주의사항

💡 user 테이블의 created_at 컬럼에 대한 인덱스를 생성함으로써 엄청난 기대효과를 확인할 수 있었습니다.

그러나, 이렇게 인덱스를 추가할 경우 기존에 사용하던 SQL 문의 실행계획이 변경될 수 있기 때문에 신중할 필요가 있으며 사전에 서비스에 사용되는 쿼리를 전수 조사하여 실행계획을 확인할 필요가 있습니다.

---

💡 핵심 포인트

▶ 인덱스 컬럼은 절대 가공하지 않기 - 함수 사용 시 인덱스 활용 불가
▶ 범위 조건 활용 - DATE() 대신 >=, < 비교 연산자 사용
▶ 적절한 인덱스 설계 - 자주 조회되는 컬럼에 인덱스 생성
▶ 실행계획 확인 - 인덱스 추가 시 전체 서비스 영향도 검토 필수