[Database] DB

바로가기

1. 데이터베이스
2. 데이터베이스 설계
3. 스키마
4. DBMS(데이터베이스 관리 시스템)
5. 데이터베이스 언어
6. 데이터베이스 사용

---

데이터베이스(Database)

• 데이터의 저장소, 여러 사람에 의해 공유되어 사용될 목적으로 통합되어 관리되는 데이터의 집합이다

1. 통합된 데이터(Integrated Data) : 자료의 중복을 배제한 데이터의 집합
2. 저장된 데이터(Stored Data) : 컴퓨터가 접근할 수 있는 저장매체에 저장된 데이터
3. 운영 데이터(Operational Data) : 조직의 고유 업무를 수행하는 데 존재 가치가 확실하고 없어서는 안될 필수 데이터
4. 공용 데이터(Shared Data) : 여러 응용 시스템들이 공동으로 소유하고 유지하는 데이터

데이터베이스 특징

1. 실시간 접근성(Real-Time Accessbility) : 비정형적인 질의(조회)에 대해 실시간 처리에 의한 응답이 가능해야 한다.
2. 내용에 의한 참조(Content Reference): 데이터를 참조할 때 데이터 레코드의 주소나 위치에 의해서가 아닌, 사용자가 요구하는 데이터 내용으로 데이터를 찾는다.
3. 동시공용(Concurrent Sharing) : 데이터베이스는 여러 사용자를 위한 것으로, 다수의 사용자가 동시에 같은 내용의 데이터를 이용할 수 있어야 한다.
4. 지속적인 변화(Continuous Evolution) : 데이터베이스의 상태는 동적으로, 데이터의 삽입, 삭제, 갱신으로 항상 최신의 데이터를 유지한다.

데이터베이스 시스템 구성요소

1. 데이터베이스
2. 스키마
3. DBMS(데이터베이스 관리 시스템)
4. 데이터베이스 언어
5. 데이터베이스 컴퓨터
6. 데이터베이스 사용

---

데이터베이스 설계

• 조직 구성원의 다양한 요구사항을 고려한 데이터베이스를 구축하기 위해서는 요구사항 분석과 분석 결과를 바탕으로 데이터베이스의 논리, 물리적 구조를 제대로 설계하는 것이 중요하다.
• 설계 단계로는 크게 요구사항 분석, 개념적 설계, 논리적 설계, 물리적 설계, 구현이 있다.

단계

1. 요구사항 분석 : 데이터베이스의 용도 파악, 요구 사항 명세
2. 개념적 설계 : DBMS에 독립적인 개념적 구조 설계, 개념적 스키마(E-R 다이어그램) 작성
3. 논리적 설계 : DBMS에 적합한 논리적 구조 설계, 논리적 스키마(릴레이션 스키마) 작성
4. 물리적 설계 : DBMS로 구현 가능한 물리적 구조 설계, 물리적 스키마 작성
5. 구현 : SQL 문을 작성한 후 이를 DBMS에서 실행하여 데이터베이스 생성

---

스키마(Schema)

• 데이터의 구조와 제약조건에 대한 전박적인 명세를 기술한 메타데이터의 집합이다.
• 데이터베이스를 구성하는 데이터 개체, 속성, 관계 및 데이터 조작 시 데이터 값들이 갖는 제약 조건 등에 관해 전반적으로 정의한다.
• 사용자 관점에 따라 스키마가 구분된다.
• 구성 관점으로는 외부 스키마, 개념 스키마, 내부 스키마로 나누어지며, 설계 관점으로는 개념적 설계(개념 스키마), 논리적 설계(논리 스키마), 물리적 설계(내부 스키마)로 나뉘어진다.

스키마 특징

• 데이터 사전에 저장되며, 다른 이름으로 메타데이터라고도 한다.
• 현실 세계의 특정한 한 부분의 표현으로 특정 데이터 모델을 이용해 만들어진다.
• 시간에 따라 불변인 특성을 갖는다.
• 데이터의 구조적 특성을 의마하고, 인스턴스에 의해 규정된다.

외부스키마(External Schema)

