DB

데이터베이스 기본 용어

✨ 테이블(Table)

행과 열로 이루어진 데이터의 집합을 테이블이라고 한다.
흔히 생각하는 엑셀을 떠올리면 편하다.
일반적인 데이터베이스에서는 행과 열만 있으면 테이블이라고 하지만, 관계형 데이터베이스에서는 여기에 특별한 제약을 추가해서 **릴레이션(Relation)**이라고 부른다.
아래 조건을 충족하는 테이블만이 릴레이션이 될 수 있기 때문에 모든 릴레이션은 테이블이지만, 모든 테이블이 릴레이션인건 아니다.

모든 값은 유일한 값을 가진다.
하나의 릴레이션에서 중복되는 행이 존재하면 안된다.

✨ 행(Row)

테이블을 구성하는 데이터들 중 가로로 묶은 데이터셋을 의미한다.
일반적으로 행은 한 객체에 대한 정보를 가지고 있다.
이또한 관계형 데이터베이스에서는 튜플 또는 레코드라는 이름으로 불린다.

✨ 열(Colum)

테이블을 구성하는 데이터들 중 세로로 묶은 데이터셋을 의미한다.
일반적으로 열은 그 테이블의 속성을 의미하며 열을 구성하는 값들은 같은 **도메인(Domain)**으로 되어 있다.
이또한 관계형 데이터베이스에서는 **속성(Attribute)**이라는 이름으로 불린다.

✨ 도메인(Domain)

데이터베이스에서 필드(Field)에 채워질 수 있는 값의 집합이다.
예를 들어, 도메인이 1에서 10사이의 정수인 속성의 필드에 11이나 -1처럼 도메인을 벗어나는 값 또는 "고양이"처럼 아예 자료형이 다른 값이 들어갈 수 없다.

✨ 스키마(Schema)

데이터베이스의 구조를 전반적으로 기술한 것을 말한다.
구체적으로 데이터베이스를 구성하는 데이터 레코드의 크기, 키의 정의, 레코드 간의 관계 등을 정의한 것을 말한다.
사용자의 관점에 따라 외부 스키마, 개념 스키마, 내부 스키마로 구분한다.
DBMS는 외부 스키마에 명세된 사용자의 요구를 개념 스키마 형태로 변환하고, 이를 다시 내부 스키마 형태로 변환한다.

외부 스키마

사용자의 입장에서 정의한 데이터베이스의 논리적 구조
데이터들을 어떤 형식, 구조, 화면을 통해 사용자에게 보여줄 것인가에 대한 명세를 말하며 하나의 데이터베이스에는 여러 개의 외부 스키마가 있을 수 있다.
일반 사용자에게는 질의어를 이용해 DB를 쉽게 사용할 수 있도록 하고 응용 프로그래머는 언어를 사용해서 DB에 접근하도록 한다.

개념 스키마

조직체 전체를 관장하는 입장에서 DB를 정의한 스키마.
DB에 대한 모든 논리적 구조를 기술하기 때문에 데이터베이스에 하나만 존재하며, 통상 스키마라고 하면 개념 스키마를 일컫는다.

내부 스키마

데이터베이스가 어떻게 저장 장치에 저장될 지에 대한 명세.
물리적인 저장 장치와 데이터베이스 간의 관계를 정의하므로 시스템 프로그래머나 시스템 설계자가 보는 관점의 스키마이다.

Key

Key란? 검색이나 정렬 시 Tuple을 구분할 수 있는 기준이 되는 Attribute.

✨ 1. Candidate Key(후보키)

릴레이션을 구성하는 속성들 중에서 Tuple을 유일하게 식별할 수 있는 속성들의 부분 집합을 의미한다.(기본키로 사용할 수 있는 속성들을 후보키라 한다.)
모든 릴레이션은 반드시 하나 이상의 후보 키를 가져야 한다.
릴레이션에 있는 모든 튜플에 대해서 유일성과 최소성을 만족시켜야 한다.

아래 2가지 조건을 만족해야 한다.

유일성 : Key로 하나의 Tuple을 유일하게 식별할 수 있음.
최소성 : 꼭 필요한 속성으로만 구성.

example

[학생] 릴레이션에서 학번이나 주민번호는 다른 레코드를 유일하게 구별할 수 있는 기본키로 사용할 수 있으므로 후보키가 될 수 있다. 즉, 기본키가 될 수 있는 키들을 후보키라 한다.

✨ 2. Primary Key(기본키)

