정리[Database]

키란?

테이블에 저장되어있는 튜플들을 식별하기 위한 Attribute값이 키입니다.

 

최소성과 유일성

- 유일성 : 특정 튜플을 바로 찾아낼 수 있는 유일한 속성이어야 한다.(나를 찾기 위한 주민등록번호)

- 최소성 : 최소한의 속성으로 튜플을 구분할 수 있어야 한다.

 

1) Super Key 슈퍼키

튜블을 식별할 수 있는 하나 이상의 속성들의 집합입니다. 슈퍼키는 최소성이 아닌 유일성만 만족하면 됩니다.

 

2) Candidate Key 후보키

튜플들을 식별하기 위해 사용되는 키들의 집합이 후보키입니다. 후보키는 두가지의 조건을 만족해야합니다.

 

3) Primary Key 기본키

후보키들 중에서 선택한 하나의 키로 오직 하나만 존재하며, 기본키는 NULL데이터와 중복 데이터를 가져선 안된다.

최소성과 유일성을 만족한다.

 

4) Alternate Key 대체키

후보키들 중에서 기본키를 선택하고 남은 키

 

5) Foreign Key 외래키

두개의 테이블이 있다고 할 때 A테이블의 기본키를 B테이블이 동일하게 갖게된다면 B테이블의 기본키는 외래키가 됩니다.

즉, 두 테이블간의 관계를 연결하는 역할을 하게됩니다.

JOIN이란?

내부조인(두 테이블의 속성을 합침)

1) 교차 조인 : 곱집합 Select * From table1 CROSS JOIN table2

2) 내부(동일) 조인 : 교집합 Select * From Atable INNER JOIN Btable ON Akey = Bkey

3) 비등가 조인 : 비교문 Select * FROM Atable, Btable Where key between 101 and 104;

4) 자연 조인 : 교집합이지만 컬럼 중복은 제외한다. SELECT * FROM Atable Natural Join Btable;

 

외부조인

1) 왼쪽 외부 조인

왼쪽 테이블 + 왼-오 속성 같은 값(나머지 제외)

SELECT * FROM table1 LEFT OUTER JOIN table2 ON table1.n = table2.n;

2) 오른쪽 외부 조인

SELECT * FROM table1 RIGHT OUTER JOIN table2 ON table2.n = table2.n;

3) 완전 외부 조인

SELECT * FROM table1 LEFT OUTER JOIN table2 ON table1.n = table2.n

UNION

SELECT * FROM table1 RIGHT OUTER JOIN table2 ON table2.n = table2.n;

 

셀프 조인 : 자기 자신과 조인하는 것

 

안티 조인 : 메인 테이블의 데이터 중에 NOT IN을 사용해서 서브쿼리의 테이블 칼럼중 조건에 해당하는 것을 제외한 결과물을 얻어낸다.

6) 세미 조인 : EXISTS을 사용해서 서브쿼리에 존재하는 데이터만 추출하며 결과값은 중복이 제거됩니다.

SQL INJECTION

1) 인증 우회

아이디 비번은 입력하면 위 사진과 같은 쿼리가 전송될 것이다. 그런데, 비밀번호 입력과 동시에 아래와 같이 쿼리문도 함께 입력하면 해당 쿼리문이 전송되어 DB가 조작될 수 있다.

 

2) 데이터 노출

에러 메시지를 통해 데이터베이스 구조를 유추하여 해킹하는 방식이다. GET방식으로 동작하는 URL 쿼리 스트링을 추가하여 에러를 발생시킨다.

 

SQL INJECTION 해결 방안

1) Input값을 받을때 특수문자 여부를 검사한다

2) SQL서버 오류 발생 시, 에러 메시지를 감춘다

3) preparestatement 사용하기, 특수문자를 자동으로 escaping해주며. 전달 인자값을 ?로 받아 필터링한다.

DBMS

