MySQL slow query 발생과 인덱스 최적화 경험

어느 날 API 응답이 갑자기 느려지는 상황이 발생했습니다. 처음에는 네트워크 문제인가 싶었는데 자세히 보니 MySQL 쿼리 latency가 계속 올라가고 있더군요. 운영을 하다 보면 이런 순간이 생각보다 자주 발생합니다.

MySQL slow query 발생과 인덱스 최적화 경험

MySQL slow query 문제는 트래픽이 조금만 올라가도 바로 체감되는 영역입니다. 특히 API 서버를 운영하다 보면 MySQL slow query가 전체 서비스 latency를 끌어올리는 경우가 꽤 많습니다. 오늘은 실제 운영 환경에서 MySQL slow query를 발견하고 인덱스를 튜닝했던 경험을 말씀드리겠습니다.

 

Real-world Issue

당시 운영하던 서비스는 중간 규모 스타트업 API 서버였습니다. 트래픽은 대략 RPS 80에서 150 정도였고 피크 시간에는 RPS 300까지 올라가곤 했습니다.

문제는 어느 날부터 API 응답 속도가 조금씩 느려지기 시작했다는 점입니다. 평균 latency가 70ms 정도였는데 갑자기 250ms 수준까지 올라가더군요. 처음에는 Redis 문제인가 싶어서 확인했는데 Redis는 정상적이었습니다.

그래서 결국 MySQL slow query 로그를 확인하게 되었는데 거기서 문제의 쿼리가 발견되었습니다. 운영에서 이런 순간이 오면 보통 직감이 옵니다. 아 이거 인덱스 문제겠구나 하는 느낌입니다.

 

Pain Point

문제가 된 테이블은 주문 테이블이었습니다. row 수는 약 3천만 정도였고 계속 증가하고 있는 상황이었습니다.

문제의 쿼리는 다음과 같은 형태였습니다.

SELECT *
FROM orders
WHERE user_id = 10234
AND status = 'COMPLETE'
ORDER BY created_at DESC
LIMIT 20;

문제는 이 테이블에 user_id 인덱스만 존재했다는 점입니다. status와 created_at 조건이 같이 들어가는데 복합 인덱스가 없었습니다.

slow query 로그를 확인해보니 평균 실행 시간이 450ms 정도였습니다. 피크 시간에는 900ms까지 올라가더군요. API 서버 CPU도 같이 올라가기 시작했습니다.

체감상 이런 문제는 데이터 row 수가 천만 단위를 넘어가면 바로 나타납니다. 처음에는 괜찮다가 어느 순간 갑자기 느려집니다.

 

Decision Process

문제를 해결하기 위해 팀 내부에서 두 가지 방법을 고민했습니다.

방법 A는 Redis 캐싱이었습니다.

장점은 간단합니다. API 결과를 Redis에 캐싱하면 DB 쿼리를 줄일 수 있습니다. 구현도 어렵지 않습니다.

단점도 명확합니다. 데이터 정합성 문제가 생깁니다. 주문 데이터는 상태 변경이 자주 발생합니다. 캐시 invalidation 로직이 생각보다 복잡해집니다.

방법 B는 MySQL 인덱스 튜닝이었습니다.

장점은 근본적인 해결입니다. 쿼리 자체를 빠르게 만드는 방식입니다.

단점은 잘못 만들면 오히려 쓰기 성능이 떨어질 수 있습니다. 인덱스가 많아지면 insert 비용이 증가합니다.

결국 선택한 방법은 인덱스 튜닝이었습니다. 이유는 단순했습니다. 이 쿼리는 API에서 매우 자주 호출되는 쿼리였습니다. 체감상 하루 수백만 번 호출되더군요.

이런 경우 캐시보다 쿼리 자체를 빠르게 만드는 것이 훨씬 안정적입니다. 

 

Practical Implementation

그래서 다음과 같은 복합 인덱스를 추가했습니다.

ALTER TABLE orders
ADD INDEX idx_user_status_created (user_id, status, created_at DESC);

-- user_id + status + created_at 순서로 인덱스 구성
-- 이 쿼리 패턴에서 가장 효율적인 구조입니다
-- 운영에서 ORDER BY까지 고려해서 만든 인덱스입니다

인덱스를 추가한 후 EXPLAIN 결과도 확인했습니다.

EXPLAIN
SELECT *
FROM orders
WHERE user_id = 10234
AND status = 'COMPLETE'
ORDER BY created_at DESC
LIMIT 20;

실행 계획을 보면 type이 ref로 변경되었고 Using index 조건이 적용되었습니다. 실행 시간도 눈에 띄게 줄었습니다.

slow query 평균 실행 시간이 450ms에서 18ms 수준으로 떨어졌습니다. API latency도 다시 80ms 수준으로 돌아왔습니다.

이거 운영에서 한번 터져보면 기억에 남습니다. 인덱스 하나로 성능이 이렇게 바뀌는 경험을 하게 됩니다.

 

Cold Truth

다만 인덱스 튜닝이 만능 해결책은 아닙니다. 인덱스가 많아지면 insert 성능이 떨어질 수 있습니다. 특히 로그 테이블이나 이벤트 테이블에서는 더 민감합니다.

그리고 데이터가 수억 row 단위로 올라가면 인덱스만으로는 한계가 옵니다. 결국 샤딩이나 아카이빙 전략도 같이 고민해야 합니다.

그래도 말씀드리고 싶은 것은 하나입니다. 대부분의 MySQL 성능 문제는 생각보다 단순한 곳에서 발생합니다. 인덱스 하나 빠진 경우가 정말 많습니다.

체감상 slow query의 절반 이상은 인덱스 문제입니다.