[MySQL]8. 데이터베이스 설계

34. 데이터베이스 설계

데이터베이스를 설계한다는 것은 데이터베이스의 스키마 내에 테이블, 인덱스, 뷰 드으이 데이터베이스 객체를 정의하는 것을 말한다.

• 테이블 명

  • 테이블을 설계할 떄 테이블 이름이나 열 이름을 지정한다.
  • CREATE TABLE로 지정하는걸 물리명, 설꼐산 이름이 논리명
  • 테이블 생성시 COMMENT로 논리명을 표시해줄 수 있다.

CREATE TABLE 물리명 (
  id INT NOT NULL
COMMENT = '논리명 설명';

• 자료형

  • 테이블의 열에 적절한 자료형 지정
  • 5.-2) 참고

• 기본키

  • 테이블을 작성할 떄 기본키 제약을 거는 경우 주의 : 행의 유일성을 지정
  • AUTO_INCREMENT : PRIMARY KEY, UNIQUE로 유일성을 지정하도록 한다.

 

ER 다이어그램

• 테이블을 설계할 떄 테이블 간의 관계를 명확히 하기위해 작성하는 설계도
• Worckbench에서 생성된 테이블의 ER다이어그램 확인할 수 있음

35. 정규화

정규화란 데이터베이스의 테이블을 규정된 올바른 형태로 개선해나가는 것

제1정규화

• 반복되는 데이터를 가로가 아닌 데로 방향으로 늘리는 것
• 테이블 분할과 기본키 지정이 이루어진다.
• 각각 관계된 데이터 모임을 기본 키로 식별한다.

정규화의 목적

정규화에서는 중복하거나 반복되는 부분을 찾아내 테이블을 분한하여 하나의 데이터를 한 곳에 저장하도록 한다.

제 1정규형

xxxxxxxxxx
mysql> mysql> select * FROM user_order_table;
+--------------+--------+-----------+-----------+--------------+-----------+--------+
| 주문번호     | 날짜   | 성명      | 연락처    | 상품코드     | 상품명    | 개수   |
+--------------+--------+-----------+-----------+--------------+-----------+--------+
|            1 | 1/1    | 박준용    | 010-0000  | 0001         | ㅁㅁ      |      1 |
|            1 | 1/1    | 박준용    | 010-0000  | 0002         | ㄴㄴ      |     10 |
|            2 | 2/1    | 김재진    | 016-0000  | 0001         | ㅁㅁ      |      2 |
|            2 | 2/1    | 김재진    | 016-0000  | 0002         | ㄴㄴ      |      3 |
|            3 | 2/5    | 박준용    | 010-0000  | 0001         | ㅁㅁ      |      3 |
|            3 | 2/5    | 박준용    | 010-0000  | 0003         | ㅇㅇ      |      1 |
+--------------+--------+-----------+-----------+--------------+-----------+--------+
6 rows in set (0.00 sec)

한 번의 주문으로 여러 개의 상품을 주문할 수 있으므로 주문버놓, 날짜, 성명, 연락처가 동일한 값을 가지는 행이 여러개 존재한다. 이 부분을 새로운 테이블로 분리한다.

​x
​
mysql> SELECT * FROM 주문상품;
+--------------+--------------+-----------+--------+
| 주문번호     | 상품코드     | 상품명    | 개수   |
+--------------+--------------+-----------+--------+
|            1 | 0001         | ㅁㅁ      |      1 |
|            1 | 0002         | ㄴㄴ      |     10 |
|            2 | 0001         | ㅁㅁ      |      2 |
|            2 | 0002         | ㄴㄴ      |      3 |
|            3 | 0001         | ㅁㅁ      |      3 |
|            3 | 0003         | ㅇㅇ      |      1 |
+--------------+--------------+-----------+--------+
6 rows in set (0.00 sec)
​
mysql> SELECT * FROM 주문;
+--------------+--------+-----------+-----------+
| 주문번호     | 날짜   | 성명      | 연락처    |
+--------------+--------+-----------+-----------+
|            1 | 1/1    | 박준용    | 010-0000  |
|            2 | 2/1    | 김재진    | 016-0000  |
|            3 | 2/5    | 박준용    | 010-0000  |
+--------------+--------+-----------+-----------+
3 rows in set (0.00 sec)

