실무로 배우는 시스템 성능 최적화 - DB

- 파티션 : 한 테이블을 물리적인 여러 테이블로 구성해 분산 저장하는 기술

- 샤딩 : 여러 DB에 분산 저장함

- 인메모리 데이터 그리드 : KEY , VALUE 형식의 데이터 캐시 솔루션이다 DBMS에 저장된 데이터나 사용자 서비스

호출간에 공유할 데이터를 캐시해서 성능을 개선할 목적으로 사용한다.

- 내포(Nested Loops Join) 조인 : 인덱스 기반으로 단 건에서 수 백 건 이내의 소량 레코드를 조회하는

온라인 애플리케이션에서 사용하기에 최적화된 조인 방식이다. 

선행 테이블에서 탐색된 레코드 건수 만큼 후행 테이블의 랜덤 엑세스 탐색이 일어난다. -> (탐색이 레코드 건수가 작은 테이블이 선행 테이블이 되는 것이 유리하다. 그리고 선행 테이블과 연결고리가 되는 것이 유리하다. 그리고 선행 테이블과 연결고리가 되는 후행 테이블의 조인 항목은 꼭 인덱스가 있어야 한다. 만약 연결고리에 인덱스가 없는데 내포조인이 사용되면 후행 테이블에 Full Scan이 발생한다.)

- 옵티마이저 : SQL을 가장 빠르고 효율적으로 수행할 최적(최저비용)의 처리 경로를 생성해주는 DBMS 내부의 엔진이다.

- BASE 옵티마이저의 우선순위 : 

1. ROWID를 통한 Table Access

2. 유니크 인덱스(primary key)를 통한 Table Access

3. 일반 인덱스를 통한 Table Access

4. Full Scan을 통한 Table Access

- 병합조인 : 조인되는 두 테이블의 연결고리가 되는 항목에 대한 인덱스가 없거나 대량 데이터를 처리하는 경우

내포 조인에서 발생하는 후행 테이블의 랜덤 엑세스를 제거하기 위해 사용하는 조인 방식이다.

- 해시조인 : 조인 대상이 되는 두 테이블을 모두 한번씩만 읽도록 개선하고 해시 연산으로 안정적인 성능을 확보해준다.

- 세미조인 : IN 안에 서브쿼리를 사용하여 조인하는 방식인데 조회 결과에는 서브쿼리에 있는 테이블의 항목이 포함 될 수 없으며 일반 조인과 다르게 조건에 맞는 레코드를 찾으면 탐색을 멈춘다. 즉 1건과 100건이 같은 의미가 된다

- 안티조인 : 세미조인과 반대로 NOT IN에 사용됨

- 루트,브랜치 블록 : 리프 블록을 찾아가는 최단 경로를 제공

- 리프 블록 : 색인 정보가 들어있는 블록으로, 색인된 항목의 값과 테이블 레코드의 ROWID가 들어 있다.

- 비트맵 인덱스 : 

가) 항목의 값 단위로 해당값의 유무를 1,0의 비트로 표기하는 방식을 이용해 인덱스 크기를 획기적으로 줄여 

탐색 성능을 확보한 인덱스이다.

나) 선택도가 좋은 항목에 대해 생성하면 오히려 용량이 증가하고 성능이 안좋다.

다) 인덱스에 대한 락 제어가 비트 단위가 아닌 블록 단위로 되기 때문에 입력과 변경이 자주 발생하는 테이블에 적용하면 락 대기가     크게 발생하고 교착 상태에 빠질 가능성이 크다

- 리버스 인덱스 :

가) 인덱스를 구성하는 항목의 순서는 유지하면서 각 항목의 값을 바이트 단위로 역순으로 기술해 인덱싱하는 것이다.

나) 순차번호와 순차 값이 비슷한 숫자들이 입력되면 일부 리프 블록에 부하가 집중되고 B트리가 한쪽으로 불균형하게 증가해서 

바로 잡으려면 예상밖의 오버헤드가 발생한다.

- 함수기반 인덱스 : 조회 하고자 하는 조건절에 함수를 사용하면 인덱스를 사용할 수 없다. 하지만 함수기반 인덱스를 사용하면 함수를 사용하면 함수를 사용하여 나온 결과를 가지고 인덱스를 생성해 인덱스 탐색이 가능하게 해준다.

- 역순 인덱스 : 일반 인덱스는 항목 값을 기준으로 오름차순으로 색인한다. 항상 조회시 역순으로 조회한다면 인덱스 부터 역순으로 만들어준다.

- 액세스와 필터 

가) 액세스 : 데이터 블록을 읽기 위해 찾아가는 경로와 범위를 결정하는 조건이다.

나) 필터 : 데이터 블록을 읽어내어 데이터를 걸러내기 위해 사용하는 조건이다.

다) 인덱스 액세스 : 리프 블록을 찾아가는 경로를 결정하는 조건

라) 인덱스 필터 : 리프 블록을 읽어낸 후 인덱스를 구성하는 항목 값으로 ROWID를 읽어낼 대상을 걸러냄

마) 테이블 액세스 : ROWID로 대상 레코드를 찾아가는 한가지 밖에 없다.

