[인덱스]-색인
책에서 원하는 내용을 빨리 찾으려면 인덱스를 이용(책의 인덱스와 비슷한 개념)
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작업은 오히려 더 많이 걸린다.
[출처] 클러스터인덱스와 비클러스터인덱스차이|작성자 애닳퍼라