๋ณธ๋ฌธ ๋ฐ”๋กœ๊ฐ€๊ธฐ
Back-end ๋ฐ๋ธŒ์ฝ”์Šค/week 01 - 02 TIL (java, DB)

[TIL] 221028 - ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค MySQL ๊ณ ๊ธ‰๊ธฐ๋Šฅ

by young-ji 2022. 10. 28.

์‹ค๋ฆฌ์ฝ˜ ๋ฐธ๋ฆฌ์—์„œ ๋‚ ์•„์˜จ ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค - 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๊ฐ€์ง€ ์ฒดํฌ ๋ฆฌ์ŠคํŠธ

DB์— ๋Œ€ํ•œ ๋ฐ์ดํ„ฐ ๋ถ„์„๊ฐ€์˜ ์—ํ‹ฐ์ผ“

medium.com

๋Œ“๊ธ€