Covered Index, Include Index, Bookmark lookup

허접씨와 Master님의 QnA 입니다.
어리버리 허접씨와 SQL 그루 이신 C 모님(MVP)과의 대화 입니다.

클러스터드 넌클러스터드외의 인덱스에 대한 용어 정리 및 이해에 도움이 될꺼 같아 올려봅니다. 아무래도 위의 3가지 용어에 대해 국내 블로그에도 피상적으로만 올라와 있고, BOL에도 너무 이해하기 어렵게 나와 있어서 그냥 한번 쏴악 이해하는 의미로 대화를 나눴습니다.

결론은 맨 하단에 있습니다.

Master님의 말:
커버드도 넌클에만 있고
간단하게 말해서 데이터 단을 읽지 않는...
북마크 룩없이 없는 인덱스가 커버드 인덱스야
조회하는 모든 데이터를 인덱스단에 올린거

허접님의 말:
그럼 클러스터드 인덱스에 선언된 절을 넌클러스터드 인덱스로
넣어주면
그게 커버든가요 ?

Master님의 말:
클러스터드랑은 상관고
클러스터등에 선언된거는 내부적으로는...
넌클러스터의 키니까 자동으로 포함되지

허접님의 말:
북마크 룩업이라는게 넌클러스터드 인덱스 서치 하고 나서 해당 포인터를 가지고 클러스터드 인덱스 서치하는거죠?
근대 어케 북마크 룩업이 없을 수 있어요?

Master님의 말:
클러스터드인덱스 서치라기보다는...
거기 달린 데이터를 가져오는거지
인덱스에 없는 데이터들...
그런데 조회하는 셀렉트 절에 모든 컬럼을...
넌 클러스터드 인덱스에 가지고 있으면...
데이터가 있는 클러스터드 인덱스쪽을 조회안해도 나오자너
인덱스만 조회해도....
그걸 커버드인덱스라고 그래...
인덱스에서 모든 데이터 조회가 가능(커버) 되니까

허접님의 말:
잠깐 이해가 될려구 하는데
아무튼 데이터는 클러스터드 인덱스를 타니간 북마크 룩업은 무조건 일어난다고
해야 하는거 아닌가요?

Master님의 말:
아니지...
북마크 룩업이라는게 비용이 많이드는 작업이야
클러스터드 인덱스를 만들게 되면...
힙과 달리
인덱스 끝에...
데이터가 달리자너
물리적으로

허접님의 말:
냅

Master님의 말:
그런데 넌클러스터 인덱스에 가지고 올 데이터의 컬럼이 없다면...
데이터를 가져오기 위해서 해야될게 뭐겟어?
젤 빠른 인덱스를 찾겟지

허접님의 말:
냅

Master님의 말:
다른 인덱스 중에서 그 데이터를 가진 인덱스가 없다면...
클러스터 인덱스를 이용해서
밑에 달린 데이터를 가져와야겠지?
그게 젤 빠를 테니까

허접님의 말:
냅

Master님의 말:
그때데이터를 가져오는 작업이 논리적 이름이 룩업이야

허접님의 말:
아~

Master님의 말:
하지만 인덱스에서 그 데이터를 다 가져올수 있다면...
그 작업이 빠지겟지?

허접님의 말:
음 클러스터드 인덱스 스켄이랑 비슷하게 들리는데...

Master님의 말:
클러스터드 인덱스 스캔은...
테이블 스캔이지~
클러스터드 인덱스가 있으면...
테이블 스캔이 클러스터드 인덱스 스캔이 되니까...

허접님의 말:
여튼 검색 조건에 잇는 컬럼이 인덱스가 없어서
클러스터드 인덱스를 통해
데이터를 조회하니깐
이건 클러스터드 인덱스 스켄이랑 동일하게 들리는데요
;;

Master님의 말:
응 그 데이터 조회가 없는게 커버드 인덱스

허접님의 말:
아 아예 인덱스 스켄이 없는 경우를
커버드 인덱스라구 한다구요?

Master님의 말:
음...
넌 클러스터드 인덱스에는 데이터가 없지?
인덱스된 컬럼만 있고

허접님의 말:
내

Master님의 말:
넌 클러스터드 인덱스만 있을경우에는...

허접님의 말:
아하... 알았다....

Master님의 말:
클러스터 없고
말속에 길이 있어요

허접님의 말:
그니깐

Master님의 말:
커버드 인덱스(인덱스로만 조회)

허접님의 말:
select c1, c3 where c2=3
이렇게 할경우에

Master님의 말:
누가 누굴 카바한다는말하고 같은 의미의
c1, c2, c3가 넌클러스터드인덱스로 있으면...
다른 컬럼이 아무리 많아도

허접님의 말:
create index idx 1 (c2, c1, c3) 일케 되어 있음

Master님의 말:
다른데는 조회할일이 없자너

허접님의 말:
어차피 c1 c2 는 인덱스에서
잡혀 있으니깐
바로 조회가 된다는말?

Master님의 말:
엉...

허접님의 말:
클러스터드 인덱스 않타구요

Master님의 말:
인덱스의 데이터로만 조회가 되는

허접님의 말:
인클루드 인덱스는요?

Master님의 말:
사실 인덱스도 물리적은 데이터 모음이자너
그건 데이터를 떼서
가지고만 있는거지

허접님의 말:
그럼 인덱스 공간 차체가 어마 어마 하게 크겠네요

Master님의 말:
저장소와 별게로
커버드 인덱스는 젤 빠른 조회중 하나야
전같이 테이블이 7기가고
거기서 조회되는 컬럼이 많지 않은 작업이 있을경우...
70메가 또는 700메가만되도...
얼마나 빠르겠어
7기가서 하는거랑