후보 키 중 선택한 Main key
한 릴레이션에서 특정 튜플을 유일하게 구별할 수 있는 속성
Null 값을 가질 수 없다.(개체 무결성의 첫 번째 조건)
기본 키로 정의된 속성에는 동일한 값이 중복되어 저장될 수 없다.(개체 무결성의 두 번째 조건)

아래 조건을 만족해야 한다.

유일성 : 기본키를 구성하는 컬럼은 테이블에서 레코드를 식별할 수 있도록 유일해야 한다.
최소성 : 유일성을 만족하는 한도 내에서 최소한의 컬럼(하나 이상)으로 구성되어야 한다.
개체 무결성 : 기본키가 가지고 있는 값의 유일성을 보장받아야 한다.

example

[학생] 릴레이션에는 학번이나 주민 번호가 기본키가 될 수 있고, [수강] 릴레이션에는 학번+과목명으로 조합해야 기본키가 만들어질 수 있다. 왜냐하면 [수강] 릴레이션에서는 학번 속성과 과목명 속성은 개별적으로 기본키로 사용할 수 없다. 다른 튜플들과 구별되지 않기 때문이다.

✨ 3. Alternate Key(대체키)

후보키가 둘 이상일 때, 기본 키를 제외한 나머지 후보키들을 말한다.
보조키라고도 한다.

example

[학생] 릴레이션에서 학번을 기본키로 정하면 주민번호 는 대체키가 된다.

✨ 4. Super Key(슈퍼키)

한 릴레이션 내에 있는 속성들의 집합으로 구성된 키로서 릴레이션을 구성하는 모든 튜플 중 슈퍼키로 구성된 속성의 집합과 동일한 값을 나타내지 않는다.
릴레이션을 구성하는 모든 튜플에 대해서 유일성은 만족하지만, 최소성은 만족시키지 못한다.

example

[학생] 릴레이션에서는 학번, 주민번호, 학번+주민번호, 학번+주민번호+성명 등으로 슈퍼키를 구성할 수 있다. 또한, 여기서 최소성을 만족시키지 못한다는 말은 학번+주민번호+성명이 슈퍼키인 경우, 3개의 속성 조합을 통해 다른 튜플과 구별이 가능하지만, 성명 단독적으로 슈퍼키를 사용했을 때는 구별이 가능하지 않기 때문에 최소성을 만족시키지 못한다.

즉, 뭉쳤을 경우 유일성이 생기고 흩어지면 몇몇 속성들은 독단적으로 유일성이 있는 키로 사용할 수 없다. 이것을 최소성을 만족하지 못한다고 한다.

✨ 5. Foreignn Key(외래키)

관계(Relation)를 맺고 있는 릴레이션 R1,R2에서 릴레이션 R1이 참조하고 있는 릴레이션 R2의 기본키와 같은 R1 릴레이션의 속성을 외래키라고 한다.
외래키는 참조되는 릴레이션의 기본키와 대응되어 릴레이션 간에 참조 관계를 표현하는 데 중요한 도구로 사용된다.
외래키로 지정되면 참조 테이블의 기본키에 없는 값은 입력할 수 없다.(참조 무결성의 조건)

example

[수강] 릴레이션이 [학생] 릴레이션을 참조하고 있으므로, [학생] 릴레이션의 학번은 기본키이고, [수강] 릴레이션의 학번은 외래키이다.

즉, 각 릴레이션의 입장에서 속성은 기본키가 되기도 하고, 외래키가 되기도 한다.

[수강] 릴레이션의 학번에는 [학생] 릴레이션의 학번에 없는 값은 입력할 수 없다.

기타

Null 값?

데이터베이스에서 아직 알려지지 않았거나, 모르는 값으로서 "해당 없음" 등의 이유로 정보 부재를 나타내기 위해 사용하는, 이론적으로 아무것도 없는 특수한 데이터를 뜻한다.

SQL injection

해커에 의해 조작된 SQL 쿼리문이 데이터베이스에 그대로 전달되어 비정상적 명령을 실행시키는 공격 기법을 말한다.

✨ 공격 방법

1) 인증 우회

보통 로그인을 할 때, 아이디와 비밀번호를 input 창에 입력한다.

예를 들어, 아이디가 abc 비밀번호가 1234일 때, 쿼리는 아래와 같은 방식으로 전송될 것이다.

SELECT * FROM USER WHERE ID = "abc" AND PASSWORD = "1234";

