나의 향기에 젖다...

[인덱스] - 색인

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

데이터베이스내의 테이블에서 원하는 정보를 좀더 빨리 찾아줄수 있게  데이터의 위치 정보를 모아놓은 데이터베이스내의 객체 object이다.

 
* 인덱스는 정렬되어 있다.
  예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작업은 오히려 더 많이 걸린다.