[6/16 TIL] DB(트랜잭션, VIEW, PROCEDURE, FUNCTION, TRIGGER, 성능 튜닝)

🍃프로그래머스 백엔드 데브코스 4기 교육과정을 듣고 정리한 글입니다.🍃

트랜잭션

START TRANSACTION;

-- 작업 수행
INSERT INTO users (name, email) VALUES ('yumyeonghan', 'yumyeonghan.com');
UPDATE accounts SET balance = balance - 200 WHERE user_id = 2;

-- 문제 발생
SELECT 1 / 0;

COMMIT; -- 실행되지 않음

ROLLBACK; -- 트랜잭션 롤백

• 데이터베이스에서 일련의 작업을 논리적 단위로 묶어서 실행

ACID

• 원자성 (Atomicity): 트랜잭션은 "전부 성공하거나 전부 실패"하는 원자적인 작업 단위, 모든 작업이 성공적으로 완료되면 커밋되고, 하나라도 실패하면 롤백
• 일관성 (Consistency): 트랜잭션 전후에 데이터베이스는 일관된 상태(무결성)를 유지
• 격리성 (Isolation): 여러 트랜잭션이 동시에 실행될 때, 각 트랜잭션은 다른 트랜잭션에 영향을 미치지 않고 독립적으로 실행, 격리 수준은 트랜잭션 간의 동시성 제어를 조정하는 데 사용
• 지속성 (Durability): 시스템 장애 또는 기타 장애가 발생하더라도 트랜잭션에서 커밋된 작업은 영구적으로 유지

autocommit

-- 0: Autocommit 모드 비활성화
-- 1: Autocommit 모드 활성화
SET autocommit = 0;

-- 트랜잭션 시작
START TRANSACTION;

-- 작업 수행
INSERT INTO users (name, email) VALUES ('yumyeonghan', 'yumyeonghan.com');
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;

-- 커밋
COMMIT;

• autocommit이란 각 SQL 문이 개별적인 트랜잭션으로 간주되어 자동으로 커밋
• MySQL의 Autocommit 모드는 기본적으로 활성화

DELETE FROM VS TRUNCATE

• DELETE FROM 테이블
  • 테이블에서 모든 레코드를 느리게 삭제
  • where 사용해 특정 레코드만 삭제 가능
  • 트랜잭션 지원
• TRUNCATE 테이블
  • 테이블에서 모든 레코드를 빠르게 삭제
  • where 지원 안함
  • 트랜잭션 지원 안함

VIEW

-- employees 테이블을 기반으로 한 View 생성
CREATE VIEW employee_view AS
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE salary > 50000;

-- View 사용 예시: employee_view 조회
SELECT * FROM employee_view;

• View는 가상의 테이블로, 하나 이상의 기본 테이블에서 조회되는 쿼리 결과(SELECT)를 나타냄

특징

• 가상 테이블: View는 실제 데이터를 저장하지 않고, 기본 테이블의 데이터를 쿼리를 통해 동적으로 조회하여 결과를 생성
• 데이터의 가독성: View를 사용하면 복잡한 쿼리를 단순화하여 데이터의 가독성을 높임
• 보안 및 접근 제어: View는 기본 테이블의 열 중 일부만 노출하거나, 특정 조건을 적용하여 데이터 접근 제어 가능
• 쿼리의 재사용: View를 사용하면 자주 사용되는 복잡한 쿼리를 View로 정의하여 재사용 가능
• 조인 및 계산: View에서는 기본 테이블 간의 조인 연산이 가능하며, 계산된 열도 추가 가능

STORED PROCEDURE

-- Stored Procedure 생성
DELIMITER //

CREATE PROCEDURE GetEmployeeDetails(IN employee_id INT)
BEGIN
    SELECT * FROM employees WHERE employee_id = employee_id;
END //

DELIMITER ;

--Stored Procedure 호출 예:
CALL GetEmployeeDetails(1);

• MySQL에서 데이터베이스 서버 내에 저장된 프로그램 로직 정의

특징

• 프로그램 로직 저장: 데이터베이스 서버 내에 프로그램 로직을 저장해서 반복적인 작업, 복잡한 데이터 조작, 비즈니스 규칙 구현을 효율적으로 처리
• 파라미터와 반환값: 하나 이상의 파라미터를 받고, 프로시저 내부에서 처리하거나 결과를 반환하는 OUT 파라미터를 정의하여 프로시저 완료 후 값을 반환
• 재사용: 재사용 가능한 로직으로서 데이터베이스 서버에 저장되므로 여러 쿼리에서 호출
• 성능 향상: 데이터베이스 서버에 미리 컴파일되어 저장되기 때문에 반복적인 작업에 대한 오버헤드를 줄이고, 데이터베이스 서버의 성능을 향상

