인덱스 index
--**장점
--검색속도 향상(그렇지 않은 경우도 있음)
--쿼리 부하 감소
--**단점
--대략 10%정도의 추가공간 필요
--최초 인덱스 생성시간 필요
--데이터 변경 작업시 성능 저하
--**인덱스 종류
--1. 클러스터형 인덱스(Clustered) - 테이블 당 한개만 생성됨
--2. 비클러스터형 인덱스(Nonclustered)
--clustered : 책의 목차 목록
--nonclustered : 책 맨 뒷장의 단어 찾기
--OnlineShopDB의 customersTbl 생성시 custId를 primary key로 지정
-- 따라서 custId 컬럼에 클러스터형 인덱스 자동 생성
--primary key에 비 클러스터형 인덱스 생성 가능
-- 방법 : primary key nonclustered
-- 따라서, 인덱스가 자동 생성되는 조건은 해당 열에 primary key 또는 unique를 지정 했을 경우!
-- 우선순위 : PK > UK(수동)
--자동 생성
create table indexTest
(
a int primary key,
b int,
c int
);
--자동 PK키에 생성 (유니크 무시)
create table indexTest2
(
a int primary key,
b int unique,
c int unique,
d int
)
--아무데도 안잡기
create table indexTest3
(
a int primary key nonclustered,
b int unique,
c int unique,
d int
)
--unique에 잡기
create table indexTest4
(
a int primary key nonclustered,
b int unique clustered,
c int unique,
d int
)
--clustered : 책의 목차 목록
--nonclustered : 책 맨 뒷장의 단어 찾기
--B-Tree
--Root Node는 Leaf Node의 주소를 가지고있는것들이 모여있음
--Leaf Node는 RootNode에서 하나씩 빠져나와 따로(조건에 따라) 만들어졌고 그 아래
(조건에 따라)추가데이터들이 붙음(분할삽입)
--데이터가 용량을 넘어가면 RootNode에 하나가 추가되고 LeafNode는 또 분할되어 균등하게 나누어짐
--RootNode가 많아져서 분할 될 경우에는 RootNode위에 RootNode가 만들어짐
--clustered 와 nonclustered 구조의 차이
--clustered table
create table clusterTbl
(
userId char(8) not null,
name nvarchar(10) not null
);
Go
insert into clusterTbl values('FFFF', '김선생');
insert into clusterTbl values('IIII', '이선생');
insert into clusterTbl values('CCCC', '박선생');
insert into clusterTbl values('GGGG', '송선생');
insert into clusterTbl values('JJJJ', '성선생');
insert into clusterTbl values('AAAA', '임선생');
insert into clusterTbl values('DDDD', '백선생');
insert into clusterTbl values('HHHH', '조선생');
insert into clusterTbl values('BBBB', '최선생');
insert into clusterTbl values('EEEE', '라선생');
--PK키를 잡는다(자동 정렬됨)
alter table clusterTbl
add constraint PK_clusterTbl_userId
primary key (userId);
--nonclustered table
create table nonclusterTbl
(
userId char(8) not null,
name nvarchar(10) not null
);
go
insert into nonclusterTbl values('FFFF', '김선생');
insert into nonclusterTbl values('IIII', '이선생');
insert into nonclusterTbl values('CCCC', '박선생');
insert into nonclusterTbl values('GGGG', '송선생');
insert into nonclusterTbl values('JJJJ', '성선생');
insert into nonclusterTbl values('AAAA', '임선생');
insert into nonclusterTbl values('DDDD', '백선생');
insert into nonclusterTbl values('HHHH', '조선생');
insert into nonclusterTbl values('BBBB', '최선생');
insert into nonclusterTbl values('EEEE', '라선생');
--UK key 추가 : 추가해도 자동 정렬되지 않는다.
alter table nonclusterTbl
add constraint UK_nonclusterTbl_userId
unique(userId);
--cluster에 data추가한 경우 : 포함되서 같이 정렬됨
insert into clusterTbl values('BBTT', '나조교');
insert into clusterTbl values('DDSS', '박조교');--noncluster에 data추가한 경우 : 맨 아래에 추가됨
2일차
--용어 설명
--Table Scan :
테이블 데이터를 페이지 처음부터 끝까지 검색
--Index Seek :
비클러스터형 인덱스에서 데이터를 검색
--RID Lookup :
비클러스터형 인텍스에서 키 검색 후, 실제데이터 페이지에서 검색
--Clustered Index Seek :
클러스터형 인덱스에서 데이터 검색
--Key Lookup :
비클러스터형 인덱스에서 데이터 검색 후,클러스터형 인덱스에서 데이터 검색
--Clustered Index Scan :
Table Scan과 유사, 클러스터형 인덱스의 Leaf 페이지는 결국 데이터 페이지
--테이블 생성
create table mixedTbl
(
userId char(8) not null,
name nvarchar(10) not null,
addr nchar(2)
);
insert into mixedTbl values('FFFF', '김선생', '서울');
insert into mixedTbl values('IIII', '이선생', '포항');
insert into mixedTbl values('CCCC', '박선생', '광주');
insert into mixedTbl values('GGGG', '송선생', '수원');
insert into mixedTbl values('JJJJ', '성선생', '서울');
insert into mixedTbl values('AAAA', '임선생', '서울');
insert into mixedTbl values('DDDD', '백선생', '포항');
insert into mixedTbl values('HHHH', '조선생', '부산');
insert into mixedTbl values('BBBB', '최선생', '수원');
insert into mixedTbl values('EEEE', '라선생', '서울');
--clustered 생성
alter table mixedTbl
add constraint PK_mixedTbl_userId
primary key (userId);
----비클러스터형 인덱스의 키로 검색한 경우
select addr from mixedTbl where name='라선생'
--클러스터형 인덱스의 키로 검색한 경우
select * from mixedTbl where userId = 'DDDD';
select * from mixedTbl;
--**rebuild
인덱스를 내부적으로 재생성(완료 후 기 인덱스 삭제)
--통계정보 다시 계산
--all옵션 사용시 해당 인덱스가 클러스터드 인덱스이고,
--그 인덱스에 관련된 Secondarty인덱스가 있는 경우 자동으로 rebuild
--( all안하면 다른 것들은 하나하나 다 바꿔줘야 함)
alter index all on mixedTbl
rebuild
with(online=on)
--** 인덱스의 리프노드를 물리적으로 재구성(조각화 조절)
--통계정보를 생성하지 않음
--온라인 상태에서 사용가능
alter index all on mixedTbl
reorganize;
--index 제거
--** primary key 나 unique 제약조건으로 자동 생성된 인덱스는 drop index구문으로 제거 불가능하다
--복합인덱스로 구성되어 있을 경우
--인덱스를 하나씩 날리는데 nonclustered부터 삭제하는것이 좋다.(하위폴더부터 삭제한는 것 처럼)
drop index 테이블 이름, 인덱스 이름
--**인덱스 정리
--1. 인덱스는 컬럼 단위에 생성된다.
--2. where 절에서 사용되는 컬럼에 인덱스를 생성해야한다.
--3. where 절에서 자주 사용하는 컬럼에 생성하는 것이 효과적이다.
--4. 데이터 중복도가 높은 컬럼에는 인덱스의 효과가 미미해질 수 있다.
--5. foreign key로 지정된 컬럼에는 인덱스를 생성하는게 효과적이다
--6. join에 빈번히 사용되는 컬럼에는 인덱스를 생성하는게 효과적이다.
--7. 삽입/갱신/삭제가 자주 있다면 인덱스 생성에 특별히 주의해아한다
--OLTP(Online transaction processing) DB경우 최소한의 인덱스 구성
--OLAP(Online Analytical processing) DB경우 where절에 사용되는 컬럼들마다 인덱스를 구성해주면 좋다
--9. 클러스터형 인덱스가 없는 테이블이 필요한 경우도 있다.
--대량의 데이터가 계속입력되는 DB는 클러스터형 인덱스의 페이지 분할이 계속 일어 날 수도 있다
--이런경우 비 클러스터형 인덱스가 유리 할 수 있음
--10. 사용하지않는 인덱스는 제거