SQL Injection으로 공격할 때, input 창에 비밀번호를 입력함과 동시에 다른 쿼리문을 함께 입력하는 것이다.

1234; DELETE * FROM USER WHERE ID = "1";

보안이 완벽하지 않은 경우, 이처럼 비밀번호가 아이디와 일치해서 True가 되고 뒤에 작성한 DELETE문도 데이터베이스에 영향을 줄 수도 있게 되는 치명적인 상황이다.

이 밖에도 기본 쿼리문의 WHERE절에 OR문을 추가하여 '1' = '1' 과 같은 true문을 작성하여 무조건 적용되도록 수정한 뒤 DB를 마음대로 조작할 수도 있다.

2) 데이터 노출

시스템에서 발생하는 에러 메시지를 이용해 공격하는 방법이다.

보통 에러는 개발자가 버그를 수정하는 면에서 도움을 받을 수 있는 존재이다. 해커들은 이를 역이용해 악의적인 구문을 삽입하여 에러를 유발시킨다.

예를 들면, 해커는 GET 방식으로 동작하는 URL 쿼리 스트링을 추가하여 에러를 발생시킨다.

이에 해당하는 오류가 발생하면 이를 통해 해당 웹앱의 데이터베이스 구조를 유추할 수 있고 해킹에 활용한다.

✨ 방어 방법

1) input 값을 받을 때, 특수 문자 여부 검사하기

로그인 전, 검증 로직을 추가하여 미리 설정한 특수문자들이 들어왔을 때 요청을 막아낸다.

2) SQL 서버 오류 발생 시, 해당하는 에러 메시지 감추기

view를 활용해 원본 데이터베이스 테이블에는 접근 권한을 높인다. 일반 사용자는 view로만 접근하여 에러를 볼 수 없도록 만든다.

3) prepare statement 사용하기

prepare statement를 사용하면 특수문자를 자동으로 escaping 해준다.

(statement와는 다르게 쿼리문에서 전달인자 값을 ?로 받는것)이를 활용해 서버 측에서 필터링 과정을 통해서 공격을 방어한다.

Join

✨ 조인이란

두 개 이상의 테이블이나 데이터베이스를 연결하여 데이터를 검색하는 방법을 말한다.
테이블을 연결하려면, 적어도 하나의 칼럼을 서로 공유하고 있어야 하므로 이를 이용하여 데이터 검색에 활용한다.

✨ Join 종류

INNER JOIN
LEFT OUTER JOIN
RIGHT OUTER JOIN
FULL OUTER JOIN
CROSS JOIN
SELF JOIN

문법 SELECT 테이블별칭.조회할컬럼, 테이블별칭.조회할컬럼 FROM 기준테이블 별칭 INNER JOIN 조인테이블 별칭 ON 기준테이블별칭.기준키 = 조인테이블별칭.기준키 ...

1) INNER JOIN

쉽게 말해 교집합이라고 생각하면 된다.
기준 테이블과 Join한 테이블의 중복된 값을 보여준다.
결과값은 A의 테이블과 B 테이블이 모두 가지고 있는 데이터만 검색된다.

Ex) SELECT A.NAME, B.AGE FROM EX_TABLE A INNER JOIN JOIN_TABLE B ON A.NO_EMP = B.NO_EMP

2) LEFT OUTER JOIN

기준 테이블의 값 + 테이블과 기준테이블의 중복된 값을 보여준다.
왼쪽 테이블을 기준으로 Join을 하겠다고 생각하면 된다.
결과값은 A 테이블의 모든 값과 A 테이블과 B 테이블의 중복되는 값이 검색된다.

SELECT A.NAME, B.AGE FROM EX_TABLE A LEFT OUTER JOIN JOIN_TABLE B ON A.NO_EMP = B.NO_EMP

3) RIGHT OUTER JOIN

LEFT OUTER JOIN의 반대이다.
오른쪽 테이블을 기준으로 Join을 하겠다고 생각하면 된다.
결과값은 B 테이블의 모든 데이터와 A 테이블과 B 테이블에서 중복되는 값이 검색된다.

SELECT A.NAME, B.AGE FROM EX_TABLE A RIGHT OUTER JOIN JOIN_TABLE B ON A.NO_EMP = B.NO_EMP

4) FULL OUTER JOIN

쉽게 말해 합집합을 생각하면 된다.
A 테이블이 가지고 있는 데이터, B 테이블이 가지고 있는 데이터 모두 검색된다.
사실상, 기준 테이블의 의미가 없다.

