데이터베이스 첫걸음

1장.  데이터베이스란 - 용도와 역할

데이터 베이스의 4가지 기본 기능

- 데이터 조작 ( 검색 / 등록 / 수정 / 제거 )
- 동시성 제어
- 장애 대응
- 보안

---

2장. 관계형 데이터베이스란 - 가장 대표적인 데이터베이스

* Oracle 이나 MySQL 같은 제품은 'DBMS 이며 데이터베이스가 아니다' 가 바른 표현이다

- 데이터베이스 : 기능이나 구조를 나타내는 추상적인 개념
- DBMS : 그것을 실현하기 위해 작성된 구체적인 소프트웨어
- '데이터베이스'와 'DBMS'는 같은 의미로 사용되는 경우가 많지만, 원래는 '추상'과 '구상'이라는 차이가 있다

 

* 데이터베이스는 OS와 애플리케이션 사이에 낀 중간 소프트웨어(미들웨어)다.

---

4장. 데이터베이스와 아키텍처 구성 - 견고하고 고속의 시스템을 구축하기 위해

* Web 3계층

- 웹 서버, 애플리케이션 서버,  DB 서버

 

* 가용성을 높이는 2가지 전략

- 심장전략(고품질 - 소수전략) : 시스템을 구성하는 각 컴포넌트의 신뢰성을 높여 장애 발생률을 낮게 억제해서 가용성을 높인다. (소수정예 노선).
- 신장전략(저품질 - 다수전략) : 시스템을 구성하는 각 컴포넌트의 신뢰성을 계속해서 높이기보다는 '사물은 언젠가 망가진다'란 체념을 전제로 여분을 준비해 둔다. 이를 철저히 대비하는 것을 '물량작전'이라고 부른다.

 

클러스터란

- 동일한 기능의 컴포넌트를 병렬화하는 것을 '클러스터링(Clustering)' 이라고 부른다.
- 클러스터는 시스템 세계에서는 '동일한 기능의 컴포넌트를 복수 개 준비해 한 개의 기능을 실현한다' 는 의미로 사용한다.
- 또한, 클러스터 구성으로 시스템의 가동률을 높이는 것을 '여유도(Redundancy)를 확보한다' 또는 '다중화' 라고 지칭한다
- 다중화는 시스템 세계에서 '내구성이 더 높고 견고하다'는 좋은 의미가 있다.
    ㆍ같은 기능을 가진 서버를 늘리면 늘릴수록 시스템 전체에서 장애 발생률이 낮아지기 때문에
        이 점에서 컴포넌트 수가 많은 것이 맞는 것처럼 보인다.

 

* 단일 장애점이란

- 다중화되어 있지 않아서 시스템 전체 서비스의 계속성에 영항을 주는 컴포넌트를 '단일 장애점(SPOF : Single Point Of Failure)라고 한다.

 

DB 서버의 다중화

- DB 서버는 오랫동안 클러스터링이 어려운 컴포넌트로 인식된다.

- 그 이유는 데이터를 보존하는 '영속(Persistence) 계층' 이기 때문이다.

- 데이터는 항상 갱신되기 때문에 다중화를 유지하는 중에 '데이터 정합성'도 중요하게 의식해야 한다.

 

가장 기본적인 다중화

- DB 서버만 다중화하고 저장소는 하나만 두는 구성

- DB 서버가 2대 있지만, 2대가 동시에 동작하는 것을 허락할지에 따라 나뉜다.

    ㆍActive-Active : 클러스터를 구성하는 컴포넌트를 동시에 가동한다. Oracle, DB2 만 사용가능

         ▷ 시스템 다운 시간이 짧다.

         ▷ 성능이 좋다. - 단, 저장소가 병목(버틀넥) 되기 때문에 성능 향상이 생각한 만큼 안될 수 도 있다.

    ㆍActive-Standby : 클러스터를 구성하는 컴포넌트 중 실제 가동하는 것은 Active, 남은 것은 대기(Stanby) 하고 있는다.

 

* Heartbeat

- Active-Standby 구성에서 Standby DB 서버는 일정 간격으로 Activce DB에 이상이 없는지를 조사하기 위한 통신

 

* Active-Standby

- Cold-Standby : 평소에는 Standby DB가 작동하지 않다가 Active DB가 다운된 시점에 작동하는 구성이다.

- Hot-Standby : 평소에도 Standby DB가 작동하는 구성이다.

 

 

리플리케이션(Replication) 이란?

- DB서버와 저장소 세트를 복수로 준비하는 것

- 리플리케이션에서 중요한 점은 Active 측 저장소의 데이터는 항상 사용자로부터 갱신된다는 것이다.

    ㆍStandby 측의 갱신 주기를 얼마로 할 것인가와 성능 사이에 트레이드오프 관계 발생

- 이 리플레이션 구성은 원칙적으로 차례대로 손자나 증손자 세트를 만들 수 있다.

    ㆍ형태를 따서 이런 구성을 피라미드형 이라고 부른다.

- 피라미드형 리플리에케이션은 '데이터가 오래되도 참조만 하면된다' 는 처리를 손자나 증손자 세트에 하기 때문에 편리하다

    ㆍ이를 통해 부모에 걸리는 부하를 분산할 수 있다.

- 다만 그만큼 DB 서버의 라이선스료와 서버, 저장소의 비용이 들고, 시스템을 구성하는 노력도 증가한다.

 

* 데이터베이스의 아키텍처 패턴

- Stand-alone

- 클러스터링

    ㆍShared Nothing

    ㆍShared Disk

         ▷ Active-Active

         ▷ Active-Standby

            ㆍHot-Standby

            ㆍCold-Standby

- 리플리케이션

    ㆍ마스터 슬레이브

    ㆍ멀티 마스터

 

정리

- 데이터베이스에서 가용성을 높이는 기술은 '클러스터링'과 '리플리케이션'으로 크게 나눌 수 있다.

- 클러스터링에는 'Active-Active'와 'Active-Standby' 가 있다.

- Active-Active는 DB서버의 부하 분산이 가능하지만, 저장소가 단일 병목 지점이 된다.

- 저장소라는 병목 지점이 생기는 것은 웹 서버와 애플리케이션 서버와는 다른 점인데, 이 때문에 데이터베이스가 성능 문제를 쉽게 일으킨다.

