인덱스(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로선언된넌클러스터색인이있기때문에클러스터색인을만들면넌클러스터색인은다시만들어진다.그래서항상클러스터색인을먼저만들고그뒤에넌클러스터색인을만드는것이좋다.
http://woodair.blog.me/100019575158
