[SQLD자격증] # 3. (2과목) SQL 기본 및 활용 - SQL 최적화 기본원리 요점 정리

옵티마이저와 실행계획
옵티마이저	- 사용자가 질의한 SQL문에 대해 최적의 실행 방법을 결정하는 역할 수행 - 질의에 대해 실행 계획 생성 - SQL의 실행계획을 수립하고 SQL을 실행하는 데이터 베이스 관리 시스템의 소프트웨어
규칙기반 옵티마이저	- 우선 순위를 가지고 실행계획을 생성 - 우선 순위가 높은 규칙이 적은 일량으로 해당 작업을 수행 - 인덱스 유무와 SQL문에서 참조하는 객체 등을 참고 - 제일 낮은 우선순위는 전체 테이블 스캔 - 제일 높은 우선순위는 ROWID를 활용하여 테이블 엑세스 - 적절한 인덱스가 존재하면 항상 인덱스를 사용하려고 함
비용기반 옵티마이저	- 현재 대부분의 DB에서 사용 - 테이블 및 인덱스 등의 통계정보를 활용하여 SQL문을 처리하는데   필요한 비용이 가장 적은 실행계획을 선택하는 방식 - 비용이란 SQL문을 처리하기 위해 예상되는 소요시간 또는 자원 사용량(CPU, I/O 자원량)을 의미 - 테이블,인덱스,칼럼 등 다양한 객체 통계정보와 시스템 통계정보 등을 이용 - 인덱스 스캔보다 전체 테이블 스캔이 비용 낮다고 판단하면 적절한 인덱스가 존재해도   전체 테이블 스캔 수행
실행계획	- SQL에서 요구한 사항을 처리하기 위한 절차와 방법을 의미(예상 정보) - 실행계획을 구성하는 요소에는 조인 순서, 조인 기법, 액세스 기법, 최적화 정보, 연산,질의 처리 예상 비용(Cost) 등이 있음 (실제 처리 건수X) - 동일 SQL문에 대해 실행 계획이 달라도 결과가 달라지지 않음. 성능이 차이날 수 있음 - 최적화 정보는 실행 계획의 단계별 예상 비용을 표시 - 위에서 아래로, 안에서 밖으로 읽는다. - SQL이 데이터베이스에서 실행될 때 실행 절차 및 방법을 표현하며 dba에게 알려줌 - 옵티마이저의 종류를 확인할 수 있는 RULE, COST가 표현되고 SQL이 내부적으로 어떤 방식으로 실행되었는지 확인 가능 - SQL 개발자가 SQL을 작성하여 실행할 때, SQL을 어떻게 실행할 것인지 계획
SQL 처리 흐름도	- 실행 계획/SQL 처리 절차 시각화 - 인덱스 스캔/전체 테이블 스캔 등의 엑세스 기법 표현 - 성능적인 표현 가능 (실행 시간을 알 수 없음)

 

