MySQL 슬로우 쿼리 개선 방법: 인덱스 최적화 및 실행 계획 분석 가이드

16분 분량 MySQL슬로우쿼리인덱스최적화실행계획분석쿼리튜닝
MySQL 슬로우 쿼리 개선 방법: 인덱스 최적화 및 실행 계획 분석 가이드

웹 서비스의 규모가 커지고 트래픽이 몰리기 시작하면 백엔드 서버보다 데이터베이스에서 병목 현상이 발생할 확률이 압도적으로 높습니다. 그중에서도 특정 쿼리의 처리 속도가 현저히 느려져 데이터베이스 CPU 점유율이 100%에 달하거나 커넥션 풀이 고갈되는 현상은 대다수의 개발자가 겪는 가장 흔한 문제입니다. 실무에서 사용량이 많은 특정 게시판 목록 조회나 복잡한 통계 계산 쿼리 하나가 서비스 전체의 응답 지연을 초래하곤 합니다. 실제로 저 역시 천만 건 이상의 회원 데이터를 처리하는 도중, 제대로 설계되지 않은 정렬 쿼리가 단 3초 만에 데이터베이스 전체를 마비시키는 치명적인 성능 저하를 경험한 적이 있습니다. 성능 튜닝은 하드웨어를 확장하는 스케일업(Scale-up)으로 일시적인 처방은 가능하지만, 늘어나는 비용과 한계를 감안하면 비효율적입니다. 이 글에서는 MySQL 데이터베이스 성능 저하의 주범인 MySQL 슬로우 쿼리 개선을 체계적으로 실현하기 위해, 수집 단계부터 EXPLAIN 기반의 실행 계획 분석, 그리고 인덱스 최적화 설계와 실전 튜닝 팁까지 모든 과정을 상세히 풀어내고자 합니다.

MySQL 슬로우 쿼리가 시스템에 미치는 영향과 진단 방법

데이터베이스 쿼리 속도가 느려지면 디스크 I/O가 폭증하고 메모리 캐시 적중률이 하락하며 락(Lock) 경합이 발생합니다. 이는 단일 트랜잭션의 지연에 그치지 않고 대기 상태의 다른 세션들이 데이터베이스 커넥션을 점유하게 만들어 전체 애플리케이션의 중단을 초래합니다.

이러한 문제를 감지하고 개선하기 위한 첫 단계는 데이터베이스에서 실행되는 느린 쿼리를 찾아내어 기록하는 것입니다. MySQL에서는 지정한 실행 시간 기준을 초과하는 쿼리를 자동으로 로깅하는 Slow Query Log 기능을 기본으로 제공합니다. 이 기능을 활성화하려면 설정 파일(my.cnf 혹은 my.ini)을 편집하거나 MySQL 콘솔에서 직접 설정을 변경해야 합니다.

아래는 슬로우 쿼리 로그를 수집하기 위한 설정 예시입니다.

# my.cnf 파일 설정 내용
[mysqld]
# 슬로우 쿼리 로그 활성화 여부
slow_query_log = 1

# 로그 파일의 저장 경로 및 파일명 정의
slow_query_log_file = /var/log/mysql/mysql-slow.log

# 슬로우 쿼리로 판정할 기준 시간 (초 단위, 소수점 설정 가능)
long_query_time = 1.000000

# 인덱스를 전혀 타지 않는 쿼리도 함께 수집하고자 할 때 활성화
log_queries_not_using_indexes = 1

설정을 변경한 후에는 변경 사항을 데이터베이스 엔진에 반영하기 위해 서비스를 재시작하거나, 무중단으로 적용하려면 다음과 같이 글로벌 변수를 설정해야 합니다.

-- 실행 중인 MySQL 서버에서 실시간으로 슬로우 쿼리 수집 활성화
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL slow_query_log_file = '/var/log/mysql/mysql-slow.log';
SET GLOBAL long_query_time = 1.0;
SET GLOBAL log_queries_not_using_indexes = 'ON';

이렇게 수집된 로그는 텍스트 파일 형태로 저장되며, 실행 시각, 질의 시간(Query time), 락 획득 시간(Lock time), 스캔한 로우 수(Rows examined), 그리고 실제 SQL 문이 꼼꼼하게 기록됩니다. 텍스트 로그가 너무 방대할 경우 MySQL에 내장된 mysqldumpslow 툴이나 오픈소스 유틸리티인 pt-query-digest 등을 활용해 가장 빈번하고 악영향을 많이 끼치는 상위 쿼리들을 모아 분석하는 것이 좋습니다.

