Database

Database란

데이터베이스가 중요한 이유?

• 효율적인 확장

• 데이터 무결성
• 조직의 데이터가 라이프사이클의 어느 시점에서든 정확하고 완전하며 일관성이 있다는 것을 보장하는 것

• 데이터 보안

• 데이터 분석

데이터 무결성 제약조건의 종류와 개념

1. 개체 무결성
1. 테이블은 기본키를 지정하고 그에 따른 무결성 원칙을 지켜야 하는 조건
2. 기본키(Primary Key)에는 null 값이 올 수 없음
1. 기본키란 테이블에서 특정 행(row)를 구별하기 위해 후보 키 중에서 선택된 고유한 식별자 키

2. 참조 무결성
1. 테이블 간의 참조 관계를 선언하는 제약 조건
2. 외래키(Foregin Key)의 값은 null이거나 참조 릴레이션의 기본키 값과 동일해야함
3. 외래키 속성은 참조할 수 없는 값을 지닐 수 없음
1. 외래키란 키 중에서 다른 테이블의 행(row)을 유일하게 식별할 수 있는 키

3. 도메인 무결성
1. 테이블에 존재하는 필드의 무결성을 보장하기 위한 것으로 필드의 타입, null 값 허용 등에 대한 사항을 정의하고 올바른 데이터가 입력되었는지 확인하는 조건

4. Null무결성
1. 특성 속성값에 NULL이 올 수 없다는 조건이 주어진 경우 그 속성값은 NULL 값이 올 수 없다는 제약 조건

5. 고유 무결성
1. 테이블의 특정 속성에 대해 각 레코들이 갖는 값들이 서로 달라야 하는 조건

6. 키 무결성
1. 하나의 테이블에는 적어도 하나의 키가 존재해야 하는 조건

7. 관계 무결성
1. 테이블의 어느 한 행의 삽입 가능 여부 또는 한 테이블과 다른 테이블의 행들 사이의 관계에 대한 적절성 여부를 지정하는 조건

DBMS : Database Management System

기능

• 데이터의 추가/조회/변경/삭제

• 데이터의 무결성 체크

• 트랜잭션 관리

• 데이터의 백업 및 복원

• 데이터 보안 관리

DBMS 발전과정

1. 파일 시스템 (ISSAM, VSAM)

2. 계층형 (HDBMS)

3. 네트워크형 (NDBMS)

4. 관계형 (RDBMS)

5. NO SQL

Database Object

스키마(Schema)

• DBMS는 스키마를 참조하여 사용자의 명령을 수행

외부 스키마 = 사용자 뷰

• 개인의 입장에서 필요로 하는 데이터베이스의 논리적 구조

• 하나의 데이터베이스의 시스템에는 여러개의 외부 스키마가 존재할 수 있으며 하나의 외부 스키마를 여러개의 응용 프로그램이나 사용자가 공용할 수도 있다.

• 질의어(SQL)을 이용하여 DB를 쉽게 사용

개념 스티마 = 전체적인 뷰

• 데이터베이스의 전체적인 논리적 구조, 모든 응용 프로그램이나 사용자들이 필요로 하는 데이터를 종합한 조직 전체의 데이터베이스로 하나만 존재

• 데이터베이스의 접근 권한, 보안 및 무결성 규칙에 관한 명세를 정의

• 데이터베이스 파일에 저장되는 데이터의 형태를 나타내는 것

내부 스키마 = 저장 스키마

• 물리적 저장장치의 입장에서 본 데이터베이스 구조, 물리적인 저장위치와 밀접한 계층

• 실제로 데이터베이스에 저장될 행의 물리적인 구조를 정의하고 저장 데이터 항목의 표현방법, 내부 행의 물지적인 순서 등을 나타냄

옵티마이저

쿼리 실행 절차

1. Parsing

2. Optimization

3. Generation

4. Execution

SQL Parsing

• DBMS는 우리가 세운 실행 계획을 쪼개서 SQL파서를 통해 옵티마이저로 파싱함

• 우리가 전송한 SQL을 쪼개서 구성요소를 파악하고 이를 파싱해서 파싱트리를 만든다.

• SQL문장의 문법적인 검사는 파싱단계에서 이루어진다.

Optimization

• SQL 파싱트리르 기반으로 옵티마이저가 가진 여러가지 동작을 수행

• Query Transformer : 파싱된 SQL을 보고 같은 결과를 도출하되 좀 더 나은 실행 계획을 갖는 SQL로 변환이 가능한지를 판단하여 변환 작업을 수행

• Estimator : 시스템 통계정보를 딕셔너리로부터 수집하여 SQL을 실행할 때 소요되는 총 비용을 계산

• Generation : 옵티마이저가 생성한 실행계획을 SQL 엔진이 실제 실행할 수 있는 코드나 프로시저 형태로 포맷팅한다.

