MySQL 고급 기능 (트랜잭션, View, 프로시져, 트리거, Explain SQL, 인덱스)

🏅 트랜잭션이란?

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

 

이 트랜잭션은 DDL이나 DML중 레코드를 수정/추가/삭제한 것에만 의미있게 작동한다.

따라서 SELECT에는 트랜잭션을 사용할 이유가 없다.

 

트랜잭션은 BEGIN(START TRANSACTION) ~ END(START TRANSACTION) 또는 BEGIN ~ COMMIT 사이에 해당 SQL들을 사용한다.

기본적으로 END와 COMMIT은 같은 기능을 하지만 다른 표현으로 사용한다.
마찬가지로 BEGIN과 START TRANSACTION은 같은 의미다.

 

BEGIN으로 시작하는 순간 트랜잭션을 시작하게 되고, END나 COMMIT 키워드가 나와야 그제서야 SQL문이 외부로 반영되게 된다.

 

 

트랜잭션을 사용해야하는 예로 아주 좋은 예가 바로 은행 계좌이다.

계좌 이체는 인출과 입금 두 과정으로 이루어진다.

 

만약 인출에는 성공했는데, 입금에 실패한다면? 돈은 빠져나갔는데 상대방 계좌로 입금이 되지않아서 엄청난 문제가 발생한다;;

 

따라서 이 두 과정은 동시에 성공하던지 동시에 실패해야 한다. (하나로 묶음으로써 Atomic함을 의미한다)

이 과정을 동시에 묶는 방법이 바로 트랜잭션이다. (그래서 단순 조회는 묶을 필요가 없다)

 

BEGIN; -- START TRANSACTION
-- 이 블록안의 명령어들은 마치 하나의 명령어 처럼 처리됨
-- 성공하던지, 다 실패하던지 둘중 하나가 됨.
    A의 계좌로부터 인출;
    B의 계좌로 입금;
END; -- COMMIT

여기서 BEGIN전 상태로 돌아가고 싶다면 END나 COMMIT 키워드 대신 ROLLBACK 키워드를 사용하면 된다.

 

이 트랜잭션은 END나 COMMIT이 불리기 전까지 지금당장 MySQL을 연결해서 트랜잭션을 실행한 사람들한테는 변경들이 만들어진것처럼 보이지만 다른사람들한테는 안보이고, 그러다가 내가 END나 COMMIT을 호출하는 순간, 다른사람한테도 그 변경이 보이게 된다.

---

트랜잭션 커밋 모드 : autocommit

 

- autocommit = True

• 모든 레코드의 수정/삭제/추가 작업이 디폴트로 바로 데이터베이스에 쓰여진다. (이를 커밋(commit) 된다고 함)
• 만약 특정 작업을 트랜잭션으로 묶고 싶다면 BEGIN~END(COMMIT)/ROLLBACK으로 묶으면 된다.

즉 BEGIN ~ END(COMMIT)으로 묶어주지 않아도 DELETE, INSERT문을 적용하면 나 뿐만 아니라 외부 사람한테도 새로운 데이터베이스 내용이 적용되어 보여지게 된다.

 

그러다가 BEGIN으로 시작하면 그때부터 END(COMMIT)이 나올 때까지 INSERT, DELETE로 인한 레코드 현황을 외부사람은 확인할 수 없게 된다.

 

- autocommit = False

• 모든 레코드 수정/삭제/추가 작업이 COMMIT이 호출 될 때까지 커밋되지 않는다.
• 즉, 명시적으로 커밋을 해야한다.
• ROLLBACK이 호출되면 앞의 작업들은 무시된다.

즉, 내가 명시적으로 END(COMMIT) 해주지 않으면 아무리 INSERT, DELETE문을 작성하더라도 SQL문을 작성한 본인의 DB에만 레코드가 추가, 삭제되고 외부 사람은 그 현황을 알지 못하고 처음 상태만 알게 된다.

 

COMMIT을 명시적으로 해줘야 하므로 BEGIN으로 시작하지 않아도 트랜잭션 상황이다.

 

이 autocommit 유무는 MySQL에서 다음과 같이 확인 가능하다.

SHOW VARIABLES LIKE 'AUTOCOMMIT'; -- 오토커밋 현황을 알려줌
SET autocommit=0 (또는 1) -- 오토커밋 현황 확인후 따로 지정 가능

 

