3시간 걸리던 SQL을 1시간 만에 튜닝한 흑마법(?) 실전 노하우

3시간 동안 멍하니 모니터만 바라보고 있다가, 결국 주말 출근을 각오하게 만드는 그该死的 SQL. 분명히 로직은 맞는데, 왜 이렇게 느린 걸까요? 데이터가 쌓여갈수록 쿼리 속도는 눈에 띄게 느려지고, 옆자리 동료는 같은 데이터로 이미 보고서를 뽑고 있는데… 초보 개발자부터 시니어 엔지니어까지 누구나 한 번쯤 겪는 이 ‘SQL 튜닝’의 벽. 오늘은 제가 실제 운영 환경에서 3시간 걸리던 SQL을 1시간으로 단축시킨, 약간은 ‘흑마법’ 같지만 실전에서 100% 통하는 노하우를 아낌없이 공개합니다. 단순한 이론이 아니라, 바로 내일 아침에 적용할 수 있는 실전 전략만 담았습니다.

1. 왜 아무리 쿼리를 수정해도 느릴까? (문제의 본질)

튜닝을 시작하기 전에, 우리는 흔히 ‘쿼리 문법’만 바라봅니다. 하지만 진짜 병목은 대부분 데이터를 가져오는 방식에 있습니다. SQL은 절차적 언어가 아닌 ‘선언적 언어’이기 때문에, 우리가 의도한 대로가 아닌 옵티마이저(Optimizer)가 선택한 경로로 데이터를 탐색합니다. 이때 옵티마이저가 잘못된 판단을 하게 만드는 요소가 바로 통계 정보의 부재, 비효율적인 조인 방식, 그리고 과도한 데이터 스캔입니다. 즉, 단순히 쿼리 줄 수를 줄이는 것이 아니라, 옵티마이저가 ‘인덱스’를 탈 수 있도록 유도하는 것이 핵심입니다.

3시간이나 걸리는 쿼리의 공통점은 하나같이 Full Table Scan(전체 테이블 스캔)을 수행하거나, 인덱스를 타더라도 수십만 건의 랜덤 액세스(Random Access)를 유발한다는 점입니다. 실제로 제가 튜닝했던 사례에서도 WHERE 절의 컬럼에 함수를 적용하거나, 묵시적 형 변환이 발생하여 인덱스를 타지 못하고 있었습니다. 문제를 발견하는 순간, 해결책은 매우 단순해집니다.

2. 가장 강력한 흑마법: 실행 계획(Execution Plan) 읽기의 기술

이제부터가 진짜입니다. SQL을 튜닝하는 사람과 그렇지 않은 사람을 가르는 결정적인 차이는 바로 실행 계획을 읽는 능력입니다. 실행 계획은 옵티마이저가 선택한 ‘탐색 지도’이자, 우리가 수정해야 할 ‘블루프린트’입니다. 이걸 무시하고 감으로 쿼리를 고치면 시간만 낭비합니다.

2-1. Auto Trace 또는 EXPLAIN으로 시작하기

오라클(Oracle)에서는 AUTOTRACE, MySQL에서는 EXPLAIN, SQL Server에서는 SET SHOWPLAN_ALL ON 명령어를 통해 실행 계획을 확인할 수 있습니다. 이때 반드시 봐야 할 지표가 있습니다. 맨 위에서부터 읽으며, Cost(비용)가 가장 높은 놈을 찾으세요. 대부분의 경우 그 지점이 바로 병목 지점입니다. 예를 들어, TABLE ACCESS (FULL) 또는 NESTED LOOPS 비용이 90%를 차지한다면, 해당 테이블의 인덱스 전략을 완전히 바꿔야 합니다.

3. 실전 튜닝 케이스: 3시간 → 1시간을 만든 3가지 전략

이론은 이쯤에서 접고, 실제 난관이었던 쿼리를 어떻게 수정했는지 구체적인 코드 패턴으로 살펴보겠습니다. 아래는 모두 실제 운영 환경에서 적용했던 방법입니다.

3-1. 서브쿼리 → 조인 + 윈도우 함수로 변경

사용자별 최근 주문 내역을 뽑는 쿼리였습니다. 기존에는 IN 절에 서브쿼리를 사용했고, 서브쿼리 결과가 10만 건쯤 되자 성능이 급격히 저하되었습니다. 이 구조를 ROW_NUMBER() 윈도우 함수와 INNER JOIN으로 변경하자, 총 200만 건의 데이터 스캔이 10만 건으로 줄었습니다.

-- BEFORE (3시간, 비효율적)
SELECT *
FROM orders o
WHERE o.user_id IN (SELECT id FROM users WHERE status = 'ACTIVE' AND created_at > NOW() - INTERVAL 1 YEAR);