SELECT A.NAME, B.AGE FROM EX_TABLE A FULL OUTER JOIN JOIN_TABLE B ON A.NO_EMP = B.NO_EMP

5) CROSS JOIN

모든 경우의 수를 전부 표현해주는 방식이다.
기준 테이블이 A일 경우, A의 데이터 한 ROW를 B 테이블 전체와 JOIN하는 방식이다.
결과값은 N*M이다.
위의 경우 A 테이블에 데이터가 3개, B 테이블에 데이터가 4개가 있으므로 총 12개가 검색된다.
-첫 번째 방식-- SELECT A.NAME, B.AGE FROM EX_TABLE A CROSS JOIN JOIN_TABLE B --두 번째 방식-- SELECT A.NAME, B.AGE FROM EX_TABLE A, JOIN_TABLE B

SELF JOIN

자기 자신과 자기 자신을 조인한다는 의미이다.
하나의 테이블을 여러번 복사해서 조인한다고 생각하면 된다.
자신이 가지고 있는 칼럼을 다양하게 변형시켜 활용할 경우에 자주 사용한다.

SELECT A.NAME, B.AGE FROM EX_TABLE A, EX_TABLE B

SQL vs NoSQL

✨ SQL (관계형 DB)

SQL을 사용하면 RDBMS에서 데이터를 저장, 수정, 삭제 및 검색할 수 있다.
관계형 데이터베이스에는 핵심적인 두 가지 특징이 있다.
1. 데이터는 정해진 데이터 스키마에 따라 테이블에 저장된다.
2. 데이터는 관계를 통해 여러 테이블에 분산된다.

데이터는 테이블에 레코드로 저장되는데, 각 테이블마다 명확하게 정의된 구조가 있다. 해당 구조는 필드의 이름과 데이터 유형으로 정의된다.

따라서 스키마를 준수하지 않은 레코드는 테이블에 추가할 수 없다. 즉, 스키마를 수정하지 않는 이상은 정해진 구조에 맞는 레코드만 추가가 가능한 것이 관계형 데이터베이스의 특징 중 하나이다.

또한, 데이터의 중복을 피하기 위해 관계를 이용한다.

하나의 테이블에서 중복 없이 하나의 데이터만을 관리하기 때문에 다른 테이블에서 부정확한 데이터를 다룰 위험이 줄어드는 장점이 있다.

✨ NoSQL(비관계형 DB)

말그대로 관계형 DB의 반대이다.
스키마도 없고 관계도 없다.
NoSQL에서는 레코드를 문서(documents)라고 부른다.

여기서 SQL과 핵심적인 차이가 있다. SQL은 정해진 스키마를 따르지 않으면 데이터 추가가 불가능했다.

하지만 NoSQL에서는 다른 구조의 데이터를 같은 컬렉션에 추가가 가능하다.

문서(documents)는 Json과 비슷한 형태로 가지고 있다. 관계형 데이터베이스처럼 여러 테이블에 나누어담지 않고, 관련 데이터를 동일한 '컬렉션'에 넣는다.

따라서 위의 사진의 SQL에서 진행한 Orders, Users, Products 테이블로 나눈 것을 NoSQL에서는 Orders에 한꺼번에 포함해서 저장하게 된다.

따라서 여러 테이블에 조인할 필요없이 이미 필요한 모든 것을 갖춘 문서를 작성하는 것이 NoSQL이다.

(NoSQL에는 조인이라는 개념이 존재하지 않는다.)

그러면 조인하고 싶을 때 NoSQL은 어떻게 할까?

컬렉션을 통해 데이터를 복제하여 각 컬렉션 일부분에 속하는 데이터를 정확하게 산출하도록 한다.

하지만, 이러면 데이터가 중복되어 서로 영향을 줄 위험이 있다. 따라서 조인을 잘 사용하지 않고 자주 변경되지 않는 데이터일 때, NoSQL을 쓰면 상당히 효율적이다.

✨ 확장 개념

두 데이터베이스를 비교할 때 중요한 Scaling 개념도 존재한다.

데이터베이스 서버의 확장성은 수직적 확장과 수평적 확장으로 나누어진다.

수직적 확장 : 단순히 데이터베이스 서버의 성능을 향상시키는 것(ex. CPU 업그레이드)
수평적 확장 : 더 많은 서버가 추가되고 데이터베이스가 전체적으로 분산됨을 의미한다.(하나의 데이터베이스에서 작동하지만, 여러 호스트에서 작동)