👨‍💻 DELETE FROM vs. TRUNCATE

DELETE FROM은 테이블에서 모든 레코드를 삭제하지만 테이블을 삭제하지는 않는다. (DROP TABLE table_name은 DDL이므로 테이블을 삭제시킨다)

 

그리고 WHERE를 이용해 특정 레코드도 삭제 가능하다.

DELETE * FROM으로 생각하기 쉬운데 이는 잘못 됐다. DELETE 와 FROM는 붙어있다고 생각하자.

 

하지만 이 DELETE FROM은 속도가 느리다.

TRUNCATE는 DELETE FROM에 비해 전체 테이블 내용 삭제가 빠르다.

 

하지만 다음 과같은 단점이 존재한다.

 

1. WHERE을 지원하지 않는다. (특정 필드 삭제 불가)

2. 트랜잭션을 지원하지 않는다.

 

따라서 각각의 장점에 맞게 사용하면 된다.

---

🏅 View 란?

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

 

이름이 있는 쿼리가 View로 데이터베이스 단에 저장된다.

SELECT 결과가 테이블로 저장되는 것이 아니라 View가 사용될 때마다 SELECT가 실행된다.
그런 이유로 가상 테이블이라고 부르기도 한다.(Virtual Table)

View는 다음과 같이 생성한다.

CREATE OR REPLACE VIEW 뷰이름 AS
SELECT ...

 

예시 (SELECT를 자주 사용한다면?)

-- 이건 그냥 SELECT문을 실행한 것
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로 데이터 베이스단에 기록
CREATE OR REPLACE VIEW test.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;

-- 가상 테이블을 만들어 주므로 FROM 뒤에 뷰 이름 쓰면 됨
SELECT * FROM test.session_details;

---

🏅 Stored Procedure 란?

• MySQL 서버단에 저장되는 SQL 쿼리들을 의미한다. 
• 프로그래밍 언어의 함수처럼 인자를 넘기는 것이 가능하다 (IN/OUT/INOUT).

IN : 읽기 전용으로 Stored Procedure에게 입력 값을 줌.

OUT : Stored Procedure한테 여기에다 어떤 값을 넣어서 나한테 리턴하라는 의미

INOUT : 위 2가지를 동시에 사용

• 리턴되는 값은 레코드들의 집합이다. (SELECT, VIEW와 동일)
• 분기문(IF, CASE)와 루프(LOOP)를 통해 간단한 프로그래밍이 가능하다. (전통적인 SQL이 갖고 있던 약점을 보완할 수 있음, 물론 그냥 SELECT 결과 리턴도 가능)
• 하지만 로직이 복잡해질수록 버그가 생겨나서 디버깅이 힘들고 MySQL 서버단의 부하를 증가시키는 단점이 있다. (5줄 넘어가면 보통 버그가 많이 발생함.)

CREATE PROCEDURE -- 프로시져 생성
DROP PROCEDURE [IF EXISTS] -- 프로시져 제거

VIEW랑 비슷(VIEW는 가상 테이블)하다. 이 VIEW도 MySQL 서버단에 저장되는 SQL 쿼리이고, Stored Procedure도 똑같다.

 

근데 VIEW보다 훨씬 강력하다.

 

- Stored Procedure 정의 문법

-- 어떤 기호든 상관 없지만 // 혹은 $$ 을 가장 많이 사용함 
-- (델리미터를 써주면 나중에 END 뒤에도 꼭 써줘야 함)
DELIMITER // 

-- 파라미터 별 타입을 지정해 줘야함 (IN, OUT , IN/OUT 중에 무엇인지)
CREATE PROCEDURE procedure_name(parameter_list) 
BEGIN
    statements; -- 로직 작성 (SELECT, IF, CASE, LOOP)
END //
DELIMITER;

- Stored Procedure 호출 문법

-- 이렇게 call 하면 기본적으로 레코드 들이 리턴됨. 
CALL stored_procedure_name(argument_list);

 

예제 1 (파라미터가 없는 경우).

-- 정의
DROP PROCEDURE IF EXISTS return_session_details;
DELIMITER //
CREATE PROCEDURE return_session_details()
BEGIN
    -- 단순히 테이블 내용을 리턴 (로직이 없음)
    SELECT *
    FROM test.wu2ee_session_details;
END //
DELIMITER;

-- 호출
CALL return_session_details();

 

예제 2 (IN 파라미터 사용).

