[SQL SERVER] Index

동영상을 보는게 더 좋음

: https://www.youtube.com/watch?v=sSOsWoaTrNk&index=28&list=PLVsNizTWUw7Gw_ubikxbzecfveoxemsAs

데이터베이스는 성능이 아주 중요한 역할을 한다

인덱스가 없어도 데이터를 조회하고 변경하는데 아무런 문제가 되지 않지만

- 장점

1) SELECT를 빠르게 접근

2) 해당 쿼리의 부하가 줄어서 시스템 전체의 성능이 향상

- 단점

1) 데이터베이스 크기의 10% 정도의 추가 공간

2) 인덱싱을 하는데 시간이 많이 소요

3) Insert Update Delete가 자주 일어날때는 성능이 나빠질 수 있음

- 인덱스가 사용되기 좋은 조건

1) 인덱스는 열 단위에서 생성

2) WHERE 절에서 사용되는 열에 인덱스 생성

3) WHERE 절에서라도 자주 사용해야 가치가 있음

4) 데이터 중복도가 높은 열은 인덱스를 만들어도 효과가 없다

5) 외래키가 사용되는 열에는 생성해주는 것이 좋음

6) JOIN에 자주 사용되는 열

7) INSERT/UPDATE/DELETE 가 자주 사용되는 지를 고려

8) 클러스터형 인덱스는 테이블당 하나만 생성

9) 사용하지 않는 인덱스는 제거

10) 계산 열에도 인덱스를 활용할 수 있음

CREATE TABLE computeTbl (input INT,input2 INT, hap AS input+input2 PERSISTED);

11) 클러스터형 인덱스가 테이블에 아예 없는 것이 좋은 경우도 있음

클러스터형 

ex) 영어사전

: 내용 자체가 순서대로 정렬

비클러스터형

ex) 책 뒤의 <찾아보기>

: 행 데이터를 인덱스로 지정한 열에 맞춰서 자동 정렬

+) 클러스터형 VS 비클러스터형

클러스터

- 인덱스를 생성할 때 데이터 페이지 전체를 다시 정렬

- 이미 대용량의 데이터가 입력된 상태라면 인덱스 입력이 시스템 부하를 줄 수 있음

- 클러스터형 인덱스는 인덱스 자체의 리프 페이지가 곧 데이터페이지 즉 인덱스 자체에 데이터가 포함

- 비클러스터형 인덱스 보다 검색 속도는 빠르지만 입력/수정/삭제는 느림

- 클러스터 인덱스는 성능이 좋지만 테이블 당 한개만 생성 가능 

  어느 열에 클러스터형 인덱스를 생성하느냐에 따라 시스템 성능이 달라짐

비클러스터형

- 데이터 페이지는 그냥 둔 상태에서 별도의 페이지에 인덱스 구성

- 인덱스의 리프 페이지는 데이터가 아니라 데이터가 위치하는 포인터 -> 클러스터형보다는 검색속도가 느리지만 데이터 입력/수정/삭제는 빠름

- 비클러스터 인덱스는 여러개 생성할 수 있으나, 남용하면 성능을 떨어뜨림

Primary Key로 설정하면 클러스터형 인덱스가 생성된다.

userID char(8) NOT NULL PRIMARY KEY

비클러스터형으로 생성하려면

userID char(8) NOT NULL PRIMARY KEY NONCLUSTERED

Unique key로 설정하면 비클러스터형 인덱스가 생성된다. 

userID char(8) UNIQUE

클러스터형으로 생성하려면

userID char(8) UNIQUE CLUSTERED

=> 만약에 인덱스를 제거하고 싶으면 ALTER INDEX로 제약 조건을 제거한다 (primary key와 unique key로 구성된 것)

+) 만약 기본 열을 UNIQUE로 변경한다면 데이터의 정렬 순서는 바뀌지 않는다

왜냐하면 비 클러스터형은 책 뒤의 찾아보기 같은 존재로

데이터는 그대로 인데 그 데이터에 indexing을 하는 것이기 때문이다

CREATE INDEX idx_userTbl_addr ON userTbl(addr);

: 인덱스를 생성

CREATE UNIQUE INDEX idx_userTbl_bd ON userTbl(birthYear);

: UNIQUE option을 설정 -> 기존의 birthYear에서 중복값이 없어야함

ALTER : 인덱스 변경

DROP : 인덱스 삭제

인덱스가 만들어지는 조건

1) Primary Key

2) Unique 

만약 Check / Default는 index가 만들어지지 않는다

+) Primary Key와 Unique의 차이

Primary Key(기본키) :  
해당 테이블의 식별자 역할을 하는 제약조건으로 테이블에 하나만 설정할 수 있음

Unique Key  :   
해당 칼럼에 입력되는 데이터가 각각 유일하다는 것을 보장하기 위한 제약조건으로 한 테이블에 여러개 설정이 가능합니다. 물론, 기본키는 Unique key에 포함됩니다.

EXEC sp_helpindex tbl;

: index를 확인

+) index 정보를 예쁘게 보여주는 저장 프로시저

USE indexDB;

GO

CREATE PROCEDURE usp_IndexInfo

@tablename sysname

AS

SELECT @tablename AS '테이블 이름',

l.name AS '인덱스 이름',

l.type_desc AS '인덱스 타입',

A.data_pages AS '페이지 개수',

A.data_pages * 8 AS '크기(KB)',

P.rows AS '행개수'

FROM sys.indexes l

INNER JOIN sys.partitions P

ON P.object_id = l.object_id

AND OBJECT_ID(@tablename) = l.object_id

AND l.index_id = P.index_id

INNER JOIN sys.allocation_units A

ON A.container_id = P.hobt_id;

SELECT쿼리를 날릴때 자체적으로 index를 안쓰는게 더욱 빠를것 같다고 판단되면 index를 쓰지 않는다

but index를 쓰라고 강제할 수도 있다

SELECT * FROM Cust_NC WITH (INDEX(idx_cust_nc)) WHERE CustomerID < 100;