mysql 실행계획 1

쿼리 앞에 EXPLAIN 키워드를 붙이면 실행 계획을 확인 할 수 있다.

EXPLAIN SELECT * FROM test;

실행 계획은 일반 조회 쿼리 처럼 테이블 형태로 출력되는데 간단하게 그 내용을 정리해본다.

1. id

SELECT 쿼리에 부여되는 아이디. 한 SELECT 쿼리 내에 복수개의 SELECT 문이 서브쿼리(Sub Query) 형태로 들어갈 수 있으므로 그런 경우에 id 값이 증가되어 여러행의 실행계획이 나타나게 된다. SELECT 문이 1개만 존재하는 JOIN(조인) 형태의 쿼리는 복수의 id가 부여되지 않는다.

2. select_type

-SIMPLE

명칭 그대로 심플한 쿼리이다. 서브쿼리같은 복잡함이 없다.

-PRIMARY

서브쿼리가 존재할때 가장 바깥 쿼리를 뜻한다. 복수 행의 실행계획에서 1행만 존재한다.

-UNION

UNION, UNION ALL 절에 사용된 쿼리를 의미한다.

-UNION RESULT

UNION, UNION ALL 절로 생성된 임시 테이블을 의미한다.

-DEPENDENT UNION

UNION, UNION ALL 절이 외부 결과에 의존할때 표현된다. UNION 쿼리가 내부에서 사용되었을때 표현된다.

-SUBQUERY

FROM 절 외에서 사용되는 서브쿼리를 의미한다.

-DERIVED

FROM 절에서 사용되는 서브쿼리를 의미한다. FROM 절에 사용된 서브쿼리(인라인 뷰라고 표현한다.)의 경우 임시 테이블을 생성해야하므로 파생되었다는 의미의 DERIVED가 표현된다.

-DEPENDENT SUBQUERY

서브 쿼리가 외부 쿼리의 결과에 의존할때 표현된다.

-UNCACHEABLE SUBQUERY

서브쿼리는 종류에 따라 바깥쿼리 행만큼 수행되어야 하는 경우도있다. 실제로 그렇게 작동한다면 성능에 큰 영향을 끼치게되므로 경우에 따라 쿼리를 캐싱해놓고 캐싱된 데이터를 갖다쓰게끔 최적화가 되어있는데 그런 캐싱이 작동할 수 없는 경우에 표현된다. 즉 캐싱되지못하는 이유가 수정 가능하다면 캐싱되게끔 하는것이 성능에 좋다.

-MATERIALIZED

MySQL 5.6 버전에 추가된 셀렉트 타입이다. 그 이전의 버전에서는 IN 절 내에 서브쿼리가 존재할 경우 매 레코드마다 서브쿼리를 실행시키는 형태로 수행되었다. 생각만해봐도 비효율적임을 알 수 있다. 5.6 에서부터 추가된 MATERIALIZED는 IN 절 내의 서브쿼리를 임시테이블로 만들어 조인을 하는 형태로 최적화를 해준다. DERIVED와 비슷하다고 생각하면 될 것 같다.

3. table

해당 실행계획 로우가 어떤 테이블을 이용하는 계획인지 보여준다. alias 로 표현되며 union, derived 같은 임시테이블은 어떤 select로 생성된 임시테이블인지 id를 보여준다.

테이블을 사용하지않는 쿼리의 경우엔 null로 표현된다.

ex) 테이블을 사용하지않는 쿼리의 예

SELECT 1;

4. type

type 컬럼은 옵티마이저가 어떤식으로 로우들을 조회하는지 보여준다. 개발자들이 실행계획을 확인하는 주된 이유는 쿼리 성능 향상을 꾀하기 위해서일텐데, 그런관점에서봤을때 앞선 3개보다는 이번에 살펴볼 type이 좀 더 중요도가 높다고 볼수 있다.

-system

row가 1건 미만인 테이블을 조회할때 나타난다. 빠르고말고할게 없는 상태지만 당연히 엄청나게 빠르다.

ex)

EXPLAIN SELECT * FROM (SELECT 1) a;

-const

unique 인덱스가 걸려있는 컬럼을 where 절 조건으로 이용하여 실제 데이터가 몇건이든 조회 결과가 1건인 경우 표현된다. 위에서 살펴본 system은 데이터 자체가 1건이어야하는데 사실 이런 경우는 특이 케이스이므로 평범한 경우엔 const가 가장 좋다고 볼 수 있다. 간단하게 얘기하면 기본키(primary key)를 이용하여 조회하면 const가 표현된다.

