[DB]MySQL과 Oracle에서 히스토리 테이블 관리하기✨

데이터베이스에서 과거 데이터를 체계적으로 관리하는 것은 데이터 변경 이력을 추적하고, 분석 및 복구 작업을 수행하는 데 매우 중요합니다. 이번 포스팅에서는 MySQL과 Oracle에서 히스토리 테이블을 설계하고 관리하는 방법을 자세히 알아보고, 사용자 계정 상태 변경 이력을 기록하는 실습 예제를 통해 실무적인 활용 방법을 제시합니다. 또한, Temporal Table (시간 테이블)의 개념과 장점을 소개하여 데이터 관리의 효율성을 높이는 방법을 함께 살펴보겠습니다.

1. 히스토리 데이터 관리의 필요성 

데이터 이력 관리의 중요성

• 변경 내역 추적: 누가, 언제, 어떤 데이터를 변경했는지 정확하게 파악하여 문제 발생 시 원인 분석 및 책임 추적 가능.
• 데이터 복구: 잘못된 데이터 변경이나 삭제 발생 시 이전 상태로 데이터를 복구하여 데이터 손실 방지.
• 규제 준수 (Compliance): 특정 산업 또는 법률에서 요구하는 데이터 보존 기간을 준수하기 위한 필수적인 요소. 감사 및 법적 분쟁 발생 시 증거 자료로 활용 가능.
• 비즈니스 분석: 과거 데이터를 분석하여 추세 파악, 패턴 발견, 미래 예측 등 비즈니스 의사 결정에 필요한 통찰력 제공.
• 문제 진단 및 디버깅: 애플리케이션 오류 발생 시 데이터 변경 이력을 분석하여 문제 원인을 신속하게 파악 가능.

Temporal Table의 장점 (MySQL 8.0 이상, Oracle 12c 이상 지원)

 

• 자동화된 이력 관리: 데이터 변경 시 시스템이 자동으로 과거 데이터를 저장하므로 개발자가 별도의 코드를 작성할 필요가 없음.
• 데이터 무결성 보장: 시스템 레벨에서 이력을 관리하므로 데이터의 일관성과 무결성을 보장.
• 시간 기반 쿼리 용이: 특정 시점의 데이터 조회가 간편해짐. 예를 들어 "어제 오후 5시의 사용자 상태"와 같은 쿼리를 쉽게 작성 가능.
• 성능 향상: 이력 관리를 위한 추가적인 로직 구현이 필요 없어 성능 향상에 도움을 줄 수 있음.

 

---

2. MySQL에서 히스토리 테이블 설계

MySQL에서는 트리거(Trigger)를 활용하여 변경 사항을 히스토리 테이블에 저장할 수 있습니다.

(1) 히스토리 테이블 생성

