1 데이터 사용량과 사용자들의 업무 프로세스를 분석한다.

데이터 사용량 분석
논리적 모델링이 끝난후 물리적 모델링으로 넘어 갔을때 관련된 테이블의 스키마의 구성이 끝난후
생성된 테이블들의 하루에 입력되는 데이터 건수와 조회되는 데이터 건수가 얼마가 되는지등을 분석하는것

사용자들의 업무 프로세스 분석
데이터를 조회할때 어떤 컬럼을 기준으로 조회/정렬 하는지
다른 테이블을 연관해서 데이터 조회 여부,
데이터 입력후 다른 테이블을 업데이트 하는것인지 등등
그 과정과 그와 관련된 프로세스를 분석하는것

2 클러스터드 인덱스의 사이즈는 적을수록 좋다.

클러스터드 인덱스가 존재하면 모든 넌클러스터드 인덱스의
리프레벨은 클러스터드 인덱스의 키값을 포인터 정보로 갖는다.

클러스터드 인덱스의 사이즈는 넌클러스터드 인덱스의 사이즈에 영향을
끼치기 때문에 클러스터드 인덱스의 사이즈는 적을수록 좋다.

예) 클러스터드 인덱스의 사이즈가 10Byte 이면 모든 넌클러스터드 인덱스의 리프레벨은
    10Byte의 클러스터드 인덱스의 키값을 포인트 정보를 갖게 된다.

※ 모든 인덱스의 사이즈는 작으면 작을수록 좋다!

3 한 테이블에 컬럼의 숫자는 많아야 10개 내외가 될 수 있도록 디자인 하자.

컬럼의 숫자가 많아지면 ROW의 길이기 길어지기 때문에 데이터 입력시
여러개의 페이지가 사용되어지기 때문에 물리적 I/O 성능이 떨어지게 된다.

그리고 컬럼의 숫자가 많아지면 하나의 테이블에 많은 데이터가 존재하기 때문에
관련된 프로세스를 처리하다보면 서로 연관성이 있는 컬럼을 엑세스하면서 프로세스가 집중될수 있다.
프로세스가 집중되다 보면 DEAD LOCK이 발생할 가능성이 높아진다.

4 복합 인덱스(Composite Index)의 경우 컬럼의 순서를 고려하자.

복합 인덱스일때 인덱스의 첫번째 컬럼으로 데이터가 정렬된다.
INDEX(컬럼A, 컬럼B)와 INDEX(컬럼B, 컬럼A)는 전혀 다른 인덱스이다.
INDEX(컬럼A, 컬럼B)는 컬럼A를 기준으로 정렬되며 INDEX(컬럼B, 컬럼A)는 컬럼A를 기준으로 정렬된다.
복합 인덱스를 생성하는 기준은 데이터 조회시 어떤 기준으로 조회하는지를 확인 후 결정한다.

복합 인덱스에서 첫번째 컬럼의 길이가 짧을수록 검색 성능은 좋아진다.

5 커버드 쿼리(Covered Query)를 적용하자.

조회 조건과 조회의 대상이되는 컬럼이 모두 인덱스로 구성되어 있는 쿼리를 커버드 쿼리라고 한다.

예) 회원 테이블에서 회원인증을 할때

SELECT 회원ID FROM 회원 WHERE 회원ID = 회원ID AND 비밀번호 = 비밀번호

CREATE INDEX 회원인증_IDX ON DBO.회원(회원ID, 비밀번호)로 인덱스 생성를 생성하면
WHERE 조건과 조회 대상이 되는 컬럼이 인덱스로 구성되어 있기 때문에
데이터 조회시 인덱스의 리프레벨에서 관련된 프로세스를 모두 처리할 수 있기 때문에
처리 속도가 빠르다.

※ 회원 테이블에서 회원인증을 할때 회원정보다 같이 가져와야 한다면
   2005의 INCLUDE 기능을 사용하는것이 좋다. 이경우도 커버드 퀴리에 속한다.

   SELECT 회원ID, 이름, 회원등급 FROM 회원 WHERE 회원ID = 회원ID AND 비밀번호 = 비밀번호

   CREATE INDEX 회원인증_IDX ON DBO.회원(회원ID)
   INCLUDE (비밀번호, 이름, 회원등급)

6 파일 그룹(File Group)을 활용하자.

성능상의 이슈와 관리상의 이슈를 해결하기 위해서 파일 그룹을 사용한다.
사용자가 원하는 테이블이나 인덱스(데이터 베이스내에서 사이즈를 가지고 있는 테이블이나 인덱스)를
사용자가 원하는 파일 그룹에 위치시켜 관리할수 있고 파일 그룹단위로 백업/복원을 할수 있기 때문에
대용량 데이터베이스를 관리하는 솔루션이 될수 있다.

