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

MySQL에서 슬로우 쿼리가 자꾸 걸려서 성능 저하가 심각해졌어요. 저는 이걸 잡으려고 여러 방법을 시도했는데, 생각보다 조정할 부분이 많더라고요. 그래서 직접 하나하나 세팅하고 쿼리도 분석하면서 알게 된 원인들과 해결 절차를 정리해봤습니다.

이 글에서는 슬로우 쿼리 로그 세팅부터 분석, 그리고 쿼리 최적화까지 제가 진행한 단계별 과정과 명령어, 결과 화면을 차근차근 보여드릴게요. 저처럼 처음 슬로우 쿼리 잡는 분들이라면 이 글 하나로 고민 덜 수 있을 거예요.

1. 슬로우 쿼리 로그 활성화부터 시작하기

사실 이 부분이 가장 기본인데도 의외로 놓치기 쉬워요. 저는 아래 순서대로 설정했는데, 이게 안 되어 있으면 쿼리가 아무리 느려도 로그가 안 쌓여서 원인을 못 찾거든요.

먼저 MySQL 설정 파일(my.cnf 또는 my.ini)을 열어 아래 내용을 확인했어요.

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

여기서 slow_query_log=1이 슬로우 쿼리 로그 활성화, slow_query_log_file는 로그 저장 위치, long_query_time=2는 2초 이상 걸리는 쿼리를 기록한다는 뜻이에요. 그리고 log_queries_not_using_indexes=1 설정은 인덱스를 안 쓰는 쿼리도 잡아내서 문제있는 쿼리를 더 쉽게 찾게 해줬어요.

위처럼 설정한 후 MySQL을 재시작했습니다.

sudo systemctl restart mysql

이제 슬로우 쿼리가 어느 정도 쌓이기 시작할 때까지 기다렸는데, 실제 서비스 중이라 바로 확인이 필요하면 테스트 쿼리를 한두 개 실행해 보는 것도 좋아요.

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

2. 슬로우 쿼리 로그 파일 분석 방법

그런데 여기서 많이들 헷갈려하는 게 슬로우 쿼리 로그 파일이 그냥 텍스트라 분석하기 어렵다는 거예요. 그래서 저는 mysqldumpslow와 더 편리한 pt-query-digest 툴을 활용했습니다.

먼저 mysqldumpslow로 간단 요약부터 해봤는데요.

sudo mysqldumpslow -s t /var/log/mysql/mysql-slow.log
t: 시간별 정렬, 기본 -c 옵션으로 쿼리 횟수 순 정렬도 가능

이 결과를 보고 어떤 쿼리가 느린지 대략 파악할 수 있었어요. 하지만 쿼리 구조가 복잡하거나 더 상세한 통계, 바인딩 값 등이 궁금하면 pt-query-digest가 훨씬 좋습니다.

저도 Percona Toolkit을 설치한 뒤 이렇게 사용해 봤어요.

pt-query-digest /var/log/mysql/mysql-slow.log > slow-query-report.txt

보고서가 너무 길긴 한데, 특히 실행 시간이 오래 걸린 쿼리, 빈도, 잠재적 인덱스 문제 등을 한눈에 파악할 수 있어서 좋았어요.

3. 실제 쿼리 최적화 절차

분석 보고서에 나온 쿼리를 하나씩 직접 실행해 보면서 느린 원인을 찾아봤어요. 보통 느린 쿼리는 인덱스 미사용, 불필요한 조인, 데이터 범위 지정이 부적절한 경우가 많더라고요.

제가 특히 발견한 문제는 아래처럼 인덱스를 전혀 활용하지 않은 쿼리였어요. 이걸 실행하면 실측 시간도 5초 이상 걸렸습니다.

EXPLAIN SELECT * FROM orders WHERE customer_name LIKE '%김%';

이 경우에는 LIKE '%값%' 형태가 일반 인덱스 활용을 못 하게 만들더라고요. 그래서 저는 아래처럼 쿼리 구조를 바꾸거나, 전문 검색 엔진 도입을 검토했어요.

방법 1. 와일드카드 위치 변경

SELECT * FROM orders WHERE customer_name LIKE '김%';

이렇게 하면 인덱스가 타서 실행 속도가 훨씬 빨라졌어요.

방법 2. FULLTEXT 인덱스 사용

