쏘니헨리

인덱스 스캔을 하면 무조건 빠른가 ? // NO 

 

조건에 의한 처리범위가 넓어짐으로 인해 분포도가 나빠지는 경우가 있는데 이 경우 인덱스 스캔을 하는 것 보다는 FULL TABLE SCAN을 하는 것이 바람직함 

 

-> FULL TABLE SCAN시엔 한 번의 I/O때 마다 여러 개의 데이터 Blocks을 처리하기 때문에 I/O횟수가 감소하게 됨 

 

한 번의 I/O 4 Block Access Fast

DB_FILE_MULTIBLOCK_READ_COUNT=4 // 10 ~ 15% 이상 : 효율적

 

인덱스 사용이 불가능한 경우 

 

1) NOT 연산자 사용 

2) IS NULL, IS NOT NULL 사용

3) 옵티마이저의 취사 선택

4) External suppressing

5) Internal suppressing

 

옵티마이저의 취사 선택 

 

옵티마이저의 자의적 판단에 의해서 인덱스를 사용할 수도 있고 사용하지 않을 수도 있는데, 

이러한 것을 취사 선택이라고 함 

Rule Base Optimizer	Cost Base Oprimizer
정해진 규칙 기준	비용 기준

 

Optimizer의 자의적 판단으로 인한 잘못된 선택을 강제로 제어하기 위해서 Hint 사용

 

External suppressing 

 

1) 불필요한 함수를 사용한 경우

 

2) 문자열 결합 

 

3) DATE변수의 가공

 

4) 산술식의 적용 

 

5) lnternal suppressing 

 

간단한 연산식 - comm + '500' 

논리비교 연삭식 - bonus> sal /'10'

함수호출 MOD (sal,'100')

 

 

옵티마이저에 의한 선택 절차 

 

특정 테이블에 대해서 SQL의 주어진 조건으로 인해 사용될 수 있는 인덱스가 두 개 이상인 경우 

 

- 옵티마이저는 조건에 가장 적절한 인덱스를 선택해서 사용해야 함

- 주어진 조건에 가장 적절한 인덱스를 선택하려 할때 일련의 절차에 따라 결정함 

 

 

옵티마이저의 인덱스 선택 시 판단 절차 

 

1 주어진 조건에 대한 각 인덱스 별로 매칭률을 계산해 매칭률이 높은 것을 우선적으로 선택함 

  

 '='의 의미는 범위 제한

 

2. 인덱스 별 매칭률이 같은 경우에는 인덱스를 구성하는 칼럼의 개수가 많은 것을 우선적으로 선택함

3. 인덱스 별 매칭률과 인덱스를 구성하는 칼럼의 개수가 같은 경우에는 가장 최근에 생성된 것을 우선적으로 선택함

 

RBO와 CBO가 선택한 인덱스의 차이 

 

 

최종 정리 

 

1.인덱스를 사용하지 말아야 하는 경우 

 

6블록 이상의 데이터를 가진 테이블에 대해서, 쿼리로 처리할 데이터가 전체 데이터 중 15%이상을 초과할 경우엔 인덱스를 사용하지 않는 것이 매우 좋은 성능을 냄 

 

2.인덱스를 사용이 불가능한 경우 

 

NOT 연산자와 비교 

IS NULL,IS NOT NULL 

옵티마이저에 의한 취사선택

인덱스 컬럼의 변형 

 

1. 옵티마이저의 개념 

 

- 사용자가 실행한 SQL을 해석하고 데이터를 추출하기 위한 실행계획을 수립하는 프로세스

 

2. 옵티마이저의 종류 

 

RBO 

 

- 규칙기준 옵티마이저는 인덱스 구조나 사용하는 연산자에 따라 부여되는 순위가 정해져 있음

 

CBO 

 

- 대상 ROWS를 처리하는데 필요한 자원사용을 최소화해서 데이터를 빨리 처리하는데 목적이 있음 

 

3. 옵티마이저 레벨별 설정 

 

데이터베이스 전체에 지정 

 

[initSID.ora를 이용해서 지정]

OPTIMIZER_MODE=[RULE/CHOOSE/FIRST_ROWS/ALL_ROWS]

 

세션별로 지정 

 

