인덱스 기본 정리

링크 : 

1. 1. 데이터 파일의 구조
2. 2. DBMS 메모리 구조
3. 3. 인덱스 기본
4. 4. 정렬을 제거하는 방법
5. 5. 프로시져 캐시 경합 확인하기
6. 6. Loop 쿼리 제거하기
7. 7. 튜닝 결과 샘플 보기

아이템 DB의 인덱스와 쿼리를 튜닝해서 실행 비용을 90% 감소 시킨 노하우를 팀과 공유하기 위한 노트

PK 인덱스 특성을 고려한 데이터 베이스 성능 향상 도모

B-트리 구조적 특징에 따라 설계에 반영해야 할 요소를 확인할 필요가 있다.

PK 설계는 데이터를 접근할 때 경로를 제공하는 성능의 측면에서 매우 중요한 의미를 갖는다. 때문에 설계 마지막에 컬럼의 순서를 조정할 필요가 있다.

PK가 복합식별자 일 때

순서 결정 기준 – 인덱스 정렬 구조를 이해한 상태에서 인덱스를 효율적으로 사용할 수 있도록 PK 순서를 지정한다. 복합 인덱스의 경우 앞쪽에 위치한 값이 최대한 필터링 가능해야 한다.

(점 조건 + 점 조건 + ……+ 선분 조건)

옵티마이저와 실행계획

옵티마이저 - SQL문에 대해 최적의 실행 방법을 결정하는 역할을 한다.

최적의 실행방법을 실행 계획이라고 한다. (Execution Plan)

가장 적은 비용으로 계획을 만들기 위해 노력한다.

관계형 데이터 베이스는 SQL문을 통해서만 데이터를 처리할 수 있는데 사용자의 요구사항만 기술할 뿐 처리 과정에 대한 기술은 하지 않는다. 때문에 요구사항을 만족하는 다양한 실행 방법이 존재할 수 있다.

DB는 옵티마이저가 결정한 실행 방법대로 실행 엔진이 데이터를 처리하여 결과 데이터를 사용자에게 전달할 뿐이다.

MSSQL의 비용기반 옵티마이저 (CBO, Cost Base Optimizer)

규칙 기반 옵티마이저는 호환성을 위해서만 남아있다.

질의 변환기

-        SQL문을 처리하기에 더욱 용이한 형태로 변환한다.

대한 계획 생성기

-       동일한 결과를 생성하는 다양한 대안 계획을 만든다.

-       인덱스 연산의 적용 순서, 연산 방법, 조인 순서 변경

-       대안 계획이 많아지면 최적화 수행 시간이 길어지기 때문에 계획의 수를 제한한다.

-       최적의 계획이 포함되지 않을 수도 있다.

 

비용 예측기

-       계획의 비용을 예측하는 모듈

B-트리 인덱스 구조

링크 화살표 중요

최상단은 Root Block이라 한다.  Branch Block은 분기를 목적으로 하며 하위 블록의 포인터를 갖는다. Leaf Block은 인덱스를 구성하는 컬럼 데이터와 레코드 식별자를 갖는다.

Leaf Block의 양방향 링크는 오름차순, 내림차순 정렬을 쉽게한다.

B-트리 인덱스 검색 방법

1. 37을 검색한다.

2. Root Block 좌측 값보다 작거나 같으면 좌측으로 이동한다.

3. Branch Block 좌측 값보다 작거나 같으면 좌측, 크면 우측, 사이값이면 가운데로 이동한다.

4. Leaf Block 이면 찾으려는 값을 검색한다.

5. Leaf Block에 값이 존재하면 레코드 식별자를 이용하여 해당 테이블을 읽는다.

디스크 I/O 부하

db file sequential read 

 : single block I/O 대기 이벤트, 한번의 I/O call 에 하나의 데이터 블록만 읽는다. 인덱스를 읽을 때, 인덱스를 거쳐 데이블에 액세스 하는 경우 발생

db file scattered read

 : multiblock I/O 대기 이벤트, I/O call 이 필요한 시점에 인접 블록을 같이 읽어 메모리에 적재한다. table full scan or index fast full scan 인 경우 발생

비클러스터형 인덱스 탐색 순서

- 클러스터형 인덱스가 없는 경우 Leaf block으로 이동 후 RID 를 이용해서 데이터 접근

- 클러스터형 인덱스가 있는 경우 Leaf block으로 이동 후 클러스터형 인덱스를 이용해서 데이터 접근

간단히 설명하면 예를 들어 클러스터형 인덱스 컬럼이 A, 비클러스터형 인덱스 컬럼이 B, C 일 때

비클러스터형 인덱스 구성은 B, C, A 이다. 비클러스터형 인덱스 뒤엔 항상 클러스터형 인덱스가 붙는다.

테이블 스캔을 이용할 때는 순서대로 조건에 맞는 데이터를 찾는다.

비클러스터형 인덱스 구성 CN

SELECT 조건절이 CN AND ITemCode 인 경우 데이터 추출 과정

랜덤 액세스가 발생하면서 낭비가 발생

인덱스 수정으로 랜덤 액세스가 제거된 추출 과정

추가로 인덱스 단편화가 심할 때 상대적으로 SELECT 성능이 20% 정도 감소를 보였음

---