[2023.02.14 / CNU SW 아카데미] 실리콘벨리에서 날아온 데이터베이스 D-15

1. 트랜잭션 소개

+ 테이블 내용을 변하는 SQL들이 연달아 실행된다.

+ 이것들이 하나의 SQL 처럼 다 같이 성공하거나 아니면 실패해야 한다.

=> 트랜잭션 사용

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

- DDL이나 DML 중 레코드를 수정/추가/삭제한 것에만 의미가 있음.

- SELECT에는 트랜잭션을 사용할 이유가 없음

- BEGIN-END 혹은 BEGIN-COMMIT 사이에 해당 SQL을 사용

- ROLLBACK : 다시 원래 상태로 돌아감

트랜잭션 예 ) 은행 계좌 이체
- 계좌 이체 : 인출과 입급의 두 과정으로 이뤄짐
Q. 만일 인출은 성공했는데 입금이 실패한다면?
=> 두 과정을 Atomic 하게 실행되어야 함(트랜잭션)

사용법

BEGIN;
( Atomic action ) => 성공 / 실패
END;

- BEGIN == START TRANSACTION

- END == COMMIT

- ROLLBACK : BEGIN 전의 상태로 돌아가고 싶다면

트랜잭션 커밋 모드 : autocommit

autocommit = True
- 모든 레코드 수정/삭제/추가 작업이 기본적으로 바로 데이터베이스에 쓰임
-> commit 된다.
- 만일 특정 작업을 트랜잭션으로 묶고 싶다면 BEGIN-END로 처리
- MySQL Workbench에서는 기본값이다.
autocommit = False
- 모든 것들이 다 트랜잭션이 것이다.
- 모든 레코드 수정/삭제/추가 작업이 commit이 호출되기 전까지 커밋되지 않음
-> 명시적으로 커밋을 해야함
- ROLLBACK이 호출되면 앞서 작업들이 무시됨

SHOW VARIABLES LIKE 'AUTOCOMMIT'; -- on(True) / off(False)
SET autocommit=0; -- 0(False) / 1(True)

DELETE FROM vs. TRUNCATE

DELETE FROM table_name (not DELETE * FROM)
- 테이블에서 모든 레코드를 삭제
- vs. DROP TABLE table_name
- WHERE 사용해서 특정 레코드만 삭제 가능
- 속도가 느림
TRUNCATE table_name
- 테이블에서 모든 레코드를 삭제
- 전체 테이블의 내용 삭제시에서는 여러모로 유리
- WHERE를 지원하지 않음
- Transaction을 지원하지 않음

2. 트랜잭션 실습

MySQL Workbench의 경우 safe mode가 있다.

* safe mode 변경하기

-> Edit

-> Preferences

-> SQL Editor

-> 맨 밑에 [] Safe Updates 부분을 언체크

-> 저장

-> 다시 시작을 해줘야 함

(1) 테이블 등록 후 INSERT

keeyoung2 는 enum의 값에 해당되지 않는 값을 넣었기 때문에 빈 문자열이 넣어진다.

(2) BEGIN, ROLLBACK 실습(autocommit = True)

BEGIN;
DELETE FROM test.sujin_name_gender;
INSERT INTO test.sujin_name_gender VALUES ('Kevin', 'Male');
ROLLBACK;
SELECT * FROM test.sujin_name_gender;

다 지우고 새로운 값 'Kevin'과 'Male'을 넣었다.

-> ROLLBACK 후에는 다시 처음으로 돌아갔다.

만약 ROLLBACK 대신 END; COMMIT;을 하면 새로한 내용들이 업데이트 된다.

(3) BEGIN, ROLLBACK 실습(autocommit = False)

SET autocommit=0;
SHOW VARIABLES LIKE 'AUTOCOMMIT';
SELECT * FROM test.sujin_name_gender;
-- BEGIN이 없음
DELETE FROM test.sujin_name_gender;
INSERT INTO test.sujin_name_gender VALUES ('Kevin', 'Male');
ROLLBACK;
SELECT * FROM test.sujin_name_gender;

내가 수정하는 경우, 내가 명시적으로 commit, rollback을 하기 전에는 테이블의 내용이 바뀌지 않는다.

(4) Java로 실습하기

3. View 소개와 실습

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

- select 문의 결과를 매핑시킨다. 마치 테이블처럼 사용한다.

이름이 있는 쿼리가 View로 데이터베이스단에 저장됨
- select 결과가 테이블에 저장되는 것이 아니라 View가 사용될 때마다 SELECT가 실행됨
- Virtual Table
CREATE OR REPLACE VIEW 뷰이름 AS SELECT

