어제보다 더 나은 개발자 되기

SQL 처리 과정과 I/O 

1. SQL 파싱과 최적화 

2. SQL 공유 및 재사용

3. 데이터저장 구조 및 I/O 매커니즘

1. SQL 파싱과 최적화 

SQL은 Structured Query Language의 줄임말이다. 말 그대로 구조적 질의 언어다. 

SQL은 기본적으로 구조적이고 집합적이고 선언적인 질의 언어다. 원하는 결과집합을 구조적, 집합적으로 선언하지만 그 겨롸집합을 만드는 과정은 절차적이다 그렇기 때문에 프로시저가 필요하며 그 프로시저를 만들어 내는 DBMS 내부엔진이 바로 옵티마이저이다. 이 옵티마이저가 프로그래밍을 대신해 주는 셈이다. 

DBMS 내부에서 프로시저를 작성하고 컴파일해서 실행 가능한 상태로 만드는 전 과정을 SQL 최적화라고 한다. 

SQL 최적화 

SQL 파싱 : 사용자로 부터 SQL을 전달 받으면 가장 먼저 SQL 파서가 파싱을 진행한다. 

   파싱트리를 생성하고 문법검사 -> 의미검사(?) -> 권한검사와 같은 것을 한다 

SQL  최적화 : 옵티마이저가 역할을 맡고 미리 수집한 시스템 및 오브젝트 통계정보를 바탕으로 다양한 실행경로를 생성해서 비교한 후 가장 효율적인 

                  하나를 선택한다. 데이터베이스의 성능을 결정하는 가장 핵심적인 엔진이다.

로우 소스 생성 : 로우(Row??) 소스 생성기가 그 역할을 맡고 실제 실행 가능한 코드 또는 프로시저 형태로 포맷팅하는 단계이다.

SQL 옵티마이저 

SQL 옵티마이저는 사용자가 원하는 작업을 가장 효율적으로 수행할 수 있는 최적의 데이터엑세스 경로를 선택해 주는 DBMS 핵심 엔진이다. 

1. 사용자로부터 전달받은 쿼리를 수행하는 데 후보군이 될만한 실행 계획들을 찾아낸다. 

2. 데이터 딕셔너리에 미리 수집해 둔 오브젝트 통계 및 시스템 통계정보를 이용해 각 실행계획의 예상비용을 산정한다. 

3. 최저 비용을 나타내는 실행 계획을 선택한다. 

옵티마이저는 네비게이션에서 길을 찾아주는 역할을 하며 비용을 근거로 인덱스 사용 유무와 쿼리 수행방법을 결정한다. 

SQL을 최적화 할 때 옵티마이저가 사용하는 정보 

- 테이블, 컬럼, 인덱스 구조에 관한 정보

- 오브젝트 통계 : 테이블 통계, 인덱스 통계, 히스토그램을 포함한 컬럼 통계

- 시스템 통계 : CPU 속도 Single Block I/O 속도, Multi Block I/O 속도 

- 옵티마이저 관련 파라미터 

2. SQL 공유 및 재사용

라이브러리 캐시 : 내부 프로시저를 반복 재사용할 수 있도록 캐싱해 두는 메모리 공간 

SGA(System Global Area) : 서버 프로세스와 백그라운드 프로세스가 공통으로 엑세스하는 데이터와 제어 구조를 캐싱하는 메모리 공간이다. 

소프트 파싱 : SQL을 캐시에서 찾아 곧바로 실행단계로 넘어가는 것

하드 파싱 : 캐시에서 찾는 데 실패해서 최적화 및 로우소스생성 단계까지 모두 거치는 것 

라이브러리 캐시가 필요한 이유 

데이터 베이스에서 이루어지는 처리과정의 대부분은 I/O 과정에 집중되는 반면 하드 파싱은 CPU를 많이 소비하는 몇 안되는 작업 중 하나 

이렇게 어렵게 작업을 거처 생성한 내부 프로시저를 한 번만 사용하고 버린다면 비효율적이다. 

이름없는 SQL 문제 

사용자 정의 함수/프로시저, 트리거, 패키지 등은 생성할 때부터 이름을 갖는다. 컴파일한 상태로 딕셔너리에 저장되며 실행할 때 라이브러리 캐시에 적재함으로써 여러 사용자가 공유하면서 재사용한다. 

DBMS에서 수행되는 SQL이 모두 완성된 SQL이 아니며 개발과정에서 수시로 변경이 일어날 수 있다 일회성(Ad-hoc) SQL도 있고 무효화된 SQL 까지 모두 저장해서 쓰려면 많은 공간이 필요하게 된다. 그만큼 SQL을 찾는 속도도 느려지므로 오라클, SQL Server 같은 DBMS가 SQL을 영구 저장하지 않는 쪽을 택했다. 

프로시저에서 파라미터를 전달 받아 실행하는 경우가 추천되며 이런 경우 바인드 변수만 변경되며 프로시저 하나를 공유하면서 재사용할 수 있다. 

3. 데이터저장 구조 및 I/O 매커니즘

프로세스의 생명주기 

프로세스는 실행 중인 프로그램이며 생성 이후 종료 전까지 준비와 실행 대기를 반복한다. 실행 중인 프로세스는 interrupt에 의해 수시로 실행 준비상태로 전환 했다가 다시 실행 상태로 전환한다 

열심히 일하던 프로세스도 디스크에서 데이터를 읽어야 할 땐 CPU를 OS에 반환하고 잠시 waiting 상태에서 I/O가 완료되기를 기다린다. 이렇게 기다리는 시간이 생기게 되면 SQL이 느려지게 된다. 이 때를 디스크 I/O 라고 하고 이것은 SQL의 성능에 큰 영향을 끼친다. 

데이터베이스의 저장 구조 

가장 큰 개념으로 테이블 스페이스가 있으며 테이블 스페이스는 여러개의 데이터파일로 구성된다. 또 테이블 스페이스는 세그먼트들을 포함한다. 세그먼트는 테이블, 인덱스 처럼 데이터 저장공간이 필요한 오브젝트다. 테이블, 인덱스를 생성할 때 데이터를 어떤 테이블스페이스에 저장할지를 지정한다. 

세그먼트는 여러 익스텐트들로 구성된다. 테이블도 하나의 세그먼트이며 인덱스(테이블??)도 하나의 세그먼트이다. 

익스텐트는 연속된 블록들의 집합이기도 하다. 사용자가 입력한 레코드를 실제로 저장하는 공간은 데이터 블록이다. SQL 서버는 블록대신 페이지 라는 용어를 사용한다. 

블록(페이지) : 데이터를 읽고 쓰는 단위

익스텐트 : 공간을 확장하는 단위, 연속된 블록 집합

세그먼트 : 데이터 저장공간이 필요한 오브젝트(테이블, 인덱스, 파티션, LOB등)

테이블스페이스 : 세그먼트를 담는 콘테이너

데이터파일 : 디스크 상의 물리적인 OS 파일

블록이 DBMS가 데이터를 읽고 쓰는 단위다. 

??? 데이터 I/O 단위가 블록이므로 특정 레코드 하나를 읽고 싶어도 해당 블록을 통째로 읽는다. 

테이블 뿐만 아니라 인덱스도 블록 단위로 데이터를 읽고 쓴다

시퀀셜 액세스 : 논리적 또는 물리적으로 연결된 순서에 따라 차례대로 블록을 읽는 방식. 인덱스 리프 블록은 앞뒤를 가리키는 주소값을 통해 논리적으로

                    서로 연결되어 있다. 이 주소 값에 따라 앞 또는 뒤로 순차적으로 스캔하는 방식이다. 

   각 익스텐트의 첫 번째 블록뒤에 연속해서 저장된 블록을 순서대로 읽으면 그것이 Full Table Scan 이다. 

랜던 액세스 : 논리적, 물리적인 순서를 따르지 않고 레코드 하나를 읽기 위해 한 블록씩 접근하는 방식

