📔설명
SQLD 2과목 3챕터의 SQL 최적화 기본 원리 요점 정리
👉🏻옵티마이저와 실행계획
비용기반 옵티마이저(CBO)
CBO : 테이블 및 인덱스 등의 통계 정보를 활용하여 SQL문을 실행하는데 소요될 처리시간 및 CPU, I/O 자원량 등을 계산하여 가장 효율적일 것으로 예상되는 실행계획 선택하는 옵티마이저
- 실행계획에 단계별
예상 비용및건수등이 표시 - 인덱스가 존재하더라도
전체 테이블 스캔이 비용이 덜 들면 그것이 유리하다고 판단 - 테이블, 인덱스, 컬럼 등
객체의 통계정보를 사용하여 실행계획을 수립하므로,통계정보가 변경되면SQL의 실행계획이 달라질 수 있음
규칙기반 옵티마이저
- 제일
높은우선순위 :행에 대한 고유 주소 사용 - 제일
낮은우선순위 : 테이블전체스캔 - 적절한 인덱스가 존재하면 항상 인덱스를 사용하려고 함
(전체 테이블 스캔보다는..)
실행계획
실행계획 : SQL 처리를 위한 실행 절차와 방법 표현
실행계획을 통해 알 수 있는 정보
- 액세스 기법
- 질의 처리 예상 비용(Cost)
- 조인 순서(위->아래, 안->밖)
- 연산
- 조인 방법
SQL 처리 흐름도(Access Flow Diagram)
- SQL 실행 시간은 알 수 없음
- 인덱스 스캔, 테이블 전체 스캔과 같은
액세스 기법표현 - SQL의 내부적인 처리 절차를 시각적으로 표현
- 실행계획과 관련
- 성능적인 측면의 표현 가능
인덱스 범위 스캔
- 결과가 없으면 한 건도 반환 안 할 수 있음
📑인덱스(Index)
- 기본 인덱스에
중복된 키 값 X - 기본 인덱스에
null값 나타날 수 없음 - 보조 인덱스에는 중복 가능
- 자주 변경되는 속성은 인덱스 정의시 좋지 않음
(update, delete) 성능에 좋지 않음 - 테이블 전체 읽는 경우
FTS(Full-Text Search)사용
(인덱스 거의 불필요) - 인덱스는
조회성능 향상/삽입,삭제,갱신성능 저하 B-tree는 관계형 DB의 주요 인덱스 구조- 대량의 데이터 삽입 시, 모든 인덱스를
제거하고 데이터 입력
(그렇지 않으면 데이터 입력시마다 매번 인덱스 정렬) - 인덱스는 내림차순 정렬시 내림차순으로 정렬 가능
- 매우 많은 양 읽을 때는 테이블 스캔이 유리
SQL Server의클러스터형 인덱스는Oracle의IOT(Index-Organized Table)와 매우 유사- 인덱스 구성 컬럼 이외의 데이터가
update시,update작업에는 부하 없음 ( INSERT, DELETE와는 다름) - 인덱스 활용 데이터 조회 시
인덱스를 구성하는 컬럼들의 순서는 SQL 실행성능과 매우 관계
B-TREE 인덱스
B-TREE 인덱스 : 브랜치 블록과 리프 블록으로 구성, 브랜치 블록은 분기를 목적으로 하고 리프 블록은 인덱스를 구성하는 컬럼의 값으로 정렬. 일반적으로 OLTP(온라인 트랜잭션 처리)시스템 환경에서 가장 많이 사용
- 일반적으로 테이블 내의 데이터 중
10% 이하만 검색시 유리 일치및범위 검색에 적절한 구조
CLUSTERED 인덱스
CLUSTERED 인덱스 : 인덱스의 리프 페이지가 곧 데이터 페이지이며, 리프페이지의 모든 데이터는 인덱스 키 컬럼 순으로 물리적으로 정렬되어 저장
BITMAP 인덱스
BITMAP 인덱스 : 시스템에서 사용될 질의를 시스템 구현 시에 모두 알 수 없는 경우인 DW 및 AD-HOC 질의 환경을 위해 설계. 하나의 인덱스 키 엔트리가 많은 행에 대한 포인터를 저장하고 있는 구조
INDEX SCAN
UNIQUE SCAN: 유일한 값 하나 찾기(ex.고객아이디)RANGE SCAN: 어떠한 조건에서 범위 찾기SKIP SCAN: 결합 인덱스의 선행 컬럼에 대한 조건이 없고, 후행 컬럼에 대한 조건이 있는 경우FAST FULL SCAN: select절과 조건절에 사용된 모든 컬럼이 인덱스 컬럼으로 인덱스 블록만 스캔하여 원하는 데이터 검색FULL SCAN: 전체 데이터
🔗Join(조인) 수행
오라클의 실행계획에 나타나는 기본적인 Join 기법으로는 NL Join, Hash Join, Sort Merge Join 등
- SQL문의
EXISTS:SEMI JOIN으로 표현
Nested Loop Join (NL Join)
OLTP의 목록 처리 업무에 사용조인 칼럼에 적당한인덱스가 있어자연조인이 효율적일 때 유용Driving Table의 조인데이터 양이 큰 영향을 주는 조인 방식
(Driving Table: Join시 먼저 액세스돼서 Access Path를 주도하는 테이블)유니크 인덱스를 활용하여수행시간이 적게걸리는소량테이블을온라인 조회하는 경우 유용- 선택도가 낮은(
결과 행의 수가 적은)테이블이 선행 테이블로 선택되는 것이 유리 랜덤 액세스 방식으로 데이터를 읽음- 실행 속도 : 선행 테이블 사이즈 * 후행 테이블 접근 횟수
Single Loop Join
조인 칼럼에 적당한인덱스가 존재할 경우 사용선행 테이블의 각 레코드들에 대해후행 테이블의 인덱스 접근 구조를 사용하여직접 검색 후조인
Sort Merge Join (SMJ)
Sort Merge Join : 조인 컬럼 기준으로 데이터를 정렬하여 조인 수행
DW등에서 데이터 집계하는 업무에 많이 사용조인 컬럼에 적당한 인덱스가없어서NL조인이 비효율적일 때사용Driving Table의 개념이중요하지 않은조인 방식조인 조건의 인덱스 유무에 영향 받지 XEQUI(=),NONEQUI(between, >, < 등)에서 동작- 조인 연결고리의 비교 연산자가
범위 연산인 경우 Nested Loop보다 유리 Join대상이 Join Key 컬럼으로 정렬되어있을 때는 Sort Merge Join이 Hash Join보다 우수한 성능을 보여줄 수 있음- 랜덤 액세스로 NL Join에서 부담되는 넓은 범위의 데이터를 처리할 때 이용. 그러나 정렬 데이터가 많아
메모리에서 모든 정렬 작업 수행이 어려운 경우 임시 영역(디스크)를 사용하므로 성능이 떨어질 수 있음
Hash Join
DW등에서 데이터 집계하는 업무에 많이 사용Sort Merge Join하기에두 테이블이 너무 커서Sort 부하가 심할 때 유용EQUI(=)조인 조건에서만 동작조인 컬럼에 적당한 인덱스가없어서NL조인이 비효율적일 때사용NL 조인시Driving 집합쪽으로 조인 액세스량이 많아Random 액세스 부하가 심할 때 사용- 결과 행의 수가
작은테이블을 선행 테이블로 사용하는 것이 유리 - 조인 칼럼의 인덱스를 사용하지 않으므로,
조인 칼럼 인덱스가 없어도 사용할 수 있는 조인 해쉬 함수를 이용하여 조인을 수행메모리의 지나친 사용으로오버헤드 발생 가능성
해시 조인 순서
- 선행 테이블에서 주어진 조건을 만족하는 레코드를 필터링
- 선행 테이블의 조인 키를 기준으로 해시 함수를 적용하여 해시 테이블 생성
- 1번, 2번 작업을 선행 테이블에서 조건을 만족하는 모든 행을 수행
- 후행 테이블에서 주어진 조건을 만족하는 레코드를 필터링
- 후행 테이블의 조인 키를 기준으로 해시 함수를 적용하여 선행 테이블에서 해시 함수 반환값과 같은 값을 반환하는 해당 버킷 찾기
MySQL DBA 신입