데이터 저장 방식으로 인해 SQL DB는 일반적으로 수직적 확장만 지원한다.

수평적 확장은 NoSQL DB에서만 가능하다.

둘 중에 뭘 선택하느냐에 대한 정답은 없다. 어떤 데이터를 다루느냐에 따라 원하는 방식에 맞게 선택을 고려하면 된다.

✨ SQL 장/단점

[장점]

명확하게 정의된 스키마, 데이터 무결성 보장
관계는 각 데이터를 중복 없이 한번만 저장.

[단점]

덜 유연하다. 데이터 스키마를 사전에 계획하고 알려야 한다.(나중에 수정하기 힘듦.)
관계를 맺고 있어서 조인문이 많은 복잡한 쿼리가 만들어질 수 있음.
대체로 수직적 확장만 가능하다.

✨ NoSQL 장/단점

[장점]

스키마가 없어서 유연하다. 언제든지 저장된 데이터를 조정하고 새로운 필드 추가가 가능하다.
데이터는 애플리케이션이 필요로 하는 형식으로 저장된다. 데이터를 읽어오는 속도가 빨라진다.
수직 및 수평 확장이 가능해서 애플리케이션이 발생시키는 모든 읽기/쓰기 요청 처리가 가능하다.

[단점]

유연성으로 인해 데이터 구조 결정을 미루게 될 수 있다.
데이터 중복을 계속 업데이트 해야 한다.
데이터가 여러 컬렉션에 중복되어 있기 때문에 수정시 모든 컬렉션에서 수행해야 한다. (SQL에서는 중복 데이터가 없으므로 한번만 수행이 가능)

✨ SQL DB 사용이 더 좋을 때

관계를 맺고 있는 데이터가 자주 변경되는 애플리케이션의 경우
NoSQL에서는 여러 컬렉션을 모두 수정해야 하기 때문에 비효율적
변경될 여지가 없고, 명확한 스키마가 사용자와 데이터에게 중요한 경우

✨ NoSQL DB 사용이 더 좋을 때

정확한 데이터 구조를 알 수 없거나 변경/확장이 될 수 있는 경우
읽기를 자주 하지만, 데이터 변경은 자주 없는 경우
데이터베이스를 수평으로 확장해야 하는 경우(막대한 양의 데이터를 다뤄야 하는 경우)

이상(Anomaly)

✨ 정규화를 해야 하는 이유는 잘못된 테이블 설계로 인해 Anomaly(이상 현상)가 나타나기 때문이다.

여기서 Anomaly가 무엇인지 알아보자.

Ex)

{Student ID, CourseID, Department, Course ID, Grade}

1) 삽입 이상(Insertion Anomaly)

기본키가 {Student ID, Course ID} 인 경우

Course를 수강하지 않은 학생은 Course ID가 없는 현상이 발생한다. 결국, Course ID를 Null로 할 수 밖에 없는데, 기본키는 Null이 될 수 없으므로 테이블에 추가될 수 없다.

굳이 삽입하기 위해서는 '미수강'과 같은 Course ID를 만들어야 한다.

불필요한 데이터를 추가해야 삽입할 수 있는 상황 => Insertion Anomaly

2) 갱신 이상(Update Anomaly)

만약 어떤 학생의 전공 {Department}이 "컴퓨터 -> 음악"으로 바뀌는 경우

모든 Department를 "음악"으로 바꾸어야 한다. 그러나 일부를 깜빡하고 바꾸지 못하는 경우, 제대로 파악하지 못한다.

일부만 변경하여 데이터가 불일치하는 모순의 문제 => Update Anomaly

3) 삭제 이상(Deletion Anomaly)

만약 어떤 학생이 수강을 철회하는 경우, {Student ID, Course ID, Department, Course ID, Grade}의 정보 중 Course ID를 삭제하면 Student ID, Department와 같은 학생에 대한 정보도 함께 삭제된다.

튜플 삭제로 인해 꼭 필요한 데이터까지 함께 삭제되는 문제 => Deletion Anomaly

인덱스(INDEX)

✨ INDEX의 의미

RDBMS에서 검색 속도를 높이기 위해 사용하는 하나의 기술이다.
INDEX는 색인이다.
해당 Table의 컬럼을 색인화(따로 파일로 저장)하여 검색시 해당 Table의 레코드를 full scan하는게 아니라 색인화 되어있는 INDEX 파일을 검색하여 검색 속도를 빠르게 한다.
INDEX 구조는 Tree 구조로 색인화한다.
RDBMS에서 사용하는 INDEX는 Balance Search Tree를 사용한다.