• 사용자나 응용프로그래머가 각 '개인의 입장에서 필요로 하는 데이터베이스의 논리적 구조'를 정의한 것이다.
• 전체 데이터베이스의 한 논리적인 부분으로 볼 수 있으며, '서브스키마'라고도 한다.
• 하나의 데이터베이스 시스템에는 여러 개의 외부스키마가 존재할 수 있고, 하나의 외부스키마를 여러 응용 프로그램 사용자가 공용할 수 있다.

개념스키마(Conceptual Schema)

• 데이터베이스의 '전체적인 논리적 구조'로서, 모든 응용 프로그램이나 사용자들이 필요로 하는 데이터를 종합한 조직 전체의 데이터베이스로 하나만 존재한다.
• 개념 스키마는 개체간의 관계와 제약 조건을 나타내고 데이터베이스의 접근 권한, 보안 및 무결성 규칙에 관한 명세를 정의한다.
• 단순히 스키마라 하면 개념 스키마를 의미한다.
• DBA에 의해서 구성된다.

내부스키마(Internal Schema)

• '물리적 저장장치의 입장에서 본 데이터베이스 구조'로, 물리적인 저장장치와 밀접한 계층이다.
• 실제로 데이터베이스에 저장될 레코드의 물리적인 구조를 정의하고, 저장 데이터 항목의 표현방법, 내부 레코드의 물리적 순서등을 나타낸다.
• 시스템 프로그래머나 시스템 설계자가 보는 관점의 스키마이다.

---

DBMS(데이터베이스 관리 시스템)

• 데이터베이스를 조작하는 소프트웨어이다.
• 데이터베이스를 관리하며 사용자들이 데이터베이스를 공유하고 사용할 수 있는 환경을 제공한다.
• 데이터베이스를 구축하는 틀을 제공하고 검색 및 저장하는 기능을 제공한다.
• 데이터베이스에 접근할 수 있는 인터페이스를 제공하며, 복구 기능과 보안성 기능을 제공한다.
• 자료의 통합성을 증진시키고 데이터의 접근이 용이하며, 유지보수 측면에서 효율적이라는 장점이 있다.
• 고비용이고 중앙 집중 관리로 취약점이 존재할 수 있으며, 백업과 복구 방법이 복잡하다는 단점이 있다.
• Oracle, MySQL, MSSQL, MariaDB 등이 있다.

DBMS 기능

1. 정의 : 데이터에 대한 형식, 구조, 조건들을 정의하는 기능이다. 정의 및 설명은 카탈로그나 사전형태로 저장된다.
2. 저장 : 기억장치에 데이터를 저장하는 기능이다.
3. 보안 : 하드웨어나 소프트웨어의 오류 또한 권한이 없는 접근으로부터 시스템을 보호한다.
4. 공유 : 여러 사용자와 프로그램이 데이터베이스에 접근할 수 있도록 공유한다.
5. 기능 : 데이터의 검색을 위한 질의나 데이터베이스의 갱신, 생성 등의 기능을 포함한다.
6. 유지 : 요구사항의 변화에 따라 반영할 수 있도록 하는 기능이다.

---

데이터베이스 언어

• 데이터베이스 언어란 컴퓨터의 데이터베이스 작업을 위한 언어이다.
• 데이터베이스 언어를 통해서 데이터베이스에 접근할 수 있다.

SQL(Structured Query Language)

• SQL은 데이터베이스로부터 요청(질의)를 하고 결과를 받는 언어로 질의어라고 불린다.
• SQL은 데이터베이스 언어와 동의어는 아니다. 데이터베이스 언어의 개념은 순수하게 검색 이상의 기능을 포함하며, SQL은 데이터베이스 이외의 영역에서도 사용할 수 있기 때문이다.
• SQL 문법은 크게 DDL, DML, DCL, TCL로 나뉘어진다.

DDL(Data Definition Language) : 데이터 정의 언어

• 데이터를 정의하는 언어로, 데이터의 전체적인 구조나 골격을 결정하는 역할을 한다.
• 구조를 결정하는 것이기 때문에 어떤 데이터베이스, 스키마, 테이블이 필요한지를 설계한다.
• 전체적인 구조를 CREATE(생성), ALTER(수정), DROP(삭제), TRUNCATE(초기화) 시키는 역할을 한다.

DML(Data Manipulation Language) : 데이터 조작 언어

