MySQL 슬로우 쿼리 잡으면서 알게 된 원인들과 해결책들

MySQL에서 슬로우 쿼리가 자꾸 걸려서 원인을 찾다 보니 생각보다 복잡한 부분이 많았어요. 쿼리문도 여러 번 고치고, 인덱스 붙이고, 실행 계획 확인하면서 삽질이 한두 번이 아니었거든요.

이 글에서는 제가 직접 경험했던 슬로우 쿼리 원인과 그 해결 방법을 FAQ 형식으로 정리해봤어요. 그래서 MySQL 쿼리 최적화에 대해 궁금했던 점은 대부분 이 글 한 편으로 해결될 거예요.

MySQL 슬로우 쿼리 로그 설정은 이렇게 합니다

사실 이 부분이 가장 기본인데도 많이들 헷갈려하시더라고요. 슬로우 쿼리를 잡으려면 로그부터 제대로 켜야 하거든요.

my.cnf (또는 my.ini)에 다음 설정을 추가하거나 확인하세요.

[mysqld]
slow_query_log=ON
slow_query_log_file=/var/log/mysql/mysql-slow.log
long_query_time=1
log_queries_not_using_indexes=ON

long_query_time은 실행 시간이 몇 초 이상인 쿼리를 기록할지 정하는 건데, 저는 1초로 낮춰서 자주 체크했어요. 그리고 인덱스를 안 쓰는 쿼리도 따로 기록하게 설정하면 잡아내기가 편해집니다.

MySQL 슬로우 쿼리 잡으면서 알게 된 원인들 관련 정보

Q. 슬로우 쿼리 로그에 쿼리가 안 찍혀요. 왜 그럴까요?

답변: 로그 설정이 제대로 안 됐거나, 서버를 재시작 안 했을 가능성이 큽니다. 설정 파일을 바꾼 뒤에는 꼭 MySQL 서비스를 재시작해야 해요.

또 한 가지는 long_query_time이 너무 높게 설정돼서 해당 쿼리가 그 기준을 못 넘는 경우예요. 쿼리 실행 시간이 빠르다면 로그가 안 찍힐 수밖에 없죠.

Q. 쿼리 실행 계획은 어떻게 봐야 하나요?

많이들 헷갈려하시는 게 바로 실행 계획(EXPLAIN) 해석이에요. 저는 이렇게 했더니 이해가 훨씬 쉬웠거든요.

쿼리 앞에 EXPLAIN을 붙여서 실행하면 결과가 나오는데요, 주요 포인트는 type과 possible_keys, 그리고 rows입니다.

type은 조인의 효율성을 나타내는데 ALL은 테이블 전체 스캔이라 느리고, ref, range가 상대적으로 좋습니다.

possible_keys는 해당 쿼리에서 쓸 수 있는 인덱스 리스트예요. 이 컬럼에 아무 값도 없으면 인덱스가 하나도 안 맞는다는 뜻이라 볼 수 있죠.

EXPLAIN SELECT * FROM orders WHERE customer_id = 1234;

이렇게 실행하면 어떤 인덱스를 쓰는지 간단히 체크할 수 있어요.

MySQL 슬로우 쿼리 잡으면서 알게 된 원인들 관련 정보

Q. 인덱스를 만들어도 슬로우 쿼리가 안 잡히는 이유는 뭘까요?

이거 저도 한참 헤맸는데, 쿼리 조건이 인덱스 컬럼과 안 맞거나, 함수가 붙어 있으면 인덱스가 무시될 수 있어요.

예를 들어, WHERE DATE(created_at) = '2023-05-01' 같은 조건은 created_at에 인덱스가 있어도 인덱스가 안 써집니다. 왜냐하면 컬럼에 함수가 적용되면 MySQL이 일반 인덱스를 못 쓰거든요.

이럴 땐 쿼리를 분리하거나, 생성된 날짜 범위 조건처럼 바꾸는 게 필요해요.

Q. 슬로우 쿼리를 잡기 위해 꼭 알아둬야 할 인덱스 팁

인덱스는 무조건 많이 만든다고 좋은 게 아니더라고요. 제가 실패한 경험을 토대로 말씀드리자면, 복합 인덱스는 컬럼 순서가 엄청 중요해요.

• 복합 인덱스에서 쿼리가 자주 쓰는 컬럼이 앞에 와야 합니다.
• WHERE 조건이나 JOIN 조건에 자주 쓰이는 컬럼 위주로 인덱스를 설계하세요.
• 범위 조건(WHERE col > 100) 다음에 오는 컬럼들은 인덱스 활용도가 떨어질 수 있어요.

제가 직접 짠 쿼리를 보여드릴게요.

CREATE INDEX idx_orders_customer_status ON orders(customer_id, status);

위처럼 고객 ID와 주문 상태 둘 다 WHERE 조건에 자주 들어가서 이렇게 인덱스를 만들었는데, 실행 계획을 보니 인덱스가 잘 쓰이는 걸 확인할 수 있었어요.

