10.3 실행 계획 - 실행 계획 분석 (1)

EXPLAIN 명령을 실행하면 쿼리 문장의 특성에 따라 표 형태로 1줄 이상의 결과가 표시된다.

표의 각 라인(레코드)은 쿼리 문장에서 사용된 테이블(임시 테이블 포함)의 개수만큼 출력된다.

실행 순서는 일반적으로 위에서 아래로 순서대로 표시되며 출력된 실행 계획에서 위쪽에 출력된 결과일수록 쿼리의 바깥 부분이거나 먼저 접근한 테이블이고, 아래쪽에 출력된 결과일수록 쿼리의 안쪽 부분 또는 나중에 접근한 테이블에 해당한다.

id 컬럼

1
2
3
SELECT ...
FROM (SELECT ... FROM tb_test1) tb1, tb_test2 tb2
WHERE tb1.id = tb2.id;

위 쿼리의 SELECT는 2개의 SELECT로 분리해서 생각해볼 수 있고, 이렇게 분리한 단위로 구분한 것을 단위 쿼리 나눌 수 있다.

1
2
SELECT ... FROM tb_test1;
SELECT ... FROM tb1, tb_test2 tb2 WHERE tb1.id = tb2.id;

실행 계획에서 가장 왼쪽에 표시되는 id 컬럼은 단위 SELECT 쿼리별로 부여되는 식별자 값이다.
하나의 테이블을 조인하면 조인되는 테이블의 개수만큼 실행 계획에 레코드가 출력되지만 같은 id 값이 부여된다.

1
2
EXPLAIN
SELECT ((SELECT COUNT(*) FROM employees) + (SELECT COUNT(*) FROM departments)) AS total_count;

위 쿼리의 실행 계획에서는 쿼리 문장이 3개 단위의 SELECT 쿼리로 구성되어있으므로 실행 계획에 각 레코드가 각기 다른 id값을 가지게 된다.

실행 계획의 id 컬럼이 테이블의 접근 순서를 의미하는 것은 아니다.

select_type 컬럼

각 단위 SELECT 쿼리가 어떤 타입의ㅏ 쿼리인지 표시되는 컬럼이다.

SIMPLE

UNION, 서브쿼리를 사용하지 않는 단순한 SELECT 쿼리인 경우 해당 쿼리 문장의 select_type은 SIMPLE로 표시된다. (쿼리에 조인이 포함된 경우 까지)

쿼리 문장이 아무리 복잡하더라도 실행 계획에서 select_type이 PRIMARY인 단위 SELECT 쿼리는 하나만 존재한다.
일반적으로 쿼리의 제일 바깥 SELECT 쿼리가 SIMPLE로 표시된다.

PRIMARY

UNION, 서브쿼리를 가지는 SELECT 쿼리의 실행 계획에서 가장 바깥쪽에 있는 단위 쿼리는 PRIMARY로 표시된다.

SIMPLE과 마찬가지로 PRIMARY인 단위 쿼리는 하나만 존재한다.
쿼리의 제일 바깥쪽에 있는 단위 쿼리가 PRIMARY로 표시된다.

UNION

UNION으로 결합하는 단위 쿼리 가운데 첫 번째를 제외한 단위 쿼리의 select_type은 UNION으로 표시된다.

UNION의 첫번째 단위 쿼리는 UNION되는 쿼리 결과들을 모아서 자장하는 임시테이블(DRIVED)이 select_type으로 표시된다.

DEPENDENT UNION

UNION, UNION ALL로 집합을 결함하는 쿼리에서 표시된다.

DEPENDENT는 UNION, UNION ALL로 결함된 단위 퀄이가 외부 쿼리에 의해 영향을 받는 것을 의미한다.

UNION RESULT

UNION 결과를 담아두는 테이블을 의미한다.

