2021-08-13 TIL (데이터베이스 - 트랜잭션,VIEW)

트랜잭션 소개

테이블 내용을 변경하는 SQL들이 연달아 실행되며 이것들이 마치 하나의 SQL처럼 다 같이 성공하던지 아니면 실패 해야 한다면 트랜잭션의 사용이 필수이다.

 

트랜잭션이란?

• Atomic하게 실행되어야 하는 SQL들을 묶어서 하나의 작업처럼 처리하는 방법
  • 이는 DDL이나 DML중 레코드를 수정/추가/삭제한 것에만 의미가 있음
  • SELECT에는 트랜잭션을 사용할 이유가 없음
  • BEGIN과 END 혹은 BEGIN과 COMMIT 사이에 해당 SQL들을 사용
  • ROLLBACK을 사용하여 되돌릴 수 있음

 

트랜잭션의 좋은 예

• 은행 계좌 이체
  • 계좌 이체: 인출과 입금의 두 과정으로 이뤄진다.
  • 만일 인출은 성공했는데 중간에 에러가 나서 입금이 실패한다면 잘못된 것이다.
  • 이 두 과정은 동시에 성공하던지 실패해야 한다. -> Atomic하다는 의미
  • 이런 과정들을 트랜잭션으로 묶어줘야한다.
  • 조회(Select)만 한다면 이는 트랜잭션으로 묶일 이유가 없음.

 

트랜잭션

BEGIN; -- START TRANSACTION
	A의 계좌로부터 인출; 	-- 이 명령어들은 마치 하나의 명령어처럼 처리됨
    B의 계좌로 입금;		--다 실패하던지 다 성공하던지 둘중의 하나가 됨
END; -- COMMIT

 

• BEGIN과 START TRANSACTION은 같은 의미
• END와 COMMIT은 동일
• 만일 BEGIN 전의 상태로 돌아가고 싶다면 ROLLBACK 실
• 이 동작은 AUTOCOMMIT 모드에 따라 달라질 수 있음.

트랜잭션 커밋 모드 : AUTOCOMMIT

• autocommit = True
  • 모든 레코드 수정/삽입/삭제 작업이 기본적으로 바로 데이터베이스에 쓰여짐. 이를 커밋(Commit)된다고 함.
  • 만일 특정 작업을 트랜잭션으로 묶고 싶다면 BEGIN과 END(COMMIT)/ROLLBACK으로 처리
• autocommit = False
  • 모든 레코드 수정/삭제/추가 작업이 COMMIT이 호출될 때까지 커밋되지 않음
  • 즉 명시적으로 커밋을 해야함
  • ROLLBACK이 호출되면 앞서 작업들이 무시됨

이는 SQL 클라이언트/라이브러리에 따라 달라짐

 

• MySQL Workbench 기본은 autocommit이 True
  • 확인 방법 : SHOW VARIABLES LIKE'AUTOCOMMIT'
  • SET autocommit=0(혹은 1)의 실행으로 변경가능

 

View란?

• 자주 사용하는 SQL 쿼리 (SELECT)에 이름을 주고 그 사용을 쉽게 하는 것
  • 이름이 있는 쿼리가 view로 데이터베이스단에 저장됨
    • select 결과가 테이블로 저장되는 것이 아니라 view가 사용될 때마다 select가 실행됨
    • 그런 이유로 가상 테이블이라고 부르기도 함(Virtual Table)
  • CREATE OR REPLACE VIEW 뷰이름 AS SELECT ...

 

Stored Procedure 란?

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

-- 정의 문법
DELIMITER //
CREATE PROCEDURE procedure_name(parameter_list)
BEGIN
	statements;
END //
DELIMITER;

-- 호출
CALL stored_procedure_name(argument_list);

-- IN 파라미터

DROP PROCEDURE IF EXISTS return_session_details;
DELIMITER //
CREATE PROCEDURE return_session_details(IN channelName varchar(64))
BEGIN
 SELECT *
 FROM test.youngkwon_session_details
 WHERE channel = channelName;
END //
DELIMITER ;

CALL return_session_details('Facebook');

