[DB]DB관리

이상현상(Anomaly)

: 불필요한 데이터 중복으로 인해 릴레이션에 대한 데이터 삽입, 수정, 삭제 연산을 할 때 발생할 수 있는 부작용

 

- 삽입이상 : 특정 데이터가 존재하지 않아 중요한 데이터를 데이터베이스에 삽입할 수 없는 현상

- 갱신이상 : 특정 데이터를 업데이트했는데, 정상적으로 변경되지 않은 경

- 삭제이상 : 특정 정보를 삭제하면, 원치 않는 정보도 함께 삭제되는 현상

 

해결방법

: 데이터베이스를 올바르게 설계한다. 즉 정규화를 통해 데이터베이스의 테이블을 적절하게 분해하여 중복을 최소화한다.

 

트랜잭션(Transaction)

: 데이터베이스의 상태를 변화시키기 위해 수행되는 작업의 단위

*상태 변화 : SQL 질의어를 통해 DB에 접근하는 것

*작업 단위 : 많은 SQL명령문들을 사람이 정하는 기준에 따라 정하는 것

예시) 사용자 A가 사용자 B에게 만원을 송금한다.

* 이때 DB 작업
- 1. 사용자 A의 계좌에서 만원을 차감한다 : UPDATE 문을 사용해 사용자 A의 잔고를 변경
- 2. 사용자 B의 계좌에 만원을 추가한다 : UPDATE 문을 사용해 사용자 B의 잔고를 변경

현재 작업 단위 : 출금 UPDATE문 + 입금 UPDATE문
→ 이를 통틀어 하나의 트랜잭션이라고 한다.
- 위 두 쿼리문 모두 성공적으로 완료되어야만 "하나의 작업(트랜잭션)"이 완료되는 것이다. `Commit`
- 작업 단위에 속하는 쿼리 중 하나라도 실패하면 모든 쿼리문을 취소하고 이전 상태로 돌려놓아야한다. `Rollback`

*commit  : 하나의 트랜잭션이 성공적으로 끝났고, 변경사항을 영구적으로 반영하기 위해 사용되는 연산

*rollback : 하나의 트랜잭션 처리가 비정상적으로 종료되어 이전 상태로 되돌리 때 사용되는 연산

 

ACID

: 트랜잭션의 안정성, 일관성을 보장하기 위한 4가지 속성

Atomicity(원자성) : DB 속성

: 트랜잭션은 모두 성공하거나 모두 실패해야한다.

 

Consistency(일관성) : Application 속성

: 데이터베이스의 상태가 일관되게 유지되어야한다.

: 트랜잭션이 일어난 이후의 데이터베이스는 제약이나 규칙을 만족해야한다.

 

Isolation(격리성) : DB 속성

: 여러 개의 트랜잭션이 병력적으로 진행될 때, 다른 트랜잭션이 진행되고 있는 트랜잭션을 방해할 수 없다.

: 동시에 실행되는 트랜잭션은 서로 격리되는 현상

 

Durablity(지속성) : DB 속성

: 트랜잭션이 성공적으로 완료된 후에는 해당 결과가 영구적으로 유지되어야 한다.

 

DB Lock

: 여러 개의 트랜잭션이 발생할 때 데이터베이스의 일관성을 지키기 위해 접근을 제어하는 기능

: 트랜잭션 처리의 순차성을 보장하기 위한 방법 + 동시성 제어, 일관성 유지 보장을 위해 사용

*데이터베이스마다 lock를 구현하는 방식과 세부적인 방법이 다르다.

 

공유 잠금(share lock or Read lock) : 데이터를 읽을 때 사용

: 공유 lock끼리는 동시에 접근이 가능하다. = 하나의 데이터를 읽는 것은 여러 사용자가 동시에 사용가능

 

배타적 잠금(Exclusive or White lock) : 데이터를 변경하고자 할 때 사용, 트랜잭션이 완료될 때 까지 유지

: 배타적 lock이 해제될 때 까지 다른 트랜잭션(읽기 포함)은 해당 리소스에 접근할 수 없음

 

Dead Lock : 교착상태

: 두 개 이상의 트랜잭션이 상대방이 소유한 자원을 얻기 위해 서로 무한정 대기하는 현상

game_master, game_detail 테이블이 있음

트랜젝션 A : game_master의 id = 5 수정 후 game_detail의 id = 5 수정

트랜젝션 B : game_detail의 id = 5 수정 후 game_master의 id = 5 수정

트랜젝션 A와 트랜젝션 B는 동시에 일어남

---