View 실습하기

현재 하고 있는 프로젝트가 데이터를 많이 접근해야 할 것 같은데 view 부분을 잘 봐둬야겠다.

4. Stored Procedure, Trigger 소개와 실습

+ Stored Function도 공부해보도록 하겠다.

이제부터 배우는 것은 전부 고급기능이다.

Stored Procedure란?

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

- 백엔드 개발을 할 때 언젠가는 쓸 일이 있다.

- View는 MySQL 서버 단위로 등록되는 것이다.
- Stored Procedure은 view보다 훨씬 강력하다.

CREATE PROCEDURE 사용
DROP PROCEDURE [IF EXISTS]로 제거
프로그래밍 언어의 함수처럼 인자를 넘기는 것이 가능하다.
리턴되는 값은 레코드들의 집합(SELECT)
간단한 분기문(if, case)과 루프(loop)를 통한 프로그램이 가능
디버깅이 힘들고 서버단의 부하를 증가시킨다는 단점 존재(로직이 복잡해지면 버그남)

+ 정의 문법
DELIMITER //
CREATE PROCEDURE return_session_details()
BEGIN
SELECT *
FROM test.sujin_session_details;
END //
DELIMITER;

+ 호출 예
CALL return_session_details();

(1) IN 파라미터

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

CALL return_session_details('Facebook');

IN 인자로 주어진 채널의 이름과 동일한 레코드만 반환한다.

VIEW 와의 차이점이 여기다. VIEW는 사전에 정의해야 하는 것이지 파라미터를 넘길 수는 없다.

(2) INOUT 파라미터

DROP PROCEDURE IF EXISTS return_session_count;
DELIMITER //
CREATE PROCEDURE return_session_count(IN channelName varchar(64),
INOUT totalRecord int)
BEGIN
SELECT COUNT(1) INTO totalRecord FROM test.sujin_session_details
WHERE channel = channelName;
END //
DELIMITER ;

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

return_session_count라는 함수를 만들었다.

두 개의 인자를 받는다.

주어진 채널 name을 만족하는 레코드의 수는 몇개냐.

totalRecord라는 파라미터에 넣어서 리턴해준다.

문법이 약간 다르다.

- CALL 로 호출한다.

Stored Function란?

: 값(Scalar)을 하나 리턴해주는 서버쪽 함수(특정 데이터베이스 밑에 등록됨)

- test 데이터베이스 밑에 생성 가능하다.

- Stored Procedude는 레코드 리턴 가능, 인자를 통해 값 리턴 가능

- Stored Function은 값을 하나만 리턴한다.

- 리턴값은 Deterministic 혹은 Non Deterministic
- Deterministic : 항상 같은 입력에 대해서 같은 출력이 나온다.

- Non Deterministic : 입력이 같아도 출력이 달라지게(예 : random)

- 현재 guest 계정으로는 test 데이터베이스 밑에 생성 가능

모든 함수의 인자는 IN 파라미터
SQL 안에서 사용사능 : Stored Procedure와 가장 다른 차이점
CREATE FUNCTION 사용

DELIMITER $$
CREATE FUNCTION test.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 the customer level
RETURN (channel_type);
END$$

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

- SELECT 안에서 호출한다.

Trigger란?

CREATE TRIGGER 명령을 사용
INSERT/DELECT/UPDATE 실행 전후에 특정 작업을 수행하는 것이 가능
- 대상 테이블 지정이 필요
NEW/OLD modifier
- NEW는 INSERT와 UPDATE에서만 사용 가능
- OLD는 DELETE와 UPDATE에서만 사용 가능

CREATE TRIGGER 트리거이름
{BEFORE | AFTER} {INSERT | UPDATE| DELETE }
ON table_name FOR EACH ROW
trigger_body;

내가 원하는 작업들을 자동으로 실행하게 해준다.

기본적으로 특정 테이블을 대상으로 한다.

CREATE의 경우에는 새로운 레코드를 access할 방법이 필요하다.

삭제를 위해서는 old가 필요하다.

update - new(갱신 후 데이터) / old(갱신 전 데이터)

- 중요 테이블의 경우 감사(audit)가 필요하다.

- 레코드에 변경이 생길 때마다 변경 전의 레코드를 저장하는 트리거를 만들어보자.

누가 이 테이블을 추가했을까?

CREATE TABLE test.sujin_name_gender_audit (
name varchar(16),
gender enum('Male', 'Female'),
modified timestamp
);

-- 트리거 정의
CREATE TRIGGER test.before_update_sujin_name_gender
BEFORE UPDATE ON test.sujin_name_gender
FOR EACH ROW
INSERT INTO test.sujin_name_gender_audit
SET name = OLD.name,
gender = OLD.gender,
modified = NOW();

