SQLD 최적화

옵티마이저와 실행계획

---

옵티마이저란

• 사용자가 질의한 SQL문에 대한 최적의 실행방법을 결정하는 역할을 수행. 이러한 최적의 실행방법을 실행 계획이라고 한다. 
• 다양한 실행 방법들을 도출하고 그중에서 최적의 실행방법을 결정하는 것이 옵티마이저의 역할이다. 

 

비용기반 옵티마이저

• SQL문을 처리하는데 비용이 가장 적게 드는 실행계획을 선택하는 방식. 
• 여기서 비용이란 SQL문을 처리하는데 예상되는 시간 또는 자원을 의미. 
• 테이블, 인덱스 등의 통계 정보와 시스템 통계정보를 이용하여 최적의 실행계획을 도출. 
• 인덱스를 사용하는 비용이 테이블 풀 스캔보다 크다고 판단되면 테이블 풀 스캔을 유도. 

 

옵티마이저의 구성 요소

• 질의 변환기 : 사용자가 작성한 SQL문을 처리하기에 보다 용이한 형태의 SQL문으로 변환. 
• 비용 예측기 : 대안 계획 생성시에 생성된 대안 계획 비용을 예측하는 모듈.
• 대안 계획 예측기 : 동일한 결과를 생성하는 다양한 대안 계획을 생성하는 모듈. 

 

인덱스 기본

---

인덱스 란?

• 인덱스는 원하는 데이터를 쉽게 찾을 수 있도록 돕는 색인 기능과 같은 기능이다. 
• 검색 조건에 부합하는 데이터를 효과적으로 검색할 수 있다. 
• 한 테이블에서 0 ~ N개의 인덱스를 가질 수 있다.
• 과도하게 많은 인덱스가 존재하면 INSERT, UPDATE, DELETE와 같은 DML 작업 시 부하가 발생함. 

 

B-TREE 인덱스

• DBMS에서 가장 널리 사용되는 인덱스 형식. 
• 루트 블록, 브랜치 블록, 리프 블록으로 구성. 
• 리프 블로은 읻게스를 구성하는 칼럼의 데이터와 해당 데이터를 가지고 있는 행의 위치를 가리키는 레코드 식벽자 인 ROWID로 구성됨. 

https://hckcksrl.medium.com/mysql-%EC%9D%B8%EB%8D%B1%EC%8A%A4-c35c250ea9dc

 

인덱스 구조 상세

• 루트와 브랜치 블록에 있는 각 레코드는 하위 블록에 대한 주소 값을 가지고 있다. 
• 키 값은 하위 블록에 저장된 키 값의 범위를 나타낸다. 
• LMC가 가리키는 주소로 찾아간 블록에는 키 값을 가진 첫번째 레코드보다 작거나 같은 레코드가 저장 되어져 있음. 
• 리프 블록에 저장된 각 레코드는 키 값 순으로 정렬돼 있을 뿐만 아니라 테이블 레코드를 가리키는 ROWID를 갖는다. 
• 인덱스 키 값이 같으면 ROWID순으로 정렬된다. 
• 인덱스를 스캔하는 이유는 검색조건을 만족하는 소량의 데이터를 빨리 찾고 거기서 ROWID를 얻기 위함이다. 

 

ROWID 구성

• ROWID : 데이터 블록 주소 + 로우 번호
• 데이터 블록 주소 : 데이터 파일 번호 + 블록 번호
• 블록 번호 : 데이터 파일 내에서 부여한 상대적 순번
• 로우 번호 : 블록 내 순번

 

테이블 풀 스캔

• 테이블에 존재하는 모든 데이터를 읽어가면서 조건에 맞으면 결과로 추출하고 조건에 맞지 않으면 패스 하는 방식. 
• 풀 스캔으로 읽은 블록은 재 사용성이 낮다고 보고 메모리 버퍼 캐시에서 금방 제거될 수 있도록 관리함. 
• 옵티마이저가 테이블 풀 스캔을 선택하는 경우. 
  • SQL문에 조건이 존재하지 않는 경우. 
  • SQL문의 조건을 기준으로 하는 인덱스 없는 경우. 
  • 옵티마이저의 판단으로 풀 스캔이 유리하다고 판단한 경우. 
  • 전체 테이블 스캔을 하도록 강제로 힌트를 지정한 경우. 

 

인덱스 스캔

• 인덱스를 구성하는 칼럼의 값을 기반으로 데이터를 추출하는 액세스 기법. 
• 리프 블록은 인덱스를 구성하는 컬럼과 ROWID로 구성. 
• 인덱스의 리프 블록을 읽으면 인덱스 구성 칼럼의 값과 ROWID를 알 수 있음. 
• 인덱스를 읽어서 대상 ROWID를 찾으면 해당 ROWID로 다시 테이블을 찾아 가야함 (TABLE RANDOM ACCESS)
• SQL문에서 필요로 하는 칼럼이 모두 인덱스 구성 칼럼이면 테이블을 찾아갈 필요 없음. 
• 추출된 데이터틑 인덱스의 칼럼의 순서로 정렬된 상태로 반환됨. 
• 인덱스 스캔으로 읽은 블록은 테이블 풀 스캔에 의해서 읽은 블록에 비해 버퍼 캐시에 더욱 더 오랫동안 남아 있는다. 

 

