---------------------------------------------------------------------------------
출처: http://www.tiptech.net/lecture/sql/sql2000/index4.html
---------------------------------------------------------------------------------
1. 색인에 대한 이해
이번에 소개해 드릴 내용은 색인입니다. 색인이라는 주제는 사실 매우 광범위하고 성능에 지대한 영향을
줄 수 있는 주제입니다. 아울러 SQL서버의 상당한 내부적인 구조를 알아야 하므로 대단히 많은 분들이
꺼려하고 어려워하는 부분이기도 하지요. 실제로 사이트에 나가 튜닝 작업을 할 경우 먼저 보는 부분이기도
합니다. 그럼 이제 이 색인에 대해서 찬찬히 알아 보도록 하지요.
＊ 색인은 무엇인가?
데이터를 담고 있는 최소 단위가 모라고 말씀 드렸죠? 네, 바로 페이지라고 말씀 드렸죠.
이 페이지에 실제 로우 데이터가 들어 있다고 말씀 드렸습니다.
물론 페이지 헤더와 옵셋 정보 역시 페이지에 포함되어 있지만, 저희는 데이터만 있다는 가정을
먼저 해 보도록 하죠. 그렇다면 색인은 무엇인가?
" 색인은 트리구조(계층형)로 이루어져 있는 실제 페이지의 데이터를 포인팅하는 데이터 입니다. "
많은 분들이 오해하는 부분을 말씀 드리지요.
1. 색인은 공간을 차지하지 않는다.
☞ 아뇨. 색인의 종류에 따라 가변적으로 인덱스의 크기가 잡힙니다.
물론 실제 데이터에 비례적인 크기가 되지요. 종류별로 정확한 크기 예측은 힘들죠.
중요한건 색인 역시 데이터라는 겁니다.
2. 색인을 만들면 항상 빠르다.
☞ 벌써 드릴 말씀은 아니지만 잘 만들어야 빠릅니다. 걱정마세요. 이번에 잘 만드는 방법을 배워 보세요.
3. 데이터가 적어도 인덱스가 유용하다.
☞ 아닙니다. 데이터가 많을 수록 인덱스는 환상의 속도를 비례적으로 낼 수 있습니다.
☞ 권고적으로 테이블의 크기가 대략 1M 정도보다 작으면 역시나 인덱스가 없는게 유용할 수 있습니다.
우선 위 세가지만 말씀 드리고 잘못 이해하는 부분을 또 찬찬히 말씀 드리지요.
색인의 모식도를 한번 봐 보도록 할까요? 우선 다음과 같은 회원 테이블 정보가 있다고 생각해 보도록
하지요.
이 회원 테이블은 보시는 바와 같이 번호, 이름, 생긴 정도를 포함하고 있습니다.
번호와 이름은 믿을만한 정보이나 모든 정보가 믿을만한건 아닙니다. 주의하세요.
여하간 이런 부분이 실제 데이터 페이지에는 어떻게 기록이 되어 있을까요?
내부 데이터 페이지에는 위와 같은 방식으로 저장되어 있을 겁니다.
만약 제가 다음처럼 번호 컬럼의 값을 찾고자 한다고 생각해 보도록 하지요.
이렇게요. 만약 제가
1번 번호를 찾으려 한다면? 와우~ 1번에 찾았군요. 1번 페이지에 있으니 8K Byte의 IO로 찾았습니다.
7번 번호를 찾으려 한다면? 3번째 페이지에 있네요. 3*8K Byte 크기의 IO로 찾았습니다.
만약 13번을 찾으려 한다면? 4페이지에 있으니 32K Byte가 소요됩니다.
데이터가 늘어나 만약 10만번째를 찾으려 한다면? 대단한 IO가 발생하겠지요?
이럴 때 인덱스의 구조와 속도를 찬찬히 이해해 보도록 하세요. 만약 이것이 혹시 기억나실지 모르지만..
C를 배우신 분이라면 B-Tree를 아실 겁니다. Balanced Tree라고 불리는 구조로 이것이 생성되어 있다면?
여기서 잠시만. 이것은 실제 인덱스의 그림과는 다른 구조이나 제가 몇번 강의를 하면서 이해하기 쉽도록
그린 그림입니다. 이 그림은 잘 이해하시는데 다음 장에서 설명할 그림을 잘 못 이해 하시더라구요.
인덱스 모식도를 그려 볼까요?
대략 이런 식이 될겁니다. 저게 뭐냐구요? 찬찬히 설명을 드리지요. 밸런스드 트리의 근간은 말 그대로
밸런스입니다. 균형이 중요하다는 거지요. 여기서 이렇게 생각해 볼까요? 위와 똑같이...
1번을 찾으려 하면? 맨위의 7을 보니 1 <> 7 그러므로 아래를 보니 4, 9 ,13이 있네요.
그 후, 1 < 4 그러니 4로 가면 되겠지요? 4로 가서 딱 보니 1이 있는 겁니다.
각각의 단계를 페이지와 비교해 보죠. 7에서 1개 페이지, 4에서 1개페이지를 보니 우리의 값인 1이 보이죠? 3개의 페이지를 읽었습니다.
다음 7번을 찾으려 하면? 어라 웬일이죠? 맨 위에서 찾았어요. 그렇죠? 땡잡았네요. 1개 페이지만에 OK.
다음 13 을 보면?
역시 2번에 찾을수 있겠죠? 자, 저 트리의 깊이가 한 단계 두 단계 늘어난다고 생각해보세요?
지금은 비록 평균적으로 3개 정도면 읽을 수 있겠지만. 노드가 늘어갈수록..
4개 페이지를 읽어 많은걸 볼 수 있구.. 5개 노드면 더 많이.. 그런 식으로 실제 테이블의 맨위부터
차근차근 긁을 때보다. 이것을 풀스캔이라고 하죠. 더 적은 IO로 원하는 데이터를 읽을 수 있겠지요.
여기서 중요한 용어를 이해해 보도록 하죠.
계속적으로 이야기가 나오겠지만. 위처럼 최상위 위치를 Root 레벨이라고 부릅니다.
중간 단계의 레벨, 위에서는 중간 레벨이 하나 뿐이지만 B-Tree의 데이터가 많아 질수록 중간 레벨은
계속 늘어날 수 있겠죠? 이 녀석들을 Non-Leaf 레벨이라고 부릅니다. 또는 중간레벨(Inertmediate Level)이라고 부르기도 합니다. 레벨 대신 가끔 노드라고도 하지요.
끝으로 최하위의 레벨을 부를 때 Leaf 레벨 또는 Leaf 노드라고도 하지요. 한글로는 각각 뿌리 수준 / 잎이 아닌 수준, 또는 가지 단계, 또는 중간 단계 / 잎수준이라고 부르기도 합니다. 자, 이제 기본적인 인덱스의 형태에 대한 이야기는 간단히 드렸구요. 다음 장에서 실제 인덱스들을 가지고 이야기를 풀어 보도록 하지요.
2. 색인(Index)의 종류
자, 이제 SQL 서버의 색인을 풀어볼 차례로군요. 약간 어려워도 조금만 참고 함께 이겨나가 보도록 하지요. 그만큼 중요합니다.
색인의 종류 SQL 서버는 두 가지 종류의 색인이 있습니다. 두가지 뿐이라니 뭔가 기분 좋지요?
그 두가지는 Clustered 인덱스와 Non-Clustered 인덱스 입니다.
각각 한글로는 클러스터된 인덱스 / 클러스터되지 않은 인덱스로 불립니다.
늘 그런 것처럼 혼용해서 코난이는 쓸겁니다. 아시져?
인덱스는 분명 만들어야 하는 것입니다. 이 말은
1. 만드는 비용이 있다라는 것이지요.
다음 인덱스는 공간이 필요하다고 했습니다.
2. 디스크 비용이 필요하며 항상 공간 유지를 적절해야 한다는 겁니다.
만들면 끝인가요? 아니죠. 만들었으면?
3. 유지보수에 비용이 필요하겠지요.
인덱스를 만들면 데이터를 조회하는 속도를 높일 순 있지만.. 만약 데이터가 삽입 / 수정 / 삭제 된다고
생각해 보세요. 앞에서 본 인덱스의 구조를 변경하면서 재생성 해야겠지요?
4. 데이터 변경이 있을 때 비용이 추가된다는 겁니다.
앞에서 또한 말씀 드렸지만 인덱스를 사용하면 항상 빨라진다고 말씀 안드렸죠.
5. 인덱스는 항상 빠르지 않다.
인덱스를 사용하면 유용한 부분은?
5-1. WHERE절에서 참조되는 컬럼
5-2. 참조키가 설정되어 있는 컬럼
5-3. 참조키는 아니지만 JOIN에 사용되는 컬럼
5-4. 범위 검색이 일어나는 컬럼
5-5. ORDER BY로 정렬 되는 컬럼, GROUP BY로 그룹핑 되는 컬럼
5-6. TOP 구문과 함께 사용되는 SELECT 절
등에서 사용하면 좋습니다.
그렇다면? 어디에 사용하면 바보 될까요?
5-7. WHERE절에서 사용되지 않는 컬럼에는 물론 효과 없음.
5-9. WHERE절에서 변환(함수등이 사용되는)되는 컬럼과 비교시 효과 없음.
5-10. 선택도(찾을 데이터 / 전체 데이터)가 클 경우 효과 적음.
잠시후 말씀 드리겠지만.. 예를 들어 성별 컬럼과 같은 남 / 여 비율적으로 대략 50 : 50 의 구성비가 있는
컬럼이라면? 인덱스의 효과가 떨어지겠죠. 이런 주의 사항이 필요합니다.
자, 이제 인덱스 생성 구문을 실제로 봐 보도록 하지요.
| CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name ON { table | view } ( column [ ASC | DESC ] [ ,...n ] ) [ WITH < index_option > [ ,...n] ] [ on filegroup ] < index_option > ::= |
기분 좋게도 그다지 길지 않지요? 주요한 몇 가지를 말씀 드리면..UNIQUE를 지정해 고유 인덱스 또는
고유하지 않은 인덱스 생성이 가능합니다. 색인의 종류는 두 가지로 CLUSTERED | NONCLUSTERED 중에 지정이 가능 합니다.
table이나 view에 생성이 가능합니다.
SQL 2000의 새로운 기능으로 View에 인덱스 생성이 가능하지요. 뷰 강좌를 참고해 보세요.
컬럼을 ASC 또는 DESC로 정렬해 생성 가능합니다. 특히 클러스터드 인덱스를 생성시 유용하며 ORDER BY 구문과도 밀접합니다. SQL 2000의 새로운 기능입니다.
다음 인덱스 옵션에서
PAD_INDEX는 중간 레벨을 적절히 비워 데이터 삽입 등에 대비하기 위한 것이며,
FILLFACTOR는 리프 레벨을 적절히 비워 역시 삽입 등에 대비하는 것입니다.
샘플에서 이야기를 해 드리도록 하지요.
IGNORE_DUP_KEY는 중복되는 값을 무시한다는 의미이구요.
DROP_EXISTING은 이미 존재하는 인덱스가 있으면 제거하고 재생성하라는 의미입니다.
STATISTICS_NORECOMPUTE는 인덱스를 사용할지 안할지 쿼리 최적화기가 결정하는데,
이 근거는 통계치 데이터라는 녀석으로 판단하게 됩니다. 이 통계 데이터는 기본적으로 자동적으로
항상 update 되는데, 이 통계 데이터를 자동 업데이트 하지 말라는 옵션입니다.
SORT_IN_TEMPDB TEMPDB상에서 정렬하라는 옵션입니다.
예를 들어 데이터와 인덱스가 같은 물리적인 디스크에 있고 데이터가 한 1000만건 정도 된다면 인덱스
생성에 대단히 많은 시간이 소요 됩니다. 이 때 TEMPDB에서 인덱스 생성시 필요한 정렬작업을 시키고
사용자 데이터베이스의 물리적인 디스크와 TEMPDB 쿨리적인 디스크가 틀리다면 인덱스 생성시 부하를
줄일 순 있지만 TEMPDB에 다른 불필요 공간이 생기니 주의하셔야 하지요.
끝으로 ON filegroup은 인덱스 역시 데이터라고 말씀 드렸습니다. 데이터베이스 강좌에서 filegroup 을
적절히 분산시켜 생성해 속도를 높일 수 있다고 말씀 드린 것처럼 인덱스 역시 적절한 filegroup에 위치시켜 최적의 속도를 낼 수 있게 할 수 있지요.
백견이 불여일타라고 우선 한번 맹거 보도록 하지요.
|
CREATE DATABASE konanTestDB USE konanTestDB --테이블 생성 --10000건의 샘플 데이터 삽입 declare @i int set nocount off --샘플 데이터 조회 |
대략적인 샘플 데이터 100건과 만건이 잘 들어간게 보일 겁니다.
여기서 어느 정도의 IO 비용이 소요되는지 보도록 할까요?
물론 시간 / IO 비용 모두가 중요하지만 우선 IO 비용만 보도록 하지요.
|
--IO통계 보기 SELECT * FROM konan_test_table WHERE konan_id = 5000 --IO통계? SELECT * FROM konan_test_table WHERE konan_id < 10 --IO통계? SELECT * FROM konan_test_table WHERE konan_id < 30 --IO통계? SELECT * FROM konan_test_table WHERE konan_id < 1000 --IO통계? --IO통계 끄기 |
메시지 부분을 보시면 IO 통계를 확인하실 수 있을 겁니다.
이제 인덱스를 생성해 보도록 하지요.
|
--간단한 인덱스 생성 |
인덱스 생성은 잘 되셨을 거구요. 다시 데이터를 조회해 보도록 할까요?
|
--IO통계 보기 SELECT * FROM konan_test_table WHERE konan_id = 5000 --IO통계? SELECT * FROM konan_test_table WHERE konan_id < 10 --IO통계? SELECT * FROM konan_test_table WHERE konan_id < 30 --IO통계? SELECT * FROM konan_test_table WHERE konan_id < 1000 --IO통계? --IO통계 끄기 |
통계를 봐 보세요. 어떠세요?
아마도 WHERE konan_id < 30 부터 인덱스가 없을 때와 비슷한 수치가 나올 겁니다.
이것은 뭘 말하는 걸까요? 다음처럼 실행계획 표시를 하시거나 또는 "컨트롤+K"를 눌러 실행계획을
봐 보도록 하지요.
바로 위의 같은 쿼리를 수행해 보면,
위의 그림처럼 실행 계획을 보실 수 있습니다. 중요한건 Index Seek를 했다는 점이지요.
다음 쿼리에서 수행 계획을 보면?
이렇게 konan_id < 30을 봐 보시면 Table Scan을 하는 것을 알 수 있습니다.
분명 어떤 것은 인덱스를 타고 어떤 것은 인덱스를 타지 않지요?
앞에서 말씀드린 건 선택도라고 해서(찾을 데이터수 / 전체 데이터수)로 판단되게 됩니다.
천천히 말씀 드리지요.
다음 인덱스의 정보를 보려면 어떻게 할까요?
|
--인덱스 정보 조회 |
그러면 인덱스의 정보를 확인할 수 있을 겁니다.
흥미있게 보실 부분으로 인덱스의 종류를 지정하지 않으면? 뒤에서 보시겠지만 넌클러스터드 인덱스로
잡히게 됩니다. 참고하세요. 인덱스의 변경 구문은 없으며 DROP INDEX 구문을 이용해 인덱스를
제거할 수 있습니다.
|
--인덱스 제거 |
물론 앞에서 말씀드린 CREATE INDEX구문의 DROP EXISTING 구문으로 존재하는 인덱스를 지우고
생성할 수 도 있습니다.
우선 인덱스를 생성하고 돌려는 보셨네요.
이제 본격적인 SQL서버의 인덱스에 대해서 알아 보도록 할까요.
클러스터드 인덱스
클러스터드 인덱스는 간단히 인덱스의 리프레벨이 데이터 페이지와 같은 겁니다.
뭔소리냐구요? 천천히 설명 드리지요.
이런 화면을 생각해 보세요. 클러스터드 인덱스는 물리적으로 행을 재배열해 리프 레벨이 차곡차곡
오른쪽의 데이터 페이지처럼 쌓이게 합니다. 그럼 인덱스를 봐 볼까요?
인덱스가 설정된 컬럼의 값이 순차적으로 재배열되게 되므로 왼쪽에 보시는 인덱스 페이지는 단지 키값과 페이지의 번호만이 지정되게 됩니다. 자, 그럼 우리가 만약 5번 강해원을 찾으려 한다고 생각해 보도록
하지요.
이때는 먼저 클러스터드 인덱스의 값을 보니 4 < 5 < 7 사이가 되므로 4 - 2 인 2번 페이지로 가게 됩니다.
2번 페이지로 가서 바로 5번 강해원을 찾게 되지요.
대략 이러한 그림으로 이루어 지게 되는 겁니다. 만약 12번 김태영을 찾는다면?
이때는 역시 10 < 12 < 13 이므로 4페이지로 가서 12번 김태영을 찾을 수 있게 되겠지요.
이것이 가능한 이유가 뭘까요?
바로 물리적으로 행이 정렬되어 있기 때문에 가능한 거지요. 만약 물리적으로 행이 재배열 되어 있지 않다면? 이런 작업은 불가해 지는 겁니다. 아울러 물리적으로 행들이 재배열 되어 있으므로 범위 검색에 대단히 유용합니다. 이 말은 선택도가 어느 정도 높아도 - 클러스터드 인덱스의 경우 30% 정도도 가능 - 인덱스를 이용해 데이터를 조회할 수 있게 되지요.
아울러 클러스터드 인덱스가 저렇게 테이블에 하나 생성되어 있는데요.
만약 클러스터드 인덱스를 하나 더 테이블에 만들고 싶다면 어떨까요?
안타깝게도 다음번 클러스터드 인덱스는 테이블을 다시 재구성하고 싶겠지만..
이미 한번 테이블이 정렬된 상태로 재구성되어 있으므로 불가능해집니다.
오로지 테이블에 단 1개의 클러스터드 인덱스만 생성이 가능하니 주의하셔야 합니다.
그럼 앞의 샘플쿼리를 클러스터드 인덱스로 생성하고 장난을 조금 쳐 볼까요?
|
--인덱스가 존재하면 지울것 --클러스터드 인덱스 생성 |
이렇게 클러스터드 인덱스를 생성할 수 있지요.
그런데 특이하게도 SQL 서버는 클러스터드 인덱스에 대해서 대단히 높은 우선권을 부여합니다.
예를 들어 이럴땐.. 풀스캔을 해도 좋을 것 같은데... 클러스터드 인덱스를 써서 검사할 경우가 있지요.
이는 말씀 드린대로 SQL 쿼리 최적화기가 판단하는데 테이블의 크기가 작거나(로우의 건수가 아닌 전체적인 크기) 또는 클러스터드 인덱스의 키값 컬럼이 작을 경우 종종 발생 합니다.
실제 현업에서는 범위검색에 종종 이 클러스터드 인덱스를 두게 되므로 정상적으로 잘 동작하게 되지요.
또한 클러스터드 인덱스는 비교적 넌클러스터드 인덱스보다 크기가 작습니다.
아울러 클러스터드 인덱스는 크기를 대략적으로 예측할 수 있는 인덱스이기도 하지요.
다음 넌 클러스터드 인덱스를 봐 보도록 할까요?
넌 클러스터드 인덱스
실제 테이블의 데이터가 항상 순차적으로 들어가 있는 것은 아닙니다. 관계형 데이터베이스에서 순차라는 것은 사실 의미가 없습니다. 관계형 데이터베이스의 순차 유지는 오로지 ORDER BY에 적절히 이용되는
컬럼을 잘 구성해야만 하는 것이지요. 예를 들어 실제 진짜 SQL서버에 들어가 있는 테이블의 순서는
다음과 같을 겁니다.
이런 식의 데이터가 실제로 들어가 있게 되지요. 그렇다면 페이지 내부는 어떤 식일까요?
대략적으로 위의 그림과 같은 식의 데이터가 들어가 있게 됩니다. 실질적인 데이터 페이지이지요.
이런 데이터 페이지들의 번호에 클러스터드 인덱스를 만약 생성한다면 어떻게 될까요?
이럴 경우는 RID라는 녀석이 필요하게 됩니다. 간단히 RID는 로우의 구별하는 특정 값이라고 생각하시면
됩니다.
넌 클러스터드 인덱스는 바로 이 RID를 가지고 데이터 페이지를 포인팅 하게 되지요.
RID 샘플을 보시면 다음과 같은 식입니다.
여기서 RID의 첫번째 1은 화일 그룸을 의미하게 됩니다. 그 다음 숫자는 데이터 페이지 번호이며
마지막 세번째 숫자는 페이지 옵셋으로 정확히 페이지의 한 로우를 포인팅(Pointing)하게 되지요.
또한 이 인덱스 페이지는 다음과 같은 형식으로 분할 되겠지요.
이러한 형식이 넌클러스터드 인덱스에서 과연 어떻게 사용 될까요? 실제 구조를 그려 보도록 하지요.
이러한 식으로 생성이 되게 됩니다.
루트 레벨은 인덱스 페이지 7이며, 중간 레벨은 엔덱스 페이지 1,2,3,4 이고, 실제 데이터 페이지는 1,2,3,4,5 데이터 페이지가 되지요.
자, 값을 네비게이션 해 보도록 합시다. 만약 제가 3번 이승용을 찾으려 한다고 생각해 보지요.
1 < 3 < 5 이므로 1페이지로 가야 겠지요? 같더니 3은 4페이지 2번째 로우에 있다고 합니다.
바로 4페이지로 가서 두번째 로우를 컨택하는 거지요.
또한 만약 8 차영인을 찾으려 한다면, 5 < 8 < 9 이므로 인덱스페이지 2로 가서 8 차영인을 보니 1-3-2라고 되어 있습니다. 데이터 페이지 3의 2번째 로우를 포인팅 하게 되지요. 이것이 넌클러스터드 인덱스 입니다.
넌클러스터드 인덱스는 이렇게 포인팅 정보를 가지게 되므로 인덱스의 크기가 커지게 됩니다.
아울러 선택도가 높으면 바로 쿼리 최적화기는 이 넌클러스터드 인덱스를 사용하지 않게 되지요.
일반적으로 3% 이내 정도면 이 넌클러스터드 인덱스를 사용하지 않게 됩니다.
넌클러스터드 인덱스는 데이터페이지를 물리적으로 재배열하지 않으므로 여러개의 인덱스를 생성할 수
있습니다. 최대 생성 가능 갯수는 249개 입니다.
두 차이를 비교한다면 다음과 같겠지요.
| 구분 | 클러스터드 인덱스 | 넌클러스터드 인덱스 |
| 차이 | 물리적으로 행을 재배열 | 물리적으로 행을 재배열하지 않는다. |
| 크기 | 비교적 작다. | 클러스터드 인덱스보다 크다. |
| 선택도 | 30% 정도면 사용한다. | 3% 이내면 사용한다. |
| 최대 갯수 | 테이블당 1개 | 테이블당 249개 |
다음으로 클러스터드 인덱스가 있는 테이블의 넌클러스터드 인덱스를 알아 보도록 하지요.
물론 당연히 클러스터드 인덱스와 넌 클러스터드 인덱스가 있을 때 넌클러스터드 인덱스를 조회할
경우겠지요?
현재는 번호에 클러스터드 인덱스가 걸린 상태이며, 이름에 넌클러스터드 인덱스가 설정된 상태입니다.
이런 식으로 구성이 되게 됩니다.
먼저 알아 두셔야 할 것은!!
넌클러스터드 인덱스의 RID는 더 이상 RID가 아닌 클러스터드 인덱스의 키값을 가지게 됩니다.
좀 더 간단히 실제 조회를 해 보도록 하지요. 넌클러스터드 인덱스가 걸린 이름 컬럼의 유병수라는 이름을 조회해 보도록 합시다.
박훈 < 유병수 < 이수선 이므로 넌클러스터드 중간 레벨의 2페이지로 가게 될겁니다.
여기서 유병수를 찾으니 유병수는 클러스터드 인덱스 10번 키값을 가지고 있습니다.
10이라는 값을 가지고 클러스터드 인덱스 페이지로 가니 10은 데이터페이지 4에 있다고 합니다.
데이터페이지 4로 가니? 바로 10 유병수를 찾을 수 있게 되지요.
네, 맞습니다. 바로 이런 그림이 되는 것이지요. 만약 한기환을 찾으려 한다면 어떻게 될까요?
한기환 < 한기환 이므로 넌클러스터드 중간 레벨의 4페이지로 가게 되겠죠?
클러스터드 인덱스의 키 값 2이니. 1 < 2 < 4 이므로 1 데이터 페이지로 가게 되며 1 데이터 페이지에서
2번 한기환을 발견하게 될 겁니다.
어떠세요? 조금 감이 잡히시나요? 내부적인 부분이라 조금 어렵기도 하시겠지만 중요한 부분이랍니다.
다음 장에서는 실제 색인을 여러가지 방법으로 생성하고 수행해 볼 겁니다. 수고하셨습니다.
3. 색인(Index)의 생성
이번에 소개해드릴 내용은 실제 색인 생성을 하는 부분입니다. 실제로 색인을 생성하면서 이리저리
살펴 보도록 하지요. 간단한 클러스터드 인덱스와 넌클러스터드 인덱스의 생성은 앞에서 보았으니
이번엔 유니크 인덱스 생성을 간단히 알아보고, 이어서 Fill Factor를 알아 보도록 하지요.
먼저 유니크 인덱스 입니다.
|
USE konanTestDB --데이터 조회 --유니크 인덱스 생성 --중복 데이터 삽입 - 에러 서버: 메시지 2601, 수준 14, 상태 3, 줄 1 --인덱스 삭제 --유니크 인덱스 생성 - WITH IGNORE_DUP_KEY INSERT INTO konan_test_table(konan_data, konan_date) VALUES('1', getdate()) 서버: 메시지 3604, 수준 16, 상태 1, 줄 1 |
테이블에 이미 PRIMARY KEY가 있더라도 얼마든지 유니크한 값을 구별할 수 있는 유니크 인덱스로
고유성을 보장받을 수 있게 됩니다.
다음으로 중요한 이야기인 Fill Factor 를 이야기해 보도록 하지요.
인덱스를 생성하면 데이터가 변경될 경우 인덱스 페이지 역시 변경이 되어야 합니다.
이 때 데이터의 삽입이 상당히 많다면 인덱스 페이지는 계속적으로 변경되므로 시스템에 어느 정도 부하를 줄 수 있게 되지요. 이 때 fill factor를 적절히 이용해 인덱스의 리프 레벨과 중간 레벨에 여유 공간을
두는 겁니다.
자, 여기서 그렇다면!!!
만약 Fill Factor가 100%라면? 이는 현재 인덱스의 리프 레벨과 중간 레벨이 모두 100%로 차 있는 상태인
겁니다. 적절한 수량만큼만 채우는 80% 정도로 값을 주면? 인덱스의 리프레벨은 20%정도의 여유 공간이
생기지요. 하지만!! 그만큼 SELECT의 속도는 느려질 수 있는 겁니다.
우선 샘플을 또한 봐 보도록 하지요.
|
USE konanTestDB --데이터 조회 --인덱스 삭제 CREATE CLUSTERED INDEX idx on konan_test_table (konan_id) --인덱스 정보 조회 --평균 페이지 밀도 판단. DBCC SHOWCONTIG이(가) 'konan_test_table' 테이블을 스캔하는 중... --하나건너 하나씩 데이터 삭제 --평균 페이지 밀도 판단. DBCC SHOWCONTIG이(가) 'konan_test_table' 테이블을 스캔하는 중...
--평균 페이지 밀도 판단. DBCC SHOWCONTIG이(가) 'konan_test_table' 테이블을 스캔하는 중... |
이와 비슷하게 생각하실 것이 PAD_INDEX 입니다.
PAD_INDEX와 FILL_FACTOR의 차이는 PAD_INDEX는 중간 레벨까지 여유 공간을 둔다는 점입니다.
|
--인덱스 재생성 |
이와 비슷한 작업을 하는 인덱스를 재구성하는 DBCC 명령이 두가지 있습니다.
먼저 DBCC DBREINDEX 명령과 그 다음 DBCC INDEXDEFRAG 입니다.
|
--DBCC DBREINDEX - 1개 인덱스인 idx인덱스를 fillfactor 90으로 재생성 --테이블내 모든 인덱스를 fillfactor 90으로 재생성 |
DBCC DBREINDEX는 물리적으로 완전히 모든 행을 재배열하며 인덱스까지 재구성합니다.
비록 SQL2000부터는 이 작업이 빨라 지고 온라인 상에서 할 수 있다고 하지만..
실제 대단한 부하가 걸리는 작업입니다.
이 때 물리적으로 모든 행을 재배열 하지 않고 인덱스만 재구성하는 명령이 있으니
바로 DBCC INDEXDEFRAG 입니다. SQL2000 만의 기능이기도 하지요.
|
--DBCC INDEXDEFRAG |
이런 식으로 사용 가능하며 비교적 좋은 성능을 보여 줍니다.
다음으로 통계데이터 입니다. 먼저 다음 질의를 봐 보도록 하지요.
|
--통계데이터 보기 |
앞에서 잠깐 말씀드린대로 선택도에 따라서 쿼리 최적화기가 인덱스를 타개할지 타개하지 않을지를
결정한다고 말씀 드렸지요? 이때 근거가 되는 것이 바로 통계 데이터 입니다.
SQL서버2000은 자동으로 통계데이터가 해당하는 인덱스에 대해서 생성되며 자동으로 유지됩니다.
|
--통계데이터 UPDATE |
이런 식으로 update가 가능합니다.
튜닝적인 요소로 볼 때 이는 수동으로 하지 않는 것이 거의 대부분 좋으며 수동으로 할 경우는 대단히 많은 모니터링이 필요합니다. 그럼 이 정도로 색인 생성에 대한 부분은 마치도록 하지요.
다음으로 색인의 마지막 이야기인 성능과 관련된 부분을 조금 더 보도록 하지요. 수고하셨습니다.
4. 색인(Index)과 성능에 대한 깊은 이야기
이번에 소개해드릴 내용은 색인으로 성능을 많이 높이는 부분입니다.
먼저 소개해드릴 내용은 복합키(Composite Key) 입니다.
이것은 예를 들어 테이블에서 조회를 할 경우 WHERE절에서
" WHERE A = 100 AND B=200 " 식으로 조회할 경우
인덱스 생성시 " CREATE INDEX 인덱스명 on 테이블 (컬럼A, 컬럼B) " 일 경우 유용합니다.
아울러 이를 넌 클러스터드 인덱스로 생성하면 커버드 쿼리라고 해서 또 다른 유용한 방식을 사용 가능합니다.
커버드 쿼리는 SELECT 리스트에 컬럼이 나타날때 이 컬럼이 모두 넌클러스터드 인덱스의 복합키 인덱스로 생성되어 있을 경우 오로지 넌클러스터드 인덱스만을 찾아서 데이터를 보여주면 되므로 성능 향상을 기대할 수 있습니다.
예를들어..
" SELECT A, B FROM 테이블 WHERE A = 1 "
이런 경우에서 만약 인덱스가 CREATE INDEX 인덱스명 on 테이블 (A, B) 식이라면 위의 질의는
커버된 쿼리라고 해서 성능에 좋은 효과를 줄 수 있지요. 하지만 대단히 제한적이니 주의해야 합니다.
한글로는 포함쿼리라고 부르기도 합니다.
두번째로 저장 프로시져의 RECOMPILE 입니다. 주의하실 부분으로.. 이런 케이스가 있습니다.
|
--Recompile --테이블이 존재하면 지울것. --테이블 생성 --10000건의 샘플 데이터 삽입 declare @i int set nocount off --데이터 조회 --간단한 인덱스 생성 --인덱스 정보 조회 --페이지수 판단.
SELECT * FROM konan_test_table WHERE konan_id = 5000 'konan_test_table' 테이블. 스캔 수 1, 논리적 읽기 수 3, 물리적 읽기 수 0, 미리 읽기 수 0. --실행계획 SELECT * FROM konan_test_table WHERE konan_id < 10 SELECT * FROM konan_test_table WHERE konan_id < 30 SELECT * FROM konan_test_table WHERE konan_id < 1000
--수행
--일반 쿼리
|
보시는 바와 같이 프로시져로 생성할 경우 실행 계획이 캐싱되면서 무조건 첫 실행시의 인덱스 서치를
가지고 판단하므로, EXEC upSelKonanTestTable 1000 을 수행시 'konan_test_table' 테이블. 스캔 수 1, 논리적 읽기 수 1002, 물리적 읽기 수 0, 미리 읽기 수 0. 이라는 무지막지한 IO를 발생시키게 됩니다.
이 때.. 다음처럼 recompile을 적절히 이용하면 이를 피할 수 있습니다.
|
--프로시져 수정 --수행 EXEC upSelKonanTestTable 30 EXEC upSelKonanTestTable 1000 SET STATISTICS IO OFF |
다음으로 SQL2000의 역시나 새로운 부분인 계산된 컬럼에 인덱스 생성 입니다.
|
--월급이라는 테이블을 생성한다. --데이터를 삽입한다. --데이터를 조회한다. --이 계산된 컬럼인 받는돈 컬럼에 색인을 생성해 보자 --SQL2000 이라서 계산된 색인에 뷰 생성이 가능하다. |
이러한 부분 입니다. 사실 인덱스는 내부적인 녀석이며 매우 크리티컬 하기도 합니다.
잘 생성해야 하며 잘못하면 느려질 소지 역시 있지요. 많은 분들이 인덱스 생성에 대해서 어느 정도 감이
잡히셨으리라 생각하면서.. 수고하셨습니다.
'IT_DBMS > MSSQL' 카테고리의 다른 글
| MS SQL 서버의 Clustered (클러스터) 인덱스에 대한 그릇된 이해. (0) | 2011.03.22 |
|---|---|
| [펌] mssql 쓸만한 저장 프로시져 (0) | 2010.06.30 |
| [펌] 현재 sql server 에서 가장 많은 부하를 일으키는 spid 와 쿼리는? (0) | 2010.06.28 |
| [Stored Procedure] Table Data Backup - Insert Script Generator (0) | 2009.08.24 |
| MSSQL Server 2005 Tips : 윈도우 인증 → 혼합 인증 & IP로 접근하기 (0) | 2009.06.17 |