mybatis로 개발을 하면서 query의 성능을 최적화 시키는게 필요한 시점들이 있다. 이때 튜닝을 하게 된느데 튜닝의 기초라고 할 수 있는 플랜 보는 법을 알아보자.
1. plan (실행계획) 이란?
DBMS는 SQL을 수행할 최적의 처리 경로를 생성해 주는 핵심 엔진인 옵티마이저(Optimizer)를 가지고 있다. 이 옵티마이저는 우리가 SQL을 작성하고 실행하면 이 쿼리를 어떤 순서로 실행하겠다고 실행계획을 세우게 된다. 이 실행계획이 어떤 순서로 짜여져 있냐에 따라 성능의 차이가 크기 때문에 plan을 보는 방법을 알아두면 쿼리 성능 개선에 큰 도움이 된다.
2. plan 보는 법
툴을 확인하거나 "Explain plan for"를 쿼리 앞단에 붙여서 실행계획을 떠보면 위와 비슷한 형식으로 나온다. (툴별로 조금씩 다를 수 있음)
우리는 보통 plan을 읽을 때 Operation 부분을 기준으로 순서를 보게 된다. 순서는 다음과 같이 읽는다.
1. 레벨이 다른 경우에는 안쪽 레벨부터 해석한다.
2. 레벨이 같은 경우에는 위에서 아래로 해석한다.
간단하게 말하면
1. 형제중에서는 형이 먼저
2. 부모와 자식중에서는 자식이 먼저
라는 말이다.
그래서 위의 예시는 순서대로 읽어보면 5 -> 4 -> 6 -> 3 -> 7 -> 2 -> 8 -> 1 -> 0 이다.
해당 순서로 위의 예시를 해석하면 다음과 같다.
5: PK_EMP 인덱스로 INDEX RANGE SCAN을 해서 조건을 만족하는 인덱스 블록과 키값을 검색한 결과를 반환
4: 5에서 읽은 ROWID를 기반으로 EMP 테이블에서 조건에 부합하는 결과 반환
6: PK_DEPTNO 인덱스에서 INDEX UNIQUE SCAN 방식으로 검색한 결과의 ROWID 반환
3: 4, 6번에서 반환된 데이터 기준으로 NESTED LOOP JOIN 방식으로 4번에서 반환된 데이터의 숫자만큼 반복해서 조인한 결과 반환
7: 4번과 동일하게 DEPT 테이블에서 조건에 부합하는 결과 반환
2: NESTED LOOP JOIN 방식으로 JOIN의 결과 반환
8: SALGRADE 서브쿼리 실행
1: 서브쿼리를 통해 해당 조건을 만족하는 데이터를 필터링해서 반환
예시로 아래 플랜의 순서를 읽어보자.
정답
4 -> 3 -> 6 -> 5 -> 2 -> 1
4. PLAN으로 성능 개선하기
Plan을 뜨면 우리는 다음과 같은 요소들을 확인할 수 있다. (Oracle, Dbeaver 기준)
그 중 주목해야하는 값은 Cost, Cardinality, Bytes이다.
Cost
- 옵티마이저가 측정한 오퍼레이션 수행에 필요한 예측 비용.
- 플랜 읽는 순서에 따라 누적된 값. 대체로 작을 수록 효율적인 쿼리.
- Full Scan을 해야하는 쿼리는 의미가 없을 수 있음.
- cost 수치가 더 높은데 빠른 경우도 있음.
Cardinality
- 행 집합에서 행의 수를 표시 (분포도)
- 행 집합은 기본 테이블, 뷰, 조인이나, GROUP BY의 결과
- 작을 수록 SQL이 빠를 수 있음 (작을수록 행이 적음)
Bytes
- 각 실행계획 단계마다 Access된 byte 수를 의미 (I/O)
즉 위 이미지의 플랜을 종합하면, 해당 쿼리는 최종적으로 1개의 데이터를 읽어오며, 77,522Byte의 I/O를 유발하고 1,385만큼의 비용을 발생시킨다.
다른 예시를 살펴보자.
이 플랜에서는 고객 테이블을 FULL SCAN을한 뒤에 SORT를 하는데, 차례대로 633K -> 6M 만큼의 비용을 발생시킨다. 우리는 여기서 조건절의 인덱스 유무도 중요하지만 소팅도 튜닝에서 중요한 요소임을 알 수 있다.
또 다른 예시를 보자.
주문 테이블의 주문번호는 인덱스가 걸려있어 INDEX(UNIQUE SCAN)을 하고 있지만 고객 테이블에서는 고객 번호에 인덱스가 없어 TABLE ACCESS (FULL) 로 풀스캔을 하고 있음을 알 수 있다. 그런데 여기서 Card=1이라는 값으로 고객번호가 UNIQUE 함을 알 수 있고, 고객번호 컬럼을 인덱스로 생성해야 함을 알 수 있다.
인덱스를 생성한 결과는 다음과 같다.
고객 테이블에서도 주문테이블과 동일하게 INDEX SCAN을 하고 있다.
이처럼 테이블 FULL SCAN을 하는 경우에 우리는 3가지를 생각해 볼 수 있다.
- 해당 쿼리에 대한 적절한 인덱스가 존재하지 않는 경우로, 필요한 인덱스를 생성해 해결 가능하다.
- 인덱스는 존재하나 인덱스를 타지 않는 경우로, 힌트절을 사용해서 해결 가능하다.
- 테이블을 FULL SCAN 하는 것이 인덱스를 통한 랜던 엑세스보다 유리한 경우로, 데이터 조회 범위가 커서 인덱스를 사용하는 것이 별 효용성이 없을 때다.
Reference
'TIL' 카테고리의 다른 글
| Servlet부터 Filter와 Interceptor까지 (0) | 2024.07.24 |
|---|---|
| 무중단 배포 전략 - 롤링, 블루-그린, 카나리 배포 (0) | 2024.01.21 |
| Maven Lifecycle (+ Gradle, Ant) (0) | 2023.10.18 |
| @Transactional 사용시 주의점(+ @Async, 같은 Class 내에서 사용하는 경우) (0) | 2023.07.05 |
| @Transactional 개념, 사용법 (1) | 2023.05.08 |