DB 버퍼 캐시 : 앞의 라이브러리 캐시가 프로시저 실행 계획과 같은 코드 캐시라고 한다면 DB 버퍼 캐시는 데이터 캐시라고 할 수 있다. 

                   디스크에서 어렵게 읽은 데이터 블록을 캐싱해 둠으로써 같은 블록에 대한 반복적인 I/O Call을 줄이는데 목적이 있다. 

                   서버 프로세스와 데이터 파일 사이에 버퍼캐시가 있으므로 데이터 블록을 읽을 땐 항상 버퍼캐시부터 탐색한다. 

논리적 I/O : SQL을 처리하는 과정에 메모리 버퍼캐시에서 발생한 총 블록 I/O를 말함 

                논리적 I/O와 메모리 I/O가 같은 으미는 아니지만 같다고 생각해도 무방하다. 

                아무리 여러번 실행해도 매번 읽는 블록 수는 같으며 SQL을 수생하면서 읽은 총 블록 I/O가 논리적 I/O이다. 

물리적 I/O : 디스크에서 발생한 총 블록 I/O를 말한다. DB 버퍼캐시에서 블록을 찾지 못해 디스크에서 읽은 블록 I/O가 물리적 I/O이다. 

버퍼캐시 히트율 (BCHR)

- 온라인 트랜잭션을 주로 처리하는 애플리케이션이라면 시스템 레벨에서 평균 99% 히트율을 달성해야 한다. 

- 실제 SQL 성능을 향상하려면 물리적 I/O(분자)가 아닌 논리적 I/O(분모)를 줄여야 한다.

- SQL을 튜닝해서 읽는 총 블록 개수를 줄이면 되며 논리적 I/O는 항상 일정하게 발생하지는 통제가능한 내생변수다. 

- 논리적 I/O를 줄임으로써 물리적 I/O를 줄이는 것이 곧 SQL 튜닝이다. 

버퍼캐시 히트율의 주의할 점 

- BCHR이 SQL의 성능을 좌우하지만 BCHR이 높다고 해서 효율적인 SQL을 의미하지는 않는다. 같은 블록을 비효율적으로 반복해서 읽는 것도 BCHR이 높아진다. 

Single Block I/O

- 한 번에 한 블록씩 요청해서 메모리에 적재하는 방식 

- 인덱스를 이용할 때는 기본적으로 인덱스와 테이블 블록모 Single Block I/O 방식 사용 

- 인덱스 루트 블록을 읽을 때 

- 인덱스 루트 블록에서 얻은 주소 정보로 브랜치 블록을 읽을 때 

- 인덱스 브랜치 블록에서 얻은 주소 정보로 리프 블록을 읽을 때 

- 인덱스 리프 블록에서 얻은 정보로 테이블 블록을 읽을 때 

Multi Block I/O 

- 한 번에 여러 블록씩 요청해서 메모리에 적재하는 방식 

- 많은 데이터 블록을 읽을 때 효율적이며 인덱스를 이용하지 않고 테이블 전체를 스캔할 때 이 방식 사용 

Table Full Scan

- 시퀀설 엑세스와 Multiblock I/O 방식으로 디스크 블록을 읽어 들인다. 

- 한 블록에 속한 레코드를 한번에 읽어 들이고 캐시에서 못 찾으면 한번의 수면(I/O Call)을 통해 인접한 수십~수백 개 블록을 한꺼번에 I/O하는 방식 

Index Range Scan 

- 큰 테이블에서 데이터를 소량 검색할 때는 인덱스를 꼭 이용해야 한다.  

- 랜덤 액세스와 Single Block I/O 방식으로 디스크 블록을 읽는다.

- 캐시에서 블록을 못 찾으면 레코드 하나를 읽기 위해 매번 프로세스 waiting에서 대기하게 된다. 하나의 레코드를 읽기 위해 블록을 계속 읽게되는 것은 성능 저하의 우려가 있다. 

- 많은 데이터를 읽을 때는 Table Full Scan이 유리하다.  

성능문제는 인덱스로 해결하지 말고 데이터 결과 집합이 많으면 Full Table Scan이 Index Scan 보다 유리하다. 

캐시 탐색 매커니즘 

버퍼 캐시 탐색 과정을 거치는 경우 

- 인덱스 루트 블록을 읽을 때 

- 인덱스 루트 블록에서 얻은 주소 정보로 브랜치 블록을 읽을 때 

- 인덱스 브랜치 블록에서 얻은 주소 정보로 리프 블록을 읽을 때 

- 인덱스 리프 블록에서 얻은 주소 정보로 테이블 블록을 읽을 때

- 테이블 블록을 Full Scan 할 때 

내부구조 

- 버퍼캐시를 해시 구조로 관리한다. 

메모리 공유자원에 대한 액세스 직렬화 

- 버퍼블록은 공유자원이다. 즉 모두에서 권한이 있기 때문에 누구나 접근할 수 있다. 문제는 두 개 이상의 프로세스가 동시에 접근하려고 할 때 발생한다. 

- 공유자원이라도 내부에서는 한 프로세스식 접근하도록 직렬화 매커니즘이 필요하다. 이런 직렬화를 실현하기 위해 DBMS는 동시성 제어를 위한 Lock을 제공한다. 

- 프로세스가 줄을 서서 기다리는 것을 가능하도록 지워너하는 매커니즘이 래치(Latch)이다. 

- 오라클은 버퍼 헤더에 버퍼 Lock을 설정함으로써 버퍼 블록 자체에 대한 직렬화 문제를 해결하고 있다 (SQL Server 는??) 

용어 정리

DB의 조인

-       논리적: 내부조인(자연조인, Inner), 외부조인(Outer), 크로스 조인(크로스 프로덕트)

-       물리적: Nested Roof, Sort Merge, Hash 조인(그 밖에 더 있으나 여기까지만)

조인 수행 원리

PPT로 (애니메이션 가능)

옵티마이저

-       옵티마이저를 이해하는 것이 튜닝의 시작이고 옵티마이저를 제어하는 것이 튜닝의 기본이다.

-       옵티마이저에 의해 발생하는 조인 3가지: NL조인 , Sort Merge 조인, Hash 조인 이 있다 이것은 물리적인 조인이기 때문에 개발자가 직접 사용하게 되는 조인은 아니다

-       개발자가 직접 제어하는 조인은 내부조인이나 외부조인과 같은 논리적인 조인을 선언해서 만든다.

n  Ludy의 경우 Merge Into 구문도 조인이 있다  

-       즉 이런 논리적인 조인을 옵티마이저라는 녀석이 DBMS 내부에서 물리적인 조인으로 표현하고 만드는 것이다

-       옵티마이저는 사용자가 질의한 SQL문에 대해 최적의 실행 방법을 결정하는 역할을 수행

-       옵티마이저가 정한 최적의 실행 방법을 실행 계획이라고 함

-       다양한 실행 방법들 중에서 최적의 실행 방법을 결정하는 것이 옵티마이저의 역할

-       실제로 SQL문을 처리해 보지 않은 상태에서 결정해야 하는 어려움이 있다

n  DBMS 별로 옵티마이저의 성능을 비교해 볼 수 있는 부분이라고 볼 수 있겠다

-       보통 CBO(비용기반 옵티마이저를) 생각하면 된다

-       최적의 실행방법 결정이라는 것은 어떤 방법으로 처리한 것이 최소 일량(비용)으로 동일한 일을 처리할 수 있을 지 결정하는 것이다

-       즉 옵티마이저 최적의 실행 방법을 결정하는 것을 개발자는 도와주어야 한다

-       단순히 조인의 원리를 아는 것보다는 이 옵티마이저가 어떻게 물리적인 조인으로 변경시키는지를 알아야 한다

카디널리티

-       Cardinality: 사전적 의미로는 집합원의 개수