SQL > ALTER SESSION SET OPTIMIZER_MODE =

         [RULE/CHOOSE/FIRST-ROWS/ALL-ROWS]

 

각 SQL 별로 지정 

 

SQL > SELECT/ * first_rows * /

                     ename 

         FROM EMP ; 

RBO와 CBO의 실행계획 비교 

 

- 동일 SQL에 대해서 각 옵티마이저가 수립한 실행계획은 다를 수 있음 

- SQL 퍼포먼스가 옵티마이저에 따라 다르다는 의미 

- 옵티마이저의 종류에 따라 달라지는 DB성능 차이점을 이해하는 것이 중요 

 

4. 인덱스 

 

사용자가 인덱스를 사용하는 이유 

 

- 데이터베이스에 저장된 자료를 더욱 빠르게 조회하기 위해 인덱스를 생성하여 사용함 

 

- 모든 SQL이 인덱스를 사용해야만 하는가 ? 

 

일반적으로 인덱스는 테이블의 전체 데이터 중에서 10 ~ 15% 이하의 데이터를 처리하는 경우에

효율적이며 그 이상의 데이터를 처리할 땐 인덱스를 사용하지 않는 것이 더 나음 

 

5. B*Tree 구조 

 

- 가장 많이 사용되는 인덱스의 구조라 할 수 있으며,

  인덱스의 데이터 저장 방식이기도 함 

- Root(기준)/Branch(중간)/Leaf(말단) Node로 구성됨 

 

- Branch 노드는 Leaf 노드에 연결되어 있으며

  조회하려는 값이 있는 Leaf 노드까지 도달하기 위해 

  비교/분기해야 될 값들이 저장됨 

 

Leaf 노드 = 인덱스 칼럼의 값 + ROWID 

 

인덱스 칼럼의 값 : 오름(내림)차 순으로 Sort되어 저장됨 

ROWID : 테이블에 있는 해당 row를 찾기위해 사용되는 논리적인 정보

 

Brancg Blocks - RootNode(뿌리),Branch Node

Leaf Blocks - Sorting,Leafnode

 

6.B*Tree 구조 

 

B*Tree 구조의 핵심은 Sort 

01 ORDER BY에 의한 Sort를 피할 수 있음 

02 MAX/MIN의 효율적인 처리 가능함 

 

7.인덱스 선정 절차 

 

1. 프로그램 개발에 이용된 모든 테이블에 대하여 Access Path 조사

2. 인덱스 칼럼 선정 및 분포도 조사 

3. Critical Access Path 결정 및 우선 순위 선정

4. 인덱스 칼럼의 조합 및 순서 결정 

5. 시험 생성 및 테스트 

6. 결정된 인덱스를 기준으로 프로그램 반영

7. 실제 적용 

 

8.인덱스 생성 및 변경시 고려할 사항

 

1. 기존 프로그램 동작에 영향성 검토 

2. 필요할 때마다 인덱스 생성으로 인한 인덱스 개수의 증가와 이로인한 DML 작업의 속도 

3. 비록 개별 칼럼의 분포도가 좋지 않을지라도 다른 칼럼과 결합하여 자주 사용되고 

  결합할 경우에 분포도가 양호하다면 결합 인덱스 생성을 긍정적으로 검토 

 

9.인덱스 스캔의 원리 

 

옵티마이저가 인덱스 사용을 위한 실행계획 수립함.

 

1. 조건을 만족하는 최초의 인덱스 row를 찾음 

2. Access된 인덱스 row의 ROWID를 이용해 테이블에 있는 row 찾음 (Random Access)

3. 처리 범위가 끝날때까지 차례대로 다음 인덱스 row를 찾으면서 스캔을 반복함

 

인덱스 스캔시에는 한 번의 I/O가 발생할때 마다 한 개의 Block을 처리함 

 

ROWID의 분해 

 

ROWID(4개의 정보) = 데이터의 저장 주소 

 

데이터의 저장 주소 

 

- 데이터를 가진 테이블 정보 

- 테이블 공간을 구성한 파일 경보 

- Block에 대한 정보 

- Row 

 

고유(Unique) 인덱스의 Equal(=) 검색

 

ex) SELECT * FROM emp WHERE empno = 7788;

고유(Unique) 인덱스의 범위(Range)검색 

 

SELECT * FROM emp WHERE empno >= 7654;