파티션 테이블, 파티션 인덱스를 구성할 수 있다.

파티션 테이블
하나의 테이블을 여러개의 파일 그룹으로 구성하면 데이터가 각각의 파일 그룹으로 분산되기 때문에
테이블에 걸리는 로드가 각각의 파일그룹으로 분산되어진다.

파티션 인덱스
파티션 테이블에 인덱스를 생성하는 경우
하나의 큰 인덱스를 생성하게되면 인덱스의 뎁스(단계)가 높아지지만
파티션 인덱스는 인덱스의 뎁스가 낮고 인덱스 검색시 속도가 빠르다.

7 인덱스를 잘 활용할 수 있도록 쿼리를 작성하자.

- 인덱스가 정의된 컬럼을 가공하면 안된다.

   CREATE INDEX 판매_IDX ON DBO.판매(판매량) 으로 인덱스를 생성했을때

   Query 1) SELECT 회원ID FROM 판매 WHERE 판매량 = 50

   Query 2) SELECT 회원ID FROM 판매 WHERE 판매량 + 5 = 45

   두쿼리의 결과는 같지만 Query 1)은 인덱스를 타지만 Query 2)는 인덱스를 타지 못한다.

- LIKE 검색시 검색단어앞의 %는 사용하지 않는다.

  CREATE INDEX 회원인증_IDX ON DBO.회원(회원ID) 으로 인덱스를 생성했을때

  Query 1) SELECT 회원ID FROM 회원 WHERE 회원ID LIKE '회원ID%'

  Query 2) SELECT 회원ID FROM 회원 WHERE 회원ID LIKE '%회원ID%'

  위의 두 쿼리도 Query 1)은 인덱스를 타지만 Query 2)는 인덱스를 타지 못한다.

8 클러스터드 인덱스를 생성할때는 주의 해야한다.

  ※ 순차적으로 입력되는 컬럼에 클러스터드 인덱스를 만든다.

   판매 기록 테이블이 있을때

   상품코드, 구매자번호, 판매일자 컬럼중 클러스터드 인덱스를 생성할 컬럼을 선택한다면?

   답은 판매일자!

   - 상품코드 컬럼에 클러스터드 인덱스를 생성
  
     판매 기록 테이블의 모든 데이터가 상품코드 컬럼을 기준으로 정렬되어 진다.
 
     상품코드A
     상품코드B
     상품코드C
     상품코드D
     상품코드E
 
     이렇게 정렬되어 있는 상황에서 상품코드B의 판매 기록이 입력되면 상품코드 컬럼을 기준으로
     데이터를 다시 정렬해야 하기 때문에 부하가 크다.

   - 구매자번호 컬럼에 클러스터드 인덱스 생성

     판매 기록 테이블의 모든 데이터가 구매자번호 컬럼을 기준으로 정렬되어진다.

     구매자1
     구매자2
     구매자3
     구매자4
     구매자5

     이렇게 정렬되어 있는 상황에서 구매자2가 판매 기록이 입력되면 구매자번호 컬럼을 기준으로
     데이터를 다시 정렬해야 하기 때문에 부하가 크다.

   - 판매일자 컬럼에 클러스터드 인덱스 생성

     판매일자는 그날그날 판매되는 날짜이기 때문에 갑자기 하루나 몇일전의 판매 기록이 입력되는 일이 없다면
     데이터를 다시 정렬할 상황이 없다.

  
  ※ 클러스터드 인덱스를 만들만한 컬럼이 없다면 일련번호 컬럼(자동 증가열)에
     클러스터드 인덱스를 만드는것도 좋은 방법이다.
 

크리에이티브 커먼즈 라이선스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.

1 기본키(Primary Key)제약 조건

기본키 제약조건을 정의하게 되면 기본키 컬럼에 유니크(Unique)한 클러스터드 인덱스가 기본적으로 만들어진다.
기본키를 생성하면서 만들어진 인덱스는 개별적으로 삭제할 수 없으며 기본키 제약조건을 해제하면 기본키 컬럼에 인덱스도 함께 제거된다.

※ 기본키 컬럼보다 더 많은 범위 검색이 이루어지는 컬럼이 있다면
    기본키를 생성할때 넌클러스터드 인덱스로 생성하고 범위 검색이 자주 이루어지는 컬럼에
    클러스터드 인덱스를 생성하는것도 좋은 방법이다.


