MySQL 느린 쿼리 문제 해결: 단계별 가이드

MySQL 느린 쿼리 문제 해결은 한 가지 불편한 규칙에서 시작합니다. 쿼리 텍스트만 보고 추측하지 마십시오. 보기 흉한 쿼리도 하루에 한 번 실행되면 무해할 수 있습니다. 단순해 보이는 쿼리도 분당 수천 번 실행되거나, 너무 많은 행을 스캔하거나, 잠금 뒤에서 대기하면 데이터베이스를 망칠 수 있습니다.

유용한 워크플로우는 가장 좋은 의미에서 지루합니다. 실제 느린 쿼리를 캡처하고, 비용별로 그룹화하고, 실행 계획을 검사하고, 한 가지를 변경하고, 다시 측정합니다. 이렇게 하면 무작위 인덱스를 추가하거나, 맹목적으로 전역 설정을 변경하거나, 애플리케이션이 피할 수 있는 쿼리 패턴을 보낼 때 MySQL을 비난하는 것을 방지할 수 있습니다.

저는 보통 세 가지 질문으로 시작합니다.

어떤 쿼리가 사용자에게 피해를 주고 있습니까? 단지 의심스러워 보이는 것이 아니라요.
시간이 행 읽기, 정렬, 잠금 대기 또는 애플리케이션 대기 중 어디에 소비되고 있습니까?
EXPLAIN, 타이밍 및 새로운 느린 로그 데이터로 수정 사항을 증명할 수 있습니까?

느린 쿼리 로그로 시작

MySQL 느린 쿼리 로그는 구성된 임계값을 초과하는 명령문을 기록합니다. MySQL 매뉴얼에 따르면 로그는 기본적으로 비활성화되어 있고, long_query_time의 기본값은 10초이며, 명령문은 일반적으로 최소한 그 시간 동안 실행되고 로그에 기록되기 전에 최소 min_examined_row_limit 행을 검사해야 합니다. log_queries_not_using_indexes가 활성화되면 MySQL은 행 조회에 인덱스를 사용하지 않는 명령문도 기록할 수 있습니다. 이 옵션은 진단 중에 유용하지만, 사용량이 많은 시스템에서는 많은 노이즈를 생성할 수 있습니다.

실용적인 시작 구성은 다음과 같습니다.

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 1
log_output = FILE

많은 프로덕션 시스템에서 1초는 합리적인 첫 번째 시도입니다. 지연 시간에 민감한 API의 경우 일시적으로 0.5 또는 0.2로 낮출 수 있습니다. 계획과 디스크 공간 모니터링을 통해 수행하십시오. 트래픽이 많은 데이터베이스는 임계값이 낮아지면 놀라울 정도로 많은 느린 로그 데이터를 쓸 수 있습니다.

MySQL 세션에서 활성 설정을 확인할 수 있습니다.

SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'slow_query_log_file';
SHOW VARIABLES LIKE 'long_query_time';
SHOW VARIABLES LIKE 'log_output';
SHOW VARIABLES LIKE 'min_examined_row_limit';
SHOW VARIABLES LIKE 'log_queries_not_using_indexes';

임시 조사의 경우 구성 파일을 편집하지 않고 로그를 활성화할 수 있습니다.

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;

SET GLOBAL 변경 사항은 일반 구성 프로세스를 통해 유지하지 않으면 재시작 후에도 유지되지 않을 수 있습니다. MySQL 8에서는 일부 팀이 SET PERSIST를 사용하지만, 다음 운영자가 볼 수 있도록 의도된 설정을 구성 관리에 커밋하는 것을 여전히 선호합니다.

log_queries_not_using_indexes를 켜면 log_throttle_queries_not_using_indexes도 설정하여 하나의 시끄러운 엔드포인트가 로그를 넘치지 않도록 하는 것을 고려하십시오. MySQL은 인덱스 미사용 로깅이 빠르게 증가할 수 있기 때문에 특별히 이 스로틀을 지원합니다.

개별 쿼리를 읽기 전에 로그 그룹화

원시 느린 로그는 반복적입니다. 동일한 쿼리가 다른 ID로 수백 번 표시될 수 있습니다. 파일을 처음부터 끝까지 읽는 것은 시간을 낭비하고 드문 무서운 쿼리가 일반적인 비용이 많이 드는 쿼리보다 더 중요해 보이게 만듭니다.

많은 환경에서 MySQL 설치와 함께 제공되는 mysqldumpslow로 시작하십시오.

sudo mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log

