SQL 성능 저하의 원인 파악하기
데이터베이스에서 수행되는 쿼리의 속도 문제는 개발 프로세스에서 빈번하게 발생하는 과제입니다. 인덱스를 생성하거나 SQL 문을 수정하는 방식만으로는 근본적인 병목 현상을 식별하기 어렵습니다. 이때 필요한 것이 데이터베이스 엔진이 쿼리를 처리하기 위해 준비한 내부 시뮬레이션 결과인 '실행 계획(Execution Plan)'입니다.
EXPLAIN 문을 사용하면 실제 데이터를 추출하지 않고도 쿼리가 어떻게 최적화되어 수행될지 미리 확인해 볼 수 있습니다. 이를 통해 불필요한 인덱스 사용을 방지하고, 전체 테이블 스캔과 같은 비효율적인 연산을 조기에 발견할 수 있습니다.
실행 계획 개요 및 조회 방법
실행计划은 서버가 특정 SELECT 문장을 처리하기 위해 선택한 알고리즘과 접근 경로를 명시합니다. 주요 포함 정보는 다음과 같습니다:
- 참조하는 테이블들의 순서
- 적용된 인덱스의 유무와 종류
- 예상되는 스캔되는 행의 수
- 임시 테이블 사용 여부 및 정렬 방식
확인 방법은 매우 직관적입니다. SELECT 키워드 앞에 EXPLAIN 을 붙이면 됩니다.
EXPLAIN SELECT product_name, price FROM inventory WHERE stock_quantity > 50;
이 명령어를 실행하면 DBMS 는 결과를 반환하지만, 실제 데이터 조작이나 검색 작업은 진행되지 않습니다. 따라서 안전하게 성능 테스트에 활용할 수 있습니다.
핵심 분석 필드 정리
출력되는 결과표는 여러 컬럼으로 구성되지만, 성능 진단에 필수적인 주요 항목들은 제한적입니다.
1. access_type (type)
테이블에 접근하는 방식을 나타내며, 성능 평가의 핵심 지표입니다. 효율성이 높은 순서대로 나열하면 다음과 같습니다:
- const / system: 주키 또는 유니크 인덱스를 통해 단일 행을 즉시 찾음 (최적).
- eq_ref: 조인 조건에서 주로 나타나며, 앞선 테이블의 각 행마다 한 번씩 참조함.
- ref: 일반적인 인덱스 조인이나 등호 조건 검색.
- range: 범위 검색 (BETWEEN, IN 등) 시 인덱스를 활용함.
- index: 전체 인덱트를 순회하지만 데이터 파일 자체는 읽지 않음.
- ALL: 전체 테이블 스캔. 가장 낮은 성능을 의미하므로 반드시 개선 대상입니다.
2. key & possible_keys
possible_keys 는 실제로 사용할 수 있는 후보 인덱스를 나열하며, key 는 선택된 실제 인덱스를 보여줍니다. 만약 후보는 있지만 key 가 NULL 이라면, 작성된 쿼리 로직상 인덱스가 무시되고 있다는 신호입니다.
3. rows
검색 결과물을 도출하기 위해 스캔해야 하는 것으로 예상되는 행 수입니다. 이 수치가 클수록 IO 비용이 증가하므로 최소화가 목표여야 합니다.
4. Extra
추가적인 정보로, Using temporary나 Using filesort 가 표시된다면 메모리나 디스크를 과도하게 사용하는 병목 지점이 될 수 있습니다.
실전 적용 사례: 매출 기록 테이블 최적화
다음은 데이터 대량 처리 환경에서 발생하는 전형적인 성능 이슈를 해결하는 과정입니다.
1. 초기 구조 및 문제 발생
CREATE TABLE sales_data (
sale_id INT PRIMARY KEY,
region_code VARCHAR(10),
total_amount DECIMAL(12, 2),
transaction_date DATETIME
);
-- region_code 에 대한 인덱스는 존재하지 않음
특정 지역 코드로 판매 내역을 조회하려는 요청이 들어왔습니다.
EXPLAIN SELECT * FROM sales_data WHERE region_code = 'SEOUL';
분석 결과: type 필드가 ALL로 표기되어 있으며, 스캔 행 수는 수천 만 건 수준입니다. 인덱스가 없어 모든 데이터를 읽어오고 있습니다.
2. 인덱스 추가 후 재점검
CREATE INDEX idx_region ON sales_data(region_code);
EXPLAIN SELECT * FROM sales_data WHERE region_code = 'SEOUL';
개선 효과: 이제 type 은 ref 으로 변경되었고, 사용된 인덱스로 idx_region 이 표시됩니다. 스캔 예정 행 수가 기하급수적으로 줄어들어 처리 속도가 획기적으로 향상되었습니다.
인덱스 무효화를 유발하는 주의 사항
쿼리 작성 시 다음 패턴들을 주의하지 않으면 EXPLAIN 에서 인덱스가 무시되는 상황을 맞닥뜨리게 됩니다.
1. WHERE 절에 함수 적용
컬럼에 함수를 적용하면 저장된 값이 아닌 계산된 결과가 비교되기 때문에 인덱스 구조가 깨집니다.
- 비추천:
WHERE YEAR(order_date) = 2024 - 권장:
WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31'
2. 복합 인덱스의 왼쪽 규칙 위반
(col_a, col_b, col_c) 형태의 인덱스가 있을 때, col_b 와 col_c 를 조건으로 주는 경우 최왼쪽 컬럼인 col_a 가 생략되면 인덱스가 작동하지 않습니다.
3. 암시적 형 변환 (Implicit Conversion)
데이터 타입이 일관되지 않아 엔진이 자동 타입 캐스팅을 수행하면 인덱스 사용이 중단됩니다. 예를 들어 정수형 컬럼에 문자열 값을 할당할 때 따옴표를 남발하는 경우입니다.
- 문제:
WHERE user_id = '1005'(user_id 가 INT 일 때) - 해결:
WHERE user_id = 1005