• Execution : 포맷팅된 SQL을 실행하고 결과를 사용자에게 전달한다.

인덱스(index)

• 어떤 데이터가 어디 위치하였는지 위치 정보를 가진 주소록 개념

• 기본원리 : 테이블 전체 스캔 → 정렬 → 블록 기록

인덱스 타입 종류

해시 테이블(Hash Table)

• 해시 테이블 기반의 DB 인덱스는 (데이터=컬럼의 값, 데이터의 위치)를 (Key, Value)로 사용하여 컬럼의 값으로 생성된 해시를 통해 인덱스를 구현

• 시간 복잡도 O(1)

• 부등호 연산이 자주 사용되는 데이터베이스 검색을 위해서는 해시 테이블이 적합하지 않다.

B+Tree

• 리프노드(데이터노드)만 인덱스와 함께 데이터(Value)를 가지고 있고, 나머지 노드(인덱스노드)들은 데이터를 위한 인덱스(Key)만을 갖는다.

• 리프노드들은 LinkedList로 연결되어 있다.

• 데이터 노드 크기는 인덱스 노드의 크기와 같지 않아도 된다.

페이지(Page)

카디날리티(Cardinality)

• 카디날리티가 높을수록 중복도가 낮아지며 유니크한 값이 많다는 것

Nested Loops 조인

드라이빙 테이블

드리븐 테이블

Table A, Table B의 결합 대상 행 수를 R(A), R(B)라고 한다면 접근되는 행 수는 R(A)*R(B) Nested Loops Join에서 실행시간은 R(A)*R(B)에 비례

데이터베이스 정규화

1. 제 1 정규화

• 개별 테이블에서 반복되는 그룹을 제거

• 각 관련 데이터 집합에 대해 별도의 테이블을 제작

• 기본 키를 사용하여 각 관련 데이터 집합을 식별

2. 제 2 정규화

• 여러 행에 적용되는 값 집합에 대해 별도의 테이블을 제작

• 외래 키를 사용하여 이러한 테이블 간의 관계를 설정

3. 제 3 정규화

• 키에 의존하지 않는 필드를 제거

Index Seek

• 필요한 리프 페이지만 탐색하여 접근

Index Scan VS Index Seek

• Index Scan : 해당 테이블의 컬럼을 모두 찾음

• Index Seek : 해당 인덱스를 바로 찾아감

동적쿼리

• MS-SQL에서 저장 프로시저, Stored Procedure는 실행 계획이 플랜 캐시라는 개체에 캐싱된다.

• 즉, 한번 컴파일된 저장 프로시저는 실행계획이 만료되거나, 새로운 실행 계획을 생성해야 하기 전까지 캐시에서 재사용되기 때문에 성능이 좋아진다.

Plan Cache

• SQL Server가 메모리를 사용하여 저장 프로시저, 임시 및 준비된 Transcat-SQL문, 트리거와 같은 개체를 저장하는 방법을 모니터링하는 카운터를 제공합니다.

• 개체의 여러 인스턴스를 동시에 모니터링 할 수 있다.

동적 쿼리가 Plan Cache에 캐시되기 위한 조건

1. EXEC()를 사용하는 경우
• 동일한 동적 SQL 문자열이 실행되더라도 매개변수의 값이 달라질 때마다 매번 새로운 실행 계획을 생성
• 동적 쿼리는 매번 새롭게 컴파일되므로 실행 계획이 캐시에 저장되지 않는다.
• 성능이 이슈

2. EXEC sp_executesql을 사용하는 경우
• 매번 동일한 쿼리 문자열이라도 일정한 매개 변수화된 형식으로 실행할 수 있다.
• SQL Server는 일정한 형식의 매개 변수를 사용한 쿼리에 대한 실행 계획을 캐시에 저장한다.
• 즉, 매개변수의 값이 달라지더라도 실행 계획이 캐시에 저장되기 때문에 동일한 쿼리 템플릿에 대한 재사용이 가능하다.

EXEC sp_executesql 사용 시 주의할 점

1. Database Context
다른 데이터베이스로 스위치하거나 다른 데이터베이스의 객체에 엑세스할 수 없다는 것을 의미
ex) DB1에서 sp_executesql을 사용하여 DB2의 테이블이나 뷰에 직접 엑세스 불가

2. Access to Local Variables
동적 SQL 내부에서 사용된 로컬 변수에 직접 액세스 할 수 없다.
로컬 변수를 사용할 때에는 매개 변수화된 쿼리를 통해 값을 전달해야한다.

DECLARE @LocalVariable INT = 123
DECLARE @DynamicSQL NVARCHAR(MAX)
SET @DynamicSQL = N'SELECT @Parameter'
EXEC sp_executesql @DynamicSQL, N'@Parameter INT', @LocalVariable