쿼리 시간별로 정렬된 상위 10개 패턴을 요청합니다. 정확한 플래그는 버전과 플랫폼에 따라 다르므로 명령이 다르게 동작하면 mysqldumpslow --help를 확인하십시오. 유용한 정렬에는 총 시간, 평균 시간, 잠금 시간 및 검사된 행이 포함됩니다.

프로덕션 조사의 경우 Percona Toolkit의 pt-query-digest가 더 풍부한 그룹화 및 백분위수 스타일 세부 정보를 제공하기 때문에 종종 더 좋습니다. 도구는 마법이 아닙니다. 수동으로 산술을 수행하지 않아도 됩니다. 중요한 것은 영향을 기준으로 순위를 매기는 것입니다. 밤에 한 번 8초가 걸리는 쿼리는 120밀리초가 걸리지만 초당 600번 실행되는 쿼리보다 덜 긴급할 수 있습니다.

그룹화된 출력을 읽을 때 패턴을 찾으십시오.

높은 총 시간: 사용자에게 표시되거나 리소스 집약적일 가능성이 높습니다.
높은 횟수: 일반적으로 애플리케이션 루프 또는 캐시 누락입니다.
높은 검사 행 수와 낮은 전송 행 수: 일반적으로 인덱싱 또는 필터링 문제입니다.
높은 잠금 시간: 트랜잭션, 쓰기 경합, 메타데이터 잠금 또는 DDL 문제일 수 있습니다.

높은 Rows_examined가 항상 나쁜 것은 아니라고 가정하지 마십시오. 보고 쿼리와 배치 작업은 의도적으로 스캔하는 경우가 있습니다. 문제는 스캔이 작업과 일치하는지와 적절한 시간에 발생하는지 여부입니다.

하나의 쿼리를 안전하게 재현

하나의 쿼리 패턴을 선택하고 매개변수가 포함된 실제 샘플을 얻으십시오. 느린 로그가 리터럴을 정규화한 경우 애플리케이션 로그, APM 추적 또는 원시 느린 로그 항목에서 원래 쿼리를 찾으십시오.

수동으로 실행하기 전에 영향 범위를 확인하십시오. 복제본에서 느린 SELECT는 일반적으로 안전합니다. 프로덕션에서 느린 UPDATE는 무심코 다시 실행할 수 있는 것이 아닙니다. 쓰기 쿼리의 경우 먼저 계획과 트랜잭션 패턴을 검사하거나 현실적인 데이터가 있는 스테이징 복사본에 대해 테스트하십시오.

각 쿼리에 대한 유용한 스크래치 노트는 다음과 같습니다.

엔드포인트: GET /customers/123/orders
쿼리 패턴: 고객 및 상태별 주문, 최신순
관찰됨: 평균 1.8초, 420k 행 검사, 20행 전송
테이블 크기: 1200만 행
예상 결과 크기: 주문 한 페이지
의심: customer_id, status, created_at에 대한 복합 인덱스 누락

해당 노트는 작업을 임의의 SQL 조각 대신 실제 사용자 경로에 연결합니다.

`EXPLAIN`을 사용한 다음 운영자처럼 읽기

느린 쿼리에 대해 EXPLAIN을 실행하십시오.

EXPLAIN
SELECT id, customer_id, status, created_at, total
FROM orders
WHERE customer_id = 123
  AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;

MySQL 8의 경우 EXPLAIN ANALYZE는 쿼리를 실행하고 실제 타이밍 정보를 표시할 수 있습니다. 비용이 많이 드는 쿼리에서는 실제로 명령문을 실행하므로 주의해서 사용하십시오. 통제된 환경에서 일반 SELECT 쿼리의 경우 매우 유용할 수 있습니다.

제가 먼저 확인하는 열은 type, possible_keys, key, rows, filtered 및 Extra입니다.

type은 액세스 패턴을 알려줍니다. const, eq_ref, ref 및 range는 일반적으로 좋은 신호입니다. index는 MySQL이 인덱스를 스캔하고 있음을 의미하며, 여전히 너무 많은 작업일 수 있습니다. ALL은 전체 테이블 스캔을 의미합니다. 작은 테이블에서 전체 스캔이 자동으로 잘못된 것은 아니지만 수백만 행이 있는 핫 테이블에서는 의심스럽습니다.

key는 MySQL이 선택한 인덱스를 보여줍니다. possible_keys에 유망한 인덱스가 나열되어 있지만 key가 다른 경우 최적화 프로그램이 다른 인덱스가 더 저렴하다고 생각할 수 있습니다. 이는 선택도 불량, 오래된 통계 또는 필터와 정렬을 함께 일치시키지 않는 인덱스 때문에 발생할 수 있습니다.

