12_인덱스

[Action Query]
1.Make Table Query (다른이름저장)
형식)Select 칼럼 into 새로운테이블 from 기존테이블

select * from tb_member
select * into mem2 from tb_member
select * into mem3 from tb_member where member_idx=0
select uid,upw into mem4 from tb_member

2.Append명령(칼럼명 갯수는 반드시 일치)
테이블에 레코드 추가할 때 다른테이블에 있는 레코드를
가져와서 추가한다.
형식)insert into 테이블명(칼럼명)
insert into mem5(uid,upw) select uid,upw from tb_member

insert tb_member values('','','','',)

select uid,upw into mem5
from tb_member where member_idx=0

select * from mem5

[인덱스] 색인 : 빠르게 찾자
책에서 원하는 내용을 빨리 찾으려면 인덱스를 이용
(책의 인텍스와 비슷한 개념)
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,upw,uname 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 설정시 그 컬럼에 자동으로 클러스터드 인덱스가
만들어진다.
이 컬럼은 데이터 입력,수정,삭제시 항상 정렬을 유지한다.
기본적으로 인덱스는 정렬되어있다. <--이자체가 인덱스

*어떤 컬럼에 인덱스를 걸어야 하는지?
1.where절이 자주 걸리는 컬럼
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작업은 오히려 더 많이 걸린다.

*성능비교

drop table c_myzip
select * into c_myzip from w_zipcode --테이블복사
select * into n_myzip from w_zipcode --테이블복사

select * from w_zipcode where zipcode='441-872'
select * from c_myzip where zipcode='441-872'
select * from n_myzip where zipcode='441-872'

--인덱스추가 포인트쿼리일때 비교
create clustered index zip인덱스
on c_myzip(zipcode asc)

create nonclustered index zip인덱스2
on n_myzip(zipcode asc)
drop index c_myzip.zip인덱스
drop index n_myzip.zip인덱스2

--인덱스추가 범위쿼리일때 비고
create clustered index dong인덱스
on c_myzip(dong asc)

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

select * from w_zipcode where dong like '%당산%'
select * from c_myzip where dong like '%당산%'
select * from n_myzip where dong like '%당산%'

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

--우편번호 dong칼럼에 인덱스 추가
create clustered index dong인덱스
on w_zipcode(dong asc)

sp_helpindex w_zipcode
* 인덱스 사후관리 DataBase Consistency Checker

인덱스의 채우기 비율은 80%가 제일 적정수준.

1.인덱스를 삭제하고 재생성
2.인덱스 다시 정렬(오버헤드는 크지만 이방법이 제일 좋음)
DBCC DBREINDEX('mydb.prettyimo.w_zipcode',dong인덱스,80)
                              DB명,소유주,테이블명

3.인덱스 조각화(하드디스크조각모음처럼 단편화현상이 일어남)
DBCC INDEXDEFRAG(mydb,w_zipcode,dong인덱스)