[DB] 실행계획 항목 분석 기초

실행계획이란

말 그대로 요청받은 SQL문으로 데이터를 옵티마이저가 '어떻게 가져올 것인지'에 대한 계획 또는 경로를 의미한다. 같은 데이터를 가져오더라도 지름길을 통해 가져온다면 더 효율적으로 데이터를 가져올 수 있을 것이다. 요청할 SQL문의 실행계획을 확인하려면 EXPLAIN, DESCRIBE, DESC 중 아무 키워드나 사용하면 된다.

 

EXPLAIN 키워드를 사용해 SQL의 실행계획을 확인해보면, 다음과 같이 id, select_type, table, type, key 등의 정보가 출력된다.

임의의 post테이블의 select 쿼리 대한 explain 실행결과

위 사진은 임의의 post라는 테이블에 explain select * from post; 쿼리를 보냈을 경우 실행계획을 보여준다.

 

이 정보들을 통해 옵티마이저가 요청받은 SQL문을 비효율적으로 수행하진 않는지, 쿼리 튜닝은 어떻게 해야할 지에 대한 정보를 얻을 수 있다.

 

 

---

 

MySQL 실행계획 구성요소

1. id

실행순서를 의미한다. 즉, id 값이 작을수록 먼저 실행된 것이고 같으면 조인이 수행된 것이다.

 

2. select_type

1) SIMPLE: 단순한 select 쿼리에 대한 경우이다.

 

2) PRIMARY: 서브쿼리를 감싸는 외부 쿼리이거나 UNION이 포함된 두 개의 쿼리 중 첫 번째에 등장하는 select 쿼리에 해당한다. 즉, 서브쿼리나 집합 연산 등을 통해 두 개의 쿼리가 합해져 보내질 때 옵티마이저가 우선적으로 접근할 SQL을 의미하기 위해 PRIMARY라는 타입을 갖는다.

 

3) SUBQUERY: 독립적으로 수행되는 서브쿼리에 해당한다. 즉, 스칼라 서브쿼리(select문에 포함), 중첩 서브쿼리(where절에 포함)에 해당한다.

 

4) DERIVED: from절에 포함된 서브쿼리의 경우이다. 즉, from절에 위치한 서브쿼리는 뷰와 같이 하나의 임시적인 테이블을 만들기 때문에 derived 키워드가 사용된다.

 

5) UNION: UNION 또는 UNION ALL 연산에서 나중에 처리될 select 구문임을 의미한다. PRIMARY와 차이점을 구분하자.

 

6) UNION RESULT: 말그대로 UNION 연산 결과를 가져오기 위해 사용한다. UNION 연산은 내부적으로 중복을 제거하기 위해 별도의 메모리 또는 디스크를 사용한다. 따라서 중복을 허용하는 경우라면 UNION ALL을 사용해 메모리에 복사하고 중복을 체크한 후 결과를 가져오는 과정에 대한 시간 소비를 줄일 수 있다. 

 

7) DEPENDENT SUBQUERY: UNION 또는 UNION ALL이 포함된 쿼리가 서브쿼리로 들어가있고 이 서브쿼리 안에서 메인 쿼리의 테이블을 사용하는 구조일 경우, UNION 또는 UNION ALL연산에서 '첫 번째 쿼리'에 해당한다. 이러한 쿼리 구조는 상당히 문제가 되는데, 쿼리가 독립적으로 수행되지 못하고 메인 쿼리에서 사용하는 테이블로부터 값을 하나씩 공급받는 구조이기 때문이다. 튜닝이 필수적인 쿼리이다.

 

8) DEPENDENT UNION: UNION 또는 UNION ALL이 포함된 쿼리가 서브쿼리로 들어가있고 이 서브쿼리 안에서 메인 쿼리의 테이블을 사용하는 구조일 경우, UNION 또는 UNION ALL연산에서 '두 번째 쿼리'에 해당한다. DEPENDENT SUBQUERY와 마찬가지로 메인 쿼리에서 사용하는 테이블로부터 값을 하나씩 공급받는 구조이기 때문에 성능적으로 좋지 않다. 튜닝이 필요한 경우이다.

 

