[SQL] SQL 성능확인, Query Plan 보는 법 (Oracle, MySQL)

1. Query Plan이란?

• SQL 관계형 데이터베이스 관리 시스템(RDBMS)의 데이터 접근에 사용되는 순서가 있는 단계별 집합
• 엑세스 플랜의 관계형 모델 개념의 특화된 경우
• SQL이 선언형이기 때문에 주어진 쿼리를 실행하기 위해 수많은 방법이 존재하는 것이 일반적이며 이에 따라 다양한 성능 차이를 보임
• Query Optimizer는 Query 실행을 위한 각기 다른 가능성 있는 올바른 플랜 중 일부를 평가한 다음 최적의 옵션을 고려할 때 반환

---

2. Query Plan 보는 법, 요소(Oracle)

1) Query Plan 보는 법

1. 위에서 아래로 내려가면서 제일 먼저 읽을 위치 찾기
2. 내려가는 과정에서 같은 들여쓰기가 있으면, 무조건 위에서 아래 순으로 읽기
3. 같은 레벨에 들여쓰기된 하위 스텝이 존재하면, 가장 안쪽으로 들여쓰기 된 스텝을 시작으로 상위 스텝으로 읽기

---

2) Plan 요소

• Cost
  • Cost는 누적된 값이며 하위(Child)의 Cost를 Roll Up 함
    → 마지막에 읽는 Cost가 모든 쿼리문에 대한 누적 Cost
  • 옵티마이저가 측정한 오퍼레이션 수행에 필요한 예측 비용
  • 쿼리를 수행함에 있어 사용된 자원이나, 작업의 단위
  • 대체로 적을 수록 효율적인 쿼리(좋은 성능)
  • Full Scan을 해야 하는 쿼리는 의미가 없을 수도 있음
  • cost 수치가 더 높은 질의문이 빠른 경우도 있음
• Cardinality
  • 행 집합에서 행의 수를 표시(분포도)
  • 행 집합은 기본 테이블, 뷰, 조인이나, GROUP BY의 결과
  • 행 집합을 의미하니 적게 나타날수록 SQL이 빠를 수 있음
  • 옵티마이저가 측정
• Bytes
  • 각 실행계획 단계에서 Access된 byte 수를 의미
  • 옵티마이저가 측정

---

3) 비용 계산 방법

• Full table scan

Cost = 전체 블록 수 / DB_FILE_MULTIBLOCK_READ_COUNT의 보정 값

• Unique index scan

Cost = blevel + 1

• Fast Full Index Scan

Cost = leaf_blocks / db_block_size

• Index Range Scan

Cost = blevel + (Selectivity X leaf_blocks) + (Selectivity X Cluster Factor)

• Sort-Merge Join

Cost = (Outer 테이블의 Sort Cost +Inner 테이블의 Sort Cost) -1

• Nest-Loop Join

Cost = Outer 테이블의 Cost + (Inner 테이블의 Cost * Outer 테이블의 Cardinality)

• Hash Join

Cost = (Outer 테이블의 Cost × #Hash 파티선수 +Inner 테이블의 Cost) + 2

---

3. Oracle

1) Orange,Toad(DB Tool)

실행계획 보는 단축 키 : 쿼리 블록 drag + Ctrl + E

---

2) Explain plan for

EXPLAIN plan FOR
SELECT *
FROM EMP
WHERE EMPNO > 7600
AND JOB = 'MANAGER'; -- 확인 할 쿼리를 plan table로 저장

SELECT *
FROM TABLE(dbms_xplan.display); -- Plan table 출력

---

4. MySQL

1) Visual Optimizer(work bench)

쿼리에 대해 돋보기 클릭

---

2) EXPLAIN

-- 문법
EXPLAIN
쿼리

EXPLAIN
SELECT A.actor_id,
       A.first_name,
       A.last_name,
       B.film_info,
       A.last_update
FROM 
	(
	SELECT *
	FROM sakila.actor
	WHERE last_update > '2023-01-12'
	) A
