[MSSQL]MS-SQL 5일차 - Index / Join

### INDEX 생성 시

1) 조회 빈도

2) 데이터 입력이 빈번한가

3) 범위형 쿼리(CI 가 좋음)

4) 포인트 쿼리(CI/NCI 관게 없음)

5) 사이즈

### INDEX  활용

 1)업무분석

 - 조회컬럼

 - 정렬컬럼

 - 컬럼에 대한 프로세스 

 2) 인덱스가 걸리는 컬럼의 사이즈

 - 컬럼의 사이즈 -> 데이터페이지 -> CI의 인덱스 페이지 (CI의 키값)

-> NCI 의 인덱스 페이지

 3) 한 테이블의 컬럼의 숫자를 고민해보세요 

 - Lock, Dead Lock

 4) 복합인덱스 => 검색 데이터 사용량 빈도 ?

 - '풍물'동아리에 있는 학생의 수 => 동아리 코드 + 학생번호

 - '홍길동'학생이 가입한 동아리의 수 => 학생번호 + 동아리코드

 5) 커버드 쿼리의 이용을 고민해보자

 - 커버드 쿼리 : 조회의 조건과 대상 컬럼이 인덱스에 있고,

그 인덱스로 질의를 하는 것

 - Size와 페이지분할

<- 복합인덱스(4)

<- 커버드쿼리(2+2)

 6) 파일그룹을 이용하자

 - 파일그룹을 이용해서 분한테이블/분할인덱스를 만듦으로써

   한개의 테이블에 걸리는 인덱스의 높이/깊이를 

   감소시키면서 성능을 향상시킨다

 7) 인덱스 활용쿼리 디자인

 - 인덱스가 정의된 열을 가공하지 말아야 한다

 - Like 키워드의 검색문자열 앞에 와일드 카드 사용을 피한다

### Index 생성 고려사항

 1. 조회가 빈번하게 일어나지 않는 테이블에 Index 생성하지말것

 2. Where 절 뒤에 오는 컬럼을 대상으로 Index를 고려해야 함

 3. 밀도가 높은 컬럼은 Index를 걸지 말것 예)성별[남,녀]

 4. Index를 재구성, 재작성을 통해서 재정렬을 할수 있음

- 재구성 (Re-Organization) : 리모델링

- 재작성 (Re-Build)   : 재건축

 5. Index 를 설정한다고 해도 반드시 Unique 한것은 아니다

 6. PK는 Clustered Index를, UQ는 Non-Clustered Index가 기본적으로

설정된다

 7. Index가 필요하다면,

포인트 쿼리, 범위쿼리, 추가(순차적/비순차적), 크기

 8. Clustered Index 

- 범위쿼리와 순차적 Data입력/수정/삭제에 바람직

- Table당 한개만 생성 가능

 9. Non-Clustered Index

- 비순차적 Data입력/수정/삭제와 포인트쿼리에 바람직

- Table당 249개 생성 가능

10. Index Key 설정 시, 같은 조건이면 크기(Size) 작은 것으로 할것

11. Scan/Seek

- Table Scan : Heap의 상태에서 전체 페이지 조회

- Clustered index Scan : CI로 설정된 상태에서 전체

페이지 조회

- Clustered index Seek : CI로 설정된 상태에서 

Index를 통한 조회

- Index Seek : NCI로 설정된 상태에서 Index를 통한 조회

12. 외래키(Foreign Key)에 Index를 설정할 때, 성능이 좋아질 

확률이 많다

13. alter index 지원은 SQL Server 2005 부터 가능

14. 단일컬럼 Index 

- 복합 컬럼 index : 설정된 컬럼 모두 Key값

create nonclustered index idx_cust_N

on cust_N (customerid,추가컬럼)

- 포괄열 index    : 포괄열은 Key값은 아님

 : NCI 에서만 가능함

create nonclustered index idx_cust_N

on cust_N (customerid) include (추가컬럼)

15. 채우기비율 설정

   -채우기비율(FillFactor) : Index Leaf Page에만 영향

   -인덱스패딩(PAD_INDEX)  : ON 하면 Leaf외에도 채우기비율 적용

### Cover Index