SELECT * FROM emp WHERE empno >= 7654;

 

중복인덱스의 범위 검색 

 

CREATE INDEX job_index on emp (job);

SELECT * FROM emp WHERE job Like 'SALE%'

SELE

 

OR & IN 조건 - 결과의 결합 

 

1. SELECT * FROM emp WHERE empno IN (7654,7788);

2. SELECT * FROM emp WHERE empno = 7654 OR empno = 778

 

인덱스 머지 . 결합인덱스 

 

인덱스 머지란 개별 칼럼에 인덱스가 생성되어 있으면서 모두 where절에 Equal(=)조건으로

사용되어, 각각의 인덱스 조합으로 자료에 접근하는 것을 의미 

일반적으로 결합인덱스를 사용할 경우, 좋은 엑세스 경로를 제공 

 

결합인덱스의 구성 

 

결합인덱스 칼럼 순서 결정

 

- where절 조건에 많이 사용되는 칼럼 

- Equal로 사용되는 칼럼

- 분포도가 좋은 칼럼

- 자주 이용되는 Sort의 순서로 결정

 

결합인덱스 사용 방법

 

- 결합인덱스의 첫 번째 칼럼이 where절에서 제외되어 있는 경우, 결합인덱스를 사용할 수 없음 

- 인덱스 스킵 스캐닝 

  결합인덱스의 첫 번째 칼럼이 WHERE절에 제외되어 있고, 두 번째 칼럼부터 WHERE절에 조건으로 기술되어 있는 경우에도 그 인덱스가 사용되는 경우 

 

결합인덱스 칼럼에 대한 '='의 의미

 

범위 제한 조건 

 

- 결합 인덱스의 선행하는 칼럼 순서로 WHERE절에 '='로 연속된 경우,

해당하는 조건을 범위제한 조건이라 함 

 

- 체크 조건 

- WHERE절 조건에서 선행 칼럼이 '='조건에 없다면 후행 조건은 체크조건이 됨 

 

- 인덱스 매칭률 =

 

데이터베이스 

 

- 데이터 파일과 로그 파일로 구성됨 

- 데이터 파일 : mdf,ndf 확장자

- 로그 파일 : ldf 확장자

- 다양한 형태의 개체 포함 

(테이블,인덱스,프로시저,트리거,함수)

 

테이블 

 

- 데이터베이스 내의 존재하는 대표적인 개체 

  (데이터를 가지고 있다.)

- 시스템 테이블 & 사용자 테이블 

- 실제 데이터를 물리적으로 포함하고 있음

- 행과 열로 구성 됨

 

페이지 

 

- 페이지 파일을 구성하는 논리 단위 

- SQL Server의 기본 데이터 저장단위(8KB)

- 데이터를 쓸 때 행을 페이지에 기록함 

- 데이터를 읽을 때(SELECT)

  페이지 내의 모든 행이 읽혀짐

- 페이지 내의 행이 많을수록 I/O 효율 증가 

- 0 ~ N 사이의 순차적인 번호를 가짐 (페이지 번호)

 

레코드 100개를 읽어야 할 경우 (SELECT)

 

페이지(8KB)에 레코드 10개 들어있는 경우는 10번 

페이지(8KB)에 레코드 100개 들어있는 경우는 1번만

읽으면 되기 때문에 1번 읽어서 레코드 100개를 

읽어주는 것이 성능이 더 좋다. 

 

인덱스 이해 

 

EX) 인덱스는 주민센터다.

- 사람을 빨리 찾을 수 있게 해준다.

 

EX) 인덱스는 도서 검색 시스템이다. 

- 그 책을 가장 빨리 찾을 수 있게 해준다. 

 

EX) 인덱스는 다리다. 

-강 건너 그 곳에 가장 빨리 갈 수 있게 해준다.

(멀리 돌아갈 곳을 다리위로 가로질러서 건너간다.)

 

위의 3개의 공통점은 "빨리"이다. (인덱스 없이는 빨리 갈 수 없다.)

 

인덱스 장점 

 

 빠른 데이터 검색 

 - 찾는 데이터를 가지고 있다면 직접 주거나 

   없다면 어디에 있는지 알려준다. 

 

데이터 중복을 방지

- PRIMARY KEY 제약조건  

  (PK 생성시 UNIQUE Clustered Index 자동 생성됨)

