MySQL 슬로우 쿼리 잡으면서 제가 저지른 실수와 해결법

MySQL에서 슬로우 쿼리를 잡으려고 하다가 생각보다 삽질을 많이 했어요. 쿼리 실행이 느려서 고생만 하다 보니, 처음에는 원인을 제대로 못 찾아 몇 번이고 같은 작업을 반복했거든요. 그래서 이 경험을 정리해두면 저처럼 헤매는 분들에게 도움이 될 것 같아서 글을 쓰게 됐습니다.

이 글에서는 제가 직접 겪은 슬로우 쿼리 잡는 과정과, 특히 실수하고 반려되었던 부분, 그리고 이를 해결하면서 알게 된 원인들을 구체적으로 다뤄보려고 해요. 실행 결과와 함께 어떻게 최적화했는지 알려드릴게요.

개발 환경 / 버전 정보

제가 사용한 MySQL 버전은 8.0.32이고, 쿼리 최적화 테스트는 로컬 환경에서 진행했어요. 데이터는 약 200만 건 정도 있고, 인덱스는 일부 컬럼에만 적용된 상태였습니다.

슬로우 쿼리 잡을 때 저는 이렇게 했어요

사실 이 부분이 제일 어려웠는데요, 처음에는 슬로우 쿼리 로그를 켜고 쿼리만 보고 빠르게 판단하려고 했거든요. 그런데 이게 생각보다 정확하지 않았어요. 그래서 저는 EXPLAIN ANALYZE를 적극 활용하기로 했습니다.

EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 12345;

이렇게 실행하면 MySQL이 실제 쿼리를 어떻게 처리하는지 자세하게 보여주거든요. 저는 이걸 보고 인덱스 스캔이 제대로 되는지, 불필요한 풀 테이블 스캔이 있는지 알 수 있었어요.

초반에 제가 저질렀던 큰 실수는 조건절에 인덱스가 없는 컬럼을 사용한 거였어요. 그래서 쿼리 실행이 엄청 오래 걸렸거든요. 나중에 인덱스를 추가하고 나니 속도가 눈에 띄게 빨라졌습니다.

-- 문제의 쿼리 (인덱스 없음)
SELECT * FROM orders WHERE order_status = 'pending';

-- 인덱스 추가 후
CREATE INDEX idx_order_status ON orders(order_status);

이 부분에서 제가 한 번에 못 알아채서 쿼리를 여러 번 갈아엎었던 기억이 나네요.

저는 이런 부분에서 막혔어요

엄청 많이 헷갈렸던 점 중 하나는 조인(join) 쿼리를 쓸 때였어요. 조인 조건이 제대로 되어 있지 않으면 MySQL이 엄청난 양의 데이터를 스캔하고, 이게 슬로우 쿼리를 일으킨다는 사실을 경험으로 알게 됐습니다.

예전에는 그냥 무작정 조인해서 데이터를 끌어왔는데, 실행 시간을 확인하고는 완전 깜짝 놀랐죠. 그래서 조인 조건에 쓰이는 컬럼에도 인덱스를 꼭 적용해야 한다는 걸 실제로 몸으로 체감했습니다.

SELECT o.order_id, u.user_name
FROM orders o
JOIN users u ON o.user_id = u.user_id
WHERE o.order_status = 'completed';

여기서 orders.user_id와 users.user_id 모두 인덱스가 있어야 속도가 확 빠르더라고요.

실제로 저랑 비슷한 실수를 한 코드 예시

아래 코드는 제가 처음에 작성했다가 슬로우 쿼리로 반려된 쿼리예요.

SELECT o.*, u.name
FROM orders o
JOIN users u ON o.user_id = u.user_id
WHERE o.created_at > '2023-01-01'
AND u.status = 'active';

이 쿼리에서 o.created_at과 u.status에 인덱스가 없었어요. 당연히 실행이 느릴 수밖에 없죠.

인덱스를 추가하면서 바로 개선됐는데, 처음에는 이거 몰라서 여러 번 다시 해야 했습니다.

CREATE INDEX idx_orders_created_at ON orders(created_at);
CREATE INDEX idx_users_status ON users(status);

여기서 많이 틀립니다, 쿼리 작성 시 주의점

사실 조건절에 함수가 들어가는 경우가 있는데요, 이거 인덱스가 무력화됩니다. 예를 들어 날짜 컬럼에 DATE() 함수를 쓰면, 인덱스가 적용이 안 되는 거예요. 그래서 쿼리가 무조건 느려지더라고요.

제가 처음에 이런 함수를 조건에 바로 써서 또 쿼리 반려당한 적 있는데, 다음과 같이 바꿔야 인덱스를 살릴 수 있습니다.

-- 안 좋은 예
WHERE DATE(created_at) = '2023-06-10'

-- 좋은 예
WHERE created_at >= '2023-06-10 00:00:00' AND created_at < '2023-06-11 00:00:00'

실행 결과로 본 개선 효과

최적화 전후의 실행 계획을 비교해보니, 인덱스가 제대로 사용되면서 풀 테이블 스캔이 사라졌어요. 덕분에 쿼리 속도가 평균 10배 이상 빨라졌습니다.

-- 최적화 전
EXPLAIN ANALYZE SELECT * FROM orders WHERE order_status = 'pending';
-- 테이블 스캔 약 5초 소요

-- 최적화 후
EXPLAIN ANALYZE SELECT * FROM orders WHERE order_status = 'pending';
-- 인덱스 스캔 0.3초 소요

자주 물어보시는 것들

Q. 슬로우 쿼리 로그는 어떻게 켜나요?

A. my.cnf 또는 my.ini 파일에 slow_query_log=ON과 long_query_time=1 같은 설정을 추가한 후, 서버를 재시작하면 됩니다.

Q. 인덱스가 너무 많으면 문제되지 않을까요?

A. 네, 인덱스가 많으면 쓰기 작업(insert/update/delete)이 느려지기 때문에 꼭 필요한 컬럼 위주로만 추가하는 게 중요해요.

제 경험상 슬로우 쿼리 잡기는 단순히 쿼리만 보는 게 아니라 실행 계획도 보고, 인덱스 설계도 같이 고민해야 한다는 걸 알게 됐어요. 특히 조건절에 함수 사용이나 조인 시 인덱스 미적용 이런 실수 때문에 여러 번 쿼리를 다시 작성했는데, 그 과정에서 배운 게 많았거든요.