STORED FUNCTION

-- Stored Function 생성
DELIMITER //

CREATE FUNCTION GetEmployeeName(IN employee_id INT)
RETURNS VARCHAR(100)
BEGIN
    DECLARE employee_name VARCHAR(100);
    
    SELECT CONCAT(first_name, ' ', last_name) INTO employee_name
    FROM employees
    WHERE employee_id = employee_id;
    
    RETURN employee_name;
END //

DELIMITER ;

--Stored Function 호출 예:
SELECT GetEmployeeName(employee_id) FROM employees;

• 데이터베이스 내에 저장된 재사용 가능한 함수로, 하나 이상의 값을 반환하는 로직을 정의

특징

• 함수 형태: Stored Function은 함수와 유사한 형태를 가지며, 입력 파라미터를 받아들여 계산, 데이터 처리, 변환, 문자열 조작 등 다양한 작업을 수행하고 하나 이상의 값을 반환
• 반환값: Stored Function은 계산이나 로직 처리 후, 다양한 데이터 타입(문자열, 숫자 등)으로 지정해서 결과 값을 반환
• 데이터 접근: Stored Function 내에서 SQL 문을 사용하여 데이터베이스의 테이블에 접근 가능
• 재사용: Stored Function은 데이터베이스 내에서 재사용 가능한 함수로 정의되어 다른 쿼리나 프로시저에서 호출 가능

TRIGGER

-- orders 테이블에 주문이 삽일될 때, 주문의 상태를 "pending"으로 설정하는 TRIGGER 작성
CREATE TRIGGER set_pending_status
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
    SET NEW.status = 'pending';
END;

• 데이터베이스의 특정 이벤트가 발생할 때 자동으로 실행되는 저장 프로시저

특징

• 이벤트 지정: Trigger는 데이터베이스에서 발생하는 특정 이벤트(INSERT, UPDATE, DELETE)에 대한 반응으로 실행
• 테이블 단위: 각 Trigger는 하나의 테이블과 연결되어 있으며, 해당 테이블에서 이벤트가 발생할 때만 작동
• 실행 시기: Trigger는 이벤트 발생 전(before) 또는 이벤트 발생 후(after)에 실행
• 여러 작업: Trigger는 여러 개의 SQL 문을 포함할 수 있으며, 필요에 따라 데이터베이스의 다른 테이블에 대한 작업도 수행 가능
• 데이터 참조: Trigger 내에서 OLD와 NEW라는 가상 테이블을 사용하여 이전 데이터(old)와 새로운 데이터(new)에 접근 가능
  • new는 INSERT, UPDATE에서만 사용 가능
  • old는 DELETE, UPDATE에서만 사용 가능

성능 튜닝

Explain SQL

-- orders 테이블에서 status가 'pending'인 주문을 선택하는 쿼리에 대해 EXPLAIN을 실행
EXPLAIN SELECT * FROM orders WHERE status = 'pending';

• SQL 쿼리의 실행 계획을 분석하고 최적화하는 데 사용되는 명령어

특징

• 실행 계획 분석: EXPLAIN은 쿼리 실행 계획을 분석하여 데이터베이스 서버가 쿼리를 어떻게 처리하는지를(테이블 스캔, 조인 순서, 인덱스) 보임
• 비용 추정: EXPLAIN은 각 단계의 예상 비용을 제공하여 쿼리 성능을 분석
• 인덱스 사용 여부: EXPLAIN은 쿼리에서 인덱스를 사용하는지 여부를 보임

INDEX

-- INDEX 생성
CREATE INDEX idx_name ON employees (last_name, first_name);

• 데이터베이스 테이블의 검색 속도를 향상시키기 위해 사용

특징

• 검색 성능 향상: 인덱스는 데이터베이스에서 검색 작업의 성능(속도)을 향상
• 유니크한 값 유지: 인덱스를 사용하여 열에 유니크 제약 조건을 설정 가능
• 정렬된 데이터 구조: 인덱스는 열의 값을 정렬된 데이터 구조로 유지
• 업데이트 성능 저하: 인덱스는 검색 성능을 향상시키지만, 데이터를 추가, 수정 또는 삭제할 때는 일정한 오버헤드가 발생