쿼리 실행계획 Query Plan

어떻게 좋은 쿼리를 짜 효율적으로 DB에서 데이터를 가지고 올 수 있을까?라는 질문에

80%가 넘는 답변은 "실행계획 떠봐" 였다.

 

상당히 중요한 부분이란 걸 알고 있었지만 어렴풋이 알고 넘어갔었기에 이렇게 포스팅을 남겨 제대로 정리해 본다 :)

 

조회는 이렇게 실행할 쿼리 앞에 EXPLAIN을 쓰거나

마우스 우클릭 > 실행 > 실행계획 보기 (DBeaver의 경우)로 조회가 가능하다. 

explain
select * from w_work;

 

아래와 같은 결과를 볼 수 있는데 실행계획에서 각 칼럼이 의미하는 것을 정리해보겠다.

 

id 컬럼

SELECT쿼리를 구분하기 위한 용도로 쓰인다. ( 몇 개의 SELECT가 실행되었는지 확인할 수 있다. )

- SELECT 쿼리에 join 등을 통해 여러 개의 테이블을 같이 조회하더라도 join으로 연결되어있기 때문에 하나의 select문으로 인식해 한 개의 id가 부여된다.

 

- SELECT 쿼리 내부에 서브 쿼리 혹은 union 등을 사용해 여러 테이블을 조회하는 경우엔 다른 id가 부여된다.

 

select_type 컬럼

SELECT 쿼리가 어떤 타입인지 알 수 있다.

SIMPLE : UNION이나 서브 쿼리를 사용하지 않은 단순 SELECT일 경우 SIMPLE

- 실행 계획 내에서 select_type이 SIMPLE인 쿼리는 반드시 하나만 있다.

 

PRIMARY : UNION, 서브 쿼리가 포함된 SELECT 쿼리의 경우에 가장 바깥쪽 쿼리가 PRIMARY쿼리로 표시되고 SIMPLE과 마찬가지로 실행 계획 내에서 반드시 하나만 존재

 

UNION : UNION을 사용한 쿼리의 경우 두 번째 이후 쿼리는 UNION으로 표시된다.

- UNION에 사용된 첫 번째 SELECT 쿼리는 UNION 쿼리의 결과를 대표하는 select_type으로 설정되어 DERIVED라는 select_type으로 조회된다.

 

DEPENDENT UNION : UNION을 사용한 쿼리의 경우에 결합된 쿼리가 외부 쿼리에 의해 영향을 받는 것을 의미한다.

- 내부 쿼리가 외부의 값을 참조해서 처리될 때 DEPENDENT 키워드가 추가된다.

 

UNION RESULT : MYSQL 8.0 이후로 UNION ALL을 쓸 때는 조회 결과를 담을 "임시 테이블"을 생선 하지 않기로 업데이트되어 보이진 않지만 UNION 혹은 UNION DISTINCT 쿼리는 임시 테이블에 결과를 담아 실행 계획상 UNION RESULT는 UNION의 결과를 담는 임시 테이블을 의미한다.

 

SUBQUERY : FROM절 이외에 사용된 서브 쿼리들에게 표시된다

- DERIVED라고 표시됨

 

DEPENDENT SUBQUERY : FROM절 이외에 사용된 서브 쿼리가 바깥쪽 SELECT 쿼리에서 정의된 칼럼을 사용하는 경우에 해당 이름으로 표기된다

 

DEPENDENT DERIVED : MYSQL 8.0 이전 버전엔 FROM절의 서브 쿼리에 외부 컬럼을 사용할 수 없었으나 버전 업데이트 이후 가능하게 되었다. LATEARAL(레터럴) 조인으로 FROM절의 서브 쿼리가 외부 컬럼을 사용할 수 있게 되었는데 레터럴 조인을 사용했을 때 표기된다.

 

UNCACHEABLE SUBQUERY : SUBQUERY, DEPENDENT SUBQUERY는 쿼리 결과를 캐싱할 수 있는데 특정 경우 결과를 캐시 할 수 없는 경우 표시됨

 

MATERILAIZED : FROM절 혹은 IN 형태의 쿼리에 사용된 서브 쿼리를 최적화시킬 때 적용된다.

보통의 경우 외부 쿼리의 테이블을 먼저 읽어와 비효율 적인데 반해 서브 쿼리 내용을 임시 테이블로 구체화한 후에 외부 부 테이블과 조인하는 형태로 최적화시킨다. 서브 쿼리가 먼저 구체화되었다는 것을 표시함

 

- 결국 임시 테이블을 사용하므로 효율이 그다지 좋지 않음

 

이렇게 select_type을 보고 어떻게 개선해야할지 힌트를 얻을 수 있기 때문에 잘 숙지해두는 것이 좋다.

기본적으로 DEPENDENT는 외부 쿼리에 의존하는 쿼리로 성능상 비교적 느리며 임시 테이블을 사용하는 쿼리 또한 디스크에 임시테이블을 만들 수 있기 때문에 비효율적이다.

 

DERIVED 또한 JOIN으로 해결해야하기 때문에 제거의 대상이 된다.

 

table 컬럼

실행계획을 조회할 때 쿼리별로 조회하는 것이 아닌 테이블 단위로 분류해서 표시된다.

조회된 테이블에 대한 정보를 나타내며 <>로 감싸져 있는 table의 경우엔 임시테이블을 사용한다.

 

 

partitions 컬럼

파티셔닝(partitioning)하여 테이블을 관리한다고 가정할 때, 어떤 파티션을 읽었는지를 알려주는 정보다.

