[모각코] 2주차 결과 (7/15 13:00 ~ 16:00 Zoom 사용)

트랜젝션

• Atomic하게 실행되어야 하는 SQL들을 묶어서 하나의 작업처럼 처리하는 방법

• DDL이나 DML 중 레코드를 수정/추가/삭제한 것에만 의미가 있음 → SELECT에는 사용할 수 없음

• 특징

  • 원자성
    • 트랜젝션은 DB에 모두 반영되거나 전혀 반영되지 않아야 한다.
    • 만약 모든 명령이 완벽히 수행되지 않으면 트랜젝션이 모두 다 취소되어야 한다.
  • 일관성
    • 트랜젝션의 작업 처리 결과는 항상 일관성 있어야 한다.
    • 트랜젝션이 진행되는 동안 데이터베이스가 변경되면 처음 트랜젝션을 진행 하기 위해 참조한 데이터베이스에 트랜젝션이 적용됨
  • 독립성
    • 한 트랜젝션이 실행될 때 다른 트랜젝션의 연산에 끼어들 수 없다
  • 지속성
    • 트랜젝션이 성공적으로 완료되었을 경우 결과는 영구적으로 반영된다.

• 상태

  • Active : 트랜젝션이 실행중인 상태
  • Partial Committed : 트랜젝션의 모든 작업이 끝난 후 commit 되기 전 상태
  • failed : 트랜젝션에 오류가 발생하여 중단된 상태
  • aborted : Rollback 연산을 수행한 상태

• COMMIT

  • 트랜젝션의 작업들이 성공적으로 끝난 후 확정하여 트랜젝션을 종료하는 명령어
  • 트랜젝션 관리자에게 작업이 끝났음을 알리고 데이터베이스에 반영함

• ROLLBACK

  • 트랜젝션 작업 중 문제가 발생하여 지금까지의 변경 사항들을 취소하는 명령어
  • 트랜젝션을 시작하기 전 상태로 돌아감

• 문법

    START TRANSACTION; -- 트랜젝션 시작 명령어(BEGIN 과 동일)
    --sql
    --sql
    COMMIT -- commit 명령(END 와 동일)
    ROLLBACK

View

• 자주 사용하는 SQL 쿼리를 (SELECT) View로 데이터베이스단에 저장하여 사용하는 것
• SELECT 결과가 테이블로 저장되는 것이 아닌 View가 사용될때마다 SELECT가 실행됨
• 가상 테이블이라고도 부름

CREATE OR REPLACE VIEW test.session_details AS
SELECT s.id, s.user_id, s.created, s.channel_id, c.channel
FROM session s
JOIN channel c ON s.channel_id = c.id  

• 장점
  • 특정 사용자에게 전체가 아닌 필요한 부분만 보여줄 수 있음
  • 복잡한 쿼리를 단순화하고 재사용할 수 있음
• 단점
  • 한번 정의된 뷰는 변경할 수 없음
  • 삽입, 삭제, 갱신에 제한을 가짐
  • 인덱스를 가질 수 없음

Stored Procedure

• 데이터베이스 서버단에 저장되는 SQL 쿼리들
  • CREATE PROCEDURE 사용
  • DROP PROCEDURE [IF EXISTS] 로 제거
• 프로그래밍 언어의 함수처럼 인자를 넘기는 것이 가능
• 리턴되는 값은 레코드의 집합
• 간단한 분기문과 루프를 통한 프로그램이 가능
• 디버깅이 힘들고 서버단의 부하를 증가시킴

DELIMITER // -- 구분자
CREATE PROCEDURE procedure_name(parameter_list)
BEGIN
    statements;
END //
DELIMITER;

CALL stored_procedure_name(argument_list); -- 호출

• IN parameter : procedure 안으로 전달되는 매개변수

• INOUT parameter : procedure 안으로 전달되고 연산된 값을 받아 저장하는 매개변수

    DELIMITER //
    CREATE PROCEDURE return_session_count(IN channelName varchar(64), INOUT
    totalRecord int)
    BEGIN
     SELECT COUNT(1) INTO totalRecord FROM test.keeyong_session_details
     WHERE channel = channelName;
    END // -- totalRecord에 COUNT(1)한 값이 저장됨
    DELIMITER ;
    SET @facebook_count = 0 -- 변수선언
    CALL return_session_count('Facebook', @facebook_count);
    SELECT @facebook_count;

Stored Function

• 값을 하나 리턴해주는 서버쪽 함수
  • 리턴값은 Deterministic 또는 Non Deterministic
• 모든 함수의 인자는 IN 파라미터
• SQL 안에서 사용 가능 → Stored Procedure와의 차이점
• CREATE FUNCTION 사용

DELIMITER $$
CREATE FUNCTION test.Channel_Type(channel varchar(32))
RETURNS VARCHAR(20) -- 리턴타입
DETERMINISTIC
BEGIN
 DECLARE channel_type VARCHAR(20);
 IF channel in ('Facebook', 'Instagram', 'Tiktok') THEN
 SET channel_type = 'Social Network';
 ELSEIF channel in ('Google', 'Naver') THEN
 SET channel_type = 'Search Engine';
 ELSE
 SET channel_type = channel;
 END IF;
-- return the customer level
RETURN (channel_type);
END$$

SELECT channel, test.Channel_Type(channel) -- 호출
FROM prod.channel;

Trigger

• INSERT, DELETE, UPDATE 실행 전후에 특정 작업을 수행하는 것
  • 대상 테이블 지정 필요
• NEW, OLD modifier
  • NEW는 INSERT와 UPDATE에서 사용 가능
  • OLD는 DELETE와 UPDATE에서 사용 가능

CREATE TRIGGER test.before_update_keeyong_name_gender -- 트리거 생성
    BEFORE UPDATE ON test.keeyong_name_gender -- 업데이트 직전에
    FOR EACH ROW 
 INSERT INTO test.keeyong_name_gender_audit
 SET name = OLD.name, -- OLD : update로 바뀌기 전의 데이터
     gender = OLD.gender,
     modified = NOW();

UPDATE test.keeyong_name_gender
SET name = 'Keeyong'
WHERE name = 'Keeyong2';

Explain SQL

• 쿼리가 어떻게 수행되는지 내부를 보여주는 SQL 명령
  • MySQL이 해당 쿼리를 어떻게 실행할 지 계획을 보여줌
  • 느리게 동작하는 쿼리의 최적화 가능
    • 일반적으로 문제가 되는 테이블에 인덱스를 붙혀 해결함

INDEX

• Index는 테이블에서 특정 찾기 작업을 빠르게 수행하기 위해 MySQL이 별도로 만드는 데이터 구조 (B-tree)
• 컬럼별로 만들어짐
• Primary Key나 Foreign Key로 지정된 컬럼은 기본적으로 Index를 가짐
• 인덱스를 사용하지 않으면 테이블을 처음부터 끝까지 읽어 시간이 많이 소모됨
• SELECT, DELETE, JOIN 명령을 빠르게 하지만 INSERT, UPDATE 명령은 느리게 하는 단점 존재
• 테이블에 너무 많은 인덱스를 추가하면 인덱스를 로딩하는 오버헤드로 인해 시스템이 전체적으로 느려짐

CREATE TABLE example (
    id INT NOT NULL AUTO_INCREMENT,
    index_col VARCHAR(20),
  PRIMARY KEY (id),
    INDEX index_name (index_col)
)