Database/Theory

[Database/Theory] 13. index(3) - 생성 시기

양승길 2016. 6. 19. 19:59

13. index(3) - 생성 시기

index는 열단위로 생성된다. 또한 2개 이상의 열을 조합하고 생성할 수 있다.


 - where절에서 사용되는 열을 index로 만든다.

그 만큼 자주 사용할 가치가 있는 index로 만든다. 

select문보다 insert문이 자주 사용되면 그만큼 페이지 분할이 될 것이고 시스템의 성능이

저하될 것이다.


 - 중복이 많은 열은 index의 가치가 없다.

non-clustered index일지라도, 중복률이 테이블의 1% ~ 3% 이상이면 사용되지 않는다.


 - 외래키나 Join에 사용되는 열을 사용하는 것이 좋다.

그만큼 설계해야 될 중요성이 크다. 제약조건에 의하여 생성된 index는 삭제가 불가능하고

대량의 데이터의 변동이 발생된다.


 - insert, update, delete의 빈도수를 고려한다.

index는 읽는 것에 대하여 DB의 성능을 높히지만 변경작업은 그 반대의 영향을 끼친다.


- clustered index는 하나만 생성할 수 있다.

clustered index는 데이터 페이지를 최소로 읽기 때문에, 

조건절에서 가장 많이 사용되는 열에 생성하는 것이 바람직하다.

또한 조건절에서 Between과 같은 범위나 집계함수를 사용하는 경우가 좋다.


order by절에 자주 사용되는 열을 clustered index로 사용되는 것이 좋다.

clustered index의 leaf는 이미 정렬되어 있기 때문이다.


clustered index는 테이블에 하나의 열에서만 생성이 가능한데,

만약에 또 하나의 열을 범위로 조회 한다면 

포괄열이 있는 인덱스(index with included columns)를 생성하도록 한다.


 - clustered index가 아예 없는 경우도 좋다.

대용량의 데이터들이 무작위로 입력되다 보면 페이지 분할이 자주 발생될 수 있다.

차라리 non-clustered index로 지정해도 나쁘지 않다.


 - 사용하지 않는 index는 제거한다.

where 조건에서 사용되지 않는 열의 index는 제거할 필요가 있다.

DB공간의 확보 뿐만 아니라 데이터 삽입에 발생하는 부하도 많이 줄일수 있다.


 - 계산 열에서도 index를 활용할 수 있다.

예시를 들어본다.

1
2
3
4
5
6
7
8
9
10
11
create table tbl (
    int1 int,
    int2 int,
    sum  as int1 + int2 persisted
);
 
insert into tbl values(100100);
insert into tbl values(200200);
insert into tbl values(300300);
 
select * from tbl;
cs

int1    int2     sum

-----------------

100    100     200

200    200    400

300    300    300


sum이라는 열을 index를 사용하려면 아래와 같은 구문을 사용하면 된다.

1
create clustered index idx_tbl_sum on tbl(sum);
cs


- 포괄열이 있는 non-clustered index를 활용하면 

   쿼리 성능이 높아진다.

포괄열이 있는 인덱스(index with included column)는 non-clustered index의

leaf page에 데이터를 포함하고 있는 열을 의미한다. 


[Database/Theory] 12. index(2) - 내부적 동작에 의하면,

clustered index의 속도는 non-clustered index보다 빠르다.

추가로 where절에 범위를 입력할 경우, clustered index는 그 기준을 찾아서 탐색하지만

non-clustered index는 전체 테이블을 검색하게 된다. clustered index는 leaf page가

곧 데이터이고 정렬되어 있기 때문이다. 


그러나 non-clustered index는 leaf page를 거치고 다시 데이터 페이지에서 검색한다.

만약에 leaf page와 데이터가 공존한다면 굳이 데이터 페이지를 찾지 않아도

non-clusered index에서 모든 작업이 종료되므로 clustered index와 같은 효과가 나타난다.


예시를 위해 기존에 작성한 테이블을 다르게 작성해 보겠다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
create table users(
   number int,  
   id     varchar,
   name   varchar,
   phone  varchar
);
 
insert into users values(9'KYY''김윤영''011');
insert into users values(6'KEH''김은혜''016');
insert into users values(3'HKM''홍경모''011');
insert into users values(11'YHH''유형환''017');
insert into users values(12'YMH''유민호''016');
insert into users values(10'LTG''이태검''017');
insert into users values(2'BSH''백승현''011');
insert into users values(8'KKC''김경철''018');
insert into users values(4'JHJ''장희정''016');
insert into users values(5'JHM''정희민''011');
insert into users values(7'KHS''김희선''018');
insert into users values(1'BCE''배찬익''016');
cs


여기서 index with included column을 생성한다.

1
2
3
create      nonclustered index idx_users_include
    on      users(number)
    include (id, name); 
cs


아래와 같은 구조가 될 것이다.



이러한 구조에서 다음과 같은 쿼리를 수행할 경우, root와 leaf page에서

해당된 데이터만 읽게 된다.

1
select number, id, name from users where number < 10;
cs

즉 leaf page에 100번과 101번만 읽게 되면 select문에 있는 number, id, name이

모두 있게되므로 clustered index와 같은 효과를 낼 수 있다.


그러나 다음과 같은 쿼리를 수행할 경우 모든 data page까지 읽게되는

table scan을 하게 된다.

1
select number, id, name, phone from users where number < 10;
cs

이유인 즉슨 select문에 phone은 leaf page에 phone이 없기 때문에,

RID를 참조하여 data page에서 탐색할 수밖에 없다.


- 결론

포괄열이 있는 index는 non-clustered index에만 생성할 수 있다. 

clustered index에는 생성할 수 없을 뿐만 아니라 생성해도

clustered index에는 leaf page 자체가 data이기 때문에 생성의 의미가 없다.


포괄열이 있는 index는 index의 크기가 커지는 단점이 있다.


포괄열이 있는 index를 생성하면 쿼리의 성능이 급격히 향상된다.


포괄열이 있는 index가 있어도 select의 열이 그 포괄열에 포함되지 않으면

index는 사용되지 않는다.