MySQL - 트랜잭션 등.

트랜잭션

• 테이블 내용을 변경하는 SQL들이 연달아 실행되며, 이들이 마치 하나의 SQL처럼 다 같이 성공해야 하며 일부의 실패가 엉뚱한 결과로 연결될 경우에 사용.

• 작업 중 일부의 실패도 허용될 수 없다면 차라리 작업 이전의 상태로 되돌리기 위해서 사용.

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

  예시: 은행 계좌 이체.
  계좌이체는 송금 계좌에서 금액 인출 > 입금 계좌에서 금액 입금. 두 과정으로 이루어지는데, 만약 인출은 성공했는데 입금이 실패하는 경우가 생긴다면?
  이 두 과정은 모두 성공하든지, 모두 실패해야 함 > 이것을 Atomic하다고 함.

• TRANSACTION의 사용

  BEGIN, START TRANSACTION 으로 시작.
  END, COMMIT으로 종결.
  ROLLBACK을 넣으면 앞의 작업을 모두 무효로 하고 원래 상태로 환원시킴.

AUTOCOMMIT

• boolean 타입.

1. auotcommmit = True

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

2. autocommit = false

• 모든 레코드 수정/삭제/추가 작업이 COMMIT이 호출될 때까지 커밋되지 않음.
• 즉, 명시적으로 커밋을 해야함.
• ROLLBACK이 호출되면 앞선 작업들이 무시됨.
• 모든 레코드 변경이 트랜잭션을 기반으로 작업된다고 생각해도 됨.
• BEGIN을 사용할 필요가 없음.
• 주기적으로 COMMIT을 해줘야 함.

SET auotcommit =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)로 제거.

• 프로그래밍 언어의 함수처럼 인자를 넘기는 것이 가능.
  IN : 어떤 값을 읽기 전용으로 넘김.
  OUT : 어떤 값을 넣어서 리턴하기.
  INOUT : 둘 다 수행.
• 리턴되는 값은 레코드들의 집합(SELECT와 동일.)
• 간단한 분기문 (if, case)와 루프(loop)를 통한 프로그램 가능.
• 디버깅이 힘들고 서버단의 부하를 증가시킨다는 단점 존재.

Stored Function

• 값(Scalar)을 하나 리턴해주는 서버쪽 함수(특정 데이터베이스 밑에 등록됨.)
  리턴값은 Deterministic 혹은 Non Deterministic
  Deterministic : 같은 입력에 대해서 같은 출력이 나오는 것.
  Non Deterministic : 입력이 같아도 출력이 다를 수 있는 것.
• 모든 함수의 인자는 IN 파라미터.
• SQL 안에서 사용 가능 : Stored Procedure와 가장 다른 차이점.
  MySQL이 제공하는 함수처럼 사용 가능.

Trigger

• CREATE TRIGGE 명령을 사용.
• INSERT/DELETE/UPDATE 실행 전후에 특정 작업을 수행하는 것이 가능.
  대상 테이블 지정이 필요.
• NEW/OLD modifier
  NEW는 INSERT, UNDATE에서만 사용 가능.
  OLD는 DELETE, UPDATE에서만 사용 가능.
• 해당 테이블에 설정해놓은 작업이 발생하기 전/발생한 후(설정 가능) TRIGGER으로 걸어놓은 작업 자동으로 실행.

  CREATE TRIGGE 트리거이름
  (BEFORE | AFTER) > TRIGGER 발동 시점. 작업 전? 작업 후?
  (INSERT | UPDATE | DELETE) > 어느 작업에 TRIGGER을 걸 것인가?
  ON table_name FOR EACH ROOW
  trigger_body;

중요 테이블의 경우 감사(audit)가 필요.
레코드의 변경이 생길 때마다 변경 전의 레코드를 저장하는 트리거를 생성할 수 있다.

Explain SQL

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

Index

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