MySQL 8.0 이전 버전에서는 UNION, UNION ALL 쿼리는 모두 UNION의 결과를 임시 테이블로 생성했는데, UNION ALL은 임시 테이블을 사용하지 않도록 개선되어 임시 테이블 버퍼링이 필요한 UNION, UNION DISTINCT 쿼리 사용시 노출된다.

실행 계획상에서 해당 임시 테이블을 가리키는 라인의 select_type이 UNION RESULT이다.
UNION RESULT는 실제 쿼리에서 단위 쿼리가 아니기 때문에 id 값이 부여되지 않는다.

SUBQUERY

일반적으로 서브쿼리라고 하면 여러 가지를 통틀에서 말하지만, select_type의 SUBQUERY는 FROM 절 이외에서 사용되는 서브쿼리를 의미한다.

MySQL 서버의 실행 계획에서 FROM 절에 사용된 서브쿼리는 DERIVED로 표시된다.

서브쿼리
사용된 위치
중첩된 서브쿼리(Nested Query): SELECT 되는 컬럼에 사용된 경우
서브쿼리(Subquery): WHERE 절에서 사용된 경우
파생 테이블(Derived Table): FROM 절에 사용된 경우
다른 RDBMS에서는 일반적으로 인라인 뷰(Inline View), 서브 셀렉트(Sub Select)라고 한다.
반환하는 값의 특성
스칼라 서브쿼리(Scalar Subquery): 하나의 값만 (컬럼이 단 하나인 레코드 1건만) 반환.
로우 서브쿼리(Row Subquery): 컬럼의 개수와 관계없이 하나의 레코드만 반환하는 쿼리

DEPENDENT SUBQUERY

서브쿼리가 바깥쪽 SELECT 쿼리에서 정의된 컬럼을 사용하는 경우 DEPENDENT SUBQUERY라고 표시된다.

안쪽의 서브쿼리 결과가 바깥쪽 SELECT 쿼리의 컬럼에 의존적이기 때문에 DEPENDENT라는 키워드가 붙는다.
DEPENDENT UNION, DEPENDENT SUBQUERY 또한 외부 쿼리가 먼저 수행된 후 내부 쿼리가 실행되어야 하므로 일반 서브쿼리보다는 처리 속도가 느린 경우가 많다.

DERIVED

DERIVED는 단위 쿼리의 실행 결과로 메모리나 디스크에 임시 테이블을 생성하는 것을 의미한다.

select_type이 DERIVED인 경우에 생성되는 임시 테이블을 파생 테이블이라고 한다.
MySQL 5.5 버전까지는 파생 테이블에 인덱스가 전혀 없으므로 다른 테이블과 조인할 때 성능상 불리할 때가 많다.

FROM 절의 서브 쿼리를 제거하고 조인으로 처리할 수 있는 형태일 경우 다른 RDBMS에서는 쿼리를 재작성하는 형태의 최적화 기능을 제공한다. MySQL 8.0 이전 버전에서는 여전히 파생 테이블을 생성하기 때문에 실행 계획을 조인 형태로 변경하는 것이 좋다.

또한, 옵티마이저가 처리할 수 있는 것은 한계가 있으므로 최적화된 쿼리를 작성하는 것이 중요하다.

DEPENDENT DERIVED

MySQL 8.0 버전부터 래터럴 조인(LATERAL JOIN) 기능 추가로 FROM 절의 서브쿼리에서도 외부 컬럼을 참조할 수 있게 되었다. 이로 인해 외부 영향을 받는 파생 테이블이 생성될 경우 표시된다.

UNCACHEABLE SUBQUERY

하나의 쿼리 문장에 서브쿼리가 하나만 있더라도 실제 해당 서브쿼리가 한 번만 실행되는 것은 아니기 때문에 조건이 똑같은 서브쿼리가 실행될 경우 다시 실행하지 않고 이전 실행 결과를 그대로 사용할 수 있게 서브쿼리의 결과를 내부적인 캐시 공간에 담아둔다.

