## 공부정리
과목2 3장 SQL 최적화 기본 원리
1) 옵티마이저와 실행계획
2) 인덱스 기본
3) 조인 수행 원리
#1 옵티마이저와 실행계획
1 옵티마이저
- SQL문에 대한 최적의 실행방법을 결정하여 실행 계획 도출
- 최적의 실행방법, 실행계획을 짠다
* SQL문 실행 순서
1) 파싱(Parsing) : SQL 문법 검사 및 구문 분석 작업
2) 실행(Execution) : 옵티마이저의 실행 계획에 따라
3) 인출(Fetch) : 데이터를 읽어 전송
2 규칙기반 옵티마이저 - 규칙(우선순위)를 가지고 실행 계획 생성
- 인덱스를 이용한 엑세스 방식이 전체 테이블 엑세스 방식보다 우선 순위가 높음
-> 이용 가능한 인덱스가 존재하면 전체 테이블 엑세스 방식보다 항상 인덱스를 사용하는 실행계획을 생성
- 조인 칼럼에 대한 인덱스가 양쪽에 존재 : 우선순위가 높은 테이블이 선행(Driving)
- 한쪽에만 인덱스 존재 : 인덱스 없는 테이블이 선행 (NL Join 사용)
- 모두 인덱스 존재 X : FROM 절의 뒤에 나열된 테이블이 선행 (Sort Merge Join 사용)
- 우선순위가 동일 : FROM절에 나열된 테이블의 역순으로 선행 테이블 선택
3 비용 기반 옵티마이저 - SQL문을 처리하는데 필요한 비용이 가장 적은 실행계획을 선택
① 특징
- 처리 비용이 가장 적은 실행계획 선택, 데이터 딕셔너리의 통계정보나 DBMS의 차리오 같은 쿼리도 다른 실행계획이 생성될 수 있음, 실행계획의 예측 및 제어가 어려움
② 옵티마이저 엔진
- 질의 변환기(Query Transformer) : 작성된 SQL문을 처리하기 용이한 형태로 변환하는 모듈
- 비용 예측기(Estimator) : 생성된 계획의 비용을 예측하는 모듈
-> 연산의 중간 집합의 크기 및 결과 집합의 크기, 분포도 등의 예측이 정확해야 한다.
-> 정확한 통계정보, 대안 계획을 구성하는 각 연산에 대한 비용 계산식이 정확해야 함.
- 대안계획 생성기(Plan Generator) : 동일한 결과를 생성하는 다양한 대안 계획 생성 모듈
-> 대안 계획은 연산의 적용 순서 변경, 연산 방법 변경, 조인 순서 변경 등을 통해 생성
-> 대안 계획의 생성이 많아지면 최적화를 수행하는 시간이 그만큼 오래 걸린다.
4 옵티마이저 실행계획
- SQL에서 요구한 사항을 처리하기 위한 절차와 방법
- 다양한 실행계획(처리방법)마다 성능(실행시간)은 서로 다를 수 있다.(옵티마이저는 최적의 실행계획 생성)
- 구성 요소 : 조인순서, 조인기법, 액세스기법, 최적화정보, 연산 등
5 SQL 처리 흐름도
- SQL 내부적 처리 절차를 시각적으로 표현한 도표 (실행시간을 알 수 없다)
- 인덱스 스캔, 테이블 전체 스캔 등과 같은 엑세스기법을 표현
- 성능적인 측면도 표현 가능
1. 조인순서는 tab1 -> tab2이다.
2. 테이블 액세스 방법은 tab1은 전체 스캔을 의미하고, tab2는 I01_tab2이라는 인덱스를 통한 인덱스 스캔을 했음을 표시하였다.
3. tab1에 대한 액세스는 스캔방식이고 조인시도 및 I01_tab2 인덱스를 통한 tab2 액세스는 랜덤방식이다.
4. 성능적인 관점을 살펴보기 위해서 처리흐름도에 일량을 함께 표시할 수 있다. 건수(액세스 건수, 조인시도 건수, 테이블 액세스 건수, 성공 건수)라고 표시된 곳에 SQL 처리를 위해 작업한 건수 또는 처리 결과 건수 등의 일량을 함께 표시할 수 있다. 이것을 통해 비효율이 발생하고 있는 지에 대한 힌트를 얻을 수 있다.
#2 인덱스 기본
1 인덱스
- 검색 조건에 부합하는 데이터를 효과적으로 검색할 수 있도록 돕는 기능(찾아보기)
- 인덱스키로 정렬되어 있어 조회속도가 빠름, DML 작업 효율은 저하
- 주로 WHERE절 ORDER BY 절에서 자주 쓰이는 칼럼을 인덱스로 지정
2 BLOCK의 개념
- 블록은 데이터가 저장되는 최소 단위, 테이블의 데이터들이 행 단위로 저장되어 있음
- 인덱스는 해당 테이블의 블록에 주소를 가지고 있음
3 랜덤 엑세스
- 인덱스를 스캔하여 테이블로 데이터를 찾아가는 방식
- 많은 양의 데이터를 읽을 경우 인덱스 스캔보다 FULL 스캔이 더 효율적
4 인덱스 종류
- 단일 인덱스
- 결합 인덱스(여러 칼럼을 묶어 결합 인덱스로 구성)
* 결합순서
① =조건으로 많이 쓰는 칼럼이 앞에 오는게 좋음
② BETWEEN 범위 지정하는 칼럼이 그 다음에 오는게 좋음
③ ID처럼 분별력이 높은 칼럼이 그 다음으로 오는게 좋음
5 트리 기반 인덱스 : RDBMS에서 가장 일반적인 인덱스 (B-트리 인덱스)
- 일치검색, 범위검색 둘 다 적합
- 브랜치 블록에서 가장 상위에 있는 블록을 루트 블록이라 한다.
- 브랜치 블록(인터럴 블록) : 하위 단계의 블록을 가리키는 포인터를 가짐
- 리프 블록 : 인덱스를 구성하는 칼럼의 데이터 + 해당 데이터에 대한 행의 위치를 가리키는 레코드 식별자, 양방향 링크를 가진다.
1단계, 브랜치 블록의 가장 왼쪽 값이 찾고자 하는 값보다 작거나 같으면 왼쪽 포인터로 이동
2단계, 찾고자 하는 값이 브랜치 블록의 값 사이에 존재하면 가운데 포인터로 이동
3단계, 오른쪽에 있는 값보다 크면 오른쪽 포인터로 이동
만약 37과 50 사이의 모든 값을 찾고자 한다면 위와 동일한 방법으로 리프 블록에서 37을 찾고, 50보다 큰 값을 만날 때 까지 오른쪽으로 이동하면서 인덱스를 읽는다. 이것은 인덱스 데이터가 정렬되어있고, 리프 블록이 양방향 링크로 연결되어 있기 때문에 가능하다. 인덱스를 경유해서 반환된 결과 데이터는 인덱스 데이터와 동일한 순서로 갖게 되는 특징을 갖는다.
인덱스를 생성할 때 동일 칼럼으로 구성된 인덱스를 중복해서 생성할 수 없다. 그렇지만 인덱스 구성 칼럼은 동일하지만 칼럼의 순서가 다르면 서로 다른 인덱스로 생성할 수 있다. 인덱스의 칼럼순서는 성능에 중요한 영향을 미치는 요소이다.
6 클러스터형 인덱스(SQL Server)
- 인덱스는 저장 구조에 따라 클러스터형과 비클러스터형 인덱스로 구분
- 인덱스의 리프 페이지 = 데이터 페이지, 테이블 탐색에 필요한 레코드 식별자가 리프 페이지에 없음
- 리프페이지의 모든 로우(데이터)는 인덱스 키 칼럼순으로 물리적으로 정렬되어 저장됨
- 인덱스 키 칼럼과 나머지 칼럼을 리프에 같이 저장 -> 테이블 랜덤 엑세스 필요 X
- 클러스터형 인덱스의 리프 페이지를 탐색하면 해당 테이블의 모든 칼럼 값 바로 얻음
7 비트맵 인덱스
- 질의 시스템 구현 시에 모두 알 수 없는 경우인 DW 및 AD-HOC 질의 환경을 위해 술계
- 하나의 인덱스 키 엔트리가 많은 행에 대한 포인터를 저장
8 인덱스 스캔 방식 종류
① 전체 테이블 스캔(FULL TABLE SCAN)
- 테이블의 모든 데이터를 읽으며 데이터 추출, 읽은 블록의 재사용성을 낮다고 판단해 메모리 버퍼에서 제거
- 1. SQL문에 조건이 없거나 2. SQL문 조건 관련 인덱스가 없거나 3. 전체 테이블 스캔을 하도록 강제로 힌트를 지정하거나 4. 옵티마이저가 유리하다고 판단하는 경우 수행
- 많은 데이터를 조회할 때 유리
② 인덱스 스캔(INDEX SCAN)
- 인덱스를 구성하는 칼럼의 값을 기반으로 데이터 추출
- 인덱스를 읽어 ROWID를 찾고 해당 데이터를 찾기 위해 테이블을 읽음
- 적은 데이터를 조회할 때 유리
- 랜던 엑세스에 의한 부하가 발생할 수 있고 중복 스캔 비효율성이 발생
③ 전체 테이블 스캔과 인덱스 스킨 방식의 비교
- 전체 테이블 스캔 : 비효율적, 여러 블록씩, But 테이블을 대부분 데이터 찾을 땐 유리
- 인덱스 스캔 : 레코드 식별자 이용, 정확한 위치 알고 읽음. 한번의 I/O요청에 한 블록씩
#3 조인 수행 원리
1 NL 조인 (주로 엑세스 방식으로 데이터 읽음)
- 두 개의 테이블을 중첩된 반복문처럼 조인을 수행
- 반복문 외부(처음 테이블)에 있는 테이블 : 선행 테이블 / 외부 테이블
- 반복문 내부(두번째 테이블)에 있는 테이블 : 후행 테이블 / 내부 테이블
- 결과 행의 수가 적은 테이블을 선행 테이블로 선택한다
2 NL 조인 작업 방법
① 선행 테이블에서 조건에 맞는 값을 찾는다
② 선행 테이블의 조인 키를 가지고 후행 테이블 조인 키 확인
③ 후행 테이블의 인덱스에 선행 테이블의 조인 키 존재 확인
④ 인덱스에서 추출한 레코드 식별자를 이용하여 후행 테이블 엑세스하여 버퍼에 저장
⑤ 앞의 작업을 선행 테이블에서 만족하는 키 값이 없을 때 까지 반복하여 수행
3 SORT MERGE JOIN (주로 스캔 방식으로 데이터 읽음)
- 조인 칼럼을 기준으로 데이터를 정렬하여 조인한다
- 넓은 범위의 데이터를 처리할 때 주로 사용
- 정렬 데이터가 많을 경우 디스크 I/O로 인한 부하 발생하여 성능이 떨어질수도 있다
- 비동등 조인에 대해서도 조인이 가능하다
- 인덱스가 존재하지 않을 경우에도 사용할 수 있다
4 SOFT MERGE JOIN 작업 방법
① 선행 테이블에서 조건에 맞는 행을 찾는다
② 선행 테이블의 조인 키를 기준으로 정렬 작업 수행
③ ①-② 번 작업을 반복 수행하여 모든 행을 찾아 정렬한다
④ 후행테이블에서도 같은 작업 진행
⑤ 정렬된 결과를 이용하여 조인을 수행하고 결과 값을 추출 버퍼에 저장
5 Hash JOIN
- 조인을 수행할 테이블의 조인 칼럼을 기준으로 해쉬 함수를 수행하여 서로 동일한 해쉬 값을 갖는 것들 사이에서 실제 값이 같은지 비교
- 조인 칼럼의 인덱스가 존재하지 않을 경우에도 사용가능
- '=' 로 수행하는 동등 조인만 가능
- 경과 행의 수가 적은 테이블을 선행 테이블로 사용
6 Hash JOIN 작업 방법
① 선행테이블에서 조건에 만족하는 행을 찾음
② 선행테이블의 조인 키를 기준으로 해쉬 함수를 적용하여 해쉬 테이블 생성
③ ①-② 작업 반복하여 선행 테이블의 모든 조건에 맞는 행을 찾아 해시테이블 완성
④ 후행테이블에서 조건에 만족하는 행을 찾음
⑤ 후행테이블의 조인 키를 기준으로 해시함수 적용하여 해당 버킷을 찾음
⑥ 같은 버킷에 해당되면 조인에 성공하여 추출버퍼에 저장
⑦ 후행 테이블의 조건만큼 반복 수행하여 완료
'SQL 공부일지' 카테고리의 다른 글
| SQL 공부 일지 11일차 230830 (0) | 2023.08.30 |
|---|---|
| SQL 공부 일지 10일차 230829 (0) | 2023.08.30 |
| SQL 공부 일지 9일차 230825 (1) | 2023.08.27 |
| SQL 공부 일지 8일차 230823 (0) | 2023.08.23 |
| SQL 공부 일지 7일차 230820 (1) | 2023.08.20 |
