데이터베이스
- 데이터 파일과 로그 파일로 구성됨
- 데이터 파일 : mdf,ndf 확장자
- 로그 파일 : ldf 확장자
- 다양한 형태의 개체 포함
(테이블,인덱스,프로시저,트리거,함수)
테이블
- 데이터베이스 내의 존재하는 대표적인 개체
(데이터를 가지고 있다.)
- 시스템 테이블 & 사용자 테이블
- 실제 데이터를 물리적으로 포함하고 있음
- 행과 열로 구성 됨
페이지
- 페이지 파일을 구성하는 논리 단위
- SQL Server의 기본 데이터 저장단위(8KB)
- 데이터를 쓸 때 행을 페이지에 기록함
- 데이터를 읽을 때(SELECT)
페이지 내의 모든 행이 읽혀짐
- 페이지 내의 행이 많을수록 I/O 효율 증가
- 0 ~ N 사이의 순차적인 번호를 가짐 (페이지 번호)
레코드 100개를 읽어야 할 경우 (SELECT)
페이지(8KB)에 레코드 10개 들어있는 경우는 10번
페이지(8KB)에 레코드 100개 들어있는 경우는 1번만
읽으면 되기 때문에 1번 읽어서 레코드 100개를
읽어주는 것이 성능이 더 좋다.
인덱스 이해
EX) 인덱스는 주민센터다.
- 사람을 빨리 찾을 수 있게 해준다.
EX) 인덱스는 도서 검색 시스템이다.
- 그 책을 가장 빨리 찾을 수 있게 해준다.
EX) 인덱스는 다리다.
-강 건너 그 곳에 가장 빨리 갈 수 있게 해준다.
(멀리 돌아갈 곳을 다리위로 가로질러서 건너간다.)
위의 3개의 공통점은 "빨리"이다. (인덱스 없이는 빨리 갈 수 없다.)
인덱스 장점
빠른 데이터 검색
- 찾는 데이터를 가지고 있다면 직접 주거나
없다면 어디에 있는지 알려준다.
데이터 중복을 방지
- PRIMARY KEY 제약조건
(PK 생성시 UNIQUE Clustered Index 자동 생성됨)
- UNIQUE 제약조건
잠금 최소화
- 최소 범위의 잠금을 가능하게 해 준다.
(인덱스를 참조하여 원하는 데이터를 빨리 찾을 수 있기 때문에 Update를 빨리하고 나오게 된다.)
- 동시성을 높여 준다.
(Update를 빨리하고 나오기 때문에 많은 사용자가 동시에 동일한 데이터를 엑세스 할 수 있게 된다)
인덱스 단점
물리적인 공간 차지
- 인덱스는 테이블처럼 데이터를 가짐
- 테이블처럼 물리적인 공간을 차지함
인덱스에 대한 유지관리 부담
- SELECT 문은 인덱스를 좋아함
- INSERT 문은 인덱스를 별로 좋아하지 않음
(데이터를 추가하면 인덱스에도 등록해야해서)
데이터가 극히 적다면
- 인덱스가 있어도 사용되지 않을 수 있음
- 얻는 효과보다 유지관리 부담이 클 수 있음
인덱스 조각화
내부 조각화
- 인덱스 페이지가 꽉 차지 않고 비어있는 상황
- OLTP성 테이블의 인덱스라면 빈공간 확보(내부 조각화)가 필요하다.
- OLAP성 테이블에서는 빈공간이 많으면 조회할 때 읽는 페이지 수가 증가하여 성능이 떨어질 수 있다.
(따라서 페이지 분할이 덜 발생하기 때문에 꼭 나쁘다고 할 수 없다.)
외부 조각화
- 인덱스의 논리적인 순서와 물리적인 순서가 맞지 않는 경우로 성능에 도움이 되지 않으므로 제거해야 한다.
(인덱스 순서가 흩어져 있는 것)
- 인덱스 리빌드 목적
인덱스 유지관리의 필요성
- 재생성 또는 재구성 작업 자동화
- 모든 인덱스를 대상으로 하는 것은 바람직 하지 않음
(인덱스 재생성 또는 재구성은 CPU 부하를 유발)
인덱스 조각모음은 인덱스 스캔(전체 조회)를 빠르게 해주는 것이지 인덱스 SEEK(조건으로 값 찾기)와는 상관이 없다.
만약 쿼리문에서 WHERE 조건을 지정해서 특정 값만 가져 오도록 쿼리를 만들었는데 인덱스 스캔한다면
쿼리 튜닝(인덱스 생성)이 필요하다.
테이블과 인덱스
테이블의 존재 형태 2가지
- 힙 테이블
- 클러스터형 인덱스 테이블
인덱스 종류
- 클러스터 인덱스
- 비 클러스터 인덱스
힙이란 ?
정렬 기준이 없이 저장 된 테이블의 존재 형태
- 데이터 페이지 내의 행동들 간에 순서가 없음
- 데이터 페이지들 간에도 순서가 없음
- 클러스터 형 인덱스가 없는 테이블
아무렇게나 쌓여 있음
힙의 장점과 단점
- INSERT문이 좋아하는 테이블의 형태
- 새로운 행을 기존 페이지의 빈 곳에 추가하면 됨
- 빈 공간이 없으면 새로운 페이지에 추가하면 됨
- SELECT문이 싫어하는 테이블 형태
- 데이터가 극히 적으면 상관이 없음
- 데이터가 많으면 원하는 데이터 찾기가 너무 힘듦
SELECT를 위한 테이블인가 INSERT를 위한 테이블인가.
INSERT를 위한 테이블이라면 힙 테이블로 유지해야 하지만.
SELECT를 위한 테이블이라면 대처가 필요(인덱스 생성)
힙의 원하는 데이터 찾아가기
Table Scan
- 데이터를 찾을 수 있는 유일한 방법
- 모든 데이터 페이지를 다 읽어야 함 (다 읽음)
- 참고 : IAM 페이지를 참조해서
해당 데이터를 모두 읽게 됨
클러스터 형 인덱스란 ?
- 특정 열을 기준으로 데이터가 정렬됨
- 물리적 정렬이 아닌 논리적 정렬
- 정렬 기준의 열로 찾는데 빠른 성능을 보여줌
- 테이블 당 하나의 클러스터 형 인덱스 설정 가능
- 실제 데이터를 가지므로 하나만 존재 가능
- 테이블을 여러 개 만들면 여러 개 존재 가능
힙 -> 클러스터형 인덱스
힙테이블
클러스터형 테이블
인덱스 키 이름을 기준으로 데이터 페이지가 정렬되어 있다.
테이블 존재 형태의 문제
힙 테이블로 둘 것인지, 클러스터형 테이블로 만들어서 정렬시킬 것인지
장/단점이 있으니 충분한 고민이 필요
클러스터형의 원하는 데이터 찾아가기
Clustered Index Scan
- 모든 데이터 페이지를 읽음
- Table Scan과 크게 다를 바 없음
- 데이터가 많은 경우 성능 문제 발생
Clustered Index Seek
- 특정 데이터를 클러스터 인덱스에서 찾음
- 아주 빠른 성능을 보여줌
인덱스 구조
클러스터형 인덱스 유일성 지정
UNIQUE Clustered Index
- 인덱스 키 열에 중복된 데이터가 추가되는 것을 차단함
UNIQUE 속성을 생략하면
- 기본적으로 UNIQUE 하지 않은 인덱스가 생성됨
그래서 내부적으로 유일성을 확보하기 위한 값이 사용됨
(공간 낭비가 되기 때문에 명시해 주는게 좋다)
UNIQUE 여부를 명확히 설정
- 인덱스 키 열의 유일성을 우리가 아는게 중요한 것이 아니고
SQL SERVER가 아는 것이 중요
클러스터형 인덱스는 만능이 아님
인명 전화번호부에서 서대문구 주민을 찾으려면
- 전화번호부를 처음부터 끝까지 다 읽어야 함
(이름으로 정렬되어 있기 때문)
A 열을 기준으로 정렬된 데이터에서
- A로만 찾는다면 좋겠지만
EX) WHERE A=10 (인덱스 활용)
- B 또는 C 등 다른 열로 찾는 경우 의미가 없어짐
EX) WHERE B=5 (인덱스 활용 못함)
다양한 검색을 위한 추가적인 도움이 요구됨
- 클러스터 인덱스의 부족함을 도와주는 비 클러스터인덱스가 필요
힙 + 비 클러스터 형 인덱스
비 클러스터 형 인덱스가 가진 것
- 생성한 인덱스 키 열의 모든 데이터
name,age 컬럼으로 만들었다면
name,age + RID값을 가지고 있음
RID
힙 테이블이 데이터가 정렬되어 있지
않기 때문에 RID를 매칭시킴
RID를 갖는 이유
- 주소를 딱 찍어주니 좋음
- RID는 거의 변하지 않음
- RID는 값이 크지도 않음
비 클러스터 인덱스는 999개까지 만들 수 있음
- 불필요한 비 클러스터 인덱스를 너무 많이 만들면 성능 저하
- 데이터와 별개로 저장되기 때문
힙 + 비 클러스터 형 인덱스
index Seek
- Root페이지부터 찾아가기
- 아주 빠른 성능을 보여 줌
Rid Lookup
- 힙 테이블을 찾아가는 과정
- 비 클러스터 형 인덱스가 가지 못한 데이터를 찾아가는 과정
Index Scan
- 리프 수준의 모든 인덱스 페이지를 다 읽음
총 정리
- 비 클러스터 인덱스는 컬럼을 다 가지고 있지 않지만, 다 가지고 있을 수 있다.
- 쿼리는 빼가는 과정이다.
조회에 필요없는 컬럼을 제거
조회 화면을 변경
- 쿼리는 테이블이 아닌 인덱스로 날려야 한다
인덱스의 도움을 받지 못한 쿼리는 테이블로 날아가고 성능에 문제가 생길 수 있다.