특히 DEPENDENT SUBQUERY의 경우 서브쿼리의 결과가 외부 쿼리의 값 단위로 캐시가 만들어진다. 하지만 서브쿼리에 포함된 요소에 의해 캐시 자체가 불가능 할 수 있는데 이러한 경우 UNCACHEABLE SUBQUERY로 표시된다.

캐시를 사용하지 못하는 경우

사용자 변수가 서브쿼리에 사용
NOT-DETERMNISTIC 속성의 스토어드 루틴이 서브쿼리 내에 사용된 경우
UUID(), RAND() 같은 결괏값이 호출할 때마다 달라지는 함수가 서브쿼리에 사용된 경우

UNCACHEABLE UNION

UNION과 UNCACHEABLE 속성이 혼합된 select_type

MATERIALIZED

FROM, IN (subquery) 형태의 쿼리에 사용된 서브쿼리가 내용을 임시 테이블로 구체화(Meterialization)한 후, 임시테이블과 대상 테이블이 조인되는 형태로 최적화가 적용될 경우 표시되는 select_type이다.

MySQL 5.6 버전부터 도입되었다.
DERIVED와 비슷하게 쿼리의 내용을 임시 테이블로 생성한다.

table 컬럼

MySQL 서버의 실행 계획은 단위 SELECT 쿼리 기준이 아닌 테이블 기준으로 표시되는데, 테이블 이름에 별칭이 부여된 경우 별칭이 표시된다.

<>로 둘러싸인 이름이 면시된 경우 임시 테이블을 의미한다.
- 안에 항상 표시되는 숫자는 SELECT 쿼리의 id 값을 지칭하게 된다.

partitions 컬럼

MySQL 5.7 버전까지는 옵티마이저가 사용하는 파티션들의 목록은 EXPLAIN PARTITION 명령을 통해 확인 가능했지만, 8.0 버전부터 EXPLAIN 명령으로 파티션 관련 실행 계획까지 모두 확인할 수 있다.

파티션을 사용할 경우 쿼리에서 조회하려는 데이터가 특정 파티션에 위치하게 되므로, 실행 계획 수립시 조건에 해당하지 않는 파티션에 대해 분석을 실행하지 않는다. (파티션 프루닝: Partition pruning)

따라서 쿼리의 실행 계획을 통해 어느 파티션을 읽는지 확인할 수 있어야 쿼리 튜닝이 가능하며, 파티션을 참조하는 쿼리의 경우 옵티마이저가 쿼리 처리를 위해 필요한 파티션들의 목록만 모아 실행 계획의 partitions 컬럼에 표시해준다.

type 컬럼

MySQL 서버가 각 테이블의 레코드를 어떤 방식으로 읽었는지를 나타낸다. 일반적으로 쿼리를 튜닝할 때 인덱스를 효율적으로 사용하는지 확인하는 것이 중요하므로 실행 계획에서 type 컬럼은 반드시 체크해야한다.

ALL을 제외한 나머지는모두 인덱스를 사용하는 접근 방법이다.
index_merge를 제외한 나머지 접근 방법은 하나의 인덱스만 사용한다.

system

레코드가 1건만 존재하는 테이블 또는 한 건도 존재하지 않는 테이블을 참조하는 형태의 접근 방법이다.

InnoDB 스토리지 엔진을 사용하는 테이블에서는 나타나지 않고, MyISAM, MEMORY 테이블에서만 사용되는 접근 방식이다.
테이블에 레코드가 1건 이하인 경우에만 사용할 수 있는 접근 방법이므로 실제 애플리케이션에서 사용되는 쿼리에서는 거의 보이지 않는다.

const

테이블의 레코드 건수와 관계없이 쿼리가 프라이머리 키나 유니크 키 컬럼을 이용하는 WHERE 조건절을 가지고 있으며, 반드시 1건을 반환하는 쿼리의 처리방식이다.

