[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;
'01.MS-SQL' 카테고리의 다른 글
| [MSSQL]MS-SQL 5일차 - Backup / Restore / Lock / Block / Cursor (0) | 2013.04.05 |
|---|---|
| [MSSQL]MS-SQL 4일차 - Trigger / Index (0) | 2013.04.04 |
| [MSSQL]MS-SQL 3일차 - Transaction / View / StoreProcedure (0) | 2013.04.03 |
| [MSSQL]MS-SQL 2일차 - Object 관리 (0) | 2013.04.02 |
| [MSSQL]MS-SQL 1일차 - 테이블 / 정규화 (0) | 2013.04.01 |
Script.zip