MariaDB 쿼리 튜닝 1시간 가이드 – 느린 쿼리로 인한 장애 극복 실전 사례

느린 쿼리 하나가 서비스 전체를 마비시킬 수 있다는 사실, 알고 계셨나요? 많은 개발자와 DBA가 새벽에 울리는 알람과 씨름하며 ‘쿼리 튜닝’의 중요성을 깨닫습니다. 특히 사용자가 급증하는 시간대에 MariaDB의 CPU 사용률이 100%에 육박하고, 모든 페이지가 로딩되지 않는 최악의 상황을 겪어보셨다면 이 글의 필요성을 실감하셨을 것입니다. 오늘은 실무에서 바로 적용할 수 있는 MariaDB 쿼리 튜닝 핵심 기술과, 실제 장애를 극복했던 경험을 바탕으로 한 구체적인 해결책을 1시간 안에 핵심을 익힐 수 있도록 구성했습니다.

1. 느린 쿼리의 원인: 병목 현상을 발견하는 프로세스

쿼리 튜닝의 첫 단계는 ‘어디서 느린가’를 찾는 일입니다. 대부분의 DB 장애는 단일 원인이 아닌 복합적인 문제로 발생합니다. 가장 흔한 원인은 불필요한 데이터 스캔(FULL SCAN)과 비효율적인 조인(JOIN) 방식입니다. 실무에서 가장 먼저 확인해야 할 것은 MariaDB/MySQL의 느린 쿼리 로그(Slow Query Log)입니다. 설정 후 24시간 동안 수집된 로그를 분석하면 어떤 쿼리가 시스템 리소스를 점유하는지 파악할 수 있습니다.

저희 팀의 경우, 하루에 100만 건 이상 호출되는 API 중 특정 통계 조회 쿼리가 전체 DB 부하의 60%를 차지하는 것을 발견했습니다. 해당 쿼리는 6개의 테이블을 JOIN하고, GROUP BY로 50만 건의 레코드를 임시 테이블에 저장하고 있었습니다. 이 쿼리의 실행 시간은 평균 12초에 달했고, 트래픽 증가와 함께 데이터베이스 연결 풀이 고갈되어 전체 장애로 이어졌습니다.

1-1. 진단 스크립트: 지금 당장 확인하는 방법

쿼리 튜닝을 시작하기 전, 다음 SQL을 실행해 현재 MariaDB/MySQL 상태를 점검하세요.

  
-- 장기 실행 쿼리 실시간 확인
SELECT id, user, host, db, command, time, state, LEFT(info, 100) AS query
FROM information_schema.processlist
WHERE command != 'Sleep' AND time > 5
ORDER BY time DESC;
  

이 명령은 5초 이상 실행 중인 쿼리를 즉시 보여줍니다. 실행 결과 state가 ‘Copying to tmp table’ 또는 ‘Sorting result’로 표시된다면 임시 테이블 생성이나 대량 정렬이 원인일 가능성이 높습니다. 이 결과를 바탕으로 우선순위를 정해 튜닝 작업을 진행하시길 권장합니다.

2. 핵심 전략: 실행 계획(EXPLAIN) 분석으로 최적 포인트 찾기

쿼리 튜닝의 꽃은 실행 계획 분석입니다. EXPLAIN 명령어를 통해 데이터베이스가 쿼리를 어떻게 실행하는지 확인할 수 있습니다. 주의 깊게 봐야 할 항목은 type과 rows입니다. type이 ALL로 표시되면 테이블 전체를 스캔한다는 뜻이므로 반드시 인덱스를 추가해야 합니다. 실행 계획에서 key 항목이 NULL로 표시되는 경우에도 인덱스가 사용되지 않고 있다는 신호입니다.

예를 들어, WHERE절에 날짜 조건과 사용자 ID 조건이 함께 있다면 각각의 단일 인덱스보다 복합 인덱스(Composite Index)가 효율적입니다. 이때 컬럼 순서가 중요합니다. 일반적으로 분별도(Distinct Value)가 높은 컬럼을 앞에 배치하는 것이 좋습니다. 아래 표는 인덱스 전략에 따른 성능 차이를 보여줍니다.

인덱스 유형	스캔 범위	실행 시간 (100만 건 기준)	장애 발생 여부
인덱스 없음	Full Scan	8.5초	지연 발생
단일 인덱스	Index Scan	1.2초	부분 개선
복합 인덱스	Index Range Scan	0.15초	장애 해소

* 표의 수치는 100만 건의 더미 데이터를 대상으로 한 내부 테스트 환경에서 측정된 값입니다.