바) 테이블 필터 : 데이터 블록을 읽어 항목값을 확인해 걸러낸다.

- 인덱스 생성 원칙

가) 1순위는 테이블의 기본키다. 기본키는 가장 빈도가 높은 테이블 액세스 경로일뿐 아니라

데이터 무결성을 유지하기 위해서도 꼭 필요하다. 테이블에서 기본키만 선언해도 유일 인덱스가 자동으로 만들어진다.

나) 2순위는 테이블 간 연결고리로 사용되는 조인 항목 그룹이다. 온라인 SQL은 대부분 내포조인을 사용하는데 후행 테이블의

연결고리에 인덱스가 없으면 성능에 치명적이다.

다) 인덱스를 만들 때는 선행 항목이 중요하다. 조회 조건에 선행 항목이 없으면 인덱스를 사용하지 못하므로

항상 조회 조건에 나오는 항목이 선행 항목이 돼야한다.

그리고 인덱스를 RANGE SCAN범위를 줄이기 위해서는 분포도가 좋으면서 일치(=)조건으로 조회하는

항목이 선행 항목이 되면 좋다. BETWEEN, <,>등 범위를 지정하는 조건이 주어지는 항목을 결합 인덱스에서

일치(=)조건 항목 이후에 위치하도록 구성해야 인덱스의 탐색범위를 줄일 수 있다.

- 인덱스 범위 탐색 (INDEX RANGE SCAN) : 한개의 항목으로 구성된 

단일 인덱스가 있을때 해당 항목이 where절에 일치 조건으로 있더라도 인덱스 탐색 방식은 범위 탐색을 수행한다.

그 이유는 유일 인덱스가 아니라서 항목 값이 동일한 레코드가 더 있을 수 있어 해당 값이 변경될 때까지 범위 탐색을

수행해야 하기 때문이다.

 

- 인덱스 전체 탐색 (INDEX FULL SCAN) 

 : 인덱스 전체 탐색은 테이블 전체 탐색과 마찬가지로 인덱스를 순차적으로 처음부터 끝까지 읽어들이는 탐색 방식이다.

(전체를 읽어들이지만 Multi-block-IO와 병렬 처리는 되지 않는다.)

- 인덱스 고속 전체 탐색 (INDEX FAST FULL SCAN) 

 : 인덱스 전체 탐색 처럼 전체를 읽어들이지만 Multi-block-IO와 병렬처리를 함으로써 전체 탐색에 비해 속도가 훨씬 빠르다.

- 인덱스 수와 성능

 : 인덱스 수가 증가하면 DML에는 성능 저하가 발생한다. 테이블에 레코드 한건을 넣는 작업량보다. 트리 구조를 지닌 인덱스에 레코드 한건을 넣는 작업량이 훨씬크다.

- Direct-Path  : 데이터 캐시를 경유하지 않고 파일 시스템에서 직접적인 데이터 블록의 IO가 발생한다.

- Conventional-Path 

: 먼저 데이터 캐시를 확인하고, 없으면 파일시스템에서 데이터 블록 IO가 발생한다. 그리고 파일시스템에서 데이터 블록을 읽어오면

먼저 데이터 캐시에 넣은 후 SELECT 프로세싱에 사용한다.

- 배치에서는 대량건을 조회하는 메인 쿼리에 PARALLEL FULL 힌트를 추가해 Direct-Path 방식으로

IO가 발생하게 해서 성능을 개선하는 경우가 종종 있다.

- 로컬 인덱스  

: 파티션 테이블과 동일한 방식과 파티션키로 균등 분할되어 생성된 인덱스로,

물리적인 테이블 수 만큼 인덱스로 생성된다. 따라서 WHERE 조건에 파티션 키가 없이 해당 인덱스를 사용하는 경우 전체 인덱스

수 만큼 액세스해야 하므로 성능 저하가 발생한다.

- 글로벌 인덱스  

: 파티션 키와 수에 상관없이 전체 파티션에 대해 인덱스를 1개 만드는 것으로 where 조건에 파티션키의 포함 여부에 상관없이 1개의 인덱스만 사용하므로 빠른 성능이 보장된다. 하지만 일부 파티션을 삭제 및 통합하는 경우 해당 인덱스가 사용불가 상태로 사용할 수 없게 되어 성능 저하를 유발시킨다.

- 복합적인 기능을 수행하는 쿼리 제거

ex) 

select 주민등록번호, 성명

from 고객정보

where (:입력 주민등록번호 IS NULL OR 주민등록번호 LIKE : 입력 주민등록번호 || '%')

and(:입력성명 IS NULL OR 성명 LIKE : 입력성명 || '%')

위 쿼리의 경우 주민등록번호 인덱스를 사용하도록 실행 계획이 수립됐다고 가정했을때 주민등록 번호가

입력 조건으로 들어오면 빠른 성능이 보장되겠지만 성명이 들어오게 되면 서명이 주민등록 번호 인덱스에 없기 때문에

인덱스와 테이블 전체를 순차적으로 읽어 성명에 일치하는 레코드를 찾는다. 따라서 성명ㅇ이 입력조건으로 들어오게 되면