• 데이터를 조작하는 언어로, 구조가 아닌 테이블 안에 있는 데이터를 조작하는 역할을 한다.
• 데이터를 SELECT(조회), INSERT(추가), UPDATE(수정), DELETE(삭제) 하는 역할을 한다.

DCL(Data Control Language) : 데이터 제어 언어

• 데이터를 제어하는 언어이다. 데이터에 대한 무결성 유지, 병행 수행 제어, 보호와 관리를 위한 언어이다.
• 데이터를 COMMIT(반영), ROLLBACK(취소) 하는 역할이 있으며, 이를 통해 무결성 유지, 병행 수행 제어 등의 기능을 수행할 수 있다. 이는 TCL로도 분류된다.
• 데이터를 사용할 수 있는 권한을 부여(GRANT), 제거(REVOKE) 하는 역할을 한다.
• 권한으론느 CONNECT, SELECT, INSERT, UPDATE, DELETE, USAGE 등이 있다.

TCL(Transaction Control Language) : 트랜잭션 제어 언어

• 트랜잭션을 제어하는 언어로, 트랜잭션은 데이터베이스의 상태를 변화시키기 위해 수행하는 작업의 단위을 한다.
• 수정한 작업을 데이터베이스에 반영(COMMIT), 취소(ROLLBACK), 특정 지점으로 되돌리는(SAVEPOINT) 의 역할을 한다.

트랜잭션의 특징

• 트랜잭션은 트랜잭션은 데이터베이스의 상태를 변화시키기 위해 수행하는 작업의 단위이며, 크게 4가지 Atomicity, Consistency, Isolation, Durability 의 특징을 갖는다.
• 원자성(Atomicity) : 트랜잭션이 데이터베이스에 모두 반영되던가, 아니면 모두 반영되지 않아야 한다는 것이다.
• 일관성(Consistency) : 트랜잭션의 작업 처리 결과가 항상 일관성 있어야 한다는 것이다.
• 독립성(Isolation) : 둘 이상의 트랜잭션이 실행되고 있을 경우, 어떤 하나의 트랜잭션이라도, 다른 트랜잭션의 연산에는 끼어들 수 없다는 특징이다.
• 영속(Durability) : 트랜잭션이 성공적으로 완료된 경우, 결과는 영구적으로 반영되어야 한다는 점이다.

---

데이터베이스 사용

• 데이터베이스 사용을 위해서는 적절한 SQL문을 사용해야 하며, 관련 기능을 알아야 한다.

데이터베이스 생성, 삭제

-- 생성
CREATE DATABASE DB_NAME;

-- 삭제
DROP DATABASE DB_NAME;

테이블 생성, 삭제

-- 생성
CREATE TABLE TB_NAME(
COL\_NAME TYPE CONSTRAINT,
...
);

-- 삭제
DROP TABLE TB_NAME;

컬럼 추가, 삭제, 변경

-- 추가
ALTER TABLE TB_NAME
ADD COL_NAME TYPE;

-- 삭제
ALTER TABLE TB_NAME
DROP COLUMN COL_NAME;

-- 변경
ALTER TABLE TB_NAME
ALTER COLUMN COL_NAME TYPE;

데이터 조회, 추가, 삭제, 변경

-- 데이터 조회
SELECT COL_NAME
FROM TB_NAME
WHERE 1=1;

-- 데이터 추가
INSERT INTO TB_NAME
VALUES(A,B,C);

-- 데이터 삭제
DELETE FROM TB_NAME
WHERE COL_NAME = 1;

-- 데이터 변경
UPDATE TB_NAME
SET COL_NAME = 1
WHERE COL_NAME = 2;

SELECT 쿼리의 수행 순서

  FROM, JOIN, ON > WHERE, GROUP BY, HAVING > SELECT > DISTINCT > ORDER BY > LIMIT

1. FROM : 각 테이블을 확인한다.
2. ON : JOIN의 조건을 확인한다.
3. JOIN : JOIN이 실행되어 데이터가 SET으로 모아지고, 서브쿼리도 함께 포함되어 임시 테이블을 만들 수 있게 도와준다.
4. WHERE : 개별 행에 조건을 적용하며 필요한 데이터를 찾는다.
5. GROUP BY : 지정된 열의 공통 값을 기준으로 그룹화 한다.
6. HAVING : 그룹화된 행에 조건을 적용한다.
7. SELECT : 원하는 자료를 조회한다.
8. DISTINCT : 조회된 자료에서 중복된 행을 제거한다.
9. ORDER BY : 조회된 자료를 조건에 맞게 정렬한다.
10. LIMIT : LIMIT에 설정된 행만큼만 결과로 출력한다.