INNER JOIN sakila.actor_info B
ON A.actor_id = B.actor_id
AND A.first_name = B.first_name
AND A.last_name = B.last_name;

• ID
  • 실행계획의 순서. 이 순서대로 select 문이 실행
• select_type
  • SIMPLE : 단순 select문
  • PRIMARY : 첫번째 쿼리
  • DERIVED : select문으로 추출된 테이블 ( from 절에서의 서브쿼리 또는 inline view)
  • SUBQUERY : sub query 중 첫번째 select문
  • UNION : UNION쿼리에서 PRIMARY를 제외한 나머지 select문
  • DEPENDENT SUBQUERY
  • DEPENDENT UNION
• table
  • 대상이 되는 테이블 or alias명
• partitions
  • 파티션 사용 시, 대상이 되는 파티션
• type
  • data access 타입(우수한 순서대로 적어 둠)
    • system : 0개 또는 하나의 row를 가진 테이블. const 타입의 특별한 케이스. (MyISAM, Memory 테이블)
    • const : primary key나 unique Key의 모든 컬럼에 대해 equal 조건으로 검색 반드시 1건의 레코드만 반환
    • eq_ref : 조인에서 첫 번째 읽은 테이블의 컬럼값을 이용해 두 번째 테이블을 primary key나 unique Key로 equal 조건 검색으로 두번째 테이블은 반드시 1건의 레코드만 반환 (1:1 관계)
    • ref : 조인의 순서와 인덱스의 종류와 관계없이 equal 조건으로 검색 (1:n 관계)
    • unique_subquery : IN(sub-query) 형태의 조건에서 반환 값에 중복 없음
    • index_subquery : unique_subquery와 비슷하지만 반환 값에 중복 있음
    • range : 인덱스를 하나의 값이 아니라 범위로 검색. 가장 많이 사용
    • Index : 인덱스를 처음부터 끝까지 읽는 인덱스 풀 스캔
    • All : 풀스캔. 성능 가장 안 좋음
• possible_keys
  • 해당 테이블에서 데이터를 찾기위해 선택한 인덱스 목록
• key
  • 실제로 쿼리 실행에 사용한 인덱스
• key_len
  • 쿼리를 처리하기 위해 단일, 다중 컬럼으로 구성된 인덱스의 각 레코드에서 몇 바이트까지 사용했는지
• ref
  • 행을 추출하는데 키와 함께 사용된 컬럼이나 상수 값
• rows
  • 쿼리 수행에서 예상하는 검색해야 할 행수. 조인문이나 서브쿼리 최적화에 있어서 중요한 항목.
  • 조회 결과 수와 rows가 차이가 크다면 성능 개선 필요
• extra
  • 쿼리에 관한 추가적인 정보
    • distinct : 이미 처리한 값과 동일한 값을 가진 Row는 처리하지 않음.
    • not exist : left join을 수행함에 매치되는 한 행을 찾으면 더 이상 매치되는 행을 검색하지 않음.
    • Range checked for each record :사용할 좋은 인덱스가 없음.
    • using filesort : 정렬을 위해 추가적인 과정을 필요로 함(물리적인 정렬작업 수행)
    • using index : 실제 데이터 Block을 읽지 않고 인덱스 Block 만으로 결과를 생성할 수 있는 경우
    • using temporary : 임시 테이블을 사용. order by 나 group by 절이 각기 다른 컬럼을 사용할 때 발생
    • using where : where절이 다음 조인에 사용될 행이나 클라이언트에게 돌려질 행을 제한하는 경우
    • using index for group-by

쿼리를 가능한 한 빠르게 하려면, Extra 값의 Using filesort나 Using temporary에 주의해야 함
EXPLAIN의 출력 내용 중 rows 컬럼값들을 곱해봄으로써 얼마나 효과적인 join을 실행하고 있는지 알 수 있음

 

 

 

참조:

https://bobr2.tistory.com/170

https://velog.io/@sihyung92/query-tunning-1-execution-plan

https://moonsiri.tistory.com/56