MySQL/MariaDB 실행계획 수행

기본 실행 계획 수행

키워드 : EXPLAIN, DESCRIBE, DESC
SQL 문 앞에 원하는 키워드를 작성하여 실행하면 옵티마이저가 만든 실행계획이 출력됨.

EXPLAIN SQL문;
DESCRIBE SQL문;
DESC SQL문;

실행계획이 출력되면, id, select_type, table, type, key등 정보가 출력됨.

 

id

실행순서를 표시하는 숫자, 조인할때는 동일한 ID가 표시되며 숫자가 작을수록 먼저 수행된 것

 

select_type

SELECT 문의 유형을 출력, 단순히 FROM 절에 위치한 것인지, 서브쿼리인지, UNION 절로 묶인 SELECT 문인지 등의 정보를 제공

 

select_type 유형 (* = 튜닝대상)

PRIMARY : 서브쿼리가 포함된 SQL문이 있을때 첫번째 SELECT 문에 해당하는 구문에 표시되는 유형 (서브쿼리가 감싸는 외부쿼리거나 UNION이 포함된 SQL문의 첫번째 SELECT)

SUBQUERY : 독립적으로 수행되는 서브쿼리

DERIVED : FROM절에 작성된 서브쿼리

UNION : union / union all 구문으로 합쳐진 SELECT문에서 첫번째를 제외한 SELECT 구문

UNION RESULT (*) : union all이 아닌 union 구문으로 SELECT 절을 결합했을때 출력. union은 출력결과에 중복 없는 값이 나와야 하므로 내부적으로 정렬하여 중복 체크하는 단계를 거침. (중복되지 않는 데이터일 경우 union all로 변경 필요함)

DEPENDENT SUBQUERY(*) : union / union all 사용하는 서브쿼리가 메인 테이블에 영향을 받는 경우의 첫번째 쿼리, 독립적으로 실행되지 않고 메인쿼리의 값을 하나씩 공급받는 구조로, 튜닝대상.

DEPENDENT UNION (*) : union / union all 사용하는 서브쿼리가 메인 테이블에 영향을 받는 경우의 첫번째를 제외한 쿼리, DEPENDENT SUBQUERY와 마찬가지로 튜닝대상.

UNCACHEABLE SUBQUERY (*) : 메모리에 상주하여 재활용되어야 할 서브쿼리가 재사용되지 못할때 출력되는 유형으로 서브쿼리 내에 함수사용 또는 매번 조회시 변경되는 함수 (RAND(), UUID()). 튜닝대상

MATERIALIZED : IN 절 구문에 연결된 서브쿼리가 임시 테이블을 생성한 뒤, 조인/가공작업을 수행할때 표시

 

table

테이블이 표시되는 항목

 

partitions

데이터가 저장된 논리적인 영역을 표시, 너무 많은 영역의 파티션에 접근하는 것으로 출력된다면 파티션 정의를 튜닝해야 함

 

type

테이블의 데이터를 어떻게 찾을지에 관한 정보를 제공하는 항목. 테이블전체를 확인 또는 인덱스 사용을 확인

 

type 유형

system : 데이터가 없거나, 1개만 존재하는 경우

const : 데이터가 1건일때 출력되는 유형

eq_ref : 조인 수행 시 고유 인덱스 또는 기본키로 1건의 데이터를 조회하는 방식

ref : eq_ref와 유사하며, 데이터 접근 범위가 2개 이상일 경우 표시

ref_or_null : ref와 유사, IS NULL 구문에 대해 인덱스를 활용하도록 최적화된 방식 (NULL에 대해서도 인덱스를 활용하여 검색할 수 있음)

range : 연속된 데이터 범위를 조회하는 유형 (=,<>,>,>=,<=, is null...) between, in 연상을 통해 범위 스캔을 수행하는 방식

스캔할 범위가 많으면 성능 저하가 되므로, 튜닝 검토 대상이 됨

fulltext : 텍스트 검색을 빠르게 하기위해 전문 인덱스를 사용하여 데이터에 접근하는 방식