EXPLAIN 명령어를 통한 MySQL 실행 계획 분석의 정석

어떤 쿼리가 시스템 성능을 좀먹고 있는지 확인했다면, 이제 그 쿼리가 내부적으로 어떻게 실행되고 있는지 뼈대를 뜯어볼 차례입니다. MySQL 옵티마이저가 질의된 SQL 문을 파싱하여 가용한 인덱스와 테이블 크기, 통계 정보를 기반으로 가장 저렴하다고 판단한 실행 계획을 세웁니다.

우리는 SQL 문 앞에 EXPLAIN 명령어를 붙임으로써 옵티마이저가 도출해 낸 상세 계산 내역을 검증할 수 있습니다.

-- 쿼리 실행 계획 확인용 EXPLAIN 질의 예시
EXPLAIN
SELECT id, email, nickname
FROM users
WHERE status = 'ACTIVE' AND created_at >= '2026-01-01'
ORDER BY created_at DESC;

실행 결과를 받아보면 표 형태로 여러 컬럼이 출력되는데, 이때 튜닝 엔지니어가 반드시 집중적으로 살피고 이해해야 하는 컬럼은 다음과 같습니다.

  • type (조인 유형): 데이터 접근 방식을 보여주는 컬럼으로, 성능에 가장 직접적인 영향을 줍니다.
  • system / const: 테이블에 단 1개의 매칭 로우만 있는 경우로, 가장 빠릅니다. (기본키 혹은 고유 인덱스 사용 시)
  • eq_ref: 조인 시 첫 번째 테이블의 한 로우가 두 번째 테이블의 단 한 로우와만 매칭될 때 나타납니다.
  • ref: 고유하지 않은 인덱스 또는 복합 인덱스의 일부 컬럼을 동등 비교(=)로 스캔할 때 발생하며 매우 우수합니다.
  • range: 특정 범위 스캔을 의미하며, <, >, BETWEEN, IN 등의 연산자를 적절히 사용할 때 발생합니다.
  • index: 인덱스 트리를 처음부터 끝까지 전체 스캔하는 비효율적인 방식입니다. (테이블 전체 스캔보다는 빠름)
  • ALL: 테이블 전체를 순차적으로 스캔하는 Full Table Scan입니다. 디스크와 CPU에 극심한 부하를 유발하므로 즉시 최적화 대상입니다.
  • key (실제 사용된 인덱스): 옵티마이저가 쿼리 실행을 위해 실제로 채택하여 매핑한 인덱스의 이름입니다. 이 필드가 비어 있다면 인덱스가 설계되어 있지 않거나 옵티마이저가 인덱스를 활용하는 것이 부적절하다고 판단하여 버린 상황입니다.
  • rows (예상 스캔 대상 로우 수): 질의를 처리하기 위해 조사해야 할 것으로 예측되는 행의 수입니다. 실제 데이터 개수와 스캔 행 수가 너무 벌어지면 비효율적인 조회가 이루어지고 있음을 의미합니다.
  • Extra (추가 정보): 옵티마이저가 쿼리를 해결하는 세부 과정에 대한 설명이 나타납니다.
  • Using index: 인덱스 내부의 값만으로 쿼리의 전체 결과를 얻었을 때 표시되는 가장 이상적인 문구입니다. (커버링 인덱스 작동 시)
  • Using filesort: 데이터의 정렬을 위해 디스크나 메모리에 임시 공간을 마련하고 별도의 정렬 연산을 수행했다는 의미입니다. 디스크 I/O를 크게 유발하므로 인덱스를 통한 정렬 유도가 시급합니다.
  • Using temporary: 쿼리 실행 과정에서 결과를 보관하기 위해 임시 테이블을 임포트했다는 뜻입니다. 대용량 데이터 처리 시 성능 저하의 주범입니다.

최근의 MySQL 버전에서는 EXPLAIN ANALYZE 명령어도 제공되므로, 쿼리의 추정치가 아닌 실제 물리적 처리 시간과 단계별 병목 구간을 상세히 추적하여 더 정확하게 튜닝에 임할 수 있습니다. 자세한 인덱스 원리는 PostgreSQL 대용량 데이터 인덱스 최적화 기법에서 다룬 개념들과도 일맥상통하므로, 데이터베이스 아키텍처 관점에서 상호 보완해 공부해 볼 수 있습니다.

인덱스 최적화를 위한 복합 인덱스 설계와 커버링 인덱스 기법

