📝SQL 기본 다지기(Transaction,View,Stored Procedure,Trigger,Index)

📝10일차 데이터베이스 실습

1. 트랜잭션(Transaction)

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

ACID(Atomicity, Consistency, Isolation, Durability)의 특징을 가진다.

• 이는 DDL이나 DML 중 레코드를 수정/추가/삭제한 것에만 의미가 있음.
• SELECT에는 트랜잭션을 사용할 이유가 없음
• BEGIN과 END 혹은 BEGIN과 COMMIT 사이에 해당 SQL들을 사용
• ROLLBACK, COMMIT

예시) ATM으로 계좌이체를 한다고 생각해보자

1. A 은행에서 출금하여 B은행으로 송금하려고 한다.
   
2. 송금 중, 알 수 없는 오류가 발생하여 A은행 계좌에서 돈은 빠져 나갔지만 B은행의 계좌에 입금되지 않았다.
   
3. 이와 같은 상황을 막기위해 거래가 성공적으로 모두 끝나야 이를 완전한 거래로 승인하고, 거래 도중 뭔가 오류가 발생했을 때는 이 거래를 처음부터 없었던 거래로 완전히 되돌리는 것을 의미한다.

   이렇게 거래의 안전성을 확보하는 방법이 트랜잭션이다.
   
   갱신, 삭제하는데 처리 도중 오류가 발생하면 모든 작업을 원상태로 되돌리고, 처리 과정이 모두 성공해야 DB에 반영한다.

• Commit(커밋)

  • 모든 부분작업이 정상적으로 완료하면 이 변경사항을 한꺼번에 DB에 반영합니다.

• Rollback(롤백)

  • 부분 작업이 실패하면 트랜잭션 실행 전으로 되돌립니다.

  • 이때 모든 연산을 취소하지 않고 정해진 부분까지만 되돌리고 싶을때 savepoint를 써주면 된다.

  • SAVEPOINT

    • 일반적으로 ROLLBACK을 명시하면 INSERT, DELETE, UPDATE 등의 작업 전체가 취소되지만,
    • SAVEPOINT를 사용하면 전체가 아닌 특정 부분에서 트랜잭션을 취소시킬 수 있다.
    • 취소하려는 지점을 SAVEPOINT로 명시한뒤 ROLLBACK TO 세이브포인트이름; 을 실행하면 지정한 해당 SAVEPOINT 지점까지 처리한 작업이 ROLLBACK됩니다.

  • -- SAVEPOINT 지정!
    SAVEPOINT 세이브포인트이름;
    
    -- SAVEPOINT로 롤백!
    ROLLBACK TO 세이브포인트이름;
    

💡 트랜잭션(Transaction)의 연산과정

SQL 문법

BEGIN; -- START TRANSACTION
 A의 계좌로부터 인출;
 B의 계좌로 입금;
END; -- COMMIT

-- ● BEGIN과 START TRANSACTION은 같은 의미
-- ● END와 COMMIT은 동일
-- ● 만일 BEGIN 전의 상태로 돌아가고 싶다면 ROLLBACK 실행

⚠️위의 동작들은 AUTOCOMMIT 모드에 따라 달라진다.

AUTOCOMMIT : 데이터 변경 작업에 대한 SQL 자체가 바로 반영되는 것을 의미하는데 autocommit 이 아닌 상태에서는 여러줄의 명령을 하나의 트랜잭션으로 묶을수가 있다.

• autocommit = TRUE

  • 모든 레코드 수정/삭제/추가 작업이 기본적으로 바로 데이터베이스에 쓰여짐. 이를 커밋(Commit)된다고 한다.
  • 만일 특정 작업을 트랜잭션으로 묶고 싶다면 BEGIN과 END(COMMIT)/ROLLBACK으로 처리한다.