-       카디널리티가 낮은 경우에서 속성의 예를 들면 성별, 부서, 지역이 있다.

n  성별의 경우 남자, 여자 두 가지 경우만 가능하므로 매우 낮다고 할 수 있다

n  주민번호, 사원번호와 같은 경우 조직원이 많을수록 카디널리티가 높다

-       간단히 생각하면 한 개를 뽑는 뽑기를 했을 때 그 뽑는 모집단의 수가 많으면 높다고 할 수 있다

-       데이터베이스로 한정해보면 테이블에서 Primary Key에 해당 되는 경우의 수 라고도 볼 수 있겠다

카디널리티 설명 예제

PPT로

예를 들면 학급 엔터티로 학년의 성적표를 만든다면 반 번호가 기본키가 되면 그 학급의 학생의 수가 카디널리티가 될 수 있다 (학급(1) : 학생(N))

다른형태로는 특정 쿼리문을 실행시켜서 나오는 결과 값(Row)를 카디널리티라고 한다

즉 학교라는 개념에서는 학급이 카디널리티일 수 도 있고 학급이라는 개념에서는 학생이 카디널리티일 수도 있다

예를 들면 사원테이블의 전체 레코드 수가 1000개일 때

WHERE 부서 = ‘인사팀’ 이면 그 중 인사팀 사원이 10명이면 선택도는 10/1000 = 0.01이 됩니다. 즉 전체 rows * 선택도 = 결과수, 즉 카디널리티 가 결과 수라면 10이 카티널리티 입니다.

카디널리티를 이해해야 하는 이유

DB의 옵티마이저에서 조인을 맺어주는 항목으로 인덱스를 사용하게 됩니다. 물론 없으면 따로 사용하지 않고 Full Scan 이 되겠지요. 중요한 건 현재 접근하는 컬럼을 조건으로 접근할 때 얼마나 많이 걸러지느냐 입니다.

선택도가 확률이라고 하면 해당 카디널리티 즉 경우의 수는 전체 데이터의 개수에 영향을 받게 됩니다. 데이터가 많을 때는 해당 조건으로 얼마나 많이 걸러지는 지 즉 중복이 최소한일수록 해당 조건으로 많이 걸러지게 됩니다. 중복된 data를 검색 즉 중복이기 때문에 해당 data 전체를 Scan 해야 하기 때문에 원하는 하나를 찾는 경우에 범위를 좁혀가는 검색에서는 시간이 더 걸리게 됩니다.

순차적인 선형 검색일 경우 순서대로 전체를 보게 되지만 범위를 좁혀가는 이진검색이 성능이 더 좋다고 합니다.

이렇게 집합에서 원소가 어떻게 분포되었냐에 따라 스캔속도가 차이가 나므로 옵티마이저는 인덱스의 설계가 어떻게 되었냐에 따라 영향을 받게 됩니다. 개발자는 물리적인 조인에 대해 직접 관여는 하지 않지만 인덱스 설정에 따라 영향은 줄 수 있다는 말입니다.

일반적으로 옵티마이저는 여러 인덱스가 있을 때 선택도가 낮은 즉 중복발생확률이 낮은 인덱스를 사용합니다.

인덱스

인덱스는 검색을 빨리 하기 위한 용도로 사용합니다.

인덱스를 여러 개 사용할 때는 넌클러스터 인덱스를 사용합니다.

인덱스를 여러 개 사용할수록 옵티마이저가 잘못된 인덱스를 선택할 확률이 높아집니다.

인텍스를 하나의 컬럼에만 걸어야 한다면 카디널리티가 가장 높은 경우 즉 중복을 제거 했을 때 집합 원소의 개수가 가장 많은 속성을 지정합니다.

예를 들면 성별을 인덱스로 지정하면 카디널리티가 2밖에 되지 않으므로 인덱스로 걸러지는(Range로 걸러진다고 할 때) 경우가 50% 밖에 걸리지지가 않습니다.

100 개 중에 유일한 하나일 경우라면 1%이나 99%가 걸리지는 경우이겠습니다.

인덱스로 걸러낸다는 개념은 무엇일까요???

그리고 우리가 사용하는 테이블은 기본적으로 어떻게 설계하길래 성능이슈가 있게 된 것일 까요??

어려워 지는데 하나 확실히 아는 것은 사원번호나 주민번호와 같은 속성의 값을 대표값으로 할 수 있는 것은 현재 구성원을 구성하는 큰 집합에서 유일한 속성으로 취급할 수 있는 것입니다. 즉 10이하의 자연수의 집합일 경우라면 {1,2,3,4,5,6,7,8,9,10} 원하는 숫자를 찾을 때 중복이 없으므로 유일한 값 하나를 찾습니다. 이렇게 유일한 속성의 인덱스는 내가 찾는 요소 하나로 최대한으로 나머지 원소를 거를 수 있습니다. 이런 개념에서 카디널리티를 이해하고 넘어가고자 합니다.

위의 개념에서 인덱스의 유일성이 필요에 대해서 이해가 되었으면(모든 인덱스가 유일성을 가지는 것은 아닙니다)

낫널에 대해서 이야기 해 볼까요? 기본적으로 데이터베이스의 엔터티 혹은 릴레이션 혹은 테이블이라고 하는 개체는 해당 컬럼이 동일 선상에서 이미 다른 컬럼들과 원자적인 구조로 만들어져야 합니다. 즉 본인이 변경되었다고 다른 컬럼의 값도 변경이 된다는 것은 정규적이지 않은 관계입니다. 그리고 data의 중복을 최소화 한다는 것은 하나의 Row, 튜플 이런 값이 다른 Row, 튜플과 구별되는 대표값이 있어야 한다는 것입니다. 그렇다면 대표값이 널이 되어버리면 일단 다른 Row들과 구별되거나 해당 Row를 대표할 수 없습니다.

유일한 Row이고 그 값이 비어있지 않으며 속성들끼리 원자적인 테이블에서 대표값만 가지고 그 Row, 튜플을 찾거나 맵핑 할 수 있습니다. 대표값만 가지고 데이터를 찾는다.. 인덱스가 그런 용도의 느낌입니다.

확률

확률이 예를 들기 좋은 것 같습니다.

카디널리티라는 주머니를 만들고 거기에 빨주노초파남보 의 무지개 색 공을 집어넣으면 이 경우 선택도는 1/7 입니다.

그러면 빨간공 3개 노란공 2개 보라색공 4개를 넣으면 이 경우 선택도는 몇 일 까요?? 빨간공의 확률은 1/3 노란공은 2/9 보라색공은 4/9 입니다 이건 단순히 시행(반복)이라는 시도와 맞물려서 말할 수 있는 확률 인 것이고

공이라는 개념으로 접근하면 3개의 색만을 가지므로 그 공이라는 릴레이션은 엔터티는 테이블의 색깔이라는 속성은 선택도가 1/3 즉 0.33 입니다.

그리고 그 빨주노초파남보 이면서 각각 1~7까지의 숫자가 매겨저 있는 상황이라면

빨 주 노 초 파 남 보

1  2  3  4  5  6  7

이런 상황에서 숫자와 색깔이라는 속성의 선택도는 어떻게 될 까요??

제가 확률을 얘기 했으니 숫자로 하면 1/7 색으로 하면 1/7입니다. 단 이것은 전에 말했듯이 시행에 의해 획득할 수 있는 한번의 공입니다 다만 유일성이 확보가 되니 선택도는 1/전체 이고 뭘로하든 카디널리티는 1 입니다.

근데 예제를 바꾸면

빨 주 노 초 파 남 보

1  2  2  2  3  4  4

이런 상황에서는 선택도가 변하겠죠? 색으로는 1/7이고 숫자로는 1/4 입니다.

여기서 어떤 속성을 기준으로 하느냐에 따라 확률이 달라졌고 그에 따라 선택도(카디널리티)도 달라졌습니다.