다중 컬럼으로 구성된 프라이머리 키나 유니크 키 중에서 인덱스의 일부 컬럼만 조건으로 사용할 때는 const 접근 방법을 사용할 수 없다.
- 실제로 레코드가 1건만 저장돼 있더라도 데이터를 읽어보지 않고서는 레코드가 1건이라는 것을 확신할 수 없기 때문
프라이머리 키의 일부만 조건으로 사용할 때는 ref로 표시된다.
프라이머리 키나 유니크 인덱스의 모든 컬럼을 동등 조건으로 명시하면 const 접근 방법을 사용한다.

MySQL의 옵티마이저가 쿼리를 최적화하는 단계에서 쿼리를 먼저 실행화여 통째로 상수화 하기 때문에 컬럼의 값이 const로 표시된다.

eq_ref

여러 테이블이 조인되는 쿼리의 실행 계획에서만 표시된다. 조인에서 처음 읽은 테이블의 컬럼값을, 그다음 읽어야 할 테이블의 프라이머리 키나 유니크 키 컬럼의 검색 조건에 사용할 때를 가르켜 eq_ref라고 한다.

조인에서 두 번째 이후에 읽는 테이블에서 반드시 1건만 존재한다는 보장이 있어야 사용할 수 있는 접근 방법이다.

두 번째 이후에 읽는 테이블이 eq_ref가 표시된다.
두 번째 이후에 읽히는 테입르을 유니크 키로 검색할 때 해당 유니크 인덱스는 NOT NULL 이어야 한다.
다중 컬럼으로 만들어진 프라이머리 키나 유니크 인덱스라면 인덱스의 모든 컬럼이 비교 조건에 사용되어야 한다.

ref

인덱스의 종류와 관계없이 동등 조건으로 검색할 때 사용되는 방법이다.

eq_ref와는 달리 조인의 순서와 관계없이 사용되며, 프라이머리 키나 유니크 키 등의 제약 조건도 없다.

ref 타입은 반환되는 레코드가 반드시 1건이라는 보장이 없으므로 const, eq_ref보다는 빠르지 않다.
하지만 동등 조건으로만 비교되므로 매우 빠른 레코드 조회 방법의 하나다.

동등 비교 연산자
=, <=>를 의미한다.

fulltext

전문 검색 인덱스를 사용해 레코드를 읽는 접근 방법을 의미한다.

전문 검색 인덱스는 통계 정보가 관리되지 않아 성능이 일관적이지 않고, 전문 검색 인덱스를 사용하려면 전혀 다른 SQL 문법을 사용해야 한다.

전문 검색 조건은 우선순위가 매우 높아, 쿼리에서 전문 인덱스를 사용하는 조건과 그 외 인덱스를 사용하는 조건을 함께 사용한다면 그외 조건이 const, eq_ref, ref가 아니라면 일반적으로 전문 검색 인덱스를 사용하는 조건으로 선택하여 처리된다.

일반적으로 전문 검색 인덱스를 이용하는 fulltext보다 일반 인덱스를 이용하는 range 접근 방법이 더 빨리 처리되므로, 전문 검색 쿼리를 사용할 때는 조건별로 성능을 확인해 보는 것이 좋다.

ref_or_null

ref 접근 방법에 NULL 비교가 추가된 방식이다. 실제 업무에서 많이 활용되지 않지만, 만약 사용된다면 나쁘지 않는 접근 방법이다.

unique_subquery

WHERE 조건절에서 사용될 수 있는 IN (subquery) 형태의 쿼리를 위한 접근 방법이다.

서브쿼리에서 중복되지 않는 유니크한 값만 반활할 때 사용된다.
MySQL 8.0 버전부터는 세미 조인을 최적화하는 형태로 처리되므로 실제로는 더 최적화된 다른 실행 계획이 보일 확률이 높다.

index_subquery

