covering index (커버링 인덱스) 를 알아보자 > db

그걸 알기전에 MS-SQL에 존재하는 인덱스 구조를 알아보자...

MS-SQL에는 인덱스가 두가지 종류가 있다.. clustered 와 non-clustered .. 이정도는 다 아나??

이정도는 알아주었으면 한다.. 만약에 이걸 모르겠다??

그럼 EM에서 F1키를 눌러라.. 그럼 알 수 있을것이다...

그래도 간단히 설명해줄까나~~~~

clustered index는 table에 오직 하나만 존재 할 수 있다... ( data를 정렬하여 저장하기 땜시... 여러개의 clustered가 있다면 ... 오 마이갓~~~ insert 할때마다 죽어날것이다.. 이건 내 생각이고 MS는 이렇게 룰을 정했다는데 뭐 할말이 있나... 그냥 참고나 하시길 ㅋㅋㅋ )

clustered index를 조금 설명하면...

1. clustered index를 가지고 key를 검색하여 데이터를 읽어오게 되면, clustered index의 leaf level이 data page 이기 땜시 point jump를 하지 않고 데이터를 읽어오게 된다.

2. clustered index의 leaf level은 인덱스를 구성하는 컬럼들의 순서대로 이미 정렬되어 저장이 되어져 있다. 아주 착하게 말이죠. clustered index의 leaf level은 그 테이블의 실제 data page를 포함하기 때문에 그 테이블의 data page들이 잘 정렬된다는 의미지요..

3. 클러스터드 인덱스의 좋은 점은 row data가 정렬되서 저장되어져 있기 때문에 인덱스에 기반을 두고 많은 양의 순차적인 데이터를 읽어올 때에는 ( 64kb 이상이라고 하네요 ) sequential disk I/O가 발생하기 때문에 I/O performance 이득을 얻을 수 있다... 이 말인 즉슨... 우리가 흔히 말하는 범위 검색시에는 아주 좋은 성능을 발휘한다는 말이죠..

그러니깐 reg_dt라는 컬럼에 클러스터드 인덱스를 생성했고 쿼리에서는 where reg_dt between a and b 라고 하면.. 아주 좋은 성능을 얻을 수 있다는것이지요.. 인덱스를 만들지 않았을때 보다..

참고로 book online에 있는 이미지를 함 올려볼까나???

[출처 : MS books online ]

이정도만 설명하겠다.. 인덱스는 설명하면 한도 끝도 없다..

그럼 이제

non-clustered index를 알아봐야겠다...

non-clustered index는 인덱스 페이즈들로 구성된 b-tree 이다. index page 들의 leaf level에는 그 인덱스를 구성하는 컬럼의 모든 데이터를 포함한다. 무슨 말이냐..

이건 정렬되지 않는다.. non-clustered index는 인덱스를 구성하는 컬럼의 값들이 어디에 존재하는지를 leaf level에 저장을 해두고 만약 이 data를 읽을 때는 leaf level에 저장되어 있는 pointer를 이용해 pointer jump를 한다. 또한 인덱스에 해당하는 컬럼들의 데이터를 leaf level에 저장한다.

위 처럼 동작하기 때문에 선택성이 좋은 컬럼을 개별 선택하는 쿼리인 경우 pointer jump 한번에 데이터를 불러 올 수 있으므로  I/O performance penalty가 적어진다. 그렇기 때문에 하나 혹은 소수의 row들을 리턴하는 query인 경우에는 좋은 성능을 얻을 수 있다.

이것도 book online에서 그림 한장  올린다...

[출처 : MS books online ]

위에 것들은 아마 시퀄서버 쓰는 사람이라면 아주 많이 많이 들었던 내용일것이다.

그럼 이제 covering index를 알아보기로 하자...

convering index는 non-clustered index에만 해당하는 특별한 경우의 인덱스 구조라고 생각하면 된다. 이는 query에 충족하는데 필요한 모든 컬럼들이 포함되어져 있는 non-clustered index를 의미한다.

이게 무슨 말이냐???

tableA에 col1, col2, col3가 있다고 하자... 그런데 쿼리를 사용해 보니

select col1, col3 from tableA where col2='AAA'라는 쿼리를 사용하여야 한다면... 성능을 좀더 높이기 위해

col1, col2, col3를 묶어서 non-clustered index를 생성하는것을 말한다..

이해가 좀 된다... ㅋㅋ 나만 그런가???

이것을 왜 사용하나??

시퀄 서버가 data page가 아닌 index page로 부터 필요한 모든 data를 얻을 수 있다. 따라서 disk I/O가 줄어들기 때문에... 좋은 성능을 얻을 수 있다.

이렇기 때문에 이 covering index를 사용하는것이다.

그렇다고 모두 이렇게 사용하면.... 난리난다.. 인덱스 페이지가 엄청 커져버리기 때문에. insert update delete 할때 성능저하가 심각해 진다.

어떨때 conering index를 사용할까.. MS에서는 해당하는 table의 row의 data byte수 에 비하여 covering index의 모든 column들의 byte수를 합한것이 작고, covered index를 이용하는 query가 자주 수행되는 확실한 경우에 covering index를 사용하는것을 권장한다.

그렇다고 무조건 이렇게 여러가지 컬럼을 하나의 index로 생성을 할 필요는 없을것같다....

그건 시퀄 서버의 기능중에 이걸 자동으로 해주는 기능이 있기 때문이다.

예를 들면 TableA에 col1, col2가 있고  non-clustered index가 col1, col2에 따로 생성이 되어져 있는 경우에

select col1 from TableA where col2='!!' 와 같이 쿼리르 하면

시퀄 서버는 두개의 컬럼에 인덱스가 생성이 되어져 있는것을 할고 이것을 Hash Table를 구축하고 여기서 데이터를 얻어내게 된다. hash table을 만드는건 index intersection 이라는 작업에 의해 생성이 됩니다.  이렇게 작동되는걸 Automatic Covering Indexes 이라고 합니다.

이렇게 선언된 covered index를 통한 것이나 index intersection에 의한 것이나 covering index에 의해 처리되는 쿼리들은

covered query 라고 한다.

이와 같이 covering index에 대해 잘 알아봤다.

index는 정말 오묘하다... 가장 어렵고 힘든것도 index관련된 부분이고.... 공부를 하면 할 수록 어려운것도 index 같다....

그래도 꼭 알아야 할 부분이 index 이다..

파일처리... 데이터베이스론 다시 공부해 볼까 -_-;;;;;