- UNIQUE 제약조건

 

잠금 최소화 

- 최소 범위의 잠금을 가능하게 해 준다.

(인덱스를 참조하여 원하는 데이터를 빨리 찾을 수 있기 때문에 Update를 빨리하고 나오게 된다.)

- 동시성을 높여 준다.

(Update를 빨리하고 나오기 때문에 많은 사용자가 동시에 동일한 데이터를 엑세스 할 수 있게 된다)

 

인덱스 단점 

 

물리적인 공간 차지 

- 인덱스는 테이블처럼 데이터를 가짐 

- 테이블처럼 물리적인 공간을 차지함 

 

인덱스에 대한 유지관리 부담

- SELECT 문은 인덱스를 좋아함 

- INSERT 문은 인덱스를 별로 좋아하지 않음 

  (데이터를 추가하면 인덱스에도 등록해야해서)

 

데이터가 극히 적다면

- 인덱스가 있어도 사용되지 않을 수 있음 

- 얻는 효과보다 유지관리 부담이 클 수 있음 

 

인덱스 조각화 

 

내부 조각화

- 인덱스 페이지가 꽉 차지 않고 비어있는 상황 

- OLTP성 테이블의 인덱스라면 빈공간 확보(내부 조각화)가 필요하다.

- OLAP성 테이블에서는 빈공간이 많으면 조회할 때 읽는 페이지 수가 증가하여 성능이 떨어질 수 있다.

  (따라서 페이지 분할이 덜 발생하기 때문에 꼭 나쁘다고 할 수 없다.)

 

외부 조각화 

- 인덱스의 논리적인 순서와 물리적인 순서가 맞지 않는 경우로 성능에 도움이 되지 않으므로 제거해야 한다.

   (인덱스 순서가 흩어져 있는 것)

- 인덱스 리빌드 목적

  

인덱스 유지관리의 필요성 

 

- 재생성 또는 재구성 작업 자동화 

- 모든 인덱스를 대상으로 하는 것은 바람직 하지 않음 

 (인덱스 재생성 또는 재구성은 CPU 부하를 유발)

 

인덱스 조각모음은 인덱스 스캔(전체 조회)를 빠르게 해주는 것이지 인덱스 SEEK(조건으로 값 찾기)와는 상관이 없다.

만약 쿼리문에서 WHERE 조건을 지정해서 특정 값만 가져 오도록 쿼리를 만들었는데 인덱스 스캔한다면

쿼리 튜닝(인덱스 생성)이 필요하다.

 

테이블과 인덱스 

 

테이블의 존재 형태 2가지 

- 힙 테이블 

- 클러스터형 인덱스 테이블 

 

인덱스 종류 

- 클러스터 인덱스 

- 비 클러스터 인덱스 

 

힙이란 ? 

 

정렬 기준이 없이 저장 된 테이블의 존재 형태 

- 데이터 페이지 내의 행동들 간에 순서가 없음 

- 데이터 페이지들 간에도 순서가 없음 

 

- 클러스터 형 인덱스가 없는 테이블 

 

아무렇게나 쌓여 있음

 

힙의 장점과 단점 

 

- INSERT문이 좋아하는 테이블의 형태

- 새로운 행을 기존 페이지의 빈 곳에 추가하면 됨

- 빈 공간이 없으면 새로운 페이지에 추가하면 됨 

 

- SELECT문이 싫어하는 테이블 형태 

- 데이터가 극히 적으면 상관이 없음 

- 데이터가 많으면 원하는 데이터 찾기가 너무 힘듦

 

SELECT를 위한 테이블인가 INSERT를 위한 테이블인가. 

 

INSERT를 위한 테이블이라면 힙 테이블로 유지해야 하지만.

SELECT를 위한 테이블이라면 대처가 필요(인덱스 생성)

 

힙의 원하는 데이터 찾아가기 

 

Table Scan 

 

- 데이터를 찾을 수 있는 유일한 방법 

- 모든 데이터 페이지를 다 읽어야 함 (다 읽음)

- 참고 : IAM 페이지를 참조해서 

해당 데이터를 모두 읽게 됨 

 

클러스터 형 인덱스란 ? 

 

- 특정 열을 기준으로 데이터가 정렬됨 