DROP PROCEDURE IF EXISTS return_session_details;
DELIMITER //
CREATE PROCEDURE return_session_details(IN channelName varchar(64))
BEGIN
    SELECT *
    FROM test.wu2ee_session_details
    WHERE channel = channelName;
END //
DELIMITER ;

CALL return_session_details('Facebook');

이게 바로 VIEW와의 차이점이다.

VIEW는 내가 미리 정해놓은 SELECT를 실행해야 하는것이지 거기다가 내가 파라미터를 넘길 수는 없다.

하지만 Stored Procedure는 파라미터에 따라 SELECT의 결과가 달라지게 된다.

 

예제 3 (INOUT 파라미터 사용).

DROP PROCEDURE IF EXISTS return_session_count;
DELIMITER //
-- OUT으로 해도 되는데 일단 INOUT으로 함
CREATE PROCEDURE return_session_count(IN channelName varchar(64), INOUT totalRecord int) 
BEGIN
    -- 입력한 채널 이름의 수가 몇갠지 카운트 해서 totalRecord에 값 넣고 리턴 해줌. 
    -- (INTO 키워드가 파라미터로 카운트된 값을 넣어줌) 
    SELECT COUNT(1) INTO totalRecord FROM  test.wu2ee_session_details
    WHERE channel - channelName;
END //
DELIMITER;

SET @facebook_count = 0 -- SET 키워드로 변수 만듦. 초기값으로 0 넣어줌
-- 기본적으로 MySQL은 변수 이름이 @(at) 캐릭터로 시작함
-- 함수가 실행되고 나면 @facebook_count 변수에는 select 문에서 count한 값이 리턴되어 저장됨
CALL return_session_count('Facebook', @facebook_count); 
SELECT @facebook_count;

---

🏅 Stored Function 란?

값(Scalar)을 하나 리턴해주는 서버단 함수 (특정 데이터베이스 밑에 등록된다.)

 

즉, Stored Procedure 처럼 레코드 들을 리턴하는게 아니라 값 하나를 리턴한다.

 

• 리턴 값은 어떤 용도로 사용하느냐에 따라 Deterministic 혹은 Non Deterministic이 된다.
• Deterministic은 항상 같은 입력에 대해서 같은 출력이 리턴된다.
• Non Deterministic은 입력이 같아도 출력이 달라지게 만들 수 있다.(ex 랜덤 함수)
• 모든 함수의 인자는 IN파라미터만 가능하다.

Stored Procedure와 가장 다른 차이점은 CALL 키워드로 함수를 부르는 것이 아니라 SQL안에서 바로 사용가능하다는 점이다. 즉, MySQL에서 제공하는 native 함수처럼 사용 가능하다. (마치 LEFT, LENGTH, BUFFER, LOWER 같은 함수처럼 사용 가능)

 

예제

-- 정의
DELIMITER $$
CREATE FUNCTION test.Channel_Type(channel varchar(32))
-- 값을 하나만 리턴하기 때문에 RETURNS 키워드가 CREATE FUNCTION 뒤에 바로 나와야 함. 
-- (리턴 타입을 결정해 줘야 함)
RETURNS VARCHAR(20) 

-- Deterministic 인지 Non Deterministic인지 적어줘야 함. 
-- (입력된 채널에 해당하는 타입을 리턴함)
DETERMINISTIC 

-- 앞 수업에서 사용했던 CASE ~ WHEN를 Stored Function으로 로직을 짬.
BEGIN
    DECLARE channel_type VARCHAR(20); -- 리턴 값을 저장할 변수 (channel_type) 만듦.
    -- stored function이나 stored procedure 안에서는 변수를 정의 할 때 
    -- DECLARE로 시작하고 변수이름 주고 변수 타입을 준다.
    
    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 the customer level
    RETURN (channel_type);
END $$

-- 호출
-- 그냥 SELECT 안에서 Stored Function을 사용하면 됨.
SELECT channel, test.Channel_Type(channel) 
FROM prod.channel;

---

🏅 Trigger 란?

SELECT랑 전혀 상관 없고 INSERT/DELETE/UPDATE 실행 전후에 특정 (내가 원하는)로직을 자동으로 실행하게 해주는 방법이다. (대상 테이블이 필요함)

 