제1정규화에서는 테이블 분할과 기본키 ㅣㅈ정이 이루어진다.

제2정규형

제1정규화에서 테이블에 기본키를 작성한 것과 같은 방법으로, 제2정규화에ㅓ슨ㄴ 데이터가 중복하는 부분을 찾아내어 테이블로 분할해 나간다.

xxxxxxxxxx
mysql> SELECT * FROM `주문상품`;
+--------------+--------------+--------+
| 주문번호     | 상품코드     | 개수   |
+--------------+--------------+--------+
|            1 | 0001         |      1 |
|            1 | 0002         |     10 |
|            2 | 0001         |      2 |
|            2 | 0002         |      3 |
|            3 | 0001         |      3 |
|            3 | 0003         |      1 |
+--------------+--------------+--------+
​
mysql> SELECT * FROM `상품`;
+--------------+-----------+
| 상품코드     | 상품명    |
+--------------+-----------+
| 0001         | ㅁㅁ      |
| 0002         | ㄴㄴ      |
| 0003         | ㅇㅇ      |
+--------------+-----------+

• 삼품 테이블은 상품코드만으로 기본키를 지정했다.
• 부분 함수 종속성을 찾아내 테이블을 분할하는 것이 제2정규화
• 키 값을 이용해 데이터를 특정지을 수 있는 것 : 함수종속성

제3정규형

xxxxxxxxxx
​
mysql> SELECT * FROM `주문`;
+--------------+--------+--------------+
| 주문번호     | 날짜   | 고객번호     |
+--------------+--------+--------------+
|            1 | 1/1    |            1 |
|            2 | 2/1    |            2 |
|            3 | 2/5    |            1 |
+--------------+--------+--------------+
3 rows in set (0.00 sec)
​
mysql> SELECT * FROM `고객`;
+--------------+-----------+-----------+
| 고객번호     | 성명      | 연락처    |
+--------------+-----------+-----------+
|            1 | 박준용    | 010-0000  |
|            2 | 김재진    | 016-0000  |
+--------------+-----------+-----------+
2 rows in set (0.00 sec)

주문테이블에서 중복된 이름을 분리

• 제2정규화 : 기본키에 중복이 없는지
• 제3정규화 : 기본키 이외의 부분에서 중복이 없는지

하나의 데이터가 반드시 한 곳에만 저장되어 있따면 데이터를 변경하더라도 한 곳만 변경하면 된다. 정규화되지 않은 경우에는 여기저기 중보갷서 저장된 데이터를 검색하고 일일이 변경해야한다. 테이블이 분리되어있을수록 데이터를 조회하기위해 조인을 수행해야하는데 이 떄 속도 저하의 우려가 있다. 사용 용도에 맞게 적절한 정규화 필요

비정규화

비정규형 모델은 정규화를 하지 않은 모델이 아니라, 정규화를 마친 후에 비정규화를 한 모델이다. 비정규화(Denormalization)는 조회 성능을 향상시키기 위해 데이터를 중복하거나 그룹핑하는 과정을 의미한다.

• 비정규형을 사용하는 유일한 목적은 조회성능을 향상시키기 위해서다.
• 단순히 조인(Join)을 피하고자，사용의 편의를 위해 중복 속성을 사용해선 안 된다.
• 중복된 데이터는 원천 데이터와 정합성을 맞춰야 해서 사용하기 편하지 않다.

단점

• 정합성

  • 비정규화 > 데이터중복 > 정합성 훼손 > 품질저하

• 쓰기성능 저하

  • 중복 자료 : 읽기 성능 향상
  • 정합성 일치 : 쓰기 성능 저하

• 데이터 성격이 불명확

• 공간차지

  • 중복 데이터의 공간 차지 > 성능 저하

참고

 

36. 트랜잭션

xxxxxxxxxx
START TRANSACTION
COMMIT
ROLLBACK

MySQL의 MyISAM에는 트랜잭션을 지원하지 않고, InnoDB 엔진에서 지원한다.

데이터 저장소 동시성에 대해 이야기할 때 지겹도록 등장하는 은행 잔고에 대한 예로 시작해 보자.