인덱스 기본
인덱스	- 원하는 데이터를 쉽게 찾을 수 있도록 돕는 책의 찾아보기와 유사한 개념 - 검색 성능의 최적화를 목적으로 두고 있지만 느려질 수 있다는 단점이 존재 (인덱스를 생성하면 검색 속도가 향상되지만 데이터의 분포에 따라서 반드시 향상되는 건 아님. 테이블에 대해 입력, 수정, 삭제 작업 시 테이블의 데이터와 함게 인덱스도 변경되므로 속도가 드려짐. 따라서 대용량 데이터를 적재하는 작업에서는 인덱스로 인하여 속도가 떨어질 수 있음) - 인덱스는 조회만을 위한 오브젝트이며, 삽입, 삭제, 갱신의 경우 오히려 부하 가중   (인덱스는 INSERT, DELETE 작업과는 다르게 UPDATE 작업에 부하 없을 수도 있음) - 기본은 오름차순, 내림차순 정렬된 상태로도 생성 가능 - SQL Server : NULL값을 인덱스 맨 앞에, 인덱스 구성 컬럼이 모두 null인 레코드도 인덱스에 저장   ORACLE : Null값을 인덱스 맨 뒤에, 인덱스 구성 컬럼이 모두 null인 레코드도 인덱스에 저장X, 하나라도 null이 아닌 레코드는 인덱스에 저장 - 기본 인덱스는 UNIQUE&NOT NULL / 보조 인덱스는 중복 데이터 입력 가능 - 자주 변경되는 속성으로 선정할 경우 UPDATE, DELETE 성능에 좋지 않은 영향을 미치므로 좋은 인덱스 후보는 아님 - 인덱스 스캔하여 테이블로 데이터를 찾아가는 방식 = 랜덤 엑세스(부하가 큼) - 테이블의 전체 데이터를 읽는 경우 인덱스를 사용하지 않는 FTS를 사용 - 인덱스가 존재하는 상황에 데이터를 입력하면, 매번 인덱스 정렬이 일어나 데이터 마이그레이션 같이 대량의 데이터를 삽입할 때는 모든 인덱스를 제거하고, 데이터 삽입이 끝난 후 인덱스를 다시 생성하는 것이 좋다. - 인덱스를 활용하여 데이터 조회 시 인덱스를 구성하는 컬럼들의 순서는 SQL 실행 성능과 관계 있음 - VARCHAR,CHAR, DATE, NUMBER 모두 인덱스 생성이 가능 - 인덱스의 수가 증가하면 입력과 삭제, 수정 속도가 저하 - 파티션 키에 대해 생성한 인덱스를 로컬 파티션 인덱스, 파티션 키와 다른 컬럼으로 생성한 인덱스를 글로벌 파티션 인덱스라고 함 - Index 사용할 수 없는 조건)   (1) NVL(Key, 0) 사용    (2) 컬럼 형변환        (ex. to_char(컬럼명, 'yyyymmdd') = sysdate)        (컬럼명 = to_char(sysdate, 'yyyy'))   (3)인덱스 변경       (ex. '||' 로 특정 문자열을 결합하여 인덱스 변경됨)       (ex. CREATE INDES idx1 ON TABLE(col1, col2);             SELECT col3 FROM TABLE WHERE col1-col2 > 50;          → col1과 col2에 대해서 조작이 발생하여 인덱스 사용 불가)
인덱스 생성	CREATE INDEX IND_A ON 테이블명(A DESC); IOT(Index-Organized Table)  - 테이블을 탐색하지 않고 Fetch  - 테이블을 참조하지 않는 인덱스를 생성  - 인덱스의 Key가 Fetch하는추출의 컬럼으로 이루어진 인덱스  - SQL Server의 클러스터형 인덱스는 ORACLE의 IOT와 매우 유사  - FULL SCAN 시 TABLE FULL SCAN이 아닌 INDEX FAST FULL SCAN (CREATE TABLE 테이블명 (컬럼명1 VARCHAR(5) PRIMARY KEY,                                             컬럼명2 VARCHAR(10)) ORGANIZATION INDEX;
인덱스 튜닝	- 인덱스를 경유한 테이블의 random 엑세스 부하가 심할 떄,   클러스터 테이블이나 IOT를 활용하는 방안을 고려할 수 있음 - 인덱스를 경유한 테이블 엑세스 횟수가 같더라도   인덱스 구성에 따라 스캔 효율이 달라짐 - 인덱스 스캔 효율을 높이기 위해 인덱스 컬럼 순서를 바꿔야 할 때가 있음 - WHERE A=10 AND B='o' 인덱스를 [A+B] 순으로 구성하나   [B+A] 순으로 구성하나 인덱스 스캔 효율 차이 없음 - 인덱스 컬럼 순서를 아무리 바꾸어도 테이블 random 엑세스 횟수가 줄지 않음
B(Balance)-TREE 인덱스	- 루트노트, 브랜치 노드, 리프 노드로 구성 - 관계형 데이터베이스에서 가장 많이 사용되는 인덱스 - EQUAL조건 뿐만 아니라 BETWEEN > 과 같은 연산자로 검색하는 범위 검색에도 사용 - 분기를 수행하는 브랜치 블록, 인덱스를 구성하는 컬럼값을 가지는 리프 블록 - 일반적으로 OLTP 환경에서 많이 사용 (B-TREE 인덱스에서 원하는 값을 찾는 과정) 1. 브랜치 블록의 가장 왼쪽 값이 찾고자 하는 값보다 작거나 같으면 왼쪽 포인터로 이동 2. 찾고자 하는 값이 브랜치 블록의 값 사이에 존재하면 가운데 포인터로 이동 3. 오른쪽에 있는 값보다 크면 오른쪽 포인터로 이동
BITMAP 인덱스	- 인덱스 내부 구조에 해당 데이터가 있으면 1, 없으면 0으로 저장 - 정보계와 같은 DW에서 사용하고 많은 양의 데이터를 검색할 때 사용 - 시스템에서 사용될 질의를 시스템 구현 시 모두 알 수 없는 경우인 DW 및 AD-hoc 질의 환경을 위해 설계 - 하나의 인덱스 키 엔트리가 많은 행에 대한 포인터를 저장하고 있는 구조
CLUSTERED 인덱스	- 인덱스를 생성하면 물리적으로 정렬되기 때문에 빠르게 연속적인 데이터 블록을 검색할 수 있음 - 인덱스의 리프 페이지가 곧 데이터 페이지이며, 리프 페이지의 모든 데이터는 인덱스 키 컬럼 순으로 물리적으로 정렬되어 저장
전체 테이블 스캔	테이블에 존재하는 모든 데이터를 읽어 가면서 조건에 맞으면 결과로서 추출하고 조건에 맞지 않으면 버리는 방식으로 검색 전체 테이블 스캔을 하는 경우) 1. SQL문에 조건이 존재하지 않는 경우 2. SQL문의 주어진 조건에 사용 가능한 인덱스가 존재하지 않는 경우 3. 옵티마이저의 취사 선택 4. 병렬처리 방식으로 처리하는 경우  5. 하나의 데이터를 읽기 위해 인덱스를 사용X
인덱스 스캔 : 인덱스를 구성하는 칼럼의 값을 기반으로 데이터를 추출하는 액세스 기법
인덱스 유일 스캔 (INDEX UNIQUE  SCAN)	- 유일 인덱스를 사용하여 단 하나의 데이터를 추출하는 방식 - 중복X, 구성 칼럼에 대해 모두 ‘=’ 로 값이 주어진 경우에만 가능 - 만약 기본키가 2개로 이루어져있으면 UNIQUE INDEX SCAN 시 KEY1번과 KEY2번 모두 사용되어야 함
인덱스 범위 스캔 (INDEX RANGE SCAN)	- 인덱스를 이용하여 한 건 이상의 데이터를 추출하는 방식 - 결과가 없으면 한 건도 반환하지 않을 수 있음 -  'Like문'은 Index Range Scan이 불가능하여 인덱스를 효율적으로 엑세스 할 수 없음
인덱스 역순 범위 스캔 (INDEX RANGE SCAN DESCENDING)	인덱스의 리프 블록의 양방향 링크를 이용하여 내림차순으로 데이터를 읽는다

 