그래서 내가 어떤 테이블을 대상으로 Trigger를 걸어 놓으면(걸때 INSERT인지 DELETE인지 UPDATE인지 지정해야 하고, 이 작업 전인지 후인지도 지정해야 함.) 해당 테이블에 내가 원했던 Operation(INSERT/DELETE/UPDATE 발생 전후 지정한 것)이 발생하는 순간 Trigger에 있는 로직이 자동으로 실행 된다.

 

- NEW/OLD modifier

• NEW는 INSERT와 UPDATE에서만 사용 가능
• OLD는 DELETE와 UPDATE에서만 사용 가능

UPDATE는 이미 있는 데이터를 바꾸는 거니까 두 쌍의 데이터가 있으므로 NEW와 OLD 키워드 둘다 사용 가능. (INSERT는 새로 생겨나는 것이므로 NEW밖에 없고 DELETE는 삭제할거니까 OLD밖에 없다.)

 

- Trigger 생성 문법

CREATE TRIGGER 트리거 이름
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
-- FOR EACH ROW는 다수의 레코드가 변경 될때, 각 레코드에 트리거를 모두 적용하고 싶다는 뜻
ON table_name FOR EACH ROW 
trigger_body; -- 여기에 trigger 로직 작성.

 

예제 (레코드 변경이 생길 때마다 변경전의 레코드를 저장하는 트리거 만들기)

-- 변경된 시간을 저장할 새로운 테이블 생성
CREATE TABLE test.wu2ee_name_gender_audit ( 
    name varchar(16),
    gender enum('Male', 'Female'),
    modified timestamp
);

-- 트리거 정의
CREATE TRIGGER test.before_update_wu2ee_name_gender
    BEFORE UPDATE ON test.wu2ee_name_gender -- 이 테이블 업데이트시 트리거 발생
    FOR EACH ROW -- 모든 변경 레코드에 트리거 적용
-- 트리거 로직 시작
INSERT INTO test.wu2ee_name_gender_audit 
SET name = OLD.name, -- 변경전 이름 저장
    gender = OLD.gender, -- 변경전 성별 저장
    modified = NOW(); -- 변경된 시간 저장

-- 트리거 사용 예
UPDATE test.wu2ee_name_gender
SET name = 'wu2ee2'
WHERE name = 'wu2ee'; -- 이름을 바꾸는 업데이트 수행 (트리거 발생함)

-- 이름 바꾸기 전 wu2ee와 함께 변경 시각이 저장된 테이블 레코드를 반환함
SELECT * FROM test.wu2ee_name_gender_audit

---

🏅 Explain SQL 란?

SELECT/UPDATE/INSERT/DELETE 등의 쿼리가 어덯게 수행되는지 내부를 보여주는 SQL 명령이다.

• MySQL이 해당 쿼리를 어떻게 실행할지 Execution Plan을 보여준다. 이를 보고 느리게 동작하는 쿼리의 최적화가 가능해진다.
• 보통 느린 쿼리의 경우(ex: 특정 컬럼으로 조인, 검색, 그룹핑하는 경우)에는 데이터가 너무 커서 느린 경우도 있지만, 약간의 최적화로 빠르게 만들 수 있는 경우들이 많다.(일반적으로 인덱스를 이용하여 최적화 함)

사용 예제

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;

보통 우리가 써온 SQL문 맨 앞에 EXPLAIN을 붙여서 사용한다.

위 처럼 간단한 SQL은 쓰나 마나이지만, 복잡한 SQL문에서 EXPLAIN을 사용하면 도움이 될 수 있다.

 

Explain SQL 은 어렵다. 일단 이런 방법이 있다는걸 알고, 나중에 sql이 너무 느릴때 이걸 사용해서 더 숙련자 한테 질문하며 배워나가면 좋을 것 같다.

---

🏅 인덱스 란?

테이블에서 특정 찾기 작업을 빠르게 수행하기 위해 사용한다.

MySQL이 별도로 만드는 데이터 구조를 의미한다.

 

• 컬럼별로 만들어 진다.
• PRIMARY KEY나 FOREIGN KEY로 지정된 컬럼은 기본적으로 INDEX를 가진다.
• 특정 컬럼을 바탕으로 검색 을 자주한다면 INDEX 생성이 큰 도움이 될 수 있다.
• INDEX와 KEY는 동의어다.
• INDEX는 SELECT/DELETE/JOIN 명령은 빠르지만 INSERT/UPDATE 명령은 느리다.

인덱스를 사용하면 오버헤드가 발생할 수 있다.

 

