[데브코스] MySQL 고급기능 사용하기

W2D5 - 트랜잭션, View, 프로시저, 성능튜닝(쿼리,인덱스)에 대해 공부해보자

트랜잭션

• 여러 쿼리를 묶어서 실행할때의 단위(?)
• 여러 SQL 실행 시, 이것이 하나의 SQL처럼 모두 성공해야한다면 트랜잭션을 사용한다
• 레코드를 수정/추가/삭제 하는 등 데이터 변경시에만 사용한다 -> 실패시 이전 상태로 돌아간다
  • ex) 계좌이체를 한다고 했을 때 인출과 이체의 성공/실패 결과는 동일하게 일어나야 함.
• ACID를 만족해야 한다.
  • Atomicity (원자성) : 분해 불가능한 최소의 단위로 동작해야 한다
  • Consistency (일관성) : 작업 전과 후의 DB가 일관된 상태를 유지해야 한다
  • Isolation (독립성) : 작업 수행 중 다른 트랜잭션에 영향을 주거나, 받아서도 안된다.
  • Durablility (영속성) : 작업 완료 후 그 결과는 영구적으로 지속되어야 한다.

BEGIN;
    쿼리
    ...     -- 트랜잭션 설정
[END/ROLLBACK];    -- COMMIT

• END(COMMIT) 이전까지의 내용은 실제 DB에 반영되지 않음.
• END 명령어가 실행되야 비로소 작업 결과가 DB에 반영됨.
• ROLLBACK 실행시 지금까지의 모든 작업은 무효가 된다.

autocommit

• True 인 경우 자동으로 커밋되어 작업이 바로 DB에 반영된다.
• False 일땐 COMMIT 호출 전까지 DB에 반영되지 않는다. (잘 안씀)
• 보통 Default로 autocommit = True이므로 이 경우에 트랜잭션을 사용하는 것!!
• 이는 SQL 클라이언트/라이브러리에 따라 다름

View

CREATE [OR REPLACE] VIEW 뷰이름 [(필드명, ...)]
    AS SELECT문...

• SELECT 문을 실행한 결과 테이블(?)에 이름을 붙여 사용하는 가상 테이블처럼 사용하는 것
• 가상으로라도 테이블 형태로 저장되는게 아니라 SELECT 문을 저장하는 것임 ^^

Stored Procedure

-- 생성
DELIMITER //(종단문자)
CREATE PROCEDURE 프로시저명([IN/INOUT]파라미터, ...)
BEGIN
    쿼리
    ...
END //
DELIMITER ;
-- 호출
CALL 프로시저명(파라미터, ...);

• MySQL ‘서버’에 저장되는 SQL 쿼리임
• View와 비슷하고, 리턴값도 SELECT의 결과와 동일하지만 파라미터를 받을 수 있다.
• 쿼리를 프로그래밍 언어처럼 사용하는 기능이라고 생각하면 됨~~
• 단점 : 디버깅이 힘들고 서버에 부하를 줌

Stored Function

-- 생성
DELIMITER //(종단문자)
CREATE PROCEDURE 함수명(IN 파라미터, ...)
RETURNS 리턴타입
[Deterministic/Non Deterministic]
BEGIN
    쿼리
    ...
END //
DELIMITER ;
-- 호출
CALL 프로시저명(파라미터, ...);

• ‘값’을 리턴하는 서버쪽 함수
• 리턴값은 Deterministic/Non Deterministic -> 입력과 출력의 타입이 동일한지 여부에 따라 결정
• 파라미터는 모두 IN임
• 함수를 등록하면 쿼리에서 count나 sum같은 기본 함수처럼 사용이 가능함

Trigger

CREATE TRIGGER 트리거명
[BEFORE/AFTER] [INSERT/UPDATE/DELETE]
ON 테이블명 FOR EACH ROW
액션
... ;

• 레코드 삽입/삭제/수정 동작 전,후에 특정 작업을 등록할 수 있는 기능
• BEFORE AFTER 로 행위의 전 후를 지정함

성능튜닝

Explain SQL

EXPLAIN 
셀렉트문 ...

• 쿼리가 어떻게 수행되는지 그 내부를 보여주는 SQL 명령어
• 해당 쿼리를 어떻게 실행할지의 Execution Plan을 보여준다.
• 슬로우 쿼리의 일반적인 해결법은 테이블에 인덱스를 붙여주는 것이다!

Index

• 테이블에서 찾기 작업을 빠르게 수행하기 위해 별도로 만드는 데이터 구조
• PK, FK는 기본적으로 인덱스를 가진다. (index == key)
• 조회는 빠르나, 삽입/수정 작업은 느리게 함
• 인덱스가 너무 많으면 인덱스 로딩에 오버헤드 발생

-- 테이블 생성시 지정
CREATE TABLE 테이블명(
    ...
    INDEX 인덱스명(필드명)
)
-- 인덱스 추가 생성
CREATE INDEX 인덱스명 ON 테이블명 (필드명, ...)

• 인덱스명은 보통 필드명과 똑같이 설정한다고 함
• ALTER로도 컬럼 설정 변경 가능
• 그냥 테이블 만들때 걸어주자..!

📢 오늘의 한마디

• 백엔드 개발자는 어디까지 알아야 되는걸까? (자주 접하는 기능이 뭔가)
• 실무에서 프로시저나 트리거까지 건드리게 될까…?
• 하지만 성능튜닝은 좀 더 공부해볼 필요가 있을것 같음!!