- 저장소의 병목을 해소하기 위한 기술로서 'Shared Nothing' 클러스터라는 선택지도 있지만, 이용할 수 있는 상황은 한정된다.

- 리플리케이션은 데이터를 물리적으로 원격 보관하여 가용성을 높이기 위한 기술이지만, 부하 분산에 이용하면 성능 향상을 위해서도 사용될 수 있다.

- 클러스터링도 리플리케이션도 만드는 데는 그에 따른 비용과 작업이 필요하므로 트레이드오프가 발생한다.

---

5장. DBMS를 조작할 때 필용한 기본 지식 - 조작하기 전에 알아두어야 할 것

- 데이터베이스는 4계층 또는 3계층으로 구조화되어 있다.

    ㆍ계층은 위에서부터 차례로 인스턴스 → 데이터베이스 → 스키마 → 오브젝트이다.

    ㆍ3계층 구조는 데이터베이스와 스키마 계층을 실질적으로 생략하고 있다. ( oracle과 MySQL )

         ▷ MySQL : 인스턴스 - 스키마 - 오브젝트

         ▷ oracle : 인스턴스 - (데이터베이스 1개만 생성가능 하므로 생략) - 스키마 - 오브젝트 

---

6장 . SQL 문의 기본 - SELECT 문의 이해

SQL의 기초적인 기술 규칙

- SQL문의 마지막에 딜리미터(Delimiter)를 붙인다.

- 키워드의 대문자와 소문자는 구별하지 않는다. 즉, select도 SELECT도 Select도 같은 의미다.

- 정수는 그대로 쓴다. 문자열이나 날짜 시각은 작은 따음표('')로 감싼다. 즉, 수치로 값을 지정하는 겨우는 100000이지만, 문자열로 지정하는 경우는 '100000'이 된다.

 

정렬 수행 시 주의할 점

- ORDER BY에 의한 정렬을 수행할 때 행의 순서를 확실히 같게 하려면 행의 정렬키를 한 가지 의미(Unique)로 정해야 한다.

- 바꿔 말하면, 정렬키가 같은 값의 행이 복수 개 존재한다면 그 행들의 순서는 일정하지 않는다.

 

테이블을 요약하는 함수

- 함수는 크게 나워 다음 2종류가 있다.

    ㆍ복수 행(이나 행의 값)에 대해 집계를 수행하는 함수

    ㆍ단일행의 값에 대해 조작이나 계산을 수행하는 함수

- 집약함수(집계함수)

COUNT	테이블의 행수를 알려주는 함수	- COUNT 함수만은 'COUNT(*)'로 표기하여    NULL을 포함한 전체 행을 집계한다.
SUM	테이블의 수치 데이터를 합계하는 함수
AVG	테이블의 수치 데이터 평균을 구하는 함수
MAX	테이블의 임의열 데이터 중 최대값을 구하는 함수
MIN	테이블의 임의열 데이터 중 최소값을 구하는 함수

    ㆍ집약함수는 기본적으로 NULL을 제외하고 집계한다.

 

집약한 결과에 조건 지정

SELECT ~ FROM ~ GROUP BY ~ HAVING 그룹의 값에 대한 조건

SELECT ~ FROM ~ WHERE ~ GROUP BY ~ HAVING ~ ORDER BY ~

 

UPDATE 테이블명 SET 열명 = 값;
INSERT INTO 테이블명 VALUES 값;
DELETE FROM 테이블명;

 

내부결합 ( inner join )

- 지정한 결합 조건에 일치하는 행만을 2개의 테이블로부터 가져온다.

SELECT 선택하고 싶은 열 리스트 FROM 첫 번째 테이블명 INNER JOIN 두 번째 테이블명 ON 결합 조건;

외부결합 ( outer join )

- 행수(기준으로 삼은 테이블의 행수)를 고정해 화면에 표시하고 싶을 때나 기준이 되는 테이블에 데이터를 등록하고 세부사항은 다른 테이블에 순차적으로 저장하고 싶은 경우에 사용

SELECT 선택하고 싶은 열의 리스트 FROM 첫 번째 테이블명 LEFT OUTER JOIN 두 번째 테이블명 ON 결합 조건;

 

DBMS의 NULL
- 1. SQL로 코딩할 때 인간의 직감에 반하는 3개의 논리 값(true, false 외에 추가로 NULL로 인한 unknown) 을 고려해야 한다.

- 2. 사칙연산 또는 SQL 함수의 인수에 NULL이 포함되면 'NULL의 전파'가 일어난다.

- 특히 1번에서 'IS NULL'이 아닌 '= NULL'이라고 쓴다면 생각한 결과를 얻을 수 없는 경우가 흔하다.

    ㆍ이는 NULL에 '=' 를 적용한 결과는 반드시 'unknown'이 되기 때문이다.

- 원칙적으로는 NULL을 사용하지 않아야 하지만, 기존 테이블에 정의되어 있다면 변경이 어렵다.

    ㆍMySQL에는 '<=>' 라는 독자적인 연산자가 있어서 A, B 어느 쪽에 NULL이 포함되어 있어도 'A <=> B'로 올바르게 비교할 수 있다.

    ㆍ즉, 일반적인 연산자와 달리 'NULL의 전파' 가 일어나지 않는다.

- 또한, SQL 표준에는 NULL이 섞여 비교를 수행하는 'IS NOT DISTINCT FROM' 이 정의 되어 있다.

    ㆍ이걸 구현한 DBMS에서는 'A IS NOT DISTINCT FROM B'로 동일한 결과를 얻을 수 있다.

 

정리

- 클라이언트가 사용하는 것은 SQL(DML/DCL/DDL)과 관리 명령이다.

- DML(데이터 조작 언어)은 SELECT 문과 넓은 의미의 갱신(UPDATE/INSERT/DELETE)가 있다.

---

7장. 트랜잭션과 동시성 제어 - 복수의 쿼리 통합

트랜잭션

- 한 덩어리의 쿼리 처리 단위

- ACID 특성 ( 트랙잭션의 4가지 특성 )

    ㆍAtomicity(원자성)

    ㆍConsistency(일관성)

    ㆍIsolation(고립성 또는 격리성)

    ㆍDurability(지속성)

 

원자성

- 데이터의 변경(INSERT/DELETE/UPDATE)을 수반하는 일련의 데이터 조작이 전부 성공할지 전부 실패할지를 보증하는 구조