9) UNCACHEABLE SUBQUERY: 서브쿼리는 보통 메모리에 상주해(캐싱되어) 메인 쿼리가 수행되면서 재사용되지만, 재사용이 불가능한 서브쿼리의 경우에 해당한다. 서브쿼리 안에 사용자 정의 함수나 사용자 변수가 포함되거나  RAND(), UUID() 등의 함수가 사용되어 서브쿼리를 조회할 때마다 결과가 달라지는 경우에 해당한다. 따라서 이러한 부분들을 외부로 뺄 수 있다면 빼고, 서브쿼리가 메모리에 상주될 수 있도록(캐싱될 수 있도록) 튜닝해야 한다.

 

10) METERIALIZED: IN절에 위치한 서브쿼리가 임시 테이블을 생성하고 메인 쿼리의 테이블과 조인되는 경우에 해당한다. 

 

 

 

3. table

실행계획 정보에서 테이블명을 의미한다. 보통 from 절 등에 위치한 서브쿼리는 내부적으로 테이블로 간주되는데, 이때 table 항목에는 <derived{number}> 와 같은 형태로 구성된다. 만약 table 항목이 <derived2> 일 경우 id 항목이 2인 테이블을 의미한다. 

 

 

 

4. partition

데이터가 저장된 논리적인 영역을 표시하는 항목이다. 기존에 정의한 파티션 중에서 파티션을 선택하여 접근하는 것이 SQL 성능 측면에서 유리하다. 만약 실행계획이 너무 많은 partition에 접근하는 것으로 출력된다면 파티션 정의를 튜닝해볼 필요가 있다. 파티션을 적절하게 사용하면 데이터 엑세스 범위를 효율적으로 다룰 수 있다.

 

 

5. type **

테이블의 데이터를 어떻게 찾을지에 대한 정보를 명시한다. 테이블 전체를 스캔할 수도 있고 인덱스를 통해 스캔할 수도 있다.

 

1) system: 테이블에 데이터가 0 또는 1개 있는 경우로 최상위 성능을 갖는 type의 경우이다.

 

2) const: 조회되는 데이터가 1건일 경우로 PK 또는 인덱스를 통해 바로 데이터 한 건에만 접근할 수 있다.(where id = 1)

 

3) eq_ref: 조인이 수행될 때 driven 테이블의 PK 또는 인덱스를 이용해 데이터를 한 건만 가져오는 경우이다. driven 테이블과 driving 테이블의 조인 키가 1대1로 매핑되는 경우를 의미한다. 또한 조인시에는 driven 테이블과 driving 테이블의 조인 키가 같으므로 조인시 성능상 유리한 경우이다. 즉, type이 eq_ref이면 조인 키가 1대1로 매핑되는 driven 테이블을 의미한다.

 

4) ref: driving 테이블의 하나의 조인 키 값에 대해 매핑되는 driven 테이블의 튜플 개수가 2개 이상일 경우에 해당한다(일대다). 보통 PK를 조인 키로 이용해 조인을 수행하면 일대일 관계이지만 그렇지 않은 경우에 해당한다(유일성이 없는 인덱스 사용 등). 따라서  데이터의 양이 많을 경우 성능 저하의 원인이 될 수 있다. 즉, type이 ref인 테이블은 driving 테이블로 부터 매핑되는 조인 키가 2개 이상인 driven 테이블을 의미한다.

 

5) ref_or_null: IS NULL 구문에서 대해 인덱스를 활용하도록 최적화된 방식이다. MySQL과 MariaDB는 NULL에 대해서도 인덱스를 활용해 검색할 수 있다. 이때 NULL은 가장 앞쪽에 정렬되는데, 테이블을 검색할 떄 NULL 데이터 양이 적다면 옵티마이저는 ref_or_null 방식을 활용해 효율적인 실행계획을 구성할 수 있다. 하지만 NULL 양이 많다면 쿼리 튜닝 대상이 된다.

 

6) range: 테이블에서 연속된 범위를 조회하는 유형이다. 예를 들어, =, <, >, <=, >=, IS NULL, <>, BETWEEN, <=> (null safe equals), IN 연산을 통한 범위 스캔 등이 해당한다. 스캔할 범위가 넓으면 성능 저하의 원인이 되므로 튜닝 대상이 된다.

 

7) fulltext: 텍스트 검색을 빠르게 처리하기 위한 특수 목적의 인덱스(full text index) 를 사용하는 방식이다.

 

8) index_merge: 2개 이상의 테이블레 생성된 각각의 인덱스를 병합해 동시에 적용되는 유형이다. 

 

9) index: 물리적인 인덱스 블록을 처음부터 끝까지 훑는 방식이다. 즉, 테이블 풀스캔이 아닌 인덱스 풀스캔인 경우이다.

 