전체 테이블 탐색보다도 길어지게 된다.

이경우 적절한 인덱스를 선택 할 수 있는 기준으로 쿼리를 분리한다. (UNION ALL)

=> 

SELECT 주민등록번호, 성명

FROM 고객정보

WHERE : 입력주민등록번호 IS NOT NULL (반드시 IS NOT NULL 사용할것)

AND : 주민등록번호 LIKE : 입력주민등록번호 || '%'

UNIONALL

SELECT 주민등록번호, 성명

FROM 고객정보

WHERE :입력성명 IS NOT NULL

AND 성명 LIKE : 입력성명 || '%'

실행계획을 분리하였기 떄문에 풀스캔을 하지 않는다.

- OR 조건이 간단할경우에는 Decode 나 NVL 을 이용하면 옵티마이저가 알아서 자동으로 UNION ALL 형식으로 실행 계획을 수립한다

(컬럼이 NOT NULL일 경우)

- DECODE를 사용한 쿼리는 인덱스 여부에 따라 풀스캔 하거나 인덱싱하여 조회 결과를 합치는 실행계획이 만들어지므로 효과적이다.

- 스칼라 서브 쿼리 사용시 조회되는 건수가 많을경우 조인을 사용하는 편이 좋고 집하 함수를 사용하는데 데이터가 많다면

인라인뷰 형태로 아우터 조인하는게 성능상 우수하다 하지만 데이터가 적을 겨우 스칼라를 사용해도 괜찮다.

- LIKE 절에 의한 성능 저하 

: 예전에는 인덱스를 사용할 수 없었지만 %를 뒤에 붙일경우 인덱스를 사용할 수 있게 바뀌었다. 그리고 인덱스 사용 효과를 높이기 위해 

문자열을 2자 이상 포함시키는게 좋다.

- Fetch

: DB에서 한번에 보내는 레코드 건수를 의미함

- 1건만 조회되는 쿼리경우 Rownum 사용

: 1건만 조회되는 쿼리의 드라이빙 테이블에 대한 인덱스 탐색이 유일 탐색(유니크 인덱스)이라면 Rownum을 추가하 필요가 없다.

그런데 Range Scan(범위탐색)이라면 Rownum을 추가해 성능을 개선할 수 있다.

그 이유는 Range Scan은 유니크한 데이터를 찾기 위해 계속해서 탐색을 하기 때문이다

- 우선순위에 따른 선택 수행을 위한 Rownum의 추가

: 레코드 한 건을 조회하는 쿼리지만 여러 select중 한곳에서만 데이터가 조회될 가능이 있는 경우에 UNION ALL로 구성하는 경우가 있다.

이때 UNION ALL 을 이용하는 SELECT절이 3개가 있다고 가정했을때 첫번째 SELECT절에서 데이터가 나왔음에도 불구하고 2번째 3번째도 

탐색하게 된다. 이럴때는 성능을 높이기 위해 인라인뷰로 만들고 ()뒤에 ROWNUM을 1로 줄경우 2번,3번까지 탐색을 하지 않게 된다.

- 파티션 키 추가

: 파티션 테이블은 배치 메인 쿼리의 경우 전체 테이블 탐색 범위를 줄이기 위해 사용하고 입력에서는 부하분산등 성능 목적으로 사용한다.

파티션 테이블이 글로벌 인덱스를 사용하는 경우 주기적으로 보관 기한이 지난 파티션을 제거하게되면 글로벌 인덱스가 사용 불가

상태로 바뀌어 인덱스 재생성이 필요해진다. 그리고 대량 테이블의 인덱스가 사용 불가 상태가되면 해당 인덱스를 사용하던 쿼리는 급격한

성능 저하가 발생한다. 이같은 상황을 방지하기 위해 파티션 테이블을 사용할때는 가급적 로컬 인덱스를 사용하는 것을 기본으로 하고,

단지 로컬 인덱스로 성능 개선을 못할경우 글로벌 인덱스를 사용하는것을 고려해본다.

- DB 집합처리(excuteBatch)

: 집합처리를 수행할 때 한번에 수십만 건씩 처리하면 자바 힙 메모리가 부족해지는 현상이 나타날 수 있으므로 최대 만 건 이내에서

적절하게 건수를 조정한다. 일반적으로는 천건 단위로 수행한다.

 

- 트랜잭션 처리

: CUD를 처리하는 끝부분에 두어야 수정된 레코드가 배타적으로 락을 점유하고 있는 시간을 최대한 줄여야 한다.

 

- 전체 테이블 탐색

: 메인리더가 수만건 내외의 소량건을 조회해서 처리한다면 테이블 조회시 인덱스를 사용하는것이 좋지만 수만건 이상

조회해야 한다면 테이블을 전체 탐색하는것이 더 효과적이다.  그 이유는 인덱스 탐색은 인덱스를 읽어 조건에 맞는 레코드 한건 한건을

순차적으로 읽는 랜덤 액세스가 이뤄지고 전체 테이블 탐색은 설정된 멀티블록 수 만큼 읽어들이기 때문이다.