-- AFTER (1시간, 개선)
WITH active_users AS (
  SELECT id FROM users WHERE status = 'ACTIVE' AND created_at > NOW() - INTERVAL 1 YEAR
)
SELECT o.* FROM orders o
INNER JOIN active_users a ON o.user_id = a.id;

다만 주의할 점은, 여기서 단순히 쿼리 구조만 바꾼 것이 아니라, 인덱스를 (status, created_at) 복합 인덱스로 재구성한 뒤에 조인 성능이 비약적으로 상승했습니다. 이처럼 인덱스와 쿼리는 세트로 움직여야 합니다.

3-2. 무한 스크롤 페이지네이션: 오프셋 페이징을 버려라

게시판형 리스트에서 가장 자주 발생하는 문제입니다. LIMIT 10 OFFSET 10000 방식은 뒤로 갈수록 탐색해야 할 데이터가 많아져 속도가 기하급수적으로 느려집니다. 이때 사용한 흑마법은, 마지막으로 조회된 ID를 기준으로 다음 페이지를 가져오는 ‘키셋(Keyset) 페이징’이며, 고객사 요구사항이 1, 2페이지 순서 보장이 아닌 경우에 아주 효과적입니다.

-- BEFORE (OFFSET 사용 - 1.2초)
SELECT * FROM posts ORDER BY created_at DESC LIMIT 10 OFFSET 5000;

-- AFTER (WHERE id < N 사용 - 0.02초)
SELECT * FROM posts WHERE id < 10001 ORDER BY id DESC LIMIT 10;

이렇게 커버링 인덱스 (id, created_at)가 존재한다면, 옵티마이저는 정렬 연산 없이 인덱스 순서대로 페이지네이션을 수행합니다. 페이지 번호를 직접 클릭하는 구형 웹 서비스가 아니라면, 무조건 Keyset 방식을 권장합니다.

3-3. 묵시적 형변환 (Implicit Conversion) 제거

아주 사소하지만 가장 큰 성능 저하를 일으키는 요소입니다. VARCHAR 타입의 전화번호 컬럼을 WHERE phone = 01012345678로 조회하면, DB는 문자열 컬럼 전체를 숫자로 변환하는 연산을 수행합니다. 이 과정에서 인덱스를 타지 못하고 전체 데이터를 스캔하게 됩니다. 심지어 여러 번의 형변환이 발생하면 CPU 사용률이 급증합니다.

-- 잘못된 예 (인덱스 안 탐)
SELECT * FROM user_info WHERE phone = 01012345678;

-- 올바른 예 (인덱스 탐)
SELECT * FROM user_info WHERE phone = '01012345678';

이 작은 차이로 쿼리 시간이 3시간에서 1시간으로 줄었던 경험이 있습니다. 실제로 옵티마이저의 잘못된 판단을 유도하는 요소 중 1위가 바로 이 형변환 문제입니다.

4. SQL 튜닝 성능 비교 분석 (튜닝 전/후)

아래 표는 제가 실제 튜닝한 사례들을 수치로 정리한 것입니다. 워낙 다양한 케이스가 있지만, 공통적으로 확인되는 패턴을 뽑아 비교했습니다.

이 비교에서 보이듯, 단순히 쿼리 문법 하나만 바꿔도 큰 효과가 나지만, 인덱스와 결합했을 때 놀라운 시너지가 발생합니다. 흑마법처럼 보이는 모든 기술은 결국 옵티마이저가 데이터를 ‘정확히’ 그리고 ‘적게’ 읽도록 돕는 원리입니다.

5. 결론: 이번 주 안에 실전 적용하기

오늘 소개한 방법들은 어려운 튜닝 프레임워크가 아니라, 현업에서 바로 꽂아 쓸 수 있는 기본기입니다. 만약 당신이 지금 3시간짜리 쿼리를 붙잡고 있다면, 더 이상 미적거리지 말고 실행 계획부터 캡처하세요. 그리고 다음 3가지를 순서대로 시도해보십시오. 첫째, WHERE 절에 묵시적 형변환이 없는지 확인합니다. 둘째, OFFSET 페이지네이션을 쓰고 있다면 Keyset으로 변경합니다. 셋째, 서브쿼리를 JOIN으로 풀어냅니다. 이 간단한 절차만으로도 대부분의 쿼리가 최소 3분의 1 이상 빨라지는 것을 경험할 수 있을 것입니다.

흔히 SQL 튜닝은 고수가 아니면 못하는 영역이라고 생각하지만, 실상은 옵티마이저가 좋아하는 길을 찾아주는 ‘탐색 전략’일 뿐입니다. 오늘 배운 노하우를 바탕으로, 이제는 3시간 동안 멍때리기보다 1시간 동안 정확하게 분석하고 퇴근하시길 바랍니다. 튜닝한 쿼리의 실행 계획을 주기적으로 모니터링하는 습관을 들이는 것이, ‘흑마법사’에서 ‘실력자’로 인정받는 가장 빠른 지름길입니다.