[2과목] - SQL 최적화 기본 원리

CBO(Cost Based Optimizer) - 비용기반 옵티마이저

테이블 및 인덱스 등의 통계 정보를 활용하여 SQL문을 실행하는데 소요될 처리시간 및 CPU, I/O 자원량 등을 계산하여 가장 효율적일 것으로 예상되는 실행계획을 선택하는 옵티마이저

---

실행계획을 통해서 알 수 있는 정보

• 액세스 기법
• 질의 처리 예상 비용(Cost)
• 조인 방법
• 조인 순서

+) 실행계획을 읽는 순서는 위에서 아래로, 안에서 밖으로 읽는다.

+) 실행계획은 SQL 처리를 위한 실행 절차와 방법을 표현한 것이다.

+) CBO의 실행계획에는 단계별 예상 비용 및 건수 등이 표시된다..

 

cf) 실행계획은 예상 정보이다. 실제 처리 건수는 트레이스 정보를 통해서 알 수 있다.

cf) 실행계획 즉, 실행방법이 달라진다고 해서 동일 SQL문의 결과가 달라지지는 않는다.

 

---

 

SQL 처리 흐름도

SQL 실행계획을 시각화해서 표현한 것으로, 인덱스 스캔, 테이블 전체 스캔 등과 같은 액세스 기법을 표현할 수 있으며, 성능적인 측면도 표현할 수 있다.

+) SQL의 내부적인 처리 절차를 시각적으로 표현해준다.

 

 

cf) SQL 처리 흐름도만 보고 실행 시간을 알 수는 없다.

 

옵티마이저 & 실행계획

+) 규칙기반 옵티마이저에서 제일 낮은 우선순위는 전체 테이블 스캔이고, 제일 높은 우선순위는 ROWID(행에 대한 고유 주소)하는 방법이다.

+) 인덱스 범위 스캔은 결과 건수만큼 반환하지만, 결과가 없으면 한 건도 반환하지 않을 수 있다.

+) ORACLE의 규칙기반 옵티마이저에서 가장 우선 순위가 높은 규칙은 Single row by rowid 액세스 기법이다.

+) 비용기반 옵티마이저는 테이블, 인덱스, 컬럼 등 객체의 통계정보를 사용하여 실행계획을 수립하므로 통계정보가 변경되면 SQL의 실행계획이 달라질 수 있다.

+) ORACLE의 실행계획에 나타나는 기본적인 Join 기법으로는 NL Join, Hash Join, Sort Merge Join 등이 있다.

+) NL Join은 OLTP의 목록 처리 업무에 많이 사용된다.

+) Hash Join 또는 Sorte Merge Join은 DW 등의 데이터 집계 업무에서 많이 사용된다.

 

---

 

인덱스(Index)

기본 인덱스(PK)는 UNIQUE & NOT NULL의 제약조건을 가진다.

보조 인덱스는 UNIQUE 인덱스가 아니라면 중복 데이터의 입력이 가능하며, 자주 변경되는 속성을 인덱스로 선정할 경우 UPDATE, DELETE 성능에 좋지 않은 영향을 미치므로 인덱스 후보로 적절하지 않다.

 

+) 테이블의 전체 데이터를 읽는 경우는 인덱스를 사용하지 않는 FTS를 사용한다.(= 테이블의 전체 데이터를 읽는 경우는 인덱스가 거의 불필요하다.)

+) 인덱스는 조회만을 위한 오브젝트이며, 삽입, 삭제, 갱신의 경우 오히려 부하를 가중한다.

+) Balance Tree(B 트리)는 관계형 데이터베이스에서 가장 많이 사용되는 인덱스이다.

+) 인덱스가 존재하는 상황에서 데이터를 입력하면, 매번 인덱스 정렬이 일어나므로 데이터 마이그레이션 같이 대량의 데이터를 삽입할 때는 모든 인덱스를 제거하고, 데이터 삽입이 끝난 후에 인덱스를 다시 생성하는 것이 좋다.

+) 규칙기반 옵티마이저는 적절한 인덱스가 존재하면 항상 인덱스를 사용하려고 한다.

+) 인덱스 범위 스캔은 결과가 없으면 한 건도 반환하지 않을 수 있다.

+) SQL Server의 클러스터형 인덱스는 ORACLE의 IOT와 매우 유사하다.

+) 인덱스는 INSERT와 DELETE 작업과는 다르게 UPDATE 작업에는 부하가 없을 수도 있다.(= 인덱스를 구성하는 컬럼 이외의 데이터가 UPDATE될 때는 인덱스로 인한 부하가 발생하지 않는다.)

+) 인덱스를 구성하는 컬럼들의 순서는 데이터 조회 시 성능적인 관점에서 매우 중요한 역할을 한다.

 

 

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

 

 

---

 

Sort Merge Join(SMJ)

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

 

Nested Loop Join

• 조인 칼럼에 적당한 인덱스가 있어서 자연조인(Natural Join)이 효율적일 때 유용하다.
• Driving Table의 조인 데이터 양이 큰 영향을 주는 조인 방식이다.
• 유니크 인덱스를 활용하여 수행시간이 적게 걸리는 소량 테이블을 온라인 조회하는 경우 유용하다.
• 선택도가 낮은(결과 행의 수가 적은) 테이블이 선행 테이블로 선택되는 것이 일반적으로 유리하다.

 

Hash Join

• Hash Join은 조인 칼럼의 인덱스를 사용하지 않기 때문에 조인 칼럼의 인덱스가 존재하지 않을 경우에도 사용할 수 있는 조인 기법이다.
• 해쉬 함수를 이용하여 조인을 수행하기 때문에 '='로 수행하는 조인 즉, 동등 조인에서만 사용할 수 있다.
• 해싱(Hashing) 기법을 이용한 조인으로 한쪽 테이블이 주 메모리의 기용 메모리에 담길 정도로 충분히 작고 해시 키 속성에 중복 값이 적을 때 효과적이다.
• EQUI JOIN에서만 동작하는 Join 방식이다.
• 조인 컬럼에 적당한 인덱스가 없어서 자연조인(Natural Join)이 비효율적일 때 유용하다.
• 자연조인(Natural Join)시 드라이빙(driving) 집합 쪽으로 조인 액세스 량이 많아 Random 액세스 부하가 심할 때 유용하다.
• Sort Merge Join을 하기에는 두 테이블이 너무 커서 소트(Sort) 부하가 심할 때 유용하다.
• 행의 수가 작은 테이블을 선행 테이블로 선택하는 것이 유리하다.

+) Hash Join은 Sort Merge Join보다 일반적으로 더 우수한 성능을 보이지만, Join 대상 테이블이 Join Key 컬럼으로 정렬되어 있을 때는 Sort Merge Join이 더 우수한 성능을 낼 수도 있다.

+) EXISTS 절은 실행계획상에 주로 SEMI JOIN으로 나타난다.