DBMS​(Database Management System) 란 사용자와 데이터베이스 사이에서 사용자의 요구에 따라 정보를 생성해주고, 데이터베이스를 관리해주는 소프트웨어입니다.

1. 정의기능 : 이 데이터베이스가 어떤용도이며 어떤 식으로 이용될거다라는 정의 기능이 필요합니다. 이러한 정의 기능이 있어야 사용자들은 좀 더편하게 데이터베이스를 만들 거나 없앨 수 있는것이지요.

 

2. 조작기능 : 데이터베이스를 만들었을 때 그 정보를 수정하거나 삭제 추가 검색할 수 있어야합니다. 

 

3. 제어기능 : 제어기능 같은 경우 이 데이터 베이스는 사용자 말고는 권한을 주면 안됩니다. 뿐만 아니라 모든 조작기능을 할 떄 이치에 맞지 않는 행위가 있다면 그부분들은 제어되어야 할 것입니다.

SQL vs NOSQL

SQL

SQL사용하면 RDBMS에서 데이터를 저장, 수정, 삭제 및 검색을 할 수 있다. 관계형 데이터베이스에는 핵심적인 두 가지 특징이 있다.

1. 데이터는 정해진 데이터 스키마에 따라 테이블에 저장된다.

데이터는 테이블에 저장이 되는데, 각 테이블 마다 스키마를 통해 속성이름과 데이터 타입, 여러 제약사항들이 정의되어 있다. 따라서 스키마를 준수하지 않은 레코드는 테이블에 추가할 수 없다.

2. 각 테이블들 간에 관계가 존재할 수 있다.

예를들어, 직원 테이블과 백화점 테이블이 있다면 지원 다수가 하나의 백화점에 소속되어 있는 경우 다대일 관계가 되듯이, 여러 관계들을 표현할 수 있게된다.

 

NOSQL

Nosql은 스키마도 없으며 관계도 없다.

Nosql에서는 레코드를 문서(documents)라고 부른다.

여기서 SQL과의 핵심적인 차이가 있는데, SQL은 스키마를 따르지 않으면 데이터 추가가 불가능했지만 NoSQL에서는 다른 구조의 데이터를 같은 컬렉션에 추가가 가능하다. 문서는 Json과 같은 형태로 가지고 있으며, 관계형 DB처럼 여러 테이블에 나누어담지 않고 동일한 컬렉션에 담기 때문에 조인 연산자체가 필요없게된다.

 

SQL의 장점

1) 스키마를 통해 명확하게 데이터 구조를 명시해놓았기 때문에 데이터 무결성을 보장한다.

2) 데이터를 더 체계적으로 저장하며 관리할 수 있다.

2) 트랜잭션이 가능하다

 

SQL의 단점

1) 테이블을 미리 정의해야하기 때문에 테이블 구조의 변화에 덜 유연하다

2) Relation에 의해 복잡한 쿼리가 필요시된다.

 

NoSQL의 장점

데이터의 구조를 미리 정의해놓지 않기 때문에 데이터 구조 변화에 유연하다

 

NoSQL의 단점

데이터의 무결성이 보장되지 않는다.

데이터가 여러 컬렉션에 중복되어 있기 때문에 수정 시 모든 컬렉션에서 업데이트 해주어야함.

 

SQL이 더 좋을 때

1) 관계를 맺고 있는 데이터가 자주 변경되는 앱의 경우(NoSQL은 여러 컬렉션을 수정)

2) 무결성이 매우 중요시되는 데이터를 관리해야 하는 경우(금융 앱)

 

NoSQL이 더 좋을 때

1) 데이터 변경이 자주 이루어지지 않고 Read를 많이 하는 경우

2) 수평확장을 통해 서버 성능을 쉽게 향상시킬 수 있다.(막대한 양의 데이터를 다뤄야 하는 경우에 좋다)

Anomaly(이상 현상)

정규화를 해야 하는 이유이며, 잘못된 테이블 설계로 인해 발생하는 현상을 의미한다.

