Execution Plan

MySQL 실행 계획

실행 계획이란?

MySQL 실행 계획은 데이터베이스가 SQL 쿼리를 실행할 때 선택하는 경로를 의미합니다. 실행 계획을 이해하고 최적화하면 데이터베이스 응답 시간을 크게 줄일 수 있습니다.

실행 계획을 확인하는 방법

실행 계획을 확인하려면 EXPLAIN 키워드를 사용합니다. 이를 통해 MySQL이 쿼리를 어떻게 실행하는지 알 수 있습니다.

EXPLAIN select *
FROM 사원
where 사원번호 BETWEEN 10001 and 20000;

1,SIMPLE,사원,,range,PRIMARY,PRIMARY,4,,18728,100,Using where

실행 계획 읽기

이전 단락에서 EXPLAIN 키워드로 실행 계획을 조회하면 id, select_type, table, type, key 등의 정보가 출력되었습니다.

ID

실행 순서를 표시하는 숫자로, 쿼리가 실행되는 차례를 나타내는 것을 id로 표기합니다. 조인을 할 때는 동일한 id가 표시됩니다.

---

SELECT_TYPE

SQL 문을 구성하는 SELECT 문의 유형을 출력하는 항목입니다. SELECT 문이 단순히 FROM 절에 위치한 것인지, 서브쿼리인지, UNION 절로 묶인 SELECT 문인지 등의 정보를 제공합니다.

SIMPLE

UNION이나 내부 쿼리가 없는 SELECT 문을 의미합니다. 말 그대로 단순한 SELECT 구문으로만 작성된 경우를 가리킵니다.

EXPLAIN SELECT * FROM 사원 WHERE 사원번호 = 100000;

PRIMARY

서브쿼리가 포함된 SQL 문이 있을 때 첫 번째 SELECT 문에 해당하는 구문에 표시되는 유형입니다.

SUBQUERY

독립적으로 수행되는 서브쿼리를 의미합니다. SELECT 절의 스칼라 서브쿼리와 WHERE 절의 중첩 서브쿼리일 경우에 해당합니다.

DERIVED

FROM 절에 작성된 서브쿼리라는 의미입니다. 즉, FROM 절의 별도 임시 테이블인 인라인 뷰를 말합니다.

UNION

SELECT 문에서 첫 번째 SELECT 구문을 제외한 이후의 SELECT 구문에 해당한다는 것을 나타냅니다. 이때 UNION 구문의 첫 번째 SELECT 절은 PRIMARY 유형으로 출력됩니다.

UNION RESULT

UNION ALL이 아닌 UNION 구문으로 SELECT 절을 결합했을 때 출력됩니다. UNION은 출력 결과에 중복이 없는 유일한 속성을 가지므로 각 SELECT 절에서 데이터를 가져와 정렬하여 중복 체크하는 과정을 거칩니다.

한편 UNION 구문으로 결합되기 전의 각 SELECT 문이 중복되지 않는 결과가 보장될 때는 UNION 구문보다는 UNION ALL 구문으로 변경하는 SQL 튜닝을 수행합니다.

DEPENDENT SUBQUERY

UNION 또는 UNION ALL을 사용하는 서브쿼리가 메인 테이블의 영향을 받는 경우를 의미하며, UNION으로 연결된 단위 쿼리들 중 처음으로 작성한 단위 쿼리에 해당합니다.

즉, UNION으로 연결되는 첫 번째 단위 쿼리가 독립적으로 수행하지 못하고 메인 테이블로부터 값을 하나씩 공급받는 구조 (AND 사원.사원번호 = 관리자.사원번호)이므로 성능적으로 불리하여 SQL 문이 튜닝 대상이 됩니다.

DEPENDENT UNION

UNION 또는 UNION ALL을 사용하는 서브쿼리가 메인 테이블의 영향을 받는 경우로, UNION으로 연결된 단위 쿼리 중 첫 번째 단위 쿼리를 제외하고 두 번째 단위 쿼리에 해당됩니다.

즉, UNION으로 연결되는 두 번째 이후의 단위 쿼리가 독립적으로 수행하지 못하고 메인 테이블로부터 값을 하나씩 공급받는 구조이므로 성능적으로 불리하여 SQL 문이 튜닝 대상이 됩니다.

UNCACHEABLE SUBQUERY

말 그대로 메모리에 상주하여 재활용되어야 할 서브쿼리가 재사용되지 못할 때 출력되는 유형입니다.

해당 서브쿼리 안에 사용자 정의 함수나 사용자 변수가 포함되거나 RAND(), UUID() 함수 등을 사용하여 매번 조회 시마다 결과가 달라지는 경우에 해당됩니다.

