[TIL] 221028 - 데이터베이스 MySQL 고급기능

실리콘 밸리에서 날아온 데이터베이스 - 5. MySQL 고급기능 살펴보기

 

1. 트랜잭션 소개

• 트랜잭션 - Atomic 하게 실행되어야 하는 SQL들을 묶어서 하나의 작업처럼 처리하는 방법

: 테이블 내용을 변경하는 SQL이 연달아 실행되며 마치 하나의 SQL처럼 전부 성공하거나 전부 실패 되어야한다. (COMMIT을 안하고 트랜잭션을 close하면 모두 무효화 → rollback이랑 동일한 효과)

: SELECT에서는 사용할 이유가 없음

: BEGIN/END 혹은 BEGIN/COMMIT 사이에 해당 SQL들을 사용하며 ROLLBACK

atomic하기때문에 동기화 문제를 해결한다.

 

 

• 트랜잭션 커밋 모드 : autocommit
  • autocommit = True : 모든 레코드 작업이 기본적으로 바로 커밋된다. 특정 작업을 트랜잭션으로 묶고 싶다면 BEGIN/END 처리를 해준다.
  • autocommit = False : 명시적으로 COMMIT이 호출될때까지 커밋되지 않는다. COMMIT 전까지 외부에서 접근시 어떤 작업도 반영이 안됨

MySQL Workbench 기본 autocommit 이 true이다.

SHOW VARIAVLES LIKE ‘AUTOCOMMIT’ 으로 확인, SET aoutocommit=0(혹은 1)로 변경가능

 

 

• TRUNCATE(전체 레코드 삭제)는 트랜잭션을 지원하지않는다.

 

---

2. View

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

• SELECT 결과가 테이블로 저장되는 것이 아니라 view가 사용될때마나다 해당 SELECT가 실행됨.
• 가상 테이블이라고 부르기도함
• CREATE OR REPLACE VIEW 뷰이름 AS SELECT _

describe test.keeyoung_session -- 테이블의 필드와 타입을 알려줌

create or replace view test.keeyoung_session as
select s.id, s.user_id, s.created, s.channel
from session s
join channel c on c.id = s.schannel_id

select * from test.keeyoung_session -- 테이블 처럼 확인

 

 

3. Stored Procedure, Trigger

• Stored Procedure
  • MySQL 서버단에 저장되는 SQL 쿼리들
  • view 보다 훨씬 강렬하다
  • CREATE PROCEDURE / DROP PROCEDURE [IF EXIST]
  • 프로그래밍 언어의 함수처럼 인자를 넘기고 리턴값은 레토드들의 집합 (SELECT와 동일)
  • 간단한 분기문, 반복문 가능
  • 디버깅이 힘들고 자주 사용하면 서버 부화가 발생한다.

-- 정의 : in 파라미터
delimiter //
create procedure return_session_detail(IN channelName varchar(64))
begin 
	select *
	from test.keeyong_session
	where channel = channelName
end //
delimiter;

-- 호출
call return_session_detail('Facebook')

--------------------------------------------------------------------

-- 정의 : inout 파라미터
delimiter //
create procedure return_session_detail(IN channelName varchar(64), INOUT totalRecord int)
begin 
	select count(1) into totalRecord -- 변수에 count값 삽입
	from test.keeyong_session
	where channel = channelName
end //
delimiter;

-- 호출
set @facebook_count = 0
call return_session_detail('Facebook',@facebook_count);
select @facebook_count; -- 변수값 출력

 

• Stored Function
  • 값을 하나 리턴해주는 서버쪽 함수
  • SQL 안에서 사용가능. (Stored Procedure와 가장큰 차이점 - 함수처럼 사용할 수 있다.)

 

 

• Trigger
  • CREATE TRIGGER 명령을 사용
  • INSERT/DELETE/UPDATE 전후에 실행 전후에 특정 작업을 수행하는 것 (대상 테이블 지정)
  • → 백업용 테이블만들기 같은 작업 수행
  • NEW(insert, update 에 사용가능)/OLD(delete와 update에 사용가능) modifier

create trigger 트리거이름
{defore | after} {insert | update | delete}
on 테이블이름 for each row
트리거 내용

 

 

4. 성능 튜닝 : Explain SQL과 Index 튜닝

Explain SQL

• 쿼리가 어떻게 수행되는지 내부를 보여주는 SQL 명령, 이를 바탕으로 느리게 동작하는 쿼리의 최적화가 가능해준다.
• 보통 느린 쿼리의 경우 문제가 되는 테이블에 인덱스를 붙이는 것이 일반적이다.

 

Index

• 테이블에서 특정 찾기 작업을 빠르게 수행하기 위해 MySQL이 별도로 만드는 데이터 구조
• 컬럼별로 지정이 가능하며 Primary key나 Foreign Key로 지정된 컬럼은 기본적으로 index를 가진다.
• 테이블에 너무 많은 인덱스를 추가하면 서버에 오버헤드가 발생할 수 있다.
• 검색은 빠르지만 INSERT/UPDATE 작업은 더 느리다.
• 테이블 생성할때 생성하기 (후에 추가도 가능 - ALTER TABLE 혹은 CREATE INDEX)

 

 

 

출처 - 한기용 강사님 : 실리콘밸리에서 날아온 데이터 베이스

 

 

 

추천 링크

https://medium.com/watcha/쿼리-최적화-첫걸음-보다-빠른-쿼리를-위한-7가지-체크-리스트-bafec9d2c073

✅ 쿼리 최적화 첫걸음 — 보다 빠른 쿼리를 위한 7가지 체크 리스트