[모각코 2회차] MySQL 고급기능 정리

목표: MySQL 고급기능 정리

MySQL 고급기능

트랜잭션

• 트랜잭션: SQL들을 묶어서 하나의 작업처럼 처리하는 방법
• 조회는 트랜잭션 필요없음

  BEGIN/START TRANSACTION;
  -- 레코드를 수정/추가/삭제
  -- ROLLBACK: BEGIN 전의 상태로 돌아가라
  END/COMMIT;

• 트랜잭션 커밋 모드
  • autocommit = True: 수정/삭제/추가시에 바로 커밋됨->트랜잭션으로 묶어 처리
  • autocommit = False: db에 반영하려면 COMMIT 직접 호출, ROLLBACK 가능

    SHOW VARIABLES LIKE 'AUTOCOMMIT'; -- 커밋모드 확인
    SET autocommit=1; -- autocommit = True
    SET autocommit=0; -- autocommit = False

• DELETE FROM vs. TRUNCATE: TRUNCATE는 Transaction을 지원하지 않음

View

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

  CREATE OR REPLACE VIEW viewName AS
  SELECT s.id, s.user_id, s.created, s.channel_id, c.channel
  FROM session s
  JOIN channel c ON c.id = s.channel_id;
  
  SELECT * FROM viewName;

Stored Procedure

• MySQL 서버단에 저장되는 SQL 쿼리들

  DROP PROCEDURE IF EXISTS procedure_name;
  
  DELIMITER //
  CREATE PROCEDURE procedure_name(IN channelName varchar(64))
  BEGIN
  SELECT *
  FROM session_details
  WHERE channel = channelName;
  END //
  DELIMITER ;
  
  CALL session_details('Facebook');

• Stored Function: 값(Scalar)을 하나 리턴, SQL 안에서 사용가능

  DELIMITER $$
  CREATE FUNCTION Food_Type(food varchar(32)) 
  RETURNS VARCHAR(20)
  DETERMINISTIC -- 동일한 결과를 반환
  BEGIN
  DECLARE food_type VARCHAR(20);
  IF food in ('초밥', '우동', '소바') THEN
  SET food_type = '일식';
  ELSEIF food in ('짜장면', '짬뽕') THEN
  SET food_type = '중식';
  ELSE
  SET food_type = food;
  END IF;
  -- return the customer level
  RETURN (food_type);
  END$$
  
  SELECT food, Food_Type(food)
  FROM food;

Trigger

• INSERT/DELETE/UPDATE 실행 전후에 특정 작업을 수행하는 것

  --트리거 생성
  CREATE TRIGGER before_update
  BEFORE UPDATE ON afterTBL
  FOR EACH ROW 
  INSERT INTO beforeTBL
  SET name = OLD.name,
  gender = OLD.gender,
  modified = NOW();
  
  --트리거 사용
  UPDATE afterTBL
  SET name = 'after'
  WHERE name = 'before';
  SELECT * FROM afterTBL;
  SELECT * FROM beforeTBL;

성능 튜닝: Explain SQL과 Index 튜닝

• Explain SQL: SELECT/UPDATE/INSERT/DELETE 등의 쿼리가 어떻게 수행되는지 내부를 보여주는 SQL 명령(느린 쿼리의 경우 문제가 되는 테이블에 인덱스 붙임)
• EXPLAIN 쿼리문;

• Index: 테이블에서 특정 찾기 작업을 빠르게 수행하기 위해서
  MySQL이 별도로 만드는 데이터 구조
• Primary Key, Foreign Key 인덱스 기본
• SELECT/DELETE/JOIN 빨라짐<->INSERT/UPDATE 느려짐
• 테이블 생성할 때 같이 생성 INDEX index_name (index_col)
• 추가 생성
  • ALTER TABLE tbl ADD INDEX (column1);
  • CREATE UNIQUE INDEX index_name ON tbl column1

@프로그래머스 미니 데브코스 & CNU SW Academy 강의 내용 정리