일관성

- 일련의 데이터 조작 전후에 그 상태를 유지하는 것을 보증한다.

- 예를 들어 유니크 제약을 설정하면 중복된 사용자 번호를 저장할 수 없다.

고립성

- 일련의 데이터 조작을 복수 사용자가 동시에 실행해도 '각각의 처리가 모순없이 실행되는 것을 보증한다'

- 데이터 베이스에는 데이터베이스 오브젝트인 테이블에 대해 '잠금'을 걸어 후속 처리를 블록하는 방법이 있다.

    ㆍ잠금 단위에는 테이블 전체, 블록, 행 등이 있다.

    ㆍMySQL에서는 트랜잭션 처리를 할 때 주로 행 단위의 잠금 기능을 이용한다.

- '각각의 처리가 모순 없이 실행되는 것을 보증한다'

    ㆍ병렬로 실행되지 않는(직렬) 상태를 말한다.

- DBMS에서 격리 수준으로 구현하고 제공하는 것이 '직렬화 가능' 이다.

- 격리 수준

    ㆍ커밋되지 않은 읽기(Read Uncommitted)

    ㆍ커밋된 읽기(Read Committed)

    ㆍ반복 읽기(Repeatable Read)

    ㆍ직렬화 가능(Serializable)

- 격리 수준의 완화에 따라 일어나는 현상

    ㆍ더티 읽기(Dirty Read) : 어떤 트랜잭션이 커밋되기 전에 다른 트랜잭션에서 데이터를 읽는 현상

    ㆍ애매한 읽기(Fuzzy/NonRepeatable Read) : 어떤 트랜잭션이 이전에 읽어 들인 데이터를 다시 읽어 들일 때

                                                                              2회 이후의 결과가 1회때와 다른 현상이다.

    ㆍ팬텀 읽기(Phantom Read) : 어떤 트랜잭션을 읽을 때 선택할 수 있는 데이터가 나타나거나 사라지는 현상

- 3가지 현상과 격리 수준의 관계

격리 수준	더티읽기	애매한 읽기	팬텀 읽기
커밋되지 않은 읽기	O	O	O
커밋된 읽기	X	O	O
반복 읽기	X	X	O
직렬화 가능	X	X	X

지속성

- 일련의 데이터 조작(트랜잭션 조작)을 완료(COMMIT)하고 완료 통지를 사용자가 받는 시점에서 그 조작이 영구적이 되어 그 결과를 잃지 않는 것을 나타낸다.

 

DBMS에서의 트랜잭션

1. DDL에 따른 암묵적인 커밋

- MySQL이나 Oracle에서는 CREATE TABLE과 같은 DDL 실행 시 암묵적인 커밋이 발행한다.

 

2. 오토커밋 설정

- 트랜잭션의 개시(BEGIN TRANSACTION, START TRANSACTION, SET TRANSATION 등)가 명시적으로 지정되지 않았을 때 트랜잭션을 구별하는 방법 2가지

    ㆍ하나의 SQL문이 하나의 트랜잭션으로 구분된다.

    ㆍ사용자가 COMMIT 또는 ROLLBACK을 실행하기까지가 하나의 트랜잭션이 된다.

    ㆍ기본 설정이 오토커밋 모드인 DBMS에는 MySQL, PostgreSQL, SQL Server 등이 있다.

 

데이터 정의 언어(DDL)	- 스키마 또는 테이블 등을 작성하거나 제거한다. - CREATE, DROP, ALTER
데이터 조작 언어(DML)	- 테이블의 행을 검색하거나 변경하는데 사용한다. - SELECT, INSERT, UPDATE, DELETE
데이터 제어 언어(DCL)	- 데이터베이스에서 실행한 변경을 확장하거나 취소하는 데 사용한다. - COMMIT, ROLLBACK

 

MySQL 의 특성

1. 읽기를 수행할 경우 갱신 중이라도 블록되지 않는다.(읽기와 읽기도 서로 블록되지 않는다)

2. 읽기 내용은 격리 수준에 따라 내용이 바뀌는 경우가 있다.

3. 갱신 시 베타적 잠금을 얻는다. 잠금은 기본적으로 행 단위로 얻으며 트랜잭션이 종료할 때까지 유지한다. 격리 수준이나 InnoDB의 설정에 따라 실제로 잠금 하는 행의 범위가 다른 경우가 있다.

4. 갱신과 갱신은 나중에 온 트랜잭션이 잠금을 획득하려고 할 때 블록된다. 일정 시간을 기다리며 그 사이에 잠금을 획득할 수 없는 경우에는 '잠금 타입아웃(Lock Timeout)'이 된다.

5. 갱신하는 경우 갱신 전의 데이터를 UNDO 로그로 '롤백 세그먼트' 라는 영역에 유지한다. 이 'UNDO 로그'는 용도가 2가지인데, 첫 번째는 갱신하는 트랜잭션의 롤백 시 갱신 전으로 되돌리는 것이고, 두 번째는 복수의 트랜잭션으로부터 격리 수준에 따라 대응하는 갱신 데이터를 참조하는 데 이용한다. ( 같은 행을 갱신할 때마다 UNDO 로그가 작성되어 같은 행에 대한 복수 버전이 존재하며 이에 의해 1번과 2번를 실현하고 있다)

잠금 타임아웃이란

- '갱신'과 '참조'는 서로를 블록하지 않지만, '갱신'과 '갱신'이 부딪치는 경우에는 나중에 온 갱신이 잠금 대기 상태가 된다.

- 잠금을 건 쪽이 언제 잠금을 풀지 알 수 없어서 잠금 해제를 기다리고 있는 쪽에서는 잠금을 기다리거나 기다리지 않거나, 기다린다면 어느 정도 기다릴지를 설정할 수 있다.

- MySQL의 경우 'innodb_lock_wait_timeout' 이란 시스템 변수로 설정

- 이때 자금 대기로 타임아웃이 발생하는 경우 DBMS로부터 롤백되는 다위가 다를 때가 있는데, 해당 트랜잭션 전체를 롤백하는 경우와 쿼리만 롤백하는 것이다.

    ㆍMySQL에서 자금 대기로 타임아웃이 발생하면 롤백되는 것은 기본으로 오류가 발생한 쿼리이다.

    ㆍ트랜잭션 전체를 롤백하고 싶다면?

         ▷ 타임아웃 오류 후 명시적으로 ROLLBACK을 실행한다.

         ▷ innodb_rollback_on_timeout 시스템 변수를 설정한다.

 