INNER JOIN 과 OUTER JOIN 차이

• Inner Join 은 서로 연관된 내용만 검색하는 조인 방법이다. (교집합을 의미)
• Outer Join 은 한 쪽에는 데이터가 있고 한 쪽에는 데이터가 없는 경우, 데이터가 있는 쪽의 내용을 전부 출력하는 방법이다. (합집합을 의미)

출처:  Visual-Representation-of-SQL-Joins  explained in detail by  C.L. Moffatt

인덱스(Index)

• 추가적인 쓰기 작업과 저장 공간을 활용하여 데이터베이스 테이블의 검색 속도를 향상시키기 위한 자료구조이다.
• 인덱스는 항상 정렬된 상태를 유지하기 때문에 원하는 값을 검색하는데 빠르지만, 새로운 값을 삽입, 삭제, 수정하는 경우에는 실행 속도가 느려질 수 있다.
• DBMS의 인덱스는 B+Tree 인덱스 자료구조와 해시 테이블을 이용한 방법이 있다.

B+Tree 인덱스 자료구조

• B-Tree를 개선시킨 자료구조로, B-Tree 리프노드들을 LinkedList로 연결하여 순차 검색을 용이하게 한 것이다.
• 해시 테이블보다 시간복잡도 부분에서 성능이 안좋지만 일반적으로 사용되는 자료구조이다.

해시 테이블

• 컬럼의 값으로 생성된 해시를 기반으로 인덱스를 구현한다.
• 시간복잡도는 O(1)로 검색이 매우 빠르다.
• 연속적인 데이터를 위한 순차 검색(부등호, BETWEEN 등)이 불가능하기 때문에 사용에 적합하지 않는 부분이 있다.

트리거(Trigger)

• 트리거는 특정 테이블에 대한 이벤트에 반응해 자동으로 동작하도록 작성된 프로그램이다.
• 사용자가 직접 호출하는 것이 아닌, 데이터베이스에서 자동적으로 호출한다는 것이 가장 큰 특징이다.

이상현상

• 이상현상은 테이블의 설계 단계에서 잘못된 설계로 인해 데이터를 삽입, 삭제, 수정할 때 생기는 논리적인 오류를 뜻한다.
• 이상현상을 예방하고 효과적인 연산을 위해서 데이터 정규화를 하기도 한다.
• 이상현상의 종류로는 삽입이상, 삭제이상, 갱신이상이 있다.

1. 삽입이상(Insertion Anomaly) : 자료를 삽입할 때 특정 속성에 해당하는 값이 없어 NULL을 입력해야 하는 현상
2. 갱신이상(Modification Anomaly) : 중복된 데이터 중 일부만 수정되어 데이터 모순이 일어나는 현상
3. 삭제이상(Deletion Anomaly) : 어떤 정보를 삭제하면, 의도하지 않게 다른 정보까지 삭제되는 현상

SQL Injection

• SQL Injection은 공격자가 악의적인 의도를 갖는 SQL 구문을 삽입하여 데이터베이스를 비정상적으로 조작하는 코드 인젝션 공격 기법이다.
• 이를 방지하기 위해서는 입력값이 개발자가 의도한 값(유효값) 인지 검증한다.
• 저장 프로시저를 사용해 사용하고자 하는 쿼리의 형식을 지정하여 의도치 않는 실행은 막는다.

RDBMS 와 NoSQL

RDBMS

• 데이터를 2차원 테이블 형태로 표현한다.
• 장점으로는 스키마에 맞춰 데이터를 관리하여 데이터의 정합성을 보장할 수 있다.
• 단점으로는 시스템이 커질수록 쿼리가 복잡해지고 성능이 저하되며 Scale-out이 어렵다는 점이 있다.

NoSQL

