안녕하세요.
MSSQL 공부중에 궁금한 점이 있어, 최대한 정리 하여 질문을 몇개 뽑아보았습니다.
인덱스 저장 구조에 따라 클러스터 인덱스와 비클러스터 인덱스가 존재하는 것으로 알고있습니다.
1. "클러스터 인덱스의 리프 페이지가 곧 데이터 페이지"라는 것은
클러스터 인덱스 생성시
물리적으로 루트 노드 페이지와 브랜치 노드 페이지만 생성이 되며 리프페이지 생성되지 않고,
브랜치 노드에서 가리키는것은 실제 데이터페이지가 되는 것인가요?
(즉 클러스터 인덱스 생성시 물리적인 용량 증가는 "기존데이터페이지 + 루트노트 페이지 + 브랜치노드 페이지" 인가요?)
2. 클러스터 인덱스가 생성된 테이블은 데이터 삽입시 정렬 순서를 맞추는 것으로 알고있는데,
먼저 인덱스 노드들을 탐색한 다음, 순서에 맞는 위치에 적재하거나 혹은 적재할 위치의 데이터페이지 공간이 모자를 시
노드와 데이터페이지를 추가한 뒤에 삽입 작업이 완료되는 것인지
아니면 빈 공간이나 마지막 데이터페이지에 먼저 값을 추가하고 작업 완료한 다음 인덱스의 노드들을 정렬하는것인가요.
3. 랜덤 엑세스(I/O)는 테이블스캔이나 순차적 I/O가 아닌 상황에서 건 바이 건으로 페이지단위의 입출력이 발생하는 것으로
알고 있는데, 그렇다면 랜덤 엑세스는 클러스터 인덱스가 아닌 비클러스터 인덱스에서만 발생하는 것인가요?
질문이 많아서 죄송스럽네요.^^;
좋은 답변 많이 부탁드리겠습니다. 감사합니다.
Comment 11
-
항해자™
2014.06.27 22:41
-
초탁
2014.06.29 00:45
좋은 답변 감사합니다.
구조적인 문제에서 헷갈리는 부분이 많았는데
참조해주신 사이트 감사히 보겠습니다.
-
초탁
2014.07.01 02:10
의 '바.클러스터형 인덱스/IOT' 항목을 보시면
'별도의 테이블을 생성하지 않고 모든 행 데이터를 인덱스 리프 페이지에 저장한다는 점이다.' 라는 문구가 있는데
1. 제가 이해하기로는 비클러스터 인덱스는 인덱스 + 테이블의 개념이고,
클러스터 인덱스는 그냥 정렬순서가 정해진 테이블의 개념(클러스터인덱스=클러스터테이블) 같은데 맞는 것일까요
2. 그렇다면 힙테이블에서 클러스터인덱스 생성시 힙테이블의 데이터들이 정렬되면서 리프 노드 페이지에 값이 저장되는 것 인가요?
(그 뒤 힙테이블은 삭제되는 것인가요?)
3. 클러스터 인덱스의 랜덤 엑세스 부분은 이해하기하 어렵습니다...
페이지 단위의 접근을 하기때문에 애초에 행주소 접근을 하는 비클러스터 인덱스에서
쿼리가 커버드인덱스가 아닐때 랜덤엑세스가 발생하는 것이 아닌지 궁금합니다.
으헝 잠이안와
-
향지
2014.07.01 11:37
다른 사람이 답변 달아도 될지 모르겠네요 ㅎ
1. 비 클러스터드 인덱스는 책의 맨 뒷장에 있는 색인으로 보시면 되시고
클러스터드 인덱스는 영어 사전으로 예를 들었을때 사전 색인+영어 사전 본문 으로 보시면 될 것 같습니다.
2. 막 써놓은 단어장을 사전으로 새로 만든다고 생각하시면 되실까요... 리프 노드에 값이 저장됩니다.
추가적으로 기존 단어장 기준으로 색인(인덱스)이 있었다면 요 색인 또한 새로 만들어집니다!
3. 랜덤 엑세스는 제가 알기로는
네이버 사전 참조
임의의 위치에 직접 액세스하여 데이터를 읽고 쓰는 것이다. 데이터를 처음부터 순차적으로 읽어나가다 목적한 데이터를 찾는 시퀀셜 액세스와 달리 미리 준비된 인덱스(어드레스 대조표)를 참조하여 직접 데이터를 읽고 기록하는 것을 말한다.
요런 뜻으로 알고 있습니다.
-
초탁
2014.07.01 16:59
답변감사합니다.
단지 말씀해주신 내용은 알고있는 내용입니다.
오라클을 사용하다보니 MSSQL과 비슷한 용어도 다른 개념이 있어서 좀더 확실히 정리해보고자 드린 질문이었습니다.
1번의 질문은 비클러스터는 인덱스페이지(루트,브랜치,리프)가 테이블과 별도로 인덱스가 생성되는것 같은데
클러스터 인덱스의 경우는 인덱스 생성시 힙테이블의 값들이 리프노드 자체에 저장이된다면
클러스터 인덱스라는것은 그자체가 정렬된 테이블이 되는것과 같은 개념인지 문의드린것입니다.
즉, 물리적으로 비클러스터가 테이블+인덱스라면
클러스터 인덱스는 생성하였을때 위의 설명과 같이 별도의 테이블이 없다면
힙테이블의 데이터들이 인덱스 리프페이지에 정렬된 상태로 들어가고
기존의 힙테이블은 삭제되고 클러스터 인덱스 자체가 테이블의 개념이 되는 것인지,
삭제되지 않는다면 기존 테이블용량 + 모든 행데이터가 들어간 리프페이지로 인해 2배의 용량이 되는 것인지 알고싶습니다.
2번의 경우, 리프노드에 값이 저장된다면 기존에 있던 힙테이블. 즉 클러스터 인덱스가 없는 테이블의 값은 리프노드에
저장후 삭제되는것인지 알고싶습니다.
3. 대부분의 블로그나 자료들에서는 랜덤엑세스를 줄여야한다는 표현이 많이 적혀있는데
이 랜덤엑세스라는것이
순차 페이지 접근이 아닌 비클러스터 인덱스의 레코드 주소를 통한 접근으로 발생하는 I/O를
통틀어서 랜덤엑세스라고 말하는건지
아니면, 쿼리가 커버드인덱스가 아닐때 추가로 레코드를 가져오기위해
레코드 주소로 접근하여 발생하는 페이지I/O를 랜덤엑세스라 하는것인지 궁금합니다. 여쭙고 싶었습니다.
-
향지
2014.07.01 18:07
1,2. 클러스터드 인덱스는 테이블 자체를 인덱스처럼 만드는것입니다.
B-Tree를 혹시 보셨다면 각 level은 정렬이 되어 있다는걸 알 수 있구요.
클러스터드 인덱스 leaf 부분에는 데이터가 저장되어 있고
클러스터드 인덱스의 leaf 부분은 정렬된 테이블과 비슷합니다.
기존의 heap 테이블을 따로 두진 않기 때문에 용량이 2배가 되진 않습니다.
3. 제가 알고 있는 데이터베이스에서 랜덤 엑세스는 레코드 주소를 통한 접근입니다!
-
초탁
2014.07.01 18:16
ㅠ_ㅠ 감사합니다.
일반적으로 인덱스를 추가한다는 개념은 기존 테이블 + 인덱스 페이지라고 생각해서 클러스터 인덱스에서
많이 헷갈렸네요. 클러스터 인덱스를 생성한다는것은 일반적으로 인덱스를 추가한다는 개념이 아닌 힙테이블을 B-TREE 인덱스 구조로 변경한다는 개념이었군요.
너무 당연한것같은데 너무 헷갈렸습니다.
그리고 랜덤 엑세스가 주소를 통한 접근이라면, 랜덤 엑세스라는것은 없애는건 사실상 테이블 풀스캔밖에 없는데
불필요한 엑세스를 줄이기위한 개념인것 같네요.
감사합니다.! ♥
-
항해자™
2014.07.01 20:07
제가 없는 사이에 덧글이 많이 달렸군요^^;;
1번과 2번은 넘어가도 될것 같구요,,
3번은 다르게 이해하고 있는 듯 합니다..
랜덤억세스는 인덱스 구조 때문에 발생하는게 맞다고 보셔야 할듯 합니다.
예를 들어 아래와 같은 인덱스를 만들었다고 합시다.
create uinque nonclustered index ix_tTest_02
on dbo.tTest
(cWorkDate
,cUserNo
)
그런데 이런 쿼리를 날립니다.
select cWorkDate,cUserNo
from dbo.tTest
where cWorkDate between 20140101 and 20140107
and cUserNo = 1000001
이러면 랜덤 억세스 발생하지 않을까요?? 클러스터드 인덱스라도 달라지지 않습니다.
엑셀 장표를 떠올려 봅시다,,, 날짜별로 정렬되어 있고 거기에 유저번호가 10만개씩 있습니다.
검색하려는 날짜를 모두 찾고 거기서 유저번호를 찾아야 하겠지요??
아래와 같은 쿼리는 어떨까요??
select *
from dbo.tTest
where cWorkDate = 20140701
and cUserNo in (1000001,3001001,...)
(참고: 넌클러스터 인덱스는 데이터를 막 넣으면서 어디에 넣었는지 위치만 적어둡니다.)
인덱스 시크는 했지만, 유저번호가 다수개 입력으로 들어왔습니다.
그리고 커버되지 않았기 때문에 데이터 페이지도 가야죠?? 좀 바쁘겠네요ㅎㅎ
혹시 틀린 부분 있으면 다른분께서 수정부탁드립니다.
-
초탁
2014.07.01 20:30
답변 감사합ㄴ디ㅏ.^^
우선 제가 잘못알고있는건지 모르겠지만
첫 쿼리에서 랜덤엑세스가 발생할 이유가 없다 보입니다.
인덱스에서 모두 확인할수 있는 값이 아닐까요?
두번째 쿼리같은 경우는 값이 들어온게 문제가 아니라
SELECT문의 * 표시때문에 나머지 값들을 가지러 데이터 페이지를 확인하러 가는
부분이 문제로 보여집니다.
-
항해자™
2014.07.02 00:58
음,,, 글을 다시 보니 제가 예제를 잘 못 선정한것 같네요;;첫번째 예제는 scan이 발생하는 예제네요;;아래 글을 참고하시면 도움이 될듯 합니다.그리고 랜덤 억세스를 다시 정리해 보겠습니다.랜덤 읽기가 문제를 일으키는 경우는 메모리보다 약 50배 느린 디스크에서가 문제인데,SQL Server는 B-Tree로 되어 있으니, 페이지 스플릿 발생(중간에 끼어들기)이 많아지면,끼어든 익스텐드들의 페이지는 뒷쪽으로 또 중간에 스플릿이 발생하면 뒷쪽으로 가게됩니다.이 상황에서 클러스터 인덱스 스캔이라는 이터레이터가 나왔다고 가정해 봅시다.cold cache 상태에서 사실상 디스크는 완전한 랜덤 읽기가 됩니다.그래서 이러한 문제를 해결하기 위해 인덱스 리빌드를 하는것 입니다.그러나 warm cache 상태에서 메모리상의 랜덤 읽기는 거의 의미가 없습니다.메모리에서는 page fill factor가 가장 중요하고, 이렇게 cold와 warm 일때의 랜덤읽기가 있습니다.이 랜덤읽기가 성능에 영향을 미치는 것은 조각난 상태에서 cold와 warm의 성능비교를 하면 되고,위 글의 결과는 "SQL Server 운영과 튜닝"의 "인덱스 억세스 패턴"에 잘 설명되어 있습니다. -
초탁
2014.07.02 17:52
감사합니다.^^ 많은 도움이 되었습니다.
2번 페이지 분할이 발생하고 데이터가 저장됩니다.
3번 클러스터 인덱스라도 랜덤읽기는 발생할 수 있습니다,,,
아래 사이트의 글들을 참고해 보세요,,,
http://databaser.net/moniwiki/wiki.php/ClusteredIndexSeek