05AM
쿼리 실행 계획 분석 (feat. 각 항목의 의미) 본문
쿼리 실행 계획은 사용자가 SQL을 실행하여 데이터를 추출하려고 할 때 DBMS의 옵티마이저가 수립하는 작업 절차이다. 즉, 실행계획을 통해 쿼리가 어떻게, 어떤 순서로 실행되는지 구체적으로 볼 수 있다.
MySQL에서는 `EXPLAIN`이나 `EXPLAIN ANALYZE` 명령어로 쿼리의 실행 계획을 분석할 수 있는데, 이번 포스팅에서는 실행 계획에 포함되는 정보가 무엇이 있는지, 해당 정보를 어떻게 해석하면 좋을지를 알아보자.
🔷 실행 계획 예시
SELECT ml.*
FROM mission_log ml JOIN user ON ml.user_id = user.id
WHERE ml.created_at = :today;
위는 오늘 수행한 미션 기록을 유저의 id로 조인하여 조회하는 쿼리문이다. 지금부터는 각 컬럼의 의미를 알아보자.
🔷 실행 계획 항목
1. id
의미: 각 쿼리 단계의 식별자
값: 정수. 각 단계마다 고유한 값을 가진다.
2. select_type
의미: 각 쿼리 단계의 유형
값:
- SIMPLE: 서브쿼리나 UNION이 없는 단순 SELECT
- PRIMARY: 쿼리의 메인 SELECT
- UNION: UNION의 두 번째 이후의 SELECT
- DEPENDENT UNION: 외부 쿼리에 종속된 UNION
- SUBQUERY: 서브쿼리의 첫번째 SELECT
- DEPENDENT SUBQUERY: 외부 쿼리에 종속된 서브쿼리
- DERIVED: from절의 서브쿼리
SIMPLE이나 PRIMARY가 일반적으로 좋다.
DEPENDENT SUBQUERY와 DEPENDENT UNION은 외부 쿼리의 각 행에 대해 반복적으로 실행해야 하기 때문에 성능에 부담을 줄 수 있다.
3. table
의미: 쿼리에서 사용되는 테이블 이름
값: 테이블 이름 또는 별칭
값 자체는 중요하지 않지만, 각 테이블에 대한 인덱스 사용 여부가 중요하다.
4. partitions
의미: 쿼리에서 참조하는 파티션
값: 파티션 이름. NULL이면 파티션을 사용하지 않음을 의미
특정 파티션을 사용하는 경우 쿼리 성능이 향상될 수 있다. 파티션을 사용하는 것이 적절한지 확인해야 한다.
5. type
좋은 순서대로 적혀있으며, 아래로 내려갈 수록 성능이 좋지 않다.
의미: 테이블 조인 유형
값:
- system: 테이블에 한 행만 있는 경우 (= 시스템 테이블) 매우 빠름
SELECT * FROM single_row_table;
- const: 매치되는 행이 하나만 존재해 상수로 간주될 수 있는 경우 매우 빠름
id가 기본 키인 경우, 이 쿼리는 항상 하나의 결과만 반환하므로 상수처럼 처리된다.
SELECT * FROM users WHERE id = 1;
- eq_ref: 조인수행을 위해 각 테이블에서 하나의 행만이 읽혀지는 경우 빠름
일반적으로 기본 키 또는 고유 인덱스를 기반으로 하는 조인에서 발생한다.
SELECT * FROM orders
JOIN customers ON orders.customer_id = customers.id;`customers.id`가 기본 키이고, `orders.customer_id`가 외래 키로 설정된 경우, 각 주문에 대해 정확히 하나의 고객이 매칭된다.
- ref: 조인이 키 값을 기반으로 단일 행을 선택할 수 없어 매칭되는 여러 행을 찾는 경우 비교적 빠름
일반적으로 기본 키나 고유 키가 아닌 인덱스를 사용하여 조회하는 경우이다.
SELECT * FROM orders WHERE customer_id = 123;`customer_id`에 인덱스가 있지만, 중복된 값이 있을 수 있다. 따라서 여러 행이 반환될 수 있다.
- range: 범위 검색을 사용하는 경우 중간 정도 성능
일반적으로 BETWEEN, <, >, >=, <= 등의 조건을 사용하여 검색하는 경우이다.
SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31';`order_date`에 인덱스가 있는 경우, 특정 범위 내의 행을 검색한다.
- index: 전체 인덱스를 스캔하는 경우 비교적 느림
일반적으로 데이터 파일을 읽지 않고 인덱스 파일만 읽는 경우이다.
SELECT id FROM orders;orders 테이블에 대해 인덱스가 `id` 컬럼에만 있다면, 이 쿼리는 전체 인덱스를 스캔하게 된다.
- ALL: 전체 테이블을 스캔하는 경우 가장 느림
인덱스가 사용되지 않고, 모든 행을 읽어야 하는 경우이다.
SELECT * FROM orders;
ALL은 인덱스가 없거나, 인덱스를 사용할 수 있는 쿼리 조건이 적용될 때 인덱스를 사용하지 않고 테이블의 모든 행을 스캔하게 되어 성능이 가장 떨어진다.
6. possible_keys
의미: 쿼리에서 사용될 가능성이 있는 인덱스 목록
값: 인덱스 이름들의 리스트
사용 가능한 인덱스가 많을 수록 좋기 때문에 가능한 한 많은 관련 인덱스가 나열되는 것이 좋다. NULL이면 인덱스가 전혀 사용되지 않음을 의미하므로 좋지 않다.
7. key
의미: 쿼리에서 실제로 사용된 인덱스
값: 인덱스 이름
관련성 있는 인덱스가 적절하게 사용되는 것이 좋다. NULL이면 인덱스가 사용되지 않음을 의미하므로 좋지 않다.
8. key_len
의미: 사용된 인덱스의 키 길이
값: 정수(바이트 단위)
인덱스에서 특정 열 또는 열들의 조합에 의해 생성된 값을 `키`, 해당 데이터 위치를 가리키는 포인터가 `값`으로 트리 구조에 주로 저장되는데, key_len은 이때 사용되는 인덱스 키의 길이이다.
인덱스의 키는 포함된 열의 데이터 타입, 인덱스 포함 열의 수 등에 의해 그 크기가 결정된다.
트리 구조에서 값을 찾을 때 키를 비교하며 탐색하는데, 이 키의 길이가 짧을 수록 비교 성능이 좋아지므로 가능한 짧은 키 길이가 좋다. 너무 길면 불필요한 데이터가 포함되었을 수 있다.
9. ref
의미: 조인에서 참조된 컬럼
값: 테이블 이름 및 컬럼 이름
조인 조건이 명확하게 지정되는 것이 좋다.
10. rows
의미: 이 단계에서 읽어야 한다고 예상되는 행의 수
값: 정수
rows는 테이블과 인덱스의 통계 정보를 바탕으로 추정되는 값이다.
가능한 적은 행을 읽는 것이 좋으며, 큰 값은 성능에 부정적인 영향을 미칠 수 있다.
11. filtered
의미: 조건을 충족할 것이라고 예상되는 행의 비율
값: 백분율(0~100)
마찬가지로 테이블과 인덱스의 통계 정보를 바탕으로 추정되는 값이다.
비율이 높을 수록 조건을 잘 충족하는 행의 비율이 높다는 것을 의미하기 때문에 값이 높은 것이 좋다.
12. Extra
의미: 추가적인 정보와 최적화 힌트
값:
- Using where: WHERE 조건이 적용됨
where 조건에 의한 필터링을 의미하기에 중립적인 값이라고 볼 수 있다.
- Using index: 인덱스만 사용하여 데이터를 읽음
쿼리가 인덱스만 사용하여 데이터를 가져오며, 실제 테이블을 읽지 않기 때문에 일반적으로 좋다고 볼 수 있다.
- Using index condition: 인덱스 조건을 사용하여 인덱스 내의 데이터를 필터링함
보통 인덱스를 사용할 수 있지만, 추가적인 조건이 필요하므로 성능이 최적은 아닐 수 있다.
- Using join buffer: 조인 버퍼가 사용됨
조인을 위해 메모리 내의 조인 버퍼를 사용한다는 것은 중립적이거나 부정적인 값이라고 볼 수 있다.
조인 버퍼는 특정 상황에서 MySQL이 조인을 효율적으로 처리하기 위해 사용하는 메모리 공간으로 인덱스가 없는 경우, 외부 조인, 큰 테이블 간의 조인, 복합 조건 조인의 경우에 사용될 수 있다.
조인 버퍼를 사용해서 조인을 수행할 때는 BNLJ (Block Nested Loop Join) 방식을 주로 사용하는데, 외부 루프 테이블의 블록 단위로 데이터를 읽어 조인 버퍼에 저장하고, 내부 루프 테이블의 각 행을 읽어 조인 버퍼에 있는 데이터와 비교하여 조인 조건을 충족하는지 확인하는 방식이다.
따라서 조인 버퍼를 사용한다는 것은 메모리를 활용하여 효율적으로 조인을 처리한다는 뜻일 수도 있지만, 조인 용량이 너무 커서 메모리를 사용해야 한다는 뜻일 수도 있다. 즉, 중립적으로도, 부정적으로도 볼 수 있으므로 조치를 취해야 한다는 신호일 수 있다.
조인 조건에 인덱스를 생성하고, 쿼리 구조를 최적화하여 피할 수 있다.
- Using temporary: 임시 테이블이 사용됨
쿼리를 처리하는 동안 중간 결과를 임시 테이블에 저장해야 할 때 발생한다.
임시 테이블을 사용하면 중간 결과를 저장하기 때문에 메모리와 디스크 I/O를 증가시켜 성능을 저하시킨다. 따라서 부정적인 값이라고 볼 수 있다.
주로 복잡한 서브쿼리나 조인, `GROUP BY`나 `ORDER BY`를 사용할 때 발생한다.
서브쿼리나 조인의 경우 그 복잡성에 따라 임시 테이블이 생성될 수도 있고, 생성되지 않을 수도 있다.
가능한 한 서브쿼리를 단순화하거나 조인을 사용하고, 필요한 경우 인덱스를 생성하여 성능을 최적화 할 수 있다.
`GROUP BY`나 `ORDER BY`의 경우 결과를 정렬하거나 그룹화할 때 결과 데이터의 크기가 크거나, 사용되는 열에 인덱스가 없거나, 복잡한 정렬 조건이 있을 때 중간 결과를 임시 테이블에 따로 저장하여 처리한다.
그룹화나 정렬에 사용되는 열에 적절한 인덱스를 생성하면 임시 테이블 생성을 막을 수도 있다. 하지만 인덱스를 생성하는 것은 그에 따른 부작용도 따라오기에 신중히 결정하는 것이 좋다.
- Using filesort: 파일 정렬이 사용됨
ORDER BY나 GROUP BY 구문을 처리할 때 사용되는 정렬 방법 중 하나로, 파일 정렬은 추가적인 디스크 I/O를 발생시켜 성능을 저하시킬 수 있기 때문에 부정적인 값이라고 볼 수 있다.
사용될만한 적절한 인덱스가 없거나, 복잡한 ORDER BY, GROUP BY 구문 혹은 처리해야할 데이터 양이 많을 때 등등의 상황에서 메모리에 올리지 못하고 디스크를 사용하게 되어 file sort가 발생할 수 있다.
적절한 인덱스를 추가하거나, 커버링 인덱스 사용, 테이블 구조 최적화, 쿼리 리팩토링 등의 방법으로 개선할 수 있다.
- Distinct: 중복 제거를 위해 추가 작업이 필요함
대규모 데이터 집합이나 복잡한 쿼리 결과에서 중복을 제거하려 DISTINCT 키워드를 사용하는 것은 성능 저하로 이어질 수 있다. 따라서 부정적인 값이라고 볼 수 있다.
Distinct를 사용하는 열에 인덱스를 생성하거나, 특정 상황에서 더 효율적으로 중복을 제거할 수 있는 GROUP BY를 대안으로 사용하거나, 결과 데이터에서 중복 행을 최소화하기 위해 쿼리를 최적화하고 테이블을 정규화하는 방식으로 개선할 수 있다.
🔷 정리
지금까지 실행계획의 각 항목이 어떤 의미를 갖고, 어떤 방식으로 개선할 수 있는지를 알아봤다. 데이터베이스와 관련된 것들이 으레 그렇듯 정답이 있기보다 직접 실행하고 테스트하고 운영해보면서 각자의 상황에 맞는 방법을 찾아나가는 것이 중요한 것 같다.