DB 정리

1. 데이터베이스 관련 기본 용어, 지식

   • DB : 구조화된 데이터들의 집합

   • RDB : 2차원 구조의 테이블에 데이터들을 저장하고 관리

   • DBMS : DBMS는 데이터베이스 관리 시스템을 나타냅니다. 응용 프로그램임.

   • NoSQL에서는 RDBMS와는 달리 테이블 간 관계를 정의하지 않습니다.

   • 관계형 데이터 모델 : '테이블 형식을 이용하여 데이터를 정의한 모델', 테이블을 릴레이션이라고 함.

   • 데이터 모델링 3요소 : 엔티티(데이터 모델링에서 사용되는 객체) - 어떤 것 / 릴레이션 - 어떤 것 간의 관계 / 어트리뷰트 - 속성

   • 데이터 모델링 : 개념 -> 논리 -> 물리(데이터 흐름 도식화하는 과정)
     개념 모델링 : 핵심 엔터티와 그 엔터티 사이의 관계를 도출, 주요 어트리뷰트 파악
     논리 모델링 : 개념 모델링 상세화하는 작업. 더는 삭제할 엔티티나 속성 없는 형태, 정규화
     물리 모델 : 데이터베이스 설계(실제 데이터베이스)

   • 엔티티 사이의 관계를 릴레이션(relationship)

   • 데이터베이스에서 다양한 유형의 관계
     => 1:1 / 1:M / M:N

   • SQL에 대해 설명 ?
     DDL (데이터 정의 언어) : 데이터 보유 구조를 정의언어. 이 명령은 자동 커밋됩니다.(Create, Alter, Drop, Truncate)
     DML (데이터 조작 언어) : 데이터베이스의 데이터를 조작 언어. 이 명령은 자동 커밋되지 않으며 롤백 할 수 있음(Select, Insert, Update, Delete)
     DCL (데이터 제어 언어) : 데이터베이스에 제어에 관한 언어. 접근, 객체에 권한. 자동으로 커밋되지 않으며 롤백 할 수 있음. - commit, rollback, grant, revoke

   -[Drop truncate delete차이 ]
   Delete : 데이터는 지워지지만 용량은 안줄어듬, 삭제 후 되돌릴 수 있음
   Truncate : 테이블 삭제하지 않고, 데이터 지우고 용량까지 줄어듬(인덱스 등도 다 지움), 삭제 되돌리지 못함
   Drop : 테이블 전체 삭제. 되돌리지 못함.

   -[저장 프로 시저]
   저장 프로시저는 사전 컴파일 된 SQL 쿼리의 모음으로, 사전에 준비해 둔 많은 명령을 자동으로 실행할 수 있기 때문에 작업의 효율성도 높일 수 있습니다.
   장점 : 네트워크 부하 감소, 처리 시간 감소 / 프로시저 수정 필요한 경우, 애플리케이션 배포 안해도됨
   단점 : 이력관리가 힘듬(git 없음) / 길게 작성되면 로직파악 어려움 / 연산있으면 실행시간 길어짐(Lock 걸려있으면 병목 됨).

   키 관련 -
   식별자 : 관계형 데이터베이스에서 각각의 구분할 수 있는 논리적 개념 ( 특성 : 유일성, 최소성 )
   유일성 : 하나의 릴레이션에서 모든 행은 서로 다른 키 값을 가져야 합니다.
   최소성 : 꼭 필요한 최소한의 속성들로만 키를 구성해야 합니다.
   릴레이션(Relation): 데이터들을 2차원 테이블의 구조로 저장한 것