여기서 속성을 색과 숫자로 나눈 이유는 인덱스를 어디다 거는 기준이 뭐냐?? 라는 말을 하고 싶어서 입니다.

최소한의 기준은 앞에서 선택도가 낮고 카디널리티가 높은 경우를 말했습니다. 그리고 여기서 선택도라는 확률에서 분모 즉 유일성이 보장되는 모집단의 개수입니다. 경우의 수라고도 보이겠네요

이게 더 큰 경우로 색깔이 나오기 때문에 공이라는 릴레이션 엔터티 테이블은 인덱스를 색깔로 잡아야 숫자보다 효율이 좋습니다.

즉 숫자로 해야 걸러지는 케이스가 많아진다는 말입니다.

확률 공식을 보면

 즉 카디널리티는 일정한 기댓값 즉 평균의 속성을 가지고 있습니다.

위의 내용은 확률 분포 내용으로 기댓값이란

사실 위의 내용을 고민하지 않아도 인덱스를 만들거나 하는 것에는 크게 상관이 없습니다. 조인을 만드는 것도 또 옵티마이저에게 성능판단을 위임하는 것도 개발자가 DBMS를 이해해야 하나? 라는 의문을 가지면 딱히 할말은 없지만 옵티마이저는 분명 만능이 아니고 최적화에 실패할 수 있습니다. 그리고 인덱스를 가지고 검색을 할 때 동작하는 거나 원리는 DBMS마다 조금씩 다를 수 있습니다. 모든게 같으면 DB가격이나 성능이 같아야 할 테니까요 위에 내용에 대해 궁금하게 접근한 이유는 이미 누구나 알고 있는 인덱스의 동작원리보다는 어떤 녀석이 인덱스가 되어야 하지?? 넌 어떤 점이 좋으니 인덱스가 되고 넌 어떤 점이 안좋으니 인덱스가 될 수 없다 하는 어떤 녀석이 인덱스로 고민하고 또 그것을 활용하는 옵티마이저는 이 인덱스의 어떤 점에 끌려 자기만의 인공지능적인 최적화를 수행 할 수 있을까? 라는 부분에서의 물음입니다.

즉 가능하면 내부적으로 개발자가 직접 인덱스를 만든다면 적어도 그 테이블이나 데이터를 보고 그 기준이 되는 부분을 찾아보고 싶기 때문입니다. 물론 옵티마이저에서 제가 생각한 선택도나 카디널리티나 기댓값이나 이런 부분에 영향을 미쳐서 판단한다고 증명된 것은 없습니다. 다만 저의 위키의 내용은 제가 인덱스를 고려할 컬럼을 정한다면 (PK 외적으로) 저런 수치적인 부분이나 그 속성의 고유한 특성을 고려해서 해당 컬럼에 인덱스를 걸고 그 인덱스를 옵티마이저가 제가 원하는 기준(작은 수량은 NL조인, 대량의 데이터는 Sort Merge 조인, 메모리 성능도 좋고 양도 많으면 Hash 조인으로 동작하기를 희망하기 때문에 인덱스를 재물로 삼았습니다. )

인덱스가 아니라면 일단 옵티마이저에게 어떻게 말을 걸고 접근할지 막연하기 때문에 인덱스로 그리고 그것이 조건이 되었을 때 옵티마이저 안녕? 이것을 하려고 합니다.

위의 내용까지가 서론이고 이제 본론에서는 실제 데이터의 수량을 달리하면서 실행 계획에서 어떻게 성능적으로 달라진는지 확인해 보겠습니다.

조인을 깊이 이해하는 것은 어렵고 동일한 결과를 얻을 수 있는 조인 방법은 많이 있지만 주어진 환경에 따라 효율성이 차이가 난다

즉 어떻게 사용하느냐에 따라 결과가 천지만별이라는 것이다.

실행계획을 이해했고 조인의 기본적인 처리 절차를 파악해둠으로서 나중에 조인을 깊이 이해 하기 위한 사전준비일 뿐이다…(책에서)

1.         내포조인

2.         정렬병합

3.         해시조인

4.         세미조인

5.         카티전 조인

6.         아우터 조인

7.         인덱스 조인

엔코아 책에서는 저렇게 만 다룬다고 하니 더 있나보다…

간단히 말하면 일단..

기본적으로 조인의 원리를 생각하려면 SQL 즉 코드로 인해 조인을 만들 것이고 그 조인을 구성하는 Table 즉 이 대상으로부터 data를 취득할 때 DBMS가 어떻게 데이터를 취합하고 어떻게 데이터를 저장하고 그 저장된 데이터에서 원하는 조건에 맞게 가져오는 지 궁금증을 가지는 데에서부터 시작한다.

즉 내부조인이나 외부조인 같은 방법을 사용해서 쿼리(질의)를 하고 그 결과는 DBMS에서 반환되며 디스크 or 메모리 상에서 어떠한 연산??(알고리즘) 에 의해 빠른 속도 or 느린 속도로 결과를 만들어주게 된다.

여기서 어떠한 연산?? 이 부분을 아는 것이 일단 지금 단계서의 목표이다.

기본적으로 생각 해야 하는 것은 적어도 우리는 최대한의 정규테이블로 구성된 DB에서 작업이 일어날 것이며 그렇기 때문에 정규테이블간의 관계에 따라 필요한 데이터를 얻기 위해 조인을 하게 되고

그 때 그 테이블을 필터하는 범위나 컬럼의 선택도나 사용하고 있는 인덱스에 의해 쿼리의 성능이 정해진다

자 그렇다면 1.내포조인 2. 정렬병합 3. 해시조인에 대해 간략히 언급하고 넘어간다

일단 기본은 내포조인이다. 즉 중첩된 루프를 돌면서 Outer table에서 Inner Table로 조건이 일치되는 data를 찾는 것이 가장 보편적으로 사용되는 조인이다

여기서 기본적으로 사용되는 자료구조는 B+Tree 이며 Root 부터 Branch를 지나 Leaf를 찾는 것이 알고리즘의 핵심이다

이 알고리즘을 사용할 때 정렬되냐 정렬이 안되냐에 따른 차이는 있지만 기본적으로 NL 조인은 랜덤엑세스를 해서 찾아간다 그래서 (Index)Table Scan을 하면 안 되고 Index Seek or Range Scan을 해서 범위로 접근해야 기본적인 성능이 좋다고 본다

그 한번의 랜덤엑세스가 중첩된 Roof를 돌면서 반복되므로 Scan이 아닌 Seek or Range를 가지는 부분이 성능의 핵심이다.

Sort Merge의 경우는 위의 Case에 정렬된 자료구조를 가지고 있다고 생각하면 된다 즉 내부적으로 정렬 프로세스를 거치니 초기 작업은 오래 걸려도 실제 찾는 프로세스는 적게 걸릴 수 있다 NL의 단점을 극복하기 위해 생겼다고 한다

Hash 의 경우는 Sort Merge가 정렬프로세스를 가지고 있다면 단점을 극복하려고 생겼다고 한다 즉 이 부분은 알고리즘적으로 접근해야 하는데 O(logn)도 충분히 훌륭한 것이지만 해시 맵을 만들면 해시 알고리즘은 O(1)이기 때문에 정렬을 하지않아고 검색이 가능하다고 한다 단 해시결과가 유일한 지 검증하는 부분이 추가되므로 꼭 O(1)은 아니라고 한다 어쨌든 알고리즘 적으로 해시 맵을 만들어서 검색하는 것이 더 탁월해 사용하는 조인방법이다.

즉 조인은 기본적으로 복수의 대상간 중첩된 루프를 돌며 조건이 맞는 경우를 찾는 것이고 NL의 경우 B+Tree 구조상에 랜덤엑세스의 부담 때문에 그 부담을 줄이고자 정렬된 상태에서 엑세스를 하는 Sort Merge 조인이 생겨났고 정렬을 미리 해야 하는 전처리에 대한 부담으로 아예 유일한 해시값을 생성해서 그 해시를 이용해 조인을 하는 것이 해시 조인이다… 라고 까지 생각하고 더 붙이자.