1) 삽입 이상(Insertion Anomaly)

원치 않은 정보까지 함께 입력해야 하는 경우를 삽입 이상이라고 합니다. 예를 들어서, 신입 학생이 입학하여 학년과 학번 등을 입력하려고 했으나, 아직 성적이 없는 상태이기 때문에 불필요한 성적정보를 함께 삽입해야하는 경우입니다.

2) 삭제 이상(Deletion Anomaly)

어떤 정보를 삭제하고자 하는데, 정보가 속한 튜플의 유용한 다른 데이터까지 모두 삭제되는 경우가 삭제 이상입니다. 예를 들어, 수강 철회로 수강 정보를 삭제하고자 하는데, 학생에 대한 기본 정보까지 모두 삭제되는 경우입니다.

3) 갱신 이상(Update Anomaly)

중복된 데이터가 있다고 가정했을 때, 일부만 바꾸어서 데이터의 불일치 문제가 발생하는 현상입니다. 예를 들어, 어떤 학생의 수강 정보가 바뀌었는데 다른 테이블에서는 바뀌기 전 데이터를 가지고 있는 경우입니다.

 

정규화(Nomalization)

정규화는 데이터베이스의 이상현상의 원인이 되는 데이터 중복성을 제거해서 무결성을 보존하는 것입니다.

 

제1 정규화

어떤 릴레이션에 속한 모든 도메인이 원자값만 가지도록 분해하는 과정이 제1 정규화입니다.

 

 

제2 정규화

릴레이션에서 기본키를 제외한 모든 컬럼이 완전 함수적 종속을 이룰 수 있도록 부분적 종속을 없애는 과정입니다.

이를 통해 Anomaly현상을 해결할 수 있습니다.

(함수적 종속 : A가 B를 결정하면 B는 A의 함수적 종속 예) 나재원은 25살이다  = 나이는 함수적 종속 )

학년은 기본키의 부분인 학번에만 함수적 종속이며, 성적은 기본키 모두에 완전 함수적 종속관계이다.

따라서 기본키에 속하지 않은 모든 속성이 기본 키에 완전 함수적 종속상태가 되도록 릴레이션은 분해해주어야 한다.

성적(학번, 과목 번호, 성적) => 기본키 : {학번, 과목번호}, 외래키 : {학번}

학년(학번, 학년) => 기본키 : 학번

 

제3 정규화

제2 정규형을 만족한 릴레이션에서 기본키에 속하지 않은 모든 속성이 기본키에 이행적 함수 종속이 아니도록 하는 과정입니다.

제 2 정규형을 통해서 Anomaly를 해결했지만 더 복잡한 경우에는 갱신 이상(데이터 중복에 의한 불일치)이 발생하는데 이를 제3 정규화로 해결한다.

(이행적 함수 종속 : 기본 키 외의 다른 속성이 다른 속성을 결정할 수 없다. 기본키는 ZIP컬럼을 결정하게 되는데 만약 여러 기본키 값이 같은 ZIP을 갖고 있다면, ZIP코드를 통해 street city state를 결정할 수 있기 때문에 이 컬럼들에는 중복 데이터들이 생기게 된다.  ZIP => street city state를 결정하도록 하면 안된다)

따라서 테이블을 분리한다.

원래 테이블에서 street city state를 지우고 ZIP을 기본키로 한 새로운 테이블을 만든다. 이를 통해 데이터 중복성을 해결할 수 있다.

 

BCNF(Boyce and Codd 정규형)

BCNF는 제3 정규형을 조금 더 강화한 것으로, 제3 정규화로 해결할 수 없는 Anomaly를 해결하는 방안입니다. BCNF는 모든 결정자가 후보키 집합에 속한 정규형입니다.

