(W2D5) 데이터베이스(5)

August 13, 2021

트랜잭션

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

Atomic하다. : 하나의 SQL처럼 동작한다.

DDL이나 DML 중 레코드를 수정/추가/삭제한 것에만 의미가 있다.
SELECT에는 트랜잭션을 사용할 이유가 없다.
BEGIN과 END / BEGIN과 COMMIT 사이에 해당 SQL들을 사용한다. (END와 COMMIT은 같은 기능을 하는데 다른 표현일 뿐)
ROLLBACK
예) 은행 계좌 이체
- 인출과 입금의 두 과정 - 인출은 성공했는데 입금이 실패한다면?
- -> 두 과정은 동시에 성공하던지 실패해야함 => Atomic하다
- 이런 과정들을 트랜잭션으로 묶어준다.
BEGIN과 START TRANSACTION은 같은 의미이다.
END와 COMMIT은 동일하다.
BEGIN 전의 상태로 돌아가고 싶다면 ROLLBACK을 실행한다.
```
BEGIN; -- START TRANSACTION
	A의 계좌로부터 인출;
	B의 계좌로 입금;
END; -- COMMIT
```
- 이 동작은 autocommit 모드에 따라 달라진다.

autocommit = True
- 모든 레코드 수정/삭제/추가 작업이 바로 데이터베이스에 쓰여진다. -> 커밋(Commit)된다고 부른다.
- 만일 특정 작업을 트랜잭션으로 묶고 싶다면 BEGIN과 END(COMMIT) / ROLLBACK으로 처리한다.
autocommit = False
- 모든 레코드 수정/삭제/추가 작업이 COMMIT이 호출될 때까지 커밋되지 않는다.
- 명시적으로 커밋을 해야한다.
- ROLLBACK이 호출되면 앞의 작업들이 무시된다.
현재 autocommit 값 확인
- MySQL Workbench 기본은 autocommit이 True이다.
- 확인 방법 : SHOW VARIABLES LIKE ‘AUTOCOMMIT’;
- SET autocommit=0 -> False / 1 -> True 로 모드를 변경 가능하다.

+) TRUNCATE는 트랜잭션 안에서 사용을 해도 모든 레코드를 삭제한다. (트랜잭션을 지원하지 않음)

자주 사용하는 SQL 쿼리(SELECT)에 이름을 주고 그 사용을 쉽게 하는 것이다.

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

정의 문법

DELIMITER //
CREATE PROCEDURE procedure_name(parameter_list)
BEGIN
	statements;
END //
DELIMITER;

호출 문법

CALL stored_procedure_name(argument_list);

값(Scalar)을 하나 리턴해주는 서버쪽 함수이다. (특정 데이터베이스 밑에 등록됨) (레코드 리턴 X)

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

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

테이블에서 특정 찾기 작업을 빠르게 수행하기 위해서 MySQL이 별도로 만드는 데이터 구조

컬럼별로 만들어진다.
Primary Key나 Foreign Key로 지정된 컬럼은 기본적으로 Index를 갖게 된다.
특정 컬럼을 바탕으로 검색을 자주 한다면 Index 생성이 큰 도움이 될 수 있다.
INDEX와 KEY는 동의어
Index는 SELECT/DELETE/JOIN 명령을 빠르게 하지만 INSERT/UPDATE 명령은 느리게 하는 단점이 존재한다.
- 테이블에 너무 많은 인덱스를 추가(리소스를 잡아먹음)하면 인덱스의 로딩으로 인한 오버헤드로 인해 시스템이 전체적으로 느려질 수 있다.

CREATE TABLE 할 때 추가 (컬럼 속성)

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

Nothing changes if nothing changes