-- INOUT 파라미터
DROP PROCEDURE IF EXISTS return_session_count;
DELIMITER //
CREATE PROCEDURE return_session_count(IN channelName varchar(64), INOUT
totalRecord int)
BEGIN
 SELECT COUNT(1) INTO totalRecord FROM test.youngkwon_session_details
 WHERE channel = channelName;
END //
DELIMITER ;

SET @facebook_count = 0
CALL return_session_count('Facebook', @facebook_count);
SELECT @facebook_count;

 

Stored Function이란?

• 값(Scalar)을 하나 리턴해주는 서버쪽 함수(특정 데이터베이스 밑에 등록됨)
  • 리턴값은 Deterministic 혹은 Non Deterministic
    • (같은 입력에 항상 같은 출력이 나오면 Deterministic, 입력이 같아도 출력이 다르면 Non Deterministic)
  • 모든 함수의 인자는 IN 파라미터
  • SQL 안에서 사용가능 : Stored Procedure와 가장 다른 차이점
  • CREATE FUNCTION 사용

-- Stored 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$$

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

 

Trigger란?

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

-- 예
CREATE TRIGGER 트리거이름
{BEFORE|AFTER}{INSERT|UPDATE|DELETE}
ON table_name FOR EACH ROW
trigger_body;​

• 중요 테이블의 경우 감사(audit)가 필요
  • 레코드에 변경이 생길 때마다 변경전의 레코드를 저장하는 트리거를 만들어보자

-- 테이블 예
CREATE TABLE test.youngkwon_name_gender_audit (
 name varchar(16),
 gender enum('Male', 'Female'),
 modified timestamp
);

-- 트리거 정의
CREATE TRIGGER test.before_update_youngkwon_name_gender
 BEFORE UPDATE ON test.youngkwon_name_gender
 FOR EACH ROW
 INSERT INTO test.youngkwon_name_gender_audit
 SET name = OLD.name,
 gender = OLD.gender,
 modified = NOW();
 
-- 트리거 사용 예
UPDATE test.youngkwon_name_gender
SET name = 'youngkwon'
WHERE name = 'youngkwon2';

SELECT * FROM test.youngkwon_name_gender_audit;

 

성능 튜닝 : Explain SQL 과 Index 튜닝과 실습

Explain SQL

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

-- EXPLAIN 예시
EXPLAIN SELECT
	LEFT(s.created,7) as mon,
    c.channel
    COUNT(DISTINCT user_id) AS mau
FROM session s
JOIN CHANNEL c ON c.id = s.channel_id
GROUP BY 1,2
ORDER BY 1 DESC, 2;

 

Index 소개

• Index는 테이블에서 특정 찾기 작업을 빠르게 수행하기 위해서 MySQL이 별도로 만드는 데이터 구조를 말함
  • 컬럼별로 만들어짐
  • Primary Key 나 Foreign Key로 지정된 컬럼은 기본적으로 Index를 갖게 됨
  • 특정 컬럼을 바탕으로 검색을 자주 한다면 Index 생성이 큰 도움이 될 수 있음
• Index와 Key는 동의어
• Index는 SELECT/DELETE/JOIN 명령을 빠르게 하지만 대신 INSERT/UPDATE 명령은 느리게 하는 단점이 존재
  • 테이블에 너무 많은 인덱스를 추가하면 인덱스의 로딩으로 인한 오버헤드로 인해 시스템이 전체적으로 느려질 수 있음 

-- 테이블 생성시 Index 생성 예
CREATE TABLE example(
	id INT NOT NULL AUTO_INCREMENT,
    index_col VARCHAR2(20),
    PRIMARY KEY(id),
    INDEX index_name(index_col)
);

-- Index를 테이블 생성 후 나중에 ALTER TABLE 혹은 CREATE INDEX 함수로 생성 가능
-- 예시
ALTER TABLE testalter_tbl ADD INDEX(column1);
ALTER TABLE testalter_tbl ADD UNIQUE(column1);
ALTER TABLE testalter_tbl ADD FULLTEXT(column1);
ALTER TABLE testalter_tbl DROP INDEX(column1);

CREATE UNIQUE INDEX index_name ON table_name (column1,column2...);

-- INDEX가 존재하는 필드 확인
SHOW INDEX FROM TABLE_NAME