테이블에 FULLTEXT 인덱스를 추가하고 MATCH() AGAINST() 문법을 써서 검색하는 방법도 있는데, 저 같은 경우는 인덱스 추가부터 쿼리 수정까지 비교적 간단히 처리했어요.

ALTER TABLE orders ADD FULLTEXT(customer_name);

SELECT * FROM orders WHERE MATCH(customer_name) AGAINST('김' IN NATURAL LANGUAGE MODE);

이렇게 하니 대량 데이터도 빠르게 검색돼서 만족스러웠습니다.

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

4. 인덱스 활용과 EXPLAIN로 쿼리 확인하기

저는 슬로우 쿼리 잡을 때 편하게 쿼리 계획을 보고 싶어서 EXPLAIN을 자주 썼는데, 이게 생각보다 강력하더라고요.

예를 들어 이런 식으로 쿼리를 확인합니다.

EXPLAIN SELECT order_id, order_date FROM orders WHERE customer_id=12345;

결과에서 type 컬럼이 const나 ref면 인덱스를 잘 타고 있다는 뜻이고, ALL이면 테이블 풀 스캔이라 느릴 가능성이 높아요. 저는 이걸 보면서 꼭 필요한 컬럼에 인덱스를 추가하는 방법으로 개선했습니다.

5. 여기서 삽질했던 부분과 해결법

이 에러가 왜 나는지 한참 찾았는데, 저는 초기 설정을 제대로 안 해서 슬로우 쿼리가 아예 기록이 안 됐던 적이 있어요.

ERROR 1290 (HY000): The MySQL server is running with the --skip-log-error option so it cannot write error log

이때는 설정 파일에 덮어쓰기나 오타가 있었는지 꼼꼼히 확인하고, MySQL 재시작도 똑바로 했는지 봐야 했어요.

그리고 쿼리 최적화 시 LIKE '%값%' 쓰면 인덱스 적용 안 되는 건 꽤 유명한 문제인데, 저는 이걸 간과해서 한참 삽질했습니다. 초반에 로그만 보고 무작정 쿼리 수정을 하면 되겠지 했는데, EXPLAIN 확인이 필수예요.

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

6. 심화: 추가로 알면 좋은 팁들

슬로우 쿼리 잡다 보면 데이터 양이 늘어날수록 쿼리 성능이 급격히 떨어지는 경우가 많은데, 이럴 때는 파티셔닝이나 복제(replication)를 고려하는 것도 좋아요.

또한, ANALYZE TABLE이나 OPTIMIZE TABLE 명령어를 가끔 실행해 주면 테이블 상태와 인덱스 통계를 최신으로 유지해서 실행 계획이 더 정확해져요.

ANALYZE TABLE orders;
OPTIMIZE TABLE orders;

마지막으로 쿼리 캐시(Query Cache)는 MySQL 8.0부터 없어졌으니, 캐시 관련 성능 개선은 어플리케이션 레벨에서 따로 고려해야 합니다.

7. 개발자들이 자주 묻는 질문과 답변

Q. 슬로우 쿼리 로그 너무 커지면 어떻게 하나요?

A. 로그 파일을 주기적으로 회전(logrotate)하거나, long_query_time 값을 임시로 올려서 기록 조건을 조절하면 돼요. 서버 용량 부족도 미리 체크하세요.

Q. pt-query-digest 설치는 어떻게 하나요?

A. 보통 리눅스 환경에선 apt install percona-toolkit이나 yum install percona-toolkit으로 설치합니다. 윈도우는 WSL 사용하거나 도커 기반 환경이 편해요.

Q. FULLTEXT 인덱스 대신 다른 방법 있나요?

A. Elasticsearch 같은 외부 검색엔진 도입하는 게 가장 확실해요. 다만 연동 작업이 조금 복잡할 수 있으니 상황에 따라 결정하는 게 좋습니다.

이렇게 제가 단계별로 슬로우 쿼리 잡으면서 만난 문제랑 해결 과정을 정리했는데요, 직접 로그 설정부터 분석, 쿼리 작성, 인덱스 설계까지 차근히 시도해보니 확실히 성능이 개선됐어요. 혹시 실무에서 느려진 MySQL 쿼리 때문에 고민이라면, 저처럼 순서대로 한 단계씩 진행해 보세요.