BOARD
DEVHolic인덱스는 전체 데이터베이스를 찾아보지 않아도 빠르게 원하는 데이터를 찾아준다.
SQL 서버 2000은 테이블, 뷰, 컬럼에 대해 인덱스를 만들 수 있다. 테이블에 대한 인덱스는 테이블의 중요한 정보나 데이터를 빨리 찾기 위해 만든다. 뷰에 대한 인덱스는 뷰에 대한 결과 값에 대하여 만든다. 계산된 컬럼에 대한 인덱스는 나타나는 값을 평가하거나 결과를 인덱스할 수 있게 한다.
인덱스 이해하기
인덱스는 테이블처럼 페이지를 사용한다. 인덱스 페이지는 테이블 데이터처럼 구조화 되어 있다.
그것들은 8KB(8192바이트)의 크기를 가지고 96바이트의 헤더를 갖는다. 데이터 페이지와 다른 점은 행에 대한 오프셋이 없다는 것이다. 각각의 인덱스는 sysindexes라는 테이블에 인덱서 ID값을 가지고 각 행에 상응하게 되어 있다.
인덱스 ID의 값이 1이면 클러스터 인덱스를, 2~250이면 넌클러스터 인덱스를 의미한다.
인덱스 ID가 255이면 text, ntext, image 데이터를 의미한다.
SQL 서버는 인덱스를 Btree 구조로 관리한다. 이 구조는 뿌리(root)부분, 중간(intermediate) 부분, 끝(leaf) 부분으로 구성된 기본 나무(tree)구조이다.
나무 구조에 대하여 아주 좋은 점은 그것들은 빠르고 효과적으로 찾을 수 있다는 것이다. 나무 구조가 없다면 알맞은 데이터를 찾기 위하여 계속해서 데이터베이스에 있는 테이블의 모든 데이터 페이지를 읽어봐야 할 것이다.
클러스터와 넌클러스터 인덱스
SQL 서버는 두가지 종류의 인덱스를 지원한다.
- 클러스터 인덱스
- 넌클러스터 인덱스
인덱스는 어떤 컬럼에나 금방 만들 수 있다. 예외 사항은 컬럼이 text, ntext, image 데이터 형식이면 안된다는 것이다.
이러한 데이터 형식에는 인덱스를 만들 수 없다. 그러므로 언제나 인덱스 컬럼을 설정할 때는 신중해야 한다.
인덱스 컬럼을 제대로 선택하면 반응 속도가 환상적으로 빨라질수도 있고, 잘못된 인덱스는 반응 속도를 더 늦게 만들어 버릴 수도 있다. 어느 컬럼을 인덱스로 지정할 것인지를 결정하기 위하여 인덱스 튜닝 마법사를 이용한다.
클러스터 인덱스 사용하기
클러스터 인덱스는 실제의 테이블 데이터 페이지를 리프 수준(leaf level)에 저장한다.
그리고 테이블 데이터는 키 값대로 물리적으로 정렬된다. 테이블은 단 하나의 클러스터 인덱스를 가질 수 있고 이 인덱스가 만들어지면 다음과 같은 일들이 벌어진다.
- 테이블 데이터가 재정렬 된다.
- 새로운 인덱스 페이지가 만들어진다.
- 데이터베이스 안에 있는 모든 넌클러스터 인덱스는 지워진다.
결과적으로 많은 디스크 I/O 작업이 일어나고 시스템의 메모리와 자원을 많이 요구한다. 클러스터 인덱스를 만들 때는 테이블의 데이터 양보다 1.5배 이상의 빈 공간을 확보하는게 이상적이다.
클러스터 인덱스에서 인덱스되는 값들은 반드시 유일성을 가져야 한다. 값이 유일하지 않으면 SQL서버는 행에 대하여 (내부적으로) 두번째 정렬 키를 만들어 유일성을 확보한다.
넌클러스터 인덱스 사용하기
넌클러스터 인덱스에서 리프 수준의 페이지는 북마크(book mark)를 포함하고 있다. 그 북마크는 인덱스에서 SQL 서버가 어디서 키에 상응하는 데이터를 찾아야 할지를 알려주는 것이다.
테이블이 클러스터 인덱스를 가지고 있다면 북마크는 클러스터 인덱스 키를 가리킨다. 테이블이 클러스터 인덱스를 가지고 있지 않다면, 북마크는 실제 행의 위치를 가리킨다.
넌클러스터 인덱스를 만들 때 SQL 서버는 필요로하는 인덱스 페이지를 만들지만, 테이블에 데이터는 정렬하지 않는다. 또한 SQL 서버는 다른 인덱스들을 지우지도 않는다. 각각의 테이블은 모두 249개의 넌클러스터 인덱스를 가질 수 있다.
어떤 컬럼을 인덱스할까?
인덱스를 쓸 것과 쓰지 말아야 할 것
인덱스를 써야 할 것 | 인덱스를 쓰지 말아야 할 것 |
많은 행을 가진 테이블 | 적은 수의 행을 가진 테이블 |
질의에서 자주 사용되는 컬럼 | 질의에서 사용 빈도가 적은 컬럼 |
여러 다양한 범위의 값을 가지고, 자주 선택되는 컬럼 | 다양한 범위의 값을 가지고 있지만, 드물게 선택되는 컬럼 |
컬럼이 집계 기능으로 사용될 때 | 컬럼이 커다란 크기를 가지고 있을 때 |
컬럼이 질의에 의해서 그룹으로 사용될 때 | 테이블이 번번하게 바뀌지만 실제 질의는 아주 적을 때 |
컬럼이 질의에 의해 정렬되어 사용될 때 |
|
컬럼이 테이블 조인으로 사용될 때 |
|
언제 클러스터 인덱스를 쓰고, 언제 넌클러스터 인덱스를 쓸 것인가?
클러스터 인덱스 | 넌클러스터 인덱스 |
계좌번호와 같이 빈번하게 찾기에 사용되는 기본 키 | 식별자 컬럼과 같은 일련의 식별자로 된 기본 키 |
많은 결과를 보여주는 질의 | 적은 수의 결과를 보여주는 질의 |
많은 질의에 사용되는 컬럼 | 집계 기능에 사용되는 질의 |
많이 읽혀지는 컬럼 | 왜래 키 |
정렬 혹은 그룹으로 사용되는 컬럼 |
|
테이블 조인에 사용되는 컬럼 |
|
인덱스 만들기
CREATE [ UNIQUE ] [ CLUSTERED | NON-CLUSTERED ] INDEX index_name
ON { table | view } ( column [ ASC | DESC ] [ ,...n ] )
[ WITH
[ PAD_INDEX ]
[ [ , ] FILLFACTOR = filetactor ]
[ [ , ] IGNORE_DUP_KEY ]
[ [ , ] DROP_EXISTING ]
[ [ , ] STATISTICS_NORECOMPUTE ]
[ [ , ] SORT_IN_TEMPDB ]
]
[ ON fileguroup ]
백수
