테이블 검색 속도가 느려질때는 테이블 단편화 정...

테이블 사용량이 잦고 데이타가 많은 경우 프로시저의 이상은 없어보이는데도 데드락 현상이 발생하는 수가 있다.

테이블 검색 속도 자체가 너무 느려져서 생기는 문제인데 인덱스도 제대로 걸려 있는 상황이라면 단편화를 생각해 볼 필요가 있다.

먼저 단편화 정보를 확인하는 명령어는 DBCC SHOWCONTIG  이다.

다음은 mail_box 라는 테이블에 대한 단편화 정보를 조회해본 샘플이다.

참고로 이 테이블은 본인이 다니는 회사 제품 중에 메일 서비스에서 사용하는 테이블이다.  메일 사용량이 많은 경우 수십만건의 데이타가 존재하기도 하고 편지함 이동 및 삭제 등으로  프로그램에서의 입력, 수정, 삭제 작업이 빈번하게 일어나는 테이블이다.

DBCC SHOWCONTIG이(가) 'mail_box' 테이블을 검색하는 중...
테이블: 'mail_box'(1139691308); 인덱스 ID: 1, 데이터베이스 ID: 9
TABLE 수준 검색을 수행했습니다.
- 검색한 페이지................................: 77802  
- 검색한 익스텐트 ..............................: 10259
- 익스텐트 스위치..............................: 76211
- 익스텐트당 평균 페이지 수........................: 7.6
- 검색 밀도[최적:실제].......: 12.76% [9726:76212]
- 논리 검색 조각화 상태 ..................: 99.27%
- 익스텐트 검색 조각화 상태 ...................: 86.36%
- 페이지당 사용 가능한 평균 바이트 수.....................: 3838.4
- 평균 페이지 밀도(전체).....................: 52.58%
DBCC 실행이 완료되었습니다. DBCC에서 오류 메시지를 출력하면 시스템 관리자에게 문의하십시오.


이때 속도에 영향을 미치는 부분은 익스텐트 스위치이다.

관련 용어를 설명해보자면  1페이지는 8Kbyte 를 갖는 공간이다. MSSQL  DB 베이스를 구성하는 최소 단위라고 보면 된다. 1익스텐트는 8개의 페이지의 묶음이다. 고로 1 익스텐트는 8*8 = 64Kbyte 의 공간이다.  이건 뭐 -_- 의미없다.  구구단을 외자-_-)

이때 제일 중요한 수치인 익스텐트 스위치는  DBCC 문이 테이블이나 인덱스의 페이지를 이동하는 동안 익스텐트 간에 이동한 횟수를 나타낸다.

익스텐트 스위치의 값은 검색한 익스텐트의 값에 가능한 근접해야 한다.  그 말은 순서대로 정렬이 되어서 필요 이상의 이동을 하지 않았다는 의미가 된다.  반면에 단편화가 많이 되어 익스텐트 간의 이동한 횟수가 높아진다면 접근 속도에 영향을 준다.

이 비율은 검색 밀도 값으로 계산되며  값은 높을수록 좋으며 인덱스 조각화를 줄여 개선 가능하다.

위의 예에 나온 테이블은 보다시피 단편화 정도가 심각하여 시스템에 아주 많은 과부하를 가져다주게 되었다.

- 검색한 익스텐트 ..............................: 10259
- 익스텐트 스위치..............................: 76211

위에서 보다시피 익스텐트의 수보다 스위치 수가 7배 이상 많다.  그만큼 익스텐트의 순서가 꼬여 있다는 말이다.

이는 밀도에서도 바로 알 수 있다.

- 검색 밀도[최적:실제].......: 12.76% [9726:76212]

최적과 비교했을때는 거의 8배의 차이가 난다.

이런 단편화가 생기는 이유는 해당 테이블에 대한 입력, 삭제 작업이 워낙 잦은 경우 빈번하게 발생할 수 있는 문제이다.

주기적인 단편화 정리 작업을 자동으로 하도록 설정이 필요한 경우라 볼 수 있다.

참고로 단편화 정리 작업을 한 경우에는 아래의 수치로 변경이 되었다.
(그래봐야 페이지 재정렬이다 -_-)

DBCC SHOWCONTIG이(가) 'mail_box' 테이블을 검색하는 중...
테이블: 'mail_box'(1139691308); 인덱스 ID: 1, 데이터베이스 ID: 9
TABLE 수준 검색을 수행했습니다.
- 검색한 페이지................................: 50463
- 검색한 익스텐트 ..............................: 6414
- 익스텐트 스위치..............................: 6670
- 익스텐트당 평균 페이지 수........................: 7.9
- 검색 밀도[최적:실제].......: 94.56% [6308:6671]
- 논리 검색 조각화 상태 ..................: 1.90%
- 익스텐트 검색 조각화 상태 ...................: 88.29%
- 페이지당 사용 가능한 평균 바이트 수.....................: 1519.7
- 평균 페이지 밀도(전체).....................: 81.22%
DBCC 실행이 완료되었습니다. DBCC에서 오류 메시지를 출력하면 시스템 관리자에게 문의하십시오.

보다시피 익스텐트 수와 익스텐트 스위치의 수가 거의 1:1에 가깝도록 변경되었으며 당연하겠지만 밀도도 94퍼센트로 올라갔다.

물론 전체를 새로 끼워 맞추는 작업도 가능하지만 운용중인 디비에 그러한 방식의 적용은 현실적으로 어렵다는걸 잊지 말자.

자 이런 경우 단편화 정리 (조각 모음)을 하는 명령어는 다음과 같다.

dbcc indexdefrag(db이름, "소유자.테이블이름",인덱스이름)

상세 정보는 MSDN 을 참고~

[출처] 테이블 검색 속도가 느려질때는 테이블 단편화 정보를 체크하자.|작성자 씨콤

http://www.dude.co.rk

이경철님의 파란블로그에서 발행된 글입니다.

MSSQL 2005의 인덱스에 대해서...

1. 인덱스의 개념

인덱스는 클러스터형 인덱스와 비클러스터형 인덱스가 있다.
클러스터형 인덱스는 영어사전과 같은 책이고, 비 클러스터형 인덱스는 그냥 인덱스가 있는 일반 책과 같다. 비 클러스터형 인덱스는 앞에서 설명했던 데이터베이스 이론에 관련된 책과 같이 인덱스가 별도로 있고 이 인덱스를 찾은 후에 기재된 해당 페이지로 가야하만 실제 내용을 접할 수 있는 것을 말하며, 클러스터형 인덱스는 영어사전과 같이 책의 내용 자체가 순서대로 정렬이 되어 있어서 인덱스 자체가 책의 내용과 같은 것을 말한다.

