인덱스(MSSQL)
1.색인의 기본 개념
SQL서버에서 기본값으로 테이블을 만들고 데이터를 추가,수정하고 필요없는 데이터 삭제해가면서 저장할 때 데이터의 레코드는 내부적으로 아무런 순서 없이 저장된다.이때 데이터 저장영역을 Heap이라고 한다. Heap에서는 인덱스가 없는 테이블의 데이터를 찾을 때 무조건 전체 데이터 페이지의 처음 레코드부터 끝페이지의 마지막 레코드까지 다 읽어서 검색조건과 비교하게 된다. 이런식의 데이터 검색방법을 테이블 스캔(table scan) 또는 풀 스캔(full scan)이라고 한다.이럴 경우 양이 많은 테이블에서 일부분의 데이터만 필요로 할때 전체 영역을 다 읽어서 검색조건과 비교하게 되므로 처리 성능이 떨어진다.즉 색인은 데이터를 select 할 때 빨리 찾기 위해 사용된다.
create table board(
id int not null,
name varchar(20) default ''default name'',
date smalldatetime default getdate()
)
go
insert board values(1,default,default)
select name from board where id=1
select문을 마우스로 드래그하여 선택한 후 메뉴의 쿼리-예상실행계획표시(Ctrl+L)를 택하고 밑에 나오는 그림에 마우스를 갖다대면 풍선도움말이 나온다.여기에 보면 물리적연산,논리적 연산에 Table Scan 이라고 나온다.
2. 인덱스 만들 때 고려할 점
인덱스를 만들면 좋은 컬럼
* where , order by , group by 문 등에서 자주 사용되는 칼럼(인덱스 데
이터는 order by문을 사용하지 않더라도 정렬된 순서로 되어있음)
* 프라이머리키,유니크 constraints 컬럼 (내부적으로 유니크 인덱스 사
용)
* 포린 키 컬럼
인덱스 만들면 나쁜 컬럼
* 쿼리에서 자주 사용하지 않는 컬럼
* 키값이 선별도가 나쁠때 (성별,국적,학력...)
3.인덱스가 있을경우
1)만드는 방법:
CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED]
INDEX index_name ON table (column [,...n])
[WITH
[PAD_INDEX]
[[,] FILLFACTOR = fillfactor]
[[,] IGNORE_DUP_KEY]
[[,] DROP_EXISTING]
[[,] STATISTICS_NORECOMPUTE]
]
[ON filegroup]
2)생성된 인덱스 보기
exec sp_helpindex 테이블이름
3)인덱스 제거
drop index ''table.index''[,...n]
4)클러스터드 인덱스(clustered index)
:책으로 예를 들면 차례에 해당한다.
한테이블에 하나만 있어야 한다.
차례에 나오는 순서와 책의 순서가 일치하듯 데이터가 키값에 따라 정렬되어있다.
일정한 범위를 주고 찾는 경우 속도 향상에 도움이 된다.
create clustered index board_CL on board(id)
go
select name from board where id=1
select문을 마우스로 드래그하여 선택한 후 메뉴의 쿼리-예상실행계획표시(Ctrl+L)를 택하고 밑에 나오는 그림에 마우스를 갖다대면 풍선도움말이 나온다.여기에 보면 물리적연산,논리적 연산에 clustered index seek이라고 나온다.
5)넌클러스터드 인덱스(nonclustered index)
:책으로 예를 들면 찾아보기에 해당한다.
용어찾기,표찾기,그림찾기 처럼 한테이블에 여러개가 있을수 있다.
찾아보기가 책의 순서와 일치하지 않듯이 데이터는 들어가 있는 순서대로 있다.
일정한 범위를 주고 찾는 경우 테이블 관리자가 테이블과 함께 인덱스까지 관리해야 한다.
create table board2(
id int not null,
name varchar(20) default ''default name'',
date smalldatetime default getdate()
)
go
create clustered index board_NC on board2(id)
go
select name from board2 where id=1
select문을 마우스로 드래그하여 선택한 후 메뉴의 쿼리-예상실행계획표시(Ctrl+L)를 택하고 밑에 나오는 그림에 마우스를 갖다대면 풍선도움말이 나온다.여기에 보면 물리적연산,논리적 연산에 index seek이라고 나온다.
5.유일색인과 중복허용
1)primary key constraint
:클러스터 색인,유일색인이 디폴트
create table board3(
id int constraint PK_id primary key not null,
name varchar(20) default ''default name'',
date smalldatetime default getdate()
)
go
2)create index
:넌클러스터 색인,중복허용
create table board4(
id int not null,
name varchar(20) default ''default name'',
date smalldatetime default getdate()
)
go
create index board4_NC on board4(id)
insert board4 values(1,default,default)
insert board4 values(1,default,default)
중복이 허용된다.
클러스터 색인에 유일색인으로 바꾸고 싶으면
delete board4 (테이블의 데이터 지우기)
drop index board4.board4_NC (색인 지우기)
create unique clustered index board4_CL on board4(id)
이제 중복이 허용되지 않고 클러스터드 인덱스로 바뀌었다.
6.테이블에서 중복된 컬럼값 찾기
테이블에 이미 중복된 키값이 있을 경우 유니크 인덱스(유일색인)를 만들 수 없다.그럼 테이블에 어떤값이 얼마나 중복되어 있는지 미리 볼 수 있는 방법을 알아보자.
create table member(
id int not null,
name varchar(20)
)
insert member values(1,''길동'')
insert member values(2,''철수'')
insert member values(2,''영희'')
insert member values(3,''순이'')
insert member values(3,''은정'')
insert member values(4,''성관'')
select * from memeber where id in(select id from member group by id having count(id)>1) order by id
7.복합(composite)인덱스
테이블에서 인덱스의 키값으로 사용되는 컬럼이 두개 이상일때를 말하며 두개 이상의 컬럼이 조건문에서 함께 자주 사용되는 경우 필요하다.주민등록번호 앞의 6다리와 뒤의 7자리로 두개의 컬럼에 저장할 때 유니크 복합인덱스를 사용하는것이 일반적이다.
*최대 16개까지의 컬럼이 하나의 인덱스에 사용될 수 있다.각 컬럼의 합은 전체 900바이트를 초과할 수 없다.
*(column1,column2)의 순서로 만든 인덱스는 (column2,column1)의 순서로 만든 인덱스와 키값 저장구조가 틀리다.중복이 가장 적은 컬럼을 앞에 두는순서로 만들면 인덱스를 더욱 효율적으로 사용하여 검색 속도를 높이게 된다.
*(column1,column2)의 순서로 만든 인덱스가 있을 때 조건문에서 column2만을 사용때는 인덱스를 사용할 수 없다.하지만 column1 또는 column1과 column2을 함께 사용할 때는 인덱스를 사용할 수 있다.
drop table member
create table member(
id int nuo null,
name varchar not null,
jumin1 char(6) null,
jumin2 char(7) null
)
create unique index ix_jumin on member(jumin1,jumin2)
8.covering index
전체 테이블에서 일부 컬럼만을 액세스할 때는 해당 컬럼에 대해서 넌클러스터드 인덱스를 만들어 두면 월등한 처리 성능의 향상을 볼 수 있다.
create table member2(
id1 int not null,
id2 int not null,
name varchar(20),
address char(950)
)
set nocount on
begin tran
declare @i int
set @i=0
while @i<1000
begin
insert member2 values(@i,@i,''A'',''TEST ADDRESS'')
set @i=@i+1
end
commit tran
set nocount off
(set nocount on은 한개행 적요됨 이 나타나지 않도록 하기 위함)
1)일반 index 성능 분석
create index ix_id1_id2 on member2(id1)
set statistics io on
select id2 from member2 where id1=100
set statistics io off
(여기서 statistics io on은 쿼리 성능 분석하는 것으로 입출력 처리 정보를 보여준다.I/O가 많다는 것은 속도가 느려진다는 것을 의미한다.
실행하는데 걸린 시간을 알고 싶으면 set statistics time on/off을 사용한다.)
실행한 후 메시지를 보면 논리적 읽기 수가 3으로 나온다.
set statistics io on
select id1 from member2 where id2=100
set statistics io off
실행한 후 메시지를 보면 논리적 읽시 수가 143이다.
이것이 인덱스를 사용할 때와 사용하지 않았을 때의 상황이다.
2)covering index를 생성했을때
create index ix_id1_id2 on member2(id1,id2) with drop_existing
(여기서 with drop existing 는 테이블에 이미 인덱스가 있을때 같은 이름으로 인덱스를 새로 만들때는 이 옵션을 쓴다.)
set statistics io on
select id2 from member2 where id1=100
set statistics io off
논리적 읽기수 4개로 나옴
set statistics io on
select id1 from member2 where id2=100
set statistics io off
논리적 읽기수 4개로 나옴
3)키값이 아닌 컬럼을 쿼리에서 사용할 때
select * from member2 where id2=100 where id2=100 ---모든 컬럼 선택
논리적 읽기 수 5개
9.Optimizer Hints사용법
Optimizer Hints는 쿼리를 실행할 때 데이터를 어떻게 액세스하는지를 지정하는 것으로 쿼리 옵티마이저가 선택하는 처리순서를 바꾸려고 할 때 사용한다.
1)사용법
select from table_name with (table_nint[,...n])
* index(0) 옵션은 테이블 스캔을 하게 한다.
* 하나의 쿼리에서 여러개의 인덱스를 사용할 수 있다. index(index1,index2)
* optimizer hints는 정적(static)으로 쿼리 실행계획(execution plan)을 지정하며 쿼리 옵티마이저보다 우선한다.그러므로 optimizer hints를 사용할 때는 처리 성능이 좋았더라도 테이블의 데이터나 환경 설정이 변했을 때는 쿼리 처리 성능이 저하될 수 있으므로 주의 해야 한다.
create table member3(
id1 int not null,
id2 int not null,
name varchar(20),
address char(950)
)
set nocount on
begin tran
declare @i int
set @i=0
while @i<1000
begin
insert member3 values(@i,@i%50,''A'',''TEST ADDRESS'')
set @i=@i+1
end
commit tran
set nocount off
create index ix_id1_id2 on member3(id1,id2)
일반 상태
set statistics io on
select * from member3 where id2=5
set statistics io off
논리적 읽기 수 143개
옵티마이저힌트 사용
set statistics io on
select * from member3 with(index(ix_id1_id2)) where id2=5
set statistics io off
논리적 읽기 수 24개
10.clusterd와 nonclustered 색인에 대한 이해
클러스터 색인이 넌클러스터 색인보다 빠르다.범위를 주고 찾는경우에도 클러스터 색인이 훨씬 좋은 성능을 자랑한다.넌클러스터 색인만으로 범위를 주고 찾는 것은 테이블 스캔보다 더 나쁜 성능을 낸다.그렇지만 클러스터색인은 테이블당 하나밖에 존해할 수 없기 때문에 신중히 선택해야 한다.
클러스터 인덱스가 없을때 프라이머리키 constraints를 생성하면 기본적으로 클러스터드 인덱스가 만들어진다.그러나 기본키를 무조건 클러스터 색인으로 설정하는 것은 옳지 않다.일반적으로 정렬이 되어있어야 더 좋은 속도를 낼 수 있는 컬럼을 클러스터 색인으로 만드는 것이 좋다.왜냐하면 클러스터 색인은 데이터가 미리 키값으로 정렬되어있기 때문이다.그래서 프라이머리키를 클러스터 인덱스로 사용하지 않을 경우 생성할 때 명시적으로 Nonclustered 옵션을 지정해줘야 한다.
1)테이블 만들고 클러스터 색인 만들기
create table t_index(
id int identity,
name char(20) default ''default name'',
date smalldatetime default getdate()
)
create clustered index t_index_CL on t_index(id)
go
2)테이블에 10000건의 데이터 입력
set nocount on
declare @i smallint
set @i=0
while @i<10000
begin
set @i=@i+1
insert t_index default values
end
set nocount off
3)제대로 입력되었는지 확인
select max(id) from t_index
4)색인이 언제 사용되는지 확인
select * from t_index where id=300
select문을 마우스로 드래그하여 선택한 후 메뉴의 쿼리-예상실행계획표시(Ctrl+L)를 택하고 밑에 나오는 그림에 마우스를 갖다대면 풍선도움말이 나온다.(아래부터 플랜이라 명한다.)여기에 보면 물리적연산,논리적 연산에 clustered index seek이라고 나온다.즉 색인을 사용하고 있다.
5)범위가 있는 경우
select * from t_index where id between 1 and 10000
플랜을 사용하면 clustered index seek이라고 나온다.즉 색인을 사용하고 있다.
6)클러스터 색인이 있을 경우 테이블 스캔
select * from t_index with(index(0)) where id=300
select * from t_index with(index(0)) where id between 1 and 10000
플랜을 사용하면 clustered index scan이라고 나온다.즉 테이블 스캔을 사용하고 있다.
7)nonclustered 색인 만들기
create nonclustered index t_index_NC on t_index(id)
go
8)질의 사용시 색인 사용하면 I/O얼마나 발생?
set statistics io on
select count(name) from t_index where id=300
set statistics io off
2페이지 발생
set statistics io on
select count(name) from t_index with(index(t_index_NC)) where id=300
set statistics io off
(클러스터드 인덱스가 존재하므로 optimizer hint를 명시하지 않으면 자동적으로 clusterd index가 사용된다.)
4페이지 발생
(그렇다면 순수하게 넌클러스터 색인에서만 발생한 페이지는 2페이지다.왜냐하면 넌클러스터 색인은 클러스터 인덱스에 의 다시 배열되어있으므로 넌클러스터 색인을 찾고 다시 클러스터 색인을 찾기 때문이다.)
9)범위를 주고 찾는 질의 수행할 경우 클러스터와 넌클러스터 색인의 차이
클러스터 색인 사용시
set statistics io on
select count(name) from t_index where id between 1 and 300
set statistics io off
논리적인 읽음 수 3
테이블 스캔 사용
set statistics io on
select count(name) from t_index with(index(0)) where id between 1 and 300
set statistics io off
논리적인 읽음 수 47
넌클러스터 색인 사용시
set statistics io on
select count(name) from t_index with(index(t_index_NC)) where id between 1 and 300
set statistics io off
논리적인 읽음 수 645
이것은 테이블 전체인 46페이지보다 더 많다.(테이블 크기는 exec sp_spaceed t_index를 실행해보면 알수 있다.data 368Kb를 8로 나누면 페이지가 나온다.)따라서 넌클러스터 색인은 범위를 주고 값을 찾을 때는 색인을 사용하지 않는 것보다 불리하다.
10)클러스터 색인만 삭제하면 어떤일이 발생?
drop index t_index.t_index_CL
넌클러스터 색인이 다시 만들어진다.크러스터 색인의 키 값을 가리키다가 행번호를 가리키게 되기 때문이다.
11)스토어드 프로시저의 recompile
프로시저 만들기
create proc porcGetById
@id int
as
select count(date) from t_index where id<@id
I/O양 비교
set statistics io on
select count(date) from t_index where id<2
exec procGetByID 2
둘다 3페이지의 I/O를 보인다.아무 차이 없다.
다음 질의를 이어서 실행해보자
set statistics io on
select count(date) from t_index where id<1000
exec procGetByID 1000
첫번째는 49페이지.두번째는 1003페이지.
첫번째 실행에서 색인을 사용하도록 컴파일 되었기 때문에 두번째도 넌클러스터 색인을 사용하였고 그래서 엄청난 I/O를 보이고 있다.recompile하면 된다.
set statistics io on
exec porcGetByID 1000 with recompile
12)새로운 테이블을 만들고 클러스터 색인과 넌클러스터 색인을 모두 만들자.
create table contig(
id int identity constraint PK_contig Primary Key nonclustered,
name char(20) not null,
date datetime default getdate()
)
set nocount on
declare @i smallint
set @i=0
while @i<2000
begin
set @i=@i+1
insert contig values(''NAME''+convert(char(20),@i),default)
end
select count(*) from contig
go
create clustered index contig_CL on contig(name)
go
이미 PK로 선언된 넌클러스터 색인이 있기 때문에 클러스터 색인을 만들면 넌클러스터 색인은 다시 만들어진다.그래서 항상 클러스터 색인을 먼저 만들고 그 뒤에 넌클러스터 색인을 만드는것이 좋다.
