책/CS 전공 지식 노트 4장 데이터베이스

4장 데이터베이스

4.1 데이터베이스의 기본

4.2 ERD와 정규화 과정

4.3 트랜잭션과 무결성

4.4 데이터베이스의 종류

4.5 인덱스

4.6 조인의 종류

4.7 조인의 원리

 

 

4.1.1 엔터티

_4.1.2 릴레이션
__4.1.3 속성
__4.1.4 도메인
__4.1.5 필드와 레코드
__4.1.6 관계
__4.1.7 키
4.2 ERD와 정규화 과정
__4.2.1 ERD의 중요성
__4.2.2 예제로 배우는 ERD
__4.2.3 정규화 과정
4.3 트랜잭션과 무결성
__4.3.1 트랜잭션
__4.3.2 무결성

4.4 데이터베이스의 종류
__4.4.1 관계형 데이터베이스
__4.4.2 NoSQL 데이터베이스
4.5 인덱스
__4.5.1 인덱스의 필요성
__4.5.2 B-트리
__4.5.3 인덱스 만드는 방법
__4.5.4 인덱스 최적화 기법
4.6 조인의 종류
__4.6.1 내부 조인
__4.6.2 왼쪽 조인
__4.6.3 오른쪽 조인
__4.6.4 합집합 조인
4.7 조인의 원리
__4.7.1 중첩 루프 조인
__4.7.2 정렬 병합 조인
__4.7.3 해시 조인
예상 질문

 

 

 

4.1 데이터베이스의 기본

데이터베이스- DBMS- 응용 프로그

4.1.1 엔터티

여러개의 속성을 가진  명사/ 회원 - 이름 아이디 주소  / 요구 사항에 맞춰 정해지는 속

약한 엔터티 - 혼자서는 존재하지 못하고 B의 존재 여부에 따라 종속 / 어떤 엔터티에 종속 적인

강한 엔터티

_4.1.2 릴레이션

데이터 베이스에서 정보를 구분하여 저장하는 기본단위

엔터티에 관한 데이터를 데이터베이스는 릴레이션 하나에 담아서 관리

회원이라는 엔터티가 데이터베이스에 관리될 때 릴레이션으로 변화된 채로 관리 됨

관계형 데이터베이스에서는 릴레이션은 테이블이라고 한다. 

노에스큐엘 데이터베이스에서는 릴레이션을 컬렉션이라고 한다. 

 

관계형 데이터베이스 - MySQL   레코드-테이블-데이터베이스

NoSQL - MongoDB                    도큐먼트 - 컬렉션 - 데이터베이스

 

__4.1.3 속성

릴레이션에서 관리하는 구체적이며 고유한 이름을 갖는 정보

차라는 엔터티의 속 중에 요구사항 기반으로 관리해야하는 정보만 엔터티의 속성이 된다. 

 

__4.1.4 도메인

릴레이션에 포함된 각각의 속성이 가질 수 있는 집합 - 

엔터티 사람 /릴레이션 사람테이블/ 속성 성별/ 도메인은 남과 여

릴레이션에 이름 아이디 주소 성별 이라는 속성이 있다. 

__4.1.5 필드와 레코드

필드와 레코드를 기반으로 한 테이블

필드 - 이름 아이디 주소 성별 이라는 필드를 가진다

레코드 - 이 테이블에 쌓이는 행 단위의 데이터들을 레코드  = 튜플 이라고 하기도 함

책이라는 엔터티에 / 책이라는 릴레이션=테이블=컬렉션 / 아이디 제목 저자 등의 필드/ 그 에 해당하는 레코드 = 튜플

사람이라는 엔터티에/ 사람이라는 릴레이션테이블컬렉션/ 이름 주소 성별 등의 필드/ 그에 해당하는 레코드= 튜플

속성(속성이름은 보통 영어로) 등이 필드가 됨/ 속성에는 그에 맞는 타입을 지정 (필드 타입 ) int varchar 등

 

> 나름 정리 