"클러스터형 인덱스는 테이블당 한 개만 생성 할 수 있고(영어사전처럼 오름/내림차순에 따라 정렬이 되므로), 비 클러스터형 인덱스는 테이블당 여러개를 생성할 수 있다. 또 , 클러스터형 인덱스는 행 데이터를 인덱스로 지정한 열에 맞춰서 자동 정렬해 버린다."

테이블의 Primary Key는 자동으로 클러스터형 인덱스가 생성된다.
기본키를 지정하는 열에 비클러스터형 인덱스를 생성할 수 있다. 이 경우에 클러스터형 인덱스가 붙은 기본 키 열은 비클러스터형 인덱스로 바뀐다.

여기서 테이블 생성 시에 자동으로 생성되는 인덱스의 특징은 다음과 같다.

"제약조건 없이 테이블 생성시에 인덱스를 만들 수 없으며, 인덱스가 자동 생성되기 위한 열의 제약조건은 Primary Key 또는 Unique 뿐이다."

Primary Key 및 UNIQUE에는 클러스터형/비클러스터형 인덱스를 자동 생성할 수 잇다. 단, 테이블에는 하나의 클러스터형 인덱스만 존재한다.
만약, Primary Key 가 A,B인 Table이 존재한다면, A,B인 이름으로 클러스터드 인덱스가 생성된다.
또한, Primary Key로 지정한 열에 무조건 클러스터형 인덱스가 생성되는 것이 아닐, 테이블에 클러스터형 인덱스로 강제 지정된 다른 열이 없을 경우에만 Primary Key가 지정된 열에 클러스터형 인덱스가 생성된다.

2. 인덱스의 내부 작동

B-Tree를 기본 근간으로 하고 있으며 B-Tree의 '노드'라는 용어를 sql 2005에서는 '페이지'라는 8kb라는 개념으로 사용하고 있다.
어떤 인덱스를 설정하느냐에 따라 페이지의 수가 다르고, 페이지 구성 방식이 다르므로 성능에 영향을 끼치게 되는것으로 본다.

1) 클러스터형 인덱스 특징
- 클러스터형 인덱스의 생성 시에는 데이터 페이지 전체를 다시 정렬한다.
- 그러므로 이미 대용량의 데이터가 입력된 상태라면 시스템에 큰 부하를 줄 수 있으므로 업무시간에 클러스터형 인덱스를 생성하는 것을 신중하게 검토해야 한다.
- 클러스터형 인덱스는 인덱스 자체의 리프 페이지가 곧 데이터이다. 그러므로 인덱스 자체에 데이터가 포함되어 있다고 말할 수 있다.
- 비클러스터형보다 검색속도는 빠르다. 하지만 데이터의 입력/수정/삭제는 더 느리다.
- 클러스터 인덱스는 성능이 좋지만, 테이블에 한 개밖에 생성하지 못한다. 그러므로 어느 열에 클러스터형 인덱스를 생성하느냐에 따라서 시스템의 성능이 달라질 수 있다.

2) 비클러스터형 인덱스
- 비클러스터형 인덱스의 생성 시에는 데이터 페이지를 그냥 둔 상태에서, 별도의 페이지에 인덱스를 구성한다.
- 비클러스터형 인덱스는 인덱스 자체의 리프 페이지는 데이터가 아니라 데이터가 위치하는 포인터(ROW ID)이다. 클러스터형보다 검색속도는 느리지만 데이터의 입력/수정/삭제는 더 빠르다.
비클러스터형 인덱스는 여러 개 생성할 수가 있다. 하지만 함부로 남용할 경우에는 오히려 시스템의 성능을 떨어트리는 결과를 초래 할 수 있다. 꼭 필요한 곳에만 생성하도록 한다.

3) 비클러스터형+ 클러스터형 인덱스
먼저 비클러스터형 인덱스로 찾고 클러스터형 인덱스를 사용하게 되고
오프셋 정보를 가지고 있던 비클러스터형 인덱스가 클러스터형 인덱스처럼 바뀐다.
따라서 클러스터형 인덱스는 될 수 있으면 적은 용량을 가지는 컬럼으로 설정하는게 좋다.

"인덱스를 검색하기 위한 1차 조건은 where 절에 해당 인덱스를 생성한 열의 이름이 나와야 한다. 물론, where 절에 해당 인덱스를 생성한 열 이름이 나와도 인덱스응 사용하지 않는 경우도 많다."

4) 인덱스를 생성해야 하는 경우

- where 절에서 사용되는 열을 인덱스로 만든다. (포함열이 있는 인덱스 경우는 제외)
- 데이터의 중복도가 높은 열은 인덱스를 만들어도 별 효용이 없다.
ex) select * from tabl1 where col1 = 'value1' 쿼리의 경우 table1의 데이터 건수가 10,000건이라면 이 쿼리의 결과가 100ㄷ건~300건 미만이어야 'col1'에 비클러스터형 인덱스를 만들 가치가 있다는 것이다. (물론 절대적이진 않다.)

5) 외래 키가 사용되는 열에는 인덱스를 생성해주는 것이 좋다.

6) JOIN에 자주 사용되는 열에는 인덱스를 생성해주는 것이 좋다.

7) INSERT, UPDATE, DELETE가 얼마나 자주 일어나는지를 고려해야 한다.
- 인덱스는 단지 읽기에서만 성능을 향상시키며 데이터의 변경에서는 오히려 많은 부담을 준다.
- 인덱스를 많이 만들어도 성능에는 문제가 되지 않는 테이블은 INSERT 작업이 거의 발생되지 않는 테이블이다.
그러므로 인덱스를 만들어서 SELECT의 성능을 높일 것인지, 만들지 않아서 INSERT/UPDATE/DELETE 시에 부하를 최호화할 것인지를 잘 결정해야 한다.

8) 클러스터형 인덱스는 하나만 생성할 수 있다.
클러스터형 인덱스를 생성할 열은(Between, >,<등의 조건)로 사용하거나, ㅈ집계 함수를 사용하는 경우에 아주 적절하다. 앞의 실습에서도 확인해 보았지만, 클러스터형 인덱스는 데잍처 페이지를 최소로 읽으므로 조건에서 가장 많이 사용되는 열에 생성하는 것이 바람직하다. 또한 Order by 절에 자주 나오는 열도 클러스터형 인덱스가 유리하다. 클러스터형 인덱스의 데이터 페이지(=리프 페이지)는 이미 정렬되어 있기 때문이다. 그런데 범위로 자주 조회하는 열이 두개 이상이라면 어떨까? 예를 들어, 다음의 두 쿼리가 비슷하게 자주 사용된다면 어떤 열에 클러스터형 인덱스를 생성해야 할까? 두 열 birthYear와 height의 중복도 등 모든 조건이 비슷하다고 가정하면 말이다.
SELECT userID, name, birthYear FROM userTbl WEHRE birthYear < 1979
SELECT userID, name, height FROM userTbl WHERE height < 179

