MySQL 고급 객체 활용 및 데이터 백업 전략

뷰(View)

뷰는 하나 이상의 테이블에서 파생된 가상 테이블로, 복잡한 쿼리를 단순화하고 보안을 강화하는 데 사용됩니다. 물리적 디스크에는 뷰의 구조만 저장되며, 실제 데이터는 원본 테이블을 참조합니다. 성능 및 데이터 무결성 문제로 인해 뷰는 주로 읽기 전용으로 활용되며, 데이터 수정 용도로는 잘 사용되지 않습니다.

-- 뷰 생성: 사원과 부서 정보를 조인하여 가상 테이블로 만듭니다.
CREATE VIEW emp_department_info AS
SELECT e.emp_id, e.first_name, e.last_name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;

-- 뷰 삭제
DROP VIEW emp_department_info;

트랜잭션(Transaction)

트랜잭션은 데이터베이스의 상태를 변화시키기 위해 수행하는 작업의 단위를 의미합니다. 트랜잭션은 ACID 속성을 보장하여 데이터의 신뢰성을 유지합니다.

원자성(Atomicity): 트랜잭션 내의 모든 작업은 완전히 수행되거나 전혀 수행되지 않아야 합니다.
일관성(Consistency): 트랜잭션 완료 후에도 데이터베이스는 일관된 상태를 유지해야 합니다.
고립성(Isolation): 동시에 실행되는 트랜잭션들은 서로의 작업에 간섭할 수 없습니다.
지속성(Durability): 성공적으로 커밋된 트랜잭션의 결과는 시스템 장애가 발생하더라도 영구적으로 반영됩니다.

CREATE TABLE product_inventory (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(100),
    stock_quantity INT
);

INSERT INTO product_inventory (product_name, stock_quantity) VALUES
('Wireless Mouse', 150),
('Mechanical Keyboard', 80),
('Monitor', 45);

-- 트랜잭션 시작
START TRANSACTION;

-- 재고 업데이트 시뮬레이션
UPDATE product_inventory SET stock_quantity = stock_quantity - 2 WHERE product_name = 'Wireless Mouse';
UPDATE product_inventory SET stock_quantity = stock_quantity - 1 WHERE product_name = 'Monitor';

-- 오류 발생 시 모든 변경사항 취소
-- ROLLBACK;

-- 정상 처리 시 변경사항 영구 반영
COMMIT;

트리거(Trigger)

트리거는 특정 테이블에서 INSERT, UPDATE, DELETE 이벤트가 발생할 때 자동으로 실행되는 저장된 프로그램입니다. 주로 데이터 감사(audit)나 무결성 검사에 사용됩니다.

DELIMITER //
CREATE TRIGGER trg_after_salary_update
AFTER UPDATE ON employee_salaries
FOR EACH ROW
BEGIN
    IF NEW.salary_amount != OLD.salary_amount THEN
        INSERT INTO salary_change_logs (emp_id, previous_salary, new_salary, modified_at)
        VALUES (NEW.emp_id, OLD.salary_amount, NEW.salary_amount, CURRENT_TIMESTAMP);
    END IF;
END //
DELIMITER ;

저장 프로시저(Stored Procedure)

저장 프로시저는 미리 컴파일되어 데이터베이스 서버에 저장된 SQL 문장의 집합입니다. 네트워크 트래픽을 줄이고 재사용성을 높여줍니다.

DELIMITER //
CREATE PROCEDURE purge_expired_sessions()
BEGIN
    -- 만료된 세션 데이터를 아카이브 테이블로 이동
    INSERT INTO session_archive (session_id, user_id, expired_at)
    SELECT session_id, user_id, last_active_at 
    FROM active_sessions
    WHERE last_active_at < DATE_SUB(NOW(), INTERVAL 24 HOUR);

    -- 원본 테이블에서 만료된 세션 삭제
    DELETE FROM active_sessions
    WHERE last_active_at < DATE_SUB(NOW(), INTERVAL 24 HOUR);
END //
DELIMITER ;

-- MySQL 클라이언트에서 호출
CALL purge_expired_sessions();

흐름 제어(Flow Control)

저장 프로시저 내부에서는 조건문과 반복문을 사용하여 복잡한 비즈니스 로직을 구현할 수 있습니다.

IF 조건문

DELIMITER //
CREATE PROCEDURE evaluate_performance(IN score INT)
BEGIN
    IF score >= 90 THEN
        SELECT 'Excellent' AS grade;
    ELSEIF score >= 75 THEN
        SELECT 'Good' AS grade;
    ELSEIF score >= 60 THEN
        SELECT 'Pass' AS grade;
    ELSE
        SELECT 'Fail' AS grade;
    END IF;
END //
DELIMITER ;

WHILE 반복문

DELIMITER //
CREATE PROCEDURE calculate_factorial(IN target_num INT)
BEGIN
    DECLARE counter INT DEFAULT 1;
    DECLARE result BIGINT DEFAULT 1;
    
    WHILE counter <= target_num DO
        SET result = result * counter;
        SET counter = counter + 1;
    END WHILE;
    
    SELECT result AS factorial_value;
END //
DELIMITER ;

REPEAT 반복문

DELIMITER //
CREATE PROCEDURE countdown_timer()
BEGIN
    DECLARE current_count INT DEFAULT 5;
    
    REPEAT
        SELECT current_count AS remaining_seconds;
        SET current_count = current_count - 1;
    UNTIL current_count < 0
    END REPEAT;
END //
DELIMITER ;

LOOP 반복문

DELIMITER //
CREATE PROCEDURE process_odd_numbers()
BEGIN
    DECLARE num INT DEFAULT 0;
    
    number_loop: LOOP
        SET num = num + 1;
        
        -- 짝수는 건너뛰기
        IF num % 2 = 0 THEN
            ITERATE number_loop;
        END IF;
        
        -- 10 초과 시 루프 종료
        IF num > 10 THEN
            LEAVE number_loop;
        END IF;
        
        SELECT num AS odd_number;
    END LOOP number_loop;
END //
DELIMITER ;

함수(Function)

함수는 저장 프로시저와 유사하지만, 반드시 하나의 값을 반환해야 하며 주로 SELECT 문 내부에서 호출됩니다.

DELIMITER //
CREATE FUNCTION get_tax_included_price(base_price DECIMAL(10,2), tax_rate DECIMAL(4,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
    DECLARE final_price DECIMAL(10,2);
    SET final_price = base_price + (base_price * tax_rate / 100);
    RETURN final_price;
END //
DELIMITER ;

데이터베이스 백업(Database Backup)

데이터 손실을 방지하기 위해 정기적인 백업은 필수적입니다. MySQL에서는 mysqldump 유틸리티를 사용하여 논리적 백업을 수행할 수 있습니다.

# 특정 데이터베이스 전체 백업
mysqldump -u db_admin -p --single-transaction --routines --triggers target_db > target_db_backup_$(date +%F).sql

# 특정 테이블만 백업
mysqldump -u db_admin -p target_db table_name1 table_name2 > specific_tables_backup.sql

# 백업 파일을 사용하여 데이터 복원
mysql -u db_admin -p target_db < target_db_backup_2023-10-25.sql

태그: MySQL 뷰 트랜잭션 저장프로시저 트리거

8월 12일 13:31에 게시됨

괴물 클럽