교착 상태란

- 잠금을 유지한 채 서로 잠금을 건 자원에 잠금이 필요한 처리(INSERT/UPDATE/DELETE)를 실행하면 아무리 기다려도 상황이 바뀌지 않는 상태

- 잠금 타임아웃은 일정 시간 기다리면 상황이 개선될(잠금을 건 곳에서 잠금을 푼다) 가능성이 있지만, 교착 상태는 상황이 개선될 가능성이 없다.

- MySQL이 교착상태가 일어나면 이를 즉시 인식해 시스템에 영향이 작은 쪽의 트랜잭션을 트랜잭션 개시 시점까지 롤백한다.

    ㆍ애플리케이션 쪽에서는 항상 트랜잭션이 교착 상태를 일으켜 롤백되는 경우 트랜잭션을 재실행할 수 있는 구조를 만들어야한다.

 

교착 상태의 발생 빈도를 낮출려면?

- DBMS 전반적인 대책

1. 트랜잭션을 자주 커밋한다. 이에 따라 트랜잭션은 더 작은 단위가 되어 교착 상태의 가능성을 낮춘다.

2. 정해진 순서로 테이블(그리고 행)에 액세스하게 한다. 예를 들어, 앞의 교착 상태의 예에서는 트랜잭션 A가 [테이블 a → 테이블 b] 순으로 액세스했고 트랜잭션 B가 [테이블 b → 테이블 a] 순으로 액세스하고 있는데, 이를 어느 트랜잭션이라도 [테이블 a → 테이블 b]처럼 동일한 순서로 액세스하게 한다.

3. 필요 없는 경우에는 읽기 잠금 획득(SELECT ~ FOR UPDATE 등) 의 사용을 피한다.

4. 쿼리에 의한 잠금 범위를 더 좁히거나 잠금 정도를 더 작은 것으로 한다. 예를 들어, 행의 잠금을 사용할 수 있는 경우에는 사용한다. MySQL은 트랜잭션의 격리 수준을 되도록 '커밋된 읽기'로 한다(InnoDB의 기본 격리 수준은 반복 읽기)

5. 한 테이블의 복수 행을 복수의 연결에서 순서 변경 없이 갱신하면 교착 상태가 발생하기 쉽다. 동시에 많은 연결에서 갱신 때문에 교착 상태가 자주 발생한다면 테이블 단위의 잠금을 획득해 갱신을 직렬화하면 동시성은 떨어지지만 교착 상태는 회피할 수 있어서 전체 처리로 보면 좋은 예도 있다.

 

- MySQL(InnoDB)의 대책

6. 테이블에 적절한 인덱스를 추가해 쿼리가 이를 이용하게 한다. 인덱스가 사용되지 않는 경우에는 필요한 행의 잠금이 아닌 스캔한 행 전체에 대해 잠금이 걸리게 된다.

 

- 또한, 교착 상태는 클라이언트에서 롤백된 트랜잭션의 오류, 서버에서 오류 로그나 명령으로 확인할 수 있다.

- 클라이언트-서버형의 DBMS에는 타임아웃, 교착상태, 커넥션-네트워크 오류, 일시적인 상태 오류 등이 발생할 수 있다고 자각하고 상한 횟수를 정한 재시도 등의 처리나 트랜잭션을 재실행해 트러블을 해결하는 데 힘써야 한다.

 

해서는 안되는 트랜잭션 처리

주의1. 오토커밋

- '오토커밋'이란 쿼리 단위로 커밋하는 설정이다.

- 애플리케이션의 잠금을 실행하는 데는 커밋의 부하가 너무 높다.

- 일정 수 이상의 갱신을 수행하는 처리나 트랜잭션의 기능(복수의 쿼리를 정리해 커밋과 롤백한다. 여러 번 실행하는 SELECT로 애매한 읽기나 팬텀을 막는다) 등은 적절한 단위와 트랜잭션 격리 수준에서 트랜잭션을 이용해 오토커밋을 사용하지 않도록 한다.

 

주의2. 긴 트랜잭션

- 긴 트랜잭션은 데이터베이스 트랜잭션의 동시성이나 자원의 유효성을 저하한다.

 

* 대량 처리를 한 개의 트랜잭션이 실행한다

- 대량의 갱신 처리를 한 개의 트랜잭션으로 실행하면 트랜잭션으로 이 대량 갱신 처리를 롤백하기 위한 대량의 UNDO 로그를 트랜잭션 종료까지 유지해야한다.

- UNDO 로그가 불필요해진 시점에 해당 영역은 해제되어 다시 사용되지만, OS 파일 시스템에서는 크기가 줄어들지 않는다.

- 쓸데없이 UNDO 로그의 크기가 큰 경우가 있다.

- 이를 막기 위해 대량 처리는 적당한 크기의 트랜잭션으로 나눠서 실행하는 것을 추천

 

* 아무것도 하지 않는 트랜잭션을 유의한다

- 정말로 아무것도 하지 않는 트랜잭션이 있다면 문제는 없다.

- 하지만 SELECT 하고 아무것도 하지 않고 트랜잭션을 열어 둔다면?

    ㆍ같은 테이블에 갱신을 실행할 때 이 테이블의 반복 읽기를 유지하기 위해 UNDO 로그가 계속 유지된 상태가 된다.

 

* 트랜잭션 중에 대화 처리를 넣는다

- 일반적인 DBMS의 트랜잭션은 매우 빡빡한 처리를 동시에 실행할 수 있는 구조를 갖추고 있다.

- 이것을 효율적으로 다루려면 트랜잭션은 되도록 작게 하고 트랜잭션을 구성하는 내용에는 언제 끝날지 알 수 없는 불명확한 처리를 포함해서는 안된다.

- 불명확한 처리 중 으뜸은 사용자와의 대화 처리이다.

    ㆍ사용자와의 대화 처리는 일반적인 트랜잭션에서 액션과 비교하면 매우 커서

        타임아웃을 설정하지 않는 한 끝없이 사용자의 처리를 기다리게 되고 , 결국 시스템 전체의 효율을 떨어뜨리게 된다.

