Ubuntu에서 MySQL/MariaDB가 느려졌을 때, 설정(tuning)부터 만지기보다 먼저 “어떤 쿼리가, 왜 느린지”를 스키마/인덱스 관점에서 좁혀가면 재발이 줄어듭니다. 이 글은 측정(쿼리/락) → 병목 → 해결 → 검증 순서로, 운영 환경에서도 비교적 안전하게 적용할 수 있는 절차를 정리했습니다.
핵심은 “인덱스를 추가한다”가 아니라, 쿼리가 어떤 데이터 접근 경로를 택하도록 스키마와 인덱스를 설계할지를 명확히 하는 것입니다.
1) 측정(쿼리): 느린 쿼리부터 정확히 잡기
체감이 느려졌다는 말만으로는 방향이 안 나옵니다. 우선 “느린 쿼리 목록”을 확보해 상위 몇 개부터 처리하세요.
- slow query log: 가장 기본. 쿼리/시간/행 수/정렬 여부 등 단서를 얻습니다.
- Performance Schema / sys schema: 쿼리 패턴별 집계(총 시간, 평균, 실행 횟수)로 우선순위를 정하기 좋습니다.
- 애플리케이션 APM/로그: 특정 API/배치가 몰리는 시간대와 쿼리를 연결해 원인-영향을 맞춥니다.
운영에서는 “짧게 켜고(예: 10~30분) 끄는” 식으로 수집 범위를 관리하는 게 안전합니다. 수집 결과로 총 소요시간이 큰 쿼리(=실제 병목)와 한 번이 매우 느린 쿼리(=특이 케이스)를 분리해 보세요.
2) 측정(락): 느림이 쿼리 자체인지, 락 대기인지 구분
같은 “느림”이라도 원인이 완전히 다릅니다. 쿼리는 빠르지만 락(lock) 대기 때문에 느린 경우가 많습니다.
- 대기 시간이 큰지: 실행 시간 중 실제 CPU/IO가 아니라 대기(Waiting)가 대부분인지 확인
- 동시에 같은 테이블/행을 만지는지: 특정 테이블에 UPDATE/DELETE가 몰리면 SELECT도 간접 영향
- 트랜잭션이 길게 열려 있는지: 커밋이 늦으면 잠금/undo가 누적돼 다른 쿼리까지 지연
락이 의심되면 “어떤 쿼리가 잠금을 잡고 있는지(holding)”와 “어떤 쿼리가 기다리는지(waiting)”를 한 화면에 묶어 보는 게 중요합니다. 이 단계에서 문제 쿼리 1~3개를 확정해 다음 단계로 넘어가면 됩니다.
3) 병목 찾기: EXPLAIN으로 ‘접근 경로’가 이상한지 확인
스키마/인덱스 관점에서 가장 빠른 진단 도구는 EXPLAIN입니다. 목표는 “DB가 어떤 인덱스를 타고, 얼마나 읽고, 어떤 순서로 조인하는지”를 보는 겁니다.
- type: ALL(풀스캔)인지, range/ref/eq_ref 같은 인덱스 접근인지
- rows: 예상 읽기 행 수가 과도하게 큰지
- Extra: Using temporary / Using filesort가 뜨는지(정렬/그룹핑 비용 힌트)
- key / possible_keys: 타야 할 인덱스가 있는데 안 타는지, 아예 후보가 없는지
자주 나오는 병목 패턴은 아래처럼 정리할 수 있습니다.
- 조건절(WHERE)에는 인덱스가 있는데 정렬(ORDER BY) 때문에 filesort가 발생
- JOIN 키 타입/정렬 규칙(collation)이 달라 인덱스를 못 타는 경우
- 카디널리티 낮은 컬럼(예: status, is_deleted) 단독 인덱스만 있어 효과가 없는 경우
- LIKE '%keyword%' 같은 선행 와일드카드로 인덱스가 무력화
- 함수/연산이 컬럼에 적용되어 인덱스 사용이 깨짐(예: DATE(created_at)=...)
여기서 중요한 건 “인덱스 추가”를 바로 결정하지 말고, 쿼리가 원하는 결과를 어떤 순서로 만들어야 하는지(필터 → 조인 → 정렬/그룹핑 → 제한)를 먼저 그려보는 것입니다.
4) 해결(인덱스): 복합 인덱스 설계와 커버링으로 읽기량 줄이기
대부분의 slow query는 “덜 읽으면” 해결됩니다. 인덱스는 그 목적(읽기량 감소)을 달성하기 위한 구조물이고, 특히 복합 인덱스가 핵심입니다.
실무 설계 규칙(자주 먹히는 것만)
- WHERE + ORDER BY를 함께 만족하는 인덱스를 우선 고려(정렬 비용 제거)
- 복합 인덱스 컬럼 순서: 보통 “선택도가 높은 조건” + “정렬/조인에 필요한 컬럼” 순으로 검토
- 커버링 인덱스: SELECT가 필요한 컬럼이 인덱스에 포함되면 테이블(클러스터) 접근을 줄일 수 있음
- IN (...) 다량: 값 개수가 많을수록 계획이 흔들릴 수 있어, 임시 테이블/조인으로 바꾸는 게 나을 때가 있음
- 페이지네이션: OFFSET이 커질수록 느려짐. 가능한 경우 “마지막 키 기반(seek)” 방식으로 변경
주의할 점도 있습니다. 인덱스는 읽기를 빠르게 하지만 쓰기(INSERT/UPDATE/DELETE) 비용과 저장 공간을 늘립니다. 운영 테이블이라면 아래 체크리스트로 “추가해도 되는 인덱스인지”를 먼저 걸러보세요.
- 해당 쿼리가 전체 지연의 큰 비중을 차지하는가(총 시간 기준)?
- 비슷한 인덱스가 이미 존재해 중복이 되지 않는가?
- 쓰기 트래픽이 큰 테이블에서 인덱스 추가가 오히려 병목을 만들 가능성은?
- 인덱스가 커져 메모리/버퍼 풀에 부담을 주지 않는가?
- 정렬/그룹핑/조인 패턴이 앞으로도 유지되는가(일회성 쿼리인지)?
5) 해결(스키마): 컬럼 타입, NULL, 정규화/비정규화로 ‘계획이 안정적’이게 만들기
같은 인덱스라도 스키마가 불안정하면 실행 계획이 흔들리고, 특정 데이터 분포에서만 느려지는 일이 생깁니다. 아래는 slow query를 줄이는 데 직접 영향을 주는 스키마 포인트입니다.
- 타입 일치: JOIN/WHERE 비교되는 컬럼의 타입(정수/문자), 길이, collation을 맞추기. 불일치 시 암묵 변환으로 인덱스가 깨질 수 있음
- 필요 이상으로 큰 타입 피하기: 예를 들어 숫자를 VARCHAR로 저장하면 비교/정렬/인덱스 효율이 떨어짐
- NULL 설계: NULL 가능 컬럼은 조건이 복잡해질 수 있어, 자주 필터링하는 컬럼은 정책적으로 NOT NULL + 기본값을 고려
- 긴 텍스트 컬럼 분리: 본문(TEXT) 같은 큰 컬럼을 자주 조회하는 목록 쿼리와 같은 테이블에 두면 읽기 비용이 커질 수 있음(필요 시 분리/요약 컬럼 고려)
- 파생 값 저장(신중히): 집계/정렬용 파생 컬럼(예: last_activity_at)을 두면 읽기는 빨라지지만, 갱신 로직/정합성 비용이 생김
추가로, 데이터가 많이 바뀌었는데 통계가 낡으면 optimizer가 잘못된 선택을 할 수 있습니다. 이 경우 통계 갱신(ANALYZE)만으로도 계획이 정상화되는 일이 있습니다. (단, 운영에서는 영향도를 고려해 비혼잡 시간에 진행하세요.)
6) 검증: 변경 전/후를 같은 기준으로 비교하고, 재발을 막기
인덱스/스키마 변경은 “빨라졌다”로 끝내면 위험합니다. 반드시 같은 조건으로 재측정해서 효과와 부작용을 확인해야 합니다.
- 쿼리 실행 시간: 평균뿐 아니라 p95/p99 같은 꼬리 지연도 확인
- 읽은 행/임시 테이블/정렬: EXPLAIN 및 실행 통계에서 읽기량이 줄었는지
- 쓰기 성능: 인덱스 추가 후 UPDATE/INSERT 지연이 늘지 않았는지
- 락/데드락 변화: 변경 후 락 대기가 줄었는지, 새로운 경합이 생기지 않았는지
- 용량/버퍼: 인덱스 크기 증가로 메모리 압박이 커지지 않았는지
가능하면 “문제 쿼리 1개를 고쳤더니 다른 쿼리가 느려짐” 같은 연쇄 효과를 보기 위해, 변경 후 1~2시간 정도는 상위 slow query 집계를 다시 확인하세요. 최종적으로는 대표 쿼리의 EXPLAIN 결과와 slow query 상위 목록을 함께 남겨두면 다음 장애 때 훨씬 빨리 대응할 수 있습니다.
마무리
Ubuntu에서의 slow query 대응은 결국 “무엇을 얼마나 읽느냐”를 줄이는 게임이고, 그 해답은 대부분 스키마와 인덱스에 있습니다. 측정으로 대상을 좁히고(EXPLAIN/락 구분), 병목 패턴을 확인한 뒤, 복합 인덱스/타입 정리/정렬·페이징 패턴 개선으로 접근 경로를 바꿔보세요.
한 번의 튜닝보다, 변경 전/후를 같은 기준으로 검증하고 기록하는 습관이 재발을 줄입니다.