어떤대상이 엔터티고/ 그 엔터티를 관리하는 릴레이션 테이블 컬렉션이 있고/  

그 엔터티에 대한 속성 을 필드로 만들고 필드타입을 정한다.

그에 해당하는 데이터들이 행 단위로 쌓여서 레코드를 만들고

하나의 데이터베이스가 생성 된다

 

> 숫자/ 날짜 /DATE / DATETIME/ TIMESTAMP

> 문자 CHAR VARCHAR /TEXT BLOB / ENUM SET

CHAR 

고정 길이 문자열 / 0-255 / 선언한 길이 값으로 고정된다  / 100으로 설정했으면 10을 저장해도 100으로 저장됨

VARCHAR

가변 길이 문자열 / 0-63535사이의 값/ 10글자의 이메일 저장시 - 10글자에 해당하는 바이트 + 길이 기록용 1바이트로 저

TEXT 

큰 데이터 저장시/ 큰 문자 /주로 게시판의 본문

BLOB

큰 데이터 저장/ 이미지 + 동영상 등 / 아마존의 이미지 호스팅 서비스인 S3를 이용하는 등 서버에 파일을 올리고 그에 대한 경로를 VARCHAR로 저장한다

ENUM

문자열을 열거한 타입

하나만 선택하는 단일 선택이 가능하고 , 없는 값을 잘못 삽입하며 빈 문자열을 삽입, ENUM을 사용하면  0,1등으로 저장되매핑되므로 메모리를 적게 사용하는 이점 /최대 65535개의 요소를 넣을 수 있다.

SET

ENUM과 비슷 하지만 여러개의 데이터를 선택할 수 있고, 비트 단위의 연산을 할 수 있고 최대 64개의 요소를 넣을 수 있다.

공간적인 이점이 있지만 애플레이케이션의 수정에 따라 데이터베이스  정의한 목록을 수정해야 한다는 단점

 

__4.1.6 관계

관계형데이터베이스에서 테이블= 릴레이션을 여러개 사용 / 서로의 관계가 정의 되어 있음/

이러한 관계를 관계화살표로 나타낸다...

A는 하나의 B로 구성되어있다  ㅣ 

A는 하나이상의 B로 구성되어있다 <-

A는 하나 이하의 B로 구성되어있다  ㅇ+

하나의 A는 0 또는 하나 이상의 B로 구성되어있다.  ㅇ<- 

 

1:1관계 유저당 유저이메일 1:1   ㅣ

1:N관계 유저당 여러개의상품 ㅇ<-

N:M관계 학생당 여러개의 강의  학생도 여러개의 강의/ 강의도 여러명의 학생

중간에 학생_강의 라는 테이블을 끼어넣고 / 두 개의 테이블을 직접적으로  연결해서 구축하지 않고 /

1:N 과 1:M이라는 관계를 갖는 테이블 두 개로 나눠서 설정

 

__4.1.7 키

테이블 간의 관계를 좀 더 명확하게 하고 테이블 자체의 인덱스를 위해서 설정된 장치로

기본키 외래키 후보키 대체키 슈퍼키 등이 있다. 

슈퍼키-유일성(중복되는 값이 없다)   

후보키-유일성+최소성(필드를 조합하지 않고, 최소 필드를 써서 키를 형성할 수 있는다) = 기본키+대체키

기본키-유일성+최소성만족

기본키는 자연키와 인조키 중에 골라서 설정한다. 

자연키는 중복된 값들 성별이나 나이 이름 같은 중복된 값들이 들어올 수 이쓴 속성을 제외하다가 자연스럽게 뽑다가 나온 키를 자연키라고 한다. 언젠가 변하는 속성을 가진다

인조키는 인위적으로 유저아이디는 속성을 만들어 고유 식별자로 생겨나게 하는 것이다. 오라클은 시퀀스/ 마이에스큐엘은 AUTO INCREMENT등으로 설정/  변하지 않는 속성 / 보통 기본키는 인조키로 만든다.

 

