(MySQL/MariaDB) 쿼리가 갑자기 느려졌다면? 20년 경력자가 알려주는 체크리스트

안녕하세요, 20년 넘게 MySQL과 MariaDB를 다루어 온 ‘코딩하는곰’입니다. 어느 날 갑자기 웹 서비스의 응답 속도가 떨어지고, 데이터베이스 쿼리가 예전 같지 않게 느려지는 경험을 해보셨나요? 이는 서비스 규모가 커지거나 데이터가 누적되면서 누구나 마주칠 수 있는 문제입니다. 당황하지 마세요! 오늘은 그런 위기 상황에서 바로 적용할 수 있는, 체계적인 쿼리 성능 저하 점검법을 단계별로 상세히 알려드리겠습니다. 단순히 ‘인덱스를 걸어라’는 차원을 넘어, 문제의 근본 원인을 찾아 해결하는 실전 가이드가 될 것입니다.

1단계: 당황하지 말고 현황 파악하기 - 문제의 범위 좁히기

쿼리가 느려졌다고 바로 EXPLAIN을 돌리기 전에, 먼저 문제의 정체를 파악해야 합니다. 성능 저하는 특정 쿼리 때문일 수도, 시스템 전반의 문제일 수도 있습니다.

• 어떤 쿼리가 문제인가? SHOW PROCESSLIST; 명령어를 실행해 현재 실행 중인 쿼리들을 확인하세요. ‘State’ 컬럼에서 ‘Sending data’, ‘Copying to tmp table’, ‘Sorting result’와 같은 상태가 오래 지속되는 쿼리를 주목합니다. MariaDB 10.0.1 / MySQL 5.7 이상에서는 SELECT * FROM information_schema.processlist; 로 더 상세한 정보를 얻을 수 있습니다.
• 전체 시스템 상태는? SHOW GLOBAL STATUS LIKE 'Threads_running'; 을 확인해 동시 실행 스레드 수가 평소보다 비정상적으로 높은지 봅니다. SHOW ENGINE INNODB STATUS\G (InnoDB 사용 시)를 통해 잠금 대기, 버퍼 풀 상태 등을 체크합니다.
• 리소스는 여유로운가? top 또는 htop 명령어로 서버의 CPU, 메모리 사용량을 확인합니다. I/O 대기(%wa)가 높다면 디스크 병목을 의심해볼 수 있습니다. 이 단계에서 특정 유형의 쿼리(예: 특정 보고서 페이지 쿼리, 대량 조인 쿼리)가 주로 문제라면, 본격적인 분석 단계로 넘어갑니다.

🤖 AI와 머신러닝 개발에 관심이 있다면, (자바 예외 처리 완전 정복) try-catch-finally의 모든 것 (20년 경력자의 노하우)를 참고해보세요.

2단계: 문제 쿼리의 실행 계획(EXPLAIN) 깊게 들여다보기

문제가 될 만한 쿼리를 찾았다면, 이제 그 쿼리가 데이터베이스 내부에서 어떻게 실행되는지 확인할 차례입니다. EXPLAIN 또는 EXPLAIN FORMAT=JSON은 옵티마이저가 선택한 실행 계획을 보여주는 가장 강력한 도구입니다.

EXPLAIN
SELECT u.user_name, o.order_date, p.product_name
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN products p ON o.product_id = p.id
WHERE u.signup_date > '2023-01-01'
ORDER BY o.order_date DESC
LIMIT 100;

실행 계획을 해석할 때 반드시 체크해야 할 핵심 포인트는 다음과 같습니다.

1. type 컬럼 - 접근 방식: 성능에 가장 큰 영향을 미칩니다. 최악부터 최선 순으로 나열해보면:
   • ALL: 풀 테이블 스캔. 테이블의 모든 행을 읽습니다. 대형 테이블에서 발생하면 즉시 경고 신호입니다.
   • index: 풀 인덱스 스캔. 인덱스의 모든 항목을 읽습니다. ALL보다는 나을 수 있지만 여전히 부담스러운 작업입니다.
   • range: 인덱스 범위 스캔. WHERE column BETWEEN ... 또는 WHERE column > ... 에 적합한 방식입니다.
   • ref, eq_ref: 인덱스를 사용한 조인. 일반적으로 좋은 접근 방식입니다.
   • const, system: 상수로 처리. 가장 이상적인 방식입니다. 목표는 ALL과 index를 피하고, range 이상의 접근 방식을 유도하는 것입니다.
2. key 컬럼 - 사용된 인덱스: 옵티마이저가 실제로 선택한 인덱스 이름입니다. NULL이라면 인덱스를 전혀 사용하지 못했다는 뜻입니다.
3. rows 컬럼 - 예상 검색 행 수: 쿼리가 처리하기 위해 접근해야 할 것으로 예상되는 행의 수입니다. 이 값이 실제 결과 행 수보다 훨씬 크다면(예: 100만 행 접근으로 10개 행 반환), 인덱스 효율이 매우 낮다는 증거입니다.
4. Extra 컬럼 - 추가 정보: 여기에 Using filesort(디스크나 메모리에서 정렬)나 Using temporary(임시 테이블 생성)가 표시되면, 이는 쿼리가 추가적인 부하가 큰 작업을 수행하고 있음을 의미합니다. ORDER BY나 GROUP BY 절이 인덱스를 타지 못할 때 발생합니다.

QR코드로 간편하게 번호를 확인하고 싶다면, AI 번호 추천과 최근 당첨번호까지 제공하는 지니로또AI 앱을 다운로드하세요.