• autocommit = FALSE

  • 모든 레코드 수정/삭제/추가 작업이 COMMIT이 호출될 때까지 커밋되지 않음
  • 즉, 명시적으로 커밋을 해야함
  • ROLLBACK이 호출되면 앞서 작업들이 무시된다.

-- autocommit 모드 확인하기 
SHOW VARIABLES LIKE 'AUTOCOMMIT';

-- (혹은 1 = ON)의 실행으로 변경가능
SET autocommit=0  -- OFF

2. View

뷰(view)는 데이터베이스에 존재하는 일종의 가상 테이블을 의미합니다.

이러한 뷰는 실제 테이블처럼 행과 열을 가지고 있지만, 실제로 데이터를 저장하고 있지는 않습니다.

• 이름이 있는 쿼리가 View로 데이터베이스단에 저장된다.

  • SELECT 결과가 테이블로 저장되는 것이 아니라 View가 사용될 때마다 SELECT가 실행된다.

• 기본 문법

CREATE VIEW 뷰이름 AS
SELECT 필드이름1, 필드이름2, ...
FROM 테이블이름
WHERE 조건

• 예시

-- CREATE 문에 OR REPLACE 절을 추가하여 기존에 존재하는 뷰를 새로운 뷰로 대체할 수 있다. 기존에 없으면 그냥 create
CREATE OR REPLACE VIEW test.youji_session_details AS
SELECT s.id, s.user_id, s.created, s.channel_id, c.channel
FROM prod.session s
JOIN prod.channel c ON c.id = s.channel_id;

SELECT * FROM test.youji_session_details;

---

3. Stored Procedure, Trigger

1️⃣ Stored Procedure

• MySQL 서버단에 저장되는 SQL 쿼리들
  • CREATE PROCEDURE 사용
  • DROP PROCEDURE [IF EXISTS]로 제거
• 프로그래밍 언어의 함수처럼 인자를 넘기는 것이 가능
• 리턴되는 값은 레코드들의 집합 (SELECT와 동일)
• 간단한 분기문(if, case)과 루프(loop)를 통한 프로그램이 가능
• 디버깅이 힘들고 서버단의 부하를 증가시킨 다는 단점 존재
  
  

기본 문법

-- 문법 정의
DELIMITER // -- $$도 가능!
CREATE PROCEDURE stored_procedure_name(parameter_list)
BEGIN
 statements;
END //
DELIMITER ;

-- 호출 문법
CALL stored_procedure_name(argument_list);

프로시져 심화 - IN 파라미터 (파라미터 값을 넘긴다.)

DELIMITER //
CREATE PROCEDURE test.return_youji_session_details(IN channelName varchar(64))
BEGIN
   SELECT *
   FROM test.youji_session_details
   WHERE channel = channelName;
END //
DELIMITER ;

-- 호출 
CALL test.return_youji_session_details('Facebook');

프로시져 심화 - INOUT 파라미터 (파라미터 값을 넘기고 반환한다.)

-- sql문
DELIMITER //
CREATE PROCEDURE test.return_youji_session_count(IN channelName varchar(64), INOUT totalRecord int)
BEGIN
   SELECT COUNT(1) INTO totalRecord FROM test.youji_session_details
   WHERE channel = channelName;
END //
DELIMITER ;

-- 호출
SET @facebook_count = 0;
CALL test.return_youji_session_count('Facebook', @facebook_count);
SELECT @facebook_count;
-- 14130 값을 출력한다.

2️⃣Stored Function

• 값(Scalar)을 하나 리턴해주는 서버쪽 함수 (특정 데이터베이스 밑에
  등록됨)
  • 리턴값은 Deterministic 혹은 Non Deterministic
  • 현재 guest 계정으로는 test 데이터베이스 밑에 생성 가능
• 모든 함수의 인자는 IN 파라미터
• SQL 안에서 사용가능: Stored Procedure와 가장 다른 차이점
• CREATE FUNCTION 사용

예시 SQL

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;

3️⃣Trigger

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

기본 문법

