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
댓글 없음:
댓글 쓰기