후보키는 {학생, 과목}이 될 수 있습니다. 그런데 교수가 한 과목만 강의할 수 있다고 가정한다면, 교수가 과목을 결정하게 됩니다. 그런데 교수는 후보키가 아닙니다. 이러한 경우 제3 정규형은 만족했지만 보이스코드 정규형은 만족하지 않았다고 볼 수 있습니다. 즉, 일반 컬럼이 후보키를 결정하는 경우입니다. 이러면 데이터가 중복되어 갱신이상이 발생하게됩니다. Mr.Sim이 과목을 바꾸면 두 개의 레코드를 갱시내야하기 때문입니다. 이를 해결하기 위해서는

문제를 일으킨 결정자를 기본키로두고 결정된 후보키를 일반속성으로하여 테이블을 만듭니다.

기존 테이블은 학생 과목이 원래 후보키였으니 그대로 두고 결정자를 없앴습니다.

 

제4 정규형

다치 종속성을 제거하는 과정입니다.



개발자마다 여러개의 자격증 값들이 존재하고

개발자마다 여러 언어값들이 존재하는 경우인데, 자격증와 언어는 관계가 없는 경우에 다치종속이라고 한다.

<개발자, 자격증>, <개발자, 언어>테이블로 분리하여 관리하면 다치 종속 관계를 제거하기 때문에 제 4정규형을 만족한다.

제5 정규화

조인 속성을 제거하는 과정으로 제4 정규화를 통해 만들어진 두개의 테이블을 조인하면 다치종속성이 생기기 때문에 아예 조인속성을 없애버리는 것, 관계 없는 두 속성에 대한 테이블을 또 하나 만듬

역정규화

정규화를 통해서 릴레이션을 계속 분리했는데, 그러면 원하는 자료가 하나의 릴레이션에 존재하지 않기 때문에 외래키를 통해서 다른 릴레이션들을 참조해야할 일이 많아집니다. 이는 검색 성능 저하로 이어질 수 있기 때문에 역정규화를 통해서 릴레이션을 다시 통합하거나 분할해서 구조를 재조정하는 것을 의미합니다.

1)릴레이션을 다시 분할하고 병합하는 방법이 있고 작업 효율을 향상시키기 위한 파생속성을 만드는 방법이 있습니다.

인덱스

인덱스는 RDBMS의 검색속도를 향상시키는 기술입니다.(쉽게 : 인덱스로 설정한 레코드는 TREE구조인 MYI파일에 저장되고 이것을 검색)

테이블의 컬럼을 따로 파일로 저장하는 색인화 과정을 통해서 특정 레코드를 검색할 때, 해당 테이블의 레코드의 전체를 읽는 것이 아니라 색인화 되어있는 INDEX파일을 검색해서 검색속도를 빠르게합니다. 검색속도가 빠른 이유는 INDEX파일이 TREE구조로 색인화되어 있기 때문입니다. 실제로는 B-Tree와 B+Tree를 사용한다고 알려져있습니다.

 

테이블을 생성하면 MYD,MYI,FRM 3가지의 파일이 생성됩니다. 테이블만 생성하면 MYI파일은 비어져 있는데 인덱싱을 하게되면 MYI파일이 생성되면서 이후에 상용자가 SELECT쿼리를 하게되면 INDEX를 사용하는 Column을 탐색할 때, MYI파일의 내용을 검색하게됩니다.

 

- 단점

MYI파일이 별도로 생성되기 때문에 파일 전반적(.mdb)파일의 크기가 커지게 됩니다.

성능적인 측면에서 봤을 때, 데이터 변경 작업이 자주 일어나는 경우에는 성능이 하락하게됩니다.

 

DML(SELECT DELETE UPDATE쿼리) 시, 단점

1) INSERT

트리구조로 되어있는 인덱스파일에서 여유공간이 없게되면 임의의 새로운 블록을 만들어 기록하고 기존 블록에 빈 공간을 만들어서 새로운 데이터를 추가하기 때문에 성능적으로 문제가 발생하게 된다.

 

2)DELETE