CREATE TABLE UserAccounts (
    UserID INT PRIMARY KEY,
    UserName VARCHAR(50),
    Status VARCHAR(20),
    LastModified TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE UserAccountHistory (
    HistoryID INT AUTO_INCREMENT PRIMARY KEY,
    UserID INT,
    UserName VARCHAR(50),
    Status VARCHAR(20),
    ModifiedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    OperationType ENUM('INSERT', 'UPDATE', 'DELETE')
);

(2) 트리거 생성

DELIMITER //

CREATE TRIGGER trg_UserAccount_AfterInsert
AFTER INSERT ON UserAccounts
FOR EACH ROW
BEGIN
    INSERT INTO UserAccountHistory (UserID, UserName, Status, ModifiedAt, OperationType, ChangedBy)
    VALUES (NEW.UserID, NEW.UserName, NEW.Status, NOW(), 'INSERT', USER()); -- USER() 함수로 변경 사용자 기록
END //

CREATE TRIGGER trg_UserAccount_AfterUpdate
AFTER UPDATE ON UserAccounts
FOR EACH ROW
BEGIN
    INSERT INTO UserAccountHistory (UserID, UserName, Status, ModifiedAt, OperationType, ChangedBy)
    VALUES (NEW.UserID, NEW.UserName, NEW.Status, NOW(), 'UPDATE', USER());
END //

CREATE TRIGGER trg_UserAccount_AfterDelete
AFTER DELETE ON UserAccounts
FOR EACH ROW
BEGIN
    INSERT INTO UserAccountHistory (UserID, UserName, Status, ModifiedAt, OperationType, ChangedBy)
    VALUES (OLD.UserID, OLD.UserName, OLD.Status, NOW(), 'DELETE', USER());
END //

DELIMITER ;

• ChangedBy 컬럼을 추가하여 누가 데이터를 변경했는지 기록하도록 개선했습니다.
• USER() 함수를 사용하여 현재 데이터베이스 사용자를 기록합니다.

---

3. Oracle에서 히스토리 테이블 설계

Oracle에서도 트리거와 PL/SQL을 사용하여 히스토리 테이블을 구현합니다.

(1) 히스토리 테이블 생성

CREATE TABLE UserAccounts (
    UserID NUMBER PRIMARY KEY,
    UserName VARCHAR2(50),
    Status VARCHAR2(20),
    LastModified TIMESTAMP DEFAULT SYSTIMESTAMP
);

CREATE TABLE UserAccountHistory (
    HistoryID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    UserID NUMBER,
    UserName VARCHAR2(50),
    Status VARCHAR2(20),
    ModifiedAt TIMESTAMP DEFAULT SYSTIMESTAMP,
    OperationType VARCHAR2(10),
    ChangedBy VARCHAR2(50) -- 변경 사용자 추가
);

(2) 트리거 생성 (개선된 버전)

CREATE OR REPLACE TRIGGER trg_UserAccount_AfterInsert
AFTER INSERT ON UserAccounts
FOR EACH ROW
BEGIN
    INSERT INTO UserAccountHistory (UserID, UserName, Status, ModifiedAt, OperationType, ChangedBy)
    VALUES (:NEW.UserID, :NEW.UserName, :NEW.Status, SYSTIMESTAMP, 'INSERT', USER);
END;
/

CREATE OR REPLACE TRIGGER trg_UserAccount_AfterUpdate
AFTER UPDATE ON UserAccounts
FOR EACH ROW
BEGIN
    INSERT INTO UserAccountHistory (UserID, UserName, Status, ModifiedAt, OperationType, ChangedBy)
    VALUES (:NEW.UserID, :NEW.UserName, :NEW.Status, SYSTIMESTAMP, 'UPDATE', USER);
END;
/

CREATE OR REPLACE TRIGGER trg_UserAccount_AfterDelete
AFTER DELETE ON UserAccounts
FOR EACH ROW
BEGIN
    INSERT INTO UserAccountHistory (UserID, UserName, Status, ModifiedAt, OperationType, ChangedBy)
    VALUES (:OLD.UserID, :OLD.UserName, :OLD.Status, SYSTIMESTAMP, 'DELETE', USER);
END;
/

• Oracle에서도 USER 함수를 사용하여 변경 사용자를 기록하도록 개선했습니다.

---

4. 실습: 사용자 계정 상태 변경 이력 기록

(1) 데이터 준비

MySQL

INSERT INTO UserAccounts (UserID, UserName, Status)
VALUES (1, 'Alice', 'Active'),
       (2, 'Bob', 'Inactive');

UPDATE UserAccounts SET Status = 'Suspended' WHERE UserID = 1;

DELETE FROM UserAccounts WHERE UserID = 2;

SELECT * FROM UserAccountHistory;

Oracle

INSERT INTO UserAccounts (UserID, UserName, Status)
VALUES (1, 'Alice', 'Active');

INSERT INTO UserAccounts (UserID, UserName, Status)
VALUES (2, 'Bob', 'Inactive');

UPDATE UserAccounts SET Status = 'Suspended' WHERE UserID = 1;

DELETE FROM UserAccounts WHERE UserID = 2;

SELECT * FROM UserAccountHistory;

---

5. 히스토리 데이터 관리의 장점 🌐

1. 정확한 변경 내역 추적: 누가, 언제, 어떤 데이터를 변경했는지 정확하게 기록하여 데이터의 투명성과 신뢰성을 확보합니다. 이는 감사, 문제 해결, 데이터 분석 등에 중요한 기반이 됩니다.
2. 간편한 데이터 복구: 잘못된 데이터 변경이나 삭제 발생 시 이전 시점의 데이터로 손쉽게 복구할 수 있습니다. 이는 데이터 손실로 인한 비즈니스 중단을 최소화합니다.
3. 규정 준수 (Compliance) 용이: 특정 법률이나 규정에서 요구하는 데이터 보존 기간을 충족할 수 있습니다. 금융, 의료 등 규제가 엄격한 산업에서 특히 중요합니다.
4. 심층적인 데이터 분석 지원: 과거 데이터를 분석하여 시간의 흐름에 따른 데이터 변화 추이를 파악하고, 비즈니스 의사 결정에 필요한 통찰력을 얻을 수 있습니다. 예를 들어, 특정 기간 동안의 사용자 활동 변화, 상품 판매 추이 등을 분석할 수 있습니다.
5. 애플리케이션 디버깅 및 문제 진단 용이: 애플리케이션 오류 발생 시 데이터 변경 이력을 추적하여 문제의 원인을 신속하게 파악하고 해결할 수 있습니다.
6. 데이터 감사 (Audit) 기능 강화: 누가, 언제, 어떤 데이터를 변경했는지에 대한 명확한 기록을 제공함으로써 내부 감사 및 외부 감사를 효과적으로 지원합니다.

---

6. Temporal Table (시간 테이블: MySQL 8.0 이상, Oracle 12c 이상)

Temporal Table은 데이터의 변경 이력을 시스템 레벨에서 자동으로 관리하는 기능을 제공합니다. 이를 통해 개발자는 별도의 트리거 코드를 작성할 필요 없이 효율적으로 데이터 이력을 관리할 수 있습니다.

MySQL Temporal Table 예제

CREATE TABLE UserAccounts (
    UserID INT PRIMARY KEY,
    UserName VARCHAR(50),
    Status VARCHAR(20),
    SysStartTime datetime(6) GENERATED ALWAYS AS ROW START,
    SysEndTime datetime(6) GENERATED ALWAYS AS ROW END,
    PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)
) SYSTEM VERSIONING;