자 여기서 중요한 것은 인덱스 설계를 잘하는 것이다 인덱스에 따라 옵티마이저가 보다 좋은 엑세스경로를 찾을 수있게하는 근본적인 방법이다.

인덱스는 현재 발생할 수 있는 상황과 데이터의 분포도나 결합도 선택도등을 고려해서 설계되어야 한다

아래 내용은 트랜잭션 2탄이다. 

다만 코드로 나타내보고 싶은에 일단 글로 남겨본다 구현의 문제는 좀 더 나중에 고민 

관련된 책은 계속 읽어 봐야 겠다 

지금 총 5권이 있는데 난이도로는 

초급

Head First SQL 

중급 이상

SQL Server(개발편)

SQL 전문가 가이드 

대용량 데이터베이스 솔루션 Vol1(엔코아)

웹 프로그래머를 위한 데이터베이스를 지탱하는 기술 

이렇게 다 봐야 할 것 같다 

지금 정리할 내용은 중첩 트랜잭션 즉 트랜잭션이 이중 For문 이상 처럼 중첩되서 시작될 때 과연 트랜잭션 개수나 명시적 혹은 묵시적 이나 Auto Commit이면 어떻게 되는 것인가에 대한 고민이다. 

일단 몇가지 주의?? 사항이 있다 

1. 트랜잭션 선언 방법 

명시적

묵시적(오라클 기본)

Auto(SQL Server 기본)

2. 트랜잭션의 관리주체

로컬

분산(or Global)

3 중첩 영역 (트랜잭션 허용범위?)

Code(.Net)

Stored Procedure(DBMS)

총 3가지 요소로 나누고 사용하는 방법을 생각해보자 

현재 내가 다루는 부분에서는 어플리케이션 계층 즉 Biz 영역을 가지고 있다 그래서 기본은 Biz에서 트랜잭션을 처리하는 것이 기본이다. 

이걸 가지고 가설(또는 주장)을 세우자 

가설 : app에 Biz영역이 있으면 어플리케이션 계층에서 트랜잭션 처리를 한다!

라는 가설을 세우고 그것에 대한 타당성 검증을 하자 

그러면 고려되는 부분이 Code에서 트랜잭션 처리를 해야 한다는 것이다. 이것에 대한 기술은 .Net의 TrasactionScope를 사용하거나 IDbTransaction을 사용해서 트랜잭션 단위를 묶어서 처리할 수 있다 

기술적으로는 문제가 없다 

그렇다면 Biz영역을 왜 만들었나에 대한 질문을 할 수 있는데 나의 답은

DAO(Database Access Object)를 통해 DB를 변경하기 전에 로직에 따라 데이터를 수정하고 원하는 타입(예를 들면 Table Value Parameter)와 같이 변경해서 사용하거나 필요에 의한 가공을 하고 여러 Table(Entity)애 접근 할 수 있으니 그건 업무에 따라 달라지는 부분을 묶어서 업무 로직을 트랜잭션으로 처리하기 위해서 라고 길게 말하고 DB에 반영하기 이전에 데이터나 모델의 수정을 한 곳에서 일괄적인 작업단위로 분리하기 위해서 라고 간단히 말 할 수 있다 

그러면 SP에서 트랜잭션이 걸리면 어떻게 할거냐?? 라는 반론이 생길 수 있는데 

이때 중첩 트랜잭션이 생기고 그것에 따라 트랜잭션 개수가 복수로 생길 수 있다 

즉 Code에서 Begin Tran을 하면 하나의 트랜잭션이지만 SP에서도 Begin Tran을 하면 2개이상의 트랜잭션이 생긴다는 것이다. 

이렇게 되면 2번의 Commit이나 Rollback에 의한 초기화가 되야 짝이 맞아진다 

Scope가 중요해지는 부분이 생기는데 이렇게 되면 Code나 SP에서 둘 다 관리포인트가 생기니 일련의 과정을 모두 확인해봐야 하는 번거로움이 생긴다 

이때 유효하게 사용할 수 있는 부분이 TM이다 즉 트랜잭션 매니저가 있어서 Commit과 Rollback을 제어해 줄 수 있고 관리를 위임할 수 있으면 개발자 입장에서는 편해진다. 

그러니 Code에서 트랜잭션 처리를 할거면 TM역할을 하는 TransactionScope나 IDbTransaction을 이용해서 Work 단위를 만들면 중첩된 부분을 커버해주지는 않더라도 한 곳에서 트랜잭션을 시작하고 끝낼 수 있다 

한 곳에서 트랜잭션을 시작하고 끝낸다는 것에 대한 고찰 

사실 답은 억지로 끌어낸 감이 없잖아 있지만 중첩트랜잭션이 필요한가?? 에 대한 경우가 일관되게 적용하기가 아직 경험이 없다 그렇게 되니 일단 한곳에서 집중하고 필요한 경우 SP에서 중첩되게 사용할 수 있다고 말하고 싶다 

트랜잭션은 성능과 연관될 수 있다 트랜잭션이 길어지면 선행 처리가 길어진다는 것이고 그러면 후행처리는 그만 큼 느려지니 타임아웃에 의한 실패나 시스템 전체에 Sleep이 걸린 것 처럼 처리속도에 영향을 줄 수 있다 

그렇기 때문에 트랜잭션은 짧게 가져가되 성능감소는 최소화 하고 처리하는 Data는 무결성을 지켜주는 그런 식의 보완을 개발자가 제어해줘야 한다고 생각한다. 

간단히 의사 코드로 표현 해 보면 

TransactionScope Start 

{

Begin Tran()                // Transation Count +1 증가

Excute SP(insert Model)

Commit()

} 

Excute SP(insert Model)

{

// 아래 내용은 SP의 내용

// insert into Table (Columns) values (Model)

Insert(Model) 

}

위와 같이 하면 Transaction Count가 1인 것이고 

아래와 같이 SP에 트랜잭션을 다시 걸면

TransactionScope Start 

{

Begin Tran()                // Transation Count +1 증가

Excute SP(insert Model)

Commit()

} 

Excute SP(insert Model)

{

// 아래 내용은 SP의 내용

// Begin Tran            // Transaction Count +1 증가 

//     if(@@Transaction Count = 2)

//        insert into Table (Columns) values (Model)

// Commit Tran

Insert(Model) 

}

트랜잭션 간단 정리 

그냥 간단하게 남기려고 쓴다 

주 내용은 서비스개발실 SQL Server 책이랑 SQL 전문가 가이드 내용을 보고 남긴다. Head First SQL 보고 추가할 내용이 있으면 수정한다.  

트랜잭션이란? 

- 처리하는 작업의 논리적인 처리 단위

- 작업 단위는 그것을 구성하는 세부적인 작업의 연산(더하기 빼기 등등)들의 집합

-  데이터베이스 응용 프로그램을 트랜잭션 들의 집합으로도 볼 수있다고 한다 (기능 하나하나가 트랜잭션이라고 볼 때)

트랜잭션을 이해해야 하는 이유

- 불필요한 잠금을 피하고 잠금(Lock)을 원천적으로 피할 수 없지만 최대한으로 관리하기 위해서 

트랜잭션의 범위

- 트랜잭션 하나에 데이터 처리작업(입력, 수정, 삭제) 하나가 포함될 수도 있고 처리작업 여러개가 하나의 묶음으로 포함될 수 있다

All of Nothing 

- 말 그대로 여러데이터를 변경할 때 변경 처리가 모두 완료되거나 아니면 일부가 처리되지 못하면 이미 처리된 작업마저 취소해서 전부 되거나 전부 안되거나 그런 말이다. 

4대 속성 

- 원자성 : All of nothing