-eq_ref

보통 이런식으로 조인을 사용하게된다.

SELECT * FROM table1 a INNER JOIN table2 b ON a.col1 = b.col1;

조인이 작동하는 원리는 일단 첫번째 테이블(첫번째로 어떤걸 드라이빙할지는 옵티마이저가 정한다. table1이 앞이라고 꼭 table1을 먼저 탐색하지않는다.)을 조회한 후 해당 컬럼을 조건절에 대입함으로 이루어진다.

이때 첫번째 드라이빙 테이블의 결과가 두번째 테이블의 unique 인덱스가 걸려있는 컬럼일 경우 eq_ref가 표현되게된다. 조금 간단히 말하면 조인시 기본키를 이용하면 된다. unique 인덱스를 이용해 탐색하는건 일단 기본적으로 빠르므로 이역시 튜닝대상에서 고려할 필요가 없다.

-ref

eq_ref같은경우 unique 인덱스를 이용한다고했는데 unique 인덱스를 이용한다는것은 곧 두번째 테이블의 결과는 1건이라는 얘기가 된다. 하지만 경우에 따라 unique 인덱스가 아닌 컬럼을 이용할 수도 있는데 unique 인덱스랑은 상관없이 동등비교(=, <=>)를 이용하면 ref 가 표현된다. 이 경우 unique 인덱스가 아니기때문에 꼭 1건의 데이터가 반환된다는 보장이 되지 않으므로 eq_ref보다는 성능이 떨어지지만 이경우도 훌륭하다.

-ref_or_null

ref와 동일한데 null 비교까지 들어가 있는경우 표현된다. 그외 특별한 점은 없다.

-index_merge

merge 라는 명칭에서부터 느껴지듯 여러 인덱스를 사용하여 조회한 결과를 합치는 방식의 타입이다. 복수의 인덱스를 읽기때문에 최대의 최적화를 얻기 힘든경우도 있다.

-fulltext

해당 인덱스를 이용하기 위해서는 MATCH AGAINST 라는 특정 문법을 활용해야한다. 또한 fulltext 인덱스가 생성되어 있어야하며 해당 인덱스가 없는데 MATCH AGAINST 문법을 사용하면 에러가 발생하게된다. 확연히 fulltext 인덱스보다 빠른 인덱스가 있는게 아니라면 옵티마이저는 일단 fulltext 인덱스를 사용하려고 하게된다. ref보다는 성능이 낮다.

-unique_subquery

서브쿼리가 고유한 값만을 반환할때 표현된다.

-index_subquery

서브쿼리가 고유한 값만을 반환하지않을때 표현된다. 가령 예를들어 IN 절 안에 서브쿼리가 존재하는 경우 서브쿼리가 중복된 값들을 반환한다면 IN 절 내에서 사용되기 위해서 중복 값들을 제거해야한다. 이때 인덱스를 사용해서 중복값을 제거한다면 index_subquery가 표현된다.

-range

인덱스를 동등 비교가 아닌 범위 비교시 발생하는 가장 많이 사용되는 방식이다. <, >, LIKE 가 대표적인 연산자이다. 범위를 탐색하기때문에 다른 타입에 비해 성능이 좋은편은 아니지만 극도의 최적화를 원하는게 아니라면 괜찮다고 볼 수 있다.

-index

range가 필요한 인덱스 범위를 지정해서 탐색하는 방식이라면 index는 인덱스를 전부 스캔하는 방식이다. 인덱스도 하나의 테이블로 관리되기때문에 어찌보면 테이블 풀스캔이라고 볼 수도 있는 방식이다. 하나 안심할 거리라면 인덱스 테이블이 데이터 테이블보다는 크기가 작다는 것.

위 존재하는 다른 방식들을 사용할 수는 없지만 데이터 테이블까지 가지않고 인덱스만으로 처리가 가능할 때 표현된다. 즉 테이블 풀스캔을 피하기위한 최후의 보루라고 볼 수 있다.

-all

이것이 바로 테이블 풀스캔이다. MySQL이나 MariaDB같은경우 테이블 풀스캔이 발생했을때에도 최적화를 통해 최대한 빨리 모든 테이블을 스캔할 수 있도록 하기는 하지만 인덱스 찾아서 가는 방식보단 당연히 성능에 이슈가 발생한다.

index, all 방식과 같은 풀스캔 타입은 튜닝시 제거 1순위인 항목들이다.