이런 경우에는 하나는 클러스터형 인덱스를 생성하고, 다른 하나는 '포괄 열이 있는 인덱스'로 생성하면 된다. 그러면 둘 다 클러스터형 인덱스의 효과를 낼 수 있다.

9) 클러스터형 인덱스가 테이블에 아예 없는 것이 좋은 경우도 있다.
ex) 회원정보 테이블일 경우 userID가 Primary Key로 지정되어 클러스터드 인덱스로 설정되어 있다면 회원이 등록될때마다 즉시 재정렬이 수행되고 페이지 분할이 끊임없이 일어날 수도 있다. 그런 경우에는 차라리 클러스터형 인덱스를 넌클러스터드로 지정하는게 좋다.

10) 계산된 열에도 인덱스를 활용 할 수 있다. input + input2 PERSISTED 키워드(SQL2005부터 지원)

11) 포괄열이 있는 비클러스터형 인덱스를 활용하면 쿼리 성능을 높일 수 있다.
'포괄 열이 있는 인덱스'(Index with Included Columns)란 비클러스터형 인덱스의 리프 페이지에 데이터를 포함하고 있는 열을 의미한다. 이를테면 회원정보테이블과, 대여정보 테이블이 있을경우에 회원정보 테이블의 넌클러스터드 인덱스인 회원아이디에 포괄 열 인덱스를 생성하여 대여정보 테이블의 책이름과 입고일자를 함께 묶어주면
회원정보 테이블의 userid를 where절로 검색하였을경우 그 리프노드에 인덱스로 설정되어있는 회원id뿐만 아니라대여정보 테이블의 책이름과 입고일자도 함께 나타나는 경우를 말한다.
맞게 이해했나? 흠


* Table Scan : 데이터 페이지를 처음부터 끝까지 찾아본다는 의미이다.ㅣ 인덱스가 없을 경우거나 인덱스가 잇어도 그냥 테이블을 차아보는 것이 빠른 경우에 사용한다.
- Index seek: 비클러스터형 인덱스에서 데이터를 찾아본다는 의미이다.
Clustered Index Seek : 클러스터형 인덱스에서 데이터를 찾아본다는 의미이다.
clustered Index Scan : Table Scan과 동일한 의미이다. 클러스터형 인덱스의 리프 페이지는 결국 데이터 페이지이기 때문이다.

출처: http://kazenohi86.tistory.com/21

http://www.dude.co.rk

이경철님의 파란블로그에서 발행된 글입니다.

인덱스(MSSQL)

인덱스(MSSQL)

1.색인의 기본 개념

SQL서버에서 기본값으로 테이블을 만들고 데이터를 추가,수정하고 필요없는 데이터 삭제해가면서 저장할 때 데이터의 레코드는 내부적으로 아무런 순서 없이 저장된다.이때 데이터 저장영역을 Heap이라고 한다. Heap에서는 인덱스가 없는 테이블의 데이터를 찾을 때 무조건 전체 데이터 페이지의 처음 레코드부터 끝페이지의 마지막 레코드까지 다 읽어서 검색조건과 비교하게 된다. 이런식의 데이터 검색방법을 테이블 스캔(table scan) 또는 풀 스캔(full scan)이라고 한다.이럴 경우 양이 많은 테이블에서 일부분의 데이터만 필요로 할때 전체 영역을 다 읽어서 검색조건과 비교하게 되므로 처리 성능이 떨어진다.즉 색인은 데이터를 select 할 때 빨리 찾기 위해 사용된다.

create table board(
id int not null,
name varchar(20) default ''default name'',
date smalldatetime default getdate()
)
go

insert board values(1,default,default)

select name from board where id=1

select문을 마우스로 드래그하여 선택한 후 메뉴의 쿼리-예상실행계획표시(Ctrl+L)를 택하고 밑에 나오는 그림에 마우스를 갖다대면 풍선도움말이 나온다.여기에 보면 물리적연산,논리적 연산에 Table Scan 이라고 나온다.



2. 인덱스 만들 때 고려할 점

인덱스를 만들면 좋은 컬럼
* where , order by , group by 문 등에서 자주 사용되는 칼럼(인덱스 데
이터는 order by문을 사용하지 않더라도 정렬된 순서로 되어있음)
* 프라이머리키,유니크 constraints 컬럼 (내부적으로 유니크 인덱스 사
용)
* 포린 키 컬럼

인덱스 만들면 나쁜 컬럼
* 쿼리에서 자주 사용하지 않는 컬럼
* 키값이 선별도가 나쁠때 (성별,국적,학력...)


3.인덱스가 있을경우

1)만드는 방법:
CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED]
INDEX index_name ON table (column [,...n])
[WITH
[PAD_INDEX]
[[,] FILLFACTOR = fillfactor]
[[,] IGNORE_DUP_KEY]
[[,] DROP_EXISTING]
[[,] STATISTICS_NORECOMPUTE]
]
[ON filegroup]

2)생성된 인덱스 보기
exec sp_helpindex 테이블이름

3)인덱스 제거
drop index ''table.index''[,...n]

4)클러스터드 인덱스(clustered index)
:책으로 예를 들면 차례에 해당한다.
한테이블에 하나만 있어야 한다.
차례에 나오는 순서와 책의 순서가 일치하듯 데이터가 키값에 따라 정렬되어있다.
일정한 범위를 주고 찾는 경우 속도 향상에 도움이 된다.

create clustered index board_CL on board(id)
go

select name from board where id=1

select문을 마우스로 드래그하여 선택한 후 메뉴의 쿼리-예상실행계획표시(Ctrl+L)를 택하고 밑에 나오는 그림에 마우스를 갖다대면 풍선도움말이 나온다.여기에 보면 물리적연산,논리적 연산에 clustered index seek이라고 나온다.



5)넌클러스터드 인덱스(nonclustered index)
:책으로 예를 들면 찾아보기에 해당한다.
용어찾기,표찾기,그림찾기 처럼 한테이블에 여러개가 있을수 있다.
찾아보기가 책의 순서와 일치하지 않듯이 데이터는 들어가 있는 순서대로 있다.
일정한 범위를 주고 찾는 경우 테이블 관리자가 테이블과 함께 인덱스까지 관리해야 한다.

create table board2(
id int not null,
name varchar(20) default ''default name'',
date smalldatetime default getdate()
)
go

create clustered index board_NC on board2(id)
go

select name from board2 where id=1

