MySQL 쿼리 최적화: 실용적인 방법 안내서
느린 데이터베이스 쿼리는 모든 애플리케이션의 심각한 병목 현상이 될 수 있으며, 이는 사용자 경험 저하 및 인프라 비용 증가로 이어집니다. 다행히도 MySQL은 이러한 성능 문제를 진단하고 해결할 수 있는 강력한 도구를 제공합니다. 이 가이드는 실용적인 적용과 명확한 이해에 중점을 두고 MySQL 쿼리 최적화를 위한 필수 기술들을 안내할 것입니다.
쿼리 실행 계획을 이해하기 위해 EXPLAIN 문을 사용하는 방법, 일반적인 성능 함정 식별, 비효율적인 쿼리 재작성 전략 등을 다룰 것입니다. 이러한 기술들을 숙달하면 데이터베이스 응답성과 전반적인 애플리케이션 성능을 크게 향상시킬 수 있습니다.
쿼리 성능 이해하기
최적화에 뛰어들기 전에 쿼리가 느린 이유를 이해하는 것이 중요합니다. 일반적인 원인은 다음과 같습니다.
- 인덱스 누락 또는 비효율성: 적절한 인덱스 없이는 MySQL이 전체 테이블 스캔을 수행해야 하며, 이는 대용량 테이블의 경우 매우 비효율적입니다.
- 잘못 작성된 SQL: 복잡한 하위 쿼리,
SELECT *, 비효율적인 조인 조건 등은 모두 성능을 저하시킬 수 있습니다. - 대용량 데이터 세트: 방대한 양의 데이터를 처리하는 것만으로도 작업 속도가 자연스럽게 느려질 수 있습니다.
- 하드웨어 및 구성: 최적이 아닌 서버 구성이나 불충분한 하드웨어 리소스도 영향을 미칠 수 있지만, 이 가이드는 쿼리 수준 최적화에 중점을 둡니다.
EXPLAIN의 강력함
EXPLAIN 문은 MySQL이 쿼리를 어떻게 실행하는지 이해하기 위한 주요 도구입니다. 실행 계획에 대한 통찰력을 제공하여 테이블이 어떻게 조인되는지, 어떤 인덱스가 사용되는지, 행이 어떻게 스캔되는지를 보여줍니다. 실제로 쿼리를 실행하지 않으므로 프로덕션 시스템에서 안전하게 사용할 수 있습니다.
EXPLAIN 사용 방법
SELECT, INSERT, DELETE, UPDATE, 또는 REPLACE 문 앞에 단순히 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: 선택된 키의 길이입니다. 일반적으로 짧을수록 좋습니다.ref: 인덱스 (key)와 비교되는 열 또는 상수입니다.rows: 쿼리 실행을 위해 MySQL이 검사해야 하는 행 수에 대한 추정치입니다.filtered: 테이블 조건으로 필터링된 행의 백분율입니다.Extra: MySQL이 쿼리를 해결하는 방법에 대한 추가 정보를 포함합니다. 주목해야 할 주요 값은 다음과 같습니다.Using where: 행을 가져온 후WHERE절을 사용하여 행을 필터링함을 나타냅니다.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을 다시 실행하십시오. 이제 훨씬 효율적인 type (예: ref 또는 range)과 훨씬 낮은 rows 수를 보게 될 것입니다.
시나리오 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)에 대한 인덱스로 충분할 수 있습니다. 쿼리가 더 복잡했다면 복합 인덱스가 필요했을 수 있습니다.
CREATE INDEX idx_customer_id ON orders (customer_id);
시나리오 3: 불필요한 SELECT * 사용
모든 열(*)을 선택하지만 몇 개만 필요한 경우, WHERE 절 열에 인덱스가 있더라도 MySQL이 쿼리를 커버하기 위해 인덱스를 사용하는 것을 방해할 수 있습니다. 이로 인해 추가적인 테이블 조회가 발생합니다.
-- 'status'에 인덱스가 있다고 가정
SELECT * FROM tasks WHERE status = 'pending';
EXPLAIN은 Using where를 표시할 수 있지만, 쿼리가 필터링에 사용된 인덱스에 없는 열을 필요로 한다면 여전히 테이블 데이터에 액세스해야 합니다.
해결책: 필요한 열만 지정합니다.
SELECT task_id, description FROM tasks WHERE status = 'pending';
특정 열과 다른 열을 자주 쿼리하는 경우, 쿼리에 필요한 모든 열을 포함하는 커버링 인덱스를 만드는 것을 고려해 보십시오.
느린 쿼리 재작성
인덱싱 외에도 SQL 구조 방식이 성능에 큰 영향을 미칠 수 있습니다.
상관 서브쿼리 피하기
상관 서브쿼리는 외부 쿼리가 처리하는 행마다 한 번씩 실행됩니다. 이들은 종종 비효율적입니다.
비효율적:
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'
);
효율적 (JOIN 사용):
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 절의 선행 와일드카드 (%)는 인덱스 사용을 방해합니다.
비효율적:
SELECT * FROM products WHERE product_name LIKE '%widget';
더 나은 방법 (가능한 경우):
SELECT * FROM products WHERE product_name LIKE 'widget%';
선행 와일드카드가 반드시 필요한 경우, 전문 검색 인덱싱 또는 대체 검색 솔루션을 고려해 보십시오.
가능한 경우 UNION ALL을 UNION 대신 사용
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_buffer_pool_size,query_cache_size(MySQL 8.0에서 더 이상 사용되지 않음),sort_buffer_size와 같은 MySQL 구성 변수를 검토하는 것이 중요합니다. - 정기적인 모니터링: MySQL Enterprise Monitor, Percona Monitoring and Management (PMM) 또는 내장 성능 스키마 뷰와 같은 도구를 사용하여 느린 쿼리를 추적하고 추세를 파악하십시오.
결론
MySQL 쿼리 최적화는 데이터 이해, EXPLAIN과 같은 진단 도구 사용, SQL 작성 모범 사례 적용을 결합하는 반복적인 프로세스입니다. 인덱싱에 집중하고, 전체 테이블 스캔을 피하며, 쿼리를 효율적으로 구성함으로써 애플리케이션 성능과 확장성을 극적으로 향상시킬 수 있습니다. 항상 변경 사항을 테스트하고 그 영향을 측정하십시오.
최적화 파이팅!