2. 키
   키 : DB에서 튜플을 찾거나 정렬할 때 다른 튜플들과 구별할 수 있는 기준이 되는 Attribute(속성), 튜플 = row
   • 슈퍼 키(Super Key): 유일성을 만족하는 키.
   • 후보 키(Candidate key): 유일성과 최소성을 만족하는 키. 기본키가 될 수 있는 후보
   • 복합 키(Composite Key): 2개 이상의 속성(attribute)를 사용한 키.
   • 기본 키(Primary key): 후보 키에서 선택된 키. Unique, not null(테이블에 오직 한 개, 최소성과 유일성 만족) - 행데이터 고유하게 식별하는 기능
   • 외래 키(Foreign Key): 테이블(Relation)들 간의 관계를 나타내기 위해서 사용됨(중복 null 허용, 기본 키(Primary key)를 참조하는 속성이다. )
   • 대체 키(Surrogate key): 후보 키 중에 기본 키로 선택되지 않은 키.

3. 인덱스
   인덱스 : 데이터베이스 테이블의 검색 속도 향상하기 위한 자료구조

DBMS 인덱스의 자료구조 : 1. 해쉬 테이블 인덱스, 2. B+ Tree 인덱스

데이터베이스에서는 B+Tree 방식을 사용 이유 - 해시 함수는 등호(=) 연산에만 특화되었기 때문에 부등호 연산(<,>)이 자주 사용되는 데이터베이스 검색을 위해서는 해시 테이블이 적합하지 않기 때문

인덱스 사용하면 좋은 경우 : 규모 큰 테이블, insert, update, delete 자주 발생하지 않는 컬럼, 데이터 중복도 낮은 컬럼,
단점 : 추가 공간 필요, Insert, Delete, Update등 변경작업 잦으면 성능 저하.

[ B+Tree ]
1. 모든 데이터는 리프 노드에 저장되며, 리프 노드는 양방향 연결 리스트로 연결되어 있습니다.
2. 내부 노드에는 키만 저장되고 실제 데이터는 리프 노드에 있습니다.

[ B-Tree(B트리) ] : 균형 트리를 확장해서 더 많은 수의 자식을 가질 수 있게 일반화한 것
1. B- 트리는 내부 노드와 리프 노드 모두 데이터를 저장하므로 내부 노드에는 키와 값이 모두 저장됩니다.

[B+로 가져가면서 얻는 장점]

• leaf node를 제외하고 데이터를 저장하지 않기 때문에 메모리를 더 확보할 수 있다.
• Full scan을 하는 경우 B+Tree는 leaf node에만 데이터가 저장되어 있고, leaf node끼리 linked list로 연결되어 있기 때문에 선형 시간이 소모된다. 반면 B-Tree는 모든 node를 확인해야 한다.

[단점]

• B-Tree는 탐색을 위해서 노드를 찾아서 이동해야 한다는 단점.

[차이]

• b-트리엔 키 중복이 없는데, b+트리엔 키 중복이 있다.(내부 노드에 키값, 리프 노드에 키, 밸류값 가짐)

[클러스터 인덱스]

• 테이블당 1개만 존재(PK생성하면 자동 생성)
• 데이터가 정렬된 상태
• 리프 페이지 = 데이터 페이지(따로 인덱스 페이지 만들지않음)
  페이지 : 기본 저장 및 io 단위

[넌클러스터 인덱스]

• 테이블당 여러 개 생성 가능

• 데이터가 정렬되지 않음

• 리프 페이지에 데이터가 있는곳 주소를 가짐(클러스터 인덱스보다 공간 더 필요)

• 클러스터 인덱스는 데이터 위치를 바로 알기 때문에 그 데이터로 바로 접근할 수 있고(넌클러스터형 인덱스보다 조회 빠름),

• 넌 클러스터 인덱스는 인덱스 페이지를 한번 거쳐서 데이터에 접근하는 방식이다.

[커버링 인덱스]

• SQL에서 원하는 데이터를 인덱스에서만 추출할 수 있는 인덱스
  인덱스에 있는 주소값을 참조하여 데이터블록으로 가지 않고 출력까지 가능한 경우
  