-- 트리거 사용 예
UPDATE test.sujin_name_gender
SET name = 'Sujin'
WHERE name = 'Keeyong2';

SELECT * FROM test.sujin_name_gender_audit;

변경되기 직전에 트리거가 시작된다.

변경전의 값을 넣고, 변경한다.

- DESCRIBE : 테이블의 스키마를 보여준다. 타입과 속성을 보여줌

5. 성능 튜닝 : Explain SQL과 Index 튜닝과 실습

내가 어떤 SQL을 실행을 했는데 너무 오래 걸린다. 왜 그럴까?

내부 과정을 훔쳐볼 수 있다. = Explain

특정 칼럼을 많이 찾는다. 그 키가 PK, FK가 아닌 경우 속도가 많이 늦을 수 있다.

column에 index를 걸어주면 빨리 걸린다.

Explain SQL

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

MySQL이 해당 쿼리를 어떻게 실행할지 Execution Plan을 보여줌
-> 느리게 동작하는 쿼리의 최적화 가능
보통 느린 쿼리의 경우 문제가 되는 테이블에 인덱스를 붙이는 것이 일반적

아직은 이해하기 쉬운 단계는 아니다.

나중에 너무 느리면 사용해봐라.

-- EXPLAIN
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이 별도로 만드는 데이터 구조를 말함

- 컬럼별로 만들어짐

- Primary Key나 Foreign Key로 지정된 컬럼은 기본적으로 Index를 갖게 됨

- 특정 컬럼을 바탕으로 검색을 자주 한다면 Index 생성이 큰 도움이 될 수 있음

Index와 Key는 동의어
Index는...
- SELECT/DELETE/JOIN 명령을 빠르게
- INSERT/UPDATE 명령은 느리게
=> 테이블이 추가되거나 변경되면 Index 자료 구조가 다시 만들어지기 때문에
테이블에 너무 많은 인덱스를 추가하면 인덱스의 로딩으로 인한
오버헤드로 인해 시스템이 전체적으로 느려질 수 있음

- 테이블 크기가 작으면 도움이 안 된다.

INDEX 선언

-- table 만들 때(추천)
CREATE TABLE example (
id INT NOT NULL AUTO_INCREMENT,
index_col VARCHAR(20),
PRIMARY KEY (id),
INDEX index_name (index_col)
);

-- 나중에
ALTER TABLE testalter_tbl ADD INDEX (column1);
ALTER TABLE testalter_tbl ADD UNIQUE (column1); -- unique한 값을 원할 때
ALTER TABLE testalter_tbl ADD FULLTEXT (column1); -- text 검색을 많이 할 때
ALTER TABLE testalter_tbl DROP INDEX (column1) ;
...
CREATE UNIQUE INDEX index_name ON table_name ( column1, column2,...);

6. 다음 스텝과 맺음말

백엔드 개발 핵심 기술 : 관계형 데이터베이스와 SQL

백엔드 개발자 뿐만 아니라 모든 개발 직군이 알아야 하는 기술
포기하지 않고 계속해서 자문자답하고 질문하기
Spring Boot 와 같은 백엔드 개발 프레임워크에서 꼭 필요하다.
- 관계형 데이터베이스는 백엔드에서 빠질 수 없는 컴포넌트임
- 데이터 모델 설계의 중요성을 꼭 기억하자.

관계형 데이터베이스는 귀중한 리소스다!

관계형 데이터베이스는 용량증대에 한계가 존재한다.
정말로 서비스 운영에 필요한 데이터만 저장
- 정보 저장 측면에서는 필요하지만 서비스 운영에 직접적으로 필요 없다면 다른 스토리지를 사용

'2023 활동 - 4학년 > [1월 ~ 4월] sw 아카데미 백엔드 과정' 카테고리의 다른 글

[2023.02.17 / CNU SW 아카데미] 33일차 회고록 (0)	2023.02.17
[2023.02.17 / CNU SW Academy] 객체지향 프로그래밍 4 (0)	2023.02.17
[2023.02.14 / CNU SW 아카데미] 30일차 회고록 (0)	2023.02.14
[2023.02.14 / CNU SW 아카데미] 객체지향 프로그래밍2 (0)	2023.02.14
[2023.02.13 / CNU SW 아카데미] 실리콘밸리에서 날아온 데이터베이스 Day 14 (0)	2023.02.14

노씨의 아이디어 은행본점

[2023.02.14 / CNU SW 아카데미] 실리콘벨리에서 날아온 데이터베이스 D-15