SQL 성능 향상을 위한 공부: 1-4) 실행 계획을 확인하는 방법

DBMS별 실행 계획을 확인하는 명령어

사용자는 옵티마이저가 결정한 실행 계획을 직접 확인할 수 있다. 확인을 위한 명령어는 DBMS마다 조금씩 다르다.

 

DBMS	예상 실행 계획 (쿼리 실행 전에 확인)	실제 실행 계획 (쿼리 실행과 함께 확인)
PostgreSQL	EXPLAIN <쿼리>	EXPLAIN ANALYZE <쿼리> (*ANALYZE는 실제 실행 + 시간 표시)
MySQL	EXPLAIN <쿼리> EXPLAIN EXTENDED <쿼리>	EXPLAIN ANALYZE <쿼리> (8.0 이상)
SQLite	EXPLAIN QUERY PLAN <쿼리>	EXPLAIN <쿼리> (실제 바이트코드)

 

 

 

실행 계획을 보는 기본적인 방법

- 실행 계획은 일반적으로 '트리 구조'다.

- 중첩 단계가 깊을수록 먼저 실행된다.

- 만일 중첩 단계가 동일한 경우, 위에서 아래 방향으로 실행된다.

 

 

 

실행 계획에서 확인할 수 있는 것

기본적인 SQL 구문의 실행 계획을 살펴보며, 실행 계획에서 어떤 것을 확인할 수 있는지 알아보자. DBMS의 실행 계획 포맷은 완전히 같지 않기 때문에 공통적으로 나타나는 부분을 위주로 학습하고자 한다. 읽기 쉬운 PostgreSQL의 실행 계획을 예시로 공부한다.

 

EXPLAIN
SELECT *
FROM cafes;

 

테이블 풀 스캔의 실행계획 (PostgreSQL)

 

(1) 조작 대상 객체

- 어떤 객체를 조작하고 있는지 표시

- 일반적으로 테이블이나 인덱스가 가장 많이 오게된다. 이외에도 파티션, 시퀀스처럼 SQL 구문으로 조작할 수 있는 객체라면 무엇이라도 올 수 있다. 

- 위 예시에서는 'cafes' 라는 테이블이 조작 대상 객체인 것을 확인할 수 있다.

 

 

(2) 객체에 대한 조작의 종류 (실행 계획에서 가장 중요)

- 무엇을 하고 있는지 표시.

- 기본적으로 트리의 최하단에는 스캔 노드가 있다. 테이블 접근 방식에 따라 여러 종류의 스캔 노드가 존재하며, 위 예시에서는 'Seq Scan' 을 할 것이라는 것을 알 수 있다.

 

조작의 종류	설명
Seq Scan	순차적으로 접근하며 테이블 풀 스캔 → 파일을 순차적으로 접근해서 해당 테이블의 전체 데이터를 읽어낸다.
Index Scan	인덱스를 사용해서 필요한 데이터만 접근하여 스캔
Bitmap Index Scan	Seq Scan과 Index Scan의 사이라고 일단 이해하고 넘어가자.

 

- 만약 쿼리가 조인(join), 집계(aggregation), 정렬(sorting) 같은 추가 연산을 필요로 한다면, 스캔 노드 위에 이러한 작업을 수행하는 상위 노드들이 추가된다.

 

 

(3) rows ⇒ 해당 노드가 끝까지 실행될 경우 반환하는 행의 수 [추정치]

- 해당 노드에서 실행을 위해 접근하는 "대상"이 아니라 실행 후 "출력되는 행 수"를 의미한다.

- 다른 말로 하면, 해당 노드가 실행 후 상위 노드에 전달하는(emit) 튜플 수의 추정치라고도 할 수 있다.

 

[참고] DBMS마다 rows 수치의 의미가 다를 수 있음. 

- 특정 DBMS에서는 rows 수치가 조작 대상이 되는 레코드 수를 의미한다.

- 이는 각 조작에서 얼마만큼의 레코드에 접근해서 처리하는지 표시하는데 MySQL의 경우가 그렇다.

 

 

(4) cost ⇒ 실행 비용  [추정치]

- 비용 상수를 기반으로 계산한 실행 비용