✨ INDEX의 원리

INDEX를 해당 컬럼에 주게 되면 초기 Table 생성시, FRM, MYD, MYI 3개의 파일이 만들어진다.

FRM : 테이블 구조가 저장되어 있는 파일
MYD : 실제 데이터가 있는 파일
MYI : INDEX 정보가 들어있는 파일

INDEX를 사용하지 않는 경우, MYI 파일은 비어져 있다.

그러나 INDEX를 해당 컬럼에 만들게 되면 해당 컬럼을 따로 인덱싱하여 MYI 파일에 입력한다.

이후에 사용자가 SELECT 쿼리로 INDEX를 사용하는 쿼리를 사용시 해당 Table을 검색하는 것이 아니라 MYI 파일의 내용을 검색한다.

만약, INDEX를 사용하지 않은 SELECT 쿼리라면 해당 Table Full scan하여 모두 검색한다.

이는 책의 뒷부분에 <찾아보기>와 같은 의미로 정리해둔 단어 중에서 원하는 단어를 찾아서 페이지수를 보고 쉽게 찾을 수 있는 개념과 같다. 만약, 이 <찾아보기>가 없다면 처음부터 끝까지 모든 페이지를 보고 찾아와야할 것이다.

✨ INDEX 장점

키 값을 기초로 하여 테이블에서 검색과 정렬 속도를 향상시킨다.
인덱스를 사용하면 테이블 행의 고유성을 강화시킬 수 있다.
테이블의 기본키는 자동으로 인덱스된다.
필드 중에는 데이터 형식 때문에 인덱스 될 수 없는 필드도 있다.
여러 필드로 이루어진(다중 필드)인덱스를 사용하면 첫 필드 값이 같은 레코드도 구분할 수 있다.

참고로 액세스에서 다중 필드 인덱스는 최대 10개의 필드를 포함할 수 있다.

✨ INDEX 단점

인덱스를 만들면 .mdb 파일 크기가 늘어난다.
사용자가 한 페이지를 동시에 수정할 수 있는 병행성이 줄어든다.
인덱스된 필드에서 데이터를 업데이트하거나 레코드를 추가 또는 삭제할 때 성능이 떨어진다.
인덱스가 데이터베이스 공간을 차지재 추가적인 공간이 필요해진다.(DB의 10% 내외의 공간이 추가로 필요하다.)
인덱스를 생성하는데 시간이 많이 소요될 수 있다.
데이터 변경 작업이 자주 일어날 경우에 인덱스를 재작성해야 할 필요가 있기에 성능에 영향을 끼칠 수 있다.

따라서 어느 필드를 인덱스해야 하는지 미리 시험해보고 결정하는 것이 좋다. 인덱스를 추가하면 쿼리 속도가 1초 정도 빨라지지만, 데이터 행을 추가하는 속도는 2초 정도 느려지게 되어 여러 사용자가 사용하는 경우, 레코드 잠금 문제가 발생할 수 있다.

또, 다른 필드에 대한 인덱스를 만들게 되면 성능이 별로 향상되지 않을 수도 있다. 예를 들어, 테이블에 회사 이름 필드와 성 필드가 이미 인덱스된 경우에 우편 번호를 필드로 추가해 인덱스에 포함해도 성능이 거의 향상되지 않는다. 만드는 쿼리의 종류와 관계 없이 가장 고유한 값을 갖는 필드만 인덱스해야 한다.

✨ INDEX의 목적

RDBMS에는 INDEX가 있다. 인덱스의 목적은 RDBMS의 검색 속도를 높이는데 있다.

SELECT 쿼리의 WHERE절이나 JOIN 예약어를 사용했을 때만 인덱스를 사용하며, SELECT 쿼리의 검색 속도를 빠르게 하는데 목적을 두고 있다.

DELETE, INSERT, UPDATE 쿼리에는 해당 사항이 없으며 INDEX 사용시 오히려 좀 느려진다.

✨ 상황 분석

[사용하면 좋은 경우]

Where절에서 자주 사용되는 Column
외래키가 사용되는 Column
Join에 자주 사용되는 Column

[Index 사용을 피해야 하는 경우]

Data 중복도가 높은 Column
DML이 자주 일어나는 Column

✨ DML에 취약

