** INDEX **
DB에서 어떤 데이터를 찾으려면, 인덱스라는 것이 상당히
필요하다. 이 것은 페이지I/O를 상당히
줄여줌으로 인해서 쿼리성능을 향상시키기 때문이다
인덱스는 검색속도와 데이터변경속도를 높이기 위해서 존재한다.
데이터베이스의 인덱스라는것은 책의 색인 부분과 너무도 많이 닮아있다.
만약 여러분이 읽고 있는 이 책의어디에도 목차나 색인이 없다면,
여러분들은 원하는 단어를 어떻게 찾을 것인가?
만약 “클러스터드“라는 단어를 검색한다고 생각해 보자. 책의 처음부터 한페이지 한페이지 넘겨가면서
찾을 수 밖에 없다. 그렇게 넘겨가면서 다행히 2번째 페이지에서 ”클러스터드“라는 단어를 찾았다.
거기서 그냥 끝낼 것인가? 안타깝게도 그럴 수 없다.
이 후의 어떤 페이지에 또 ”클러스터드“라는 단어가 있을지 알 수 없는 일이기 때문이다.
결과적으로, 만약 어떤 책이 1000페이지로 구성되어 있다면, 1000페이지를 전부 다 검색해보아야
원하는 정확한 결과를 얻을 수 있다는 얘기다. 이전의 파일데이터베이스에서는 데이터를 이렇게
검색할 수 밖에 없었다. 그러나, 색인이 있다면 상황은 180도 달라진다.
책 맨 뒤에 보통 색인이 있고 어떤 단어가 어떤 페이지에 있는지를 기록해 놓고 있다.
인덱스도 이와 똑같은 구조를 가진다(사실 넌클러스터드 인덱스만 있을 때 같다.).
몇 페이지에 있는지를 알고 있으므로, 해당 페이지만 엑세스 즉 I/O를 하면 된다.
다른 페이지엔 없다는 것을 앎으로 아예 I/O를 일으키지 않아도 되는 것이다.
만약 10개의 페이지에 “클러스터드”라는 단어가 있다면, PAGE I/O 10이면 된다는 얘기다.
여기에 색인이 있는 페이지도 검색해야 하므로 약간 I/O가 더 늘어난다.
그래봐야 11~12번 정도이다. 1000번의 I/O를 일으킬 때와는 비교가 되지 않는 것이다.
물론 데이터의 저장상태에 따라서 인덱스를 쓰지 않는 것이 더 빠를 때도 있다. 하지만,
기본적으로 관계형 데이터베이스에서의 강점은 관계로써 무결성을 강화하고, 한 테이블에
인덱스를 여러개 둘 수 있게 함으로써 속도를 빨리하는데 있다.
책에서 인덱스를 사용하면 책을 다 읽지 않고도 특정 내용을 빠르게 찾을 수 있다. 마찬가지로
데이터베이스에서 인덱스를 사용하면, 전체 테이블의 모든 페이지를 스캔하지 않고도 데이터를
찾을 수 있다. 이것이 인덱스가 필요한 이유이다.
* 인덱싱 지원 : 데이터 베이스에 대해 정의된 전체 텍스트 카탈로그와 인덱스를 구현하고 색인 채우기를 한다.
* 쿼리 지원 : 전체 텍스트 검색 쿼리를 처리한다.
* 선행 작업 목록
1. 전체 텍스트 서비스를 설치하고 시작한다.
2. 전체 텍스트 색인에 포함될 컬럼을 정의하여 색인을 만든다.
3. 카탈로그에 등록시킨다.
4. 색인에 데이터를 갱신한다.
5. 주기적으로 색인을 갱신한다.
* 전체 텍스트 색인의 특성
1. 데이터베이스에 저장되지 않고 다른 곳에 저장된다.
2. 자동으로 갱신되지 않는다.
3. create index문을 사용하는 것이 아니다. 프로시저를 사용한다.
* 프로시저 : 사용자가 편리하게 호출만 하면 불러와지는 미리 만들어진 절차들
ex) sp_help~~~~~~
4. 테이블당 하나의 색인만 존재할 수 있다.
5. 카탈로그라는 것으로 함께 모여져 관리된다.
6. image 컬럼에 특정 유형(.doc,.hwp....)의 문서가 있으면 전체 텍스트 검색이 필터를 사용하여
데이터(image, binary)를 처리한다.
7. pk (=primary key),unique 와 연결되어야 한다.
1. INDEX 생성 및 삭제
1) INDEX 만들기
기본키를 설정하면 기본키 컬럼에 기본값으로 클러스터드 인덱스가 생성된다.
이것은 별로 권장되지 않는다. (기본키는 유일키이기때문에 인덱스를 하나마나이다.)
(기본키로 설정하면 자동으로 인덱스가 형성된다.)
자주 조회되는 조건컬럼이며 값이 중복된 컬럼에 주는 것이 더 효과적이다.
----------------------------------------------------------------------
이것은 데이터베이스 물리적 설계단계에서 항상 기억해야 할 중요한 일이다.
Create Table Idx_01
( id int Primary Key, Name Varchar(20))
Go
exec Sp_HelpIndex idx_01
Go
Create Table Idx_02
( id int Primary Key NonClustered, Name Varchar(20))
Go
exec Sp_helpindex idx_02
Go
--Drop Table idx_01
--Drop Table idx_02
Create Index idx_idx_01_name
On idx_01(Name)
Go
Create Clustered Index idx_idx_02_name
On idx_02(Name)
Go
exec Sp_HelpIndex Idx_01
exec Sp_HelpIndex Idx_02
UNIQUE INDEX 를 만들어 보자. UNIQUE INDEX 는 클러스터드, 넌클러스터드와는 상관없는 얘기이다.
인덱스의 종류가 아니라 옵션이라고 생각하는 것이 더 옳다. 이것은 똑같은 값이 입력되지 못하도록
---------------------------------------
데이터의 중복을 방지하는 옵션이다. 한편, 이 데이터의 중복방지는 제약 중 “UNIQUE" 제약으로도
----------------------------------
설정이 가능하다. 그러나, 어차피 이 ”UNIQUE" 제약도 내부적으로는 UNIQUE 인덱스를 통해서
구현되는 것이기 때문에, UNIQUE 제약을 준 컬럼엔 UNIQUE 인덱스가 생성되며 UNIQUE 제약을
없애기 전까진 UNIQUE 인덱스도 삭제할 수 없다. 결론은 기본키 제약과 UNIQUE 제약 둘 다,
내부적으로는 UNIQUE 인덱스를 사용해서 중복방지를 구현한다는 것이다.
Create Table UNQ
( Uid Int Primary key, Uname varchar(20) UNIQUE)
GO
EXEC SP_HELPINDEX UNQ
GO
Uname 컬럼에 우리가 명시적으로 생성한 인덱스는 없지만, 이미 Unique 인덱스가 Uname 컬럼에
생성되어 있는 것을 확인할 수 있다.
/* 참고
인덱스를 준 컬럼의 데이터타입 (varchar 등등)은 900바이트를 초과할 수 없다. 사실 900바이트가 아니라,
100바이트도 무리가 된다. 인덱스를 준다는 것은, 해당 컬럼을 Where 절에 써서 조건검색을
한다는 얘기인데, 그 컬럼이 100바이트이면 100바이트 내의 데이터를 일일이 찾고 비교검색을
하느라 성능은 상당히 느려진다. 아울러 인덱스의 키 값 자체가 커지므로 데이터베이스의
전체 크기도 커져서 유지관리하는 데 비용 (=인적 비용)이 늘어나게 된다. 한번 해당 인덱스의 키 값을 변경하면
인덱스 페이지들의 데이터가 다시 정렬되어야 하는데, 인덱스 키 값이 크니까 그만큼 인덱스 페이지도
많고, 많은 인덱스 페이지들의 데이터를 다시 정렬하는 데에 많은 비용이 소모될 것은 당연하다.
이런 이유로 인덱스를 준 컬럼은 900바이트를 넘을 수 없으며, 마찬가지 이유로 Text, Image 컬럼에
인덱스를 주는 것은 불가능하다. 다음과 같이 해보라. 분명 실패할 것이고 이 때 출력되는
에러메시지도 잘 보아두자.
Create Table idx_03
( id char(901) Primary key, name varchar(20))
go
만약 id 컬럼의 데이터타입을 char(900)으로 변경한다면 위 문장은 성공한다.
*/
2) INDEX 삭제하기
idx_01 테이블에 있는 두 개의 인덱스를 지워보도록 하겠다.
이 테이블엔 현재 클러스터드 인덱스와 넌클러스터드 인덱스가 각각 하나씩 존재한다.
Set Nocount ON (서비스가 돌아가는 것을 잠깐 멈춘다.)
go
exec sp_helpindex idx_01
go
-- 1) 넌클러스터드 인덱스부터 제거한다.
Drop Index idx_01.idx_idx_01_name
go
-- 2) 프라이머리 키에 있는 인덱스는 지울 수 없다.
Drop Index idx_01.PK__Idx_01__6EC0713C -- So 에러발생
go
-- 3) 클러스터드 인덱스를 넌클러스터드 인덱스로 변경할 수 도 없다.
Create Index PK__Idx_01__6EC0713C
On idx_01(id)
With Drop_Existing
go
exec sp_helpindex idx_01 -- PK 에 걸린 인덱스는 그대로 남아있다.
go
idx_01 테이블엔 id 컬럼과 name 컬럼에 두 개의 인덱스가 있었다. name 컬럼에 주어진 인덱스는
넌클러스터드 인덱스이므로 잘 삭제된다. 그러나, 기본키인 id 컬럼에 주어진 인덱스는 지울 수 없다.
왜냐하면 기본키는 중복을 방지하는 속성이 있으며, 내부적으로 이것은 Unique Index 로 구현되기
때문이다. 그러므로 기본키는 반드시 Unique Index를 가진다. 그래서 인덱스를 삭제할 수 없다.
클러스터드 인덱스는 넌클러스터드 인덱스로 변경할 수 없다. 그 반대는 얼마든지 가능하다.
아직 테이블에 클러스터드 인덱스가 존재하지 않는다면, 넌클러스터드는 언제든 클러스터드 인덱스로
변환할 수 있다. 클러스터드 인덱스를 넌클러스터드 인덱스로 변환해야 한다면,
그것은 EM에서 해야한다. EM을 열고, 다음 메뉴를 실행해보자.
EM
idx_01테이블에서오른쪽버튼/테이블디자인/
저장단추 바로옆 오른쪽단추인 테이블 및 인덱스 속성단추클릭/인덱스&키탭 클릭
창이 열리면 현재는, 체크박스(CLUSTERED로 만들기)에 체크가 되어 있을 것이다.
체크를 없앤다. 그리고 “닫기”버튼을 누른 후 저장단추를 다시 클릭하면 이제 클러스터드 인덱스는
넌클러스터드 인덱스로 변경되게 된다. 이런 방식으로 인덱스를 관리할 수 있다.
물론 이 창에서 얼마든지 인덱스를 새롭게 만들고 삭제하며, 옵션들을 변경할 수 있다.
그리고 EM 의 다이어그램 창에서도 똑같은 창을 열어서 편집이 가능하다.
EM에서 다음과 같이 다이어그램을 만들어 보자.
다이어그램에서 오른쪽버튼/새 데이터베이스 다이어그램 메뉴 클릭/창이뜨면
다음클릭/idx_01테이블을 선택해서 오른쪽 리스트박스로 추가, 그리고 다음버튼클릭/마침
다시 idx_01 테이블에서 오른쪽 버튼을 눌러서 “인덱스/키“ 메뉴를 클릭하거나, 속성을 클릭해서
”인덱스/키“ 탭을 클릭
2.INDEX 설정
인덱스에 줄 수 있는 옵션 설정들은 Fillfactor, Pad_index, 인덱스종류바꾸기(넌클러스터드를 클러스터드로)
등등이 있다. 하나하나 개념과 함께 짚어가면서 설명이 될 것이다.
1) Fillfactor
PFS(Page Free Space ,사용가능한 공간) 페이지란 얘기를 이전 챕터에서 들어보았을 것이다.
이 페이지가 가지고 있는 정보는 어떤 페이지의 남은 여유공간에 대한 정보이다.
한 페이지가 가질 수 있는 데이터의 한계는8060바이트이며, 이 중 현재 얼마나 남았는가에 대한 정보이다.
만약 페이지의 여유공간이 아직 남아 있는 상태라면, 새로운 데이터를 입력할 때 혹은 새로운
인덱스 키 값이 입력될 때 그 값들이 들어갈 수 있다. 그러나, 여유공간이 전혀 없다면 어떻게 될 것인가...
이럴 때는 페이지 분할(Page Split) 현상이 발생하게 된다. 예를 들어서 설명해 보기로 하겠다.
Create Table idx_03 -- 테이블 만들기
( Iid char(900) Primary key NonClustered, Iname char(600))
GO
-- Iname 컬럼에 클러스터드 인덱스 생성하기
Create Clustered Index idx_idx_03_iname_C On idx_03(Iname)
GO
Insert idx_03(Iid, Iname) Values (1, '지오디')
Insert idx_03(Iid, Iname) Values (2, '싸이')
Insert idx_03(Iid, Iname) Values (3, '이소라')
Insert idx_03(Iid, Iname) Values (4, '하늘')
Insert idx_03(Iid, Iname) Values (5, '왁스')
Insert idx_03(Iid, Iname) Values (6, '나훈아')
Insert idx_03(Iid, Iname) Values (7, '김수한')
Insert idx_03(Iid, Iname) Values (8, '크라잉넛')
Insert idx_03(Iid, Iname) Values (9, '어떤날')
Insert idx_03(Iid, Iname) Values (10, '시인과촌장')
Insert idx_03(Iid, Iname) Values (11, '유리상자')
Insert idx_03(Iid, Iname) Values (12, '김광석')
Insert idx_03(Iid, Iname) Values (13, '푸른하늘') -- 데이터 13개 입력
GO
exec Sp_helpindex idx_03
--SELECT * FROM IDX_03
--drop table idx_03
idx_03 테이블을 만들되, iid 컬럼을 900바이트로 iname 컬럼을 600바이트로 생성했다.
그렇게 하면 한 행 당 대략 1500 바이트로 생성될 것이며, 한 페이지에 5개 행씩 입력되어
총 3개의 데이터페이지를 만들게 될 것이다. 물론 페이지 내의 데이터 량을 계산하는 방법은 따로 있지만,
여기서는 대략적으로 이렇게만 설명하기로 하겠다.
그리고 900바이트(=인덱스를 생성할 수 있는 최대 컬럼 길이)로 설정된 iname 컬럼에 클러스터드
인덱스가 걸려 있으므로, 데이터페이지는 인덱스페이지의 리프레벨이 될 것이다.
즉 데이터페이지가 iname 컬럼으로 정렬되어 있는 형태로 저장되어 있을 것이라는 말이 된다.
참조) 그림.. PageSplit_저장구조.bmp
구조를 보면, 데이터페이지는 3개로 구성되어 있으며, 상위레벨은 바로 Root 레벨이 되었다.
물론 그리고, iid 컬럼에 대한 넌클러스터드 인덱스도 따로이 존재하고 있을 것이다.
여기까지는 지금까지 설명한 바와 같다. 그러나, 여기 iname 컬럼에 어떤 데이터를 입력한다면
얘기가 틀려질 것이라는 것이다.
예를 들어서, “14,김대중”이라는 데이터를 입력한다고 해보자. 이 “김대중” 데이터는 클러스터드
인덱스이며 2306페이지에 입력되어야 한다. 그러나, 현재 2306 페이지는 (5개의 데이터*5=7500바이트)
정도의 용량을 가지고 있어서, 더 이상 들어갈 공간이 없다.
이럴 때 페이지분할이 일어나게 되는 것이다.
페이지 분할 시에는 다음과 같은 상황이 발생하게 된다.
------------
- 새로운 페이지인 2309 페이지에, 2306 페이지의 반정도가 할당될 것이다
- 2306 페이지의 절반정도가 여유공간이 되고, 새 데이터를 입력할 수 있게 된다.
그래서, “김대중” 데이터는 정렬되어서 제 자리에 쓰여질 수 있게 되는 것이다. 그러나 이 Page Split 은
시스템에 부하를 많이 주며, 다른 페이지까지 영향을 주므로 최대한 일어나지 않는 것이 좋다.
참조) 그림.. PageSplit_저장구조_after.bmp
새로운 페이지 2309가 생겼고 페이지의 행 반정도가 옮겨갔으며, “김대중”데이터는 2306 에 입력되었다.
그러나, 페이지가 하나 생기고 행 할당작업이 일어나고, 상위레벨의 포인터들도 다시 정렬되는 과정을
겪게 되므로 시스템에는 많은 부하가 걸리게 된다. 만약 넌클러스터드 인덱스가 데이터페이지의
페이지 번호와 행 번호를 저장하고 있었다면, 모든 넌클러스터드 인덱스의 정보도 함께 변경되어야 할 것이다. 넌클러스터드 인덱스가 많으면 많을수록 엄청난 Load 가 걸릴 수 있다.
그러나, SQL Server 7.0 및 2000 Edition 에서는 클러스터드 색인의 키 값만을 저장하게 함으로써,
데이터페이지에서 페이지 분할이 일어나더라도 넌클러스터드 인덱스의 데이터들은 거의 변화가
없도록 인덱스 구조를 바꿔서 획기적인 성능향상을 가져왔다.
변경이 자주 일어나는 컬럼이라면 이런 현상이 자주 발생하게 될 것이고 페이지 분할은 더 자주 발생이 된다.
이것은 데이터베이스의 성능을 악화시킨다. 그래서 페이지 분할을 애초부터막기 위해 Fillfactor(채우기비율)
라는 인덱스 옵션이 존재하는 것이다.
이 옵션이 뜻하는 것은 8060 바이트의 페이지 공간 중 얼만큼까지 데이터를 채울 것인가 이다.
이것은 나중에 페이지 중간에 데이터가 입력될 경우를 대비한 것이다.
만약 인덱스의 Fillfactor 값을 80%로 주었다고 한다면, 해당 인덱스페이지는 8060 바이트의 80%인
약 6400 바이트정도만 데이터로 채운다. 그리고, Update 작업이나 Insert 작업으로 인해 페이지에
새로운 데이터가 입력되면, 빈 공간을 이용해서 데이터를 할당하는 것이다.
그림 PageSplit_저장구조.bmp, PageSplit_저장구조_after.bmp 의 내용은 Fillfactor 값이 100%일때의 상황이다.
그리고 이것이 기본설정값이다. 만약 Fillfactor 값이 80%인 경우로 바꾸어서 본다면, 다음과 같이 될 것이다.
참조) 그림.. Fillfactor.bmp
처음부터 데이터페이지에 80%만 데이터가 채워지게 된다. 그래서 한 페이지 당 4개의 행씩만 할당되었다.
이 때 페이지 수가 늘어나게 되는 것은 Fillfactor 옵션을 줄 때의 부정적인 면이다.
그래서 Fillfactor 값을 너무 낮게 주면 페이지의 수가 많아져서, 결과적으로 항상 많은 수의 페이지 I/O를
일으키게 되므로 반드시 주의해야 하는 사항이다. 어쨌든, 처음부터 4개의 페이지에 할당되어 들어가고,
나머지 공간 20%정도는 여유공간이 된다. 이때, 새로운 데이터 “김대중”을 입력한다면
참조) 그림.. Fillfactor_after.bmp
데이터가 입력이 되어서 2306 페이지의 여유공간에 새 행을 할당했고, 페이지의 행 수는 4개에서
5개로 늘어났다. 하지만, 페이지 분할 현상이 일어날 필요가 없으므로 전체 페이지 수는
그대로 4개이고, 상위레벨의 데이터도 마찬가지로 아무런 변동이 없다.즉 그만큼 부하가 덜 걸리는 것이다.
이런 이유로 Fillfactor 옵션을 사용한다.
그러나, 이 옵션을 사용할 때 항상 조심해야 할 것은 Fillfactor 값이 낮으면 낮을수록 인덱스 페이지가
더 많이 소모되므로 I/O를 많이 일으키게 되므로, 적절한 값을 설정해야 한다는 것이다.
더구나 위의 예제처럼 클러스터드 인덱스에 Fillfactor 옵션을 줄 때는 더더욱 그러하다.
클러스터드 인덱스에서, 인덱스페이지의 리프레벨 자체가 데이터페이지이기 때문이다.
만약 Fillfactor 옵션 값을 50%로 준다면 읽기시간은 2배로 늘어날 것이다. 어떤 테이블에
클러스터드 인덱스가 없다면, Fillfactor 옵션은 데이터페이지와는 상관없다. Fillfactor 옵션은
인덱스에만 적용된다.
-- 기존에 존재하는 인덱스에 Fillfactor 옵션을 설정할 때
exec Sp_helpindex idx_03
/*
위 명령으로 확인결과, 필자의 예제에서는 idx_idx_03_iname_C
라는 이름의 인덱스가, iname 컬럼에 적용되어 있다.
여기에 Fillfactor 를 설정해 본다.
*/
Create Clustered index idx_idx_03_iname_C
On idx_03(iname)
With Drop_existing, -- 인덱스 변경을 위해 반드시 필요하다.
Fillfactor = 80 -- 채우기비율을 80%로...
Go
--select * from idx_03
그러면, 인덱스가 재작성 되면서 페이지가 분할되고 성능에도 영향을 미친다.
인덱스 생성작업은 작업(트랜잭션)이 일어나지 않는 시간대에 실행하는 것이 좋다.
위의 명령을 살펴보면, 이미 있는 인덱스임에도 불구하고 “Create .." 문을 사용하는 것을 볼 수 있다.
원래 DB 나 TABLE 같은 개체의 구조변경은 ”ALTER..." 문으로 하는 것이 정상이다.
그러나 인덱스에 대한 변경은 예외이다.
존재하는 개체의 이름과 똑같이 해서 “WITH Drop_Existing" 문을 사용해서 변경을 해야 한다.
다만 이 문장을 사용해도 클러스터드 인덱스를 넌클러스터드 인덱스로바꿀 수 없다.
(넌클러스터드는 클러스터드로 바꿀수 있다.
위의 쿼리는 기존의 있는 인덱스에 채우기 비율을 설정할 때의 예제이다.
그러나, 채우기비율은 인덱스를새로 생성할 때 주는 것이 정석이다.
다음과 같이 하는 것이 정석이라는 이야기다. 처음에 fillfator비율을 할당하는 것이 좋다.
Create Table idx_04
( iid int primary key, iname varchar(20))
GO
Create index idx_04_iname_NC
On idx_04(iname)
With Fillfactor = 80
GO
생성후의 확인은 다음과 같이 하면 된다.
EM/idx_04테이블에서 오른쪽버튼/테이블 디자인/도구 중 저장버튼 오른쪽에 있는,
테이블 및 인덱스 속성단추 클릭/새로 열린 창의 인덱스&키 탭 클릭/“선택한 인덱스“
콤보박스를 눌러서, 위 쿼리에서 생성한 인덱스 이름 선택
글 상자의 내용대로 차례차례 클릭해서 실행하면, 해당 인덱스 idx_04_iname_NC 의
채우기비율이 80%로 되어 있는 것을 확인할 수 있다.
SQL Server 2000에서 Fill factor의 기본값은 0이며 설정할 수 있는 값의 범위는 0부터 100까지이다.
Fill factor 값이 0 이어도 실제 페이지의 채우기 정도가 0%라는 의미는 아니다. 사실 0 값을 주면
------------------
100% 로 처리된다. 그러나 B-Tree 인덱스의 상위 레벨에 일부 공간을 남겨둔다는 점에서 100% 로
------------------
설정하는 것과는 다르다.
인덱스 수준에서가 아니라, 서버 레벨에서도 Fillfactor 옵션을 적용할 수 있다.
그렇게 되면 서버 내의 모든 데이터베이스에서, 모든 인덱스는 동일한 Fillfactor 값을 적용받게 된다.
이렇게 하려면 SQL 문장으로 SP_Configure 옵션을 사용하거나, EM 의 서버설정에서 변경하는
방법 둘 중 하나를 써야 한다. Fillfactor 옵션 뿐 아니라 서버 레벨 옵션의 변경은, 이 두 가지 중
하나를 선택해서 변경한다.
-- 현재 설정보기, config_value 컬럼이나 run_value 컬럼값이 현재 설정값이다.
-- 현재는 0 이다.
exec Sp_Configure 'Fill Factor'
go
-- 80% 로 설정값을 변경한다.
exec SP_Configure 'Fill Factor', 80
go
-- 변경된 것을 확인해보자.
exec Sp_Configure 'Fill Factor'
go
-- config_value 컬럼만 80으로 변경되었다.
-- 그러나, 이 옵션은 서버를 재시작해야만 적용된다.
EM 에서의 설정
서버이름에서 오른쪽버튼, 등록정보 클릭/데이터베이스설정 탭 클릭/상단의 설정 프레임에
“고정“체크박스가 확인된 것을 확인하고, 마우스로 드랙%드롭해서 값을 변경한다.
2) PAD_INDEX
인덱스를 생성할 때 Fillfactor 값을 설정한다고 해서, 인덱스 트리의 모든 레벨이 전부 80% 값을
적용받는 것은 아니다. 다만, 제일 하위레벨인 리프레벨에만 적용될 뿐이다. 그렇게 하는 것이 좋다.
페이지 분할 이 일어난다고 해도 상위레벨의 인덱스 페이지들까지 페이지 분할이 일어나는 일은
별로 없기 때문이다. 클러스터드 인덱스나 넌클러스터드 인덱스 모두 이것은 똑같다. 대량의
데이터변경이 일어날 때만 상위레벨에도 영향을 미치게 된다. 이럴 때 사용되도록 Pad_Index 라는
옵션이 존재한다.
이 옵션은 리프레벨 뿐 아니라 상위의 넌리프레벨까지 Fillfactor 값이 적용되도록 해 주는 역할을
하는 옵션이다. 모든 레벨에서 Fillfactor 값은 똑같이 적용된다.
Create Table idx_05
( iid int primary key, iname varchar(20))
GO
Create index idx_05_iname_NC
On idx_05(iname)
With Fillfactor = 80, PAD_INDEX
GO
3) IGNORE_DUP_KEY
이 옵션은 UNIQUE 인덱스가 있는 컬럼에 중복된 키 값을 입력했을 때, 트랜잭션을
어떻게 처리할 것인가에 대한 옵션이다.
-- 1) 테이블 생성
Create table ign_01 ( iid int , iname varchar(20))
go
-- 2) iid 컬럼에 UNIQUE INDEX 생성
Create unique index idx_ign_01_iid On ign_01(iid)
go
insert ign_01(iid, iname) values(1, 'aaa')
go
-- 3) iid 에 중복된 값을 입력한다. 에러를 내고 트랜잭션을 롤백한다.
insert ign_01(iid, iname) values(1, 'bbb')
go
-----------------------------------------------------------------------------------------
Drop Table ign_01 -- 1-1) 테이블을 삭제하고, 1)번 명령을 다시 실행한다.
go
-- 2-1) IGNORE_DUP_KEY 옵션을 주어서 다시 인덱스 생성
Create unique index idx_ign_01_iid On ign_01(iid)
with IGNORE_DUP_KEY
go
insert ign_01(iid, iname) values(1, 'aaa')
go
-- 3) iid 에 중복된 값을 입력한다. 경고를 발생시키지만, 트랜잭션이 롤백되지는 않는다. (=커밋된다)
* 수행된다 그러나 트랜잭션입장에서는 차이가 있다는 뜻이다.?
insert ign_01(iid, iname) values(1, 'bbb')
go
SELECT * FROM IGN_01 -- 옵션에 관계없이 데이터는 입력되지 않는다.
4) 내림차순 인덱스
SQL Server 2000 에서는 이전버전과 달리, 내림차순 인덱스도 지원한다. 이것은 높은 값의
데이터를 질의할 때, 넌리프레벨에서의 인덱스 페이지 I/O를 감소시키므로 약간의
성능향상을 가져올 수 있다.
CREATE TABLE IDX_05
( IID INT, INAME VARCHAR(20), IDATE DATETIME)
GO
CREATE INDEX IDX_05_IDATE
ON IDX_05(IDATE DESC)
GO
/*
이렇게 IDATE 컬럼에 내림차순 인덱스를 주면,
최근날짜로 자주 검색하는 쿼리에서 페이지 I/O를 줄이는
효과를 볼 수 있다.
*/
--drop table idx_05
IDX_05 라는 테이블을 생성하고, IDATE 컬럼에 내림차순 인덱스를 생성했다.
그러므로 현재날짜나 최근날짜를 조건으로 검색하는 쿼리에 사용하면 좋다.
또한, 특히 SQL Server 2000 에서는 뷰에도 인덱스를 줄 수가 있다. 원래 뷰는
가상의 테이블이며 뷰에는 데이터가 존재하지 않는다. 뷰는 그 정의만 SQL Server 에 존재할 뿐이다.
그러나, SQL Server 2000 에서는 뷰에 클러스터드 인덱스를 줌으로써 뷰를 실제로 존재하게 만들 수 있다.
그렇게 되면 수많은 데이터 중에서 원하는 데이터만을 뷰로 만들어 놓고 인덱스를 주어,
그 뷰를 검색하면 상당히 나은 쿼리성능 향상을 볼 수 있다.
그러나 한편, 데이터변경(Insert,Update,Delete) 시에는 뷰까지 변경해야 하므로 오히려
역효과를 볼 수 도있다. 그러므로, 뷰에 인덱스 생성하기는 조심스럽게 실제 성능테스트를 해서
설정되어야 할 것이다.
3. 인덱스 관리
만들어진 인덱스는 계속적으로 관리를 하고, 상황에 따라 삭제 및 재생성 해야 할 수 도 있다.
생성 및 삭제에 관해서는 이미 알아보았으므로, 다른 사항에 대해서도 알아보자.
1) 조각 모음
데이터베이스를 사용하다 보면 항상 데이터는 조각이 나게 되어 있다. 데이터베이스 수준에서
조각모음을 하려면, DBCC SHRINKDATABASE ..명령을 사용하면 되고, 인덱스 수준에서
-------------------------
(분할 안나타난다)
조각모음을 하려면, 다음 두 가지 방법 중 하나를 사용하면 된다.
- DROP_EXISTING 옵션을 사용해서 인덱스 재생성하기
- DBCC INDEXDEFRAG 명령을 실행하기
DBCC INDEXDEFRAG 옵션을 사용하면, 인덱스의 논리적인 정렬순서에 맞게 인덱스 페이지들을 다시
정렬해 준다. 이 작업은 테이블이나 인덱스를 잠그지 않고, 트랜잭션에 영향을 미치지 않으므로
온라인 상에서도 얼마든지 할 수 있다. 그러나, 이 작업은 디스크에서의 물리적인 순서를 바꾸지
않기 때문에 물리적인 I/O 성능은 향상시키지 못한다. 차라리 DROP_EXISTING 옵션을 사용하거나,
인덱스를 삭제하고 다시 생성하는 방법을 더 권장한다. 이것은 실제로 디스크상의 조각난
인덱스페이지들을 조각모음해서 재정렬하는 방법을 사용하기 때문이다. (성능적으로 더 유리하다.)
만약 클러스터드 인덱스를 재작성하려면, 해당 인덱스공간의 1.2배 정도 되는 공간이 사용되어야
하므로 미리 이 공간이 확보되어 있어야 한다. 이렇게 조각모음을 하면 스캔성능이 향상된다.
시스템 테이블 ( = 마스터테이블)에 대한 인덱스는 다시 작성될 수 없다.
2) COVERED INDEX
SQL Server 에서는 두 개 이상의 컬럼에 하나의 인덱스를 줄 수 있는데, 이것을 복합인덱스라고 한다.
이 복합인덱스는 시스템에 무리를 주기 때문에 잘 사용되지 않는다. 그러나 사용할 필요가
생길 때가 있는 데, 다음과 같은 경우에 그렇다. 먼저 다음 쿼리를 실행해 보라.
USE NORTHWIND
SELECT * FROM EMPLOYEES
실행해보자. 9개의 데이터가 출력이 된다. 컬럼은 18 개가 있다. 9개가 아니라 많은 데이터가 있을수록
이 내용은 더 유효하게 된다. LASTNAME 컬럼에 클러스터드 인덱스가 있다고 생각해 본다.
여기에 Lastname 컬럼과 Title 컬럼에 복합인덱스가 하나 있다고 가정하겠다.
그러면, 복합인덱스를 생성하기 전과 생성한 후, 쿼리의 유형에 따라 쿼리계획이 어떻게 바뀌는가
인덱스를 어떤 식으로 사용하는가 비용이 어떻게 변하는가에 대해서 예를 통해서 알아보자.
USE NORTHWIND
GO
SELECT * FROM EMPLOYEES -- 9 개의 데이터가 있다.
-- 1) 두 컬럼을 조건으로, 두 컬럼을 불러내는 쿼리를 한다.
SELECT lastname, title FROM EMPLOYEES
where lastname = 'buchanan' and title like 's%'
-- 2) Lastname,Title 컬럼에 복합인덱스를 생성한다.
CREATE INDEX IDX_EMPLOYEES_LASTNAME_TITLE_NC
ON EMPLOYEES(LASTNAME, TITLE ASC)
GO
-- 3) 인덱스 생성 뒤, 두 컬럼을 조건으로, 전체 데이터를 불러내는 쿼리를 한다.
SELECT * FROM EMPLOYEES
where lastname = 'buchanan' and title like 's%'
-- 4) 인덱스 생성 뒤, 두 컬럼을 조건으로, 두 컬럼을 불러내는 쿼리를 한다.
SELECT lastname, title FROM EMPLOYEES
where lastname = 'buchanan' and title like 's%'
-- 5) 인덱스 생성 뒤, Lastname 컬럼을 조건으로, 두 컬럼을 불러내는 쿼리를 한다.
SELECT lastname, title FROM EMPLOYEES
where lastname = 'buchanan'
-- 6) 인덱스 생성 뒤, Title 컬럼을 조건으로, 두 컬럼을 불러내는 쿼리를 한다.
SELECT lastname, title FROM EMPLOYEES
where title like 's%'
--Drop index employees.IDX_EMPLOYEES_LASTNAME_TITLE_NC
--sp_helpindex employees
쿼리계획을 보는 것이 목적이므로, 실행할 때에는 실행단추를 누르지 말고
“예상실행계획표시”단추를 반드시 눌러서 계획을 확인한다. 현재는 lastname 컬럼에만
넌클러스터드 인덱스가 있다는 사실을 잊지 말자.
1) lastname 컬럼과 title 컬럼을 동시에 조건으로, lastname,title 컬럼을 조회하고 있다.
현재 lastname 컬럼에만 인덱스가 있다. 이 명령의 쿼리계획을 보면 lastname 컬럼의 인덱스를
사용하지만 title 컬럼은 인덱스가 없어서 사용치 못한다. 그래서 예상 비용이 약 반반씩으로
나누어져 있다. 합해 보면, 약 0.0126 이라는 예상 비용이다.
2) 그래서, lastname,title 컬럼에 복합 넌클러스터드 인덱스를 설정한다.
3) lastname 컬럼과 title 컬럼을 동시에 조건으로 데이터를 검색하되, lastname,title 컬럼 뿐 아니라,
전체 컬럼을 모두 을 조회하고 있다. 실행계획은 예상 비용은 1)의 예제와 같다.
4) lastname 컬럼과 title 컬럼을 동시에 조건으로 데이터를 검색하되, lastname,title 컬럼
만을 조회하고 있다. 이 때 우리가 만든 복합인덱스를 사용하면 예상 비용은 인덱스하나만을
사용한 비용인 0.006408을 나타낸다. 그러므로 lastname 컬럼에 있던 인덱스 하나만을
사용할 때의 절반정도 밖에 되지 않는다. I/O 가 줄어들 것이며 실행시간은 그만큼 빨라질 것이다.
5) lastname 컬럼만을 조건으로 데이터를 검색하되, lastname, title 컬럼을 조회한다.
예상비용은 1)의 예제와 같다.
6) title 컬럼만을 조건으로 데이터를 검색하되, lastname, title 컬럼을 조회한다.
예상비용은 지금까지의 5개의 쿼리 중 제일 많다.
그 이유는 인덱스를 사용하지 않기 때문이다.
“index seek"는 인덱스를 사용한다는 얘기지만, 6)번의 예제는 ”index scan" 이라고 예상쿼리계획을 보여준다. 이것은 인덱스를 사용하지 않는 다는 얘기이다.
그러므로, 전체 테이블 스캔할 때와 같을 비용인 0.037662 이다.
만약 title 컬럼에 인덱스를 설정한다면 1)번과 같은 비용을 나타낼 것이다.
이것은 테이블 스캔시보다 약 1/3정도 줄어든 효과를 보인다.
4. STATISTICS 관리
통계정보는 SQL Server 2000에서 자동으로 관리하는, 데이터 및 인덱스 페이지에 대한 구성정보이다.
실제로 데이터 및 인덱스가 어떻게 분포되어 있는지 분포도와 밀도, 선택도 정보를 갖고 있다.
SQL Server 는 이 정보와 사용자의 쿼리유형을 이용해서 쿼리계획(Query Plan =예상실행계획)을 생성하게 된다.
즉 어떤 데이터가 얼마만큼 어떻게 저장되어 있는 지를 파악해서, 사용자의 쿼리유형을 분석한 다음,
어떻게 해야 가장 빠른 방법으로 쿼리를 실행할지를 판단한다는 것이다. 이 판단의 결과가 쿼리계획이고,
쿼리는 이 계획에 따라 실행된다. 계획은 첨부터 하나를 선택해서 바로 실행할 수도 있고,
몇 개의 계획을 후보로 만들어서 그 중 가장 나은 것을 선택하는 방식으로 실행되기도 한다.
통계정보에 대한 데이터베이스의 기본설정은 통계정보자동작성,자동갱신이다.
인덱스가 만들어지거나, Where 절에서 자주 조회되는 컬럼에 대해서 SQL Server 는 통계를 만들고
주기적으로 갱신한다. 뿐만아니라 데이터의 대량 변경시에도 이를 감지하고 자동으로 갱신해준다.
그러므로 사용자나 관리자는 통계정보를 수동으로 관리해 줄 필요가 거의 없다.
그러나, 개념을 알면 다른 내용을 이해하는 데 많은 도움이 되므로 알고 넘어갈 필요가 있다.
먼저 자동옵션에 대한 설정사항을 확인해 보자.
EM / 해당 DB 속성 / 옵션 / 통계자동 업데이트
CREATE TABLE STAT_01
(SID CHAR(900) PRIMARY KEY, SNAME CHAR(900))
GO
CREATE INDEX IDX_STAT_01_SNAME
ON STAT_01(SNAME)
GO
CREATE STATISTICS STAT_SNAME (통계자료이름)
ON STAT_01(SNAME)
WITH SAMPLE 50 PERCENT
-- WITH FULLSCAN
GO
--DROP TABLE STAT_01
이미 SID 컬럼은 기본키여서(클러스드) 인덱스가 있으므로 통계정보가 생성되었을 것이고,
SNAME 컬럼에 인덱스를 주고 50% 만 샘플링해서 통계정보를 생성해 본 예제이다.
중복된 데이터가 많으면 인덱스를 사용하는 것이 오히려 좋지않다.
여러 단계를 거치게 되므로 더 많은 I/O를 일으킬 가능성이 많다.
그러므로, 인덱스가 있어도 얼마나 중복이 되어 있느냐에 따라서 인덱스는 사용될 수 도 있고
사용되지 않을 수도 있다. 이 모든 것을 SQL Server 가 알아서 판단하게 되는 데,
중요한 것은 얼마나 중복되어 있는 지를 알아야 한다는 것이다. 그래서 통계정보가 중요하다.
얼마나 어떻게 중복되어 있는지에 대한 정보를 저장하고 있는 것이 바로 통계정보이기 때문이다.
넌클러스터드 인덱스에서는 데이터중복도가 3%을 넘으면 쿼리실행시 인덱스를 사용하지 않는다.
3%미만이라면 인덱스를 사용한다. 물론 사용자의 쿼리유형도 상당히 중요한 역할을 한다
(쿼리유형에 대해서는 이 책의 다른 부분에서 다루고 있다).
그러나 통계정보에 따라서 인덱스를 사용할지 하지 않을 지에 대해, 쿼리계획이 변경되는 것이다.
인덱스를 쓰지 말아야 할 쿼리에 인덱스를 사용하는 쿼리계획을 생성한다면,
그것은 그냥 테이블스캔을 하는 것보다 몇배 몇십배 I/O와 쿼리비용이 증가될수 있다.
그러므로 인덱스선택은 매우 신중히 고려되어야 하고, SQL Server 는 이것을 알아서 해준다.
사용자가 인덱스선택을 할 경우는 거의 없다. 기존에 있는 테이블을 예제로 통계정보를 한번 보자.
USE pubs
-- 통계보기, 매개변수는 테이블명과 인덱스 이름이다.
DBCC SHOW_STATISTICS (authors, UPKCL_auidind)
GO
PUBS 데이터베이스에 있는 AUTHORS 테이블의 인덱스에 대한 통계정보를 보여주는 예제이다.
이 인덱스는 AU_ID 컬럼에 주어진 인덱스이다.
마지막으로 업데이트된 시간과 전체행(23개), 그리고 샘플된 ROW(23개), 또 밀도 등이 나타나 있다.
-- ID 컬럼에 UNIQUE 인덱스 생성해서 테이블 만들기
CREATE TABLE IDX_TUNE1
(ID INT UNIQUE, NAME CHAR(12), SAL INT)
GO
-- NAME 컬럼에 클러스터드 인덱스 생성
CREATE CLUSTERED INDEX IDX_TUNE1_NAME
ON IDX_TUNE1(NAME)
GO
-- 8000 건의 데이터 입력
DECLARE @NO INT
SET @NO = 0
WHILE (@NO < 20000)
BEGIN
BEGIN TRAN
INSERT INTO IDX_TUNE1 VALUES(@NO * 4 + 0, 'KIM', 700)
INSERT INTO IDX_TUNE1 VALUES(@NO * 4 + 1, 'KANG', 700)
INSERT INTO IDX_TUNE1 VALUES(@NO * 4 + 2, 'SEO', 700)
INSERT INTO IDX_TUNE1 VALUES(@NO * 4 + 3, 'PARK', 700)
SET @NO = @NO + 1
COMMIT TRAN
END
GO
SELECT COUNT(*) FROM IDX_TUNE1
실행하면 통계정보는 자동으로 생성되고 수정이 될 것이므로 우리가 신경쓰지 않아도 된다.
그러나, 혹시나 하는 마음에 통계정보를 수동으로 업데이트하고, 확인해보면, 다음과 같다.
-- 인덱스(IDX_TUNE1_NAME) 에 대한 통계업데이트
UPDATE STATISTICS IDX_TUNE1(IDX_TUNE1_NAME)
WITH SAMPLE 50 PERCENT
GO
DBCC SHOW_STATISTICS ( IDX_TUNE1, IDX_TUNE1_NAME )
/*
EXEC SP_HELPINDEX IDX_TUNE1
-- ID 컬럼과 NAME 컬럼에 각각,
-- UQ__IDX_TUNE1__39237A9A,IDX_TUNE1_NAME 이란 이름의 인덱스존재.
SELECT * FROM SYSINDEXES
WHERE ID = OBJECT_ID('IDX_TUNE1')
*/
결과창의 “RANGE_HI_KEY" 값이 바로 인덱스를 생성한 컬럼의 값들이다. 모든 값의 종류가 전부
출력된다. 현재 여기에는 4개의 값밖에 없기 때문에 그것만 출력이 된 것이다. 그리고 ”EQ_ROWS"
----
=equal(같다)
컬럼값이 현재 해당 데이터가 얼마나 중복이 되어 있는가에 대한 정보이다.
SQL 문에서, 각각 2000건씩 입력했으니, 입력한 그대로 2000 씩 보여주고 있다.
통계정보는 이렇게 해서 확인하면 되겠다. 통계정보를 수동으로 업데이트하는 명령은
“UPDATE STATISTICS..."외에도 SP_UPDATESTATS 라는 명령이 하나 더 있다.
먼저 UPDATE STATISTICS 명령은 특정 테이블의 특정 인덱스에 걸린 통계정보만 갱신하는 반면,
SP_UPDATESTATS 명령은 데이터베이스의 모든 테이블에 대해 한꺼번에 통계정보를 갱신하는
막강한 힘을 가지고 있다.
-- 모든 통계정보를 갱신한다.
USE DBSYS
EXEC SP_UPDATESTATS
DB에서 어떤 데이터를 찾으려면, 인덱스라는 것이 상당히
필요하다. 이 것은 페이지I/O를 상당히
줄여줌으로 인해서 쿼리성능을 향상시키기 때문이다
인덱스는 검색속도와 데이터변경속도를 높이기 위해서 존재한다.
데이터베이스의 인덱스라는것은 책의 색인 부분과 너무도 많이 닮아있다.
만약 여러분이 읽고 있는 이 책의어디에도 목차나 색인이 없다면,
여러분들은 원하는 단어를 어떻게 찾을 것인가?
만약 “클러스터드“라는 단어를 검색한다고 생각해 보자. 책의 처음부터 한페이지 한페이지 넘겨가면서
찾을 수 밖에 없다. 그렇게 넘겨가면서 다행히 2번째 페이지에서 ”클러스터드“라는 단어를 찾았다.
거기서 그냥 끝낼 것인가? 안타깝게도 그럴 수 없다.
이 후의 어떤 페이지에 또 ”클러스터드“라는 단어가 있을지 알 수 없는 일이기 때문이다.
결과적으로, 만약 어떤 책이 1000페이지로 구성되어 있다면, 1000페이지를 전부 다 검색해보아야
원하는 정확한 결과를 얻을 수 있다는 얘기다. 이전의 파일데이터베이스에서는 데이터를 이렇게
검색할 수 밖에 없었다. 그러나, 색인이 있다면 상황은 180도 달라진다.
책 맨 뒤에 보통 색인이 있고 어떤 단어가 어떤 페이지에 있는지를 기록해 놓고 있다.
인덱스도 이와 똑같은 구조를 가진다(사실 넌클러스터드 인덱스만 있을 때 같다.).
몇 페이지에 있는지를 알고 있으므로, 해당 페이지만 엑세스 즉 I/O를 하면 된다.
다른 페이지엔 없다는 것을 앎으로 아예 I/O를 일으키지 않아도 되는 것이다.
만약 10개의 페이지에 “클러스터드”라는 단어가 있다면, PAGE I/O 10이면 된다는 얘기다.
여기에 색인이 있는 페이지도 검색해야 하므로 약간 I/O가 더 늘어난다.
그래봐야 11~12번 정도이다. 1000번의 I/O를 일으킬 때와는 비교가 되지 않는 것이다.
물론 데이터의 저장상태에 따라서 인덱스를 쓰지 않는 것이 더 빠를 때도 있다. 하지만,
기본적으로 관계형 데이터베이스에서의 강점은 관계로써 무결성을 강화하고, 한 테이블에
인덱스를 여러개 둘 수 있게 함으로써 속도를 빨리하는데 있다.
책에서 인덱스를 사용하면 책을 다 읽지 않고도 특정 내용을 빠르게 찾을 수 있다. 마찬가지로
데이터베이스에서 인덱스를 사용하면, 전체 테이블의 모든 페이지를 스캔하지 않고도 데이터를
찾을 수 있다. 이것이 인덱스가 필요한 이유이다.
* 인덱싱 지원 : 데이터 베이스에 대해 정의된 전체 텍스트 카탈로그와 인덱스를 구현하고 색인 채우기를 한다.
* 쿼리 지원 : 전체 텍스트 검색 쿼리를 처리한다.
* 선행 작업 목록
1. 전체 텍스트 서비스를 설치하고 시작한다.
2. 전체 텍스트 색인에 포함될 컬럼을 정의하여 색인을 만든다.
3. 카탈로그에 등록시킨다.
4. 색인에 데이터를 갱신한다.
5. 주기적으로 색인을 갱신한다.
* 전체 텍스트 색인의 특성
1. 데이터베이스에 저장되지 않고 다른 곳에 저장된다.
2. 자동으로 갱신되지 않는다.
3. create index문을 사용하는 것이 아니다. 프로시저를 사용한다.
* 프로시저 : 사용자가 편리하게 호출만 하면 불러와지는 미리 만들어진 절차들
ex) sp_help~~~~~~
4. 테이블당 하나의 색인만 존재할 수 있다.
5. 카탈로그라는 것으로 함께 모여져 관리된다.
6. image 컬럼에 특정 유형(.doc,.hwp....)의 문서가 있으면 전체 텍스트 검색이 필터를 사용하여
데이터(image, binary)를 처리한다.
7. pk (=primary key),unique 와 연결되어야 한다.
1. INDEX 생성 및 삭제
1) INDEX 만들기
기본키를 설정하면 기본키 컬럼에 기본값으로 클러스터드 인덱스가 생성된다.
이것은 별로 권장되지 않는다. (기본키는 유일키이기때문에 인덱스를 하나마나이다.)
(기본키로 설정하면 자동으로 인덱스가 형성된다.)
자주 조회되는 조건컬럼이며 값이 중복된 컬럼에 주는 것이 더 효과적이다.
----------------------------------------------------------------------
이것은 데이터베이스 물리적 설계단계에서 항상 기억해야 할 중요한 일이다.
Create Table Idx_01
( id int Primary Key, Name Varchar(20))
Go
exec Sp_HelpIndex idx_01
Go
Create Table Idx_02
( id int Primary Key NonClustered, Name Varchar(20))
Go
exec Sp_helpindex idx_02
Go
--Drop Table idx_01
--Drop Table idx_02
Create Index idx_idx_01_name
On idx_01(Name)
Go
Create Clustered Index idx_idx_02_name
On idx_02(Name)
Go
exec Sp_HelpIndex Idx_01
exec Sp_HelpIndex Idx_02
UNIQUE INDEX 를 만들어 보자. UNIQUE INDEX 는 클러스터드, 넌클러스터드와는 상관없는 얘기이다.
인덱스의 종류가 아니라 옵션이라고 생각하는 것이 더 옳다. 이것은 똑같은 값이 입력되지 못하도록
---------------------------------------
데이터의 중복을 방지하는 옵션이다. 한편, 이 데이터의 중복방지는 제약 중 “UNIQUE" 제약으로도
----------------------------------
설정이 가능하다. 그러나, 어차피 이 ”UNIQUE" 제약도 내부적으로는 UNIQUE 인덱스를 통해서
구현되는 것이기 때문에, UNIQUE 제약을 준 컬럼엔 UNIQUE 인덱스가 생성되며 UNIQUE 제약을
없애기 전까진 UNIQUE 인덱스도 삭제할 수 없다. 결론은 기본키 제약과 UNIQUE 제약 둘 다,
내부적으로는 UNIQUE 인덱스를 사용해서 중복방지를 구현한다는 것이다.
Create Table UNQ
( Uid Int Primary key, Uname varchar(20) UNIQUE)
GO
EXEC SP_HELPINDEX UNQ
GO
Uname 컬럼에 우리가 명시적으로 생성한 인덱스는 없지만, 이미 Unique 인덱스가 Uname 컬럼에
생성되어 있는 것을 확인할 수 있다.
/* 참고
인덱스를 준 컬럼의 데이터타입 (varchar 등등)은 900바이트를 초과할 수 없다. 사실 900바이트가 아니라,
100바이트도 무리가 된다. 인덱스를 준다는 것은, 해당 컬럼을 Where 절에 써서 조건검색을
한다는 얘기인데, 그 컬럼이 100바이트이면 100바이트 내의 데이터를 일일이 찾고 비교검색을
하느라 성능은 상당히 느려진다. 아울러 인덱스의 키 값 자체가 커지므로 데이터베이스의
전체 크기도 커져서 유지관리하는 데 비용 (=인적 비용)이 늘어나게 된다. 한번 해당 인덱스의 키 값을 변경하면
인덱스 페이지들의 데이터가 다시 정렬되어야 하는데, 인덱스 키 값이 크니까 그만큼 인덱스 페이지도
많고, 많은 인덱스 페이지들의 데이터를 다시 정렬하는 데에 많은 비용이 소모될 것은 당연하다.
이런 이유로 인덱스를 준 컬럼은 900바이트를 넘을 수 없으며, 마찬가지 이유로 Text, Image 컬럼에
인덱스를 주는 것은 불가능하다. 다음과 같이 해보라. 분명 실패할 것이고 이 때 출력되는
에러메시지도 잘 보아두자.
Create Table idx_03
( id char(901) Primary key, name varchar(20))
go
만약 id 컬럼의 데이터타입을 char(900)으로 변경한다면 위 문장은 성공한다.
*/
2) INDEX 삭제하기
idx_01 테이블에 있는 두 개의 인덱스를 지워보도록 하겠다.
이 테이블엔 현재 클러스터드 인덱스와 넌클러스터드 인덱스가 각각 하나씩 존재한다.
Set Nocount ON (서비스가 돌아가는 것을 잠깐 멈춘다.)
go
exec sp_helpindex idx_01
go
-- 1) 넌클러스터드 인덱스부터 제거한다.
Drop Index idx_01.idx_idx_01_name
go
-- 2) 프라이머리 키에 있는 인덱스는 지울 수 없다.
Drop Index idx_01.PK__Idx_01__6EC0713C -- So 에러발생
go
-- 3) 클러스터드 인덱스를 넌클러스터드 인덱스로 변경할 수 도 없다.
Create Index PK__Idx_01__6EC0713C
On idx_01(id)
With Drop_Existing
go
exec sp_helpindex idx_01 -- PK 에 걸린 인덱스는 그대로 남아있다.
go
idx_01 테이블엔 id 컬럼과 name 컬럼에 두 개의 인덱스가 있었다. name 컬럼에 주어진 인덱스는
넌클러스터드 인덱스이므로 잘 삭제된다. 그러나, 기본키인 id 컬럼에 주어진 인덱스는 지울 수 없다.
왜냐하면 기본키는 중복을 방지하는 속성이 있으며, 내부적으로 이것은 Unique Index 로 구현되기
때문이다. 그러므로 기본키는 반드시 Unique Index를 가진다. 그래서 인덱스를 삭제할 수 없다.
클러스터드 인덱스는 넌클러스터드 인덱스로 변경할 수 없다. 그 반대는 얼마든지 가능하다.
아직 테이블에 클러스터드 인덱스가 존재하지 않는다면, 넌클러스터드는 언제든 클러스터드 인덱스로
변환할 수 있다. 클러스터드 인덱스를 넌클러스터드 인덱스로 변환해야 한다면,
그것은 EM에서 해야한다. EM을 열고, 다음 메뉴를 실행해보자.
EM
idx_01테이블에서오른쪽버튼/테이블디자인/
저장단추 바로옆 오른쪽단추인 테이블 및 인덱스 속성단추클릭/인덱스&키탭 클릭
창이 열리면 현재는, 체크박스(CLUSTERED로 만들기)에 체크가 되어 있을 것이다.
체크를 없앤다. 그리고 “닫기”버튼을 누른 후 저장단추를 다시 클릭하면 이제 클러스터드 인덱스는
넌클러스터드 인덱스로 변경되게 된다. 이런 방식으로 인덱스를 관리할 수 있다.
물론 이 창에서 얼마든지 인덱스를 새롭게 만들고 삭제하며, 옵션들을 변경할 수 있다.
그리고 EM 의 다이어그램 창에서도 똑같은 창을 열어서 편집이 가능하다.
EM에서 다음과 같이 다이어그램을 만들어 보자.
다이어그램에서 오른쪽버튼/새 데이터베이스 다이어그램 메뉴 클릭/창이뜨면
다음클릭/idx_01테이블을 선택해서 오른쪽 리스트박스로 추가, 그리고 다음버튼클릭/마침
다시 idx_01 테이블에서 오른쪽 버튼을 눌러서 “인덱스/키“ 메뉴를 클릭하거나, 속성을 클릭해서
”인덱스/키“ 탭을 클릭
2.INDEX 설정
인덱스에 줄 수 있는 옵션 설정들은 Fillfactor, Pad_index, 인덱스종류바꾸기(넌클러스터드를 클러스터드로)
등등이 있다. 하나하나 개념과 함께 짚어가면서 설명이 될 것이다.
1) Fillfactor
PFS(Page Free Space ,사용가능한 공간) 페이지란 얘기를 이전 챕터에서 들어보았을 것이다.
이 페이지가 가지고 있는 정보는 어떤 페이지의 남은 여유공간에 대한 정보이다.
한 페이지가 가질 수 있는 데이터의 한계는8060바이트이며, 이 중 현재 얼마나 남았는가에 대한 정보이다.
만약 페이지의 여유공간이 아직 남아 있는 상태라면, 새로운 데이터를 입력할 때 혹은 새로운
인덱스 키 값이 입력될 때 그 값들이 들어갈 수 있다. 그러나, 여유공간이 전혀 없다면 어떻게 될 것인가...
이럴 때는 페이지 분할(Page Split) 현상이 발생하게 된다. 예를 들어서 설명해 보기로 하겠다.
Create Table idx_03 -- 테이블 만들기
( Iid char(900) Primary key NonClustered, Iname char(600))
GO
-- Iname 컬럼에 클러스터드 인덱스 생성하기
Create Clustered Index idx_idx_03_iname_C On idx_03(Iname)
GO
Insert idx_03(Iid, Iname) Values (1, '지오디')
Insert idx_03(Iid, Iname) Values (2, '싸이')
Insert idx_03(Iid, Iname) Values (3, '이소라')
Insert idx_03(Iid, Iname) Values (4, '하늘')
Insert idx_03(Iid, Iname) Values (5, '왁스')
Insert idx_03(Iid, Iname) Values (6, '나훈아')
Insert idx_03(Iid, Iname) Values (7, '김수한')
Insert idx_03(Iid, Iname) Values (8, '크라잉넛')
Insert idx_03(Iid, Iname) Values (9, '어떤날')
Insert idx_03(Iid, Iname) Values (10, '시인과촌장')
Insert idx_03(Iid, Iname) Values (11, '유리상자')
Insert idx_03(Iid, Iname) Values (12, '김광석')
Insert idx_03(Iid, Iname) Values (13, '푸른하늘') -- 데이터 13개 입력
GO
exec Sp_helpindex idx_03
--SELECT * FROM IDX_03
--drop table idx_03
idx_03 테이블을 만들되, iid 컬럼을 900바이트로 iname 컬럼을 600바이트로 생성했다.
그렇게 하면 한 행 당 대략 1500 바이트로 생성될 것이며, 한 페이지에 5개 행씩 입력되어
총 3개의 데이터페이지를 만들게 될 것이다. 물론 페이지 내의 데이터 량을 계산하는 방법은 따로 있지만,
여기서는 대략적으로 이렇게만 설명하기로 하겠다.
그리고 900바이트(=인덱스를 생성할 수 있는 최대 컬럼 길이)로 설정된 iname 컬럼에 클러스터드
인덱스가 걸려 있으므로, 데이터페이지는 인덱스페이지의 리프레벨이 될 것이다.
즉 데이터페이지가 iname 컬럼으로 정렬되어 있는 형태로 저장되어 있을 것이라는 말이 된다.
참조) 그림.. PageSplit_저장구조.bmp
구조를 보면, 데이터페이지는 3개로 구성되어 있으며, 상위레벨은 바로 Root 레벨이 되었다.
물론 그리고, iid 컬럼에 대한 넌클러스터드 인덱스도 따로이 존재하고 있을 것이다.
여기까지는 지금까지 설명한 바와 같다. 그러나, 여기 iname 컬럼에 어떤 데이터를 입력한다면
얘기가 틀려질 것이라는 것이다.
예를 들어서, “14,김대중”이라는 데이터를 입력한다고 해보자. 이 “김대중” 데이터는 클러스터드
인덱스이며 2306페이지에 입력되어야 한다. 그러나, 현재 2306 페이지는 (5개의 데이터*5=7500바이트)
정도의 용량을 가지고 있어서, 더 이상 들어갈 공간이 없다.
이럴 때 페이지분할이 일어나게 되는 것이다.
페이지 분할 시에는 다음과 같은 상황이 발생하게 된다.
------------
- 새로운 페이지인 2309 페이지에, 2306 페이지의 반정도가 할당될 것이다
- 2306 페이지의 절반정도가 여유공간이 되고, 새 데이터를 입력할 수 있게 된다.
그래서, “김대중” 데이터는 정렬되어서 제 자리에 쓰여질 수 있게 되는 것이다. 그러나 이 Page Split 은
시스템에 부하를 많이 주며, 다른 페이지까지 영향을 주므로 최대한 일어나지 않는 것이 좋다.
참조) 그림.. PageSplit_저장구조_after.bmp
새로운 페이지 2309가 생겼고 페이지의 행 반정도가 옮겨갔으며, “김대중”데이터는 2306 에 입력되었다.
그러나, 페이지가 하나 생기고 행 할당작업이 일어나고, 상위레벨의 포인터들도 다시 정렬되는 과정을
겪게 되므로 시스템에는 많은 부하가 걸리게 된다. 만약 넌클러스터드 인덱스가 데이터페이지의
페이지 번호와 행 번호를 저장하고 있었다면, 모든 넌클러스터드 인덱스의 정보도 함께 변경되어야 할 것이다. 넌클러스터드 인덱스가 많으면 많을수록 엄청난 Load 가 걸릴 수 있다.
그러나, SQL Server 7.0 및 2000 Edition 에서는 클러스터드 색인의 키 값만을 저장하게 함으로써,
데이터페이지에서 페이지 분할이 일어나더라도 넌클러스터드 인덱스의 데이터들은 거의 변화가
없도록 인덱스 구조를 바꿔서 획기적인 성능향상을 가져왔다.
변경이 자주 일어나는 컬럼이라면 이런 현상이 자주 발생하게 될 것이고 페이지 분할은 더 자주 발생이 된다.
이것은 데이터베이스의 성능을 악화시킨다. 그래서 페이지 분할을 애초부터막기 위해 Fillfactor(채우기비율)
라는 인덱스 옵션이 존재하는 것이다.
이 옵션이 뜻하는 것은 8060 바이트의 페이지 공간 중 얼만큼까지 데이터를 채울 것인가 이다.
이것은 나중에 페이지 중간에 데이터가 입력될 경우를 대비한 것이다.
만약 인덱스의 Fillfactor 값을 80%로 주었다고 한다면, 해당 인덱스페이지는 8060 바이트의 80%인
약 6400 바이트정도만 데이터로 채운다. 그리고, Update 작업이나 Insert 작업으로 인해 페이지에
새로운 데이터가 입력되면, 빈 공간을 이용해서 데이터를 할당하는 것이다.
그림 PageSplit_저장구조.bmp, PageSplit_저장구조_after.bmp 의 내용은 Fillfactor 값이 100%일때의 상황이다.
그리고 이것이 기본설정값이다. 만약 Fillfactor 값이 80%인 경우로 바꾸어서 본다면, 다음과 같이 될 것이다.
참조) 그림.. Fillfactor.bmp
처음부터 데이터페이지에 80%만 데이터가 채워지게 된다. 그래서 한 페이지 당 4개의 행씩만 할당되었다.
이 때 페이지 수가 늘어나게 되는 것은 Fillfactor 옵션을 줄 때의 부정적인 면이다.
그래서 Fillfactor 값을 너무 낮게 주면 페이지의 수가 많아져서, 결과적으로 항상 많은 수의 페이지 I/O를
일으키게 되므로 반드시 주의해야 하는 사항이다. 어쨌든, 처음부터 4개의 페이지에 할당되어 들어가고,
나머지 공간 20%정도는 여유공간이 된다. 이때, 새로운 데이터 “김대중”을 입력한다면
참조) 그림.. Fillfactor_after.bmp
데이터가 입력이 되어서 2306 페이지의 여유공간에 새 행을 할당했고, 페이지의 행 수는 4개에서
5개로 늘어났다. 하지만, 페이지 분할 현상이 일어날 필요가 없으므로 전체 페이지 수는
그대로 4개이고, 상위레벨의 데이터도 마찬가지로 아무런 변동이 없다.즉 그만큼 부하가 덜 걸리는 것이다.
이런 이유로 Fillfactor 옵션을 사용한다.
그러나, 이 옵션을 사용할 때 항상 조심해야 할 것은 Fillfactor 값이 낮으면 낮을수록 인덱스 페이지가
더 많이 소모되므로 I/O를 많이 일으키게 되므로, 적절한 값을 설정해야 한다는 것이다.
더구나 위의 예제처럼 클러스터드 인덱스에 Fillfactor 옵션을 줄 때는 더더욱 그러하다.
클러스터드 인덱스에서, 인덱스페이지의 리프레벨 자체가 데이터페이지이기 때문이다.
만약 Fillfactor 옵션 값을 50%로 준다면 읽기시간은 2배로 늘어날 것이다. 어떤 테이블에
클러스터드 인덱스가 없다면, Fillfactor 옵션은 데이터페이지와는 상관없다. Fillfactor 옵션은
인덱스에만 적용된다.
-- 기존에 존재하는 인덱스에 Fillfactor 옵션을 설정할 때
exec Sp_helpindex idx_03
/*
위 명령으로 확인결과, 필자의 예제에서는 idx_idx_03_iname_C
라는 이름의 인덱스가, iname 컬럼에 적용되어 있다.
여기에 Fillfactor 를 설정해 본다.
*/
Create Clustered index idx_idx_03_iname_C
On idx_03(iname)
With Drop_existing, -- 인덱스 변경을 위해 반드시 필요하다.
Fillfactor = 80 -- 채우기비율을 80%로...
Go
--select * from idx_03
그러면, 인덱스가 재작성 되면서 페이지가 분할되고 성능에도 영향을 미친다.
인덱스 생성작업은 작업(트랜잭션)이 일어나지 않는 시간대에 실행하는 것이 좋다.
위의 명령을 살펴보면, 이미 있는 인덱스임에도 불구하고 “Create .." 문을 사용하는 것을 볼 수 있다.
원래 DB 나 TABLE 같은 개체의 구조변경은 ”ALTER..." 문으로 하는 것이 정상이다.
그러나 인덱스에 대한 변경은 예외이다.
존재하는 개체의 이름과 똑같이 해서 “WITH Drop_Existing" 문을 사용해서 변경을 해야 한다.
다만 이 문장을 사용해도 클러스터드 인덱스를 넌클러스터드 인덱스로바꿀 수 없다.
(넌클러스터드는 클러스터드로 바꿀수 있다.
위의 쿼리는 기존의 있는 인덱스에 채우기 비율을 설정할 때의 예제이다.
그러나, 채우기비율은 인덱스를새로 생성할 때 주는 것이 정석이다.
다음과 같이 하는 것이 정석이라는 이야기다. 처음에 fillfator비율을 할당하는 것이 좋다.
Create Table idx_04
( iid int primary key, iname varchar(20))
GO
Create index idx_04_iname_NC
On idx_04(iname)
With Fillfactor = 80
GO
생성후의 확인은 다음과 같이 하면 된다.
EM/idx_04테이블에서 오른쪽버튼/테이블 디자인/도구 중 저장버튼 오른쪽에 있는,
테이블 및 인덱스 속성단추 클릭/새로 열린 창의 인덱스&키 탭 클릭/“선택한 인덱스“
콤보박스를 눌러서, 위 쿼리에서 생성한 인덱스 이름 선택
글 상자의 내용대로 차례차례 클릭해서 실행하면, 해당 인덱스 idx_04_iname_NC 의
채우기비율이 80%로 되어 있는 것을 확인할 수 있다.
SQL Server 2000에서 Fill factor의 기본값은 0이며 설정할 수 있는 값의 범위는 0부터 100까지이다.
Fill factor 값이 0 이어도 실제 페이지의 채우기 정도가 0%라는 의미는 아니다. 사실 0 값을 주면
------------------
100% 로 처리된다. 그러나 B-Tree 인덱스의 상위 레벨에 일부 공간을 남겨둔다는 점에서 100% 로
------------------
설정하는 것과는 다르다.
인덱스 수준에서가 아니라, 서버 레벨에서도 Fillfactor 옵션을 적용할 수 있다.
그렇게 되면 서버 내의 모든 데이터베이스에서, 모든 인덱스는 동일한 Fillfactor 값을 적용받게 된다.
이렇게 하려면 SQL 문장으로 SP_Configure 옵션을 사용하거나, EM 의 서버설정에서 변경하는
방법 둘 중 하나를 써야 한다. Fillfactor 옵션 뿐 아니라 서버 레벨 옵션의 변경은, 이 두 가지 중
하나를 선택해서 변경한다.
-- 현재 설정보기, config_value 컬럼이나 run_value 컬럼값이 현재 설정값이다.
-- 현재는 0 이다.
exec Sp_Configure 'Fill Factor'
go
-- 80% 로 설정값을 변경한다.
exec SP_Configure 'Fill Factor', 80
go
-- 변경된 것을 확인해보자.
exec Sp_Configure 'Fill Factor'
go
-- config_value 컬럼만 80으로 변경되었다.
-- 그러나, 이 옵션은 서버를 재시작해야만 적용된다.
EM 에서의 설정
서버이름에서 오른쪽버튼, 등록정보 클릭/데이터베이스설정 탭 클릭/상단의 설정 프레임에
“고정“체크박스가 확인된 것을 확인하고, 마우스로 드랙%드롭해서 값을 변경한다.
2) PAD_INDEX
인덱스를 생성할 때 Fillfactor 값을 설정한다고 해서, 인덱스 트리의 모든 레벨이 전부 80% 값을
적용받는 것은 아니다. 다만, 제일 하위레벨인 리프레벨에만 적용될 뿐이다. 그렇게 하는 것이 좋다.
페이지 분할 이 일어난다고 해도 상위레벨의 인덱스 페이지들까지 페이지 분할이 일어나는 일은
별로 없기 때문이다. 클러스터드 인덱스나 넌클러스터드 인덱스 모두 이것은 똑같다. 대량의
데이터변경이 일어날 때만 상위레벨에도 영향을 미치게 된다. 이럴 때 사용되도록 Pad_Index 라는
옵션이 존재한다.
이 옵션은 리프레벨 뿐 아니라 상위의 넌리프레벨까지 Fillfactor 값이 적용되도록 해 주는 역할을
하는 옵션이다. 모든 레벨에서 Fillfactor 값은 똑같이 적용된다.
Create Table idx_05
( iid int primary key, iname varchar(20))
GO
Create index idx_05_iname_NC
On idx_05(iname)
With Fillfactor = 80, PAD_INDEX
GO
3) IGNORE_DUP_KEY
이 옵션은 UNIQUE 인덱스가 있는 컬럼에 중복된 키 값을 입력했을 때, 트랜잭션을
어떻게 처리할 것인가에 대한 옵션이다.
-- 1) 테이블 생성
Create table ign_01 ( iid int , iname varchar(20))
go
-- 2) iid 컬럼에 UNIQUE INDEX 생성
Create unique index idx_ign_01_iid On ign_01(iid)
go
insert ign_01(iid, iname) values(1, 'aaa')
go
-- 3) iid 에 중복된 값을 입력한다. 에러를 내고 트랜잭션을 롤백한다.
insert ign_01(iid, iname) values(1, 'bbb')
go
-----------------------------------------------------------------------------------------
Drop Table ign_01 -- 1-1) 테이블을 삭제하고, 1)번 명령을 다시 실행한다.
go
-- 2-1) IGNORE_DUP_KEY 옵션을 주어서 다시 인덱스 생성
Create unique index idx_ign_01_iid On ign_01(iid)
with IGNORE_DUP_KEY
go
insert ign_01(iid, iname) values(1, 'aaa')
go
-- 3) iid 에 중복된 값을 입력한다. 경고를 발생시키지만, 트랜잭션이 롤백되지는 않는다. (=커밋된다)
* 수행된다 그러나 트랜잭션입장에서는 차이가 있다는 뜻이다.?
insert ign_01(iid, iname) values(1, 'bbb')
go
SELECT * FROM IGN_01 -- 옵션에 관계없이 데이터는 입력되지 않는다.
4) 내림차순 인덱스
SQL Server 2000 에서는 이전버전과 달리, 내림차순 인덱스도 지원한다. 이것은 높은 값의
데이터를 질의할 때, 넌리프레벨에서의 인덱스 페이지 I/O를 감소시키므로 약간의
성능향상을 가져올 수 있다.
CREATE TABLE IDX_05
( IID INT, INAME VARCHAR(20), IDATE DATETIME)
GO
CREATE INDEX IDX_05_IDATE
ON IDX_05(IDATE DESC)
GO
/*
이렇게 IDATE 컬럼에 내림차순 인덱스를 주면,
최근날짜로 자주 검색하는 쿼리에서 페이지 I/O를 줄이는
효과를 볼 수 있다.
*/
--drop table idx_05
IDX_05 라는 테이블을 생성하고, IDATE 컬럼에 내림차순 인덱스를 생성했다.
그러므로 현재날짜나 최근날짜를 조건으로 검색하는 쿼리에 사용하면 좋다.
또한, 특히 SQL Server 2000 에서는 뷰에도 인덱스를 줄 수가 있다. 원래 뷰는
가상의 테이블이며 뷰에는 데이터가 존재하지 않는다. 뷰는 그 정의만 SQL Server 에 존재할 뿐이다.
그러나, SQL Server 2000 에서는 뷰에 클러스터드 인덱스를 줌으로써 뷰를 실제로 존재하게 만들 수 있다.
그렇게 되면 수많은 데이터 중에서 원하는 데이터만을 뷰로 만들어 놓고 인덱스를 주어,
그 뷰를 검색하면 상당히 나은 쿼리성능 향상을 볼 수 있다.
그러나 한편, 데이터변경(Insert,Update,Delete) 시에는 뷰까지 변경해야 하므로 오히려
역효과를 볼 수 도있다. 그러므로, 뷰에 인덱스 생성하기는 조심스럽게 실제 성능테스트를 해서
설정되어야 할 것이다.
3. 인덱스 관리
만들어진 인덱스는 계속적으로 관리를 하고, 상황에 따라 삭제 및 재생성 해야 할 수 도 있다.
생성 및 삭제에 관해서는 이미 알아보았으므로, 다른 사항에 대해서도 알아보자.
1) 조각 모음
데이터베이스를 사용하다 보면 항상 데이터는 조각이 나게 되어 있다. 데이터베이스 수준에서
조각모음을 하려면, DBCC SHRINKDATABASE ..명령을 사용하면 되고, 인덱스 수준에서
-------------------------
(분할 안나타난다)
조각모음을 하려면, 다음 두 가지 방법 중 하나를 사용하면 된다.
- DROP_EXISTING 옵션을 사용해서 인덱스 재생성하기
- DBCC INDEXDEFRAG 명령을 실행하기
DBCC INDEXDEFRAG 옵션을 사용하면, 인덱스의 논리적인 정렬순서에 맞게 인덱스 페이지들을 다시
정렬해 준다. 이 작업은 테이블이나 인덱스를 잠그지 않고, 트랜잭션에 영향을 미치지 않으므로
온라인 상에서도 얼마든지 할 수 있다. 그러나, 이 작업은 디스크에서의 물리적인 순서를 바꾸지
않기 때문에 물리적인 I/O 성능은 향상시키지 못한다. 차라리 DROP_EXISTING 옵션을 사용하거나,
인덱스를 삭제하고 다시 생성하는 방법을 더 권장한다. 이것은 실제로 디스크상의 조각난
인덱스페이지들을 조각모음해서 재정렬하는 방법을 사용하기 때문이다. (성능적으로 더 유리하다.)
만약 클러스터드 인덱스를 재작성하려면, 해당 인덱스공간의 1.2배 정도 되는 공간이 사용되어야
하므로 미리 이 공간이 확보되어 있어야 한다. 이렇게 조각모음을 하면 스캔성능이 향상된다.
시스템 테이블 ( = 마스터테이블)에 대한 인덱스는 다시 작성될 수 없다.
2) COVERED INDEX
SQL Server 에서는 두 개 이상의 컬럼에 하나의 인덱스를 줄 수 있는데, 이것을 복합인덱스라고 한다.
이 복합인덱스는 시스템에 무리를 주기 때문에 잘 사용되지 않는다. 그러나 사용할 필요가
생길 때가 있는 데, 다음과 같은 경우에 그렇다. 먼저 다음 쿼리를 실행해 보라.
USE NORTHWIND
SELECT * FROM EMPLOYEES
실행해보자. 9개의 데이터가 출력이 된다. 컬럼은 18 개가 있다. 9개가 아니라 많은 데이터가 있을수록
이 내용은 더 유효하게 된다. LASTNAME 컬럼에 클러스터드 인덱스가 있다고 생각해 본다.
여기에 Lastname 컬럼과 Title 컬럼에 복합인덱스가 하나 있다고 가정하겠다.
그러면, 복합인덱스를 생성하기 전과 생성한 후, 쿼리의 유형에 따라 쿼리계획이 어떻게 바뀌는가
인덱스를 어떤 식으로 사용하는가 비용이 어떻게 변하는가에 대해서 예를 통해서 알아보자.
USE NORTHWIND
GO
SELECT * FROM EMPLOYEES -- 9 개의 데이터가 있다.
-- 1) 두 컬럼을 조건으로, 두 컬럼을 불러내는 쿼리를 한다.
SELECT lastname, title FROM EMPLOYEES
where lastname = 'buchanan' and title like 's%'
-- 2) Lastname,Title 컬럼에 복합인덱스를 생성한다.
CREATE INDEX IDX_EMPLOYEES_LASTNAME_TITLE_NC
ON EMPLOYEES(LASTNAME, TITLE ASC)
GO
-- 3) 인덱스 생성 뒤, 두 컬럼을 조건으로, 전체 데이터를 불러내는 쿼리를 한다.
SELECT * FROM EMPLOYEES
where lastname = 'buchanan' and title like 's%'
-- 4) 인덱스 생성 뒤, 두 컬럼을 조건으로, 두 컬럼을 불러내는 쿼리를 한다.
SELECT lastname, title FROM EMPLOYEES
where lastname = 'buchanan' and title like 's%'
-- 5) 인덱스 생성 뒤, Lastname 컬럼을 조건으로, 두 컬럼을 불러내는 쿼리를 한다.
SELECT lastname, title FROM EMPLOYEES
where lastname = 'buchanan'
-- 6) 인덱스 생성 뒤, Title 컬럼을 조건으로, 두 컬럼을 불러내는 쿼리를 한다.
SELECT lastname, title FROM EMPLOYEES
where title like 's%'
--Drop index employees.IDX_EMPLOYEES_LASTNAME_TITLE_NC
--sp_helpindex employees
쿼리계획을 보는 것이 목적이므로, 실행할 때에는 실행단추를 누르지 말고
“예상실행계획표시”단추를 반드시 눌러서 계획을 확인한다. 현재는 lastname 컬럼에만
넌클러스터드 인덱스가 있다는 사실을 잊지 말자.
1) lastname 컬럼과 title 컬럼을 동시에 조건으로, lastname,title 컬럼을 조회하고 있다.
현재 lastname 컬럼에만 인덱스가 있다. 이 명령의 쿼리계획을 보면 lastname 컬럼의 인덱스를
사용하지만 title 컬럼은 인덱스가 없어서 사용치 못한다. 그래서 예상 비용이 약 반반씩으로
나누어져 있다. 합해 보면, 약 0.0126 이라는 예상 비용이다.
2) 그래서, lastname,title 컬럼에 복합 넌클러스터드 인덱스를 설정한다.
3) lastname 컬럼과 title 컬럼을 동시에 조건으로 데이터를 검색하되, lastname,title 컬럼 뿐 아니라,
전체 컬럼을 모두 을 조회하고 있다. 실행계획은 예상 비용은 1)의 예제와 같다.
4) lastname 컬럼과 title 컬럼을 동시에 조건으로 데이터를 검색하되, lastname,title 컬럼
만을 조회하고 있다. 이 때 우리가 만든 복합인덱스를 사용하면 예상 비용은 인덱스하나만을
사용한 비용인 0.006408을 나타낸다. 그러므로 lastname 컬럼에 있던 인덱스 하나만을
사용할 때의 절반정도 밖에 되지 않는다. I/O 가 줄어들 것이며 실행시간은 그만큼 빨라질 것이다.
5) lastname 컬럼만을 조건으로 데이터를 검색하되, lastname, title 컬럼을 조회한다.
예상비용은 1)의 예제와 같다.
6) title 컬럼만을 조건으로 데이터를 검색하되, lastname, title 컬럼을 조회한다.
예상비용은 지금까지의 5개의 쿼리 중 제일 많다.
그 이유는 인덱스를 사용하지 않기 때문이다.
“index seek"는 인덱스를 사용한다는 얘기지만, 6)번의 예제는 ”index scan" 이라고 예상쿼리계획을 보여준다. 이것은 인덱스를 사용하지 않는 다는 얘기이다.
그러므로, 전체 테이블 스캔할 때와 같을 비용인 0.037662 이다.
만약 title 컬럼에 인덱스를 설정한다면 1)번과 같은 비용을 나타낼 것이다.
이것은 테이블 스캔시보다 약 1/3정도 줄어든 효과를 보인다.
4. STATISTICS 관리
통계정보는 SQL Server 2000에서 자동으로 관리하는, 데이터 및 인덱스 페이지에 대한 구성정보이다.
실제로 데이터 및 인덱스가 어떻게 분포되어 있는지 분포도와 밀도, 선택도 정보를 갖고 있다.
SQL Server 는 이 정보와 사용자의 쿼리유형을 이용해서 쿼리계획(Query Plan =예상실행계획)을 생성하게 된다.
즉 어떤 데이터가 얼마만큼 어떻게 저장되어 있는 지를 파악해서, 사용자의 쿼리유형을 분석한 다음,
어떻게 해야 가장 빠른 방법으로 쿼리를 실행할지를 판단한다는 것이다. 이 판단의 결과가 쿼리계획이고,
쿼리는 이 계획에 따라 실행된다. 계획은 첨부터 하나를 선택해서 바로 실행할 수도 있고,
몇 개의 계획을 후보로 만들어서 그 중 가장 나은 것을 선택하는 방식으로 실행되기도 한다.
통계정보에 대한 데이터베이스의 기본설정은 통계정보자동작성,자동갱신이다.
인덱스가 만들어지거나, Where 절에서 자주 조회되는 컬럼에 대해서 SQL Server 는 통계를 만들고
주기적으로 갱신한다. 뿐만아니라 데이터의 대량 변경시에도 이를 감지하고 자동으로 갱신해준다.
그러므로 사용자나 관리자는 통계정보를 수동으로 관리해 줄 필요가 거의 없다.
그러나, 개념을 알면 다른 내용을 이해하는 데 많은 도움이 되므로 알고 넘어갈 필요가 있다.
먼저 자동옵션에 대한 설정사항을 확인해 보자.
EM / 해당 DB 속성 / 옵션 / 통계자동 업데이트
CREATE TABLE STAT_01
(SID CHAR(900) PRIMARY KEY, SNAME CHAR(900))
GO
CREATE INDEX IDX_STAT_01_SNAME
ON STAT_01(SNAME)
GO
CREATE STATISTICS STAT_SNAME (통계자료이름)
ON STAT_01(SNAME)
WITH SAMPLE 50 PERCENT
-- WITH FULLSCAN
GO
--DROP TABLE STAT_01
이미 SID 컬럼은 기본키여서(클러스드) 인덱스가 있으므로 통계정보가 생성되었을 것이고,
SNAME 컬럼에 인덱스를 주고 50% 만 샘플링해서 통계정보를 생성해 본 예제이다.
중복된 데이터가 많으면 인덱스를 사용하는 것이 오히려 좋지않다.
여러 단계를 거치게 되므로 더 많은 I/O를 일으킬 가능성이 많다.
그러므로, 인덱스가 있어도 얼마나 중복이 되어 있느냐에 따라서 인덱스는 사용될 수 도 있고
사용되지 않을 수도 있다. 이 모든 것을 SQL Server 가 알아서 판단하게 되는 데,
중요한 것은 얼마나 중복되어 있는 지를 알아야 한다는 것이다. 그래서 통계정보가 중요하다.
얼마나 어떻게 중복되어 있는지에 대한 정보를 저장하고 있는 것이 바로 통계정보이기 때문이다.
넌클러스터드 인덱스에서는 데이터중복도가 3%을 넘으면 쿼리실행시 인덱스를 사용하지 않는다.
3%미만이라면 인덱스를 사용한다. 물론 사용자의 쿼리유형도 상당히 중요한 역할을 한다
(쿼리유형에 대해서는 이 책의 다른 부분에서 다루고 있다).
그러나 통계정보에 따라서 인덱스를 사용할지 하지 않을 지에 대해, 쿼리계획이 변경되는 것이다.
인덱스를 쓰지 말아야 할 쿼리에 인덱스를 사용하는 쿼리계획을 생성한다면,
그것은 그냥 테이블스캔을 하는 것보다 몇배 몇십배 I/O와 쿼리비용이 증가될수 있다.
그러므로 인덱스선택은 매우 신중히 고려되어야 하고, SQL Server 는 이것을 알아서 해준다.
사용자가 인덱스선택을 할 경우는 거의 없다. 기존에 있는 테이블을 예제로 통계정보를 한번 보자.
USE pubs
-- 통계보기, 매개변수는 테이블명과 인덱스 이름이다.
DBCC SHOW_STATISTICS (authors, UPKCL_auidind)
GO
PUBS 데이터베이스에 있는 AUTHORS 테이블의 인덱스에 대한 통계정보를 보여주는 예제이다.
이 인덱스는 AU_ID 컬럼에 주어진 인덱스이다.
마지막으로 업데이트된 시간과 전체행(23개), 그리고 샘플된 ROW(23개), 또 밀도 등이 나타나 있다.
-- ID 컬럼에 UNIQUE 인덱스 생성해서 테이블 만들기
CREATE TABLE IDX_TUNE1
(ID INT UNIQUE, NAME CHAR(12), SAL INT)
GO
-- NAME 컬럼에 클러스터드 인덱스 생성
CREATE CLUSTERED INDEX IDX_TUNE1_NAME
ON IDX_TUNE1(NAME)
GO
-- 8000 건의 데이터 입력
DECLARE @NO INT
SET @NO = 0
WHILE (@NO < 20000)
BEGIN
BEGIN TRAN
INSERT INTO IDX_TUNE1 VALUES(@NO * 4 + 0, 'KIM', 700)
INSERT INTO IDX_TUNE1 VALUES(@NO * 4 + 1, 'KANG', 700)
INSERT INTO IDX_TUNE1 VALUES(@NO * 4 + 2, 'SEO', 700)
INSERT INTO IDX_TUNE1 VALUES(@NO * 4 + 3, 'PARK', 700)
SET @NO = @NO + 1
COMMIT TRAN
END
GO
SELECT COUNT(*) FROM IDX_TUNE1
실행하면 통계정보는 자동으로 생성되고 수정이 될 것이므로 우리가 신경쓰지 않아도 된다.
그러나, 혹시나 하는 마음에 통계정보를 수동으로 업데이트하고, 확인해보면, 다음과 같다.
-- 인덱스(IDX_TUNE1_NAME) 에 대한 통계업데이트
UPDATE STATISTICS IDX_TUNE1(IDX_TUNE1_NAME)
WITH SAMPLE 50 PERCENT
GO
DBCC SHOW_STATISTICS ( IDX_TUNE1, IDX_TUNE1_NAME )
/*
EXEC SP_HELPINDEX IDX_TUNE1
-- ID 컬럼과 NAME 컬럼에 각각,
-- UQ__IDX_TUNE1__39237A9A,IDX_TUNE1_NAME 이란 이름의 인덱스존재.
SELECT * FROM SYSINDEXES
WHERE ID = OBJECT_ID('IDX_TUNE1')
*/
결과창의 “RANGE_HI_KEY" 값이 바로 인덱스를 생성한 컬럼의 값들이다. 모든 값의 종류가 전부
출력된다. 현재 여기에는 4개의 값밖에 없기 때문에 그것만 출력이 된 것이다. 그리고 ”EQ_ROWS"
----
=equal(같다)
컬럼값이 현재 해당 데이터가 얼마나 중복이 되어 있는가에 대한 정보이다.
SQL 문에서, 각각 2000건씩 입력했으니, 입력한 그대로 2000 씩 보여주고 있다.
통계정보는 이렇게 해서 확인하면 되겠다. 통계정보를 수동으로 업데이트하는 명령은
“UPDATE STATISTICS..."외에도 SP_UPDATESTATS 라는 명령이 하나 더 있다.
먼저 UPDATE STATISTICS 명령은 특정 테이블의 특정 인덱스에 걸린 통계정보만 갱신하는 반면,
SP_UPDATESTATS 명령은 데이터베이스의 모든 테이블에 대해 한꺼번에 통계정보를 갱신하는
막강한 힘을 가지고 있다.
-- 모든 통계정보를 갱신한다.
USE DBSYS
EXEC SP_UPDATESTATS