4. 정규화
   [이상]
   삽입 이상 : 불필요한 정보를 함께 저장하지 않고서는 어떤 정보를 저장하는 것이 불가능하다.
   갱신 이상 : 중복된 데이터 중 일부만 수정되어 데이터 모순이 일어난다.
   삭제 이상 : 어떤 정보를 삭제하면 다른 정보까지 삭제되는 현상

[정규화] :
관계형 데이터베이스에서 중복을 최소화하기 위해 데이터를 구조화하는 작업
정규화 목적 : 테이블 간에 중복된 데이터를 허용하지 않는다는 것

[제1 정규화]
테이블의 컬럼이 원자값을 갖도록 테이블 분해(컬럼당 값 하나만 가진다)

[제2 정규화]
1 정규화 만족, 기본키의 부분집합이 결정자가 되어서는 안되도록 테이블 분해

[제3 정규화]
제 2정규화 만족, 이행적 종속을 없애도록 테이블 분해
- 이행적 종속 : A->B, B->C성립할 때 A->C성립하는 것

[BCNF 정규화]
3 정규화 만족, 모든 결정자가 후보키가 되도록 테이블을 분해하는 것

[반정규화(역정규화)]
쿼리 성능을 향상시키기 위해 테이블에 중복 데이터를 추가하는 작업 : 정규화 거치면 릴레이션간 연산(join)많아짐.

• 역정규화는 읽기 작업 많이 필요한 부분

정규화의 장점은?
-데이터베이스 변경 시 이상 현상을 제거하고, 데이터베이스 구조 확장 시 재디자인을 최소화합니다.

정규화의 단점은?

• 릴레이션 분해로 인해 릴레이션 간의 연산(join)이 많아집니다. 이로 인해 응답 시간이 느려질 수 있습니다.

반정규화 단점은?

• read에는 빠르지만 삭제, 업데이트에는 느림(데이터 중복때문), 저장공간 효율 느림, 데이터 무결성(정확성)이 떨어짐 - 데이터 나중에 수정할때 일부만 수정됨.

5. 트랜잭션

   [트랜잭션 특징] : ACID
   A : 원자성 - 모든 연산이 완벽 수행 또는 취소
   C : 일관성 : 트랙잭션 수행 전후, 데이터베이스 상태가 일관되어야 한다
   - 트랜잭션 일관된 이후의 DB는 DB의 제약이나 규칙 만족해야 한다.
   I : 고립성 - 트랜잭션끼리는 서로의 연산에 끼어들 수 없다.
   D : 지속성 - 커밋된 트랜잭션 결과는 보존되어야 한다

   [트랜잭션 격리수준] : 동시에 여러 트랜잭션 처리될 때, 트랜잭션끼리 얼마나 서로 고립되어 있는가(ACID에서 Isolation)
   READ UNCOMMITTED 커밋되지 않은 데이터 읽음
   READ COMMITTED 커밋 완료된 데이터만 읽음
   REPETABLE READ 트랜잭션이 시작되기 전에 커밋된 내용에 대해서만 조회할 수 있는 격리수준(읽은 데이터의 일관성을 보장함, 쓰기는 보장 안함)
   SERIALIZED 한 트랜잭션에서 읽고 쓰는 레코드를 다른 트랜잭션에서는 절대 접근할 수 없는 것이다.

   [데이터 부정합 문제]
   더티리드 : 아직 커밋되지 않은 상황에서 다른 트랜잭션이 해당 변경 사항을 조회할 수 있는 문제
   Non-Repeatable Read : 같은 트랜잭션 내에서 같은 데이터를 여러번 조회했을 때 읽어온 데이터가 다른 경우 (Read commited에서 나타남)
   팬텀리드 : 같은 트랜잭션 내에서 같은 데이터를 여러번 조회했을 때 조회해온 결과의 행이 새로 생기거나 없어지는 현상

   [데이터베이스 락종류와 역할]
   공유락 : 데이터 읽을 때 사용되는 Lock(공유Lock끼리는 동시접근 가능)
   배타락 : 데이터를 변경할 때 사용되는 Lcok : Lock해재 될때까지 다른 트랜잭션(읽기포함) 접근 불가
   업데이트락 : 배타락 걸기전 데드락 방지위해 사용되는 락, update쿼리의 where가 실행되는 과정에서 적용
   내재락 : 락을 걸수 있는지 여부 파악하기 위해 사용되는 락

   [데이터베이스 데드락] : 여러 개의 트랜잭션들이 실행하지 못하고 서로 무한정 기다리는 상태
   [예방]

   • 각 transaction이 실행되기 전에 필요한 데이터를 모두 lock을 하고 모두 lock을 하지 못하면 lock 반납하도록 하여 데드락을 예방할 수 있다.
   • 데드락 발생시 일정 시간이 지나면 데이터에 접근하는 쿼리 취소한다

