DB 성능개선 - 인덱스

인덱스

 

유형(물리적순서(일치:Clustered),밀집도(Dense(순서),Sparce(포인터만)),구현방식(갱신 분포분산),비트맵(비정형 조회질의성능향상)

* ROWID(18자리, 16진수값), 구조(탐색키값, 포인터)

 

[개념]검색연산 최적화 위해 탐색키값과 해당 데이터 위치 포인터(Pointer)로 구성된 데이터 구조(object), 컬럼값과 ROWID를 이용, Data Block에 빠르게 접근하여 요청데이터를 검색

 

[구성]<레코드 키 값, 레코드 주소(포인터)>쌍을 체계적 수집/관리

 

[특징]

1)탐색성능개선 

2)독립성(데이터 저장구조와 별도) 

3)알고리즘(Tree, Hash구조) 

4)Trade-off(인덱스 생성 효율성 고 려필요)

 

[장점/단점]

(+)성능향상, 디스크I/O최소화, 정렬없이 인덱스 순서대로 추출가능

(-)성능저하(분포도 낮은경우 풀스캔이 유리), 부정형질의시 사용불가, NULL정보 저장X, 저장공간낭비

 

인덱스는 결국 지정한 컬럼들을 기준으로 메모리 영역에 일종의 목차를 생성하는 것입니다. 

insert, update, delete (Command)의 성능을 희생하고 대신 select (Query)의 성능을 향상시킵니다. 

여기서 주의하실 것은 update, delete 행위가 느린것이지, update, delete를 하기 위해 해당 데이터를 조회하는것은 인덱스가 있으면 빠르게 조회가 됩니다. 

인덱스가 없는 컬럼을 조건으로 update, delete를 하게 되면 굉장히 느려 많은 양의 데이터를 삭제 해야하는 상황에선 인덱스로 지정된 컬럼을 기준으로 진행하는것을 추천드립니다.

 

 

 

출처: http://jojoldu.tistory.com/243 [기억보단 기록을]

 

[유형/구조측면]

1)트리기반(OLTP범위검색) 

2)해시기반(OLTP 키검색) 

3)비트기반(DW, Mart데이터 검색)

 

[예제]SELECT ROWID FROM emp WHERE ename = 'MILLER';

ROWID => AAAAsf(Object번호) AAB(파일번호) AAAETB(블록번호) AAN(row번호)

 

인덱스/키

 

개발생산성향상,유지보수비용절감,가용성,신뢰성,고객만족도,응답속도논리(유클스컴) / 물리(비트리파)

 

[인덱스와 키의 관계]

인덱스 - 테이블내 항목검색시 저비용, 고속탐색을 위한 별도로 구성된 목차

키 - DB에서 Row를 식별하기 위해 최소,유일,불변을 가진 레코드값

 

[유형]

논리(클러,논클러,유니,논유니,Sparse,Dense,Composite,Single,Function-Based)

물리(파티션드,비트맵,리버스,Descending,비트리,비트맵조인,IOT)

 

[선정절차]

Access path조사->컬럼선정 및 분포도분석->Critical Access path 결정->클러스터링검토->컬럼 조합 및 순서결정 ->시험생성 및 테스트 ->조사 및 수정 -> 일괄적용

 

[고려사항]

분포도가 좋은 컬럼은 단독으로 생성(15%이내), 자주 조합되어 사용되는 컬럼은 결합인덱스 생성, 인덱스간 역할정의, 수정이 자주 일어나지 않는 컬럼을 인덱스로 사용

인덱스로 위치를 찾고 키로 해당 항목의 전체값을 알 수 있음

 

인덱스 선정기준 / 설계절차

 

[기외+접분인후인적]

※하나의 테이블에 3~4개의 인덱스가 적당함, 업데이트X, 유니크

 

① 기본키와 외래키 : 기본키는 자동으로 인덱스 생성, 외래키는 Full 스캔 방지위해 무조건 인덱스 선정

② 접근경로분석 : 자주 사용(운영자,어플리케이션)하는 SQL 수집 및 분석으로 인덱스 선정

③ 분포도조사 : 분포도=평균ROW수/총ROW수 * 100, 분포도가 10~15% 컬럼 인덱스 후보로 선정