10) all: 테이블을 처음부터 끝까지 읽는 테이블 풀스캔 방식이다.인덱스를 통해 데이터에 접근하는것이 오히려 더 비효율적이라고 옵티마이저가 판단할 경우, 실행계획  type이 all이 된다.

 

 

6. possible_keys

옵티마이저가 SQL을 최적화하기 위해 사용할 수 있는 인덱스들이 출력되는 항목이다. 최종적으로 사용한 인덱스는 아니며 사용될 가능성이 있는 인덱스를 의미한다..

 

 

 

7. key **

옵티마이저가 쿼리를 최적화 하기 위해 사용한 PK 또는 인덱스 명을 나타내는 항목이다. 이 항목을 보고 비효율적인 인덱스를 사용했거나, 인덱스를 사용하면 유리한 경우에서 사용하지 않을 경우, 쿼리 튜닝 대상이 된다.

 

 

8. key_len

인덱스를 사용할 때에는 인덱스 전체를 사용(풀스캔)하거나 일부 인덱스만 사용하는데, 이때 사용된 인덱스의 바이트 수를 의미한다. (cf. VARCHAR는 단위 당 3바이트이다)

 

 

9. filtered **

스토리지 엔진으로부터 MySQL 엔진으로 데이터를 가져오고 MySQL 엔진에서 얼마만큼의 데이터가 필터링되어 실질적으로 결과로 출력되었는지를 나타내며, 단위는 %이다. 만약 filtered가 100.00 이라면 스토리지 엔진으로부터 가져온 데이터가 MySQL 엔진에서 모두 살아남아 실질적으로 모두 출력되었음을 의미한다. (where절 등을  통해 필터링 됨)

 

 

10. extra **

1)  distinct: UNION 구문이나 distinct가 포함된 select 절인 경우에 해당한다.

 

2) using where: where절을 사용해 필터링할 경우를 의미한다. MySQL 엔진이 필터 조건을 가지고 있고, 스토리지 엔진으로부터 가져온 데이터들에 필터 조건을 적용시킨다.

 

3) using temporary: 데이터의 중간 결과를 임시 테이블에 저장하겠다는 의미이다. 보통 결과를 정렬하거나 중복을 제거하기 위해서 메모리에 임식 테이블을 만들고 작업한다. distinct, group by, order by 등이 포함된 구문의 경우에 사용된다. 임시 테이블을 저장하기 위해 메모리나 디스크가 필요하므로 쿼리 튜닝 대상이 될 수 있다.

 

4) using index: 커버링 인덱스라고도 불리면 물리적인 테이블 접근없이 인덱스만을 읽는 방식이다.

 

5) using filesort: 정렬이 필요한 경우 대상 데이터를 메모리에 올리고 정렬 작업을 수행하는 경우에 해당한다. 인덱스를 사용하는 경우라면 정렬이 필요없다.

 

6) using join buffer: 조인 수행중 중간 결과를 저장하는 버퍼(메모리)를 사용하겠다는 의미이다. driving 테이블의 데이터를 먼저 접근해 결과를 가져오고 이를 토대로 driven 테이블에서 서로 일치하는 조인 키값을 찾는 과정에서 조인 버퍼를 사용한다.

 

7) using union/intersect/sort_join: index merger를 어떻게 수행했는지에 대한 상세정보이다.

 

8) using index condition: using where과 같이 필터 조건을 MySQL 엔진이 수행하지 않고, MySQL 엔진이 필터 조건을 스토리지 엔진에게 전달해 필터 작업으로 인한 MySQL 엔진의 부하를 줄이는 방식이다. 또한 스토리지 엔진과 MySQL 엔진 사이의 이동하는 데이터 양도 줄어들기 떄문에 옵티마이저의 최적화 방법이다.

 

---

 

위 실행계획 항목들이 상황에 맞게 적절하게 사용되어야 좋은 옵티마이저라고 할 수 있다. select_type 항목에서는 SIMPLE, PRIMARY, DERIVED 를, type 항목에서는 system, const, eq_ref를, extra 항목에서는 using index를 적절하게 사용되어야 한다. 보통 DEPENDENT, UNREACHABLE, using filter sort, using temporary 등의 실행계획 요소가 사용된다면 튜닝 대상이 될 수 있다.

 

 

Reference

- https://docs.oracle.com/cd/E17952_01/mysql-8.0-en/explain-output.html