- 후보키 중에 선택한 메인 키.
- NotNull, Unique
- 데이터를 유일하게 식별하기 위해 사용하는 속성들 부분 집합.
- 후보키중에서 기본키를 선택한다.
- 조건
- 하나의 투플을 유일하게 식별
- 꼭 필요한 최소 속성으로만 구성.
- 후보키중 기본키를 제외한 나머지, 보조키.
- 유일하지만 최소성을 만족시키지 못하는 키.
- 다른 릴레이션의 기본키를 그대로 참조하는 집합.
- 데이터의 중복을 피하기 위해(3NF를 만족시키기 위해) 데이터를 나누어 저장한다.
- 이렇게 분해되어 있는 정보를 다시 조합하여 원하는 결과를 도출하기 위해 Join을 사용한다.
- 즉, Join은 컬럼을 기준으로 행을 합쳐주는 연산.
- 교차 조인(Cross Join)
- 동등 조인(Equi Join)
- 비등가 조인(Non-Equi Join)
- 자연 조인(Natural Join)
-
- 두 테이블의 카티션 프로덕트(곱집합)의 결과.
- 즉 각 테이블의 모든 컬럼의 조합.
A(a,b), B(c,d) -> AxB(ac,ad,bc,bd)
-- 명시적 FROM A CROSS JOIN B; -- 묵시적 FROM A,B
-
- 가장 많이 사용되는 조인중 하나. 교집합(교차 조인 후 조건에 부합하는 부분.)
-- 명시적 FROM A INNER JOIN B ON A.team_id = B.team_id -- 묵시적 FROM A,B WHERE A.team_id = B.team_id
-
- 조건문에서 동등비교를 하는 조인. (내부 조인의 예시와 같이)
-
- 동등비교를 사용하지 않는 조인
- betweeb, 크다, 작다 등 사용.
-
- 동등 조인의 한 유형.
- 두 테이블의 컬럼명이 같을 때 이것을 기준으로 조인.(team_id)
- 해당 컬럼은 한번만 나타나게 된다.
- Left Outer Join
- Right Outer Join
- Full Outer Join(MySQL에서는 지원하지 않는다. UNION으로 사용.)
-
FROM A LEFT OUTER JOIN B ON A.team_id = B.team_id
- 위의 상황일 때 A의 모든 데이터와 B가 매칭되는 레코드.
- 매칭되지 않은 로우의 B 컬럼은 null 이 된다.
| a team_id | | b team_id | | a1 1 | | b1 1 | | a2 2 | | b2 2 | | a3 3 | | b3 5 |- 위와같이 데이터가 주어져있다면
- 왼쪽 외부 조인 이후에는
| a team_id b team_id | | a1 1 b1 1 | | a2 2 b2 2 | | a3 3 null null |이 된다.
- Right Outer Join 은 반대로 B의 모든 테이블에 A를 매치한다.
- Full Outer Join 은 A B의 데이터가 모두 나타나고 마찬가지로 매치되지 않는 데이터는 null이 된다.
- 말 그대로 자기 자신과 조인.
FROM table AS A, table AS B
WHERE A.id <> B.id
AND A.team_id = B.team_id- 자신과 다른 아이디를 가진 데이터 중에서 team_id가 같은 데이터를 조인.
- 서브 쿼리 내에서 존재하는 데이터를 메인 쿼리에서 추출.
- EXISTS, IN 절 등을 사용할 수 있다.
이미지 출처 : https://mangkyu.tistory.com/110
- 테이블 간 중복된 데이터를 허용하지 않아 무결성 유지.
- 이상현상 방지
- 데이블 구성을 논리적, 직관적으로
- 확장에 용이.
- 테이블 컬럼이 원자값을 갖도록 테이블을 분리.
- 모든 도메인이 원자값으로 구성.
- 모든 속성에는 반복되는 그룹이 없어야 한다.
- 기본키를 사용하여 데이터의 고유한 식별 가능.
- 위으 이미지에서는 취미들 컬럼에 여러개의 데이터가 들어가 있기 때문에 모든 도메인이 원자 값을 갖는다는 조건을 만족하지 못한다.
- 컬럼을 취미로 변경하고 추신수-영화, 추신수-음악 과 같이 분리하여 1NF를 만족시킬 수 있다.
- 기본키의 부분집합이 다른 컬럼을 결정해서는 안된다.
- 위의 표에서는 (학생 번호,강좌 이름)이 복합키로 기본키가 된다.
- 그러나 강좌 이름이 강의실 컬럼을 결정하기 때문에 2NF에 위배된다.
- 때문에 2NF를 만족시키기 위해서는 이를 분리해야 한다.
- 제 2 정규화를 만족하는 상태에서 이행적 종속을 없애기 위해 분리.
- *이행적 종속: A->B, B->C 이면 A-C 이다.
- 즉, 기본키가 아닌 속성들은 기본키에 의존해야 한다.(기본키에 의해서만 결정)
- 위의 이미지에서 학생번호는 강좌이름을 결정하고, 강좌 이름은 수강료를 결정하므로 이행적 종속성이 존재한다.
- 테이블을 분리하여 이행적 종속성을 제거한다.
- 3NF를 만족하고, 모든 결정자가 후보키가 되어야 한다.
- 위의 이미지에서 (학생번호,특강이름)은 기본키로 교수를 결정한다.
- 또한 교수는 특강이름을 결정하는 결정자이다. 하지만 교수는 후보키가 아니다. 때문에 BCNF를 위배한다.
- 또는 특강신청 테이블에서 컬럼은 학생번호, 특강이름이 되어도 된다.
- 데이터베이스의 상태를 변화시키기 위해 사용하는 작업 단위.
-
- 트랜잭션이 DB에 모두 반영되거나(commit) 모두 반영되지 않아야(Rollback) 한다.(일부만 반영 불가능)
-
- 트랙잭션의 동일한 작업 처리 결과는 항상 같아야 한다.
-
- 둘 이상의 트랜잭션이 동시해 병행 실행되어도 서로에게 영향을 주지 않아야 한다.
- 다른 트랜잭션에 끼어들 수 없다.
-
- 트랜잭션이 성공적으로 완료되었다면 결과는 영구적으로 반영(저장)되어야 한다.
-
Database Management System
-
크게 질의 처리기(Query Processor), 저장 시스템(Storage System)으로 나뉜다.
-
고정 길이의 page 단위로 disk 에서 읽고 쓴다.
-
Buffer Manager: Buffer 관리 정책에 따라 Undo,Redo 결정.
- Transaction 이 정상적으로 종료되지 않은(commit 되지 않은) page들을 원래대로 돌려놓게 되는데 이를 Undo라고 한다.
- 즉 Update 쿼리에서 set n = n + 1을 수행하고 있었다면 Undo에서는 set n= n-1을 수행하여 쿼리를 실행하기 이전으로 복원한다.
- 이 때 2개의 정책이 존재하는데
- steal 정책은 수정된 페이지를 언제든지 디스크 쓸 수 있다. 대부분의 DBMS가 채택하고 있는 정책이다. Undo logging과 복구를 필요로 한다.
- not steal 정책은 반대로 수정된 페이지들을 트랜잭션이 끝나기 전까지 버퍼에 유지한다. Undo 작업이 필요 없지만 매우 큰 메모리 버퍼를 필요로 한다.
-
이미 Commit한 트랜잭션의 수정을 재 반영하는 복구 작업.
-
2개의 Buffer 관리 정책
- FORCE: 수정 했던 모든 페이지를 Commit 시점에 디스크에 기록. Redo 필요없음.
- Not FORCE: 디스크에 반영하지 않음. Redo가 필요, Redo logging, 대부분의 DBMS의 정책.
- 트랜잭션에서 일관성 없는 데이터를 허용하는 수준.
- 데이터베이스는 트랜잭션이 독립적으로 수행되도록 한다, 하지만 무조건 Locking을 통해 수 많은 트랜잭션이 순차적으로 수행되게 한다면 성능이 떨어진다. 하지만 Locking의 범위를 무분별하게 줄인다면 잘못된 처리가 증가할 것이다.
- 트랜잭션에 아직 처리중인(commit 되지 않은) 데이터를 다른 트랜잭션이 읽는 것을 허용한다.(Dirty Read)
- 데이터베이스의 일관성이 깨지게 된다.
- A에서 변경한 데이터를 B가 읽었는데 Rollback되는 경우 등.
-
트랜잭션이 작업 수행중일 때 다른 트랜잭션이 접근할 수 없어 대기한다.
-
Commit 된 트랜잭션에만 접근 가능하다.
-
SQL 서버가 Default 로 사용하는 격리 레벨.
-
항상 같은 결과를 반환해야한다는 정합성에 어긋난다(Non-Repeatable Read)
- 트랜잭션 A에서 데이터 N을 조회 -> 10
- 트랜잭션 B에서 데이터 N을 11로 변경.
- 트랜잭션 A에서 데이터 N을 조회 -> 11
- 트랜잭션이 시작되기 전에 커밋된 내용에 대해서만 조회 가능.(Undo 테이블에서 가져온다.) (MySQL에서는 트랜잭션에 번호를 부여하고, 자신보다 번호가 낮은 트랜잭션이 변경한 것만 읽게한다. )
-
트랜잭션 범위 내에서 조회한 데이터의 일관성 보장.
-
Phantom Read: 보이지 않았던 투플이 갑자기 나타나는 현상이 발생한다.(다른 트랜잭션에서 Insert를 실행하는 경우.)
- 트랜잭션이 완료될 때 까지 읽기 작업 또한 Lock을 건다.
- 다른 트랜잭션의 접근이 불가능.
- 동시 처리 속도가 떨어진다.
옵티마이저란 가정 효율적으로 SQL을 수행할 최적의 쿼리 경로를 생성해주는 DBMS의 핵심 엔진이다. SQL을 실행하면 옵티마이저를 거쳐 실행계획을 세우고 되고, 각 실행계획을 비교하여 최적의 실행계획에 따라 쿼리를 수행하게 된다.
사전에 정의된 규칙을 기반으로 최적화 한다. 실행 우선순위가 존재하며 판단이 매우 규칙적이기 때문에 실행이 예상 가능하다. 하지만 예측 통계정보 요소를 무시하는 단점이 있다.
- ROWID를 사용한 단일 행인 경우
- 클러스터 조인에 의한 단일 행인 경우
- 유일하거나 기본키(Primary Key)를 가진 해시 클러스터 키에 의한 단일 행인 경우
- 유일하거나 기본키(Primary Key)에 의한 단일 행인 경우
- 클러스터 조인인 경우
- 해시 클러스터 조인인 경우
- 인덱스 클러스터 키인 경우
- 복합 칼럼 인덱스인 경우
- 단일 칼럼 인덱스인 경우
- 인덱스가 구성된 칼럼에서 제한된 범위를 검색하는 경우
- 인덱스가 구성된 칼럼에서 무제한 범위를 검색하는 경우
- 정렬-병합(Sort-Merge) 조인인 경우
- 인덱스가 구성된 칼럼에서 MAX 혹은 MIN을 구하는 경우
- 인덱스가 구성된 칼럼에서 ORDER BY를 실행하는 경우
- 전체 테이블을 스캔(FULL TABLE SCAN)하는 경우
최근에 많이 사용하는 옵티마이저 방식으로 옵티마이저 하면 가장 먼저 떠오르는 방식이다. 비용 기반 옵티마이저는 실행 계획을 세운 뒤 비용이 최소한으로 나온 실행 계획을 수행한다. 이때 옵티마지어는 비용을 예측하기 위해 테이블, 인덱스, 컬럼 등의 객체 통계 정보와 시스템 통계정보를 이용한다.
- CHOOSE: SQL 실행 환경에서 통계 정보를 가져올 수 있다면 비용기반을, 없다면 규칙 기반 옵티마이저를 작동시킨다.
- FIRST_ROWS: 옵티마이저가 처리 결과중 첫 로우를 출력하는데 걸리는 시간을 최소화 하도록 실행계획을 수립한다.
- FIRST_ROWS_n: SQL의 실행 결과를 출력하는데 까지 걸리는 응답속도를 최적화한다.
- ALL_ROWS: 실행 결과 전체를 빠르게 처리하는데 최적화된 실행계획을 수립한다. 마지막으로 출력될 행까지 최소한의 자원, 최대한 빨리 가져오도록 최적화 한다.
옵티마이저는 위의 그림과 같이 동작한다.
Parser에서 SQL 문장을 분석하여 파싱 트리를 생성하고, Query Transformer 에서는 파싱 결과를 보고
좀 더 나은 실행 계획을 갖도록 SQL을 변환한다.
Estimator 에서는 시스템 통계정보를 딕셔너리로부터 수집하며 실행에 소요되는 비용을 계산한다. Plane Generator에서 계산 값들을 토대로 실행계획을 생성하고
Row-Source Generaator에서 옵티마이저가 생성한 실행계획을 SQL 엔진이 실행할 수 있는 형태로 포맷팅한다.
- 테이블의 전체 행의 수, 테이블이 차지하고 있는 전체 블록의 수, 로우의 평균 길이
- 컬럼 값의 종류, Null의 분포, 컬럼 값의 평균 길이, 컬럼의 데이터 분포 추정치ㅣ.
- 인덱스의 리프 블록의 수, 인덱스 트리의 높이, 접근할 데이터의 군집도.
- I/O, CPU의 성능과 사용률.
https://pearlluck.tistory.com/46
https://nesoy.github.io/articles/2019-05/Database-Transaction-isolation
https://coding-factory.tistory.com/743