MySQL 슬로우 쿼리 잡으면서 알게 된 원인들 관련 정보

Q. JOIN이 많은 쿼리 슬로우할 때는 어떻게 고쳤나요?

여기서 삽질했던 부분 중 하나가 무조건 JOIN을 다 때려넣었는데, 실제로 필요한 컬럼만 골라서 서브쿼리로 분리하는 게 훨씬 빠르다는 걸 알게 됐어요.

예전엔 이런 쿼리를 썼거든요.

SELECT o.id, c.name, p.product_name
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN products p ON o.product_id = p.id
WHERE o.status = 'completed';

이걸 서브쿼리로 나누면서 필요한 부분만 먼저 필터링하니까 훨씬 성능이 개선되었어요.

SELECT o.id, c.name, p.product_name
FROM
  (SELECT * FROM orders WHERE status = 'completed') o
JOIN customers c ON o.customer_id = c.id
JOIN products p ON o.product_id = p.id;

Q. 슬로우 쿼리 원인이 함수 사용 때문일 때는 어떻게 해야 하나요?

함수를 WHERE 조건에 쓰면 MySQL이 인덱스 활용을 못 할 때가 많아요. 저도 DATE 함수가 붙은 조건에서 많이 걸렸거든요.

그럴 땐 함수 사용 대신 범위 비교로 바꾸는 게 좋아요.

-- 안 좋은 예
WHERE DATE(created_at) = '2023-05-01'

-- 좋은 예
WHERE created_at >= '2023-05-01 00:00:00' AND created_at < '2023-05-02 00:00:00'

이렇게 하면 created_at 컬럼에 인덱스가 있으면 잘 잡아줘서 쿼리가 훨씬 빨라졌어요.

Q. 통계용 큰 데이터 쿼리는 어떻게 최적화했나요?

통계성 쿼리 같은 경우에는 보통 많은 데이터 스캔이 불가피한데요, 저는 주로 별도의 요약 테이블을 만들어서 운영 중인 데이터에서 일정 주기로 집계하는 방식을 썼어요.

예를 들어, 매일 주문 수량 합계를 미리 집계한 테이블을 만들어서 실시간 쿼리는 그 테이블만 조회하는 식입니다. 이렇게 하니까 대폭 쿼리 시간이 줄었어요.

Q. 슬로우 쿼리 잡으면서 자주 묻는 질문들

Q1. 인덱스를 너무 많이 달면 오히려 쿼리가 느려지나요?

A. 네, 맞아요. 인덱스가 많으면 쓰기는 느려지고, 관리 비용이 커져서 전체 성능 저하 요인이 될 수 있어요. 읽기 작업이 많으면 괜찮지만, 자주 쓰기 작업이 있다면 신중하게 인덱스를 설계하세요.

Q2. 슬로우 쿼리가 특정 시간대에 몰려서 발생하는데 왜 그런 건가요?

A. 보통 대량 트래픽 또는 배치 작업 시간대와 겹치기 때문인데요, 이런 경우는 배치 시간을 분산하거나, 쿼리 튜닝과 하드웨어 리소스 확충을 같이 고민해야 합니다.

Q3. slow query log 말고 쿼리 성능 분석할 다른 방법이 있나요?

A. 네, MySQL 프로파일링 기능이나 SHOW PROFILE, PERFORMANCE_SCHEMA 등을 활용할 수도 있습니다. 하지만 가장 직관적인 건 역시 슬로우 쿼리 로그죠.

Q4. 쿼리가 느린데 인덱스는 잘 되어 있는데 왜 그럴까요?

A. 인덱스가 있어도 데이터 분포나 조인 방식, 하드웨어 이슈, 잠금(lock) 문제 때문일 수 있어요. EXPLAIN 결과만 믿지 말고 실제 실행 시간을 ANALYZE FORMAT=JSON으로 확인하거나 서버 상태를 체크해 보세요.

Q5. 데이터가 계속 쌓이는데 주기적으로 어떻게 관리하나요?

A. 데이터 파티셔닝이나 아카이빙 전략을 고민하는 게 좋아요. 오래된 데이터는 별도 테이블로 이전하거나 삭제하는 정책을 세우면 쿼리 속도에 큰 도움이 됩니다.

슬로우 쿼리를 잡으면서 인덱스 설계, 실행 계획 분석, 함수 사용 주의 등 다양한 부분에서 배웠던 경험들이었어요. 데이터가 커질수록 쿼리 하나가 전체 서비스 퍼포먼스에 미치는 영향이 커지니, 꾸준히 모니터링하면서 최적화하는 습관이 중요하다고 느꼈습니다.

📌 함께 보면 좋은 글

• PostgreSQL과 MySQL, 실무에서 비용으로 선택하는 기준은?
• Redis pub/sub를 메시지 큐로 써보니 비용 차이가 이렇게 났어요
• MySQL 8.0 업그레이드하면서 깨졌던 쿼리들, 이렇게 해결했어요