- 물리적 정렬이 아닌 논리적 정렬 

- 정렬 기준의 열로 찾는데 빠른 성능을 보여줌 

 

- 테이블 당 하나의 클러스터 형 인덱스 설정 가능 

- 실제 데이터를 가지므로 하나만 존재 가능 

- 테이블을 여러 개 만들면 여러 개 존재 가능 

 

힙 -> 클러스터형 인덱스 

 

힙테이블 

클러스터형 테이블

인덱스 키 이름을 기준으로 데이터 페이지가 정렬되어 있다.

 

 

테이블 존재 형태의 문제 

 

힙 테이블로 둘 것인지, 클러스터형 테이블로 만들어서 정렬시킬 것인지 

장/단점이 있으니 충분한 고민이 필요 

 

클러스터형의 원하는 데이터 찾아가기 

 

Clustered Index Scan 

- 모든 데이터 페이지를 읽음 

- Table Scan과 크게 다를 바 없음 

- 데이터가 많은 경우 성능 문제 발생 

 

Clustered Index Seek 

- 특정 데이터를 클러스터 인덱스에서 찾음 

- 아주 빠른 성능을 보여줌

인덱스 구조

클러스터형 인덱스 유일성 지정 

 

UNIQUE Clustered Index 

- 인덱스 키 열에 중복된 데이터가 추가되는 것을 차단함

 

UNIQUE 속성을 생략하면 

- 기본적으로 UNIQUE 하지 않은 인덱스가 생성됨 

  그래서 내부적으로 유일성을 확보하기 위한 값이 사용됨 

  (공간 낭비가 되기 때문에 명시해 주는게 좋다)

 

UNIQUE 여부를 명확히 설정 

- 인덱스 키 열의 유일성을 우리가 아는게 중요한 것이 아니고 

  SQL SERVER가 아는 것이 중요

 

클러스터형 인덱스는 만능이 아님 

 

인명 전화번호부에서 서대문구 주민을 찾으려면 

 

- 전화번호부를 처음부터 끝까지 다 읽어야 함

  (이름으로 정렬되어 있기 때문)

 

A 열을 기준으로 정렬된 데이터에서 

 

- A로만 찾는다면 좋겠지만 

EX) WHERE A=10 (인덱스 활용)

 

- B 또는 C 등 다른 열로 찾는 경우 의미가 없어짐 

EX) WHERE B=5 (인덱스 활용 못함)

 

다양한 검색을 위한 추가적인 도움이 요구됨 

 

- 클러스터 인덱스의 부족함을 도와주는 비 클러스터인덱스가 필요 

 

힙 + 비 클러스터 형 인덱스 

 

비 클러스터 형 인덱스가 가진 것 

 

- 생성한 인덱스 키 열의 모든 데이터 

  name,age 컬럼으로 만들었다면 

  name,age + RID값을 가지고 있음 

 

RID

 

 힙 테이블이 데이터가 정렬되어 있지 

 않기 때문에 RID를 매칭시킴

 

RID를 갖는 이유 

 

- 주소를 딱 찍어주니 좋음 

- RID는 거의 변하지 않음 

- RID는 값이 크지도 않음 

 

비 클러스터 인덱스는 999개까지 만들 수 있음

 

- 불필요한 비 클러스터 인덱스를 너무 많이 만들면 성능 저하 

- 데이터와 별개로 저장되기 때문 

 

힙 + 비 클러스터 형 인덱스 

 

index Seek 

 

- Root페이지부터 찾아가기 

- 아주 빠른 성능을 보여 줌 

 

Rid Lookup 

 

- 힙 테이블을 찾아가는 과정

- 비 클러스터 형 인덱스가 가지 못한 데이터를 찾아가는 과정 

 

Index Scan

 

- 리프 수준의 모든 인덱스 페이지를 다 읽음 

 

총 정리 

 

- 비 클러스터 인덱스는 컬럼을 다 가지고 있지 않지만, 다 가지고 있을 수 있다.

 

- 쿼리는 빼가는 과정이다. 

  조회에 필요없는 컬럼을 제거 

  조회 화면을 변경

 

- 쿼리는 테이블이 아닌 인덱스로 날려야 한다 

  인덱스의 도움을 받지 못한 쿼리는 테이블로 날아가고 성능에 문제가 생길 수 있다.