-- Index Lookup --

if OBJECT_ID('Orders') is not null

drop table Orders

go

select * into Orders from Northwind.dbo.Orders

go

select * from Orders

Exec sp_helpindex Orders

create nonclustered index idx_ord_01 

on Orders(CustomerID) -- nchar(5)

go

set statistics profile on

set statistics io on

set statistics time on

select * from Orders

go

-- Index를 타고 있는가? --

select * 

from Orders

where CustomerID = 'ERNSH'

and ShipVia = 3

go

-- Index 강제 적용 --

select ShipVia 

from Orders with(index(idx_ord_01))

where CustomerID = 'ERNSH'

and ShipVia = 3

go

exec sp_helpindex orders

-- NC Index 재생성 -- (인덱스 포괄열 포함)

drop index Orders.idx_ord_01

create nonclustered index idx_ord_01 

on Orders(CustomerID) include (ShipVia)

go

-- NC Index 추가 생성 --

drop index Orders.idx_ord_02

create nonclustered index idx_ord_02 

on Orders(EmployeeID,ShippedDate)

go

Exec sp_helpindex Orders

go

-- 검색 --

Select * from Orders

where EmployeeID = 4

and ShippedDate >= CONVERT(datetime,'19960101',112)

and ShippedDate < CONVERT(datetime,'19980101',112)

go

-- Hint 사용 --

Select * from Orders with(index(idx_ord_02))

where EmployeeID = 4

and ShippedDate >= CONVERT(datetime,'19960101',112)

and ShippedDate < CONVERT(datetime,'19980101',112)

go

-- NC Index 제거 및 CI 추가 생성 --

drop index Orders.idx_ord_02

create clustered index idx_ord_03 

on Orders(EmployeeID,ShippedDate)

go

Exec sp_helpindex Orders

go

-- CI를 통한 검색 --

Select * from Orders

where EmployeeID = 4

and ShippedDate >= CONVERT(datetime,'19960101',112)

and ShippedDate < CONVERT(datetime,'19980101',112)

go

--별도] NC Index 강제 적용: Key Lookup을 하고 있는가? --

select * 

from Orders with(index(idx_ord_01))

where CustomerID = 'ERNSH'

and ShipVia = 3

go

-- 페이지 I/O 2개 <-- 논리적 읽기 수 2로 알수 있음

Select COUNT(*)

from Orders 

where CustomerID like 'Bo%'

go

-- Key Lookup의 횟수는 총 34회

-- 페이지 I/O가 2개 이므로, (81-2)

-- Key Lookup이 읽었던 페이지 수는 79페이지

Select CustomerID,ShippedDate,Freight

from Orders with(index(idx_ord_01))

where CustomerID like 'Bo%'

go

--> Heap의 상태에서 RID Lookup을 통한

--> 페이지 읽기의 수보다는 늘어난 상태

-- NCI 제거 후, 커버된 Index 생성 --

drop index Orders.idx_ord_01

go

create index idx_ord_04 on Orders (CustomerID)

include (ShipVia,Freight)

go

-- NCI 검색 : 읽기 페이지수 2개 --

Select CustomerID,ShippedDate,Freight

from Orders with(index(idx_ord_04))

where CustomerID like 'Bo%'

go

--> NCI Leaf레벨의 Index 페이지에 

--> ShippedDate,Freight 가 포함되어

--> Key Lookup 이 일어나지 않음

### INDEX Tuning

-- create database inDB

use inDB

select ProductID, Name, Color, Size, Weight

 into pTbl from Adventureworks.Production.Product

select SalesOrderDetailID as OrderID, ProductID, OrderQty,

unitPrice, LineTotal as Total

 into oTbl from Adventureworks.Sales.SalesOrderDetail

-- Create Constraint

Alter Table pTbl

 add constraint pk_pTbl Primary key (ProductID)

Alter Table oTbl

 add constraint pk_oTbl Primary key (OrderID)

Alter Table oTbl

 add constraint fk_pTbl 

foreign key (ProductID) references pTbl(ProductID)

--

select count(*) from pTbl

select count(*) from oTbl

-- 

Select Top(5)* from pTbl 

Select Top(5)* from oTbl

