2022년 04월 01일 TIL

Transaction

• 데이터베이스의 상태를 변화시키기 위해 수행되는 작업의 단위
• atomic하게 실행되어야 하는 SQL들을 묶어서 하나의 작업처럼 처리하는 방법

insert/delete/update → commit X

세션 내에서는 반영된 것 처럼 보이지만 commit을 하지 않았기 때문에 실제 변경 사항이 DB에 반영되지 않는다.

autocommit = true, autocommit = false

autocommit = ture

작업이 기본적으로 바로 데이터베이스에 반영된다. 만약 바로 반영을 원하지 않을 경우 BEGIN과 END(COMMIT)/ROLLBACK을 사용해서 처리한다.

autocommit = false

명시적으로 커밋을 해야만 변경된 내용이 반영된다.

DELETE FROM table_name VS TRUNCATE table_name

모든 레코드를 삭제한다.

TRUNCATE table_name

• 속도가 빠르다.
• where를 지원하지 않는다.
• transaction이 지원되지 않는다.(ROLLBACK이 불가능)

View

자주 사용하는 SQL(select)에 이름을 주고 쉽게 사용하자!

// View 생성
CREATE OR REPLACE VIEW session_details 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;

// View 사용
SELECT * FROM session_details;

Stored Procedure

• View와 비슷하나 더 강력하다
• 인장를 넘길 수 있다.
  • in: 입력값
  • out: 반환값
  • inout: 입력값, 반환값
• 프로그램이 가능하다.
• 단 디버깅이 힘들고 서버단의 부하를 증가시킨다.

(1) without parameter
DELIMITER //
CREATE PROCEDURE return_session_details()
BEGIN
 SELECT * FROM session_details;
END // 
DELIMITER;

CALL return_session_details();

(2) IN parameter
DELIMITER //
CREATE PROCEDURE return_session_details(IN channelName varchar(64)) 
BEGIN
	SELECT * FROM session_details WHERE channel = channelName;
END // 
DELIMITER;

CALL return_session_details('Facebook');

(3) INOUT parameter
DELIMITER //
CREATE PROCEDURE return_session_count(IN channelName varchar(64), INOUT totalRecord int)
BEGIN
	SELECT COUNT(1) INTO totalRecord FROM session_details WHERE channel = channelName; 
END //
DELIMITER;

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

Stored Function

• 값을 하나 return 한다.
  • Deterministic: 같은 입력에 대해 항상 출력
  • Non Determinisitc: 같은 입력에 대해 다른 출력
• 특정 데이터베이스 밑에 등록된다.
• 모든 파라미터가 in이다.(입력만 가능)

DELIMITER $$
CREATE FUNCTION 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 (channel_type);
END$$

SELECT channel, test.Channel_Type(channel) FROM prod.channel;

Trigger

• 레코드에 변경이 실행 전후에 특정 작업을 수행하도록 한다.
  • NEW: INSERT, UPDATE에서만 사용 가능
  • OLD: DELETE와 UPDATE에서만 사용 가능

// Trigger 삭제
DROP TRIGGER IF EXISTS before_update_name_gender;

// Trigger 생성
CREATE TRIGGER test.before_update_name_gender 
    BEFORE UPDATE ON test.name_gender
    FOR EACH ROW 
 INSERT INTO test.name_gender_audit
SET name = OLD.name,
     gender = OLD.gender,
     modified = NOW();

// Trigger 작동
UPDATE name_gender SET name = 'Keeyong' WHERE name = 'Keeyong2';
SELECT * FROM name_gender_audit;

Explain SQL

쿼리가 어떻게 수행되는지 내부를 보여준다.

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 소개

• 테이블에서 특정 찾기 작업을 빠르게 수행하기 위해서 MySQL이 별도 만드는 데이터 구조를 말한다.
• 단 시스템이 전체적으로 느려질 수 있다.

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

// Index 추가
ALTER TABLE testalter_tbl ADD INDEX (column1); 
ALTER TABLE testalter_tbl ADD UNIQUE (column1); //unique한 Index 생성
ALTER TABLE testalter_tbl ADD FULLTEXT (column1); // FULLTEXT 검색을 많이 할 경우
ALTER TABLE testalter_tbl DROP INDEX (column1); // Index 삭제

// Index 추가 + 복수개의 컬럼 Index 설정
CREATE UNIQUE INDEX index_name ON table_name (column1, column2);