- 이러한 처리는 트랜잭션 내에 넣지 않아야 하고, 어쩔 수 없이 넣는 경우에도 반드시 상한을 정해서 무한으로 기다리는 일이 없도록 해야 한다.

 

* 처리능력 이상의 트랜잭션 수

- 커넥션이 잘 작동하는 수나 동시 실행할 수 있는 수의 상한을 어느 정도로 설정해야 할지는 시스템의 요건이나 하드웨어 성능에도 좌우되므로 최적의 트랜잭션 수는 부하 실험을 수행해 측정하는 수 밖에 없다.

 

정리

- DBMS의 트랜잭션은 'ACID'의 특성이 있다.

- 트랜잭션은 원자성에 의해 전부 성공하거나 전부 실패하는 원칙으로 동작한다.

- 고립성에 따라 병렬 실행이 일관성 있게 수행된다

- 트랜잭션에는 4가지 격리 수준이 있는데, 직렬화 가능 이외의 수준을 선택하면 3가지의 현상이 발생한다. 실제 운용에서는 '커밋된 읽기' 또는 '반복 읽기'의 격리 수준을 이용한다.

- 트랜젹션에서 잠금 대기와 교착 상태는 피할 수 없으므로 적절히 대처하는 것이 필요하다.

- 오토커밋은 쿼리 단위로 커밋하는 설정인데, 주의해서 사용하지 않으면 트랜잭션의 헤택을 받지 못할뿐더러 성능에 악영향을 미친다.

---

8장. 테이블 설계의 기초 - 테이블의 개념과 정규형

- 관계형 데이터베이스에서 테이블은 '어떤 공통의 속성을 가진 것의 집합'을 나타내야 하고, 이것이 테이블 설계의 제1 규칙이다.

- '반드시 기본키를 설정할 것' : '한 개 테이블의 내용에는 중복 행을 허용하지 않는다'

    ㆍ데이터베이스 사용 여부와 관계없이 데이터를 고유하게 식별할 수 있는 기본키를 할당하는 것은 데이터 관리의 기본

 

- 기본키의 값이 바뀌면 왜 곤란한가?

    ㆍ1. 변경후 값의 유일성을 보증할 수 없다.

    ㆍ2. 과거 데이터와의 결합(매칭)이 어렵다.

 

정규형이란

- 테이블을 한마디로 정리하면 '고유한 기본키를 가진 공통점에 의해 정리된 것들의 집합'

 

제 1정규형(1NF)

- '테이블 셀에 복합적인 값을 포함하지 않는다'

    ㆍ복합적인 값의 예로는 배열이 있다.

- 제 1정규형이란 '스칼라 값(단일값) 이외의 값을 포함하지 않는 테이블'

 

- ex) 제1정규형 테이블이지만 문제있는 경우.

사원ID	이름	나이	성별	피보험자1	피보험자2
				박초롱	null
				이수빈	이수인

    ㆍ피보험자가 계속들어남에 따라 컬럼수를 늘려야한다. → 피보험자 테이블을 따로 만드는게 좋아보인다.

 

* 함수 종속성(Funtional Dependency) : 기본키와 다른 열 사이에 성립하는 함수적인 유일성

 

제 2정규형(2NF)

 - 부분함수 종속은 간단히 말하면 '기본키를 구성하는 열의 일부에만 함수 종속이 존재하는 것'

    ㆍ부분 종속이 존재할 경우 해당 키와 종속하는 열만 다른 테이블로 만들어 외부로 꺼내야 한다.

- ex) 제 2정규형 아님.

고객기업 ID	주문번호	주문접수일	고객기업명	고객기업 규모
CA	001	2014/12/20	A 상사	대규모
CA	002	2014/12/21	A 상사	대규모
CB	001	2014/12/12	B 건설	중규모
CB	002	2014/12/25	B 건설	중규모
CB	003	2014/12/25	B 건설	중규모
CC	001	2014/12/1	C 화학	소규모

    ㆍ{고객기업 ID} → {고객 기업명}

    ㆍ{고객기업 ID} → {고객 규모}

- ex) 제 2정규형 적용.

고객기업ID	주문번호	주문접수일
CA	001	2014/12/20
CA	002	2014/12/21
CB	001	2014/12/12
CB	002	2014/12/25
CB	003	2014/12/25
CC	001	2014/12/1

고객기업ID	고객기업명	고객기업 규모
CA	A 상사	대규모
CB	B 건설	중규모
CC	C 화학	소규모

 

* 갱신이상 : 갱신 시의 데이터 부정합

- 비정규형 테이블은 대체로 이 갱신 이상의 위험이 높은 테이블

- 기본키가 1개 열밖에 없는 겨우 자동으로 제2정규형을 만족하게 된다.

 

제 3정규형(3NF)

- ex) 제 3정규형 아님.

고객 기업 ID	고객기업명	고객기업규모	업계코드	업계명
CA	A상사	대규모	D001	석유
CB	B건설	중규모	D002	건설
CC	C화학	소규모	D003	바이오

    ㆍ{업계코드} → {업계명}

 

* 추이함수 종속 : 기본키 이외의 키 간에 발생하는 함수 종속

- ex) 제 3정규형

고객기업 ID	고객기업명	고객기업 규모	업계코드(FK)
CA	A상사	대규모	D001
CB	B건설	중규모	D002
CC	C화학	소규모	D003

업계코드	업계명
D001	석유
D002	건설
D003	바이오

 

- '부분함수 종속'과 '추이함수 종속'을 머리에 새겨두고 기계적으로 테이블을 나누기 바란다.

    ㆍ이렇게 하면 90%는 능숙하게 진행할 수 있다. (남은 10%는 성능을 고려해 비정규화하는 경우이다.)

- 이론적으로 제4정규형이나 제5정규형이라는 더 고차원의 정규형도 있지만, 실무에서는 거의 이용할 기회가 없기에 이론적인 흥미가 없다면 무리해서 배우지 않아도 업무에는 지장이 없다.

 

ER 다이어그램

- 정규화를 수행하면 테이블이 나누어져 그 수가 증가한다.

    ㆍ정규화의 목적은 '갱신 이상'의 위험을 없애는 것이 목적이다.

- 테이블 간의 관련성을 한눈에 알 수 있게 고안한 기술이 ER 다이어그램(Entity-Relationship Diagram) 이다.