위 표에서 확인할 수 있듯이 복합 인덱스 적용 시 실행 시간이 약 98% 감소했습니다. 쿼리 튜닝이 단순히 서버의 성능 문제가 아니라 SQL 작성 방식과 인덱스 설계에 달려 있음을 보여주는 사례입니다. 단, 인덱스가 많아지면 INSERT/UPDATE 성능이 떨어질 수 있으므로 서비스의 읽기/쓰기 비중을 함께 고려해야 합니다.

3. 쿼리 리팩토링 기법: 작은 변경, 큰 효과

인덱스만으로 해결되지 않는 쿼리가 있습니다. 특히 비즈니스 로직이 복잡하거나, 대량의 데이터를 집계(Aggregation)해야 하는 경우에는 쿼리 자체를 재작성해야 합니다.

3-1. 불필요한 데이터베이스 함수 사용 피하기

WHERE 절에서 컬럼에 함수를 적용하면 인덱스를 활용할 수 없습니다. 예를 들어 WHERE DATE(created_at) = '2024-01-01'보다는 WHERE created_at >= '2024-01-01 00:00:00' AND created_at < '2024-01-02 00:00:00' 형태로 작성해야 인덱스를 효과적으로 사용할 수 있습니다. 아래와 같이 범위 조건으로 바꾸는 것만으로도 실행 계획이 달라집니다.

  
-- 비효율적인 예: 함수로 인덱스 무효화
SELECT order_id, user_id, amount FROM orders WHERE DATE(created_at) = '2024-01-01';

-- 권장 예: 범위 조건으로 인덱스 활성화
SELECT order_id, user_id, amount FROM orders
WHERE created_at >= '2024-01-01 00:00:00'
  AND created_at < '2024-01-02 00:00:00';
  

3-2. 서브쿼리 대신 JOIN 활용

서브쿼리는 실행 시 임시 테이블을 생성하여 성능을 저하시킵니다. 대부분의 경우 JOIN으로 변환하면 불필요한 임시 테이블 생성을 방지할 수 있습니다. 특히 IN (SELECT ...) 패턴은 데이터 건수가 늘어날수록 EXISTS나 JOIN으로 변경했을 때 성능 개선 폭이 커집니다.

또한 SELECT *는 반드시 필요한 컬럼만 명시하도록 수정합니다. 모든 컬럼을 조회하면 네트워크 리소스뿐만 아니라 디스크 I/O까지 증가하여 전체 병목의 원인이 됩니다.

4. 캐싱 및 파티셔닝: 튜닝의 마지막 단계

쿼리 튜닝으로 해결하기 어려운 데이터 규모라면 캐싱과 파티셔닝을 고려해야 합니다. 느린 쿼리의 원인이 데이터가 특정 테이블에 집중되어 인덱스 크기가 커진 경우, 테이블 파티셔닝(Partitioning)을 통해 데이터를 분산 저장할 수 있습니다. 예를 들어 날짜 기반으로 월별 파티션을 구성하면, 특정 월의 데이터만 스캔하므로 쿼리 성능이 비약적으로 빨라집니다.

다만 파티셔닝은 모든 테이블에 유효한 해법이 아닙니다. 데이터 보관 주기, 조회 패턴, 파티션 프루닝(Partition Pruning) 가능 여부를 먼저 확인한 뒤 적용해야 합니다.

또한 로그성 데이터 조회는 Redis와 같은 인메모리 캐시 서버에 저장하는 방식으로 전환하는 것이 효율적입니다. DB에 직접 접근하는 쿼리 튜닝과 더불어 애플리케이션 레벨의 캐시 전략을 병행하면 DB 부하를 현저히 줄일 수 있습니다.

저희 사례에서는 월별 파티셔닝과 Redis 캐시를 적용한 후 최대 트래픽 시간대의 DB CPU 사용률이 80%에서 30%로 감소했습니다. 이후에도 지속적인 쿼리 튜닝을 병행해 전체적인 안정성을 확보할 수 있었습니다.

5. 마무리: 쿼리 튜닝의 시작이 곧 서비스 안정성

쿼리 튜닝은 일회성 작업이 아니라 지속적인 관리가 필요한 영역입니다. 데이터가 쌓일수록, 트래픽이 증가할수록 실행 계획은 변화합니다. 오늘 다룬 쿼리 튜닝 기법을 활용해 1시간 안에 핵심 병목 구간을 파악하고 개선해보시길 바랍니다. 처음에는 생소할 수 있지만, 실행 계획을 읽는 습관을 들이면 문제의 근본 원인을 빠르게 해결하는 실력이 갖춰집니다. 서비스의 안정적인 운영을 위해 꼭 알아 두면 좋은 업무 프로세스입니다.

* 이 글은 실제 운영 중인 서비스의 장애 극복 사례를 기반으로 작성되었으며, 제시된 수치는 내부 테스트 환경에서 측정된 결과임을 명시합니다.