조인 수행 원리
Nested Loop Join	- 프로그래밍에서 사용하는 중첩된 반복문과 유사한 방식으로 조인을 수행 - 랜덤 액세스 방식으로 데이터를 읽음 - 선행 테이블의 조건을 만족하는 건수만큼 반복 수행 (FROM절 나열된 모든 테이블이 동시 조인 작업X) - 절차) - 조인 컬럼에 적당한 인덱스가 있어 Natural Join이 효율적일 때 유용 - 대용량 데이터 조인할 후행 테이블에 인덱스가 없을 경우 사용하면 안됨. 이런 경우 Hash Join, Sort Merge, Full Scan을 사용 - Driving table의 조인 데이터의 양이 큰 영향을 주는 조인 방식 - 데이터를 집계하는 업무보다는 유니크 인덱스를 활용하여 수행시간이 적게 걸리는 소량 테이블을 온라인 조회, 즉 OLTP 목록 처리 업무에 많이 사용 - OLTP 시스템에서 데이터를 조인할 때 먼저 나오는 테이블의 선택도가 낮은 테이블을 참조하는 것이 유리  (1) 선행 테이블에서 조건을 만족하는 첫 번째 행을 찾음  (2) 선행 테이블의 조인 키를 가지고 후행 테이블에 조인 키가 존재하는지 찾으러 가서 조인을 시도  (3) 후행 테이블의 인덱스에 선행 테이블의 조인 키가 존재하는 지 확인  (4) 인덱스에서 추출한 레코드 식별자를 이용하여 후행 테이블을 엑세스
Sort Merge Join	- 조인되는 N개의 테이블을 조인 칼럼을 기준으로 모두 정렬하여 조인을 수행 - 스캔 방식으로 데이터 읽음 - Equal Join / Not Equal Join에서도 사용 가능 - DW 등의 데이터 집계 업무에서 사용 - Merge가 완료되면 한 번의 Full Scan으로 데이터를 검색 - 조인 컬럼에 적당한 인덱스가 없어 NL조인이 비효율적일 때 사용 - 조인 조건의 인덱스 유무에 영향 받지 않음 - 정렬할 데이터가 많아 메모리에서 모든 정렬 작업을 수행하기 어려운 경우 임시 영역(디스크)을 사용하기 때문에 성능이 떨어질 수 있음 - 정렬된 결과들을 통해 조인작업이 수행되며 조인에 성공하면 추출 버퍼에 넣는 작업을 수행함 - 기본키와 외래키 관계에서 외래키에 인덱스가 없을 때 NL Join보다 효율적
Hash Join	- CPU 작업 위주로 처리, 해슁 기법 이용 - CPU 연산이 많이 발생 - 랜덤 엑세스는 발생 X - DW 등의 데이터 집계 업무에서 사용 - NL Join의 랜덤 액세스 문제와 SMJ의 정렬 작업 부담을 해결하기 위한 대안으로 등장 - SMJ 수행하기에 두 테이블이 너무 커서 Sort 부하가 심할 때 유용, 정렬이 부담되는 대량 배치작업에 유용    (SMJ보다 일반적으로 더 우수한 성능을 모이지만 조인 대상 테이블이 해당 조인키 컬럼을 정렬되어 있을 때는     SMJ가 더 우수한 성능을 낼 수 있음) - 작은 테이블을 선행 테이블로 사용하는 것이 성능 관점에서 좋음 - 해시 함수를 사용해서 주소 계산하고 조인 수행 - 조인 컬럼에 인덱스가 없어도 사용 가능 - 해시함수를 사용하기 때문에 EQUAL JOIN('='으로 수행)에만 사용 가능(NON-EQUAL JOIN 사용 못함) - (1) 선행 테이블에서 조건에 만족하는 데이터에 대해 필터링 수행 - (2) 선행 테이블의 조인 키를 해시 함수의 입력으로 넣고 해시 테이블을 생성 - (3) (1), (2) 작업은 선행 테이블에서 조건을 만족하는 모든 행에 대해 실행 - (4) 후행 테이블에 조건 있으면 데이터에 대해 필터링 - (5) 후행테이블의 조인 키를 해시 함수의 입력에 넣어서 해시값을 생성, 선행 테이블의 해시값과 비교해 검색

 

그 외

[PL/SQL 사용 순서]

- Cursor 선언 → Cursor Open → Fetch → Cursor Close

- DECLARE CURSOR <커서명> IS SELECT <컬럼명> FROM <테이블명>

- OPEN <커서명>

- FETCH <커서명> INTO 변수1, 변수2, ...;

- CLOSE <커서명>