-- 데이터 삽입
INSERT INTO UserAccounts (UserID, UserName, Status) VALUES (1, 'Alice', 'Active');

-- 데이터 업데이트
UPDATE UserAccounts SET Status = 'Suspended' WHERE UserID = 1;

-- 특정 시점의 데이터 조회
SELECT * FROM UserAccounts FOR SYSTEM_TIME AS OF '2024-10-26 10:00:00';

-- 모든 변경 이력 조회
SELECT * FROM UserAccounts HISTORY;

Oracle Temporal Validity 예제

CREATE TABLE UserAccounts (
    UserID NUMBER PRIMARY KEY,
    UserName VARCHAR2(50),
    Status VARCHAR2(20),
    ValidFrom TIMESTAMP(6) GENERATED ALWAYS AS ROW START VALID,
    ValidTo TIMESTAMP(6) GENERATED ALWAYS AS ROW END VALID
);

-- 데이터 삽입
INSERT INTO UserAccounts (UserID, UserName, Status) VALUES (1, 'Alice', 'Active');

-- 데이터 업데이트 (기존 데이터는 자동으로 이력 테이블로 이동)
UPDATE UserAccounts SET Status = 'Suspended' WHERE UserID = 1;

-- 특정 시점의 데이터 조회
SELECT * FROM UserAccounts
VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE
WHERE UserID = 1 AND ValidFrom <= TIMESTAMP '2024-10-26 10:00:00' AND ValidTo > TIMESTAMP '2024-10-26 10:00:00';

-- 모든 변경 이력 조회
SELECT * FROM UserAccounts VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE WHERE UserID = 1;

Temporal Table을 사용하면 과거 데이터 조회 쿼리가 더욱 간결해지고 직관적으로 작성할 수 있습니다.

---

6. 마무리 ✨

MySQL과 Oracle에서 히스토리 테이블을 관리하는 방법과 Temporal Table의 활용에 대해 살펴보았습니다. 히스토리 테이블은 데이터 변경 이력을 추적하고 관리하는 데 필수적인 도구이며, Temporal Table은 이러한 작업을 더욱 효율적으로 수행할 수 있도록 도와줍니다. 프로젝트의 요구 사항과 데이터베이스 버전을 고려하여 적절한 방법을 선택하고 활용하시기 바랍니다. 질문이 있다면 언제든지 댓글로 남겨주세요! 😊