인덱스를 붙일 때마다 MYSQL이 B+ TREE라는 자료구조를 별도로 만들어서 메모리에 로딩을 하는데, 이게 커지면 오히려 속도가 느려질 수 있고, 이런 컬럼이 너무 많아지면 시스템 리소스를 너무 많이 잡아먹기 때문에 MYSQL 전체 성능이 떨어질 수 있다. 그래서 이걸 너무 많이 쓰면 안된다.

 

또 인덱스를 붙일 떄마다 INSERT/UPDATE 명령은 오히려 더 느려지는데, 그 이유는 레코드가 새롭게 업데이트 되거나 추가될때마다 인덱스 자료구조가 다시만들어 진다. 그래서 인덱스 지정을 안했으면 테이블만 바꾸면 됐는데, 이제 인덱스 까지 바꿔야 하니까 느려짐 (즉, 쓰기 작업은 속도가 저하되는 단점이 있음.)

 

이런 단점들 때문에 이 인덱스는 꼭 필요한 컬럼에만 붙여야 한다.

 

- 인덱스 거는 방법

 

1. CREATE TABLE (테이블 생성할 때)

CREATE TABLE example (
    id INT NOT NULL AUTO_INCREMENT,
    index_col VARCHAR(20),
    PRIMARY KEY(id),
    -- 보통 index_name과 index_col 이름을 같게 함. (다르게 해도 됨)
    INDEX index_name(index_col) 
);

2. ALTER TABLE, CREATE INDEX (테이블 생성후 테이블 구성을 바꾸거나, 인덱스를 따로 지정하는 함수 이용)

ALTER TABLE testalter_tbl ADD INDEX(column1);

-- UNIQUE는 인덱스인데, 컬럼이 같은 값이여도 
-- 다른 인덱스 값을 만들라는 의미 (원래는 컬럼 값이 같으면 인덱스 값이 같음)
ALTER TABLE testalter_tbl ADD UNIQUE(column1); 

-- MySQL에서 text에 대한 검색을 많이 시행할 때 FULLTEXT를 걸면 
-- 텍스트를 빠르게 찾아주는 B+ TREE 자료구조를 만들어 준다. (text 타입 한정)
ALTER TABLE testalter_tbl ADD FULLTEXT(column1); 

-- 특정 컬럼에 걸려있는 인덱스를 삭제
ALTER TABLE testalter_tbl DROP INDEX(column1); 

-- 특정 테이블에 어느 컬럼에 인덱스 줄지 지정 가능 (여기서 UNIQUE 는 Optional)
CREATE UNIQUE INDEX index_name ON table_name( column1, column2, ... );

 

🤔인덱스가 있는 경우와 없는 경우의 SELECT 필터링 성능 측정

 

대상 테이블은 prod.session과 prod.session_with_index로 비교한다. (id와 channel_id는 기본키, 외래키로 지정되어 있어 이미 인덱스가 지정되어 있다. 따라서 인덱스는 user_id에 적용한다.)

 

실습을 위한 데이터는 AWS RDS에 이미 10만개 저장되어 있는 상황임.

 

-- 인덱스 비교를 위한 테이블 생성
CREATE TABLE prod.session_with_index (
    id int NOT NULL auto_increment,
    user_id int not NULL,
    created timestamp not NULL default CURRENT_TIMESTAMP,
    channel_id int not NULL,
    PRIMARY KEY(id),
    FOREIGN KEY(channel_id) references channel(id),
    INDEX user_id(user_id) -- 인덱스 설정
);


-- 새로 만든 테이블에 기존 테이블의 레코드들을 복사해 넣는 방법 
-- (양쪽 테이블의 필드와 타입이 동일한 경우만 가능)
INSERT INTO prod.session_with_index SELECT * FROM prod.session;

SELECT user_id, COUNT(1)
FROM prod.session
GROUP BY 1;
-- 0.299[sec]

SELECT user_id, COUNT(1)
FROM prod.session_with_index
GROUP BY 1;
-- 0.216[sec]

인덱스를 걸면 좀더 빠른 결과를 확인할 수 있다. 하지만 테이블 레코드 수가 많지 않아서 (10만개) 큰 차이는 안난다.

 

※ 참고 

SHOW INDEX FROM 테이블 이름 -- 테이블에 어떤 컬럼이 인덱스가 걸려있는지 보여줌