rows는 추정치이지 약속이 아닙니다. 추정치가 크게 잘못된 경우 적절한 유지 관리 기간 동안 ANALYZE TABLE을 실행하거나 데이터 분포가 편향되어 있는지 검토하십시오.

Extra는 종종 이야기를 알려줍니다. Using filesort는 MySQL에 별도의 정렬 단계가 필요함을 의미합니다. 반드시 디스크 정렬을 의미하는 것은 아니지만 결과 집합이 클 때 확인할 가치가 있습니다. Using temporary는 종종 그룹화, 고유 쿼리 또는 복잡한 정렬과 함께 나타납니다. Using index는 테이블 행을 읽지 않고 인덱스에서 쿼리가 충족되므로 좋을 수 있습니다.

전체 쿼리 형태를 염두에 두고 인덱스 수정

가장 일반적인 느린 쿼리 수정은 "WHERE 절의 열에 인덱스 추가"가 아닙니다. 더 나은 규칙은 쿼리가 행을 필터링, 조인, 정렬 및 제한하는 방식과 일치하는 인덱스를 구축하는 것입니다.

위의 주문 쿼리의 경우 customer_id에 대한 단일 열 인덱스가 도움이 될 수 있지만 MySQL은 해당 고객에 대해 많은 행을 계속 정렬할 수 있습니다. 복합 인덱스가 더 유용한 경우가 많습니다.

CREATE INDEX idx_orders_customer_status_created
ON orders (customer_id, status, created_at);

쿼리가 최신순으로 정렬하는 경우 MySQL은 종종 인덱스를 역순으로 스캔할 수 있습니다. MySQL 8에서는 더 큰 패턴에 맞을 때 내림차순 인덱스를 정의할 수도 있습니다.

CREATE INDEX idx_orders_customer_status_created_desc
ON orders (customer_id, status, created_at DESC);

열 순서가 중요합니다. 쿼리와 일치할 때 같음 필터를 먼저 배치한 다음 범위 또는 정렬 열을 배치하십시오. 예를 들어 WHERE customer_id = ? AND status = ? ORDER BY created_at DESC LIMIT 20의 경우 customer_id, status, created_at이 created_at, customer_id, status보다 일반적으로 더 유용합니다.

도움이 될 것 같은 모든 인덱스를 추가하지 마십시오. 인덱스는 읽기 속도를 높이지만 쓰기 속도를 늦추고 스토리지를 소비합니다. 테이블이 많은 삽입 또는 업데이트를 받는 경우 새 복합 인덱스에는 실제 비용이 있습니다. 먼저 기존 인덱스를 확인하십시오.

SHOW INDEX FROM orders;

때로는 올바른 답은 세 개를 모두 유지하는 것이 아니라 두 개의 약한 인덱스를 하나의 더 나은 복합 인덱스로 교체하는 것입니다.

인덱스 사용을 차단하는 쿼리 다시 작성

일부 느린 쿼리는 인덱스 값을 함수 뒤에 숨기거나 MySQL이 효율적으로 사용할 수 없는 패턴을 사용하기 때문에 느립니다.

이 버전은 일반적이고 고통스럽습니다.

SELECT *
FROM orders
WHERE YEAR(created_at) = 2026;

created_at이 인덱스된 경우 YEAR()로 래핑하면 일반적인 범위 조회를 방지할 수 있습니다. 대신 조건자를 범위로 작성하십시오.

SELECT id, customer_id, status, created_at, total
FROM orders
WHERE created_at >= '2026-01-01'
  AND created_at <  '2027-01-01';

동일한 아이디어가 선행 와일드카드 검색에도 적용됩니다.

WHERE email LIKE '%@example.com'

일반 B-트리 인덱스는 문자열 중간으로 이동할 수 없습니다. 접미사 검색이 중요한 경우 생성된 열, 별도의 정규화된 필드 또는 해당 사용 사례에 맞게 설계된 검색 시스템이 필요할 수 있습니다.

또한 SELECT *를 주의하십시오. 개발 중에는 무해해 보이지만 더 작은 프로젝션이 커버링 인덱스를 사용할 수 있을 때 MySQL이 테이블 행을 읽도록 강제할 수 있습니다. 또한 네트워크를 통해 불필요한 데이터를 보냅니다.

쿼리 계획이 괜찮아 보일 때 잠금 확인

쿼리는 괜찮은 계획을 가지고 있지만 대기 중이기 때문에 여전히 느릴 수 있습니다. 느린 로그 Lock_time이 해당 방향을 가리킬 수 있지만 모든 종류의 대기를 설명하지는 않습니다. 사용자가 무작위 일시 중지를 보고하면 활성 세션을 확인하십시오.

