์ค๋ฆฌ์ฝ ๋ฐธ๋ฆฌ์์ ๋ ์์จ ๋ฐ์ดํฐ๋ฒ ์ด์ค - 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)
์ถ์ฒ - ํ๊ธฐ์ฉ ๊ฐ์ฌ๋ : ์ค๋ฆฌ์ฝ๋ฐธ๋ฆฌ์์ ๋ ์์จ ๋ฐ์ดํฐ ๋ฒ ์ด์ค
์ถ์ฒ ๋งํฌ
โ ์ฟผ๋ฆฌ ์ต์ ํ ์ฒซ๊ฑธ์โ—โ๋ณด๋ค ๋น ๋ฅธ ์ฟผ๋ฆฌ๋ฅผ ์ํ 7๊ฐ์ง ์ฒดํฌ ๋ฆฌ์คํธ
DB์ ๋ํ ๋ฐ์ดํฐ ๋ถ์๊ฐ์ ์ํฐ์ผ
medium.com
'Back-end ๋ฐ๋ธ์ฝ์ค > week 01 - 02 TIL (java, DB)' ์นดํ ๊ณ ๋ฆฌ์ ๋ค๋ฅธ ๊ธ
[TIL] 221027 - ๋ฐ์ดํฐ๋ฒ ์ด์ค SQL : JOIN (0) | 2022.10.28 |
---|---|
[TIL] 221026 - ๋ฐ์ดํฐ๋ฒ ์ด์ค SQL : SELECT, GROUP BY (0) | 2022.10.26 |
[TIL] 221025 - MySQL ์ฌ์ฉํ๊ธฐ (0) | 2022.10.25 |
[TIL] 221024 - ๋ฐ์ดํฐ๋ฒ ์ด์ค ์์คํ ์๊ฐ (0) | 2022.10.24 |
[TIL] 221021 - Java ์ค์ต ํ๋ก์ ํธ : ์ซ์์ผ๊ตฌ ๊ฒ์ (0) | 2022.10.21 |
๋๊ธ