허접님의 말:
그렇죠
근대
커버드인덱스
아까 같을때
각각 c1, c2, c3 에 대해서
idx1, idx2, idx3 로 선언을 하더라도
커버드 인덱스를 타죠?
동일 인덱스로 선언 않해두요

Master님의 말:
인덱스가 있음...
먼저 인덱스를 찾으니까...
건수나 뭐 분포도 따라서 달라질수도 있겠지
옵티마이저가
판단하기 나름
100%라고는 말못하지

허접님의 말:
c1,c2,c3를 idx1 으로 선언하거나 각각 인덱스로
잡거나
커버드로 처리 되겠죠 ?

Master님의 말:
뭐 의미만 놓고 보면...
인덱스로만 되면 커버드지

허접님의 말:
아
냅
글구 인클루드는 인덱스에 데이터를 넣어버린다고 알고 있는데
맞나요?
리프에 데이터를 쑤셔 넣은

Master님의 말:
잠시만...
아까 말했다 시피...
인클루드는...
복사본을 만드는거야
단 인클루드열은...
인덱스크기에서 빠져서 엔진에서 계산되고~
장점은 인덱스의 한계를 벗어나서 만들수 있다~
16개열 900 바이트
단점은 무리한 인클루드는 리프단에 복사되기 때문에 저장공간및 i/o추가 발생~
커버드인덱스와의 차이는...
비슷하긴 한데...
커버드 인덱스도 인덱스기 때문에
위에서 말한 인덱스의 한계를 벗어날수 없음

허접님의 말:
형님 점심 먹고 나서
제가
정리한 걸로 말씀 드릴께요
ㅎ,.ㅎ
대충 정리 완료

Master님의 말:
어~~~
밥먹고 왔당

허접님의 말:
형님 그럼 커버드 인덱스는
create index idx1 (c2, c1, c3) 이렇게 만드는것만
해당하네요
따로 인덱스 만들어서는 커버드가 아니네요
그럼 c2로 정렬이 되고
이미 c2의 인덱스에 c1, c3 값이 있으니
클러스터 인덱스를 안타고 바로 데이터를 접근
따로 인덱스를 만들게 되면
C2만 사용하게 되고 그럼 클러스터 인덱스를 통해 c1과 c3를 접근해야하고
즉 create index idx1 (c1) , create index idx2 (c2), create index idx3 (c3) 은 정확하게 커버드 인덱스라 부를 수 없네요

Master님의 말:
ㅎㅎ 그리고 그렇게 인덱스를 만들지도 않지

허접님의 말:
즉 커버드는 create index idx1 (c2, c1, c3) 를 말하는 거죠?
create index idx1 (c1) , create index idx2 (c2), create index idx3 (c3) 이건 커버드가 아니죠?

Master님의 말:
뒤에꺼 같이 만들면 정렬에 따라서 인덱스 끼리 머지하거나 해서 플랜이 나올껄

허접님의 말:
제가 정리를 하자면
검색조건에
검색조건이
인덱스로 지정이 되어 있고
Select 절에
해당 인덱스의 하위절로 정의된 컬럼들만 요구하고 있다면
해당 데이터는 클러스터드 인덱스를 검색하지 않고 인덱스만으로 데이터를 반환한다.

Master님의 말:
맞습니다
그것이 커버드 인덱스입니다

허접님의 말:
원칙은 커버드는 해당 열을 포함하는 인덱스를 만들어야 하나
간혹 옵티마이저에 의해
인덱스들을 조인 혹은 머지해서
커버드 인덱스로 처리하기도 한다.
인클루드 인덱스는 커버드 인덱스와 비슷하나 커버드가 인덱스 안에 데이터가 있다면 인클루드는 리프레벨에 데이터가 붙는다
잉 아닌데 인클루드도... 위쪽부터 커버드처럼 데이터가 붙어야 정상이지 않나요?
구조 자체도 동일해버리네요

Master님의 말:
둘다 데이터 포함돼
헌데 인클루드는 인덱스의 한계를 넘고
아까 말했듯이 엔진에서 크기 계산시 인클루드 열이 빠진다는거

허접님의 말:
냅

Master님의 말:
그리고
간혹 옵티마이저에 의해
인덱스들을 조인 혹은 머지해서
커버드 인덱스로 처리하기
요건...
뭐랄까 용어 문제긴한데...
저것도 커버드 인덱스로 보느냐 마느냐는 잘 몰겠다
ㅎㅎ

- 결론
1. Covered Index
형님 그럼 커버드 인덱스는
create index idx1 (c2, c1, c3) 이렇게 만드는것만
해당하네요
따로 인덱스 만들어서는 커버드가 아니네요
그럼 c2로 정렬이 되고
이미 c2의 인덱스에 c1, c3 값이 있으니
클러스터 인덱스를 안타고 바로 데이터를 접근
따로 인덱스를 만들게 되면
C2만 사용하게 되고 그럼 클러스터 인덱스를 통해 c1과 c3를 접근해야하고
즉 create index idx1 (c1) , create index idx2 (c2), create index idx3 (c3) 은 정확하게 커버드 인덱스라 부를 수 없네요

2. Include Index
인덱스의 한계를 넘고
아까 말했듯이 엔진에서 크기 계산시 인클루드 열이 빠진다는거

3. Bookmark Lookup
허접님의 말:
북마크 룩업이라는게 넌클러스터드 인덱스 서치 하고 나서 해당 포인터를 가지고 클러스터드 인덱스 서치하는거죠?
근대 어케 북마크 룩업이 없을 수 있어요?
Master님의 말:
클러스터드인덱스 서치라기보다는...
거기 달린 데이터를 가져오는거지
인덱스에 없는 데이터들...