MariaDB 쿼리 계획(실행 계획)

MariaDB 쿼리 계획(실행계획) 방법

실행할 쿼리 앞에 explain을 붙여주고, 실행 하면 끝!!

 

개요

MariaDB는 쿼리 처리 순서를 확인 할 수 있는 쿼리 계획(query plan) 기능을 제공한다.
MariaDB에서는 쿼리 계획을 확인하는 explain, analyze가 있다. explain은 예상되는 실행 계획을 보여주고, analyze는 쿼리를 실제 실행한 후 실행한 쿼리 계획을 보여준다. 

 

MariaDB는 (10.0.1 이후부터) 사용 가능

select version(); -- 버전확인 

 

쿼리 계획 항목 

1) id

대상 쿼리문에 join이 포함되어 있을 때, 어떠한 순서로 테이블이 join되는지를 나타내는 값이다.

 

2) select_type

각 단계를 실행할 때 어떤 종류의 SELECT가 실행되었는지를 나타낸다.

값이 DEPENDENT SUBQUERY, 혹은 DEPENDENT UNION 인 경우 의존성 등의 문제로 쿼리가 특정 순서로만 실행되어야 함을 뜻하므로 비효율적인 쿼리일 가능성이 있다. 

- simple: 단순 Select 

- primary: UNION이나 서브 쿼리가 포함된 SELECT 쿼리의 실행 계획에서 가장 바깥쪽에 있는 단위 쿼리는 primary로 표시. primary 단위의 쿼리는 반드시 하나만 존재.

-  union:  union에서의 두번째 혹은 나중에 따라오는 select문

- union result: union의 결과물

- dependent union: union에서의 두번째 혹은 나중에 따라오는 select문, 외곽 쿼리에 의존적이다.

- dependent subquery: 서브쿼리의 첫번째 select, 바깥 쪽 쿼리에 의존적이다.

- subquery: 서브 쿼리의 첫 번째 select 

 

3) table

접근하는 테이블. 테이블 or 임시 테이블일 수 있다.

 

4) type

테이블 내에서 접근이 필요한 레코드를 어떻게 찾았는지에 대한 정보이다. 

속도와 아주 밀접한 항목이다. 위에서 아래로 좋은 순서입니다. 
- system: 테이블 내에 레코드가 1개 이하인 경우. 
- const: 해당 단계가 PK 나 유니크 인덱스 검색을 이용해 레코드에 접근함을 뜻한다. 가장 빠른 검색. 

- eq_ref: 조인수행을 위해 각 테이블에서 하나의 행만이 읽혀지는 형태. 
- ref: 인덱스를 이용하여 동등 비교 연산을 통해 레코드에 접근. 역시 매우 빠른 검색 방법이다.
- fulltext: fulltext 색인을 사용. 일반적인 비교 연산으로 접근이 어려운 경우에 주로 사용되므로 최적화하기 어려운 경우가 많다.
- range: 인덱스를 사용하여 주어진 범위 내의 행들만 추출된다. range 타입은 키 컬럼이 상수와 =, <>, >, >=, <, <=, is null, <=>, between 또는 in 연산에 사용될때 적용된다.
- index: index 전체를 스캔해야만 필요한 레코드에 접근할 수 있음을 뜻한다. 풀 테이블 스캔보다는 빠르지만, 인덱스가 매우 큰 경우 등에는 비효율적이다.

- all: 인덱스를 이용하여 필요한 레코드를 검색할 수 없어, 전체 테이블을 스캔해야만 함을 뜻한다. 당연히 테이블 내 레코드 수에 따라 실행 시간이 매우 길어지므로 적절한 인덱스 추가나 HINT 문 사용 등을 통해 최적화하는 것이 좋다. 

만약 조인에 쓰인 첫 테이블이 고정이 이라면 효율적이다. 보통 상수값이나 상수인 컬럼값으로 row를 추출하도록 인덱스를 추가하여 ALL 타입을 피할 수 있다.

5) possible_keys

레코드에 접근하기 위해 사용할 수 있는 키, 혹은 인덱스 목록을 보여준다. 실제로 사용된다는 의미가 아니므로 실제로 어떠한 키가 사용되었는지는 key 항목을 확인해야 한다.

 

6) key, key_len

레코드에 접근하기 위해 어떠한 index를 참조하는지, 인덱스 중 몇 바이트를 참조했는지에 대한 정보이다. key_len 은 둘 이상의 컬럼으로 구성된 인덱스를 참조했을 경우에만 의미가 있다.

 

7) ref

인덱스 검색 시 비교 연산 등에 사용되는 기준값을 보여준다. 최적화 시에는 큰 의미는 없다.

 

8) rows

필요한 레코드들을 추려내는 과정에서 몇 개의 레코드에 접근해야 하는지를 예측(정확하지 않다)하여 보여준다.

9) extra

특이 사항들이 있다면 해당 내용을 표시해준다.

예를 들어, 접근해야 하는 컬럼이 모두 인덱스에 포함되어 있어 인덱스 검사만으로 필요한 값을 반환할 수 있다면 Using index 가 표시된다. 때에 따라 성능에 영향을 줄 수 있는 값들이 있으므로, 최적화 시에 이 컬럼이 비어 있지 않다면 확인할 것을 권한다.

 

유의사항

인덱스의 크기나 수, 레코드의 수 등을 같이 고려하므로 같은 쿼리라 하더라도 실행 계획 조회 시점에 따라 실행 계획이 달라질 수 있다. 그러므로 유효한 데이터를 얻기 위해서는 될 수 있는 한 실 서비스에서, 혹은 실 서비스와 최대한 비슷한 환경에서 실행 계획을 조회하는 것이 좋다.

참고: https://blog.ifunfactory.com/