TABLE에서 데이터가 DELETE되면 지워지고 빈 공간을 다른 데이터가 사용하는데,

INDEX에서 데이터가 DELETE되면 삭제가 되는게 아니라 사용안됨 표시만 해놓기 때문에 불필요한 데이터들이 쌓여서 오히려 검색 성능이 하락될 수도 있다.

 

3)UPDATE

INDEX에서는 UPDATE개념이 없기 때문에 DELETE와 INSERT작업을 해야하기 때문에 성능이 안좋다.

 

트랜잭션(Transaction)

데이터베이스의 상태를 변화시키기 위해 수행하는 작업 단위를 의미합니다.

이때, 상태를 변화라는 것은 SQL문을 통해서 데이터베이스에 접근하여 검색 삽입 삭제 수정하는 것을 의미합니다.

그리고 작업 단위라는 어떤 상황에 따라 정하는 기준입니다. 예를들어서, A가 B에게 돈을 송금한다고 하면, A의 계좌가 Update되고 그 다음 B의 계좌가 Update되어야 합니다. 이 두 번의 Update쿼리가 하나의 작업 단위가 되는 것입니다.

 

두 번의 Update쿼리가 모두 성공하게 되면 하나의 작업이 완료된 것이므로 Commit

두 번 중 하나의 Update쿼리라도 실패하면 모든 쿼리문을 취소하고 이 전 상태로 돌려놓아야 하기 때문에 Rollback입니다.

 

트랜잭션의 특징

- 원자성(Atomicity) : 트랜잭션이 DB에 모두 반영되거나, 혹은 전혀 반영되지 않아야 한다.

- 일관성(Consistency) : 트랜잭션의 작업 처리 결과는 항상 일관성 있어야 한다.

- 독립성(Isolation) : 둘 이상의 트랜잭션이 동시에 병행 실행되고 있을 때, 트랜잭션들은 서로 영향을 끼쳐선 안된다.

- 지속성(Durability) : 트랜잭션이 완료되었으면, 영구적으로 결과가 반영되어야 한다.

 

Commit : 트랜잭션이 성공적으로 끝났고, DB가 일관성있는 상태일 때 이를 알려주기 위해 사용하는 연산

Rollback : 트랜잭션 하나가 비정상적으로 종료되어 원자성이 깨진 경우에 트랜잭션의 시작단계로 롤백할 수 있습니다.

 

Transaction관리를 위한 DBMS의 전략

트랜잭션이 관리된다는 의미는, 트랜잭션에 의해 데이터베이스의 데이터가 변경될 때 데이터의 무결성을 해치지 않게 보장해준다는 것과 같다고 생각합니다. 이를 위해서는 데이터베이스의 트랜잭션 설계를 올바르게 하는 것도 중요하지만, 시스템 차원에서도 관리가 중요합니다. 왜냐하면 트랜잭션을 수행하는 도중에 디스크에 손상이 생긴다던지, 갑자기 전원이 꺼지는 문제가 발생할 수 있기 때문입니다. 따라서, DB를 관리하는 DBMS에 의해서 이런 시스템적인 오류에 대한 트랜잭션을 관리할 수 있습니다.

 

일단 트랜잭션을 수행하게 되면 그 즉시 데이터베이스에 기록되는 것이 아니라 일단 수행된 트랜잭션들을 로그파일에 기록하게 됩니다. 만약 로그 파일에서 트랜잭션의 시작 Start가 있고 종료 Commit이 없는 경우는 아예 원상복구 시키는 UNDO를 수행하고, COMMIT이 있는 경우에는 재실행을 하는 REDO를 수행하게 됩니다. 이와 같이 로그기반의 회복 기법을 통해서 Transaction을 관리할 수 있습니다.

 

Isolation LEVEL

트랜잭션에서 일관성 없는 데이터를 어디까지 허용하느냐를 결정하는 수준을 의미합니다.

 

Isolation LEVEL의 필요성