- 'Entity(엔티티, 실체)'란 '테이블', 'Relationship(괸계, 관련성)' 이란 '테이블 간의 관계'를 의미한다.

 

정리

- 테이블이란 집합이다

- 테이블이란 함수다

- 앞의 2가지를 기억하면 테이블 설계는 끝났다고 봐도 무방하다.

- 그래도 불안한 사람은 '제2정규형'과 '제3정규형'의 규칙을 기억해 두면 좋다.

- 정규화를 해서 테이블 수가 증가하는 경우 'ER 다이어그램'을 그려서 정리하면 좋다.

---

9장. 백업과 복구 - 장애에 대비하는 구조

지속성과 성능이 양립하는 구조

- ACID의 'D'는 지속성(Durability)으로, 일련의 데이터 동작(트랜잭션 동작)을 완료(COMMIT)하고 완료 통지를 사용자가 받는 시점에서는 그 동작이 '영속화'되어 결과를 잃어버리지 않는 것을 나타낸다.

    ㆍ이는 시스템이 정상일 때뿐만 아니라 데이터베이스 서버나 OS의 비정상적 종료 등의 시스템 장애에 견딜 수 있다는 것을 의미;

 

로그 선행쓰기

- 로그 선행 쓰기(WAL, Write Ahead Log)의 기본 개념은 데이터베이스의 데이터 파일 변경을 직접 수행하지 않고, 우선 로그로 변경 내용을 기술한 로그 레코드를 써서 동기화하는 구조이다.

- 장점

    ㆍ1. 디스크에 연속해서 쓰기 때문에 무작위로 쓰는 것보다 성능이 좋다.

    ㆍ2. 디스크에 쓰는 용량과 횟수를 줄일 수 있다.

    ㆍ3. 데이터베이스 버퍼를 이용해 데이터베이스의 데이터 파일로의 변경을 효율성 높게 수행한다.

 

데이터베이스 버퍼

- 커밋 시에는 WAL에 변경 내용을 쓰기 때문에 데이터 파일의 변경 내용은 트랜잭션이 커밋되면서 동시에 동기화할 필요가 없다.

- 그렇다고 트랜잭션마다 버퍼를 취해 비동기적인 쓰기를 하면 로그와 데이터 파일 간 일관성을 유지하기 어렵다.

- 그래서 일반적인 DBMS에서는 '데이터베이스 버퍼'를 준비해 데이터 파일로의 입력을 데이터베이스 버퍼 경우로 일원화해서 단순화하고 있다.

- 이 때문에 효율적으로 데이터 일관성을 유지할 수 있다.

- MySQL의 경우 갱신의 흐름

    ㆍ1. 갱신 대상의 데이터를 포함한 페이지(버퍼나 캐시를 다루는 단위)가 버퍼 풀에 있는지를 확인하고 없다면

           데이터 파일로부터 읽어 들인다.

    ㆍ2. 버퍼 풀의 해당 페이지에서 갱신을 수행한다.

    ㆍ3. 2번의 갱신 내용이 커밋과 함께 로그에 기록된다.

           버퍼 풀에 갱신되었지만, 아직 데이터 파일에 써지지 않은 페이지는 버퍼 풀 내에서 더티 페이지로 다룬다.

    ㆍ4. 데이터 페이지는 나중에 적당한 타이밍에 정리되어 데이터 파일로 써진다(이것을 '체크포인트'라고 부른다.)

    ㆍ5. 4번의 체크포인트 이전 로그 파일은 불필요해진다. 또한, 갱신을 더불어 1번부터 순서가 반복된다.

    ㆍ* 더티페이지 : 일반적으로 메모리로 읽어서 갱신된 페이지

- 즉, WAL과 버퍼 풀에 갱신을 반영해가며 데이터 파일보다 앞질러가는 형태가 되며, 체크포인트에서 데이터 파일이 수정사항을 따라잡고 WAL과 버퍼 풀이 선행해서 수정하기를 반복한다.

 

 크래스 복구

- WAL과 데이터베이스 버퍼, 데이터베이스 파일 3가지가 연계 플레이로 지속성을 담보하면서 현실적인 성능으로 DBMS가 동작하고 있다.

- 크래시(Crash)(예를 들면, MySQL 서버의 비정상적 종료) 가 발생한 경우 어떻게 복구하는지 살펴보자

    ㆍ1. WAL : 마지막으로 커밋된 트랜잭션의 갱신 정보를 가진다.

    ㆍ2. 데이터베이스 버퍼 : 크래시로 내용이 전부 소실된다.

    ㆍ3. 데이터베이스 파일 : 최후 체크포인트까지의 갱신 정보를 가진다.

- 크래시 이후 MySQL 서버를 재시작하면 3번과 1번의 체크포인트 이후 갱신 정보를 사용해 데이터베이스 파일을 크래시 때까지 커밋된 최신 상태로 수정한다. 

    ㆍ이 동작을 롤 포워드(Roll-Forword)라고 한다.

 

 

- 논리적인 파괴(DDL 문에 따른 테이블의 파기 등)나 물리적인 파손(디스크 장치의 고장 등)에는 대응이 힘들다.

    ㆍ주기적으로 백업하고 이를 이용해 복원이나 복구하는 것이 좋다.

 

백업과 복구

- PITR(Point-in-time Recovery) : 임의의 시점에서의 데이터 변경을 포함한 변경

    ㆍ아카이브 지정 : 크래시 복구용으로는 불필요한 로그도 PITR용으로 보존이 필요할 수 있으며 이를 위한 모드

 

백업의 3가지 관점

 1. 핫 백업과 콜드 백업

- 데이터베이스의 상태따라 구분

- 핫백업은 '온라인 백업' 이라고도 하며, 백업 대상의 데이터베이스를 정지하지 않고 가동한채로 백업 데이터를 얻는다.

    ㆍMySQL에서는 트랜잭션 구조를 이용하기도 하고 특수한 로그를 지정하거나 OS 또는 하드웨어의 스냅샷을 이용해

       해당 시점의 스냅샷을 백업 데이터로 취득하는 방법이 있다.

- 콜드 백업은 '오프라인 백업'이로 불리며, 백업 대상의 데이터베이스를 정지한 후 백업 데이터를 얻는다.

    ㆍMySQL에서는 MySQL 서버를 셧다운해서 데이터 디렉토리에 있는 디렉터리와 파일을 전부 OS 명령으로 복사한다.

