본문 바로가기

work/mssql

클러스터인덱스와 비클러스터인덱스차이

[인덱스]-색인
책에서 원하는 내용을 빨리 찾으려면 인덱스를 이용(책의 인덱스와 비슷한 개념)
DB도 사용자가 원하는 내용을 빨리 찾으려면 색인이란 정보를 미리 만들어서 원하는 데이터를 빨리
찾을 수 있게 할 수 있다.

DB의 고급 퍼포먼스 성능 튜닝과 관련된 작업

데이터베이스내의 테이블에서 원하는 정보를 좀더 빨리 찾아줄수 있게
데이터의 위치 정보를 모아놓은 데이터베이스내의 객체 object이다
DB의 전반적인 성능의 핵심이 되고 반드시 알아야 할 기술

*인덱스는 정렬되어 있다.
예1) 사진에서 원하는 데이터를 찾을때
예2) 책에서 책뒷부분의 인덱스 페이지


table scan  -- 전부다 검색
index seek  -- 원하는 페이지만 검색
===========================================
*포인트쿼리   point query
 조회되는 데이터가 한두개
 select * from tb_member where uid='kim'    --값이 하나

*범위쿼리   range query
 조회되는 데이터가 다수
 select * from tb_member where regdt='2008/06/19' -- 값이 다수

*커버드쿼리  covered query
 조회의 대상과 조회의 결과가 컬럼이 일치하는 상태
 인덱스 측면에서 제일 빠른 성능을 냄

 select * from tb_member where uid='kim' and upw='1234'--커버드 쿼리 아님 포인트쿼리

 select uid from tb_member
 where uid='kim' and upw='1234' : 커버드 쿼리
 

1. clustered index 클러스터 인덱스
형식) create clustered index 인덱스명
on 테이블명(칼럼명 오름/내림)
해당 컬럼을 기준으로 정렬, 테이블당 1개씩만 허용.
데이터를 여러개 조회하는 범위 쿼리이건, 하나만 조회하는 포인트 쿼리건 둘다 성능 발휘, 아껴서 사용
primary key  설정시 그 컬럼에 자동으로 클러스터드 인덱스가 만들어진다.
이 컬럼은 데이터 입력, 수정, 삭제시 항상 정렬을 유지한다.
기본적으로 인덱스는 정렬되어 있다. <<-- 이자체가 인덱스

즉 그 인덱스가 사전식으로 정렬 된다.


2. nonclustered index 넌클러스터 인덱스  인덱스 페이지 따로 만든다. 용량이 더 차지 한다.(로그파일에 저장)
기존의 테이블 + 넌클러스트 인덱스테이블
형식)create nonclustered index 인덱싀름
   on 테이블명(칼럼명 정렬)
   -인덱스 페이지 따로 만든다.
   -레코드 원본은 정렬 안됨
   -테이블당 240개 정도 만들수 있다
   -포인트 쿼리는 성능발휘/ 범위 쿼리는 장담 할 수 없음


예) 클러스터 인덱스는 책을 비유하자면 페이지를 알기 때문에 바로 그 페이지를 펴는 것과 비교되는것이고
넌클러스터 인덱스는 뒤에 목차에서 찾고자 하는 내용의 페이지를 찾고 그 페이지로 이동 하는것과 같음
테이블스캔은 처음부터 한장씩 넘기면서 내용을 찾는것과 같다.

--포인트 쿼리일때 비교 : 포인트 쿼리에서는 별다른 속도 차이가 없다.

--인덱스 추가 범위 쿼리일때 비교

select * from w_zipcode where dong like '당산%'
select * from c_myzip where dong like '당산%'
select * from n_myzip where dong like '당산%'--%당산% 일때 처음에 %가 있으면 table scan 으로 된다. 따라서 속도시간이 늘어난다.

create clustered index dong인덱스
on c_myzip(dong asc)

create nonclustered index dong인덱스2
on n_myzip(dong asc)

--어떤 컬럼에 인덱스를 걸어야 하는지??
1.where절에서 자주 사용되는 컬럼 (예 : dong 컬럼 -> 자주 검색하기 때문에)
2.like '%~~~' 조심 . %는 뒤에만 오게 해야 속도가 빨라진다.
3.between A and B (클러스터인덱스가 유리)--범위 쿼리문에서는 클러스터드인덱스가 유리하지만 클러스터드인덱스는
-- 그 테이블에서 한번만 사용되는 단점을 가지고 있다.
4. order by가 항상 사용되는 컬럼
5. join으로 자주 사용되는 컬럼
 FK( 1:1 대응이 많을 때 -- >  둘다 상관 없음(상황에 따라 넌클러스터드 인덱스를 사용)
  1:N 대응이 많을 때 -- > 클러스터드 인덱스 유리
6. 100만건 중에 10개 조회/1000개 조회 --찾는 것이 적은 수에 주로 인덱스를 걸어주는 것이 상책이다.
 주의)  중복 데이터가 많은 컬럼 (성별)-->인덱스를 거는게 아님
   조회되는것이 많으면 그냥 처음부터 찾는것이 나은편.
7.not 연산자 -> 긍정문을 바꿔서...
8.insert, delete가 빈번한 컬럼은 인덱스에 좋은 영향은 아님

*인덱스로 인해 얻는 손해
1.만드는데 시간과 많은 공간이 필요하고, 만들고 난 후에도 추가적인 공간이 필요한다.
2.데이타를 수정(insert, delete, update)하는 시간,
   특히 insert작업은 오히려 더 많이 걸린다.