④ 후보목록결정 : 접근유형에 따라 후보결정

⑤ 인덱스순서결정 : 접근경로에 따른 순서 결정

⑥ 적용시험 : 인덱스시험

 

[생성]CREATE INDEX 인덱스명 ON 테이블명(인덱스 열1, 열2...) [삭제]DROP INDEX 인덱스명;

* IOT(Index Organized Table)을 생성하면 인덱스와 데이터가 같은 저장구조에 생성. 저장공간이 적게들고 속도향상

 

인덱스 유형

 

[CN DS 기보] 차이나 디지털서비스는 기본이다

 

[파일조직측면]

1)Clustered Index : 데이터 레코드 물리적순서와 파일인덱스 순서가 동일 (키값,데이터정렬) - 빠른검색

☞ 테이블마다 한개, 물리행순서=인덱스순서, 리프노드=데이터, 색인크기작음, 검색속도빠름, 추가시재구성필요

2)Non-Clustered Index : 데이터 물리적 순서가 인덱스 순서와 상관없이 저장 (키값만정렬) - 느린검색

☞ 여러개, 물리행!=인덱스순서, 리프노드=데이터아님, 크기큼, 느림, 재구성시간적음

 

[데이터범위측면]

3)Dense Index : 레코드 하나에 하나의 인덱스 엔트리 구성

4)Sparse Index : 레코드 그룹, 데이터 블록 당 하나의 엔트리 구성

[키사용측면]

5)기본Index : 기본키를 포함한 인덱스

6)보조Index : 기본인덱스 이외의 인덱스

 

인덱스 분류

 

[단순해결함 비클] [트해비 함조도]

 

1)단일인덱스 : 하나컬럼으로만 인덱스 지정

2)순서인덱스 : 정렬된 순서로 인덱스 생성관리 (B-Tree 이용)

3)해시인덱스 : 해시함수에 의하여 직접 데이터 키값으로 접근

4)결합인덱스 : 복수개 컬럼 이용하여 인덱스 지정

5)함수기반인덱스 : 함수기반으로 사전에 인덱스 설정 (예.Create Index IDX_EMP01 ON EMP01(SAL*12);)

6)비트맵인덱스 : 특정컬럼의 비트열을 인덱스로 활용 (예. 성별) - 수정변경적은경우 유용

7)클러스터드인덱스 : 데이터 레코드 물리적순서와 파일인덱스 순서가 동일

 

 

인덱싱 방법  (정적인덱싱, 동적인덱싱)

 

[인덱스 재구성]삽입,삭제등에 의해 초기 인덱스 구조가 흐트러짐. 주기적 재구성필요

 

1)정적:레코드 삽입/삭제에 따라 인덱스 내용변화, 구조변경없음, 데이터파일에 새로운 레코드 저장공간이 없으면 오버 플로우 영역사용, 삽입/삭제등 파일변화를 오버플로우 영역 활용하여 수용(체인연결)→시간에 따른 성능저하→재구성

[비교] 인덱스(레벨,블로킹,초기인덱스레별수,max), 구성방식

 

2)동적:하나의 블록이 가득차면 동적으로 분열(Split), 일정수의 레코드를 유지못하면 병합(Merge),오버플로우구역없음, 오버플로우발생시 데이터블록분할 및 인덱스수정, 필요할때마다 수시로 부분적 파일재구성(전체재구성X) (사전에 레코 드 삽입 감안한 여분공간 준비)

 

IOT (Index Organized Table)

 

키 값 → 인덱스 검색 : ROWID 얻음 → 테이블 읽기

키컬럼을 인덱스와 테이블 모두 중복저장, 공간낭비 => IOT등장 IOT:인덱스 안에 테이블이 삽입된 구조, 인덱스만 읽으면 작업완료

- 기본키(Primary Key)기반 인덱스구조

- 기본키+ B*트리 인덱스

- ROWID정보 無

장점 : 빠른 키기반 검색, 스토리지 절약

 

일반테이블 vs IOT : 행구별(ROWID/PK), 결과값(순서예측불가/PK값 순서기반 출력), 유니크제약조건(설정가능/설정불 가), 저장공간(중복/중복없음)