INSERT
- indext split : 인덱스의 Block들이 하나에서 두개로 나누어지는 현상.
- 인덱스는 데이터가 순서대로 정렬되어야 한다. 기존 블록에 여유 공간이 없는 상황에서 그 블록에 새로운 데이터가 입력되어야 할 경우, 오라클이 기존 블록의 내용 중 일부를 새 블록에다가 기록한 후, 기존 블록에 빈 공간을 만들어서 새로운 데이터를 추가하게 된다.
- 성능면에서 매우 불리하다.
  - Index split은 새로운 블록을 할당받고 Key를 옮기는 복잡한 작업을 수행한다. 모든 수행 과정이 Redo에 기록되고 많은 양의 Redo를 유발한다.
  - Index split이 이루어지는 동안 해당 블록에 대해 키 값이 변경되면 안되므로 DML이 블로킹된다.
DELETE
- 테이블에서 데이터가 Delete될 경우, 지워지고 다른 데이터가 그 공간을 사용할 수 있다.
- index에서 데이터가 delete될 경우, 데이터가 지워지지 않고 사용 안됨 표시만 해둔다.
- 즉, 테이블에 데이터가 1만건 있는 경우, 인덱스에는 2만건이 있을 수 있다는 뜻이다.
- 인덱스를 사용해도 수행 속도를 기대하기 힘들다.
UPDATE
- 인덱스에는 Update 개념이 없다.
- 테이블에 update가 발생할 경우, 인덱스에서는 delete가 먼저 발생한 후 새로운 작업의 insert 작업이 발생한다.
- delete와 insert 두 개의 작업이 인덱스에서 동시에 일어나 다른 DML보다 더 큰 부하를 주게 된다.

정규화

데이터의 중복을 줄이고, 무결성을 향상시킬 수 있는 정규화에 대해 알아보자

✨ Normalization

가장 큰 목표는 테이블 간 중복된 데이터를 허용하지 않는 것이다.

중복된 데이터를 만들지 않으면, 무결성을 유지할 수 있고, DB 저장 용량 또한 효율적으로 관리할 수 있다.

✨ 목적

데이터의 중복을 없애면서 불필요한 데이터를 최소화시킨다.
무결성을 지키고, 이상 현상을 방지한다.
테이블 구성을 논리적이고 직관적으로 할 수 있다.
데이터베이스 구조를 확장에 용이해진다.

정규화에는 여러가지 단계가 있지만, 대체적으로 1~3단계 정규화까지의 과정을 거친다.

✨ 제 1정규화(1NF)

테이블 컬럼이 원자값(하나의 값)을 갖도록 테이블을 분리시키는 것을 말한다.

만족해야 할 조건은 아래와 같다.

어떤 릴레이션에 속한 모든 도메인이 원자값만으로 되어 있어야한다.
모든 속성에 반복되는 그룹이 나타나지 않는다.
기본키를 사용하여 관련 데이터의 각 집합을 고유하게 식별할 수 있어야 한다.

현재 테이블은 전화번호를 여러개 가지고 있어 원자값이 아니다. 따라서 1NF에 맞추기 위해서는 아래와 같이 분리할 수 있다.

✨ 제 2정규화(2NF)

테이블의 모든 컬럼이 완전 함수적 종속을 만족해야 한다.

조금 쉽게 말하면, 테이블에서 기본키가 복합키(키1, 키2)로 묶여있을 때, 두 키 중 하나의 키만으로 다른 컬럼을 결정지을 수 있으면 안된다.

기본키의 부분집합 키가 결정자가 되어선 안된다는 것

Manufacture과 Model이 키가 되어 Model Full Name을 알 수 있다.

Manufacturer Country는 Manufacturer로 인해 결정된다. (부분 함수 종속)

따라서, Model과 Manufacturer Country는 아무런 연관관계가 없는 상황이다.

결국 완전 함수적 종속을 충족시키지 못하고 있는 테이블이다. 부분 함수 종속을 해결하기 위해 테이블을 아래와 같이 나눠서 2NF를 만족할 수 있다.

✨ 제 3정규화(3NF)

2NF가 진행된 테이블에서 이행적 종속을 없애기 위해 테이블을 분리하는 것이다.

이행적 종속 : A → B, B → C면 A → C가 성립된다

아래 두가지 조건을 만족시켜야 한다.

릴레이션이 2NF에 만족한다.
기본키가 아닌 속성들은 기본키에 의존한다.

현재 테이블에서는 Tournament와 Year이 기본키다.

