close
프로필 배경
프로필 로고

고은별의 기술 공유 연구소

Database/SQL · 2023. 8. 17. fullscreen 넓게보기

SQL - 실행 계획과 비용, SQL 처리 흐름도

 

실행계획과 비용 (Execution Plan & Cost)

개념

  • 옵티마이저를 통해 최적의 실행 방법을 실행계획이라고 칭함
  • SQL에서 요구한 사항을 처리하기 위한 절차와 방법을 의미
  • 즉, SQL을 어떤 순서로 어떻게 실행할 지를 결정하는 작업
  • 생성된 실행계획을 확인하는 방법은 데이터베이스 벤더마다 다름
  • 최종적으로는 실행계획을 통해 보다 향상된 SQL을 활용 하기 위함

실행계획 구성 요소

  • 조인 순서(Join Order)
    • 조인 작업을 수행할 때 참조하는 테이블의 순서
    • 논리적으로 가능한 조인순서는 n!만큼 존재 (n은 FROM절에 존재하는 테이블의 수)
    • 현실적으로 옵티마이저가 적용 가능한 조인 순서는 같거나 적음
    • 예시를 위한 조건) FROM절에 A, B 두개의 테이블이 존재
    • 예시) 조인 작업을 위해 먼저 A테이블을 읽고 B테이블을 읽는 작업을 할 경우의 조인 순서는 A → B
  • 조인 기법(Join Method)
    • 두 개 이상의 테이블을 하나의 집합으로 만드는 연산
    • FROM절에 두 개 이상의 테이블이 나열될 경우 조인 수행
    • NL Join (Nested Loop Join: 중첩 루프 조인)
      • 프로그래밍에서 사용하는 중첩된 반복문과 유사한 방식으로 조인 수행
      • 주로 랜덤 액세스 방식으로 데이터를 읽기 때문에 좁은 범위의 데이터 처리에 유리
      • 반복문 외부에 있는 테이블을 선행 테이블 또는 외부 테이블(Outer Table)로 칭함
      • 반복문 내부에 있는 테이블을 후행 테이블 또는 내부 테이블(Inner Table)로 칭함
      • 선행 테이블의 조건을 만족하는 행을 추출하여 후행 테이블을 읽으면서 조인 수행
    • Sort Merge Join (정렬 병합 조인)
      • 조인 컬럼을 기준으로 데이터를 정렬하여 조인 수행
      • 주로 스캔 방식으로 데이터를 읽기 때문에 넓은 범위에 데이터 처리에 사용
      • 컬럼 인덱스를 사용하지 않기 때문에 조인 컬럼의 인덱스가 존재하지 않을 경우에도 사용 가능
      • NL Join에서 부담이 되는 넓은 범위의 데이터를 처리할 때 이용되는 조인 기법
      • 정렬할 데이터가 많아 메모리에 모든 정렬 작업을 수행하기 어려운 경우엔 임시 영역(디스크)를 사용하기 때문에 성능 저하 가능성 존재 (정렬이 없는 대량의 조인 작업 시엔 CPU 작업 위주 처리의 Hash Join이 성능상 유리)
      • 동등, 비동등 조인에 대한 조인 작업이 가능
    • Hash Join (해시 조인)
      • 해싱 기법을 이용하여 조인 수행
      • 조인을 수행할 테이블의 조인 컬럼을 기준으로 해시 함수를 수행하여 서로 동일한 해시 값을 갖는 것들 사이에 실제 값이 같은지를 비교하면서 조인 수행
      • NL Join의 랜덤 액세스 문제점과 Sort Merge Join의 정렬 작업의 부담성을 해결하기 위한 대안으로 등장
      • 컬럼 인덱스를 사용하지 않기 때문에 조인 컬럼의 인덱스가 존재하지 않을 경우에도 사용 가능
      • 해시 함수를 이용하여 조인을 수행하기 때문에 동등 조인(동등'=' 연산자를 수행하는 조인)에서만 사용 가능
      • 작업 수행 시 해시 테이블을 메모리에 생성하며 메모리에 적재할 수 있는 크기보다 더 크면 임시 영역(디스크)에 해시 테이블을 저장
    • 등등
  • 액세스 기법(Access Method)
    • 하나의 테이블을 액세스할 때 사용하는 방법
    • 인덱스 스캔(Index Scan) : 인덱스를 이용하여 테이블을 액세스하는 방법
    • 전체 테이블 스캔(Full Table Scan) : 테이블 전체를 모두 읽으면서 조건을 만족하는 행을 찾는 방법
  • 최적화 정보(Optimization Information)
    • 옵티마이저가 실행계획의 각 단계마다 예상되는 비용 사항을 표시한 것
    • 비용 사항이 표시되지 않았다는 것은 규칙기반 최적화 방식으로 실행계획을 생성했다는 것을 의미
    • 비용 사항이 표시된다는 것은 비용기반 최적화 방식으로 실행계획을 생성했다는 것을 의미
    • 비용 사항은 실제로 SQL을 실행하고 얻은 결과가 아닌 통계 정보를 바탕으로 옵티마이저가 계산한 예상치를 뜻함
    •  
    • Cost : 상대적인 비용 정보
    • Card : Cardinality의 약자로, 주어진 조건을 만족한 결과 집합혹은 조인 조건을 만족한 결과 집합의 건수를 의미
    • Bytes : 결과 집합이 차지하는 메모리 양을 바이트로 표시한 것
  • 연산(Operation)
    • 여러 가지 조작을 통해서 원하는 결과를 얻어내는 일련의 작업
    • 연산에는 조인 기법, 액세스 기법, 필터, 정렬, 집계, 뷰 등 다양한 종류가 존재
    • 예시) SQL에서 정렬 목적으로 ORDER BY 수행 : 정렬 연산 표시
  • 등등

