MSSQL 색인

색인

 

 

왜 색인을 사용하는 것인가?

 

  - 색인은 데이터를 찾을(SELECT) 때 빨리 찾기 위해서 사용한다.

    색인이 없다면 특정한 값을 찾기 위해 모든 데이터 페이지를 다 뒤져야 한다.

    색인이 찾고자 하는 컬럼이나 표현식에 존재하고, 색인을 사용하는 것이 더 효과적이라면,

    SQL 서버는 모든 페이지를 뒤지지 않고 색인 페이지를 찾아서 쉽게 데이터를 가져온다.

    이것을 INDEX SEEK 라고 한다.

  - 색인이 있으면 특정한 작업을 빨리 할 수 있다. (ORDER BY, GROUP BY)

    뿐만 아니라 색인을 사용하면 유일성 검사를 할 수 있다.

 

 

왜 모든 컬럼에 색인을 사용하지 않는 것인가?

 

  - 이렇게 좋은 색인이라면 모든 컬럼에 사용해야 하지 않을까? 색인으로 인한 손해가 있다.

  - 만드는데 시간이 걸린다.

  - 만드는데 많은 공간이 필요하고, 만들고 난 후에도 추가적인 공간이 필요하다.

  - 데이터를 수정(INSERT, UPDATE, DELETE)하는 시간, 특히 INSERT 작업은 오히려 더 많이 걸린다.

 

 

그럼 어떤 컬럼에 색인을 쓸까?

 

  - WHERE 절에서 자주 사용되는 컬럼

  - 자주 검색되는 컬럼(SELECT 절에서)

  - 기본 키 (자동적으로 색인이 만들어진다.)

  - 참조 키 : 조인 사용할 때 조인의 속도를 향상시킨다. (색인이 자동적으로 만들어 지지 않는다.)

  - 자주 JOIN 으로 사용하는 컬럼 (참조 키가 아닐 때)

  - 정렬된 순서로 자주 사용되는 컬럼 : 가능하다면 클러스터 색인을 만드는 것이 좋다.

  - 범위를 주고 찾는 컬럼 : 가능하다면 클러스터 색인을 만드는 것이 좋다.

 

 

그럼 어떤 컬럼에 색인을 만들면 안될까?

 

  - 좀처럼 검색되지 않는 컬럼

  - 전체 중 상당 부분을 가져오는 질의에 사용되는 컬럼

  - 유일성 또는 같은 값이 많은 컬럼

  - SELECT 속도보다 데이터의 변경 속도가 훨씬 중요할 때 (되도록 색인의 수를 최소화한다.)

 

 

색인만들기 / 지우기

 

  - CREATE INDEX 에 아무 옵션도 지정하지 않으면 UNIQUE 하지 않고(non-unique), NONCLUSTERED 색인이 만들어진다.

  - 컬럼에 DESC 순으로 색인을 만들 수도 있따.

  - text, ntext, image 자료형의 컬럼에는 색인을 만들 수 없다.

  - 뷰에도 색인을 만들 수 있다.

  - WITH DROP_EXISTING 을 사용하면 색인을 만드는 시간을 단축할 수 있다.

예)

CREATE INDEX myTable_PK ON myTable(부서 DESC, 사번 ASC)

--> 색인을 부서 컬럼에 내림차순으로, 사번 컬럼에 오름차순으로 만든다.

 

예)

DROP INDEX myTable.idx1

--> 색인을 변경하는 ALTER INDEX ... 문은 존재하지 않는다. 지우고 다시 만들어야 한다.

 

 

색인의 종류

 

1. 클러스터 색인 / 넌 클러스터 색인

  

  ○ 클러스터 색인

     - 물리적인 행(데이터)의 순서가 색인의 순서와 동일하다

     - 색인의 맨 마지막 단계인 리프 레벨(leaf level)이 곧 데이터 페이지이다.

     - 책의 차례(목차)로 생각하면 이해가 빠르다.

     - 일정한 범위를 추고 찾는 경우 속도 향상에 매우 도움이 된다.

     - 넌 클러스터 색인에 비해 색인의 단계가 한 단계 적기 때문에 색인의 크기도 작고, 그만큼 빨리 찾을 수 있다.

     - 반면, 새로운 데이터가 입력되면 항상 물리적 순서를 색인의 순서에 맞추어야 하기 때문에 많은 데이터가

       자신의 자리를 찾는 작업을 해야하므로 이 부분에서는 오히려 넌 클러스터 색인보다 느리다.

 

  ○ 넌 클러스터 색인

     - 물리적인 행의 순서가 색인 순서와 동일하지 않다.

     - 리프 레벨은 데이터 페이지가 아니다.

       리프 레벨에서 한 단계 더 내려가야만 데이터 페이지다. 따라서 클러스터 색인보다 한 단계를 더 거쳐야 실제 찾고자 하는

       데이터에 도달할 수 있다.

     - 책의 찾아보기로 생각하면 이해가 쉽다.

     - 일정 양의 데이터를 범위를 주고 찾는다면 오히려 테이블 전체를 뒤지는 것이 더 빠르다.

     - 넌 클러스터 색인은 한 특정 값을 찾거나, 많은 양의 데이터 중에서 작은 범위를 찾을 때만 유용하다.

     - 그러나 데이터를 삽입할 때는 자신의 자리를 찾을 필요 없이 제일 마지막에 들어가면 되므로, 부하가 없다.