외래키는 다른 테이블의 기본키를 그대로 참조하는 키- 그렇지만 외래키는 중복되어도 괜찮다 FK

후보키는 기본키가 될 수 있는 후보들 = 유일성과 최소성 둘 다 만족

대체키는 후보키가 두 개 이상일 경우 = 하나를 기본키로 하고 남은 키>>> 그럼 얘도 둘다만족해야하는거아냐? 

슈퍼키는 각 레코드들을 유일하게 식별할 수 있는 유일성을 가진 키 

 

ERD ENTITY RELATIONSHIP DIAGRAM은 데이터베이스를 구축할 때 가장 기초적인 뼈대역할을 하고/ 릴레이션 간의 관계를 정의한 것 / 서비스 구축시 가장 신경 써야 할 부분 /  시스템의 요구사항을 기반으로 

 

데이터베이스를 구축한 이후에 디버깅 또는 비즈니스 프로세스 재설계가 필요한 경우 설계도 역할을 담당하기도 함

관계형 구조로 표현할 수 있는 데이터를 구성하는데 유용/ 비정형 데이터(비구조화된 데이터를 말하며 미리 정의된 데이터 모델이 없거나 미리 정의된 방식으로 정리되지 않는 정보)를 충분히 표현할 수 없다. 

 

예제ERD

테이블필드/타입생략

1. 사원 0-N고객을 관리한다/ 고객은 0-N의 주문이 가능하다/ 주문에는 0-N상품이 가능하다/   0<-

2. 선수들 1명의 챔피언을 고른다/ 챔피언은 한 개 이상의 스킬보유/스킬은 한 개이상으 특성을 갖는다 / ㅣ <- <-

 

__4.2.3 정규화 과정

릴레이션 간의 잘못된 종속관계로 인해 데이터베이스 이상현상 일어나서 이를 해결하거나 , 저장 공간을 효율적으로 사용하기 위해 릴레이션을 여러개로 분리 하는 과정

이상현상이란

회원이 한 개의 등급을 가져야하는데 세 개의 등급을 갖거나

삭제할 때 필요한 데이터가 같이 삭제 되거나

데이터를 삽입해야 하는데 하나의 필드가 NULL이 되면 안되어서 삽입하기 어려운 현상

 

정규화과정은 정규형 원칙을 기반으로 정규형을 만들어가는 과정이며 정규화된 정도는 정규형 NORMAL FORM으로 표현

기본 정규형 1 2 3 보이스/코드 정규형

고급 정규형 4 5 정규형

 

 

4.3 트랜잭션과 무결성
__4.3.1 트랜잭션

트랜잭션은 데이터베이스에서 하나의 논리적 기능을 수행하기 위한 작업의 단위를 말한다.

여러개의 쿼리를 하나로 묶는 단위

원자성 일관성 독립성 지속성 원일독지 원일독지 ACID 원자성일관성독립성지속성 durability

원자성 All or nothing

트랜잭션과 관련된 일이 모두 수행되었거나 되지 않았거나를 보장하는 특징이다.

트랜잭션을 커밋했는데 문제가 발생하여 롤백하는 경우 그 이후에 모두 수행하지 않음을 보장하는 것

10000만원을 가진 홍철이가 0원을 가진 규영이에게 500만원 이체 

1. 홍철의 잔고를 조회한다

2. 홍철에게서 500만원 을 뺀다

3. 규영에게 500만원을 넣는다. 

세 과정을 볼 수가 없다.

취소할 시에 일부 오퍼레이션 만 적용된 500 0이 될 수는 없고 다시 1000과 0 이 되어버립니다.

트랜젝션 단위로 여러 로직을 묶을 때 외부API를 호출하는 것이 있으면 안됩니다. 

만약 있다면 롤백이 일어 났을 때 어떻게 할지 해결법이 있어야한다+ 트랜젝션 전파를 신경써서 관리해야함

 

커밋 롤백

커밋 : 여러 쿼리가 성공적으로 처리되었다고 확정하는 명령어