만약 자주 호출되는 SQL 문이라면 메모리에 서브쿼리 결과가 상주할 수 있도록 변경하는 방향으로 SQL 튜닝을 검토해볼 수 있습니다.

MATERIALIZED

IN 절 구문에 연결된 서브쿼리가 임시 테이블을 생성한 뒤, 조인이나 가공 작업을 수행할 때 출력되는 유형입니다. 즉, IN 절의 서브쿼리를 임시 테이블로 만들어서 조인 작업을 수행하는 것을 의미합니다.

---

TABLE

table은 말 그대로 테이블명을 표시하는 항목입니다. 실행 계획 정보에서 테이블명이나 테이블 별칭(alias)을 출력하며, 서브쿼리나 임시 테이블을 만들어서 별도의 작업을 수행할 때는 <subquery#>나 <derived#>라고 출력됩니다.

partitions

실행 계획의 부가 정보로, 데이터가 저장된 논리적인 영역을 표시하는 항목입니다. 사전에 정의한 전체 파티션 중 특정 파티션에 선택적으로 접근하는 것이 SQL 성능 측면에서 유리합니다. 만약 너무 많은 영역의 파티션에 접근하는 것으로 출력된다면 파티션 정의를 튜닝해봐야 할 것입니다.

TYPE

테이블의 데이터를 어떻게 찾을지에 관한 정보를 제공하는 항목입니다. 테이블을 처음부터 끝까지 전부 확인할지, 아니면 인덱스를 통해 바로 데이터를 찾아갈지 등을 해석할 수 있습니다.

SYSTEM

테이블에 데이터가 없거나 한 개만 있는 경우로, 성능상 최상의 type이라고 할 수 있습니다.

CONST

조회되는 데이터가 단 1건일 때 출력되는 유형으로, 성능상 매우 유리한 방식입니다.

고유 인덱스나 기본키를 사용하여 단 1건의 데이터에만 접근하면 되므로, 속도나 리소스 사용 측면에서 지향해야 할 타입입니다.

EQ_REF

조인이 수행될 때 드리븐 테이블의 데이터에 접근하며, 고유 인덱스 또는 기본 키로 단 1건의 데이터를 조회하는 방식입니다.

드라이빙 테이블과의 조인 키가 드리븐 테이블에 유일하므로 조인이 수행될 때 성능상 가장 유리한 경우라고 할 수 있습니다.

REF

조인을 수행할 때 드리븐 테이블의 데이터 접근 범위가 2개 이상일 경우를 의미합니다. 즉, 드라이빙 테이블의 한 개 값이 드리븐 테이블에서 여러 개의 데이터로 존재하는 일대다 관계입니다.

또한, = , <, > 등의 연산자를 사용해 인덱스로 생성된 열을 비교할 때도 출력됩니다.

REF_OR_NULL

IS NULL 구문에 대해 인덱스를 활용하도록 최적화된 방식입니다.

테이블에서 검색할 NULL 데이터 양이 적다면 ref_or_null 방식을 활용했을 때 효율적인 SQL 문이 될 것이나, 검색할 NULL 데이터 양이 많다면 SQL 튜닝의 대상이 될 것입니다.

RANGE

테이블 내의 연속된 데이터 범위를 조회하는 유형으로, =, <, <=, >, >=, BETWEEN, IN 연산자를 통해 범위 스캔을 수행하는 방식입니다.

주어진 데이터 범위 내에서 행 단위로 스캔하지만, 스캔할 범위가 넓으면 성능 저하의 요인이 될 수 있으므로 SQL 튜닝 검토 대상이 됩니다.

FULLTEXT

텍스트 검색을 빠르게 처리하기 위해 전문 인덱스 (FULLTEXT index)를 사용하여 데이터에 접근하는 방식입니다.

INDEX_MERGE

말 그대로 결합된 인덱스들이 동시에 사용되는 유형입니다. 즉, 특정 테이블에 생성된 두 개 이상의 인덱스가 병합되어 동시에 적용됩니다.

INDEX

인덱스 풀 스캔을 의미합니다. 즉, 물리적인 인덱스 블록을 처음부터 끝까지 훑는 방식을 말합니다.

이때 데이터를 스캔하는 대상이 인덱스라는 점이 다를 뿐, 이후 설명할 ALL 유형(테이블 풀 스캔 방식)과 유사합니다. 인덱스는 보통 테이블보다 크기가 작으므로 테이블 풀 스캔 방식보다는 빠를 가능성이 높습니다.

ALL

테이블을 처음부터 끝까지 읽는 테이블 풀 스캔 방식에 해당되는 유형입니다. ALL 유형은 활용할 수 있는 인덱스가 없거나, 인덱스를 활용하는 것이 오히려 비효율적이라고 옵티마이저가 판단했을 때 선택됩니다.