단일 컬럼 단위로 모든 필드에 각각 인덱스를 만들어 두는 것은 성능에 오히려 독이 될 수 있습니다. 쿼리에서 여러 조건(WHERE column_a = ? AND column_b = ?)을 조합하여 자주 호출한다면 다중 컬럼 인덱스인 복합 인덱스(Composite Index)를 구성하는 것이 훨씬 강력한 성능을 냅니다.

복합 인덱스를 구성할 때는 다음 순서로 컬럼 순서를 배열하는 것이 필수 원칙입니다.

  1. 동등 조건(=)으로 비교되는 컬럼을 가장 앞에 둡니다.
  2. 범위 검색 조건(<, >, BETWEEN, LIKE)으로 활용되는 컬럼은 그 뒤로 밀어야 합니다. 범위 검색 컬럼이 인덱스의 앞에 올 경우 그 뒤에 배열된 컬럼들은 인덱스 정렬의 이점을 받지 못하고 필터링만 거치기 때문입니다.
  3. 카디널리티(Cardinality)가 높은 컬럼을 배치합니다. 고유값 개수가 많고 필터링 효율이 좋은 컬럼이 선두에 서야 스캔 데이터양을 효율적으로 차단할 수 있습니다.

또한 데이터 조회 속도를 비약적으로 단축할 수 있는 고급 기술 중 하나가 바로 커버링 인덱스(Covering Index)입니다. 일반적인 인덱스 스캔은 인덱스 트리에서 특정 키를 찾은 뒤, 행의 물리적 주소(TID)를 따라 디스크 내의 테이블 블록(Heap)을 다시 읽어오는 추가 과정을 거치게 됩니다. 이 테이블 블록으로 향하는 랜덤 디스크 I/O가 데이터베이스 엔진에 있어 가장 값비싸고 병목이 걸리는 비용입니다.

커버링 인덱스는 쿼리 내부의 SELECT 절, WHERE 절, ORDER BY 절, GROUP BY 절에 포함된 모든 컬럼을 통째로 포함하도록 다중 인덱스를 설계하는 기법입니다. 이렇게 설계하면 MySQL 엔진은 인덱스 구조만 조회하고 실제 데이터 블록으로의 무작위 접근을 생략하게 됩니다.

다음은 일반 인덱스와 커버링 인덱스의 구조 및 쿼리 비교를 직관적으로 정리한 비교 분석 표입니다.

구분 일반 인덱스 스캔 커버링 인덱스 스캔
인덱스 대상 컬럼 WHERE 절에 지정된 일부 필드만 포함 쿼리에 사용된 모든 필드(SELECT, WHERE 등) 포함
디스크 I/O 발생 인덱스 스캔 후 실제 데이터 블록 접근 발생 인덱스 페이지만 직접 탐색하고 조회 즉시 종료
성능 체감 수준 양호 (단, 대량 조회 시 랜덤 I/O 급증) 극도로 빠름 (랜덤 I/O 비용 0에 수렴)
데이터 크기 오버헤드 상대적으로 적음 인덱스 파일 크기가 다소 증가함

아래의 실무 SQL 예시 코드를 통해 커버링 인덱스의 구체적인 적용 방식을 확인할 수 있습니다.

-- 1. [비효율] users 테이블 전체 데이터를 탐색해야 하는 인덱스 구조
-- 인덱스가 (status) 로만 이루어져 있어, nickname과 email을 찾기 위해 테이블 블록을 찾아가야 합니다.
CREATE INDEX idx_users_status ON users(status);

SELECT nickname, email 
FROM users 
WHERE status = 'ACTIVE'; -- Extra 필드에 'Using index'가 표시되지 않음

-- 2. [효율] 커버링 인덱스 구성 및 쿼리
-- 쿼리문에 들어간 모든 컬럼을 복합 인덱스로 감싸줍니다.
CREATE INDEX idx_users_covering ON users(status, nickname, email);

SELECT nickname, email 
FROM users 
WHERE status = 'ACTIVE'; -- 실행 계획 확인 시 Extra에 'Using index' 노출

다만 커버링 인덱스를 구축하면 인덱스의 용량이 대폭 커지며, 해당 테이블에 신규 행이 추가되거나 기존 컬럼 내용이 갱신(INSERT/UPDATE)될 때 인덱스를 동기화하기 위한 물리 연산 오버헤드 또한 가중됩니다. 따라서 블로그 글 작성이나 회원 프로필 갱신처럼 읽기 연산의 비율이 전체의 90% 이상을 점유하는 핵심적인 핫스팟 쿼리를 대상으로만 제한적으로 활용해야 합니다.