트랜잭션 단위로 수행되며 변경된 내용이 모두 영구적으로 저장된다

커밋이 수행되었다 = 하나의 트랜잭션이 성공적으로 수행되었다.

롤백 : 트랜잭션으로 처리한 하나의 묶음 과정을 일어나기전으로 돌리는 일

커밋과 롤백 덕에 무결성이 보장된다.  변경 전 변경사항을  쉽게 확인 할 수 있고 작업을 그룹화할 수 있다,

 

트랜젝션 전파

트랜잭션을 수행할 때 커넥션 단위로 수행하기 때문에 커넥션 객체를 넘겨서 수행해야 합니다. 

__4.3.2 무결성

4.4 데이터베이스의 종류
__4.4.1 관계형 데이터베이스
__4.4.2 NoSQL 데이터베이스
4.5 인덱스

인덱스 

인덱스의 필요성 : 데이터를 빠르게 찾을 수 있는 하나의 장치

B-트리 구조 

루트노드

브랜치 노드

리프노드

 

예를 들어 E를 찾는다고 하면 전체 테이블에서 탐색하는 것이 아니라, E가 있을 법한 리프노드로 들어가서 E를 탐색한다. ABC DEF GHI 에서  찾으려고하면 ABCDE 다섯번 탐색 해야하지만

노드들로 나누어 놓으면 두 번만에 탐색가능

 

예를들어 57을 찾으려고 한다 

트리탐색 : 루트 노드 부터 시작한다. / 브랜치 노드를 거쳐서 리프노드로 간다 / 57보다 같거나 클 때까지  <= 을 기반으로 처음 루트 노드에서는 39,83 이후에 아래 노드로 내려와 46,52,57 등 정렬된 값을 기반으로 탐색

마지막 리프노드까지 도달하여 57을 가리키는 데이터 포인트를 통해 결과값을 반환. 

 

인덱스의 효율성/ 대수확장성

균형잡힌 트리구조와 

트리 깊이의 대수 확장성으로 효율적이다. 

대수확장성 : 트리 깊이가 리프 노드 수에 비해서 매우 느리게 성장하는 것. 

기본적으로 인덱스가 한 깊이씩 증가할 때 마다 최대 인덱스 항목의 수는 4배씩 증가한다.

3-64 4 -256 5 -1024 6 -4096  *4 *4.. 10 -1048576 

트리깊이가 10개짜리로 100만개의 레코드를 검색가능 /실제는 이것보다 더 효율적이다. 

 

인덱스 만드는 방법 - 데이터베이스마다 상이

MySQL - 클러스터형 인덱스 와 세컨더리 인덱스  

클러스터형 인덱스는 테이블당 하나 설정가능

기본키 옵션으로 기본키를 만들면 클러스터형 인덱스를 생성할 수 있고 

기본키 아닌 unique not null옵션을 붙이면 클러스터형 인덱스로 만들 수 있습니다.

create index명령어를 기반으로 만들면 세컨더리 인덱스를 만들 수 있습니다. 

하나의 인덱스만 생성할 것이라면 클러스터형 인덱스를 만드는 것이 성능이 좋다.

 

세컨더리 인덱스는 보조인덱스로 여러가지 필드값을 기반으로 쿼리를 많이 보낼 때 생성해야 하는 인덱스

예를 들어 age라는 하나의 필드 만으로 쿼리를 보낸다면 클러스터 인덱스만  

age나 name email등 다양한 필드를 기반으로 쿼리를 보낸다면 세컨더리 인덱스가 필요함

 

 

MongoDB-도큐먼트를 만들면 자동으로 ObjectID가 형성되며, 해당 키가 기본키로 설정됨.

세컨더리키도 부가적으로 설정해서 기본키와 세컨더리키를 같이쓰는 복합키 설정도 가능하다. 

 

인덱스 최적화 기법 

기본적인 골조가 비슷하므로 특정 데이터베이스 기준으로 설명해도 무방

 