데드락 :

[운영체제] : 각각의 프로세스가 서로의 자원을 점유하기 위해 대기하는 상황
[DB] : 여러 개의 트랜잭션(Transaction)들이 실행을 하지 못하고 서로 무한정 기다리는 상태

• 상호 배제 (Mutual Exclusion) : 한 번에 한 개의 프로세스만이 공유 자원을 사용할 수 있어야 한다.
• 점유와 대기 (Hold & Wait) : 최소한 하나의 자원을 점유하고 있으면서 대기하는 프로세스가 있어야 한다.
• 비선점 (Non-Preemptive) : 다른 프로세스에 할당된 자원은 사용이 끝날 때까지 강제로 빼앗을 수 없어야 한다.
• 환형 대기 (Circular Wait) : 공유 자원과 자원을 사용하기 위해 대기하는 프로세스들이 원형으로 구성되어 있다

[DB에서 데드락 해결 방안]

• 예방 기법 : 교착상태인 데이터가 있다면, 데이터에 접근하는 쿼리 timeout 설정하여 일정 시간이 지나면 쿼리 취소하도록 함 / 트랜잭션 실행전 필요한 자원에 Lock을함
• 회피 기법 : Wound Wait - 먼저 들어오면 선점, 나중에 들어오면 대기, Wait Die - 트랜잭션 A가 트랜잭션 B에 의해 잠금된 데이터를 요청할 때 트랜잭션 A이 먼저 들어온 트랜잭션이라면 대기(Wait)한다, 트랜잭션 A가 나중에 들어온 트랜잭션이라면, 포기(Die)하고 나중에 다시 요청
• 빈도 줄이기 기법 : 트랜잭션 커밋을 더 자주한다.
• 낙관적 병행 : 트랜잭션이 커밋된 후 데이터 문제있으면 롤백한다.

6. 트리거

• 트리거(Trigger)에 대해 설명해주세요.
  테이블에 대한 이벤트에 반응해 DML 문이 수행되었을 때, 데이터베이스에서 자동으로 동작하도록 작성된 프로그램입니다.

7. DB튜닝

• DB 시스템의 전체적인 성능 향상
  -> 설계 튜닝(모델링 관점) : 인덱스 설계, 반정규화
  -> DBMS튜닝(환경) : 성능을 고려하여 메모리나 블록크기 지정(버퍼 크기, 캐시 크기)
  -> SQL튜닝(App관점) : SQL성능 - Hash/Join

8. inner / outer join
   inner : 밴다이어그램으로 교집합
   outer join : 밴다이어그램으로 합집합

9. 기타

• JOIN에서 ON과 WHERE의 차이를 설명해주세요.
  (=ON 조건으로 필터링이 된 레코들간 JOIN이 이뤄진다)
  (=JOIN을 한 결과에서 WHERE 조건절로 필터링이 이뤄진다)

• 옵티마이저 : 개발자가 SQL작성하고 실행하면 옵티마이저에서 실행계획을 세우고 이에따라 쿼리 수행됨