<실행계획 정보의 구성요소 예시, 출처 한국데이터문화진흥원>

* 예시 이미지에서는 조인 기법으로 NL Join, 액세스 방법은 Index Scan인 비용기반 최적화 방식을 사용하고 있다.

실행계획 통계 수집

데이터베이스 시스템에서 테이블의 통계 정보를 수집 

  • Oracle
EXEC DBMS_STATS.GATHER_TABLE_STATS(
	'statOwnName', -- 스키마명
    'statTableName' -- 테이블명
)

 

  • MS-SQL
-- 테이블 통계 수동 업데이트
UPDATE STATISTICS YourTableName; -- 테이블명

-- 모든 테이블의 통계 업데이트
EXEC sp_updatestats;

 

  • MySQL
ANALYZE TABLE YourTableName; -- 테이블명

비용(Cost)

  • 쿼리를 수행하는 동안 발생할 것으로 예상되는 I/O 횟수 또는 예상 소요시간을 표현한 값
  • SQL 실행 계획에 표시되는 Cost는 어디까지나 예상치를 나타낸 값을 의미
  • 실행경로를 선택하기 위해 옵티마이저가 여러 통계정보를 활용해서 계산해 낸 값
  • 실측치가 아니므로 실제 수행할 때 발생하는 I/O 또는 시간과 차이 날 수 있음

실행 계획 확인

데이터베이스에서 옵티마이저가 선택한 실행 계획을 확인

실행 계획을 출력하지만 SQL문은 실행하지 않음

  • Oracle
set autotrace traceonly explain;

SQL; -- SQL문 작성

 

  • MS-SQL
    • SQL문을 작성하고 MS-SQL에서 컨트롤+L을 누르면 확인 가능
    • 혹은 상단 메뉴의 Query - Display Estimated Execution Plan 선택
SQL -- SQL문을 작성 후 SSMS에서 Ctrl+L

 

  • MySQL
explain SQL -- SQL문

실행 계획의 인덱스 변경 (인덱스 힌트)

  • Oracle
SELECT /*+ index(인덱스명) */ * FROM 테이블명

 

  • MS-SQL
SELECT * FROM 테이블명 WITH (INDEX(인덱스명))

 

  • MySQL
SELECT * FROM 테이블명 FORCE INDEX (인덱스명)

SQL 처리 흐름도

개념

  • SQL의 내부적인 처리 절차를 시각적으로 표현한 도표
  • 액세스 처리 흐름도에는 SQL문의 처리를 위해 조인 순서, 조인 기법, 액세스 기법 등을 표현
  • 즉, 실행계획을 시각화 한 것

SQL 처리 흐름도 예시

<SQL 처리 흐름도 예시, 출처 한국데이터산업진흥원>

  • 조인 순서
    • TAB1 → TAB2
    • TAB1 : Outer Table 또는 Driving Table (선행 테이블)
    • TAB2 : Inner Table 또는 Loopup Table (후행 테이블)
  • 테이블의 액세스 방법
    • TAB1 : 테이블 전체 스캔, 스캔 방식으로 액세스
    • TAB2 : I01_TAB2 라는 인덱스를 통한 인덱스 스캔, 조인시도 및 인덱스를 통한 랜덤 방식으로 액세스
      + 대량의 데이터를 랜덤 방식으로 액세스하게 되면 많은 I/O가 발생하여 성능상 좋지 않음
  • 조인 방법
    • NL Join 수행
  • 액세스 건수
    • SQL 처리를 위해 TAB1을 액세스한 건수
    • 테이블 전체 스캔을 수행했으므로 액세스 건수는 TAB1 테이블의 총 건수와 동일
  • 조인 시도 건수
    • TAB1을 액세스한 후 테이블에서 읽은 해당 건에 대해 조건을 만족한 건만이 TAB2와 조인을 시도한 건수
    • TAB1에 주어진 조건을 만족한 건수와 동일
    • 조건이 만족하지 않는다면 더 이상 조인 작업을 진행할 필요가 없기 때문
  • 테이블 액세스 건수
    • TAB1에 조건을 만족한 건만이 TAB2 테이블 액세스
    • 즉, 조인 시도한 건들 중에서 컬럼의 키를 동일하게 맞춘 조건까지 만족한 건과 동일 (TAB1.id = TAB2.id)
  • 성공 건수
    • SQL 실행을 통해 사용자에게 답으로 보여지는 결과 건수
    • TAB2 테이블을 액세스해서 TAB2의 조건까지 만족해야 사용자에게 보여질 수 있음

참고

 

저작자표시 동일조건
Database/SQL 관련 글
더 보기

티스토리툴바