MongoDB기준 도큐먼트 - 컬렉션 - 데이터베이스

인덱스는 비용이다 - 두 번 탐색하도록 강요한다.  

인덱스 리스트 - 그다음 컬렉션(테이블) 순으로 탐색 / 관련 읽기 비용

컬렉션(테이블)이 수정되었을 때 리스트도 수정 되어야 한다 ( 마치 본문이 수정되면 목차도 수정되야 하는 것처럼)

 

항상 테스팅 하라

인덱스 최적화 기법은 서비스 특징에 따라 달라진다. 서비스에서 사용하는 객체의 깊이 , 테이블 양등이 다르기 때문이다.

항상 테스팅하는 것이 중요하다. EXPLAIN()함수를 통해 인덱스를 만들고, 쿼리를 보낸 이후에 테스팅을 하며 걸리는 시간을 최소화해야한다. 

MYSQL에서는 다음의 코드로 테스팅한다

EXPLAIN

SELECT * FROM t1

JOIN t2 ON t1.c1=t2.c1

 

복합 인덱스는 같음, 정렬, 다중 값 , 카디널리티 순이다. 

여러필드를 기반으로 조회를 할 때 복합 인덱스를 생성하는 이 인덱스를 생성할 때는 순서가 있고 순서에 따라 인덱스의 성능이 달라진다. 같음 정렬 다중값 카디널리티 순이다. 같음.정렬.다중값.카디널리티. 

1. 어떠한 값과 같음을  비교하는 == 이나 equal이라는 쿼리가 있다면 제일 먼저 인덱스로 생성한다.

2. 정렬에 쓰는 필드라면 그 다음 인덱스로 설정

3. 다중 값을 출력해야 하는 필드, 즉 쿼리 자체가 > 이거나 < 등 많은 값을 출력해야하는 쿼리에 쓰는 필드라면 나중에 인덱스를 생성한다.

4. 유니크한 값 정도를 카디널리티라고 한다. 카디널리티가 높은 순서를 기반으로 인덱스를 생성해야한다. age email중에 email이라는 필드에 대한 인덱스를 먼저 생성해야 한다. 

 

4.6 조인의 종류

조인이란 하나 이상의 테이블을 

MySQL은  join이라는 쿼리로 MongoDB에서는 lookup이라는 쿼리로 이를 처리 

MongoDB에서는 쓰지 말아야합니다. 조인 연산에 대해 성능이 떨어집니다. 벤치마크 테스트에서 이미 알려져있습니다,

내부조인/왼쪽조인 B에 해당하는 값이 없으면 NULL이 됩니다 /오른쪼조인 A에 해당하는 값이 없으면 NULL/ 외부조인

FULL OUTER 일치하는 항목이 없으면 누락된 쪽이 NULL값이 된다 

 

 

중첩 루프 조인

정렬 병합 조인

해시 조인

해시테이블을 기반으로 조인하는 방법/  두 개의 테이블 조인시 하나의 테이블이 메모리에 온전히 들어간다면 보통 중첩 루프 조인보다 더 효율적이다/ 만약 메모리에 올릴 수 없을 정도로 크다면 디스크를 사용하는 비용이 발생하게 됩니다.

동등 조인에서만 사용가능하다. 

빌드단계 : 입력테이블 중 하나를 기반으로 메모리 내 해시테이블을 빌드하는 단계/ 바이트가 더 작은 테이블을 기반으로 해서 테이블을 빌드한다.  조인에 사용되는 필드가 해시테이블의 키로 사용된다. 

프로브단계 : 데이터를 읽기 시작하며 레코드에서 각 키에 일치하는 레코드를 찾아서 결과값으로 반환한다. 

> 테이블을 각 한 번만 읽게되어 중첩해서 두개의 테이블을 읽는 중첩 루프 조인보다 성능이 좋다.

사용가능한 메모리양은 시스템 변수 join_buffer_size에 의해 제어 되며 런타임 시에 조정가능하다. 

 

 

 

SQL Joins Visualizer 쿼리