CREATE TRIGGER 트리거이름
{BEFORE | AFTER} {INSERT | UPDATE| DELETE } -- before/after중 언제 호출할지 정하고 3개의 명령어중 하나를 고른다.
ON table_name FOR EACH ROW
trigger_body;

실습 예제

-- Trigger
DESCRIBE test.youji_name_gender; -- describe는 그다음에 나오는 테이블의 스키마를 보여준다.(구성하는 필드의 속성,값을 보여준다).
CREATE TABLE test.youji_name_gender_audit (
    name varchar(16),
    gender enum('Male', 'Female'),
    modified timestamp
);
SELECT * FROM test.youji_name_gender;

CREATE TRIGGER test.before_update_youji_name_gender 
    BEFORE UPDATE ON test.youji_name_gender
    FOR EACH ROW 
 INSERT INTO test.youji_name_gender_audit
 SET name = OLD.name,
     gender = OLD.gender,
     modified = NOW();

UPDATE test.youji_name_gender -- 이 업데이트 문을 실행하면 프로시져로 등록된 테이블에 자동으로 old값이 추가된다.
SET name = 'youji123'
WHERE name = 'youji';

-- 업데이트된 결과 호출
select * from test.youji_name_gender;

-- 트리거로 설정한 필드값들을 보여준다.(update 후)
SELECT * FROM test.youji_name_gender_audit;

---

4. Explain SQL과 Index 튜닝

1️⃣Explain SQL

• SELECT/UPDATE/INSERT/DELETE 등의 쿼리가 어떻게 수행되는지 내부를 보여주는 SQL 명령
  • MySQL이 해당 쿼리를 어떻게 실행할지 Execution Plan을 보여줌. 이를 바탕으로 느리게 동작하는 쿼리의 최적화가 가능해졌다.
  • 보통 느린 쿼리의 경우 문제가 되는 테이블에 인덱스를 붙이는 것이 일반적이다.

실습 예제

-- EXPLAIN
EXPLAIN SELECT  -- 나중에 성능에 문제가 있다? 그러면 explain을 써보자. 일단은 이런게 있다는것에 집중
  LEFT(s.created, 7) AS mon,
  c.channel,
  COUNT(DISTINCT user_id) AS mau
 FROM prod.session s
 JOIN prod.channel c ON c.id = s.channel_id
GROUP BY 1, 2      
ORDER BY 1 DESC, 2;

2️⃣INDEX

• Index는 테이블에서 특정 찾기 작업을 빠르게 수행하기 위해서
  MySQL이 별도로 만드는 데이터 구조를 말한다.
  • 컬럼별로 만들어짐
  • Primary Key나 Foreign Key로 지정된 컬럼은 기본적으로 Index를 갖게 됨
  • 특정 컬럼을 바탕으로 검색을 자주 한다면 Index 생성이 큰 도움이 될 수 있음
• INDEX와 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)
 -- index 뒤에 사용할 이름 ( 필드 명)을 넣어준다.
);

실습 예제

CREATE TABLE test.session_with_index_youji (
    id int NOT NULL auto_increment,
    user_id int not NULL,
    created timestamp not NULL default CURRENT_TIMESTAMP,
    channel_id int not NULL,
    PRIMARY KEY(id),
    FOREIGN KEY(channel_id) references prod.channel(id),
    INDEX user_id(user_id)
);

-- 위의 테이블에 기존 prod.session의 내용을 복사하고 싶다면 아래 명령 실행
INSERT INTO test.session_with_index_youji SELECT * FROM prod.session;

-- 호출 : index가 걸린 정보를 알 수 있다.
show index from test.session_with_index_youji;

-- 성능비교
SELECT user_id, COUNT(1) FROM prod.session GROUP BY 1; -- 0.057 sec

SELECT user_id, COUNT(1) FROM test.session_with_index_youji GROUP BY 1; -- 0.027 sec 

-- => index를 쓸때 조금 더 빠른 성능을 보인다.