select문을 마우스로 드래그하여 선택한 후 메뉴의 쿼리-예상실행계획표시(Ctrl+L)를 택하고 밑에 나오는 그림에 마우스를 갖다대면 풍선도움말이 나온다.여기에 보면 물리적연산,논리적 연산에 index seek이라고 나온다.

5.유일색인과 중복허용
1)primary key constraint
:클러스터 색인,유일색인이 디폴트
create table board3(
id int constraint PK_id primary key not null,
name varchar(20) default ''default name'',
date smalldatetime default getdate()
)
go

2)create index
:넌클러스터 색인,중복허용
create table board4(
id int not null,
name varchar(20) default ''default name'',
date smalldatetime default getdate()
)
go

create index board4_NC on board4(id)

insert board4 values(1,default,default)
insert board4 values(1,default,default)
중복이 허용된다.

클러스터 색인에 유일색인으로 바꾸고 싶으면
delete board4 (테이블의 데이터 지우기)
drop index board4.board4_NC (색인 지우기)

create unique clustered index board4_CL on board4(id)
이제 중복이 허용되지 않고 클러스터드 인덱스로 바뀌었다.

6.테이블에서 중복된 컬럼값 찾기
테이블에 이미 중복된 키값이 있을 경우 유니크 인덱스(유일색인)를 만들 수 없다.그럼 테이블에 어떤값이 얼마나 중복되어 있는지 미리 볼 수 있는 방법을 알아보자.
create table member(
id int not null,
name varchar(20)
)

insert member values(1,''길동'')
insert member values(2,''철수'')
insert member values(2,''영희'')
insert member values(3,''순이'')
insert member values(3,''은정'')
insert member values(4,''성관'')

select * from memeber where id in(select id from member group by id having count(id)>1) order by id

7.복합(composite)인덱스
테이블에서 인덱스의 키값으로 사용되는 컬럼이 두개 이상일때를 말하며 두개 이상의 컬럼이 조건문에서 함께 자주 사용되는 경우 필요하다.주민등록번호 앞의 6다리와 뒤의 7자리로 두개의 컬럼에 저장할 때 유니크 복합인덱스를 사용하는것이 일반적이다.
*최대 16개까지의 컬럼이 하나의 인덱스에 사용될 수 있다.각 컬럼의 합은 전체 900바이트를 초과할 수 없다.
*(column1,column2)의 순서로 만든 인덱스는 (column2,column1)의 순서로 만든 인덱스와 키값 저장구조가 틀리다.중복이 가장 적은 컬럼을 앞에 두는순서로 만들면 인덱스를 더욱 효율적으로 사용하여 검색 속도를 높이게 된다.
*(column1,column2)의 순서로 만든 인덱스가 있을 때 조건문에서 column2만을 사용때는 인덱스를 사용할 수 없다.하지만 column1 또는 column1과 column2을 함께 사용할 때는 인덱스를 사용할 수 있다.

drop table member

create table member(
id int nuo null,
name varchar not null,
jumin1 char(6) null,
jumin2 char(7) null
)

create unique index ix_jumin on member(jumin1,jumin2)

8.covering index
전체 테이블에서 일부 컬럼만을 액세스할 때는 해당 컬럼에 대해서 넌클러스터드 인덱스를 만들어 두면 월등한 처리 성능의 향상을 볼 수 있다.
create table member2(
id1 int not null,
id2 int not null,
name varchar(20),
address char(950)
)

set nocount on
begin tran
declare @i int
set @i=0
while @i<1000
begin
insert member2 values(@i,@i,''A'',''TEST ADDRESS'')
set @i=@i+1
end
commit tran
set nocount off

(set nocount on은 한개행 적요됨 이 나타나지 않도록 하기 위함)

1)일반 index 성능 분석
create index ix_id1_id2 on member2(id1)

set statistics io on
select id2 from member2 where id1=100
set statistics io off

(여기서 statistics io on은 쿼리 성능 분석하는 것으로 입출력 처리 정보를 보여준다.I/O가 많다는 것은 속도가 느려진다는 것을 의미한다.
실행하는데 걸린 시간을 알고 싶으면 set statistics time on/off을 사용한다.)

실행한 후 메시지를 보면 논리적 읽기 수가 3으로 나온다.

set statistics io on
select id1 from member2 where id2=100
set statistics io off
실행한 후 메시지를 보면 논리적 읽시 수가 143이다.
이것이 인덱스를 사용할 때와 사용하지 않았을 때의 상황이다.

2)covering index를 생성했을때
create index ix_id1_id2 on member2(id1,id2) with drop_existing

(여기서 with drop existing 는 테이블에 이미 인덱스가 있을때 같은 이름으로 인덱스를 새로 만들때는 이 옵션을 쓴다.)

set statistics io on
select id2 from member2 where id1=100
set statistics io off

논리적 읽기수 4개로 나옴

set statistics io on
select id1 from member2 where id2=100
set statistics io off

논리적 읽기수 4개로 나옴

3)키값이 아닌 컬럼을 쿼리에서 사용할 때
select * from member2 where id2=100 where id2=100 ---모든 컬럼 선택

논리적 읽기 수 5개


9.Optimizer Hints사용법
Optimizer Hints는 쿼리를 실행할 때 데이터를 어떻게 액세스하는지를 지정하는 것으로 쿼리 옵티마이저가 선택하는 처리순서를 바꾸려고 할 때 사용한다.
1)사용법
select from table_name with (table_nint[,...n])

* index(0) 옵션은 테이블 스캔을 하게 한다.
* 하나의 쿼리에서 여러개의 인덱스를 사용할 수 있다. index(index1,index2)
* optimizer hints는 정적(static)으로 쿼리 실행계획(execution plan)을 지정하며 쿼리 옵티마이저보다 우선한다.그러므로 optimizer hints를 사용할 때는 처리 성능이 좋았더라도 테이블의 데이터나 환경 설정이 변했을 때는 쿼리 처리 성능이 저하될 수 있으므로 주의 해야 한다.

create table member3(
id1 int not null,
id2 int not null,
name varchar(20),
address char(950)
)

set nocount on
begin tran
declare @i int
set @i=0
while @i<1000
begin
insert member3 values(@i,@i%50,''A'',''TEST ADDRESS'')
set @i=@i+1
end
commit tran
set nocount off

create index ix_id1_id2 on member3(id1,id2)

일반 상태
set statistics io on
select * from member3 where id2=5
set statistics io off

논리적 읽기 수 143개

옵티마이저힌트 사용
set statistics io on
select * from member3 with(index(ix_id1_id2)) where id2=5
set statistics io off

논리적 읽기 수 24개