인덱스 범위 스캔

• 인덱스를 이용하여 한건 이상의 데이터를 추출하는 방식 
• 인덱스 스캔으로 특정 범위를 스캔하면서 대상 레코드를 하나하나 리턴하는 방식. 

 

인데스 유일 스캔

• 인덱스를 사용하여 단 하나의 데이터를 추출하는 방식
• 유일 인덱스는 중복 레코드를 허용하지 않음. 
• 유일 인덱스는 반드시 '=' 조건으로 조회 해야 함. 

 

인덱스 전체 스캔

• 인덱스를 처음부터 끝까지 전체를 읽으면서 조건에 맞는 데이터를 추출함. 

 

인덱스 스킵 스캔

• 인덱스 선두 컬럼이 조건절에 없어도 인덱스를 활용하는 스캔 방식. 
• 루트 또는 브랜치에서 읽은 컬럼 값 정보를 이용해 조건절에 부합하는 레코드를 포함할 가능성이 있는 리프 블록만 액세스. 

 

인덱스 고속 전체 스캔

• 물리적으로 디스크에 저장된 순서대로 인덱스 리프 블록들을 Multi Block I/O 방식으로 읽어 들임. 

 

인덱스 역순 범위 스캔

• 인덱스 리프 블록은 Doubly Linked List방식으로 저장되어 있음. 
• 인덱스를 뒤에서부터 앞쪽으로 스캔하기 때문에 내림차순으로 정렬된 결과 집합을 얻을 수 있다. 

 

테이블 스캔 vs 인덱스 스캔

테이블 풀 스캔	인덱스 스캔
항상 이용 가능	인덱스가 존재해야만 가능
한번에 여러 개의 BLOCK을 읽음	한번에 한 개의 블록만 읽음
많은 데이터를 조회 시 성능 상 유리	극히 일부분의 데이터를 조회 시 유리
Table Random Access 부하 없음	Table Random Access에 의한 부하 발생
읽었던 블록을 반복해서 읽는 경우 없음	읽었던 블록을 반복해서 읽을 수 있음.

 

조인 수행 원리

---

조인 이란

• 두개 이상의 테이블을 하나의 집합으로 만드는 연산. 
• 각각의 조인 단계에서는 서로 다른 조인 기법을 이용 가능함. 

 

NL 조인

• RANDOM ACCESS 위주로 조인.
• 한 레코드 씩 순차적으로 진행.
• DRIVING 테이블 처리 범위에 의해 전체 성능이 결정됨. 
• 인덱스 유무, 인덱스 구성에 크게 영향을 받음. 
• 소량의 데이터를 처리하거나 부분범위처리가 가능한 OLTP환경에 적합. 

 

소트 머지 조인

• 양쪽 집합을 우선적으로 정렬을 하고 NL 조인과 같이 수행. 
• 인덱스 유무에 영향을 받지 않음 : 미리 정렬된 인덱스가 있으면 좀 더 빠르게 수행할 수는 있음
• 양쪽 집합을 개별적으로 읽고 나서 조인 : 조인 컬럼에 인덱스가 없는 상황에서 두 테이블을 독립적으로 읽어 조인 대상 집합을 줄일 수 있을 때 아주 유리
• 스캔(Scan) 위주의 액세스 방식 : 양쪽 소스 집합에서 정렬 대상 레코드를 찾는 작업은 인덱스를 이용 Random 액세스 방식으로 처리될 수 있음

 

해시 조인

• 대량의 데이터 처리가 필요하고 쿼리 수행 시간이 오래 걸리는 대용량 테이블을 조인할 때
• NL 조인처럼 Random 액세스 부하 없음
• 소트 머지 조인처럼 정렬 부하 없음
• 해시 테이블을 생성하는 비용에 따라서 Build Input이 (Hash Area에 담을 수 있을 정도로 충분히) 작을 때 효과적

 

조인 순서의 중요성

항목	설명	비고
First Table	- 두 개의 Table을 조인 할 경우 먼저 처리되는 테이블을 의미. - WHERE 절에 상수/바인드 변수 조건이 존재하는 것이 유리.	OUTER TABLE DRIVING TABLE BUILD INPUT
Second Table	- 두 개의 테이블을 조인 할 경우 뒤에 처리되는 테이블을 의미.  - First Table로 부터 입력 값을 받아서 처리하게 됨.  - 조인 조건의 여부 및 성질이 조인 성능에 영향을 미침.  - 인덱스 존재 여부가 중요.	INNER TABLE DRIVEN TABLE PROBE INPUT
최적화 Join Order	- First Table이 Second Table에 비해서 작은 집합이어야 성능상 유리.