• RDMS와 반대로 데이터간의 관계를 정의하지 않고, 스키마가 없어 좀 더 자유롭게 데이터를 관리할 수 있으며, 컬렉션이라는 형태로 데이터를 관리한다.
• 장점으로는 스키마 없이 Key-Value 형태로 데이터를 관리해 자유롭게 데이터를 관리할 수 있고, 데이터 분산이 용이하여 성능 향상을 위한 Scale-up, Scale-out 이 가능하다.
• 단점으로는 데이터의 중복이 발생할 수 있고, 중복된 데이터가 변경될 경우 수정을 모든 컬렉션에서 수행해야 한다. 또한 스키마가 존재하기 않기에 명확한 데이터 구조를 보장하지 않아 데이터 구조 결정이 어려울 수 있다.

RDBMS와 NoSQL 선택 기준

• RDBMS : 데이터 구조가 명확하고, 변경될 여지가 적으며 스키마가 중요한 경우 사용하는 것이 좋다.
• NoSQL : 정확한 데이터 구조를 알 수 없고 데이터가 변경/확장 될 수 있는 경우, 그리고 중복된 데이터가 변경될 경우 수정을 모든 컬렉션에서 수행해야하기에 단순 조회 업무만을 수행한다면 사용을 고려해보는 것이 좋다.

DB Lock

• DB Lock은 트랜잭션 처리의 순차성을 보장하기 위한 방법이다.
• 공유락(Shared Lock) Read Lock이라고도 하는 공유락은 트랜잭션이 읽기를 할 때 사용하는 락이며, 데이터를 조회만하기 때문에 같은 공유락은 동시 접근이 가능하다.
• 배타락(Exclusive Lock) Write Lock이라고도 하는 배타락은 데이터를 변경할 때 사용하는 락이며, 트랜잭션이 완료될 때까지 유지되며, 배타락이 끝나기 전까지 어떠한 접근도 허용하지 않는다.

Elastic Search의 키워드 검색과 RDBMS의 LIKE 검색의 차이

• RDMS는 단순 텍스트매칭에 대한 검색만을 제공해 동의어나 유의어 같은 검색은 불가능하다.
• MySQL 최신 버전에서 n-gram 기반의 Full-Text 검색을 지원하긴 하지만, 한글 검색의 경우 아직 부족한 점이 있다.
• Elastic Search는 동의어나 유의어를 활용한 검색이 가능하며, 비정형 데이터의 색인과 검색이 가능하고, 역색인 지원으로 매운 빠른 검색이 가능하다.

옵티마이저(Optimizer)

• 옵티마이저는 SQL을 빠르고 효율적으로 수행할 최적의 처리 경로를 생성해주는 DBMS 내부의 핵심 엔진이다.
• 컴퓨터의 CPU역할을 DBMS에서 옵티마이저가 한다고 생각하면 편하다. 개발자가 SQL을 작성하고 실행하면 즉시 실행되는 것이 아니고, 옵티마이저에서 여러 실행 계획을 세우고, 최고의 효율을 갖는 실행계획을 판별한 후 그 실행계획에 따라 쿼리를 수행하는 것이다.

DB 튜닝

• DB 튜닝이란 DB의 구조나, DB 자체, 운영체제 등을 조정해 DB 시스템의 성능을 개선하는 작업이다.
• 튜닝은 DB 설계 튜닝 -> DMBS 튜닝 -> SQL 튜닝 단계로 진행한다.

1. DB 설계 튜닝 (모델링 관점)
   • DB 설계 단계에서 성능을 고려하여 설계
   • 데이터 모델링, 인덱스 설계
   • 데이터 파일, 테이블 스페이스 설계
   • 데이터베이스 용량 산정
   • 튜닝 사례 - 반정규화, 분산파일배치
2. DBMS 튜닝 (환경 관점)
   • 성능을 고려하여 메모리나 블록 크기 지정
   • CPU, 메모리 I/O에 관한 관점
   • 튜닝 사례 - Buffer 크기, Cache 크기
3. SQL 튜닝(App 관점)
   • SQL 작성 시 성능 고려
   • Join, Indexing, SQL Excution Plan
   • 튜닝 사례 - Hash / Join

---

[참고 사이트]

https://dev-coco.tistory.com/158

 

잘못된 내용은 알려주시면 감사하겠습니다.