- 일관성 : 트랜잭션이 완료된 데이터는 일관되게 유지되어야 하는 것

- 격리성 : 동시에 트랜잭션이 실행될 때 서로간에 간섭하지 않는 것 

- 영속성 : 트랜잭션이 완료된 데이터베이스 변경사항이 영구적으로 저장되는 것

TCL(Transaction control language) 

- Commit : 올바르게 반경된 데이터를 데이터베이스에 반영시키는 것

- Rollback : 트랜잭션 시작 이전의 상태로 되돌리는 것

- Savepoint : 데이터 변경을 사전에 지정한 저장점 까지만 롤백하라는 것 

트랜잭션의 대상 

- UPDATE, INSERT, DELETE등 데이터를 수정하는 DLM문

- SELECT 문은 직접적인 트랜잭션의 대상이 아니지만 SELECT FOR UPDATE 등 배타적 LOCK을 요구하는 SELECT 문장은 트랜잭션의 대상이 된다고 한다

  --> 나중에 Lock 정리할 때 내용 추가

Commit이나 Rollback 이전의 데이터 상태 

- 메모리 Buffer에만 저장되어 있어서 데이터의 변경 이전 상태로 복구 가능

- 현재 사용자가 SELECT 문장으로 결과를확인 가능 (이런건 테스트 해보자)

- 다른 사용자는 현재 사용자가 수행한 명령의 결과를 볼 수 없다 (Lock?)

- 변경된 행은 잠금(Lock)이 설정되어서 다른 사용자가 변경할 수 없다. 

Commit 이후의 데이터 상태 

- 데이터에 대한 변경 사항이 데이터베디읏에 반영된다

- 이전 데이터는 영원히 잃어버린다. 

- 모든 사용자는 결과를 볼 수 있다 

- 관련된 행에 대한 잠금(Lock)이 풀리고 다른 사용자들이 행을 조작할 수 있게 된다

Auto Commit (이놈은 트랜잭션이냐... 아니냐...)

- SQL Server 기본값 

- DDL, DML 수행시 DBMS가 트랜잭션을 컨트롤하는 방식 명령어가 성공적으로 수행되면 Auto Commit이 되고 오류가 발생하면 Rollback을 자동으로 수행

암시적 트랜잭션

- Oracle 기본 값

- 트랜잭션의 시작은 DBMS가 처리하고 트랜잭션의 끝은 사용자가 명시적으로 Commit 또는 Rollback으로 처리하는 것

- 인스턴스(데이터베이스 자체 연결??)나 세션 단위(사용자 단위????)로도 설정할 수 있다????

명시적 트랜잭션

- 트랜잭션의 시작과 끝을 사용자가 명령어를 써서 지정하는 방식 

- BEGIN TRAN ~~~~ COMMIT or ROLLBACK 

Commit과 Rollback 으로 얻는 효과 

- 데이터 무결성 보장 (명령이 실행되어야만 반영되고 트랜잭션의 고립성 특성으로 데이터 접근이 제어되기 때문)

- 영구적인 변경을 하기 전에 데이터의 변경사항 확인 가능(이건 현재 사용자만 가능할 듯 다른 사용자는 안 됨)

- 논리적으로 연관된 작업을 그룹핑하여 처리 가능 (작업 내부의 세부적인 연산을 처리한다는 말과 같은 말)

트랜잭션 주의사항 

- Create, Alter, Drop, Rename, Truncate 등 DDL 문장을 실행하면 그 전후 시점에 자동으로 커밋된다

- DML 이후에 DDL이 수행되면 DDL 수행전에 자동으로 커밋된다는 말이다 

- 데이터베이스를 정상적으로 접속을 종료시키면 자동으로 트랜잭션이 커밋된다.

- 어플리케이션의 이상 종료로 데이터베이스와의 접속이 단절되면 트랜잭션이 자동으로 롤백된다 (진짜????? connection이 어떻게 관리되는데???)

트랜잭션의 개수 

- 트랜잭션 카운트라는 것이 있다 MS SQL에서는 @@TRANCOUNT 라는 것이 있어서 현재 BEGIN TRAN이 몇개나 있는지 알 수있다 

예를들면 중첩 트랜잭션을 보면 이해가 쉽다 

BEGIN TRY

             BEGIN TRAN

                           SELECT @@TRANCOUNT  -- 1

                           BEGIN TRAN

                                        SELECT @@TRANCOUNT -- 2

                                        SELECT TOP 1 * FROM sys.sysobjects

                           ROLLBACK TRAN

                           SELECT @@TRANCOUNT -- 0

             COMMIT TRAN  -- error

             SELECT @@TRANCOUNT -- 0

END TRY

BEGIN CATCH 

END CATCH

출처: http://ddoung2.tistory.com/178 [DDoung2]

뭐 저렇다는 것인데 저건 예시고 실제로는 .NET의 C# 코드에서 한번 DBMS가 실행하는 SP에서 한번이상 이런 식으로 BEGIN TRAN을 할 수 있다.

아래 훈스닷넷 커뮤니티에 예시가 잘되어 있는데 

http://ucclight.hoons.net/Board/qacshap/Content/97132

여기서 보면 C# 코드에서는 TransactionScope로 트랜잭션을 시작했고 SP안에도 Begin Tran이 있다 즉, TRANCOUNT가 2가 되는 것인데 

위의 중첩 트랜잭션 예시는 Try로 감싸서 오류가 나도 예외처리가 된다 Try를 제거하면 두 번의 트랜잭션이 시작되고 Rollback이 되면서 TRANCOUNT가 0이 되고 나서 다시 COMMIT 을 하게 되니 트랜잭션 처리할 것이 없는데 처리하라고 해서 생기는 오류가 난다

훈스닷넷의 예시를 보면 Code에서 시작한 트랜잭션이 SP 스크립트 안에서도 트랜잭션이 동작하면서 관리가 양쪽에서 다하니 힘들어지는?? Code, SP 양쪽에서 관리 한다니 ㅠㅠ 어쨌든 error가 나는 case이고 이것에 대한 트랜잭션 개수는 좀 더 정확히 파악해서 테스트를 하도록 한다 

또 한가지... Auto Commit이 일어 날 때 뭐가 달라지는 지 확인해 보자.. 어렵다 Auto Commit은 개수를 무시해야 하나???

어쨌든 훈스닷넷 예시에서는 기본적으로 트랜적션은 프로그램으로 하실꺼면 프로그램에서만 프로시저에서 할꺼면 프로시저에서만 하시는걸 추천드립니다. 

이렇게 말 하고 있다 그리고

개인적으론 특별한 경우가 아니면 프로시져에서만 합니다.  한쪽에서 하지 않으면 논리적인 오류가 날 가능성이 매우 큽니다.그리고 추적역시 쉽지 않지요. 그리고 프로그램에서 하실꺼면 .net2.0 이상이면 TransactionScope로 싸시는걸 추천드립니다.

이렇게 자신의 추천하는 방법도 제시해 주고 있다. 

약간 정답이 없는 문제이다. 모든 트랜잭션을 SP내부에 위임할 수도 있고 코드로 제어하는 것이 좋아서 .NET에서 TransactionScope나 ITransaction 인터페이스를 사용해 구현한 UnitOfWork(라이브러리 아님 커넥션 하나에서 트랜잭션을 처리하는 패턴 같음(회사에서 사용)) 에서 트랜잭션 관리를 위임할 수 있는 선택의 문제가 같이 있다 

어짜피 코드가 좋을지 SP에서가 좋을지 판단할 만한 경험이나 통찰력이 부족하다 일단 양쪽에 다 Transaction 관리가 된다는 것만 이해하고 나중에 개념을 업그레이드 시켜보자.

기본적인 내용은 여기까지!!!

Entity Framework