index_merge : 결합된 인덱스들이 동시에 사용되는 유형. (특정 테이블에 생성된 두개이상의 인덱스가 병합되어 동시 적용), 이때 전문인덱스는 제외됨.

index : 인덱스 풀 스캔을 의미, 인덱스는 보통 테이블보다 크기가 작으므로 테이블 풀 스캔방식보다 빠르다.

all : 테이블 풀 스캔을 의미하여, 활용할 수 있는 인덱스가 없거나 인덱스를 활용하는게 오히려 비효율적이라고 옵티마이저가 판단할 경우 사용, 기존 인덱스를 변경하거나 인덱스 추가가 필요하나, 전체 테이블의 10~20% 이상 데이터를 조회할 때는 all 유형이 더 좋을 경우도 있음

 

possible_keys

옵티마이저가 SQL문 최적화를 위해 사용할 수 있는 인덱스 목록을 출력, 실제 사용한 인덱스가 아니며, 사용가능한 후보군 목록임

 

key

옵티마이저가 사용한 PK 또는 인덱스 명이 표시됨, 어떤 인덱스를 사용했는지 확인이 가능하며, 값이 없을 경우 튜닝대상이 됨

 

key_len

사용한 인덱스의 바이트 수가 표시됨. UTF-8 기준 INT 데이터 유형은 단위당 4바이트, VARCHAR 는 단위당 3바이트

ex) varchar(50) = (50+1)*3 = 155바이트

 

ref

조인을 수행할때 어떤 조건으로 해당 테이블에 액세스 되었는지 알려줌

 

rows

SQL문 수행시 접근하는 데이터의 모든 행수를 예측하여 표시됨 ( 디스크에서 데이터 파일을 읽고 메모리에 처리해야 할 행 수를 예상하는 값)

 

filtered

SQL문을 통해 가져온 데이터 중 필터 조건(where)에 따라 어느정도 비율로 데이터를 제거 했는지 표시

100건의 데이터를 가져와 where 절에 limit 10이라고 되어 있을 경우 10건이 필터 되었으므로 10이라고 표시됨(단위는 %)

 

extra

SQL문을 어떻게 수행할 것인지에 대한 추가정보가 표시됨

 

extra 유형

distinct : distinct나 union 구문이 포함된 경우 출력됨, 중복이 제거되어 유일한 값을 찾을때 출력

using where : where절의 필터 조건이 사용되었다는 의미

using temporary : 임시테이블을 생성하겠다는 의미, 보통 distinct, group by , order by 구분이 포함된 경우

using index : 물리적인 데이터를 읽지 않고, 인덱스만을 읽어서 SQL문을 처리

using filesort : 정렬이 필요한 데이터를 메모리에 올리고 정렬작업이 수행된다는 의미로 인덱스를 사용하지 못한 경우로 튜닝이 필요함

using join buffer : 조인 수행을 위해 중간 테이블 결과를 저장하는 조인 버퍼를 사용한다는 의미

using union / using intersect / using sort_union : 인덱스가 병합되어 실행되는 SQL문의 어떤 인덱스를 어떻게 병합했는지 정보. using union (인덱스들의 합집합처럼 모두 결합하여 데이터에 접근, OR문), using intersect (인덱스들의 교집합처럼 추출하는 방식, AND 문), using sort_union (where절의 or 구문이 동등조건이 아닐때 표시)

using index condition : 필터조건을 스토리지 엔진으로 전달하여 필터링 작업에 대한 엔진의 부하를 줄이는 방식

using index condition(BKA) : 데이터를 검색하기 위해 배치키액세스를 사용하는 방식

using index for group-by : SQL문의 group by, order by 구분이 포함될때 인덱스로 정렬작업을 수행하여 최적화 하는데 이때 해당 인덱스로 정렬작업을 수행할 때 출력

not exists : 하나의 일치하는 행으 찾으면 추가로 행을 더 검색하지 않아도 될 때 출력