ALL 유형일 때는 인덱스를 새로 추가하거나 기존 인덱스를 변경하여 인덱스를 활용하는 방식으로 SQL 튜닝을 할 수 있으나, 전체 테이블 중 10~20% 이상 분량의 데이터를 조회할 때는 ALL 유형이 오히려 성능상 유리할 수 있습니다.

---

POSSIBLE_KEYS

옵티마이저가 SQL 문을 최적화하고자 사용할 수 있는 인덱스 목록을 출력합니다.

다만 실제 사용한 인덱스가 아닌, 사용할 수 있는 후보군의 기본 키와 인덱스 목록만 보여주므로 SQL 튜닝의 효용성은 없습니다.

---

KEY

옵티마이저가 SQL 문을 최적화하고자 사용한 기본 키(PK) 또는 인덱스명을 의미합니다.

어떤 인덱스로 데이터를 검색했는지 확인할 수 있으므로, 비효율적인 인덱스를 사용했거나 인덱스를 사용하지 않았다면 SQL 튜닝의 대상이 됩니다.

KEY_LEN

인덱스를 사용할 때 인덱스 전체를 사용하거나 일부 인덱스만 사용할 경우, 사용한 인덱스의 바이트 수를 의미합니다.

---

REF

ref는 테이블 조인을 수행할 때 어떤 조건으로 해당 테이블에 액세스되었는지를 알려주는 정보입니다.

ROWS

SQL 문을 수행하고자 접근하는 데이터의 모든 행 수를 나타내는 예측 항목입니다.

즉, 디스크에서 데이터 파일을 읽고 메모리에서 처리해야 할 행 수를 예상하는 값으로, 수시로 변동되는 MySQL의 통계 정보를 참고하여 산출하는 값이므로 수치가 정확하지는 않습니다.

---

FILTERED

SQL 문을 수행하고자 접근하는 데이터의 모든 행 수를 나타내는 예측 항목입니다.

즉, 디스크에서 데이터 파일을 읽고 메모리에서 처리해야 할 행 수를 예상하는 값이며, 수시로 변동되는 MySQL의 통계 정보를 참고하여 산출하므로 수치가 정확하지는 않습니다.

한편, SQL 문의 최종 결과 건수와 비교해 rows 수가 크게 차이 날 때는 불필요하게 MySQL 엔진까지 데이터를 많이 가져왔다는 뜻이므로 SQL 튜닝의 대상이 될 수 있습니다.

EXTRA

SQL 문을 어떻게 수행할 것인지에 관한 추가 정보를 보여주는 항목입니다. 이러한 부가적인 정보들은 세미콜론(;)으로 구분하여 여러 가지 정보를 나열할 수 있으며, 약 30여 가지 항목으로 정리할 수 있습니다.

Distinct

중복이 제거되어 유일한 값을 찾을 때 출력되는 정보입니다. 중복 제거가 포함되는 DISTINCT 키워드나 UNION 구문이 포함된 경우 출력됩니다.

Using where

실행 계획에서 자주 볼 수 있는 extra 정보입니다. 이는 WHERE 절의 필터 조건을 사용해 MySQL 엔진으로 가져온 데이터를 추출할 것이라는 의미로 이해할 수 있습니다.

Using temporary

데이터의 중간 결과를 저장하기 위해 임시 테이블을 생성하겠다는 의미입니다. 데이터를 가져와 저장한 뒤에 정렬 작업을 수행하거나 중복을 제거하는 작업 등을 수행합니다.

보통 DISTINCT, GROUP BY, ORDER BY 구문이 포함된 경우 Using temporary 정보가 출력됩니다.

임시 테이블을 메모리에 생성하거나, 메모리 영역을 초과하여 디스크에 임시 테이블을 생성하면 Using temporary는 성능 저하의 원인이 될 수 있습니다.

따라서 이 항목의 정보가 출력되는 쿼리는 SQL 튜닝의 대상이 될 수 있습니다.

Using index

물리적인 데이터 파일을 읽지 않고 인덱스만을 읽어서 SQL 문의 요청 사항을 처리할 수 있는 경우를 의미합니다.

일명 커버링 인덱스(covering index) 방식이라고 부르며, 인덱스로 구성된 열만 SQL 문에서 사용할 경우 이 방식을 활용합니다.

물리적으로도 테이블보다 인덱스가 작고 정렬되어 있으므로 적은 양의 데이터에 접근할 때 성능 측면에서 효율적입니다.

Using filesort

정렬이 필요한 데이터를 메모리에 올리고 정렬 작업을 수행한다는 의미입니다.

보통 이미 정렬된 인덱스를 사용하면 추가적인 정렬 작업이 필요 없지만, 인덱스를 사용하지 못할 때는 정렬을 위해 메모리 영역에 데이터를 올리게 됩니다.

