MySQL 쿼리 최적화: 실용적인 방법 가이드
EXPLAIN, 인덱스, 안전한 재작성 및 느린 쿼리 증거를 활용한 실용적인 MySQL 쿼리 튜닝 가이드입니다.
MySQL 쿼리 최적화: 실용적인 방법 가이드
느린 MySQL 쿼리는 실행 계획을 살펴보면 대개 명확해집니다. 어려운 점은 인덱스가 중요하다는 것을 모르는 것이 아닙니다. 어려운 점은 어떤 쿼리가 느린지 증명하고, MySQL이 특정 계획을 선택한 이유를 이해하며, 쓰기, 저장소 또는 다른 쿼리에 악영향을 주지 않고 쿼리나 인덱스를 변경하는 것입니다.
증거부터 시작하세요. 느린 쿼리 로그, Performance Schema, 애플리케이션 추적 또는 PMM과 같은 모니터링 도구를 사용하여 실제로 사용자에게 영향을 주는 쿼리를 찾으세요. 그런 다음 EXPLAIN을 사용하고, 안전할 때는 EXPLAIN ANALYZE를 사용하여 MySQL이 무엇을 하고 있는지 확인하세요.
쿼리 성능 이해
일반적인 원인은 다음과 같습니다:
- 인덱스 누락 또는 비효율적인 인덱스: 적절한 인덱스가 없으면 MySQL은 전체 테이블 스캔을 수행해야 하며, 이는 큰 테이블에서 매우 비효율적입니다.
- 잘못 작성된 SQL: 비-sargable 필터, 불필요한
SELECT *, 우발적인 크로스 조인, 비효율적인 조인 조건은 모두 성능을 저하시킬 수 있습니다. - 대용량 데이터 세트: 더 많은 데이터는 읽기, 정렬, 그룹화 및 캐싱에 더 많은 페이지를 필요로 합니다.
- 하드웨어 및 구성: 최적이 아닌 서버 구성이나 불충분한 하드웨어 리소스도 역할을 할 수 있지만, 이 가이드는 쿼리 수준 최적화에 중점을 둡니다.
EXPLAIN의 힘
EXPLAIN은 MySQL이 쿼리를 어떻게 계획하는지 이해하려고 할 때 가장 먼저 사용하는 도구입니다. 일반 EXPLAIN SELECT의 경우 MySQL은 결과 집합을 반환하지 않고 옵티마이저가 선택한 계획을 보여줍니다. EXPLAIN ANALYZE는 쿼리를 실행하고 실제 타이밍을 보고하므로 프로덕션 시스템에서는 주의해서 사용하세요.
EXPLAIN 사용 방법
읽기 쿼리의 경우 EXPLAIN을 앞에 붙이세요:
EXPLAIN SELECT * FROM users WHERE username = 'john_doe';
EXPLAIN 출력 해석
EXPLAIN의 출력은 몇 가지 중요한 열이 있는 테이블입니다:
id: 쿼리 내 SELECT의 시퀀스 번호입니다. 일반적으로 숫자가 높을수록 먼저 실행됩니다.select_type: SELECT 유형입니다 (예:SIMPLE,PRIMARY,SUBQUERY,DERIVED).table: 액세스 중인 테이블입니다.partitions: 사용된 파티션입니다 (파티셔닝이 활성화된 경우).type: 조인 유형입니다. 이것은 가장 유용한 열 중 하나입니다. 쿼리 형태가 허용할 때const,eq_ref,ref또는range를 목표로 하세요. 큰 테이블에서index와 특히ALL은 의심스러워하세요.possible_keys: MySQL이 사용할 수 있는 인덱스를 보여줍니다.key: MySQL이 실제로 사용하기로 선택한 인덱스입니다.key_len: MySQL이 사용할 것으로 예상하는 인덱스 부분의 길이입니다. 짧다고 자동으로 더 좋은 것은 아닙니다. 선택도와 쿼리에 따라 다릅니다.ref: 인덱스(key)와 비교되는 열 또는 상수입니다.rows: MySQL이 검사할 것으로 예상하는 행 수의 추정치입니다.filtered: 테이블 조건에 의해 필터링된 행의 백분율입니다.Extra: MySQL이 쿼리를 해결하는 방법에 대한 추가 정보를 포함합니다. 주목해야 할 주요 값:Using where: MySQL이 행을 처리하는 동안 조건을 적용함을 나타냅니다. 일반적이며 항상 나쁜 것은 아닙니다.Using index: 쿼리가 인덱스로 커버됨을 의미합니다 (필요한 모든 열이 인덱스에 있음). 좋은 신호입니다.Using temporary: MySQL이 임시 테이블을 생성해야 합니다. 종종GROUP BY또는ORDER BY작업에서 발생합니다. 느릴 수 있습니다.Using filesort: MySQL이 외부 정렬을 수행해야 합니다 (인덱스를 사용하여 정렬하지 않음). 이는 종종 비효율적인ORDER BY절의 신호입니다.
EXPLAIN으로 병목 현상 식별
일반적인 시나리오와 EXPLAIN이 문제를 식별하는 방법을 살펴보겠습니다:
시나리오 1: 전체 테이블 스캔
다음과 같은 쿼리를 고려하세요:
SELECT * FROM orders WHERE order_date = '2023-10-26';
order_date 열에 인덱스가 없으면 EXPLAIN은 다음과 같이 표시될 수 있습니다:
+----+-------------+--------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+------+---------+-------------+
| 1 | SIMPLE | orders | ALL | NULL | NULL | NULL | NULL | 1000000 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+---------+-------------+
문제: type: ALL은 전체 테이블 스캔을 나타냅니다. rows: 1000000은 MySQL이 orders 테이블의 모든 행을 검사해야 함을 보여줍니다. key: NULL은 인덱스가 사용되지 않았음을 의미합니다.
해결책: order_date 열에 인덱스를 추가하세요:
CREATE INDEX idx_order_date ON orders (order_date);
인덱스를 추가한 후 EXPLAIN을 다시 실행하세요. ref 또는 range와 같은 더 선택적인 액세스 유형이 표시되어야 하며, 날짜 필터가 선택적이라면 예상 행 수도 감소해야 합니다.
시나리오 2: 비효율적인 ORDER BY 또는 GROUP BY
SELECT customer_id, COUNT(*) FROM orders GROUP BY customer_id ORDER BY customer_id;
customer_id에 인덱스가 없으면 EXPLAIN은 다음과 같이 표시될 수 있습니다:
+----+-------------+--------+-------+---------------+------+---------+------+--------+----------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+---------------+------+---------+------+--------+----------------------------------+
| 1 | SIMPLE | orders | index | NULL | NULL | NULL | NULL | 100000 | Using temporary; Using filesort |
+----+-------------+--------+-------+---------------+------+---------+------+--------+----------------------------------+
문제: Using temporary와 Using filesort는 MySQL이 데이터를 정렬하고 그룹화하기 위해 비용이 많이 드는 작업을 수행하고 있음을 나타냅니다. 이는 종종 그룹화와 정렬 요구 사항을 모두 효율적으로 충족할 수 있는 인덱스가 없기 때문입니다.
해결책: 이 특정 쿼리의 경우 (customer_id)에 인덱스를 생성하면 MySQL이 그룹화 순서로 행을 스캔할 수 있습니다. 실제 쿼리가 먼저 날짜, 상태 또는 테넌트로 필터링하는 경우 (tenant_id, status, customer_id)와 같은 복합 인덱스가 더 나을 수 있습니다.
CREATE INDEX idx_customer_id ON orders (customer_id);
시나리오 3: 불필요하게 SELECT * 사용
모든 열(*)을 선택하지만 몇 개만 필요한 경우 더 많은 데이터를 전송하고 커버링 인덱스가 유용해지는 것을 방지할 수 있습니다. 이는 JSON 열, 텍스트 블롭 또는 많은 nullable 필드가 있는 넓은 테이블에서 특히 두드러집니다.
-- 'status'에 인덱스가 있다고 가정
SELECT * FROM tasks WHERE status = 'pending';
EXPLAIN은 Using where를 표시할 수 있지만 쿼리에 필터링에 사용된 인덱스에 없는 열이 필요한 경우 여전히 테이블 데이터에 액세스해야 합니다.
해결책: 필요한 열만 지정하세요:
SELECT task_id, description FROM tasks WHERE status = 'pending';
이 정확한 형태의 쿼리를 자주 실행하는 경우 필터 열과 반환 열을 포함하는 커버링 인덱스를 고려하세요:
CREATE INDEX idx_tasks_status_id_description
ON tasks (status, task_id, description);
모든 쿼리에 대해 커버링 인덱스를 생성하지 마세요. 읽기 속도를 높이지만 저장소 및 쓰기 오버헤드가 발생합니다.
느린 쿼리 재작성
인덱싱 외에도 SQL을 구성하는 방식이 MySQL이 수행해야 하는 작업량을 변경할 수 있습니다.
상관 서브쿼리 피하기
상관 서브쿼리는 외부 쿼리에서 처리되는 각 행에 대해 한 번씩 실행될 수 있습니다. MySQL은 일부를 최적화할 수 있지만 EXPLAIN이 반복적인 종속 조회를 보여주는 경우 조인 또는 파생 테이블이 일반적으로 더 명확하고 빠릅니다.
종종 비효율적:
SELECT o.order_id, o.order_date
FROM orders o
WHERE o.customer_id IN (
SELECT c.customer_id
FROM customers c
WHERE c.country = 'USA'
);
종종 조인으로 더 나음:
SELECT o.order_id, o.order_date
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE c.country = 'USA';
두 버전 모두에 EXPLAIN을 사용하세요. 조인이 모든 스키마에서 자동으로 더 빠른 것은 아니지만 많은 팀이 추론하고 인덱싱하기 더 쉽습니다.
LIKE 절 최적화
LIKE 절의 선행 와일드카드(%)는 일반적으로 일반 B-트리 인덱스가 범위 검색에 사용되는 것을 방지합니다.
비효율적:
SELECT * FROM products WHERE product_name LIKE '%widget';
더 나음 (가능한 경우):
SELECT * FROM products WHERE product_name LIKE 'widget%';
포함 스타일 매칭이 필요한 경우 적절한 텍스트 검색을 위한 MySQL 전체 텍스트 인덱스, 특정 언어를 위한 n-gram 접근 방식, 또는 관련성과 유연한 매칭이 중요할 때 검색 엔진을 고려하세요.
가능하면 UNION 대신 UNION ALL 사용
UNION은 중복 행을 제거하므로 추가 정렬 및 중복 제거 단계가 필요합니다. 중복이 없거나 제거할 필요가 없다는 것을 알고 있다면 UNION ALL이 더 빠릅니다.
느림:
SELECT name FROM table1
UNION
SELECT name FROM table2;
빠름:
SELECT name FROM table1
UNION ALL
SELECT name FROM table2;
기타 최적화 팁
- 통계 업데이트 유지: 쿼리 옵티마이저가 정보에 기반한 결정을 내릴 수 있도록 테이블 통계가 최신 상태인지 확인하세요. 이는 일반적으로 자동으로 처리되지만
ANALYZE TABLE로 수동으로 업데이트할 수 있습니다. - 서버 구성: 쿼리 튜닝은 작은 InnoDB 버퍼 풀이나 과부하된 디스크를 보상하지 않습니다. MySQL 8.0에서는 이전 쿼리 캐시가 제거되었으므로
query_cache_size를 중심으로 새 튜닝을 계획하지 마세요. - 정기적인 모니터링: MySQL Enterprise Monitor, Percona Monitoring and Management (PMM) 또는 내장된 Performance Schema 뷰와 같은 도구를 사용하여 느린 쿼리를 추적하고 추세를 식별하세요.
실용적인 튜닝 워크플로우
프로덕션 시스템의 경우 느린 쿼리부터 바깥쪽으로 튜닝하세요:
- 정확한 SQL, 바인드 값, 행 수 및 타이밍을 캡처합니다.
- MySQL 버전이 지원하는 경우
EXPLAIN FORMAT=TREE또는EXPLAIN FORMAT=JSON을 실행합니다. - 선택한 인덱스가 필터 및 조인 패턴과 일치하는지 확인합니다.
- 실제 데이터로 쿼리 재작성 또는 인덱스 변경을 테스트합니다.
- 검사된 행, 임시 테이블, 정렬 동작 및 실제 지연 시간을 비교합니다.
이렇게 하면 쿼리가 "느려 보인다"는 이유로 인덱스를 추가하는 것을 방지할 수 있습니다. 인덱스에는 비용이 따릅니다. 모든 INSERT, UPDATE 및 DELETE는 이를 유지 관리해야 합니다. 10개의 중복 인덱스가 있는 테이블은 하나의 읽기 쿼리가 개선되더라도 전반적으로 더 느려질 수 있습니다.
일반적인 멀티 테넌트 애플리케이션 쿼리의 경우 인덱스 순서가 인덱스된 열 수보다 더 중요한 경우가 많습니다:
SELECT id, created_at, total
FROM orders
WHERE tenant_id = 42
AND status = 'paid'
AND created_at >= '2025-01-01'
ORDER BY created_at DESC
LIMIT 50;
유용한 인덱스는 다음과 같을 수 있습니다:
CREATE INDEX idx_orders_tenant_status_created
ON orders (tenant_id, status, created_at DESC);
해당 인덱스는 동등 필터로 시작한 다음 날짜 범위와 정렬을 지원합니다. created_at을 먼저 넣으면 MySQL이 올바른 테넌트를 찾기 전에 많은 테넌트를 스캔할 수 있습니다. status를 생략하면 쿼리가 여전히 작동할 수 있지만 많은 추가 행을 검사합니다.
비-sargable 필터 주의
조건이 sargable하다는 것은 MySQL이 인덱스를 사용하여 일치하는 행을 검색할 수 있음을 의미합니다. 인덱스된 열을 함수로 감싸면 종종 이를 깨뜨립니다:
-- created_at 인덱스를 사용하기 어려움
SELECT * FROM orders
WHERE DATE(created_at) = '2025-01-15';
범위로 다시 작성하세요:
SELECT *
FROM orders
WHERE created_at >= '2025-01-15'
AND created_at < '2025-01-16';
두 번째 버전은 MySQL이 created_at 인덱스를 검색할 수 있게 합니다. 동일한 아이디어가 LOWER(email), 숫자 열에 대한 수학 연산 및 암시적 유형 변환에도 적용됩니다. 열이 인덱스된 경우 가능하면 열 쪽의 비교를 깨끗하게 유지하세요.
페이지네이션 주의
오프셋 페이지네이션은 깊은 페이지에서 비용이 많이 듭니다:
SELECT id, title
FROM posts
WHERE status = 'published'
ORDER BY published_at DESC
LIMIT 20 OFFSET 200000;
MySQL은 요청한 페이지를 반환하기 전에 여전히 이전 행을 통과해야 합니다. 피드, 감사 로그 및 관리자 테이블의 경우 키셋 페이지네이션이 일반적으로 더 좋습니다:
SELECT id, title, published_at
FROM posts
WHERE status = 'published'
AND (published_at, id) < ('2025-05-01 12:00:00', 987654)
ORDER BY published_at DESC, id DESC
LIMIT 20;
(status, published_at, id)와 같은 인덱스와 함께 사용하세요. 이는 사용자가 10,000페이지로 점프하는 대신 커서를 통해 이동하기 때문에 제품 동작을 약간 변경하지만, 고통스러운 쿼리를 예측 가능한 쿼리로 바꿀 수 있습니다.
실제 데이터로 검증
작은 스테이징 데이터베이스는 거짓말을 합니다. 20,000행에서 즉각적인 쿼리는 특히 데이터 분포가 치우친 경우 2억 행에서 끔찍할 수 있습니다. 가능하면 프로덕션과 유사한 볼륨 및 카디널리티로 테스트하세요. 프로덕션 데이터를 복사할 수 없는 경우 최소한 유사한 테넌트 크기, 상태 분포 및 날짜 범위로 데이터를 생성하세요.
한 가지 더 도움이 되는 습관: 이전 계획과 새 계획을 티켓에 보관하세요. 미래의 당신은 인덱스가 존재하는 이유를 알고 싶어할 것입니다.
최고의 MySQL 튜닝 습관은 모든 변경 사항이 그 자리를 차지하도록 하는 것입니다. 느린 쿼리를 캡처하고, 계획을 검사하고, 하나의 쿼리 또는 인덱스를 변경한 다음 지연 시간과 검사된 행을 비교하세요. 깔끔한 EXPLAIN 계획은 유용하지만, 실제 승리는 새로운 쓰기 압력이나 저장소 팽창을 만들지 않으면서 프로덕션 지연 시간을 낮추는 것입니다.