[수행 과정]

트랜젝션 A → game_master에 베타락 설정

트랜젝션 B → game_detail에 베타락 설정

이와 함께(교차로)

트랜젝션 A는 game_detail의 id = 5에 lock을 설정하려고 할 것이고, 트랜젝션 B도 마찬가지일 거임

하지만, 이미 각 id = 5 들은 서로 다른 트랜젝션에 의해 베타락이 설정되어 있음 

⇒ lock이 해제되기를 기다림 

⇒ lock 해제 안됨 ⇒ **영.원.히. → 데드락**

: 데드락이 발생하면 데이터베이스가 둘 중 한 트랜잭션 에러를 발생시킴으로써 문제를 해결 -> 교착상태 발생 가능성을 줄이기 위해서 접근 순서를 동일하게 하는 것이 중요

* 위의 상황에서는 game_master를 업데이트 한 후 game_detail를 업데이트 한다. 와 같은 규칙을 정해 테이블 접근의 교착상태가 일어나지 않도록 한다.

 

테이블 파티셔닝

: 서비스의 크기가 점점 커지고 DB에 저장하는 데이터의 규모 또한 대용량화 되면서, 기존에 사용하는 DB 시스템의 용량(storage)의 한계와 성능(performance)의 저하 를 가져오게 된다.
: 대용량의 데이터베이스 테이블을 더 작은 논리적 단위로 분할하여 관리하는 기법

 

장점

*관리적 측면 : 파티션 단위 백업, 추가, 삭제, 변경

- 전체 데이터를 손실할 가능성이 줄어들어 데이터 가용성이 향상된다.

- 파티션 별로 백업 및 복구가 가능하다.

- 파티션 단위로 I/O분산이 가능하므로 update 성능이 향상된다.

*성능적 측면 : 파티션 단위 조회 및 DML 수행

- Full  Scan에서 데이터 Access 범위를 줄여 성능 향상을 가져온다.

- 필요한 데이터만 빠르게 조회할 수 있어 쿼리 자체가 가볍다.

 

단점

- table간 join에 대한 비용이 증가한다.

- table과 index를 별도로 파티셔닝 할 수 없다. = table과 index를 같이 파티셔닝 해야한다.

 

수평(horizonal) 파티셔닝

- 하나의 테이블의 각 행을 다른 테이블에 분산시키는 것

특징

: 퍼포먼스 가용성을 위해  key 기반으로 여러 곳에 분산 저장

: 일반적으로 분산 저장 기술에서 파티셔닝은 수평 분할을 의미

 

장점

: 데이터의 개수가 작아지고 따라서 index의 개수도 작아지게 된다. 자연스럽게 성능 향상

단점

: 데이터르 찾는 과정이 기존보다 복잡하기 때문에 latency(지연시간)가 증가 

: 하나의 서버가 고장나게 되면 무결성이 깨질 수 있다.

 

수직(vertical) 파티셔닝

: 테이블의 일부 열을 빼내는 형태로 분할

: 일반적으로 자주 사용되는 열과 그렇지 않은 열을 분리하여 저장시켜 효율을 높임 

:

제 3 정규형(이행적 함수 종속 제)과 같은 개념으로 접근하면 쉬움, 하지만 수직 파티셔닝은 이미 정규화된 데이터를 분리하는 과정

 

장점

: 자주 접근되는 열을 별도로 저장함으로써 특정 쿼리의 처리 성능을 향상시킬 수 있다. = I/O비용감소, 쿼리 실행시간 단출

: 민감한 정보가 포함된 열을 별도의 파티션으로 분리하여 접근 권한을 제어할 수 있다. = 보안상 이점

 

DB파티셔닝 분할 기준

1. 범위 분할(range partitioning)

- 분할 키 값이 범위 내에 있는지 여부로 구분

ex) 우편 번호를 분할 키로 수평 분할 하는 경우

 

2. 목록 분할(list partitioning)

- 값 목록에 파티션을 할당 분할 키 값을 그 목록에 비추어 파티션을 선

ex) Country라는 컬럼의 값이  iceland, Norway, sweden, finland, Denmark 중 하나에 있는 행을 빼낼 때 북유럽국가 파티션을 구축할 수 있다.

 

3. 해시 분할(hash paritioning)

- 해시 함수의 값에 따라 파티션에 포함할지 여부를 결정

ex) 4개의 파티션으로 분할하는 경우 해시 함수는 0~3의 정수를 돌려준다.

 

4. 합성 분할(composite partitioning)

- 상기 기술을 결합하는 것을 의미

