[SQLD] 2과목. SQL 기본 및 활용 - 3

03. SQL 최적화 기본 원리

옵티마이저와 실행계획

옵티마이저

다양한 실행 방법들 중 최적의 실행방법을 결정하는 것

 

옵티마이저 종류

• 규칙 기반 옵티마이저(Rule Based Optimizer, RBO)
  • 제일 낮은 우선순위 : 전체 테이블 스캔
  • 제일 높은 우선순위 : 행에 대한 고유 주소(ROWID)를 활용해 테이블 액세스 하는 방법
  • 적절한 인덱스가 존재하면 항상 인덱스를 사용하려고 함
• 비용 기반 옵티마이저(Cost Based Optimizer, CBO)
  • 테이블 및 인덱스 등의 통계정보를 활용해 SQL문을 실행하는 데 소요될 처리시간 및 CPU, I/O 자원량 등을 계산해 가장 효율적일 것으로 예상되는 실행계획을 선택하는 옵티마이저
  • 통계정보가 변경되면 SQL 실행계획이 달라질 수 있음
  • 인덱스가 존재하더라도 전체 테이블 스캔이 유리할 수 도 있음

 

옵티마이저 실행계획

• SQL 처리를 위한 실행 절차와 방법을 표현한 것
• 동일 SQL문에 대해 실행계획이 다르더라도 실행 결과는 달라지지 않음
• CBO의 실행계획에는 단계별 예상 비용 및 건수 등이 표시됨
• 구성요소 : 조인기법, 연산, 액세스 기법, 최적화 정보...
• 실행계획을 통해 알 수 있는 정보 : 액세스 기법, 질의 처리 예상 비용(Cost), 조인 순서...
• 실행계획 읽는 순서 : 위에서 아래로, 안에서 밖으로

 

SQL 처리 흐름도

• SQL의 처리 흐름도만 보고  실행 시간 알 수 없음
• SQL 실행계획을 시각화해서 표현한 것(SQL 내부적인 처리 절차를 시각적으로 표현)
• 인덱스 스캔, 테이블 전체 스캔 등과 같은 액세스 기법이 표현
• 성능적인 측면도 표현 가능
• 인덱스 범위 스캔은 결과 건수만큼 반환하지만 결과가 없으면 한 건도 반환하지 않음

 

인덱스 기본

인덱스

• 기본 인덱스(PK Index)는 UNIQUE & NOT NULL의 제약조건을 가짐
• 보조 인덱스(Secondary Index)는 UNIQUE인덱스가 아니라면 중복 데이터 입력이 가능
• 자주 변경되는 속성은 인덱스 후보로 좋지 않음(UPDATE, DELETE 성능에 좋지 않은 영향을 미치기 때문)
• 테이블의 전체 데이터를 읽는 경우는 인덱스를 사용하지 않는 FTS 사용
• 인덱스는 조회 성능을 최적화하기 위해 사용하는 것이며, 삽입, 삭제, 갱신의 경우 오히려 부하 가중
• 단, UPDATE의 경우 인덱스를 구성하는 칼럼 이외의 데이터가 갱신될 때는 인덱스로 인한 부하 X
• Balance Tree는 관계형 DB에서 가장 많이 사용되는 인덱스
• 데이터 마이그레이션 같이 대량의 데이터를 삽입할 때는 모든 인덱스를 제거하고, 데이터 삽입이 끝난 후에 인덱스 다시 생성하는 것이 좋음
• 인덱스 구성하는 칼럼의 순서는 데이터 조회 시 성능적인 관점에서 매우 중요한 역할
• 인덱스 생성할 때 정렬 순서를 내림차순으로 설정하면 내림차순으로 정렬됨(항상 오름차순 X)
• 많은 양의 데이터를 읽을 경우에는 인덱스 스캔보다 테이블 전체 스캔이 유리할 수 있음

 

인덱스 종류

B-Tree	브랜치 블록(분기를 목적)과 리프 블록(인덱스를 구성하는 컬럼의 값으로 정렬됨)으로 구성 일치 및 범위 검색에 적절한 구조. 일반적으로 OLTP시스템 환경에서 가장 많이 사용
CLUSTERED	인덱스의 리프 페이지가 곧 데이터 페이지며, 리프 페이지의 모든 데이터는 인덱스 키 컬럼 순으로 물리적으로 정렬되어 저장됨
BITMAP	시스템에서 사용될 질의를 시스템 구현 시에 모두 알 수 없는 경우인 DW 및 AD-HOC 질의 환경을 위해 설계 하나의 인덱스 키 엔트리가 많은 행에 대한 포인터를 저장하고 있는 구조

• SQL Server의 클러스터형 인덱스는 ORACLE의 IOT와 매우 유사

 

조인 수행 원리

JOIN

 

NL Join(Nested Loop Join)

• OLTP의 목록 처리 업무에 많이 사용
• 조인 칼럼에 적당한 인덱스가 있어 자연 조인이 효율적일 때 유용
• Driving Table의 조인 데이터 양이 큰 영향을 주는 조인 방식
• 유니크 인덱스를 활용해 수행 시간이 적게 걸리는 소량 테이블을 온라인 조회하는 경우 유용
• 랜덤 액세스 방식으로 데이터 읽음
• 선택도가 낮은(결과 행의 수가 적은) 테이블이 선행 테이블로 선택되는 것이 일반적으로 유리

 

Hash Join

• DW 등의 데이터 집계업무에서 많이 사용
• 조인 칼럼에 적당한 인덱스가 없어서 NL Join이 비효율 적일 때 유용
• 자연 조인 시 드라이빙 집합 쪽으로 조인 액세스량이 많아 Random 액세스 부하가 심할 때 유용
• Sort Merge Join을 수행하기에 두 테이블이 너무 커 Sort 부하가 심할 경우 Hash Join이 유용
• 조인 칼럼의 인덱스를 사용하지 않기 때문에 인덱스가 존재하지 않아도 사용 가능
• 해쉬 함수를 이용해 조인을 수행하므로 '='로 수행하는 조인(동등 조인, EQUI JOIN)에서만 사용 가능
• 결과 행의 수가 적은 테이블을 선형 테이블로 사용하는 것이 성능에 유리
• Sort Merge Join보다 일반적으로 더 우수한 성능을 보이지만, Join대상 테이블이 Join Key칼럼으로 정렬되어 있을 때는 Sort Merge Join이 더 우수한 성능을 낼 수 있음

 

SMJ(Sort Merge Join)

• DW 등의 데이터 집계업무에서 많이 사용
• 조인 칼럼을 기준으로 데이터를 정렬하여 조인 수행
• 스캔 방식으로 데이터 읽음
• Driving Table 개념이 중요하지 않음
• 조인 조건의 인덱스 유무에 영향받지 않음
• 조인 칼럼에 적당한 인덱스가 없어서 NL Join이 비효율적일 때 사용
• 랜덤 액세스로 NL Join에서 부담이 되던 넓은 범위의 데이터 처리할 때 이용되던 조인 기법
• 그러나 정렬할 데이터가 많아 메모리에서 모든 정렬 작업을 수행하기 어려운 경우에는 임시 영역(디스크)을 사용하기 때문에 성능이 떨어질 수 있음

 

EXISTS 절은 실행계획상에 주로 SEMI JOIN으로 나타남
- NESTED LOOP SEMI JOIN

 

 

---

References

• SQL 자격검정 실전문제, 한국데이터산업진흥원