개요
백엔드 개발자의 관점에서 데이터베이스와 관련된 모든 내용을 집필하고 있습니다.
코드가 필요한 부분에서는 Spring과 MySQL을 기준으로 작성하였습니다.
목차
Database
데이터베이스란?
데이터베이스는 여러 사용자나 시스템 (회사, 기관 등) 이 필요로 하는 데이터를 한 곳에 모아 관리하는 데이터의 집을 의미합니다. DBMS는 해당 집을 관리해주는 도구라고 생각할 수 있습니다. 여러 사용자가 읽고 쓸 수 있게 해주는 역할을 합니다.
데이터베이스의 특징
•
실시간 접근성(Real-Time Accessibility) : 비정형적인 질의(조회)에 대하여 실시간 처리에 의한 응답이 가능해야 합니다.
•
지속적인 변화(Continuous Evloution) : 데이터베이스의 상태는 동적입니다. 즉 새로운 데이터의 삽입(Insert), 삭제(Delete), 갱신(Update)으로 항상 최신의 데이터를 유지해야 합니다.
•
동시 공용(Concurrent Sharing) : 데이터베이스는 서로 다른 목적을 가진 여러 응용자들을 위한 것이므로 다수의 사용자가 동시에 같은 내용의 데이터를 이용할 수 있어야 합니다.
•
내용에 의한 참조(Content Reference) : 데이터베이스에 있는 데이터를 참조할 때 데이터 레코드의 주소나 위치에 의해서가 아니라 사용자가 요구하는 데이터 내용으로 찾습니다.
데이터베이스의 스키마란?
데이터베이스에서 스키마는 설계도를 의미합니다. 어떤 데이터가 들어가고, 해당 데이터가 어떤 형식인지, 어떻게 연결되어 있는지 등이 정의되어 있습니다.
스키마는 외부, 개념, 내부로 나뉩니다.
❗️ 외우자. 외개내. 왜 걔네야?
외부 스키마
외부 스키마는 사용자가 데이터베이스를 어떻게 보는지 정의합니다.
CREATE VIEW CustomerView AS
SELECT product_name, price, description
FROM Products;
Java
복사
이처럼, 사용자의 관점에서 데이터베이스의 일부를 보여줍니다. 예를 들어, 고객은 자신이 구매한 상품 정보만 필요하고, 관리자는 모든 고객의 구매 기록을 확인할 수 있어야 하기 때문에 같은 데이터베이스라도 다양한 관점이 필요합니다.
조회 할 때 Select 쿼리로 필요한 정보만 선택할 텐데 View가 필요할까View는 데이터베이스 내 필요한 데이터를 미리 준비하고 가공된 상태로 제공할 수 있습니다.
예를 들어, 동일한 데이터를 반복해서 조회하거나 조인, 필터링 등의 과정이 포함된다면 View 를 사용하는 것이 성능적인 이점을 가져갈 수 있습니다.
또한, 대규모 데이터를 처리할 때, 모든 데이터를 가져와 필터링 하는 것이 아닌 데이터베이스 레벨에서 뷰를 통해 미리 필터링 된 데이터를 제공한다면 성능을 크게 향상 시킬 수 있습니다.
스프링 부트에서는 어떻게 사용할까1.
데이터베이스에서 뷰를 생성합니다.
CREATE VIEW CustomerOrderView AS
SELECT Customers.name, Orders.order_id, Products.product_name
FROM Orders
JOIN Customers ON Orders.customer_id = Customers.customer_id
JOIN OrderDetails ON Orders.order_id = OrderDetails.order_id
JOIN Products ON OrderDetails.product_id = Products.product_id;
Java
복사
2.
JPA 엔티티로 뷰를 매핑합니다.
@Entity
@Table(name = "CustomerOrderView") // 데이터베이스에 생성된 뷰 이름과 매핑
public class CustomerOrder {
@Id
@Column(name = "order_id")
private Long orderId;
@Column(name = "customer_name")
private String customerName;
@Column(name = "product_name")
private String productName;
// Getter and Setter
}
Java
복사
3.
일반 엔티티와 비슷하게 레포지토리로 조회합니다.
뷰는 복잡한 조인과 필터링 로직을 캡슐화하여 간단한 SELECT * FROM ViewName 방식으로 처리할 수 있기 때문에 성능 상의 이점을 가져올 수 있습니다!
개념 스키마
개념 스키마는 추상적인 데이터 모델입니다. 개념 스키마 자체는 데이터베이스가 어떻게 설계되었고, 그 안에 어떤 데이터가 존재하고, 데이터가 어떻게 서로 연결되는지를 설계 수준에서 설명합니다. ERD와 같은 다이어그램도 개념 스키마의 일종입니다.
개념 스키마는 다음을 표현합니다.
•
테이블
•
테이블 간의 관계 (연결, 의존성)
•
제약 조건
•
데이터 타입
이를 사전적 정의로는 사용자와 DB 관리자 관점에서의 스키마라고 부르기도 합니다. 하나의 데이터베이스에서 여러 개의 ERD가 존재할 수 없듯, 하나의 DB에는 하나의 개념 스키마가 존재합니다.
내부 스키마
내부 스키마도 개념 스키마와 마찬가지로 추상적인 개념입니다. 개념 스키마가 데이터베이스의 전체적인 구조 느낌이었다면, 내부 스키마는 데이터의 물리적 저장 방식과 성능 최적화를 다루는 설계입니다.
예를 들어, MySQL에서는 데이터가 InnoDB 엔진을 사용해 .ibd 파일 형태로 저장됩니다.
또한, 내부 스키마에서는 데이터베이스가 인덱스를 어떻게 관리할지 결정합니다.
MySQL에서 InnoDB 엔진이 B-Tree 인덱스를 사용하는 경우, 인덱스 구조는 데이터 파일과 별도로 관리됩니다
이외에도 스토리지 블록, 페이지 관리, 쿼리 실행 계획, 내부 레코드의 물리적 순서 등이 있으며, 이는 사용자가 직접 관리하는 부분이 아닌 DBMS가 자동으로 관리하는 부분입니다.
데이터 독립성
데이터 독립성은 논리적 독립성과 물리적 독립성으로 나뉩니다.
논리적 독립성
논리적 독립성은 개념 스키마를 수정하더라도, 외부 스키마에는 영향을 미치지 않도록 하는 것을 의미합니다. 즉, 테이블 간의 관계나 데이터의 논리적 구조를 변경해도, 사용자나 애플리케이션이 그 변경 사항을 알 필요 없이 기존 방식대로 데이터를 사용할 수 있는 것을 말합니다.
예를 들어, 온라인 쇼핑몰 데이터베이스에서 Orders 테이블과 Products 테이블이 있다고 가정해보겠습니다.
논리적 구조 변경: 만약 데이터베이스 관리자가 Products 테이블에 category 컬럼을 추가하고, Orders 테이블에서 product_id 대신 order_details 테이블을 새로 만들어 이를 참조하도록 변경했다고 가정해봅시다.
이런 변경에도 불구하고, 사용자는 여전히 기존에 있던 Orders와 Products 테이블에 접근하는 외부 스키마인 뷰를 사용하여 상품 주문 정보를 조회할 수 있습니다. 즉, 내부적으로는 테이블 구조가 바뀌었지만, 사용자는 여전히 자신이 사용하던 방식으로 데이터를 조회할 수 있습니다.
CREATE VIEW CustomerOrderView AS
SELECT o.order_id, p.product_name, p.price
FROM Orders o
JOIN Products p ON o.product_id = p.product_id;
SQL
복사
사용자는 기존과 동일하게 CustomerOrderView를 사용해 주문 정보를 조회할 수 있으며, 논리적인 데이터 구조가 변경된 것을 알 필요가 없습니다. 즉, 데이터의 논리적 구조가 변경되어도 사용자와 애플리케이션에는 영향이 없다는 것을 의미합니다.
물리적 독립성
물리적 독립성은 데이터가 실제로 어떻게 저장되는지를 변경하더라도, 개념 스키마에는 영향을 미치지 않도록 하는 것을 의미합니다. 즉, 데이터가 디스크에 어떻게 배치되고 최적화되든지 간에, 논리적인 데이터 구조와 사용자에게 제공되는 정보는 변하지 않는다는 것입니다.
데이터 언어
데이터 언어에는 DDL, DML, DCL이 존재합니다. 이는 각각의 데이터베이스에서 다른 기능을 수행하는 SQL 명령어입니다.
DDL (Data Definition Language)
DDL 명령어는 데이터베이스의 구조를 정의하거나 변경할 때 사용됩니다. 즉, 테이블, 인덱스, 스키마 등의 데이터베이스 객체를 생성, 수정, 삭제하는 작업을 수행합니다. 이 명령어는 테이블의 구조나 스키마를 정의하는 데 사용됩니다.
CREATE: 새로운 데이터베이스 객체(테이블, 인덱스, 뷰 등)를 생성합니다.
CREATE TABLE Customers (
customer_id INT PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(100)
);
SQL
복사
ALTER: 기존 데이터베이스 객체의 구조를 수정합니다(컬럼 추가/삭제 등).
ALTER TABLE Customers ADD COLUMN phone_number VARCHAR(15);
SQL
복사
DROP: 데이터베이스 객체를 삭제합니다(테이블, 인덱스, 스키마 등).
DROP TABLE Customers;
SQL
복사
TRUNCATE: 테이블의 모든 데이터를 삭제하지만 테이블 구조는 유지합니다.
TRUNCATE TABLE Customers;
SQL
복사
RENAME: 데이터베이스 객체의 이름을 변경합니다.
RENAME TABLE Customers TO Clients;
SQL
복사
DML (Data Manipulation Language)
DML 명령어는 데이터베이스에서 데이터를 조작하는 데 사용됩니다. 즉, 데이터를 삽입, 수정, 삭제, 조회하는 작업을 수행합니다. DML은 주로 테이블 내 데이터에 대한 작업을 다룹니다.
SELECT: 테이블에서 데이터를 조회합니다.
SELECT * FROM Customers WHERE customer_id = 1;
SQL
복사
INSERT: 테이블에 새로운 데이터를 삽입합니다.
INSERT INTO Customers (customer_id, name, email)
VALUES (1, 'John Doe', 'john@example.com');
SQL
복사
UPDATE: 테이블의 기존 데이터를 수정합니다.
UPDATE Customers
SET email = 'john.doe@example.com'
WHERE customer_id = 1;
SQL
복사
DELETE: 테이블에서 데이터를 삭제합니다.
DELETE FROM Customers WHERE customer_id = 1;
SQL
복사
DCL (Data Control Language)
DCL 명령어는 데이터베이스에 대한 권한을 제어하고, 데이터에 대한 접근을 관리하는 데 사용됩니다. 주로 사용자에게 권한을 부여하거나 해제하는 작업을 수행합니다.
GRANT: 사용자에게 특정 권한을 부여합니다.
GRANT SELECT, INSERT ON Customers TO 'user1';
SQL
복사
REVOKE: 사용자에게 부여된 특정 권한을 해제합니다.
REVOKE INSERT ON Customers FROM 'user1';
SQL
복사
Select Query의 실행 순서
FROM, ON, JOIN > WHERE, GROUP BY, HAVING > SELECT > DISTINCT > ORDER BY > LIMIT
데이터베이스의 설계 순서 (요개논물구)
데이터베이스 설계의 각 단계를 필요의 관점에서 보면 쉽게 이해할 수 있습니다.
먼저 요구사항을 분석해야 합니다.
어떤 데이터를 저장하고, 사용자들이 어떤 데이터를 사용할지 정리합니다.
예를 들어, 온라인 쇼핑몰을 만든다면 상품 명, 가격, 재고 등이 필요합니다.
다음으로 개념적 데이터 모델을 만듭니다. 개념 스키마는 ERD로 표현할 수 있다고 앞 전에 언급한 적이 있습니다. 개념적 데이터 모델을 만드는 과정에서 엔티티 간의 관계를 정의합니다.
온라인 쇼핑몰 예제를 계속해서 이어나간다고 가정하면 고객, 상품, 주문과 같은 엔티티를 정의하고 이들의 관계를 설정할 필요가 있습니다. 고객와 주문은 1:N 관계로 볼 수 있습니다.
개념적 모델 다음에는 논리적 모델입니다. 논리적 모델은 구체적인 테이블 구조와 데이터 타입을 정의하는 과정입니다.
고객 테이블에는 customer_id, customer_name, customer_email 등이 포함될 수 있습니다.
물리적 모델은 실제 저장소에 맞게 물리적으로 구현합니다. 사용자가 직접 건드릴 일은 없습니다.
테이블을 어떤 방식으로 저장할 지, 인덱스를 어떻게 설정할 지 등을 고민합니다.
마지막으로 데이터베이스를 실제로 구축하는 단계입니다. SQL을 사용해 테이블, 인덱스, 제약 조건 등을 생성하고, 시스템을 배포하여 데이터베이스를 운영할 준비를 합니다.
데이터 모델의 구성 요소
엔티티(Entities):
•
데이터베이스에서 저장해야 할 주요 객체를 의미합니다. 예를 들어, 고객, 주문, 상품과 같은 것이 엔티티입니다.
•
테이블은 이 엔티티를 물리적으로 구현한 형태라고 생각할 수 있습니다.
속성(Attributes):
•
각 엔티티가 가지는 세부 데이터 항목입니다. 예를 들어, 고객 엔티티의 속성은 고객ID, 이름, 이메일 등이 될 수 있습니다. 속성은 테이블의 컬럼에 해당합니다.
•
반대로 튜플 (레코드) 는 행을 의미합니다.
관계(Relationships):
•
서로 다른 엔티티 간의 연결과 상호작용을 나타냅니다. 예를 들어, 고객이 여러 주문을 할 수 있는 관계는 1(일대다) 관계로 표현됩니다. 이 관계는 데이터베이스에서 외래 키로 구현됩니다.
제약조건(Constraints):
•
데이터의 무결성을 보장하기 위해 적용되는 규칙입니다. 예를 들어, 고객 ID는 중복될 수 없다는 제약 조건이나, 주문은 반드시 고객과 연결되어 있어야 한다는 외래 키 제약 조건이 있습니다.
추가적으로 알아야 될 용어는 다음과 같습니다.
•
릴레이션 (Table), 속성, 차수, 도메인(성별 = 남,여) , 릴레이션 인스턴스 (릴레이션에 실제로 저장된 데이터 집합)
키 (Key)
무결성
이상 현상
이상 현상은 데이터베이스에서 정규화가 제대로 발생하지 않은 경우 발생하는 데이터 무결성을 의미합니다. 테이블 설계가 잘못되어 데이터가 중복되거나 비효율적으로 저장될 때 이상 현상이 발생할 수 있습니다. 이상 현상은 크게 세 가지로 나뉠 수 있습니다.
삽입 이상
❗️ 킥 : 학생과 과목을 붙인다면
삽입 이상은 새로운 데이터를 삽입할 때 문제가 생기는 상황을 말합니다. 데이터베이스 구조가 잘못 설계되었을 때, 불필요한 데이터를 함께 삽입해야 하거나, 일부 데이터를 삽입할 수 없는 경우가 발생합니다.
학생과 수업 정보가 하나의 테이블에 저장되어 있다고 가정해봅시다.
Students
+-----------+--------------+---------------------+
| 학생ID | 학생이름 | 수업이름 |
+-----------+--------------+---------------------+
| 1 | John | 데이터베이스 수업 |
| 2 | Jane | 웹 개발 수업 |
+-----------+--------------+---------------------+
Plain Text
복사
새로운 학생을 추가하고 싶은데, 아직 그 학생이 수업을 듣지 않는 상황이라고 가정해봅시다. 이때 수업 정보가 필요 없는데도 불구하고, 수업 정보를 빈 값으로 넣거나, 수업 정보 없이 삽입이 불가능할 수 있습니다.
(학생ID: 3, 학생이름: Mark, 수업이름: NULL) → 불필요한 NULL 값 삽입
Plain Text
복사
이는 학생과 수업 정보를 하나의 테이블에 저장했기 때문에 생긴 문제입니다.
삭제 이상
삭제 이상은 데이터를 삭제할 때 다른 중요한 데이터까지 함께 삭제되는 문제를 말합니다. 테이블에 관련 없는 데이터가 함께 저장되어 있으면, 하나의 데이터를 삭제할 때 다른 데이터도 같이 사라질 수 있습니다.
같은 테이블에서 학생이 수업을 듣지 않는 경우를 삭제한다고 가정해봅시다.
Students
+-----------+--------------+---------------------+
| 학생ID | 학생이름 | 수업이름 |
+-----------+--------------+---------------------+
| 1 | John | 데이터베이스 수업 |
| 2 | Jane | 웹 개발 수업 |
+-----------+--------------+---------------------+
Plain Text
복사
John이 수업을 그만두었다고 가정해봅시다. 이제 John의 데이터를 삭제하면, 학생 정보도 함께 삭제되어서 John이 아예 데이터베이스에서 사라지게 됩니다.
DELETE FROM Students WHERE student_id = 1;
Plain Text
복사
John이 어떤 수업을 듣는지에 대한 정보만 삭제하고 싶은데, 그와 함께 학생 정보도 없어지게 됩니다.
갱신 이상
갱신 이상은 데이터의 중복으로 인해 한 번에 모든 데이터를 수정하기 어려운 상황을 말합니다. 중복된 데이터가 여러 곳에 저장되어 있으면, 한 부분을 수정할 때 모든 중복된 데이터를 일일이 수정해야 합니다.
여러 학생이 같은 수업을 듣고 있고, 수업 이름이 변경되었다고 가정해봅시다.
Students
+-----------+--------------+---------------------+
| 학생ID | 학생이름 | 수업이름 |
+-----------+--------------+---------------------+
| 1 | John | 데이터베이스 수업 |
| 2 | Jane | 데이터베이스 수업 |
+-----------+--------------+---------------------+
Plain Text
복사
데이터베이스 수업의 이름을 "DB 설계 수업"으로 변경해야 한다고 할 때, 모든 관련된 행을 수정해야 합니다.
정규화
이상 현상은 정규화 과정을 통해 해결할 수 있습니다. 정규화는 제 1 정규형 부터 제 5 정규형 까지 존재합니다.
제 1 정규형
1NF는 모든 속성이 원자 값을 가져야 한다는 조건을 만족하는 정규형입니다. 즉, 각 속성은 하나의 값만을 가져야 하며, 중첩된 구조(배열, 리스트 등)가 없어야 합니다.
예를 들어, 학생 테이블에 학번 이름 수강과목이 있다고 가정해보겠습니다.
만약 수강 과목에 데이터베이스 네트워크 운영체제 등 여러 값이 있다면 이를 각각의 행으로 나누어 저장해야 합니다.
제 2 정규형
2NF는 1NF를 만족하면서, 기본 키에 대해 부분 함수 종속이 없는 상태를 말합니다. 부분 함수 종속이란 기본 키의 일부만으로 특정 속성을 결정할 수 있는 경우를 뜻합니다. 이는 복합 기본 키에서 문제가 발생합니다.
조금 말이 어려울 수 있는데, 간단히 말하면 테이블의 복합 기본 키 중 일부 기본 키에만 종속되는 속성이 없는 것을 의미합니다.
다음 테이블에서 복합 키는 (회원 ID, 상품 코드) 입니다. 하지만 테이블을 보시면 회원이름과 회원주소는 회원ID에 종속되어 있으며, 상품명과, 가격은 상품 코드에 종속되어 있습니다.
이렇게 되면 다음과 같은 문제가 발생할 수 있습니다.
삽입 이상
•
새로운 상품이 등록될 때 회원 정보와 연관된 모든 컬럼에 추가해야 합니다.
•
또한, 상품만 추가하는 것이 아닌 회원 정보까지 포함해서 추가해야 합니다.
수정 이상
•
회원 이름인 홍길동을 변경한다면 모든 컬럼에 대해 변경을 진행해야 합니다.
삭제 이상
•
상품을 삭제하게 된다면, 해당 상품을 구매한 회원 정보 또한 사라집니다.
이를 해결하기 위해서는 각각 (회원 ID, 회원 이름, 회원 주소), (상품코드, 상품명, 가격), (회원 ID, 상품 코드) 로 나누어야 합니다.
3NF는 2NF를 만족하면서, 기본 키에 대해 이행적 함수 종속이 없는 상태를 말합니다. 이행적 종속이란, 기본 키가 아닌 속성이 다른 속성에 종속되는 경우를 뜻합니다.
다음 예제에서는 학번이 기본 키 입니다. 여기서 학번 → 소속학과, 소속학과 → 학과장의 이행적 종속이 존재합니다.
따라서 이는 학번 이름 소속학과 로 구성된 테이블과 소속학과 학과장 으로 이루어진 테이블로 분리할 수 있습니다.
반정규화
반정규화는 데이터베이스 설계 시 성능 최적화를 위해 데이터 중복을 허용하거나 테이블을 합쳐서 정규화된 구조를 일부 되돌리는 작업입니다. 정규화를 통해 데이터 무결성과 중복을 방지할 수 있지만, 지나치게 정규화된 구조는 성능에 부정적인 영향을 줄 수 있기 때문에, 효율적인 조회나 성능 향상을 위해 반정규화가 필요할 때가 있습니다.
대게 다음과 같은 상황에서 반 정규화가 필요합니다.
•
빈번한 JOIN 연산: 여러 테이블을 조인해야 할 때 성능이 떨어지거나 쿼리 속도가 느려질 수 있습니다.
•
복잡한 쿼리: 정규화된 구조에서 복잡한 쿼리를 자주 실행해야 하는 경우.
•
읽기 속도가 중요한 경우: 특히 조회가 많은 시스템에서는 데이터를 미리 중복하여 저장함으로써 쿼리 성능을 높일 수 있습니다.
•
성능이 우선인 경우: 데이터 무결성보다 성능이 우선시되어야 하는 상황에서 반정규화를 고려합니다.
예를 들어 사용자 테이블 에는 user_id name address가 있다고 가정하고,
주문 테이블에는 order_id user_id order_date total_amount 가 있다고 가정해보겠습니다.
사용자의 주문 내역을 조회 할 때면 항상 name과 address가 필요했기 때문에 테이블을 조인하였습니다..
SELECT u.name, u.address, o.order_date, o.total_amount
FROM User u
JOIN Order o ON u.user_id = o.user_id;
SQL
복사
만약 빈번하게 같이 조회가 된다면 조인 비용을 줄이기 위해 Order 테이블에 name과 address 필드를 추가하여 데이터를 중복 저장할 수 있습니다.
그러면 조인이 필요없이 단순히 조회 만으로 원하는 정보를 얻을 수 있습니다.
SELECT name, address, order_date, total_amount FROM Order;
SQL
복사
조인
조인은 두 개 이상의 테이블을 결합하여 데이터를 가져오는 SQL 연산입니다. 조인을 통해 여러 테이블에서 필요한 데이터를 쉽게 검색할 수 있습니다.
조인의 종류
동등 조인 (Equi Join): 두 테이블에서 공통 열의 값이 같은 행들만 결합하는 조인입니다. = 연산자를 사용하여 조인을 수행합니다.
SELECT A.*, B.*
FROM A
JOIN B ON A.id = B.id;
SQL
복사
세타 조인 (Theta Join): 두 테이블을 결합할 때 =뿐만 아니라 <, >, <=, >= 등 다양한 비교 연산자를 사용하는 조인입니다.
SELECT A.*, B.*
FROM A
JOIN B ON A.value > B.value;
SQL
복사
자연 조인 (Natural Join): 두 테이블에서 동일한 이름의 열을 기준으로 결합하는 조인입니다. 동일한 열이 여러 개 있을 경우 모든 동일한 열을 기준으로 조인합니다.
SELECT *
FROM A
NATURAL JOIN B;
SQL
복사
Left Outer Join
Left Outer Join은 왼쪽 테이블의 모든 행을 포함하고, 오른쪽 테이블에 일치하는 행이 없으면 NULL 값을 채워서 반환합니다. 이를 통해 왼쪽 테이블의 모든 데이터를 유지하면서 오른쪽 테이블의 관련된 데이터만 가져옵니다.
SELECT A.*, B.*
FROM A
LEFT JOIN B ON A.id = B.id;
SQL
복사
트랙섹션
트랜섹션은 데이터베이스의 상태를 변화시키는 하나의 논리적 기능을 수행하기 위한 작업 단위 입니다. 트랙섹션은 데이터의 정합성을 보장하기 위한 기능입니다.
데이터 정합성의 예제는 다음과 같습니다.
A 고객이 B 고객에게 100만 원을 송금하는 경우를 생각해봅시다. 이 과정에서 A의 계좌에서 100만 원이 빠져나가고, B의 계좌에 100만 원이 입금됩니다. 이 작업은 두 가지 단계로 이루어집니다:
1.
A의 계좌에서 100만 원을 차감 (A의 계좌에서 100만 원 빠짐)
2.
B의 계좌에 100만 원을 입금 (B의 계좌에 100만 원 추가)
만약 이 두 단계 중 하나라도 실패하면 어떻게 될까요? 예를 들어, A의 계좌에서는 100만 원이 차감되었는데, B의 계좌에 입금되는 과정에서 시스템 오류가 발생했다면? 이 경우 A는 100만 원이 빠져나갔지만, B는 돈을 받지 못하게 되므로 데이터는 일관성이 깨집니다.
트랙섹션의 속성
트랙섹션을 정의하는 4가지 속성은 ACID라고 부릅니다; 원자성, 일관성, 독립성, 영속성.
각각을 하나의 문장으로 요약하면 다음과 같습니다.
원자성은 모든 작업이 성공하거나 취소됩니다.
일관성은 시스템의 고정 요소는 트랙섹션 수행 전/후로 동일합니다.
고정요소는 데이터베이스의 규칙과 제약 조건을 의미합니다.
격리성은 여러 트랙섹션이 동시에 실행되어도 서로 영향을 주지 않습니다.
영속성은 트랙섹션이 성공하면 결과가 영구적으로 저장됩니다.
MySQL에서의 트랙섹션
MySQL에서 InnoDB 스토리지 엔진을 사용하면 트랜잭션 처리를 통해 데이터의 일관성과 무결성을 보장할 수 있습니다. 반면, MyISAM은 트랜잭션을 지원하지 않기 때문에 데이터 정합성 유지가 어렵습니다. 이를 간단한 예제로 정리해 보겠습니다.
1.
MyISAM과 InnoDB를 사용하는 테이블을 각각 생성합니다.
•
MyISAM: 트랜잭션을 지원하지 않는 스토리지 엔진.
•
InnoDB: 트랜잭션을 지원하는 스토리지 엔진.
2.
두 테이블에 3이라는 레코드를 저장합니다.
•
두 엔진 모두 3이라는 값이 정상적으로 저장됩니다.
3.
1, 2, 3이라는 레코드를 추가로 저장합니다.
•
Duplicate entry '3' for key 'PRIMARY' 오류가 발생합니다. 이는 기본 키 중복 오류로, 이미 저장된 3이라는 값이 다시 삽입되어 발생한 문제입니다.
4.
MyISAM과 InnoDB의 처리 결과:
•
MyISAM: 트랜잭션을 지원하지 않기 때문에 오류가 발생했음에도 불구하고 1과 2가 부분적으로 저장됩니다. 이를 부분 업데이트(Partial Update)라고 하며, 이는 데이터 정합성을 깨뜨릴 수 있습니다.
•
InnoDB: 트랜잭션을 지원하기 때문에 오류가 발생하자 전체 작업이 롤백되어 데이터가 원래 상태로 복구됩니다. 즉, 1, 2, 3이 모두 저장되지 않으며, 데이터의 일관성을 유지합니다.
❗️트랙섹션의 범위는 최소화하라
트랙섹션은 DBMS의 커넥션과 동일하게 필요한 최소의 코드에만 적용하는 것이 좋습니다.
좋지 않은 트랙섹션 묶음의 예
1. 처리 시작
* 데이터베이스 커넥션 생성 *
* 트랙섹션 시작 *
2. 사용자 인증
3. 사용자의 게시글 검증 및 오류 확인
4. 첨부로 업로드 된 파일 확인
5. 사용자의 게시글 내용을 DBMS에 저장
6. 사용자의 첨부 파일 정보를 DBMS에 저장
7. 저장된 내용을 DBMS에서 조회
8. 게시글 등록에 대한 알림 메일 발송
9. 알림 메일 발송 로그를 DBMS에 저장
* 트랙섹션 종료 *
* 데이터베이스 커넥션 반납 *
10. 처리 완료
Java
복사
위 흐름이 안좋은 이유를 가볍게 짚고 넘어가겠습니다. 우선 트랙섹션의 작업 시간은 단축시키는게 좋습니다. 왜냐하면 일반적으로 데이터베이스 커넥션 개수가 한정적이어서 각 단위 프로그램이 커넥션을 보유하는 시간이 길어질 수록 사용 가능한 커넥션 개수가 줄기 때문입니다.
또한, 외부 API 호출이나 네트워크를 통한 원격 서버와의 통신(예: 메일 서버, 결제 서버 등)은 DBMS의 트랜잭션 처리에서 제외하는 것이 좋습니다. 네트워크 통신은 프로그램이 완전히 제어할 수 없는 영역이므로, 트랜잭션이 장기화될 위험이 높아지기 때문입니다. 따라서, 이러한 작업은 트랜잭션 외부에서 별도로 처리하는 것이 효율적입니다.
수정한 트랙섹션 묶음
1. 처리 시작
2. 사용자 인증
3. 사용자의 게시글 검증 및 오류 확인
4. 첨부로 업로드 된 파일 확인
* 데이터베이스 커넥션 생성 *
* 트랙섹션 시작 *
5. 사용자의 게시글 내용을 DBMS에 저장
6. 사용자의 첨부 파일 정보를 DBMS에 저장
* 트랙센션 종료 *
7. 저장된 내용을 DBMS에서 조회
8. 게시글 등록에 대한 알림 메일 발송
* 트랙섹션 시작 *
9. 알림 메일 발송 로그를 DBMS에 저장
* 트랙섹션 종료 *
* 데이터베이스 커넥션 반납 *
10. 처리 완료
Java
복사
트랙섹션을 제어하는 명령어
트랜잭션과 관련된 주요 명령어들인 COMMIT, ROLLBACK, REDO, UNDO 이 있습니다.
COMMIT 명령어는 트랜잭션 내에서 수행된 모든 변경 사항을 영구적으로 저장하는 역할을 합니다. 트랜잭션이 성공적으로 완료되면 COMMIT을 호출하여 데이터베이스에 변경된 내용을 반영합니다.
START TRANSACTION;
UPDATE Accounts SET balance = balance - 1000 WHERE account_id = 1;
UPDATE Accounts SET balance = balance + 1000 WHERE account_id = 2;
COMMIT;
Java
복사
ROLLBACK 명령어는 트랜잭션 내에서 수행된 모든 변경 사항을 취소하고, 트랜잭션 시작 시점으로 데이터를 복구합니다. 트랜잭션 중 오류가 발생하거나 특정 조건을 만족하지 않으면 ROLLBACK을 호출하여 원래 상태로 되돌릴 수 있습니다.
START TRANSACTION; -- 트랜잭션 시작
UPDATE Accounts SET balance = balance - 1000 WHERE account_id = 1;
UPDATE Accounts SET balance = balance + 1000 WHERE account_id = 2;
-- 오류 발생 또는 조건 불만족 시
ROLLBACK; -- 변경 사항 취소
Java
복사
•
트랜잭션 내에서 오류가 발생하면 ROLLBACK이 실행되고, 모든 데이터 변경은 취소되며 트랜잭션이 시작된 시점으로 돌아갑니다.
REDO는 사용자가 직접 실행하는 명령어는 없으며, 이는 데이터베이스 복구 시 자동으로 처리됩니다.
트랜잭션 실행 중 변경 사항이 발생할 때마다 기록되며, COMMIT이 되면 Redo Log에 있는 데이터가 데이터베이스에 반영됩니다.
만약 시스템 장애나 크래시 후 트랜잭션이 성공적으로 완료된 경우 리두 로그를 사용하여 영구적으로 적용되지 못한 변경 사항을 다시 적용할 수 있습니다.
UNDO는 Undo Log를 사용하여 트랜잭션 중에 발생한 변경 사항을 롤백하는 데 사용됩니다. 트랜잭션이 실행될 때, 데이터의 변경 이전 상태가 Undo Log에 기록됩니다. 트랜잭션 중 오류가 발생하거나 ROLLBACK이 호출되면, Undo Log를 참조하여 변경된 데이터를 트랜잭션 전 상태로 되돌립니다.
데이터 회복 기법
즉시 갱신 기법은 트랜잭션이 실행되는 동안 데이터베이스의 실제 데이터를 즉시 갱신하는 방식입니다. 변경된 데이터는 트랜잭션이 커밋되기 전에 바로 적용되지만, 장애 발생 시 Undo 로그를 사용해 변경 사항을 되돌릴 수 있습니다.
MySQL의 INNOD는 즉시 갱신 기법을 사용합니다.
지연 갱신 기법은 트랜잭션이 완료되고 커밋이 된 이후에만 데이터베이스의 실제 데이터에 변경 사항을 반영하는 방식입니다. 즉, 트랜잭션이 완료되기 전에는 데이터베이스에 실제 갱신이 이루어지지 않습니다.
이 외에도 검사 시점 기법과 그림자 페이징 기법이 있습니다.
락
데이터베이스에서 락은 다수의 커넥션이 동시에 같은 데이터를 수정하는 상황을 방지하며 데이터가 안전하게 처리되도록 돕는 역할을 합니다.
락은 크게 공유 락과 베타 락으로 나뉩니다.
공유 락 (Shared Lock)
공유 락을 책 읽기에 비유해보겠습니다.
친구들과 함께 도서관에 방문했다고 가정해보겠습니다. 공유 락이 걸리면 여러 명의 친구들은 다함께 동시에 책을 읽을 수 있습니다.
하지만 공유 락이 걸린 상태에서는 그 누구도 책에 낙서를 할 수 없습니다. 즉, 읽기만 가능하고 책의 내용을 수정 하지는 못합니다.
“여러 사람이 같은 데이터를 안전하게 보되, 내용을 바꿀 수 없게 하는 것이 공유 락의 핵심입니다.”
하나의 커넥션이 공유 락을 획득한 경우, 다른 커넥션도 공유 락을 획득할 수 있습니다. 그러나, 그 상태에서는 책의 내용을 수정하는 배타 락을 획득할 수는 없습니다.
JPA에서의 공유 락
Spring에서는 JPA 혹은 JDBC를 통해 락 매커니즘을 구현할 수 있습니다.
다음은 JPA에서 공유 락을 사용하는 예시입니다.
JPA의 @Lock 어노테이션을 사용하여 PESSIMISTIC_READ 모드를 설정하면 공유 락과 유사한 읽기 전용 락을 걸 수 있습니다.
@Query(value = "SELECT balance FROM Account WHERE id = ?1")
@Lock(LockModeType.PESSIMISTIC_READ)
BigDecimal getBalance(Long id);
Java
복사
계좌 잔액을 조회할 때는 잔액을 수정하는 트랙섹션은 제한되어야 합니다. 공유 락을 사용하면 여러 사용자가 동시에 잔액을 조회할 수 있지만, 잔액을 수정하는 트랜잭션은 이 락을 해제할 때까지 대기합니다.
이 외에도 다음과 같은 예제가 있습니다.
// ?1 은 첫 번째 파라미터를 의미합니다.
@Query(value = "SELECT stock FROM Product WHERE id = ?1")
@Lock(LockModeType.PESSIMISTIC_READ)
Integer getProductStock(Long productId);
Java
복사
상품 재고를 동시에 읽기는 가능하지만, 재고를 변경하는 작업은 락 해제 후에 가능합니다.
락은 해당 쿼리가 실행되고 결과가 반환되는 순간 즉시 해제됩니다.
배타 락 (Exclusive Lock)
배타 락이 걸리면 한 사람이 책에 메모하거나 수정하는 동안, 다른 사람은 그 책을 수정할 수 없을 뿐만 아니라 책을 읽는 것조차 할 수 없습니다. 이는 데이터를 읽는 사람이 수정 중인 데이터를 일관되지 않은 상태로 읽어 잘못된 정보에 접근하지 않도록 하기 위함입니다.
“배타 락은 데이터를 수정하는 권한을 독점적으로 부여하고, 그동안 다른 사람이 그 데이터에 접근하거나 수정하지 못한다”
JPA에서의 배타 락
@Repository
public interface AccountRepository extends JpaRepository<Account, Long> {
// 배타 락(PESSIMISTIC_WRITE) 설정
@Lock(LockModeType.PESSIMISTIC_WRITE)
@Query("SELECT a FROM Account a WHERE a.id = :id")
Account findAccountLockingById(Long id);
}
Java
복사
@Lock(LockModType.PESSIMISTIC_WRITE)을 걸게되면 트랙섹션이 데이터에 배타 락을 걸어 다른 트랙섹션이 해당 데이터를 읽거나 쓰지 못하게 합니다.
락과 블로킹
위 예제를 통해, 특정 락이 걸린 상황에서 다른 트랜잭션이 대기하는 블로킹 현상이 발생할 수 있다는 것을 알 수 있습니다. 예를 들어, 공유 락이 걸린 환경에서 배타 락이 걸리려 하거나, 배타 락이 걸린 환경에서 다른 트랜잭션이 공유 락 또는 배타 락을 요청하는 상황들이 대표적인 예시입니다.
블로킹은 락을 걸고 있는 트랜잭션이 완료될 때까지 다른 트랜잭션이 해당 자원에 대해 읽거나 쓰기 작업을 진행할 수 없는 상황을 의미합니다.
블로킹의 문제
문제는 이러한 블로킹이 데드락을 유발할 수 있다는 점입니다. 예를 들어, 트랜잭션 A가 트랜잭션 B의 락을 기다리고, 동시에 트랜잭션 B도 트랜잭션 A의 락을 기다리는 상태라면, 두 트랜잭션 모두 무한 대기 상태에 빠지게 되어 시스템이 더 이상 진행되지 않는 데드락이 발생합니다.
또한, 블로킹이 빈번하게 발생하면 대기 시간이 길어져 시스템 성능에 부정적인 영향을 미칠 수 있습니다. 특히 다수의 트랜잭션이 동시에 자원에 접근하려는 환경에서는 성능 저하가 심각해질 수 있습니다.
❗️ MySQL로 직접 실습하고 싶다면 데드락 감지 옵션을 해제 하고 테스트 해보자
set global innodb_deadlock_detect=off;
Java
복사
블로킹 해결 방법
1. 적절한 트랜잭션 범위 설정
트랜잭션 범위를 짧게 유지하고, 필요한 작업에만 락을 적용하여 블로킹이 발생할 가능성을 줄입니다. 트랜잭션이 오래 걸리면 락이 길게 유지되어 다른 트랜잭션을 차단하는 시간이 늘어나므로, 최소한의 트랜잭션 범위를 설정하는 것이 중요합니다.
Spring에서 트랜잭션 범위는 @Transactional 어노테이션을 통해 설정할 수 있으며, 트랜잭션 범위를 최대한 짧게 설정하여 필요한 최소한의 코드에만 적용해야 합니다.
@Service
public class AccountService {
@Autowired
private AccountRepository accountRepository;
@Transactional // 트랜잭션 범위를 짧게 유지
public void transferFunds(Long fromAccountId, Long toAccountId, BigDecimal amount) {
Account fromAccount = accountRepository.findById(fromAccountId).orElseThrow();
Account toAccount = accountRepository.findById(toAccountId).orElseThrow();
fromAccount.withdraw(amount);
toAccount.deposit(amount);
// 트랜잭션은 메서드가 종료될 때 자동으로 커밋됩니다.
}
}
Java
복사
2. 낙관적 락 적용
낙관적 락을 사용하여 실제 락을 걸지 않고도 동시성 제어를 수행할 수 있습니다. 낙관적 락은 일반적으로 데이터가 충돌할 가능성이 낮은 환경에서 사용하며, 데이터 충돌이 발생하면 수정 작업을 롤백하고 다시 시도하게 합니다. 이는 비관적 락과 달리 트랜잭션 간 충돌이 발생하지 않는 한, 락을 걸지 않고도 여러 사용자가 동시에 데이터를 수정할 수 있도록 해줍니다.
Spring Data JPA에서는 @Version 어노테이션을 사용하여 낙관적 락을 구현할 수 있습니다. 엔티티에 @Version을 추가하면 JPA가 버전을 관리하며, 충돌이 발생하면 예외를 발생시킵니다.
@Entity
public class Product {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private String name;
private Integer stock;
@Version // 낙관적 락 적용
private Integer version;
// getters and setters
}
Java
복사
이 경우 낙관적 락은 version 필드를 통해 관리됩니다. 두 개의 트랜잭션이 같은 데이터를 수정하려고 할 때, 충돌이 발생하면 OptimisticLockException이 발생하며, 충돌이 해결되지 않으면 롤백됩니다.
3. 타임아웃 설정
트랜잭션이나 락에 타임아웃을 설정하여 특정 시간이 지나도 락이 해제되지 않으면 트랜잭션을 취소하도록 할 수 있습니다. 이를 통해 장시간 대기하는 블로킹 문제를 완화할 수 있습니다.
Spring JPA에서 트랜잭션의 타임아웃은 @Transactional 어노테이션의 timeout 속성을 사용하여 설정할 수 있습니다. 특정 시간이 지나면 트랜잭션이 강제로 롤백됩니다.
@Service
public class OrderService {
@Autowired
private OrderRepository orderRepository;
@Transactional(timeout = 5) // 타임아웃 5초 설정
public void processOrder(Long orderId) {
// 트랜잭션이 5초 내에 완료되지 않으면 롤백됩니다.
Order order = orderRepository.findById(orderId).orElseThrow();
order.process();
}
}
Java
복사
4.
자원의 동일한 순서로 락 설정
트랜잭션에서 자원에 항상 동일한 순서로 락을 걸도록 설계하면, 데드락 발생을 줄일 수 있습니다. 이를 통해 두 트랜잭션이 서로의 락을 기다리며 무한 대기 상태에 빠지는 상황을 방지할 수 있습니다.
데드락 방지를 위해 자원에 접근하는 순서를 명확히 설정해야 합니다. 예를 들어, 두 개 이상의 자원에 접근할 때 항상 동일한 순서로 락을 요청하도록 코드를 설계합니다.
@Service
public class AccountService {
@Autowired
private AccountRepository accountRepository;
@Transactional
public void transferFunds(Long fromAccountId, Long toAccountId, BigDecimal amount) {
// 항상 fromAccount -> toAccount 순서로 락을 걸어 데드락 방지
Account fromAccount = accountRepository.findByIdWithExclusiveLock(fromAccountId);
Account toAccount = accountRepository.findByIdWithExclusiveLock(toAccountId);
fromAccount.withdraw(amount);
toAccount.deposit(amount);
}
}
Java
복사
같은 account 뿐만 아니라, account 후 order 을 처리한다던지 서비스의 논리구조 순서를 정할 필요가 있습니다.
5.
그래프 기반으로 사이클 탐색
자원 할당 그래프를 사용하여 트랜잭션 간의 의존 관계를 파악하고, 사이클이 형성되면 데드락이 발생했다고 판단하는 방식입니다. 하지만 자원 할당 그래프를 지속적으로 유지하고, 각 트랜잭션이 실행될 때마다 사이클을 탐지하는 데는 비용이 많이 발생합니다. 특히, 대규모 시스템에서 트랜잭션이 자주 발생하면 그래프를 계속 업데이트하고 탐색하는 비용이 상당히 큽니다.
대규모 데이터베이스에서는 트랜잭션 수가 많아지고 자원에 대한 의존성이 복잡해지면서 사이클 탐색의 시간 복잡도가 증가합니다. 따라서, 그래프 기반 사이클 탐색은 이론적으로 가능하지만 쓰이지 않습니다.
6.
격리 수준 변경
격리 수준은 바로 다음 챕터를 통해 알아보도록 하겠습니다.
트랙섹션의 격리 수준
READ UNCOMMITED
READ COMMITTED
REPEATABLE READ
SERIALIZABLE
인덱스
트리거
옵티마이저
SQL Injection
MySQL
왜 MySQL인가
개인의 입장에서가 아닌 회사 차원에서 MySQL은 가격 경쟁력이 충분합니다. 추후 들어갈 회사에서도 MySQL을 쓰고 있을 확률이 높으니 자연스럽게 MySQL을 선택하였습니다.
자기가 가장 잘 활용할 수 있는 DBMS가 가장 좋은 DBMS 입니다.
MySQL 설정
MySQL은 단 하나의 설정 파일을 사용합니다. 경로가 고정돼있는 것은 아니며 순차 탐색을 통해 가장 처음 발견된 my.cnf 파일을 사용합니다.
MySQL 서버는 설정 파일을 읽어 메모리나 작동 방식을 초기화 하고, 이러한 값을 시스템 변수 라는 별도의 값으로 저장합니다.
변수는 글로벌 변수와 세션 변수, 정적 변수와 동적 변수 등이 있습니다.
→ 더 자세한 정보가 필요하다면 Real MySQL 8.0 1편의 41페이지를 참고하자
MySQL 사용자 및 권한
Dual Password
Dual Password는 왜 생겨났을까?
역할
읽기 전용 DB와 쓰기 전용 DB를 분리하는 명령어는 다음과 같습니다.
mysql> GRANT SELECT ON employees.* To role_emp_read;
mysql> GRANT INSERT, UPDATE, DELETE ON employess.* TO role_emp_write;
Java
복사
MySQL 엔진 아키텍쳐
InnoDB
MySQL에서 가장 널리 사용되는 스토리지 엔진 입니다.
Elastic Search
Reference
•
Real MySQL 8.0 1편