3단계: 인덱스 전략 점검 및 최적화

EXPLAIN 분석 결과 문제 원인이 인덱스와 관련되었다면, 본격적인 인덱스 수술이 필요합니다.

• 인덱스가 없나? WHERE, JOIN ON, ORDER BY, GROUP BY 절에 자주 사용되는 컬럼에 인덱스가 없는지 확인합니다.
• 잘못된 인덱스를 타나? 복합 인덱스(Composite Index)의 컬럼 순서가 쿼리 조건과 맞지 않을 수 있습니다. 왼쪽 프리픽스(Leftmost Prefix) 원칙을 기억하세요. INDEX (A, B, C) 인덱스는 WHERE A=?, WHERE A=? AND B=?, WHERE A=? AND B=? AND C=? 쿼리는 사용할 수 있지만, WHERE B=?나 WHERE B=? AND C=? 쿼리는 이 인덱스를 효율적으로 사용하지 못합니다.
• 인덱스 컬럼 선택의 함정: 카디널리티(Cardinality, 고유한 값의 수)가 매우 낮은 컬럼(예: 성별, 활성화 여부)에 단독 인덱스를 걸면 오히려 옵티마이저가 인덱스를 사용하지 않고 풀 스캔을 선택할 수 있습니다. 이런 컬럼은 복합 인덱스의 마지막 부분에 두는 것이 좋습니다.
• 인덱스가 너무 많나? 인덱스는 읽기 성능을 높이지만, INSERT, UPDATE, DELETE 시에는 매번 인덱스도 갱신해야 하므로 쓰기 성능을 저하시킵니다. 사용하지 않는 인덱스는 과감히 제거하세요. SHOW INDEX FROM 테이블명;으로 인덱스 현황을 파악할 수 있습니다. 실전 예시: 위 EXPLAIN 예제 쿼리의 경우, users.signup_date, orders.user_id, orders.product_id, orders.order_date 등에 적절한 인덱스가 있는지 확인하고, 복합 인덱스 (signup_date, id) on users, (user_id, product_id, order_date) on orders 등을 고려해볼 수 있습니다.

4단계: 캐시 및 설정 확인 (종종 간과되는 요소)

인덱스와 실행 계획이 완벽해도 성능이 나오지 않는다면, 데이터베이스의 ‘기본 설정’과 ‘캐시’ 상태를 점검해야 합니다.

• 쿼리 캐시(Query Cache): MySQL 8.0부터는 제거되었으며, MariaDB 10.1.7 이후 버전에서도 기본 비활성화되어 있습니다. 만약 오래된 버전을 사용 중이고 활성화되어 있다면, SHOW VARIABLES LIKE 'query_cache_type'; 로 상태를 확인하세요. 캐시 무효화로 인한 오버헤드가 성능을 해칠 수 있어, 현대적인 환경에서는 비활성화를 권장합니다.
• 버퍼 풀(Buffer Pool): InnoDB의 핵심 메모리 영역입니다. 자주 접근하는 테이블과 인덱스 데이터를 캐싱합니다. SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%'; 명령으로 히트율을 계산할 수 있습니다. Innodb_buffer_pool_read_requests / (Innodb_buffer_pool_read_requests + Innodb_buffer_pool_reads) * 100 공식으로 계산하며, 일반적으로 99% 이상이 되어야 합니다. 히트율이 낮다면 innodb_buffer_pool_size 값을 서버 물리 메모리의 50~80% 수준으로 적절히 증가시켜야 합니다.
• 통계 정보(Statistics): 옵티마이저는 테이블과 인덱스의 통계 정보를 바탕으로 실행 계획을 수립합니다. 이 정보가 오래되면 잘못된 계획을 선택할 수 있습니다. ANALYZE TABLE 테이블명; 명령어로 통계 정보를 최신화하세요.

AI가 분석한 로또 번호 추천을 받고 싶다면, QR코드 스캔과 통계 기능을 제공하는 지니로또AI 앱이 도움이 될 것입니다.

지금까지 쿼리 성능이 갑자기 느려졌을 때 단계별로 따라 해야 할 점검법을 자세히 살펴보았습니다. 요약하자면, 1) 시스템 현황 파악 → 2) 문제 쿼리 실행 계획 분석 → 3) 인덱스 전략 재점검 → 4) 캐시 및 DB 설정 확인의 흐름을 기억하시면 됩니다. 데이터베이스 튜닝은 마치 의사가 환자를 진단하는 것과 같습니다. 증상(느린 쿼리)만 보고 약(인덱스)을 무작정 처방하기보다, 정확한 진단(EXPLAIN, 상태 확인)을 통해 근본 원인을 찾아 치료해야 지속적인 건강(성능)을 유지할 수 있습니다. 특히 대용량 서비스로 발전할수록 이러한 체계적인 접근이 더욱 중요해집니다. 이 글이 여러분의 데이터베이스 성능 위기를 극복하는 데 도움이 되었기를 바랍니다. 궁금한 점이나 더 깊이 알고 싶은 주제가 있다면 언제든 댓글로 남겨주세요. 다음 시간에는 ‘인덱스는 있는데 왜 안 탈까?‘에 대한 구체적인 사례와 해법을 가지고 찾아오겠습니다. 코딩하는곰이었습니다. 감사합니다!

두뇌 건강을 위한 재미있는 퍼즐 게임이 필요하다면, 크립토 할아버지의 지혜가 담긴 스도쿠 저니를 설치해보세요.