Select * from pTbl --> 7 페이지

Select * from oTbl --> 664 페이지

set statistics IO on

-- Tunning Advisor --

select p.ProductID, p.name, 

sum(o.orderqty) as [oTotal],sum(o.Total) as [Total]

from pTbl P inner join oTbl O on p.ProductID = o.ProductID

group by p.ProductID, p.name

having p.ProductID = '717';

select * from pTbl where color='Silver' order by name;

select ProductID, Total from oTbl order by Total Desc;

## 데이터 베이스 엔진 튜닝 관리자 실행 후 사용될 쿼리문 수행

### Nested Loop Join

set statistics profile on

-- [Nested Loop JOIN ]

select name,addr,mobile1,mobile2

from userTbl u, buyTbl b

where u.userID = b.userid

and prodName = '모니터'

select prodName,price,amount

from userTbl u, buyTbl b

where u.userID = b.userid

and name = '박주영'

-- [LOOP JOIN : 중첩루프조인]

SELECT * 

FROM Sales.Customer AS c

INNER JOIN Sales.CustomerAddress AS ca

ON c.CustomerID = ca.CustomerID

OPTION (LOOP join);

go

-- [Merge Join: 병합조인]

SELECT * 

FROM Sales.Customer AS c

INNER JOIN Sales.CustomerAddress AS ca

ON c.CustomerID = ca.CustomerID

OPTION (MERGE join, FORCE ORDER);

GO

SELECT * 

FROM Sales.Customer AS c

INNER JOIN Sales.CustomerAddress AS ca

ON c.CustomerID = ca.CustomerID

OPTION (MERGE join);

GO  

SELECT * 

FROM Sales.CustomerAddress AS ca

INNER JOIN Sales.Customer AS c

ON c.CustomerID = ca.CustomerID

OPTION (MERGE join);

GO

-- [Hash Join] --

SELECT * 

FROM Sales.Customer AS c

INNER JOIN Sales.CustomerAddress AS ca

ON c.CustomerID = ca.CustomerID

OPTION (hash join, FORCE ORDER);

GO

SELECT * 

FROM Sales.Customer AS c

INNER JOIN Sales.CustomerAddress AS ca

ON c.CustomerID = ca.CustomerID

OPTION (hash join);

GO

### Hash Match Join

-- Hash Match(Aggregate)

-- Stream Aggregate 

USE indb

GO

IF OBJECT_ID('TEST','U') IS NOT NULL

       DROP TABLE TEST

GO

CREATE TABLE TEST( A INT NOT NULL, B INT NOT NULL)

GO

-- NCI 생성

CREATE INDEX NC_TEST_A ON TEST(A)

GO

SET NOCOUNT ON

GO

DECLARE @I INT = 1

WHILE(@I <= 10000)

BEGIN

       INSERT INTO TEST SELECT @I, CONVERT(INT,RAND()* 1000) +1

       SET @I = @I + 1

END

SET NOCOUNT OFF

GO

SELECT  B

FROM TEST

GROUP BY B

SELECT A

FROM TEST

GROUP BY A

-- 기존 Index 삭제 후, Unique & NCI 생성

DROP INDEX TEST.NC_TEST_A

GO

CREATE UNIQUE INDEX NC_TEST_A ON TEST(A)

GO

SELECT A

FROM TEST

GROUP BY A

---------------------------------------------

-- Table 삭제 후, 재생성

IF OBJECT_ID('TEST','U') IS NOT NULL

     DROP TABLE TEST

GO

CREATE TABLE TEST( A INT NOT NULL, B INT NOT NULL)

GO

SET NOCOUNTON

GO

DECLARE @I INT = 1

WHILE(@I <= 2519)

BEGIN

       INSERT INTO TEST SELECT @I, @i

       SET @I = @I + 1

END

SET NOCOUNTOFF

GO

SELECT B

FROM TEST

GROUP BY B

### SQL Profiler 테스트

select * from Cust_A

select * from Cust_c

select * from Cust_n

go

select * from Cust_A

go

select * from Cust_c

go

select * from Cust_n

go

select * from Cust_A;

select * from Cust_c;

select * from Cust_n;

go