IN 연산자 특성으로 인해 괄호 안에 있는 값의 목록에서 중복된 값이 먼저 제거되어야 하는데, IN (subquery)에서 서브쿼리가 중복된 값을 반환할 수 있을때 이를 인덱스를 이용해서 중복을 제거할 수 있는 경우 index_subquery 접근 방법이 사용된다.

range

인덱스 레인지 스캔 형태의 접근 방법이다. range는 인덱스를 하나의 값이 아니라 범위로 검색하는 경우를 의미하며, 주로 <, >, IS NULL, BETWEEN, IN, LIKE 등의 연산자를 이용해 인덱스 검색할 때 사용된다.

일반적으로 애플리케이션의 쿼리가 가장 많이 사용하는 접근 방법이다.
얼마나 많은 레코드를 필요로 하느냐에 따라 차이는 있지만 상당히 빠른 방법중 하나이다.

index_merge

2개 이상의 인덱스르 이용해 각각의 검색 결과를 만들어낸 후, 그 결과를 병합해서 처리하는 방식이다.

여러 인덱스를 읽어야 하므로 일반적으로 range 접근 방법보다 효율성이 떨어진다.
전문 검색 인덱스를 사용하는 쿼리에는 적용되지 않는다.
처리된 결과는 항상 2개 이상의 집합이 되기 때문에 그 두 집합의 교집합이나 합집합, 또는 중복 제거와 같은 부가적인 작업이 더 필요하다.

index

인덱스를 처음부터 끝까지 읽는 인덱스 풀스캔을 의미한다.

테이블을 처음부터 끝까지 읽는 풀 테이블 스캔 방식과 비교했을때 비교하는 레코드 건수는 같지만, 일반적으로 데이터 파일 전체보다 크기가 작으므로 풀 테이블 스캔보다 빠르게 처리된다.
쿼리의 내용에 따라 정렬된 인덱스의 장점을 이용할 수 있다.

index 접근 방법은 다음 주건 가운데 (1 + 2) 또는 (1 + 3) 조건을 충족하는 쿼리에서 사용되는 읽기 방식이다.

range, const, ref 같은 접근 방법으로 인덱스를 사용하지 못하는 경우
인덱스에 포함된 컬럼만으로 처리할 수 있는 쿼리인 경우(데이터 파일을 읽지 않아도 되는 경우)
인덱스를 이용해 정렬이나 그루핑 작업이 가능한 경우(별도의 정렬 작업을 피할 수 있는 경우)

ALL

풀 테이블 스캔을 의미하는 접근 방법이다. 테이블을 처음부터 끝까지 읽어 불필요한 레코드를 제거하고 반환하는 가장 비효율적인 방법이다.

데이터 웨어하우스나 배치 프로그램처럼 대용량의 레코드를 처리하는 쿼리에서 잘못 튜닝된 쿼리(억지로 인덱스를 사용하게된 튜닝쿼리)보다 더 나은 접근방법이다.
일밙거으로 작업 범위를 제한하는 조건이 아니므로 빠른 응답을 사용자에게 보내야하는 웹 서비스에는 적합하지 않다.
테이블이 매우 작지 않다면 실제로 테이블에 데이터를 어느 ㅈ어도 저장한 상태에서 쿼리의 성능을 확인해보고 적용하는 것이 좋다.

possible_keys 컬럼

옵티마이저가 최적의 실행 계획을 만들기 위해 후보로 선정했던 접근 방법에서 사용되는 인덱스의 목록이다.

실제 실행 계획에서는 해당 테이블의 모든 인덱스가 목록에 포함되는 경우가 많아 쿼리 튜닝에 크게 도움되지는 않는다.
특별한 경우를 제외하고 무시해되 된다.
컬럼에 인ㄷ게스 이름이 나열됐다고 해서 그 인덱스를 사용하지는 않는다.

key 컬럼

key 컬럼에 표시되는 인덱스는 최종 선택된 실행 계획에서 사용하는 인덱스를 의미한다.