2 포린키(Foreign Key)제약 조건

포린키 컬럼은 기본적으로 조인의 조건이나 참조 무결성을 구현하는 과정에서 자주 액세스되지만 기본적으로 인덱스가 만들어지지 않는다.

※ 조인의 성능 항샹을 위해서 포린키 컬럼에 적절한 인덱스를 생성해 주어야 한다.
    두 컬럼의 차수가 1:1 이면 포인트 쿼리이기 때문에 포린키에 넌클러스터드 인덱스를 생성하는것이 좋다
    두 컬럼의 차수가 1:N 이면 범위 쿼리이기 때문에 포린키에 클러스터드 인덱스를 생성하는것이 좋다.

3 유니크(Unique)제약 조건

유니크 제약조건을 정의하게 되면 해당 컬럼에 유니크(Unique)한 넌 클러스터드 인덱스가 기본적으로 만들어진다.

4 인덱스 페이지의 채우기 비율

- FillFactor : 리프 레벨의 채우기 비율을 정의
- PadIndex : 넌 리프 레벨의 채우기 비율을 정의

※ 일반적으로 85%를 기준으로 데이터 입출력 비율을 감안하여 설정한다.
    실제로 성능에 영향을 끼치는것은 FillFactor


5 조각 모음과 재생성

인덱스의 채우기 비율을 잘잡아도 데이터의 입/출력이 있다보면 조각화가 발생하여 조각 모임 또는 재생성 해줘야한다.

※ 조각화의 상태가 심각하다면 재생성을 조각화의 상태가 덜하다면 조각모음을 권장한다.

- sys.dm_db_index_physical_stats : 인덱스의 동적인 상태정보 확인
- 인덱스 재 생성 : ALTER INDEX ALL ON TEST03 REBUILD WITH (FILLFACTOR = 80)
- 인덱스 조각모음 : ALTER INDEX ALL ON TEST03 REORGANIZE


6 인덱스의 크기와 데이터 입력시의 성능 비교

인덱스의 크기
넌클러스터드 인덱스 > 클러스터드 인덱스

데이터 입력시 성능
넌클러스터드 인덱스 > 클러스터드 인덱스

데이터 조회시 성능
클러스터드 인덱스 > 넌클러스터드 인덱스

7 데이터의 밀도가 높은 경우 인덱스를 만들지 않는다.

예) 성별의 경우 남/녀의 경우만 존재하기 때문에 인덱스를 만들지 않는다.
     이런경우는 인덱스를 생성해도 성능차이가 없다.

크리에이티브 커먼즈 라이선스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.

* 인덱스를 사용하는 이유
  데이터의 검색속도를 향상시키기 위해서

* 인덱스를 통해서 데이터를 빠르게 검색할 수 있는 이유
  인덱스는 항상 정렬된 상태를 유지하고 있기 때문


1 클러스터드 인덱스와 넌클러스터드 인덱스 소개

* 클러스터드 인덱스 (Clustred Index)
- Leaf Level은 데이터페이지이다.
- 클러스터드 인덱스가 생성되는 컬럼를 기준으로
  데이터 페이지에 저장된 데이터를 물리적으로 정렬시킨다.
- 기본적으로 넌클러스터드 인덱스보다 검색 속도가 빠르며
  특히 범위 조회(Range Query)에 빠른 속도를 나타낸다.
  (클러스터드 인덱스는 인덱스의 리프 레벨이 데이터 페이지기 때문에
   넌클러스터드 인덱스보다 검색 속도가 빠르다.
   클러스터드 인덱스는 데이터가 정렬되어 있기 때문에 범위 조회에서 속도가 빠르다.)
- 한 테이블에 하나의 클러스터드 인덱스만 만들 수 있다.
- 기본키 제약조건(Primary Key)을 정의하게 되면
  기본적으로 기본키 컬럼에 Unique한 클러스터드 인덱스가 만들어진다.

* 넌 클러스터드 인덱스 (Non Clustered Index)
- 인덱스 생성시 물리적으로 데이터를 정렬시키지 않고 있는 그대로의 위치 정보로 인덱스를 구성한다.
- 데이터 페이지 위에 인덱스 페이지가 위치하게 되며
  기본적으로 클러스터드 인덱스보다 검색속도가 느리며
  범위 조회(Range Query)를 할 경우 거의 인덱스의 도움을 받을 수 없다.
  (인덱스를 안탄다. Table Scan을 하게 된다.)
- 포인트 쿼리에서 좋은 성능을 발휘한다.
- 한 테이블에 249개까지의 넌클러스터드 인덱스를 만들수 있다.