- 시간이 아님 주의!

- 실행 비용은 작을수록 좋은 건가요? 일반적으로 비용은 대부분의 경우 작을수록 좋다. 다만 실행 계획에서 보이는 '실행 비용(cost)'나 '실행 시간', 처리 레코드 수는 추정값이므로 '절대 지표'로 사용하면 안된다. 이름만 보고 "작은 cost가 무조건 빠르다"는 식의 절대적 해석은 위험하다. 근데 왜일까?

 

 

(5) width ⇒ 행 당 평균 바이트 수  [추정치]

- 실행 계획에 따라 실행한 결과를 예상했을 때, 행 당 평균 바이트 수

 

 

※ 주의할 점: 실행 계획에서 표시되는 (3), (4), (5) 수치들은 모두 카탈로그 매니저로부터 얻은 통계 정보를 기반으로 계산된 값이다. 즉 실제 SQL 구문을 실행한 시점의 수치와는 차이가 있을 수 있다. 옵티마이저는 어디까지나 통계라는 메타 정보를 믿을 뿐, 실제로 그 시점에 테이블을 직접 보고 실행 계획을 세우는 게 아님!

 

 

 

예시

1. 테이블 풀 스캔 vs 인덱스 스캔의 실행 계획

 

▶ 테이블 풀 스캔: 테이블의 모든 레코드를 읽는다. 읽을 데이터가 많을 수록 비용이 선형적으로 증가한다. 데이터 양에 비례하여 처리  비용이 선형적으로 늘어남 → O(n)

▶ 인덱스 스캔: 필요한 데이터만 인덱스를 통해 찾아간다. 인덱스 스캔의 처리 비용은 로그 형태로 늘어난다. 인덱스 스캔 시, 처리 비용이 완만하게 증가한다는 뜻. 이는 인덱스를 사용할 때 활용되는 B-tree 가 모집합의 데이터양에 따라 대수 함수적으로 처리 비용이 늘어나기 때문이다. 특정 데이터양(n)을 손익분기점으로 인덱스 스캔이 풀 스캔보다도 효율적인 접근을 하게 된다. → O(log n)  

 

* 단 레코드 수가 작으면 차이가 크지 않고, 레코드 수가 많아질수록 처리 비용 차이가 커진다.

* 일반적으로 스캔하는 모집합 레코드 수에서 선택되는 레코드 수가 적다면 테이블 풀 스캔보다 빠르게 접근을 수행한다. -> 이건 무슨 말이지?

 

 

2. 간단한 테이블 '결합'의 실행 계획

- 결합의 실행 계획을 이해하는 것은 굉장히 중요하다.

- 왜냐하면 결합을 사용하면 실행 계획이 상당히 복잡해지는데, 옵티마이저도 최적의 실행 계획을 세우기 어렵다. 그래서 결합과 관련하여 SQL 처리 지연이 발생하는 경우가 꽤 많다. 결합은 실무에서 많이 사용하기 때문에 결합 시점의 실행 계획 특성을 공부하는 것은 중요하다. 

 

<결합 연산 알고리즘 종류>

 

알고리즘	설명
Nested Loops	한쪽 테이블을 읽으면서 레코드 하나마다 결합 조건에 맞는 레코드를 다른 쪽 테이블에서 찾는 방식.
Sort Merge	결합(조인) 키로 레코드를 정렬한 후 → 순차적으로 두 개의 테이블을 결합하는 방식. 결합 전에 정렬이 먼저 수행되어야 하는데, 이때 작업용 메모리로 '워킹 메모리'를 사용한다.
Hash	결합(조인) 키 값을 해시값으로 매핑하는 방식. 해시 테이블을 만들어야 하므로, 이때도 작업용 메모리 영역을 필요로 한다.

 

 

 

 

 

[참고]

MySQL 공식문서: EXPLAIN Output Format

https://dev.mysql.com/doc/refman/8.4/en/explain-output.html

MySQL :: MySQL 8.4 Reference Manual :: 10.8.2 EXPLAIN Output Format

 

PostgreSQL 공식문서: Using EXPLAIN

https://www.postgresql.org/docs/current/using-explain.html

14.1. Using EXPLAIN