쿼리 튜닝시 의도했던 인덱스가 표시되는지 확인하는 것이 중요하다.
실행 계획의 type 컬럼이 index_merge가 아는 경우에는 반드시 테이블 하나당 하나의 인덱스만 이용할 수 있다.
인덱스를 사용하지 못하면 NULL로 표시된다.

key_len 컬럼

실제 업무에서 사용하는 테이블은 단일 컬럼으로 만들어진 인덱스보다 다중 컬럼으로 만들어진 인덱스가 더 많은데, key_len 컬럼은 쿼리를 처리하기 위해 다중 컬럼으로 구성된 인덱스에서 몇 개의 컬럼까지 사용했는지 알려준다.(인덱스의 각 레코드에서 몇 바이트까지 사용했는지)

다중 컬럼 인덱스뿐 아니라 단일 컬럼으로 만들어진 인덱스에서도 같은 지표를 제공한다.

ref 컬럼

접근 방법이 ref라면 참조 조건(Equal 비교 조건)으로 어떤 값이 제공되었는지 보여준다.

상숫값을 지정했다면 const 표시
다른 테이블의 컬럼 값이면 테이블명과 컬럼명 표시
콜레이션 변환이나 값 차체의 연산을 거쳐 참조했을 경우 func 표시
MySQL 서버가 내부적으로 값을 변환해야 하는 경우 func 표시
- 문자집합이 일치하지 않는 두 문자열 컬럼을 조인
- 숫자 타입의 컬럼과 문자열 타입의 컬럼 조인 등

rows 컬럼

rows 컬럼은 실행 계획의 효율성 판단을 위해 예측한 읽어야 할 총 레코드 건수를 의미한다.

MySQL 옵티마이저는 각 조건에 대해 가능한 처리 방식을 나열하고, 각 처리방식의 비용을 비교해 최종적으로 하나의 실행 계획을 수립할 때 각 처리 방식이 얼마나 많은 레코드를 읽고 비교해야 하는지 예측해 비용을 산정한다.
대상 테이블에 얼마나 많은 레코드가 포함되어 있는지
각 인덱스 값의 분포도가 어떤지

rows 값은 각 스토리지 엔질별로 가지고 있는 통계 정보를 참조해 산출해낸 예상값이므로 전확하지는 않다.
반환하는 레코드의 예측치가 아니라 쿼리를 처리하기 위해 얼마나 많은 레코드를 읽고 체크해야 하는지를 의미한다.

filtered 컬럼

filtered 컬럼은 읽어야 할 총 레코드 건수 rows에서 필터링 되고 남는 레코드의 비율을 의미한다.

1
2
3
4
5
6
EXPLAIN
SELECT *
FROM employees e
WHERE e.first_name='Matt'
    AND e.hire_date BETWEEN '1990-01-01' AND '1991-01-01'
;

id	select_type	type	key	rows	filtered
1	SIMPLE	ref	ix_firstname	233	16.03

위 실행계획에서 ix_firstname 인덱스를 활용하여 읽어야 할 범위를 제한하였고, 따라서 필수적으로 읽어야 하는 레코드 건수는 rows 값인 233이다.

읽어야 하는 레코드 233건 중 인덱스를 활용할 수 없는 조건인 hire_date 조건을 통해 다시 값을 필터링하게 되는데, filtered 값은 읽어와야할 레코드 중 조건으로 인해 필터링 되고 남은 레코드의 비율을 의미한다.

따라서 쿼리의 결과 예상 건수는 233 * 0.1603 으로 약 37건이다.

대부분 쿼리에서 WHERE 절에서 사용되는 조건이 모두 인덱스를 사용할 수 있는 것은 아니며, 특히 조인이 사용되는 경우에는 WHERE 절에서 인덱스를 사용할 수 있는 조건도 중요하지만 인덱스를 사용하지 못하는 조건에 일치하는 레코드 건수를 파악하는 것도 매우 중요하다.