xxxxxxxxxx
`사용자 A에게 X 코인 입금`

A의 잔고 관리 구현을 자연어로 풀어 쓰면

1. A의 잔고를 DB 에서 가져와 애플리케이션 변수 b에 저장. (읽기)
2. 변수 b에 X를 더한 값을 다시 저장. (수정)
3. DB 에 변수 b 값을 쓴다. (쓰기)

이를 SQL 및 애플리케이션 로직으로 다시 쓰면 아래와 같을 것이다. (애플리케이션 로직은 SQL 주석으로 표시하였다. <...> 는 애플리케이션 로직에서 SQL 문을 생성할 때 변수 값으로 치환해 주어야 하는 부분이다)

xxxxxxxxxx
/* b = \ */
SELECT amount
FROM balance
WHERE user_id = <A.id>;
/* b = b + X */
UPDATE balance
SET amount = <b>
WHERE user_id = <A.id>;

이 단순한 로직으로 입금 요청이 여러 곳에서 동시(concurrent)에 발생하면, 의도대로 동작하지 않을 수도 있다. 이를 설명하기 위해 A 의 현재 잔고(balance)가 0 코인이라고 가정하자. 이 상태에서 두 곳으로 부터 동시에 A에게 각각 300 코인, 700 코인을 입금하려고 한다. 입금 후 A 의 잔고는 당연히 300 + 700 = 1000이 되어야 한다.

그러나 만약 두 입금 요청이 아래와 같은 순서로 실행되면 기대했던 것과는 다른 결과가 나온다.

300 코인 입금 요청 (T1)	700 코인 입금 요청 (T2)
/* b = */ SELECT ...; : b = 0
	/* b = */ SELECT ...; : b = 0
/* b = b + 300 */ : b = 300
	/* b = b + 700 */ : b = 700
UPDATE ...; : DB 상 A 잔고 = 300
	UPDATE ...; : DB 상 A 잔고 = 700

DB 의 A 잔고는 최종적으로 700 코인이다. 300 코인 입금 분이 사라져 버린다.

원자성

원자성은 간단히 말하자면 해당 연산이 쪼개 질수 없다는 의미이다. 하나의 논리적인 단위로 실행되면 정확한 결과가 나온다. 앞 서 예로 든 로직은 겉보기에도 3단계로 나누어져 실행된다.

일반적으로 RDBMS에서 원자성을 보장하기 위해 트랜잭션을 사용한다. 여러 DB 연산을 하나의 논리적인 단위로 묶을 수 있고, 그 연산들을 한 번에 반영하거나(commit), 불가능하다면 전부 없었던 일로 무효화(rollback)하는 방식으로 동작한다.

xxxxxxxxxx
START TRANSACTION;
​
/*
연산
...
...
*/
​
COMMIT

트랜잭션은 데이터 처리의 정확성을 확보해 주는 RDBMS 만의 큰 장점 중 하나이다. 논리적인 작업들을 모두 완벽하게 처리하여 적용하거나(COMMIT), 처리하지 못할 경우 원 상태로 복구해서 작업의 완전성을 보장해주는 기능이다.

격리수준

• 격리 수준이란 트랜잭션에서 일관성이 없는 데이터를 허용 하는 정도
• 무조건적으로 트랜잭션 간의 관여를 막는것은 데이터베이스의 성능을 저하시킬뿐만 아니라 사용자의 의도대로 사용 할 수 없어지기 때문입니다.
• 따라서 격리 수준을 조금 완화해 서로 영향을 받으면서 상충되지 않게 적절한 레벨을 지정하여 처리 하도록 해야한다.
• 격리 수준이 높아질 수록 동시성도 떨어진다.

낮은 단계의 트랜잭션 격리 수준에서 발생하는 현상

• Dirty Read

  • 다른 트랜잭션에서 처리 중인 내용이 완료되지 않았음에도, 트랜잭션에서 볼 수 있게되는 현상
  • 트랜잭션1에서 A테이블을 SELECT 한 후 트랜잭션2에서 A테이블 내용을 변경하는 상황 가정
  • 트랜잭션2가 해당 변경사항을 commit 하지도 않았는데, 트랜잭션1에서 다시 A테이블을 SELECT하면 해당 변경사항을 읽어들일 수 있게됨