10.clusterd와 nonclustered 색인에 대한 이해
클러스터 색인이 넌클러스터 색인보다 빠르다.범위를 주고 찾는경우에도 클러스터 색인이 훨씬 좋은 성능을 자랑한다.넌클러스터 색인만으로 범위를 주고 찾는 것은 테이블 스캔보다 더 나쁜 성능을 낸다.그렇지만 클러스터색인은 테이블당 하나밖에 존해할 수 없기 때문에 신중히 선택해야 한다.
클러스터 인덱스가 없을때 프라이머리키 constraints를 생성하면 기본적으로 클러스터드 인덱스가 만들어진다.그러나 기본키를 무조건 클러스터 색인으로 설정하는 것은 옳지 않다.일반적으로 정렬이 되어있어야 더 좋은 속도를 낼 수 있는 컬럼을 클러스터 색인으로 만드는 것이 좋다.왜냐하면 클러스터 색인은 데이터가 미리 키값으로 정렬되어있기 때문이다.그래서 프라이머리키를 클러스터 인덱스로 사용하지 않을 경우 생성할 때 명시적으로 Nonclustered 옵션을 지정해줘야 한다.

1)테이블 만들고 클러스터 색인 만들기
create table t_index(
id int identity,
name char(20) default ''default name'',
date smalldatetime default getdate()
)

create clustered index t_index_CL on t_index(id)
go

2)테이블에 10000건의 데이터 입력
set nocount on
declare @i smallint
set @i=0
while @i<10000
begin
set @i=@i+1
insert t_index default values
end
set nocount off

3)제대로 입력되었는지 확인
select max(id) from t_index

4)색인이 언제 사용되는지 확인
select * from t_index where id=300

select문을 마우스로 드래그하여 선택한 후 메뉴의 쿼리-예상실행계획표시(Ctrl+L)를 택하고 밑에 나오는 그림에 마우스를 갖다대면 풍선도움말이 나온다.(아래부터 플랜이라 명한다.)여기에 보면 물리적연산,논리적 연산에 clustered index seek이라고 나온다.즉 색인을 사용하고 있다.

5)범위가 있는 경우
select * from t_index where id between 1 and 10000
플랜을 사용하면 clustered index seek이라고 나온다.즉 색인을 사용하고 있다.

6)클러스터 색인이 있을 경우 테이블 스캔
select * from t_index with(index(0)) where id=300
select * from t_index with(index(0)) where id between 1 and 10000
플랜을 사용하면 clustered index scan이라고 나온다.즉 테이블 스캔을 사용하고 있다.

7)nonclustered 색인 만들기
create nonclustered index t_index_NC on t_index(id)
go

8)질의 사용시 색인 사용하면 I/O얼마나 발생?
set statistics io on
select count(name) from t_index where id=300
set statistics io off

2페이지 발생

set statistics io on
select count(name) from t_index with(index(t_index_NC)) where id=300
set statistics io off

(클러스터드 인덱스가 존재하므로 optimizer hint를 명시하지 않으면 자동적으로 clusterd index가 사용된다.)

4페이지 발생
(그렇다면 순수하게 넌클러스터 색인에서만 발생한 페이지는 2페이지다.왜냐하면 넌클러스터 색인은 클러스터 인덱스에 의 다시 배열되어있으므로 넌클러스터 색인을 찾고 다시 클러스터 색인을 찾기 때문이다.)

9)범위를 주고 찾는 질의 수행할 경우 클러스터와 넌클러스터 색인의 차이

클러스터 색인 사용시
set statistics io on
select count(name) from t_index where id between 1 and 300
set statistics io off
논리적인 읽음 수 3

테이블 스캔 사용
set statistics io on
select count(name) from t_index with(index(0)) where id between 1 and 300
set statistics io off
논리적인 읽음 수 47

넌클러스터 색인 사용시
set statistics io on
select count(name) from t_index with(index(t_index_NC)) where id between 1 and 300
set statistics io off
논리적인 읽음 수 645

이것은 테이블 전체인 46페이지보다 더 많다.(테이블 크기는 exec sp_spaceed t_index를 실행해보면 알수 있다.data 368Kb를 8로 나누면 페이지가 나온다.)따라서 넌클러스터 색인은 범위를 주고 값을 찾을 때는 색인을 사용하지 않는 것보다 불리하다.

10)클러스터 색인만 삭제하면 어떤일이 발생?
drop index t_index.t_index_CL
넌클러스터 색인이 다시 만들어진다.크러스터 색인의 키 값을 가리키다가 행번호를 가리키게 되기 때문이다.

11)스토어드 프로시저의 recompile
프로시저 만들기
create proc porcGetById
@id int
as
select count(date) from t_index where id<@id

I/O양 비교
set statistics io on
select count(date) from t_index where id<2
exec procGetByID 2
둘다 3페이지의 I/O를 보인다.아무 차이 없다.

다음 질의를 이어서 실행해보자
set statistics io on
select count(date) from t_index where id<1000
exec procGetByID 1000
첫번째는 49페이지.두번째는 1003페이지.
첫번째 실행에서 색인을 사용하도록 컴파일 되었기 때문에 두번째도 넌클러스터 색인을 사용하였고 그래서 엄청난 I/O를 보이고 있다.recompile하면 된다.

set statistics io on
exec porcGetByID 1000 with recompile

12)새로운 테이블을 만들고 클러스터 색인과 넌클러스터 색인을 모두 만들자.
create table contig(
id int identity constraint PK_contig Primary Key nonclustered,
name char(20) not null,
date datetime default getdate()
)

set nocount on
declare @i smallint
set @i=0
while @i<2000
begin
set @i=@i+1
insert contig values(''NAME''+convert(char(20),@i),default)
end

select count(*) from contig
go

create clustered index contig_CL on contig(name)
go

이미 PK로 선언된 넌클러스터 색인이 있기 때문에 클러스터 색인을 만들면 넌클러스터 색인은 다시 만들어진다.그래서 항상 클러스터 색인을 먼저 만들고 그 뒤에 넌클러스터 색인을 만드는것이 좋다.

 

출처: http://kazenohi86.tistory.com/22

방장은: 사업에 관심이 많으며 www.joytingstory.co.kr을 운영 중.

 

 

 

이경철님의 파란블로그에서 발행된 글입니다.

MSSQL 색인(Index)의 종류

MSSQL 색인(Index)의 종류

서버 및 DB

자, 이제 SQL 서버의 색인을 풀어볼 차례로군요.

약간 어려워도 조금만 참고 함께 이겨나가 보도록 하지요. 그만큼 중요합니다. ^_^

색인의 종류 SQL 서버는 두 가지 종류의 색인이 있습니다. 두가지 뿐이라니 뭔가 기분 좋지요? 그 두가지는 Clustered 인덱스와 Non-Clustered 인덱스 입니다.