추가적인 정렬 작업이므로 인덱스를 활용하도록 SQL 튜닝 검토 대상이 될 수 있습니다.

Using join buffer

조인을 수행하기 위해 중간 데이터 결과를 저장하는 조인 버퍼를 사용한다는 의미입니다.

즉, 드라이빙 테이블의 데이터에 먼저 접근한 결과를 조인 버퍼에 담고 난 뒤, 조인 버퍼와 드리븐 테이블 간에서 일치하는 조인 키 값을 찾는 과정을 수행합니다.

Using index condition

MySQL 엔진에서 인덱스로 생성된 열의 필터 조건에 따라 요청된 데이터만 필터링하는 Using where 방식과 달리, 필터 조건을 스토리지 엔진으로 전달하여 필터링 작업에 대한 MySQL 엔진의 부하를 줄이는 방식입니다.

이는 스토리지 엔진의 데이터 결과를 MySQL 엔진으로 전송하는 데이터 양을 줄여 성능 효율을 높일 수 있는 옵티마이저의 최적화 방식입니다.

Not exists

Not exists는 하나의 일치하는 행을 찾으면 추가로 행을 더 검색하지 않아도 될 때 출력되는 유형입니다.

해당 메커니즘은 왼쪽 외부 조인 또는 오른쪽 외부 조인에서 테이블에 존재하지 않는 데이터를 명시적으로 검색할 때 발생합니다.

---

Tuning Decision

실행 계획을 수행하여 출력된 정보를 살펴보았을 때, SQL 튜닝 대상인 실행 계획과 튜닝이 필요하지 않은 실행 계획을 명확히 구분하기란 어려운 작업입니다.

이때 검토 대상인 실행 계획 열은 select_type, type, extra입니다.

실행 계획 수행

EXPLAIN FORMAT = TRADITIONAL

기본적인 실행 계획은 EXPLAIN 키워드를 사용하여 입력하며, 기본 포맷은 TRADITIONAL입니다. 따라서 명시하지 않아도 다음과 같이 기본적인 실행 계획 정보가 출력됩니다.

EXPLAIN FORMAT = TREE

트리 형태로 추가된 실행 계획 항목을 확인할 수 있습니다. 이 형식은 쿼리의 실행 계획을 보다 시각적으로 이해하기 쉽게 보여줍니다.

EXPLAIN FORMAT = JSON

JSON 형태로 추가된 실행 계획 항목을 확인할 수 있습니다. 이 형식은 쿼리의 실행 계획을 구조화된 JSON 데이터로 제공하여, 프로그램에서 쉽게 파싱하고 분석할 수 있도록 합니다.

EXPLAIN PARTITIONS

파티션으로 설정된 테이블에 대해 접근 대상인 파티션 정보를 출력합니다. 이 옵션을 사용하면 쿼리가 특정 파티션에만 접근하는지, 아니면 모든 파티션을 스캔하는지를 확인할 수 있습니다.

3. 실행 계획 최적화

3.1. 인덱스 사용 인덱스는 쿼리 성능에 큰 영향을 미칩니다. 인덱스를 적절하게 사용하면 데이터 검색 속도를 크게 향상시킬 수 있습니다.

CREATE INDEX idx_department_id ON employees(department_id);

3.2. 조인 최적화 조인 순서와 전략을 최적화하면 쿼리 성능을 개선할 수 있습니다. MySQL은 Nested Loop 조인을 기본으로 사용하지만, 필요한 경우 조인 순서를 변경하여 성능을 향상시킬 수 있습니다.

SELECT e.name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

3.3. 쿼리 재작성 쿼리 구조를 개선하여 실행 계획을 최적화할 수 있습니다. 서브쿼리를 조인으로 변경하거나, 불필요한 컬럼을 제거하여 쿼리 성능을 향상시킬 수 있습니다.

-- 서브쿼리 예시
SELECT name FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE location = 'New York');

-- 조인으로 변경
SELECT e.name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE d.location = 'New York';

4. 고급 주제

4.1. 실행 계획 캐시 MySQL은 실행 계획을 캐시하여 반복적인 쿼리의 성능을 개선합니다. 이를 통해 동일한 쿼리에 대해 실행 계획을 재사용할 수 있습니다.

4.2. 성능 모니터링 도구 MySQL Workbench, Percona Toolkit, pt-query-digest 등의 도구를 사용하여 쿼리 성능을 모니터링하고 최적화할 수 있습니다.

4.3. MySQL 최신 버전 기능 MySQL 최신 버전에서는 실행 계획과 관련된 여러 개선 사항이 도입되었습니다. 이를 활용하여 더 나은 성능을 달성할 수 있습니다.