• Non-Repeatable Read (Inconsistent Read)

  • 트랜잭션 시작 후, 반복적인 조회작업에서 다른 트랜잭션에서 데이터가 변경되는 경우, 조회 시 데이터가 일치하지 않는 문제가 발생하는 현상
  • 트랜잭션1에서 A테이블을 SELECT 한 후 트랜잭션2에서 A테이블 내용을 변경(UPDATE)하는 상황 가정
  • 트랜잭션2가 해당 변경사항(UPDATE)을 commit 한 이후에, 트랜잭션1에서 다시 A테이블을 SELECT하면 해당 변경사항을 읽어들일 수 있게됨

• Phantom Read

  • 한 트랜잭션에서 일정 범위 내의 레코드를 두 번 이상 읽을 때, 첫번째 쿼리 조회 결과에서 없던 레코드가 조회되는 현상
  • 트랜잭션1이 A테이블에서 SELECT한 이후 트랜잭션2에서 A테이블에 내용을 추가/삭제(INSERT/UPDATE)하는 상황 가정
  • Repeatable Read가 보장된 경우, A테이블에서 SELECT해왔던 데이터들을 다른 트랜잭션2가 수정(UPDATE)하여 commit한 후 트랜잭션1에서 다시 A테이블을 SELECT 하더라도 트랜잭션2의 수정내용을 읽어들일 수 없다.
  • 하지만 트랜잭션2가 추가/삭제(INSERT/DELETE)를 한 경우, 다시 A 테이블에서 SELECT하게되면 기존에 A에서 SELECT했던 데이터에서 row가 추가되거나 사라질 수 있다.(유령 데이터)

 

격리 수준

• Read Uncommitted (level 0)

  • 커밋되지 않은 읽기, 커밋되지 않은 데이터를 다른 트랜잭션이 읽는 것을 허용.
  • A라는 데이터를 B라는 데이터로 변경하는 동안 다른 사용자는 B라는 커밋 되지않은 데이터 B를 읽을 수 있다.
  • Dirty Read, Non-Repeatable Read, Phantom Read가 발생할 확률이 높다.

• Read committed (level 1)

  • 커밋된 읽기, 커밋되어 확정된 데이터를 다른 트랜잭션에서 읽는 것을 허용. : dirty read 방지
  • A라는 데이터를 B라는 데이터로 변경하는 동안 다른 사용자는 해당 데이터에 접근할 수 없다.
  • Non-Repeatable Read, Phantom Read가 발생할 확률이 높다.

• Repeatable Read (level 2)

  • 반복 읽기, 트랜잭션 내에서 한번 조회한 데이터가 반복적으로 조회 된다.
  • A 트랜잭션이 읽은 데이터는 트랜잭션이 종료될 때까지 B 트랜잭션이 갱신하거나 삭제하는 것을 허용하지 않음으로써 같은 데이터를 두 번 쿼리했을 때 일관성 있는 결과를 리턴 한다.
  • SELECT col FROM a WHERE col1 BETWEEN 1 AND 10 을 수행하였고 결과는 두 건의 데이터 출력(col1 = 1 ,5). 다른 사용자가 col1이 1이나 5인 row에 대한 update는 불가능하다. 이를 제외한 나머지 범위에 해당하는 row를 insert하는 것은 가능. : Phatom Read 가능성
  • Phantom Read가 발생할 확률이 높다.

• Serializable (level 3)

  • 트랜잭션이 완료될 때까지 SELECT 문장에 사용하는 모든 데이터는 shared lock이 걸리므로 다른 사용자는 그 영역에 해당되는 데이터에 대한 수정 및 입력이 불가능 하다.
  • Dirty Read, Non-Repeatable Read, Phantom Read가 발생할 확률이 낮지만 동시 처리 성능은 크게 떨어질 수 있다.

MySQL InnoDB의 디폴트 격리수준은 Repeatable Read이다. 격리수준 변경은 SET tx_isolation='격리수준이름'으로 바꿀수있다...

출처: 링크, 격리수준에 따른 동시성 이슈, 격리 수준, 격리수준 예시 잘나옴, 락종류