각각 한글로는 클러스터된 인덱스 / 클러스터되지 않은 인덱스로 불립니다. -_-;; 늘 그런 것처럼 혼용해서 코난이는 쓸겁니다. 아시져? ^_^;;

인덱스는 분명 만들어야 하는 것입니다. 이 말은

1. 만드는 비용이 있다라는 것이지요.

다음 인덱스는 공간이 필요하다고 했습니다.

2. 디스크 비용이 필요하며 항상 공간 유지를 적절해야 한다는 겁니다.

만들면 끝인가요? 아니죠. 만들었으면?

3. 유지보수에 비용이 필요하겠지요.

인덱스를 만들면 데이터를 조회하는 속도를 높일 순 있지만..

만약 데이터가 삽입 / 수정 / 삭제 된다고 생각해 보세요. 앞에서 본 인덱스의 구조를 변경하면서 재생성 해야겠지요?

4. 데이터 변경이 있을 때 비용이 추가된다는 겁니다.

앞에서 또한 말씀 드렸지만 인덱스를 사용하면 항상 빨라진다고 말씀 안드렸죠.

5. 인덱스는 항상 빠르지 않다.

인덱스를 사용하면 유용한 부분은?

5-1. WHERE절에서 참조되는 컬럼

5-2. 참조키가 설정되어 있는 컬럼

5-3. 참조키는 아니지만 JOIN에 사용되는 컬럼

5-4. 범위 검색이 일어나는 컬럼

5-5. ORDER BY로 정렬 되는 컬럼, GROUP BY로 그룹핑 되는 컬럼

5-6. TOP 구문과 함께 사용되는 SELECT 절

등에서 사용하면 좋습니다.

그렇다면? 어디에 사용하면 바보 될까요?

5-7. WHERE절에서 사용되지 않는 컬럼에는 물론 효과 없음.

5-9. WHERE절에서 변환(함수등이 사용되는)되는 컬럼과 비교시 효과 없음.

5-10. 선택도(찾을 데이터 / 전체 데이터)가 클 경우 효과 적음.

잠시후 말씀 드리겠지만.. 예를 들어 성별 컬럼과 같은 남 / 여 비율적으로 대략 50 : 50 의 구성비가 있는 컬럼이라면? 인덱스의 효과가 떨어지겠죠.

이런 주의 사항이 필요합니다.

자, 이제 인덱스 생성 구문을 실제로 봐 보도록 하지요.

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name ON { table | view } ( column [ ASC | DESC ] [ ,...n ] ) [ WITH < index_option > [ ,...n] ] [ ON filegroup ] < index_option > ::= { PAD_INDEX | FILLFACTOR = fillfactor | IGNORE_DUP_KEY | DROP_EXISTING | STATISTICS_NORECOMPUTE | SORT_IN_TEMPDB }

기분 좋게도 그다지 길지 않지요? 주요한 몇 가지를 말씀 드리면..

UNIQUE를 지정해 고유 인덱스 또는 고유하지 않은 인덱스 생성이 가능합니다.

색인의 종류는 두 가지로 CLUSTERED | NONCLUSTERED 중에 지정이 가능 합니다.

table이나 view에 생성이 가능합니다. SQL 2000의 새로운 기능으로 View에 인덱스 생성이 가능하지요. 뷰 강좌를 참고해 보세요.

컬럼을 ASC 또는 DESC로 정렬해 생성 가능합니다. 특히 클러스터드 인덱스를 생성시 유용하며 ORDER BY 구문과도 밀접합니다. SQL 2000의 새로운 기능입니다.

다음 인덱스 옵션에서

PAD_INDEX는 중간 레벨을 적절히 비워 데이터 삽입 등에 대비하기 위한 것이며

FILLFACTOR는 리프 레벨을 적절히 비워 역시 삽입 등에 대비하는 것입니다.

샘플에서 이야기를 해 드리도록 하지요.

IGNORE_DUP_KEY는 중복되는 값을 무시한다는 의미이구요.

DROP_EXISTING은 이미 존재하는 인덱스가 있으면 제거하고 재생성하라는 의미입니다.

STATISTICS_NORECOMPUTE는 인덱스를 사용할지 안할지 쿼리 최적화기가 결정하는데, 이 근거는 통계치 데이터라는 녀석으로 판단하게 됩니다.

이 통계 데이터는 기본적으로 자동적으로 항상 update 되는데, 이 통계 데이터를 자동 업데이트 하지 말라는 옵션입니다.

SORT_IN_TEMPDB TEMPDB상에서 정렬하라는 옵션입니다. 예를 들어 데이터와 인덱스가 같은 물리적인 디스크에 있고 데이터가 한 1000만건 정도 된다면 인덱스 생성에 대단히 많은 시간이 소요 됩니다.

이때 TEMPDB에서 인덱스 생성시 필요한 정렬작업을 시키고 사용자 데이터베이스의 물리적인 디스크와 TEMPDB 쿨리적인 디스크가 틀리다면 인덱스 생성시 부하를 줄일 순 있지만 TEMPDB에 다른 불필요 공간이 생기니 주의하셔야 하지요.

끝으로 ON filegroup은 인덱스 역시 데이터라고 말씀 드렸습니다. 데이터베이스 강좌에서 filegroup 을 적절히 분산시켜 생성해 속도를 높일 수 있다고 말씀 드린 것처럼 인덱스 역시 적절한 filegroup에 위치시켜 최적의 속도를 낼 수 있게 할 수 있지요.

백견이 불여일타라고 우선 한번 맹거 보도록 하지요.

CREATE DATABASE konanTestDB GO USE konanTestDB GO --테이블 생성 CREATE TABLE konan_test_table( konan_id int IDENTITY (1, 1) NOT NULL , konan_data char (50) NOT NULL , konan_date char (50) NOT NULL ) GO --10000건의 샘플 데이터 삽입 set nocount on GO declare @i int set @i = 0 while @i < 10000 begin --WAITFOR DELAY '00:00:01' insert into konan_test_table values ( @i , convert(varchar, datepart(yy, getdate())) + '년 ' + convert(varchar, datepart(mm, getdate())) + '월 ' + convert(varchar, datepart(dd, getdate())) + '일 ' + convert(varchar, datepart(hh, getdate())) + '시 ' + convert(varchar, datepart(mi, getdate())) + '분 ' + convert(varchar, datepart(ss, getdate())) + '초 ' + convert(varchar, datepart(ms, getdate())) + '미리초 ' ) set @i = @i + 1 end GO --10초. set nocount off GO --샘플 데이터 조회 SELECT TOP 100 * FROM konan_test_table SELECT COUNT(konan_id) FROM konan_test_table