ex) 먼저 범위를 분할하고 다음에 해시 분할 같은 것

 

https://gmlwjd9405.github.io/2018/09/24/db-partitioning.html

[DB] DB 파티셔닝(Partitioning)이란 - Heee's Development Blog

Trigger

: 특정 테이블에 insert, delete, update와 같은 DML 문이 수행되었을 때, 데이터베이스에서 자동으로 동작하도록 작성된 프로그램

: 사용자가 호출하는 것이 아닌 데이터베이스에서 자동으로 호출하는 것

 

 

Data Dictionary : 데이터 사전

데이터베이스에 저장되는 테이블, 뷰, 인덱스, 접근 권한 등에 대한 정보를 저장하는 데이터베이스

= 데이터베이스 시스템에서 사용되는 데이터에 대한 정의와 설명을 저장하는 메타데이터의 한 형태

 

데이터 사전의 내용 :  메타데이터

사용자 정보 : 아이디, 패스워드, 성별 등

데이터베이스 객체 정보 : 테이블, 뷰, 인덱스 등

무결성 제약 정보 : constraints

함수, 프로시저, 트리거

 

데이터 사전 검색 

oracle : 뷰로 데이터 사전 접근(DBA, ALL_ , USER_ )

SELECT * FROM DBA_TABLES;
SELECT * FROM ALL_TABLES;

MySQL : 테이블 형태로 구성, Information_schema 라는 데이터베이스에 존재

-- 해당 데이터베이스로 이동
use information_schema;
-- 테이블 목록 조회 
show tables;

 

DB 모델링

DB 매핑

매핑 방법

Merge : 두 엔티티의 타입을 합치기

Foreign Key : 관계형 데이터베이스의 전형적인 방식

Relationship relation 추가 : 관계를 표현하는 새로운 relation 추가

 

Key

: 데이터베이스에서 조건에 만족하는 튜플을 찾거나 순서대로 정렬할 때, 다른 튜플들과 구별할 수 있는 유일한 기준이 되는 속성

 

-Condidate Key : 후보키 - 기본키가 될 수 있는 키

튜플에서 유일하게 식별할 수 있는 속성들의 부분집합

ex) 학생 릴레이션에서 "학번"이나 "주민번호"

 

-Primary Key : 기본키 -> 후보키 중 선택한 메인 키

한 릴레이션에서 특정 튜플을 유일하게 구별할 수 있는 속성

Not Null(개체 무결성의 첫번째 조건)

동일한 값의 중복 저장 불가(개체 무결성의 두번째 조건)

 

Alternate Key : 대체키 : 후보키가 둘 이상일 때 기본키를 제외한 나머지 후보키

 

Super key : 슈퍼키 : 한 릴레이션 내에 있는 속성들의 집합으로 구성된 키

릴레이션을 구성하는 모든 튜플에 대해 유일성 만족 o, 최소성 만족 x

ex) 학생 릴레이션에서 학번, 주민번호, 학번+ 주민번호, 학번 + 주민번호+ 성명

 

Foreign Key : 외래키 : 관계를 맺고 있는 릴레이션 R1,R2에서 릴레이션 R1이 참조하고 있는 릴레이션의 R2의 기본키와 같은 R1릴레이션의 속성

외래키로 지정되면, 참조 테이블의 기본키에 없는 값은 입력 불가(참조 무결성 조건)

ex) <수강> 릴레이션이 <학생> 릴레이션을 참조

-> <학생> 릴레이션의 학번은 기본키 , <수강> 릴레이션의 학번은 외래키

 

개념적 데이터베이스 모델링

사용자 요구에 대한 트랜잭션을 모델링하는 단계

 

논리적 데이터베이스 모델링

트랜젝션의 인터페이스 설계

DBMS에 맞는 논리적 스키마를 설계하는 단계(매핑, 스키마)

*정규화를 수행

*정규화 : 관계형 데이터 모델에서 데이터의 중복성을 제거하여 이상 현상을 방지하고 데이터의 일관성과 정확성을 유지하기 위해 무손실 분해를 하는 과정

물리적 데이터베이스 모델링

논리 데이터 모델을 특정 DBMS의 특성 및 성능을 고려하여 물리적인 스키마를 만드는 단계

테이블, 인덱스, 뷰, 파티션 등 객체 생성

성능 측면에서 *반 정규화를 수행

*반 정규화 : 정규화된 엔티티, 속성, 관계에 대해 성능 향상과 개발 운영의 단순화를 위해 중복, 통합, 분리 등을 수행하는 데이터 모델링의 기법