- 콜드 백업에서는 'OS의 기능'으로 백업하지만, 핫 백업에서는 주로 '데이터베이스의 기능'으로 백업 데이터를 얻는다.

 

2. 논리 백업과 물리 백업

- '형식'에 따라 구분

구분	논리 백업	물리 백업
설명	- SQL 기반의 텍스트 형식으로 백업 데이터가 기록	- 데이터 영역을 그대로 덤프(데이터 파일이나 화면에 출력)하는 이미지로 바이너리 형식으로 기록
장점	- 편집할 수 있다. 텍스트를 변경해 백업된 내용 일부를 수정할 수 있다. - 이식성이 우수하다. 텍스트 변경으로 (CSV 등은 변경없이) 동일한 DBMS의 다른 버전이나 다른 DBMS에 복원할 수 있다.	- 최소 크기로 데이터를 얻을 수 있다. 데이터의 교환이 없어서(또한 최소) 백업과 복원 속도가 빠르다.
단점	- 물리 백업보다 크기가 크다. 바이너리와 텍스트 상호교환에 들어가기 위한 백업과 복원의 동작 속도가 느리다.	- 복원 단위는 도구에 따라 다르며 일부 데이터의 교환이나 적용 등이 불가능하다. - 플랫폼 의존 바이너리는 동일한 DBMS라도 호환되지 않는다.

 

3. 풀 백업과 부분(증분/차등) 백업

- 백업 시 대상과 이에 따른 데이터의 양을 중심으로 구분

구분	풀 백업	부분 백업
설명	- '전체 백업'이라고도 하며, 데이터베이스 전체 데이터를 매일 백업하는 방식	- 우선 풀 백업을 한후에 이후 갱신된 데이터를 백업한다.
장점	- 백업 데이터가 한군데에 모여 있어서 복원 처리가 단순하다	- 갱신한 데이터만을 대상으로 하므로 백업에 필요한 시간이 짧고, 백업 데이터의 용량이 작아도 문제없다.
단점	- 데이터베이스 전체를 백업하므로 백업에 걸리는 시간이 길다 - 갱신량이 적어도 매일 데이터베이스 전체를 백업하므로 백업 데이터를 저장하는 데 충분한 용량이 필요하다	- 복원에는 풀 백업과 부분 백업이 필요해서 복원 절차가 복잡하다.

- 부분 백업

    ㆍ차등 백업 : 최근에 풀 백업한 이후에 갱신된 데이터를 백업

    ㆍ증분 백업 : 최근 백업(종류는 풀 백업에 한정되지 않는다.)한 후 갱신된 데이터를 백업 

 

데이터베이스 관리 시 주의점

- 백업 파일들은 떨어진 곳에 각각 보관하는 것이 중요하다.

- 전체를 하나의 디스크에 보관하는 것은 절대해서는 안된다.

- 장애 대비 방법을 선택할 때,

    ㆍ예를 들어 '24시간 가동해야 한다' 라는 조건이 있다면 '핫 백업'이 필요하다.

    ㆍ또는 VLDB(Very Large DB)를 운용한다면 논리 백업으로는 시간이 너무 오래 걸리므로 물리 백업을 해야한다.

    ㆍ또한, 갱신 범위가 매우 한정되어 있다면 차등 백업이 유효할 수 있다.

    ㆍ대조적으로 '데이터베이스가 오전 9시부터 오후 9시까지만 동작하면 되고, 문제가 있다면 전날 백업 내용으로 돌려도 상관없다'면

       데이터베이스를 정지하고 콜드 백업을 1일 1회 실행하면 된다.

 

정리

- 데이터베이스에는 ACID 특성 중 'Durability(지속성)'에 의해 커밋된 내용이 영속화되기 때문에 데이터를 잃어버리는 경우는 없다.

- 데이터 파일이 있는 파일 시스템의 파손이나 서버, 디스크의 물리 장애에 대비하기 위해 '백업' 프로세스가 필요하다.

- 백업 관점에는 DBMS 정지 여부(콜드와 핫), 형식(논리와 물리), 범위(풀과 차등/증분)가 있다.

- 기본 백업 방식은 풀 백업이지만, 필요에 따라 차등/증분 백업을 검토한다.

- 백업에 걸리는 시간과 부하, 복원과 복구에 걸리는 시간을 고려한다.

---

10장. 부록_성능을 생각하자 - 성능 향상을 위해

1.처리 시간 또는 응답 시간

- 어떤 특정 처리의 시작부터 종료까지 걸린 시간

 

2. 처리율

- 특정처리(트랜잭션)를 단위 시간에 몇 건 처리 가능한가

- 예를 들면, 트랜잭션을 '초당 50건' 처리하는 것이 가능하면 '50TPS'가 이 시스템의 처리율이다.

    ㆍ여기서 TPS는 'Transaction Per Second'의 약자이다.

- 처리율이 성능에서 중요한 이유는 시스템의 자원용량을 결정하는 요인이기 때문이다.

    ㆍ즉, 처리율이 높은 시스템일수록 'CPU나 메모리 같은 하드웨어 자원이 매우 필요하다는 것'을 의미

 

- 클라우드는 가상화를 기반으로 자우너량을 유연하게 변동할 수 있는 기술이다

    ㆍ스케일업, 스케일 아웃도 예전과 비교하면 쉬우며 짧은 시간 내에 실시할 수 있다.

    ㆍ따라서 정점일 때만 자원을 증하고 정점이 아닐 때는 줄이는 등의 동적의 자원 관리가 가능

    ㆍ즉, 이는 물리 자원의 임대 모델이다.

 

데이터베이스와 병목의 관계

- 취급하는 데이터양이 가장 많다.

- 자원 증가를 통한 해결이 어렵다.

    ㆍ저장소란 스케일 아웃이 어려운 요소이다.

    ㆍ튜닝 : 애플리케이션을 효율화해 같은 양의 자원이라도 성능을 향상하게 하는 기술

 

성능을 결정하는 요인

구문 오류가 없는지를 보는 파스

- Parse : 데이터베이스는 SQL문을 받으면 이 SQL문이 문법적으로 잘못된 부분이 없는지 점검

- Parser : 위와 같은 역할을 담당하는 내부 프로그램

 

실행계획과 옵티마저