대략적인 샘플 데이터 100건과 만건이 잘 들어간게 보일 겁니다.

여기서 어느 정도의 IO 비용이 소요되는지 보도록 할까요?

물론 시간 / IO 비용 모두가 중요하지만 우선 IO 비용만 보도록 하지요.

--IO통계 보기 SET STATISTICS IO ON SELECT * FROM konan_test_table WHERE konan_id = 5000 --IO통계? SELECT * FROM konan_test_table WHERE konan_id < 10 --IO통계? SELECT * FROM konan_test_table WHERE konan_id < 30 --IO통계? SELECT * FROM konan_test_table WHERE konan_id < 1000 --IO통계? --IO통계 끄기 SET STATISTICS IO OFF

메시지 부분을 보시면 IO 통계를 확인하실 수 있을 겁니다.

이제 인덱스를 생성해 보도록 하지요.

--간단한 인덱스 생성 CREATE INDEX idx ON konan_test_table (konan_id) GO

인덱스 생성은 잘 되셨을 거구요. 다시 데이터를 조회해 보도록 할까요?

--IO통계 보기 SET STATISTICS IO ON SELECT * FROM konan_test_table WHERE konan_id = 5000 --IO통계? SELECT * FROM konan_test_table WHERE konan_id < 10 --IO통계? SELECT * FROM konan_test_table WHERE konan_id < 30 --IO통계? SELECT * FROM konan_test_table WHERE konan_id < 1000 --IO통계? --IO통계 끄기 SET STATISTICS IO OFF

통계를 봐 보세요. 어떠세요?

아마도 WHERE konan_id < 30 부터 인덱스가 없을 때와 비슷한 수치가 나올 겁니다.

이것은 뭘 말하는 걸까요?
다음처럼 실행계획 표시를 하시거나 또는 컨트롤+K를 눌러 실행계획을 봐 보도록 하지요.

바로 위의 같은 쿼리를 수행해 보면,

위의 그림처럼 실행 계획을 보실 수 있습니다. 중요한건 Index Seek를 했다는 점이지요.

다음 쿼리에서 수행 계획을 보면?

이렇게 konan_id < 30을 봐 보시면 Table Scan을 하는 것을 알 수 있습니다.

분명 어떤 것은 인덱스를 타고 어떤 것은 인덱스를 타지 않지요?

앞에서 말씀드린 건 선택도라고 해서(찾을 데이터수 / 전체 데이터수)로 판단되게 됩니다. 천천히 말씀 드리지요.

다음 인덱스의 정보를 보려면 어떻게 할까요?

--인덱스 정보 조회 sp_helpindex konan_test_table

그러면 인덱스의 정보를 확인할 수 있을 겁니다.

흥미있게 보실 부분으로 인덱스의 종류를 지정하지 않으면? 뒤에서 보시겠지만 넌클러스터드 인덱스로 잡히게 됩니다. 참고하세요.

인덱스의 변경 구문은 없으며 DROP INDEX 구문을 이용해 인덱스를 제거할 수 있습니다.

--인덱스 제거 DROP INDEX konan_test_table.idx

물론 앞에서 말씀드린 CREATE INDEX구문의 DROP EXISTING 구문으로 존재하는 인덱스를 지우고 생성할 수 도 있습니다.

우선 인덱스를 생성하고 돌려는 보셨네요.

이제 본격적인 SQL서버의 인덱스에 대해서 알아 보도록 할까요.

클러스터드 인덱스

클러스터드 인덱스는 간단히 인덱스의 리프레벨이 데이터 페이지와 같은 겁니다.

뭔소리냐구요? 천천히 설명 드리지요.

이런 화면을 생각해 보세요.

클러스터드 인덱스는 물리적으로 행을 재배열해 리프 레벨이 차곡차곡 오른쪽의 데이터 페이지처럼 쌓이게 합니다.

그럼 인덱스를 봐 볼까요?

인덱스가 설정된 컬럼의 값이 순차적으로 재배열되게 되므로 왼쪽에 보시는 인덱스 페이지는 단지 키값과 페이지의 번호만이 지정되게 됩니다.

자, 그럼 우리가 만약 5번 강해원을 찾으려 한다고 생각해 보도록 하지요.

이때는 먼저 클러스터드 인덱스의 값을 보니 4 < 5 < 7 사이가 되므로 4 - 2 인 2번 페이지로 가게 됩니다. 2번 페이지로 가서 바로 5번 강해원을 찾게 되지요.

대략 이러한 그림으로 이루어 지게 되는 겁니다. 만약 12번 김태영을 찾는다면?

이때는 역시 10 < 12 < 13 이므로 4페이지로 가서 12번 김태영을 찾을 수 있게 되겠지요.

이것이 가능한 이유가 뭘까요?

바로 물리적으로 행이 정렬되어 있기 때문에 가능한 거지요. 만약 물리적으로 행이 재배열 되어 있지 않다면? 이런 작업은 불가해 지는 겁니다.
아울러 물리적으로 행들이 재배열 되어 있으므로 범위 검색에 대단히 유용합니다.

이 말은 선택도가 어느 정도 높아도 - 클러스터드 인덱스의 경우 30% 정도도 가능 - 인덱스를 이용해 데이터를 조회할 수 있게 되지요.

아울러 클러스터드 인덱스가 저렇게 테이블에 하나 생성되어 있는데요. 만약 클러스터드 인덱스를 하나 더 테이블에 만들고 싶다면 어떨까요?

안타깝게도 다음번 클러스터드 인덱스는 테이블을 다시 재구성하고 싶겠지만.. 이미 한번 테이블이 정렬된 상태로 재구성되어 있으므로 불가능해 집니다.

오로지 테이블에 단 1개의 클러스터드 인덱스만 생성이 가능하니 주의하셔야 합니다.

그럼 앞의 샘플쿼리를 클러스터드 인덱스로 생성하고 장난을 조금 쳐 볼까요?

--인덱스가 존재하면 지울것 DROP INDEX konan_test_table.idx --클러스터드 인덱스 생성 CREATE CLUSTERED INDEX idx ON konan_test_table (konan_id) GO

이렇게 클러스터드 인덱스를 생성할 수 있지요. 그런데 특이하게도 SQL 서버는 클러스터드 인덱스에 대해서 대단히 높은 우선권을 부여합니다.

예를 들어 이럴땐.. 풀스캔을 해도 좋을 것 같은데... 클러스터드 인덱스를 써서 검사할 경우가 있지요.

이는 말씀 드린대로 SQL 쿼리 최적화기가 판단하는데 테이블의 크기가 작거나(로우의 건수가 아닌 전체적인 크기) 또는 클러스터드 인덱스의 키값 컬럼이 작을 경우 종종 발생 합니다.