데이터 분할과 파티셔닝을 통한 대용량 테이블 쿼리 성능 개선

테이블의 누적 데이터 양이 수천만 건을 넘어가면 아무리 인덱스를 꼼꼼하게 배치해도 탐색해야 하는 트리의 깊이가 깊어져 조회 연산 속도가 점진적으로 후퇴합니다. 특히 날짜가 지난 로그나 만료된 데이터들이 테이블의 지분을 차지하면 백업, 복구는 물론 인덱스 갱신 성능도 극도로 나빠집니다. 이 경우 물리적인 테이블을 논리적인 파티션으로 쪼개는 파티셔닝(Partitioning) 설계가 훌륭한 해법입니다.

MySQL에서는 특정 날짜 범위를 기준으로 테이블 내부 영역을 조각내는 RANGE Partitioning을 빈번하게 사용합니다.

-- 로그 테이블에 대해 연도별 레인지 파티셔닝 적용 예시
CREATE TABLE access_logs (
    id INT NOT NULL,
    access_time DATETIME NOT NULL,
    ip_address VARCHAR(45),
    request_data TEXT,
    PRIMARY KEY (id, access_time) -- 파티션 키는 반드시 프라이머리 키에 속해야 합니다.
)
PARTITION BY RANGE (YEAR(access_time)) (
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p2025 VALUES LESS THAN (2026),
    PARTITION p2026 VALUES LESS THAN (2027),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

이와 같이 파티션을 분리해두면 MySQL 엔진은 쿼리의 WHERE 조건절에 기술된 날짜 필드를 보고 필요한 파티션만 콕 집어 검색하는 Partition Pruning을 수행합니다. 예를 들어 access_time이 2026년인 데이터를 질의하면 p2026 파티션 내부의 인덱스 파일만 스캔하게 되어 무의미한 대량 스캔을 원천 차단합니다.

오래되어 아예 참조되지 않는 과거 아카이브 로그 파티션은 아예 드롭(ALTER TABLE DROP PARTITION)하거나 압축 모드로 전환하여 보관 비용을 절감하는 실무적인 아키텍처 운용도 손쉽게 구현할 수 있습니다.

MySQL 쿼리 튜닝 시 흔히 범하는 실수와 예방책

숙련된 개발자라 하더라도 실무 코딩 과정에서 실수로 옵티마이저가 인덱스를 포기하고 풀 테이블 스캔을 하도록 쿼리를 잘못 작성하는 일이 생각보다 잦습니다. 대표적인 오용 사례 세 가지와 대안을 정리했습니다.

1. 인덱스 컬럼에 가공(함수 또는 연산) 적용

-- ❌ [나쁜 예] 인덱스 컬럼을 함수로 감싸버려 인덱스 트리 탐색 불가
SELECT id, name FROM users WHERE DATE_FORMAT(created_at, '%Y-%m-%d') = '2026-06-18';

--  [좋은 예] 컬럼의 순수 값을 범위 조건으로 직접 비교
SELECT id, name FROM users WHERE created_at >= '2026-06-18 00:00:00' AND created_at <= '2026-06-18 23:59:59';

인덱스는 원래 저장된 값을 기반으로 정렬되어 구축되기 때문에, 비교 대상 컬럼을 변경하거나 함수로 감싸면 옵티마이저는 값을 예측할 수 없게 되어 테이블 전체를 풀 스캔합니다. 연산이나 변환은 반드시 비교하는 우변의 상수값이나 변수에 수행해 원본 컬럼의 형태를 고스란히 노출해야 합니다.

2. LIKE 연산자 사용 시 선두 와일드카드 배치

-- ❌ [나쁜 예] 앞부분에 %가 붙어 정렬 구조를 사용하지 못함
SELECT id, name FROM products WHERE name LIKE '%텀블러%';

--  [좋은 예] 뒷부분만 와일드카드를 적용하거나 전문 검색(Full-text Index) 도입
SELECT id, name FROM products WHERE name LIKE '텀블러%';

B-Tree 구조는 글자의 앞부분부터 가나다순으로 정렬하여 배치하므로 검색 값 앞에 %가 들어오면 검색 범위를 좁힐 수 없게 되어 처음부터 끝까지 다 뒤지게 됩니다. 전방 일치 형태 검색으로 유도하거나, 중간 검색이 빈번하다면 GIN 인덱스나 Elasticsearch 같은 전문 검색 기술을 설계에 얹어야 합니다.

3. 컬럼의 암묵적 타입 변환 (Implicit Type Conversion)

-- ❌ [나쁜 예] 문자열 타입의 회원 번호 컬럼에 숫자 상수를 대입하여 묵시적 형변환 유발
SELECT id FROM users WHERE phone_number = 01012345678;

--  [좋은 예] 컬럼 타입과 정밀히 일치하게 쿼리를 문자형 상수로 기재
SELECT id FROM users WHERE phone_number = '01012345678';

테이블에 정의된 컬럼의 타입과 조회 문법에 기술한 상수 타입이 다를 때 MySQL은 내부적으로 형변환을 강제로 실행합니다. 이때 인덱스 스캔을 상실하는 주범이 되므로, 특히 문자형 키에 숫자 값이나 반대의 경우 상수를 반드시 자료형에 정확하게 맞춰 쿼리문을 짜야 합니다.

자주 묻는 질문 (FAQ)

Q. MySQL 슬로우 쿼리 로그가 디스크 공간을 너무 많이 점유하면 어쩌죠?

슬로우 쿼리 로그 파일이 기하급수적으로 부풀어 오르면 디스크 고갈 사고를 유발할 수 있습니다. 이를 예방하기 위해 주기적으로 파일을 교체하고 압축 보관하는 logrotate 도구 설정을 리눅스 환경에서 걸어두어야 합니다. 또한 long_query_time 설정을 처음에는 2.0~3.0초 정도로 느슨하게 잡아 주요 무거운 쿼리부터 수습하고 점차 기준 시간을 0.5초 등으로 좁혀나가는 계단식 정책 수립이 효율적입니다.

Q. 인덱스가 분명히 생성되어 있는데 옵티마이저가 왜 작동을 시키지 않고 ALL로 스캔하나요?

옵티마이저는 테이블 안의 전체 행 수 대비 필터링되어 남는 행 수가 일정 비율(통상 20~30% 내외) 이상을 초과하면 굳이 인덱스를 거쳐 테이블 블록을 왕복하느라 랜덤 I/O 비용을 발생시키는 것보다 테이블 처음부터 한 번에 쭉 미는 것이 비용적으로 훨씬 경제적이라고 판단하여 인덱스를 무시합니다. 또한 데이터 분포도의 편향 때문에 통계 데이터가 갱신되지 않아 생기는 오판일 수 있으므로 ANALYZE TABLE [테이블명]; 명령어로 데이터베이스 통계를 즉시 새로 계산해 주는 조치가 주효할 수 있습니다.

Q. 데이터 정렬을 위한 인덱스 최적화는 어떻게 설계하나요?

ORDER BY 절에 포함된 컬럼들에 대한 인덱스가 조건 매핑과 연속적으로 정렬을 공유하도록 설계해야 Using filesort 현상을 원천 방지합니다. 예를 들어 WHERE a = 1 ORDER BY b DESC 쿼리가 있다면 (a, b) 순서로 복합 인덱스를 구성하여 a 값을 선행 분류한 직후 인덱스 트리 내부의 정렬 규칙 그대로 데이터를 내보내 불필요한 메모리 내 정렬 단계를 완벽히 지울 수 있습니다.

결론: 지속 가능한 MySQL 데이터베이스 성능 최적화

데이터베이스 튜닝은 단 한 번의 설정 변경으로 평생 끝나는 일회성 과업이 결코 아닙니다. 비즈니스가 팽창하고 기능이 점진적으로 개편되며 쿼리의 구조와 저장되는 행들의 볼륨이 실시간으로 변화하기 때문입니다. 그러므로 성능에 중대한 악영향을 초래하는 MySQL 슬로우 쿼리 개선 작업을 위해 데이터베이스 성능 임계값 모니터링 경고 장치를 구성하고 정기적인 느린 쿼리 정제 파이프라인을 가동해야 합니다.

오늘 소개한 슬로우 쿼리 수집 기법, EXPLAIN 실행 계획 해독, 복합 및 커버링 인덱스의 설계 원리를 시스템 핵심 쿼리에 즉각 적용해 보시길 권장합니다. 안정적인 DB 가동 상태를 유지하면서 쿼리 튜닝 프로세스를 확립할 수 있다면 서비스 스케일이 커지더라도 트래픽 장애 없이 쾌적한 서비스를 지속해서 안정감 있게 제공할 수 있을 것입니다.

관련 글

Related: PostgreSQL 대용량 데이터 인덱스 최적화: 쿼리 실행 계획 분석과 성능 튜닝 방법론

PostgreSQL 대용량 데이터 인덱스 최적화: 쿼리 실행 계획 분석과 성능 튜닝 방법론