5.7 이전에는 EXPLAIN PARTITION 명령을 해야 보였으나 8.0 부터는 EXPLAIN 명령만해도 보이게 되었다.

 

type 컬럼

실행한 쿼리가 적절하게 인덱스를 참조 했는지를 확인 할 수 있는 핵심컬럼이다.

기본적으로 ALL 타입을 제외 하곤 모두 인덱스를 참조했다고 볼 수 있지만 무조건 효율적인 것은 아니다.

 

앞서 index란 개념에 대해서 알아보면 더 좋다.

 

type컬럼에서 성능이 좋은 순서대로 나열해 보겠다.

1 ~ 9 효율적인 케이스 10 ~ 12 비효율적인 케이스

 

1. system : 레코드가 0 or 1건만 존재하는 테이블에 접근 할때의 방식

 

2. const : 쿼리에 PRIMARY KEY / UNIAUE KEY 컬럼을 이용하는 WHERE 조건에 있으며 결과가 반드시 1건을 반환하는 쿼리로 접근 할때의 방법

- 쿼리 결과가 1개인 것을 실행전에 DBMS가 예측할 수 있어야한다. 

 

3. eq_ref : 여러 테이블이 JOIN되는 쿼리에서만 발생하며 JOIN에서 컬럼 값을 두 번째 이후 읽는 테이블의 PRIMARY KEY/ UNIQUE INDEX 컬럼의 동등 조건이 사용될 때 반드시 1건만 존재한다는 보장이 있을때 사용되는 접근방법

 

4. ref : 바로 위의 eq_ref와 다르게 JOIN 순서에 영향을 받지 않으며, PRIMARY KEY, UNIQUE INDEX  등의 제약도 상관없이 사용된다. INDEX와 관계없이 동등(equal) 조건이 사용 될때 접근되는 방법 (결과가 1건이라는 보장이 없으므로 eq_ref보단 느리지만 빠른 방법이다! )

 

5. fulltext : Mysql로 전문 검색 인덱스를 이용해 레코드에 접근한느 방식으로 전문 검색할 인덱스가 있어야한다.

"MATCH ... AGAINST ..." 구문을 사용해서 실행된다. (사실 아직 한번도 본적이 없다...)

6. ref_or_null : ref와 같은데 NULL 비교(IS NULL)가 추가된 형태 (이것도 본적이 없다...)

7. unique_subquery : WHERE 조건에 IN(subquery)를 사용할때, 서브쿼리에서 중복되지않은 고유한 값을 반환할 때 표시되는 방식

 

8. index_subquary : IN 연산자 특성상 IN () 괄호안에 들어가는 조건 정보는 중복값이 없어야한다. 이것이 중복되었을때 인덱스를 이용해 중복을 제거하는 방식

 

9. range : 인덱스를 하나의 값이 아닌 범위로 검색하는 경우에 사용되는 방법이며 주로 < , >, IN NULL, IS NOT NULL 등의 범위 검색에 표시되는 방식

---

10. index_merge : 2개 이상의 인덱스를 이용해 각각의 검색결과를 만든 후 결과들을 합치는 방식으로 실제 우선순위는 range보다 높지만 비효율적으로 동작하지 않는 문제가 종종있다고 한다...

 

11. index : 인덱스를 처음부터 끝까지 다 읽어야하는 경우에 표시됨

 

12. ALL : 풀 테이블 스캔 가장 나쁜 방법으로 정의 하긴 했지만 잘못설계된 인덱스를 타는 경우보다 더 적절한 경우도 있다.

 

 

possible_keys 컬럼

옵티마이저가 쿼리를 처리하기 위해 여러가지 방법을 모색하던 중 찾은 후보 인덱스 목록

 

key 컬럼

possible_keys의 후보중에 실제로 사용된 인덱스를 의미 인덱스를 타지 못한경우는 null로 표기

 

key_len 컬럼

실무에선 인덱스를 단일 컬럼으로 만들기 보단 다중 컬럼으로 만들어지는 경우가 더 많은데

이 때 다중 컬럼 인덱스 중에서 몇 바이트를 사용했는지 표시해준다.

각 인덱스 컬럼에 할당된 바이트를 알 수 있으므로 몇 개의 인덱스 컬럼이 사용했는지를 추측할 수 있다.

 

아래 쿼리는 w_work(work_no , p_work_no, member_no 등 key가 있는 ) 테이블을 work_no로 작업을 조회하는 쿼리이다.

select * from w_work where work_no = 2000;

key_len은 8(bigint = 8byte)이 나온다. work_no컬럼(bigint)

 

 

ref 컬럼

type 컬럼에서 접근 방법이 ref일때 표시되는데 어떤 컬럼이 조건에 사용되었는지를 보여준다.

"func"라고 표시될 때도 있는데 이는 단순 컬럼이 아닌 어떤 가공된 컬럼이 사용될 때 표시된다.

 

row 컬럼

옵티마이저가 비용을 산정하기 위해 얼마나 많은 레코드를 읽고 비교 했는지를 예측해본 레코드의 수

실제 레코드 수와 일치하지 않은 경우가 많으며 대략적인 예측이다.

 

Extra 컬럼

mysql이 어떻게 쿼리를 풀었는지에 대한 부가 정보가 표시된다. 많은 종류가 있으며 여러개가 동시에 나올 수 있다.

 

 

 

참고 - https://jeong-pro.tistory.com/243

MySQL 실행 계획(좋은 쿼리는 못 만들어도 뭐같은 쿼리는 만들지 말아야지)