- 추억의 Entity Framewok이다. 이 내용을 보다가 좀 화가 났다.

  사실 C#을 처음 접하게 된 계기가 ASP.NET MVC3 이다.  ActiveX(ocx) 환경의 웹 화면을 ASP.NET MVC3 로 변경하게 되면서 보게 되었는데 문제는 이걸 하기로 한 C# 개발자 과장님이 실패하고 쫓겨나면서 이제 일한지 6개월도 안된 나에게 넘어 온 프레임워크이다.

 젠장 C#도 처음보고 DB도 모르고 ORM은 기억이 안나는 거 보니 검색도 못했던 것 같고 웹 개발이랑 console 개발이랑 차이도 모르니 당연히 프론트 쪽의 javascript나 html 도 잘 모르는데 다 다루고 앉아 있고 힘든건 이게 메인업무도 아닌데 매일마다 어디까지 했냐고 압박받으면서 ㅠㅠ 거의 10년차도 포기한 걸 억지도 떠맡아서 개고생하면서 XXXXXXXXXXXXXXXXXXXXXXXXXXX 어쨌든 웹 페이지 호출이랑 CRUD 기능 동작하는 웹 어플리케이션 개발한 것을 시연하고 풀려났던 기억이 있다.. 내가 틈틈히 주말 야간으로 2달간 고생한 걸 경력직 과장님이 와서 1주일도 안되서 해결한 걸 보고 자괴감은.. 플러스..

 이번에는 Code-First 에 대한 내용을 보다가 여기까지 올라왔는데 갑자기 첫 회사 신입때가 생각나서 울컥하는 마음에 남기게 되었다.... 결국은 내가 완성할 수 있는 솔루션 규모가 아니었는데 왜 그런식으로 시험을 받아야 했었는지..모르겠다 문제 해결 능력만으로 개발을 할 수 있는(최대한 좋게 말해서이다.) 것도 장점이 있었겠지만.. 단점이 너무 많았던 것도 같다 집에와서 해야 하니 머릿속에 온통 How에 대한 생각밖에 없었으니..

 그냥.. 잊자.. 어짜피 퇴사전이나 퇴사 이후에 충분히 내 몫을 해내고 내 스스로 다 했다는 말도 들었으니.. 그것으로 만족하자 다른 회사 면접날 전에 술먹인 회사는 여기밖에 없다  

간단히 남기려는 것은 2가지 이다.

 1. Entity Framework에서 Code-First로 Model을 생성하는 것에 대한

 2. Code-First Model에서 다른 Table과의 관계에 대한

내용을 간단히 남기고자 한다

일단 위에랑 직접적인 관련은 아니지만 위로 개념을 올린 김에 ORM 을 보자

C#과 같은 객체 지향형 프로그래밍 언어에서 데이터베이스를 쉽게 사용하기 위한 도구라고 한 줄로 말할 수 있다

OOP개념의 객체(Object)와 관계(Relation)형 DB의 테이블을 맵핑(Mapping)해서 (native)SQL 작성을 안하고 쉽게 DB의 데이터에 Access 할 수 있는 기술이라고도 할 수 있다

여기서 나오는 3개의 단어 Object / Relation / Mapping 이 것이 ORM에서 쓰는 핵심 단어이다. ASP.NET 에서는 데이터 엑세스하는 기본 프레임워크로 Entity Framework를 쓰기 때문에 기본값 처럼 사용하는 것이다. MFC를 했을 때 DB 엑세스 하는 기술은 OLE DB를 사용 했기 때문에 그런 기술로 생각하면 된다.

Entity Framwork 모델

크게 3가지가 있다고 한다

1. Code First

2. Model First

3. Database First   

Model First와 Database First 접근 모델은 Visual Studio 의 Visual Model Designer (EDMX) 를 통해 객체/테이블 매핑을 디자인 하는 방식으로 두 개간 차이점은 Database Frist는 기존 DB로 부터 테이블 구조를 읽어와서 디자이너를 통해 Visual Model로 구성되는 것을 말하고 Model First는 기존 DB가 없을 때 직접 Model Designer를 써서 Entity들을 추가해 가면서 모델을 구성하는 방식이다. 위 두가지는 Visual Model Designer로 디자인한 것을 edmx 파일에 저장하게 된다.

Code First 방식은 Model Designer / EDMX를 사용하지 않고 데이터 모델을 C# 클래스로 직접 코딩하는 방식으로 앞으로의 EF는 Code First 방식만을 지원한다고 한다. (http://www.csharpstudy.com/web/article/8-Entity-Framework)

Code First

C# 클래스로 테이블의 구조를 정의하고 클래스의 프로퍼티를 테이블 컬럼으로 맵핑한다.

Code First란 말 그대로 DB를 미리 설계하지 않고 C# 클래스로 Domain Object(Model???) 를 정의하고 프로그램 실행 시 DB가 없으면 자동으로 DB를 생성하는 방식을 취한다

-> 이 말은 코드 우선이니까 테이블 모델이 Code 기반에서 생성되고 DB에 대한 생성과 추가 수정이 Code 기반의 Class Model에서 된 다는 것을 말한다

     MS SQL의 management studio를 통해 추가/수정/설정을 하는 것이 아니라 code로 구현해서 DB 관리자가 설정하는 것 처럼 추가하고 변경 사항을

    할 수 있다는 것을 말한다