일반적인 책 맨 끝의 인덱스는? 넌클러스터드 인덱스
클러스트드 인덱스에 해당하는것은? 사전

※ 밀도, 선택도
밀도 높다. -> 선택도가 낮다.
선택도 높다 -> 밀도가 낮다.

넌클러스터드 인덱스 : Point Query 에서 좋음, 선택도가 높다.

선택도가 높다, 낮다라는 판단은 전체의 데이터중 몇건의 데이터를 리턴하느냐에 따라서 결정된다.



2 인덱스의 생성

* 넌클러스터드 인덱스





* 클러스터드 인덱스





3 페이지 분할 현상





위의 그림을 보면 데이터가 입력될때는 클러스터드 인덱스가 더 많은 영향을 받는다는것을 알수 있다.
클러스터드 인덱스의 경우 인덱스의 리프레벨에 레코드가 끼어들게 되고
넌 클러스터드 인덱스의 경우 데이터는 데이터 페이지에 입력되고 인덱스 정보만
갱신 되기 때문에 데이터가 입력될때는 넌클러스터드 인덱스가 성능상의 영향을 더 적게 받는다.

데이터가 입력될때마다 페이지 분할 현상이 발생하게 되면 입력할때도
많은 오버헤드가 발생하게되고 조각화가 심해진다.
조각화가 심해지면 같은양의 정보를 저장하는데 있어서 더 많은 페이지가 사용되고
인덱스의 뎁스도 더 높아지며 데이터를 조회할때 성능도 보장하지 못한다.

4 넌클러스터드 인덱스가 있는 상황에서 클러스터드 인덱스를 생성할때

크리에이티브 커먼즈 라이선스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.

* 인덱스를 사용하는 이유?
  데이터의 검색 속도를 향상 시키기 위해

* 인덱스를 통해 데이터를 빨리 찾을 수 있는 이유?
  인덱스는 항상 정렬된 상태를 유지하기 때문



1 인덱스의 구조
  인덱스는 계층적인(B-Tree) 구조를 갖는다.

  ※ 사전의 인덱스와 달리 컴퓨터에서의 인덱스의 구조가 계층적인 이유
  사람의 경우 사전처럼 A~Z로 정렬이 되어 있는 구조에서 원하는 단어를 찾기 위해서
  임의의 지점으로 접근하여 원하는 단어를 찾을수 있지만
  컴퓨터는 Start라는 단어를 찾기 위해서 A,B,C,D~~ 순으로 인덱스를 처음부터 검색하여
  Start라는 단어가 나올때까지 검색을 하기 때문에 계층적으로 되어 있다.
  (왜 계층적으로 되어 있는지 자세한 설명은 저~~ 밑에서)

  Leaf Level
  인덱스의 가장 하의 영역
  일반적인 인덱스의 영역

  Non-Leaf Level
  Root Level, Intermediate Level 을 통틀어 Non-Leaf Level이라고 한다.
  하위 페이지(Leaf Lavel)에 대한 정보를 가지고 있다.



2 데이터 검색 방법

Table Scan
인덱스가 없거나 인덱스가 있더라도 인덱스가 없는 컬럼을 조회하여 테이블의 데이터가 저장된 Data Page를 순차적으로 조회하는 방법

Index Seek
인덱스가 있거나 인덱스가 정의된 컬럼을 조회해서 인덱스로 데이터를 찾아 들어가는 방법


3 데이터의 검색 유형

* 포인트 쿼리 (Point Query)
  조회를 통해 얻어진 결과값이 하나 혹은 없는 경우의 질의 (최대 결과값은 1개)

* 범위 쿼리 (Range Query)
  조희를 통해 얻어진 결과값이 여러개가 리턴될 수 있는 경우의 질의

※ 포인트 쿼리 범위 쿼리냐를 구분하는것은 SELECT 구문이 어떻게 되어있느냐가 아니라
   그 테이블의 데이터가 어떻게 저장되어 있느냐에 따라서 같은 SELECT문이라도
   포인트 쿼리, 범위 쿼리가 될수 있다.

   예) SELECT * FROM 테이블 WHERE 필드A = 'A'
    테이블의 필드A에 'A' 데이터가 1개가 저장되어 있으면 포인트 쿼리가 되지만
    'A' 데이터가 1개이상 저장되어 있으면 범위 쿼리가 된다.

* 커버드 쿼리 (Covered Query)
  조회의 조건과 조회의 대상이 되는 컬럼이 모두 인덱스로 구성된 질의

 

크리에이티브 커먼즈 라이선스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.