실제 현업에서는 범위검색에 종종 이 클러스터드 인덱스를 두게 되므로 정상적으로 잘 동작하게 되지요.

또한 클러스터드 인덱스는 비교적 넌클러스터드 인덱스보다 크기가 작습니다. 아울러 클러스터드 인덱스는 크기를 대략적으로 예측할 수 있는 인덱스이기도 하지요.

다음 넌 클러스터드 인덱스를 봐 보도록 할까요?

넌 클러스터드 인덱스

실제 테이블의 데이터가 항상 순차적으로 들어가 있는 것은 아닙니다.

관계형 데이터베이스에서 순차라는 것은 사실 의미가 없습니다. 관계형 데이터베이스의 순차 유지는 오로지 ORDER BY에 적절히 이용되는 컬럼을 잘 구성해야만 하는 것이지요.

예를 들어 실제 진짜 SQL서버에 들어가 있는 테이블의 순서는 다음과 같을 겁니다.

이런 식의 데이터가 실제로 들어가 있게 되지요. 그렇다면 페이지 내부는 어떤 식일까요?

대략적으로 위의 그림과 같은 식의 데이터가 들어가 있게 됩니다.

실질적인 데이터 페이지이지요. 이런 데이터 페이지들의 번호에 클러스터드 인덱스를 만약 생성한다면 어떻게 될까요?

이럴 경우는 RID라는 녀석이 필요하게 됩니다. 간단히 RID는 로우의 구별하는 특정 값이라고 생각하시면 됩니다.

넌 클러스터드 인덱스는 바로 이 RID를 가지고 데이터 페이지를 포인팅 하게 되지요.

RID 샘플을 보시면 다음과 같은 식입니다.

여기서 RID의 첫번째 1은 화일 그룸을 의미하게 됩니다.

그 다음 숫자는 데이터 페이지 번호이며 마지막 세번째 숫자는 페이지 옵셋으로 정확히 페이지의 한 로우를 포인팅(Pointing)하게 되지요.

또한 이 인덱스 페이지는 다음과 같은 형식으로 분할 되겠지요.

이러한 형식이 넌클러스터드 인덱스에서 과연 어떻게 사용 될까요?

실제 구조를 그려 보도록 하지요.

이러한 식으로 생성이 되게 됩니다.

루트 레벨은 인덱스 페이지 7이며, 중간 레벨은 엔덱스 페이지 1,2,3,4 이고, 실제 데이터페이지는 1,2,3,4,5 데이터 페이지가 되지요.

자, 값을 네비게이션 해 보도록 합시다. 만약 제가 3번 이승용을 찾으려 한다고 생각해 보지요.

1 < 3 < 5 이므로 1페이지로 가야 겠지요? 같더니 3은 4페이지 2번째 로우에 있다고 합니다. 바로 4페이지로 가서 두번째 로우를 컨택하는 거지요.

또한 만약 8 차영인을 찾으려 한다면

5 < 8 < 9 이므로 인덱스페이지 2로 가서 8 차영인을 보니 1-3-2라고 되어 있습니다. 데이터 페이지 3의 2번째 로우를 포인팅 하게 되지요.

이것이 넌클러스터드 인덱스 입니다.

넌클러스터드 인덱스는 이렇게 포인팅 정보를 가지게 되므로 인덱스의 크기가 커지게 됩니다.
아울러 선택도가 높으면 바로 쿼리 최적화기는 이 넌클러스터드 인덱스를 사용하지 않게 되지요. 일반적으로 3% 이내 정도면 이 넌클러스터드 인덱스를 사용하지 않게 됩니다.

넌클러스터드 인덱스는 데이터페이지를 물리적으로 재배열하지 않으므로 여러개의 인덱스를 생성할 수 있습니다. 최대 생성 가능 갯수는 249개 입니다.

두 차이를 비교한다면 다음과 같겠지요.

구분	클러스터드 인덱스	넌클러스터드 인덱스
차이	물리적으로 행을 재배열	물리적으로 행을 재배열하지 않는다.
크기	비교적 작다.	클러스터드 인덱스보다 크다.
선택도	30% 정도면 사용한다.	3% 이내면 사용한다.
최대 갯수	테이블당 1개	테이블당 249개

다음으로 클러스터드 인덱스가 있는 테이블의 넌클러스터드 인덱스를 알아 보도록 하지요.

물론 당연히 클러스터드 인덱스와 넌 클러스터드 인덱스가 있을 때 넌클러스터드 인덱스를 조회할 경우겠지요?

현재는 번호에 클러스터드 인덱스가 걸린 상태이며, 이름에 넌클러스터드 인덱스가 설정된 상태입니다.

이런 식으로 구성이 되게 됩니다. 먼저 알아 두셔야 할 것은!!

넌클러스터드 인덱스의 RID는 더 이상 RID가 아닌 클러스터드 인덱스의 키값을 가지게 됩니다.

좀 더 간단히 실제 조회를 해 보도록 하지요. 넌클러스터드 인덱스가 걸린 이름 컬럼의 유병수라는 이름을 조회해 보도록 합시다.

박훈 < 유병수 < 이수선 이므로 넌클러스터드 중간 레벨의 2페이지로 가게 될겁니다.

여기서 유병수를 찾으니 유병수는 클러스터드 인덱스 10번 키값을 가지고 있습니다. 10이라는 값을 가지고 클러스터드 인덱스 페이지로 가니 10은 데이터페이지 4에 있다고 합니다.

데이터페이지 4로 가니? 바로 10 유병수를 찾을 수 있게 되지요.

네, 맞습니다. 바로 이런 그림이 되는 것이지요. 만약 한기환을 찾으려 한다면 어떻게 될까요?

한기환 < 한기환 이므로 넌클러스터드 중간 레벨의 4페이지로 가게 되겠죠?

클러스터드 인덱스의 키값 2이니. 1 < 2 < 4 이므로 1 데이터 페이지로 가게 되며 1 데이터 페이지에서 2번 한기환을 발견하게 될 겁니다.

어떠세요? 조금 감이 잡히시나요? ^_^ 내부적인 부분이라 조금 어렵기도 하시겠지만 중요한 부분이랍니다.

다음 장에서는 실제 색인을 여러가지 방법으로 생성하고 수행해 볼 겁니다. 수고하셨습니다.

 

출처: http://kazenohi86.tistory.com/23?srchid=BR1http%3A%2F%2Fkazenohi86.tistory.com%2F23

방장은: 사업에 관심이 많으며... www.joytingstory.co.kr 을 운영 중....

이경철님의 파란블로그에서 발행된 글입니다.