Winner는 이 두 복합키를 통해 결정된다.

하지만 Winner Date of Birth는 기본키가 아닌 Winner에 의해 결정되고 있다.

따라서 이는 3NF를 위반하고 있으므로 아래와 같이 분리해야 한다.

저장 프로시저(Stored Procedure)

✨ 일련의 쿼리를 마치 하나의 함수처럼 실행하기 위한 쿼리의 집합

데이터베이스에서 SQL을 통해 작업을 하다 보면, 하나의 쿼리문으로 원하는 결과를 얻을 수 없을 때가 생긴다. 원하는 결과물을 얻기 위해 사용할 여러줄의 쿼리문을 한 번의 요청으로 실행하면 좋지 않을까? 또한, 인자 값만 상황에 따라 바뀌고 동일한 로직의 복잡한 쿼리문을 필요할 때마다 작성한다면 비효율적이지 않을까?

이럴 때 사용할 수 있는 것이 바로 프로시저다.

✨ 프로시저 장점

최적화 & 캐시
프로시저의 최초 실행 시 최적화 상태로 컴파일이 되며, 그 이후 프로시저 캐시에 저장된다.
만약 해당 프로세스가 여러번 사용될 때, 다시 컴파일 작업을 거치지 않고 캐시에서 가져오게 된다.
유지 보수
작업이 변경될 때, 다른 작업은 건드리지 않고 프로시저 내부에서 수정만 하면 된다. (But, 장점이 단점이 될 수도 있는 부분이기도.. )
트래픽 감소
클라이언트가 직접 SQL문을 작성하지 않고, 프로시저명에 매개변수만 담아 전달하면 된다. 즉, SQL문이 서버에 이미 저장되어 있기 때문에 클라이언트와 서버 간 네트워크 상 트래픽이 감소된다.
보안
프로시저 내에서 참조 중인 테이블의 접근을 막을 수 있다.

✨ 프로시저 단점

호환성
구문 규칙이 SQL / PSM 표준과의 호환성이 낮기 때문에 코드 자산으로의 재사용성이 나쁘다.
성능
문자 또는 숫자 연산에서 프로그래밍 언어인 C나 Java보다 성능이 느리다.
디버깅
에러가 발생했을 때, 어디서 잘못됐는지 디버깅하는 것이 힘들 수 있다.

Statement vs PreparedStatement

우선 속도 면에서 PreparedStatement가 빠르다고 알려져 있다. 이유는 쿼리를 수행하기 전에 이미 쿼리가 컴파일 되어 있으며, 반복 수행의 경우 프리 컴파일된 쿼리를 통해 수행이 이루어지기 때문이다.

Statement에는 보통 변수를 설정하고 바인딩하는 static sql이 사용되고 Prepared Statement에서는 쿼리 자체에 조건이 들어가는 dynamic sql이 사용된다. PreparedStatement가 파싱 타임을 줄여주는 것은 분명하지만 dynamic sql을 사용하는데 따르는 퍼포먼스 저하를 고려하지 않을 수 없다.

하지만 성능을 고려할 때 시간 부분에서 가장 큰 비중을 차지하는 것은 테이블에서 레코드(row)를 가져오는 과정이고 SQL 문을 파싱하는 시간은 이 시간의 10 분의 1 에 불과하다. 그렇기 때문에 SQL Injection 등의 문제를 보완해주는 PreparedStatement를 사용하는 것이 옳다.

트랜잭션

데이터베이스의 상태를 변화시키기 위해 수행하는 작업 단위
상태를 변화시킨다는 것은 SQL 질의어를 통해 DB에 접근하는 것이다.

SELECT INSERT DELETE UPDATE

작업 단위 -> 많은 SQL 명령문들을 사람이 정하는 기준에 따라 정하는 것
Ex) 게시판에서 사용자가 글을 작성하고 올리기 버튼을 누른 후 게시판에 다시 돌아오면 게시판에 내가 쓴 글이 업데이트된 상태를 볼 수 있다.
이때 DB 작업
- 올리기 버튼을 누른다. : INSERT문을 사용해 사용자가 입력한 게시글 데이터를 옮김.
- 게시판 새로 구성 : SELECT문을 사용해 최신 정보를 유지.
현재 작업 단위 : INSERT문+SELECT문
- 이를 통틀어 하나의 트랜잭션이라고 한다.
즉, 하나의 트랜잭션 설계를 잘 만드는 것이 데이터를 다룰 때 많은 이점을 가져다준다.