재밌는 부분은 Table 과 맵핑되는([Table("name")] 이런 형태의 단순한 Entity Class를 POCO(Plain Old CLR Obect) 라고 부른다고 한다. 기본적인 단순히 테이터를 저장하고 담아저 전달되는 모델을 말하는데 자바에서는 POJO라는 것이 있었다 (Plain Old Java Object) 두 개가 같은 개념으로 보인다. 제일 기본이 되는 Model 객체?? (비즈니스가 딱히 없는??) 이라고 생각하면 쉬울 것 같다

Code First를 활용하는 부분을 보다보면 크게 3가지가 나온다

1. DbContext

2. Fluent API

3. Data Annotation

위 내용을 깊게 들어가면 EF로 DB Table 추가하고 컬럼 변경 및 키지정 관계 지정 까지 프레임워크 사용법 까지 다루게 되어서 저런게 있다는 것만 간략히 말한다. DbContenxt는 간단히 System.Data.Entity의 클래스 이며 DB와 관련된 여러 API를 사용할 수 있다 Context란 말에서 뭔가 저장소Repository 란 단어가 생각나는데 Domain Classes 와 Database의 중간에 있는 녀석으로 생각하면 될 것 같다 간단히 그림으로

http://www.entityframeworktutorial.net/entityframework6/dbcontext.aspx 참고하자

DbContext가 DB 전체를 관리할 수 있는 API를 제공하는 느낌이라면 Fluent API는 Table에 대한 설정을 할 수있다 테이블 간의 관계나 키 설정과 관련된 매서드들을 사용할 수 있다 Data Annotation은 C#의 Attribute로 컬럼의 값에 대한 Data Validation이나 컬럼의 속성(기본키(Key, notMapped))에 대해 프로터티 바로 위에 지정할 수 있다

즉 DB 제어 / Table 제어 같은 것이지 않겠나

마지막으로

Code-First Model에서 다른 Table과의 관계에 대한 표현은 크게 3가지가 있다

1. 1:1 관계

2. 1:N 관계

3. N:M 관계

아마도 관계형 데이터베이스라는 개념에서 관계라는 것이 핵심일 듯 하다 이놈의 관계에서 정규화도 고려되고 인덱스 키나 Join 할 테이블도 고려된다 그냥 테이블 간의 관계라고만 생각하기보다는 Object 간의 연관된 관계로 생각해야 그 객체 안의 속성이 어떤 특성이며 이 특성은 어떤 요소들의 집합 모임인지까지 생각할 수 있을 것 같다

(수학에서 집합/확률통계/방정식(변수를 통한 식만들기) 는 체감적으로 많이 나오는 것 같다 (선형대수학이나 행렬은 아직 뭔가 더 해봐야 알 것 같다)

관계는 코드로 보는게 편하다 (https://www.c-sharpcorner.com/UploadFile/3d39b4/relationship-in-entity-framework-using-code-first-approach-w/)

일대일 관계

1. namespace EF.Core.Data  
2. {  
3.    public class User : BaseEntity  
4.     {  
5.         public string UserName { get; set; }  
6.         public string Email { get; set; }  
7.         public string Password { get; set; }  
8.         public UserProfile UserProfile { get; set; }  
9.     }  
10. }  

1. namespace EF.Core.Data  
2. {  
3.    public class UserProfile : BaseEntity   
4.     {  
5.         public string FirstName { get; set; }  
6.         public string LastName { get; set; }  
7.         public string Address { get; set; }  
8.         public virtual User User { get; set; }  
9.     }  
10. }  

두 클래스간 virtual 속성으로 각각의 class 간 연결을 만들었다 즉 연결되어 있는 다른 테이블의 속성을 알 수 있다

일대다 관계

1. using System.Collections.Generic;  
2.   
3. namespace EF.Core.Data  
4. {  
5.   public  class Customer : BaseEntity   
6.     {  
7.       public string Name { get; set; }  
8.       public string Email { get; set; }  
9.       public virtual ICollection<Order> Orders { get; set; }  
10.     }  
11. }  

1. using System;  
2.   
3. namespace EF.Core.Data  
4. {  
5.     public class Order : BaseEntity  
6.     {  
7.         public byte Quanatity { get; set; }  
8.         public Decimal Price { get; set; }  
9.         public Int64 CustomerId { get; set; }  
10.         public virtual Customer Customer { get; set; }  
11.     }  
12. }  

virtual로 선언된 객체를 보면 한쪽이 Collection이다 Code First로 생성한 Entity는 HashSet(ICollection 상속된 클래스)으로 자료형이 되어 있는데 어쨌든 .Net 적으로 얘기하면 Collection 객체를 포함하고 있다는 것이다. 그러니 단순 List도 포함된다

하나의 객체가 다수의 다른 객체를 가지고 있으니 1:N 관계가 성립되는 것이고 부모하나에서 파생된 자식들이라고도 표현이 되고 Has 관계로 연관된 Object / Entity / Table 라는 개념을 포함할 수 있다

(OOP는 진짜 용어들만 이해해도... (개념적/논리적/물리적) 이런 구분인건가? 누군가에게 설명하기도 어렵고 하고 싶지 않는 부분이다...  

어쨌든 일대다 관계는 하나의 객체가 다수의 객체를 포함한다.. 이렇게 정의할 수 있다

다대다 관계

1. using System.Collections.Generic;  
2.   
3. namespace EF.Core.Data  
4. {  
5.     public class Student : BaseEntity  
6.     {  
7.         public string Name { get; set; }  
8.         public byte Age { get; set; }  
9.         public bool IsCurrent { get; set; }  
10.         public virtual ICollection<Course> Courses { get; set; }  
11.     }  
12. }  

1. using System;  
2. using System.Collections.Generic;  
3.   
4. namespace EF.Core.Data  
5. {  
6.    public class Course : BaseEntity  
7.     {  
8.        public string Name { get; set; }  
9.        public Int64 MaximumStrength { get; set; }  
10.        public virtual ICollection<Student> Students { get; set; }  
11.     }  
12. }  

위의 일대다를 보고 다대다를 보면 매우 간단히 답이 나온다 각각 서로에 대한 Collection 객체를 가지고 있으면 다대다 관계이다.

여기서 포인트랄 하나 잡자면 다대대 관계를 그대로 쓰지 않는다 두개 간의 관계를 연결해주는 연결테이블 or 맵핑테이블 or 조인테이블 이런 단어로 사용되는 테이블이 가운데 있다 간단히 생각하면 다대다 관계를 다이렉트로 연결하는 것이 아니라 중간에 맵핑테이블을 두어서 일대다 관계 두 개로 쪼개는 것이다. 그러면 EF의 Database First에서 EDMX 파일에서는 맵핑 Class가 생기고 그 맵핑 정보를 각각 가지고 있는다 위의 코드는 관계만 정의된 것이고 맵핑 Entity에 대한 포함 객체는 없는 것이다. 여기서 DB의 관계와 객체인 Class간에 차이가 생길 수 있는데 (회사 업무에서 발견)

간단히 생각하면 객체에 대한 역정규화 기법?? 이라고 보인다

맵핑 객체를 EF가 만들어 준대로 바로 쓰는 것이 아니라 Business Model로 변경 시 해당 컬럼을 프로퍼티의 일부로 포함 할 수도 있고 이미 관계테이블에 포함한 프로퍼티일 경우 중복된 프로퍼티(컬럼)을 쓰지 않아도 되는 것이다.

물론 Entity 형태의 Model을 그대로 쓰는 것이 아니라 모델이 Customizing 되기 때문에 모델이 합쳐지거나 Colection이 포함되는 관계를 맺을 때 중복된 data를 제거하거나 1:N 으로 변경된 Class 내부에 포함하거나 해서 다른 class의 속성을 가져 올 수 있는 것이다.

DB에서 중복을 만들거나 두 개의 Entity를 합치는 경우 보통 역정규화라고 하는 것 처럼

Class에서도 맵핑 Class를 포함 하거나 Class를 해체해서 관련 Class의 프로퍼티로 포함 시키는 부분이 모델의 역정규화 인 것 처럼 보인다.

물론 Business Model / Customizing Model에서 쓰이는 부분이다.

예제로 만들면 좋겠지만... 개념적인 부분에 시간이 너무 들어가서 말로 압축한다.. 길어야 하루면 될게 몇시간 초과되었다... ㅠㅠ

기본 개념은 1:N 관계 2개의 Class가 생기고 그 Mapping Entity도 Class로 생성이 되나 Business Model을 만들면서 중복을 합치거나 Class를 해체해서 관계 Class에서 자기 속성으로 바로 접근하게 만드는 건 그때그때 다르다고 생각하면 된다. 

SQL JOIN에 대해 집합으로 잘 표현된 자료가 있다 

출처 : http://rapapa.net/?p=311

기본적인 JOIN문의 형태는 

SELECT column1, column2, column3..... 

FROM TableA A

(LEFT / RIGHT / FULL / INNER) JOIN TableB B

ON A.Key = B.Key

LEFT / RIGHT / FULL 은 OUTER Keyword를 써서 나타낼 수도 있다 (안 써도 결과는 같다)

위의 집합관계 그림에서 

- 차집합 Set(A-B) or Set(B-A)

- Set(U) - Set(A∩B) 

과 같은 경우 조건을 추가해서 나타내는 방법이 있다 

WHERE 절을 추가해서 반대 방향의 테이블의 Key(맵핑되는 컬럼) 가 IS NULL일 때 순수하게 한쪽에만 속하는 원소(Row)를 가져온다

아래 예제를 보자 

예제 data는 Oracle XE 버전을 설치하면 제공되는 기본 Scott DB를 이용했다 

ex)

SELECT * 

FROM EMP A

RIGHT OUTER JOIN DEPT B

ON A.DEPTNO = B.DEPTNO

SELECT * 

FROM EMP A

RIGHT OUTER JOIN DEPT B

ON A.DEPTNO = B.DEPTNO

WHERE A.DEPTNO IS NULL

차이가 느껴지는가?? 

JOIN의 결과는 INNER 처럼 Key가 같은 내용을 찾아주지만 LEFT나 RIGHT와 같이 쓰이면 한쪽에만 존재하는 data를 포함하게 된다 

그 반대방향의 Key가 Null인 경우만 필터링하면 순수한 A or B Table의 data를 가져올 수 있다 

교집합에 속하는 data를 가져와야 하는지 

차집합에 속하는 data를 가져와야 하는지 

그것에 따라 RIGHT 나 LEFT 또는 INNER를 잘 쓰도록 하자