데이터베이스는 ACID 특성에서 봤을 때, 각각의 트랜잭션들은 독립적으로 수행해야합니다. 따라서 Locking을 통해 트랜잭션 작업시에 다른 세션이 관여하지 못하도록 막아야 합니다. 하지만 무조건 Locking만 사용하게 되면 Locking을 풀때까지 기다려야하기 때문에 성능이 떨어질 것입니다. 따라서 무조건 적인 Locking을 하지 않고, 어느 정도, 즉 어느 레벨까지의 일관성 없는 데이터를 받아들일지 정의한 것이 Isolation LEVEL입니다.

 

0. Read Uncommitted(레벨 0)

아직 트랜잭션이 처리중이거나 Commit하지 않은 데이터를 다른 트랜잭션이 SELECT하는 것을 허용하는 계층입니다.

즉, 아직 트랜잭션 실행이 끝나지 않았는데도 조회가 가능한 Dirty Read를 허용하는 격리 수준입니다.

 

1. Read Commited(레벨 1)

어떤 트랜잭션의 변경 내용이 Commit되어야지만 다른 트랜잭션에서 조회가 가능한 격리 수준입니다. 레벨1이 가장 일반적인 격리수준입니다. 따라서, Dirty Read를 허용하지 않게 보장해주는 격리 레벨입니다. 하지만 어떤 값이 Update되고 Commit될때마다 다른 값이 보이기 때문에 데이터의 동일성을 보장해주지 않습니다.(다른 트랜잭션에서 Update하기 때문)

 

2.Repeatable Read(레벨 2)

트랜잭션이 시작되기 전에 커밋된 내용에 대해서만 조회할 수 있는 격리수준입니다. 따라서 트랜잭션이 범위 내에서 조회한 데이터 내용이 항상 동일함을 보장하게됩니다. 따라서, Non-Repeatable Read를 허용하지 않음으로써 데이터의 동일성을 보장하게됩니다.

예) Transaction의 변경을 Begin과 함께 사용한 경우에

1. A가 Begin isolation level repeatable read함, B가 Begin을 동시에 하고 B가 Update한 후 Commit함

2.A가 값을 조회하는데 Update한 값이 안보임. 원래 값만 계속 보임

3.즉, 트랜잭션이 Begin하기 전에 Update하고 커밋한 것만 볼 수 있음, 현재 Begin하고 Update한 것은 안보임

 

3.Seralizable(레벨 3)

가장 높은 격리 수준으로, 트랜잭션이 완료될때 까지 Shared Lock을 걸어주는 격리 수준입니다. 다른 격리 레벨은 어떤 값을 Update하더라도 다른 트랜잭션에 Update를 해버리면 자신이 Update한 내용이 유실되어 버립니다. 따라서 Phantom Read가 발생하게 되는데, Seralizable은 트랜잭션을 완전히 종료하지 않는 이상 다른 트랜잭션에서 Update를 못하도록 Shared Lock을 걸어놓는 가장 높은 격리 수준입니다.

예를 들어서, A가 Update를 수행한 다음 B가 Update를 수행하게 되면 A가 변경한 값이 유실됩니다. 따라서, A가 Update 수행을 완전히 끝낼 때까지 B는 트랜잭션에 실패하게됩니다.

 

Redis

데이터를 Key Value형태로 저장해놓는 데이터 저장소인데, 원래 데이터베이스는 하드디스크나 SSD에 저장이 되지만, Redis는 RAM에 저장을 해서 디스크를 스캐닝하지 않고 빠르게 검색하는 장점이 존재합니다. 그런데, RAM은 휘발성 메모리이기 때문에 컴퓨터를 껏다 키면 다 날라가는데 이를 대비하기 위해서 백업과정이 존재합니다. SnapShot을 이용해서 특정 지점에 대해서 디스크에 백업해놓는 것과, 쿼리들을 저장해누고 서버가 셧다운 되면 재실행해서 다시 만들어 놓는 방식이 있습니다.