- 실행계획 or 액세스 플랜 : Parse를 무사히 통과하면 SQL문에 필요한 데이터에 어떤 경로로 접근할까? 라는 계획을 세운다.

- 옵티마이저 : 실행계획을 결정하는 내부 프로그램

    ㆍ대부분의 경우 옵티마이저는 바른 판단을 한다.

- 통계정보 : 옵티마이저가 실행계획을 세울 때 참조하는 정보

    ㆍ1. 테이블의 행수-열수

    ㆍ2. 각 열의 길이와 데이터형

    ㆍ3. 테이블의 크기

    ㆍ4. 열에 대한 기본키나 NOT NULL 제약의 정보

    ㆍ5. 열 값의 분산과 편향

- SQL 문을 받아서 실행하는 과정

    ㆍ파스 → 실행계획 작성 ( 통계정보 참조 ) → 실행계획 평가 → 데이터 액세스

 

- 테이블로의 액세스 방법은 '풀 스캔(ALL)' 과 '레인지 스캔(range)' 2가지 있다.

    ㆍ풀 스캔 : 테이블에 포함된 레코드를 처음부터 끝까지 전부 읽어들이는 방법으로, '테이블 풀 스캔'이라 한다.

    ㆍ레인지 스캔 : 테이블의 일부 레코드에만 액세스하는 방법

         ▷ 레인지 스캔을 실행하려면 인덱스가 꼭 필요하다.

 

* 기본키를 구성하는 열에는 반드시 인덱스가 작성되어 있다.

 

인덱스는 SQL에서 만든다.

CREATE INDEX [인덱스명] ON [테이블명]([열명]);

- 사용자는 단지 인덱스를 만들었을 뿐 특별히 '이 인덱스를 사용하라'는 지시를 DBMS에 수행하지 않아도 '풀 스캔보다 인덱스를 사용하는 편이 빠르다' 라고 판단하고, 자동으로 인덱스를 사용하는 실행계획으로 변경한다.

- 반대로 '인덱스를 사용하지만 풀 스캔이 빠르다'고 판단하면 일부러 인덱스를 사용하지 않는 경우도 있다.

- 같은 인덱스를 사용하는 경우에도 기본키의 인덱스는 type 열에 'range', 만든 인덱스에는 'ref'라는 다른 문자열이 표시된다.

    ㆍ하지만 이것은 인덱스가 고유 키인지, WHERE 구의 조건으로써 등호를 사용했는지, 범위 조건을 사용했는지에 따라 바뀐다.

    ㆍ그래도 모두 테이블의 일부 데이터에만 액세스하고 있음을 보여주는 점은 간다.

 

인덱스의 구조

- 인덱스는 데이터베이스의 성능 향상 수단으로 가장 일반적인 방법

- 튜닝의 제 1선택, 인덱스를 선택하는 이유 3가지

    ㆍ1. SQL 문을 변경하지 않아도 성능을 개선할 수 있다.

    ㆍ2. 테이블의 데이터에 영향을 주지 않는다.

    ㆍ3. 일정한 효과를 기대할 수 있다.

- B-tree는 관계형 데이터베이스에서 튜닝의 기본이 되는 인덱스이다.

    ㆍB-tree의 장점1 '어떤 값에 대해서도 같은 시간에 결과를 얻을 수 있다' - 균일성

    ㆍ장점2. 데이터양이 증가할수록 우수한 개선 효과를 발휘하기 때문이다.

 

- SQL에서 내부적으로 정렬을 발생하게 하는 처리

    ㆍGROUP BY

    ㆍ집약함수 ( COUNT/SUM/AVG 등 )

    ㆍ집합연산 ( UNION/INTERSECT/EXCEPT )

 

- 쓸데없는 인덱스를 지나치게 만드는 것은 데이터베이스 설계의 안티 패턴의 한가지이다.

 

인덱스 작성이 역효과 나는 예

1. 인덱스 갱신의 오버헤드로 갱신 처리의 성능이 떨어진다

- SELECT 문은 고속화할 수 있는 것은 INSERT나 UPDATE 같은 갱신 SQL을 늦춘다

 

2. 의도한 것과 다른 인덱스가 사용된다.

- 사용할 수 있는 인덱스 후보가 많으면 옵티마이저도 인간처럼 '헤매게' 된다.

 

인덱스를 만들 때 기준

1. 크기가 큰 테이블만 만든다.

 

2. 기본키 제약이나 유일성 제약이 부여된 열에는 불필요하다

- 기본키 제약이 부여된 열에는 자동으로 인덱스가 작성되어있다.

- 유일성 제약이 붙어 있는 열도 같다.

 

3. Cardinality가 높은 열에 만든다.

- 'Cardinality'란 '값의 분산도'를 나타내는 단어

    ㆍ특정 열에 대해 많은 종류의 값을 가지고 있다면 Cardinality가 높고 반대로 값의 종류가 적으면 Cardinality가 낮다.

- Cardinality가 낮은 열에 인덱스 효과를 기대할 수  없는 것은 인덱스 트리를 따라가는 조작이 증가할수록 오버헤드가 증가해 인덱스를 작성한 혜택을 받지 못하기 때문이다.

 

성능의 안티 패턴

- 정밀도가 높은 실행계획을 세우는 데는 정밀도가 높은 통계정보가 필요하다

    ㆍ하지만 통계정보의 정밀도가 현저히 낮아지는 경우가 있다.

1. 결과 정보의 갱신이 OFF로 설정되어 있다.

- 낡은 통계정보 상태를 유지한채 갱신 X

2. 정기 갱신을 설정하고 데이터양이 급격히 변화했다.

- 옵티마이저를 신용하지 않는 대책을 채용하면 안된다.

 

정리

- 시스템 성능은 처리 시간(응답 시간)과 처리 속도(처리율)로 측정한다.

- 어느 한 개의 자원이라도 한계점에 도달하면 응답 시간과 처리율이 낮아지기 시작한다.

- 데이터베이스는 스케일 아웃에 따른 자원 증가가 어려워서 튜닝 기술이 중요하다

- SQL의 튜닝에는 '인덱스'를 사용하는 것이 편리하다

- SQL의 실행계획은 옵티마이저가 작성하므로 사용자는 기본적으로는 생각하지 않아도 좋다.

- 옵티마이저도 잘못된 통계정보를 바탕으로 하면 길을 헤매기 때문에 통계정보의 정확도에 주의해야 한다.