SHOW PROCESSLIST;

MySQL 8에서는 서버 구성 방식에 따라 Performance Schema 및 sys 스키마 뷰가 더 나은 세부 정보를 제공할 수 있습니다. 빠른 확인을 위해 인덱스를 변경하기 전에 장기 실행 트랜잭션과 차단된 명령문을 자주 확인합니다.

실제 예: UPDATE orders SET status = ? WHERE id = ? 쿼리는 빨라야 합니다. 기본 키 조회로 느린 로그에 나타나면 문제는 관련 없는 작업을 수행하는 동안 행을 잠긴 상태로 둔 트랜잭션일 수 있습니다. 수정 사항은 다른 인덱스가 아닙니다. 수정 사항은 트랜잭션을 단축하고 느린 외부 호출을 외부로 이동하는 것입니다.

메타데이터 잠금은 유사한 함정을 만들 수 있습니다. ALTER TABLE을 실행하는 마이그레이션은 오래된 트랜잭션을 기다릴 수 있으며, 새 쿼리는 보류 중인 DDL 뒤에 쌓입니다. 느린 쿼리 로그는 증상을 보여주지만 근본 원인은 배포 동작입니다.

쿼리 작업 후에만 서버 설정 조정

구성은 중요하지만 첫 번째 응답으로 과도하게 사용하기 쉽습니다. 쿼리가 10개를 반환하기 위해 500만 행을 스캔하는 경우 메모리를 늘리면 잘못된 계획의 피해를 덜 수 있을 뿐입니다.

InnoDB 중심 시스템의 경우 innodb_buffer_pool_size가 검토할 첫 번째 설정입니다. 전용 MySQL 서버에서는 종종 많은 메모리로 설정되지만 올바른 값은 호스트에서 실행되는 다른 항목, 데이터 세트 크기 및 워크로드에 따라 다릅니다. 블로그 게시물에서 맹목적으로 백분율을 복사하지 마십시오.

또한 데이터베이스가 디스크를 기다리고 있는지 확인하십시오. 작업 세트가 메모리에 맞지 않거나 스토리지가 포화 상태인 경우 잘 인덱스된 쿼리도 지연될 수 있습니다. 쿼리 검토와 호스트 메트릭(CPU, 디스크 대기 시간, IOPS, 메모리 압력 및 연결 수)을 함께 검토하십시오.

연결 풀은 느린 쿼리를 더 나쁘게 보이게 만들 수 있습니다. 하나의 엔드포인트가 너무 많은 느린 명령문을 실행하면 풀이 가득 차고 관련 없는 요청이 연결을 기다리며 전체 앱이 고장난 것처럼 느껴집니다. 이 경우 쿼리 수정이 여전히 주요 작업이지만 풀 제한 및 시간 초과는 시스템이 얼마나 정상적으로 실패하는지 결정합니다.

수정 사항 증명

인덱스를 추가하거나 쿼리를 다시 작성한 후 EXPLAIN을 다시 실행하십시오. 더 적은 예상 행, 더 잘 선택된 키 및 더 적은 비용이 많이 드는 추가 단계를 보고 싶습니다. 그런 다음 실제 매개변수로 실제 쿼리를 테스트하십시오.

한 번의 빠른 실행으로 멈추지 마십시오. 웜 캐시는 문제를 숨길 수 있습니다. 일반적이고 크고 까다로운 경우를 시도하십시오.

주문이 많은 고객.
일치하는 주문이 없는 고객.
바쁜 기간에 걸친 날짜 범위.
대부분의 행과 일치하는 상태 값.

그런 다음 배포 후 느린 로그를 확인하십시오. 최상의 결과는 "스테이징에서 쿼리가 더 나아 보였다"가 아닙니다. 최상의 결과는 쿼리 패턴이 상위 위반자 목록에서 사라지고 CPU 또는 I/O 압력이 떨어지며 사용자 경로가 더 빨라지는 것입니다.

MySQL 느린 쿼리 문제 해결은 대부분 훈련된 증거 수집입니다. 합리적인 임계값으로 로그를 활성화하고, 비용이 많이 드는 패턴을 그룹화하고, 계획을 검사하고, 쿼리 형태를 수정하고, 새로운 데이터로 검증하십시오. 이 습관은 과소 수정과 과잉 수정을 모두 방지하며, 데이터베이스가 이미 압박을 받고 있을 때 정확히 필요한 것입니다.