๐Ÿ“š ์ฑ…/์—…๋ฌด์— ๋ฐ”๋กœ ์“ฐ๋Š” SQL ํŠœ๋‹

์—…๋ฌด์— ๋ฐ”๋กœ ์“ฐ๋Š” SQL ํŠœ๋‹ 1์žฅ MySQL๊ณผ MariaDB ๊ฐœ์š”

MyeongDev 2024. 11. 3. 01:07
728x90
๋ฐ˜์‘ํ˜•

1์žฅ MySQL๊ณผ MariaDB ๊ฐœ์š”

์˜ค๋ผํด๊ณผ MySQL๋น„๊ต(MariaDB ํฌํ•จ)

MySQL(MariaDB)

  • ์Šคํ† ๋ฆฌ์ง€ ๊ตฌ์กฐ
    • ๋ฌผ๋ฆฌ์ ์ธ DB ์„œ๋ฒ„๋งˆ๋‹ค ๋…๋ฆฝ์ ์œผ๋กœ ์Šคํ† ๋ฆฌ์ง€๋ฅผ ํ• ๋‹น(Shared Nothing)
    • ์ด์ค‘ํ™”๊ตฌ์กฐ ๋งˆ์Šคํ„ฐ-์Šฌ๋ ˆ์ด๋ธŒ๊ตฌ์กฐ๊ฐ€ ๋Œ€๋ถ€๋ถ„.
    • ๋ฌผ๋ฆฌ์ ์œผ๋กœ ์—ฌ๋Ÿฌ๋Œ€์˜ DB์„œ๋ฒ„์— ์ ‘์†ํ•˜๋”๋ผ๋„ ๋™์ผํ•œ ๊ตฌ๋ฌธ์ด ์ฒ˜๋ฆฌ๋˜์ง€ ์•Š์„ ์ˆ˜ ์žˆ์œผ๋ฉฐ, DB ์„œ๋ฒ„ ๋งˆ๋‹ค ๊ฐ์ž์˜ ์—ญํ• ์ด ๋ถ€์—ฌ๋  ์ˆ˜ ์žˆ๋‹ค.
  • ์กฐ์ธ ์•Œ๊ณ ๋ฆฌ์ฆ˜
    • ๋Œ€๋ถ€๋ถ„ ์ค‘์ฒฉ ๋ฃจํ”„ ์กฐ์ธ(Nested Loop Join)
    • ์ตœ๊ทผ 8.0.18 ๋ฒ„์ „ ๋ถ€ํ„ฐ ์ œ์•ฝ์ ์œผ๋กœ ํ•ด์‰ฌ ์กฐ์ธ ์ œ๊ณต
  • ๋ฉ”๋ชจ๋ฆฌ ์‚ฌ์šฉ๋ฅ 
    • ์‚ฌ์–‘์ด ๋‚ฎ์€ ์ปดํ“จํ„ฐ ํ™˜๊ฒฝ์—์„œ ์‚ฌ์šฉ ๊ฐ€๋Šฅ.
    • ์•ฝ 1MB ๋ฉ”๋ชจ๋ฆฌ ํ™˜๊ฒฝ์—์„œ๋„ ์‚ฌ์šฉ ๊ฐ€๋Šฅ.
    • ์˜ค๋ฒ„ํ—ค๋“œ ์ž‘์Œ.

Oracle

  • ์Šคํ† ๋ฆฌ์ง€ ๊ตฌ์กฐ
    • ํ†ตํ•ฉ๋œ ์Šคํ† ๋ฆฌ์ง€ ํ•˜๋‚˜๋ฅผ ๊ณต์œ (Shared Everything)ํ•˜์—ฌ ์‚ฌ์šฉ
    • ๊ณต์œ  ์Šคํ† ๋ฆฌ์ง€๋ฅผ ์‚ฌ์šฉํ•˜๋ฏ€๋กœ ์–ด๋А DB ์„œ๋ฒ„์— ์ ‘์†ํ•˜์—ฌ SQL๋ฌธ์„ ์ˆ˜ํ–‰ํ•˜๋”๋ผ๋„ ๊ฐ™์€ ๊ฒฐ๊ณผ๋ฅผ ์ถœ๋ ฅ & ์ฒ˜๋ฆฌ
  • ์กฐ์ธ ์•Œ๊ณ ๋ฆฌ์ฆ˜
    • ์ค‘์ฒฉ ๋ฃจํ”„ ์กฐ์ธ(Nested Loop Join), ์ •๋ ฌ ๋ณ‘ํ•ฉ ์กฐ์ธ(Sort Merge Join), ํ•ด์‰ฌ ์กฐ์ธ(Hash Join)
  • ๋ฉ”๋ชจ๋ฆฌ ์‚ฌ์šฉ๋ฅ 
    • ๋น„๊ต์  ๋†’์€ ์‚ฌ์–‘ ์ˆ˜๋ฐฑMB์˜ ๋ฉ”๋ชจ๋ฆฌ ์‚ฌ์šฉ๋Ÿ‰์ด ํ•„์š”.

๊ตฌ๋ฌธ ์ฐจ์ด

Null ๋Œ€์ฒด

-- MySQL
IFNULL(COLUMN_NAME, '๋Œ€์ฒด๊ฐ’')

-- Oracle
NVL(COLUMN_NAME, 'N/A')

ํŽ˜์ด์ง• ์ฒ˜๋ฆฌ

-- MySQL
LIMIT ์ˆซ์ž

-- Oracle
ROWNUM <= ์ˆซ์ž

ํ˜„์žฌ ๋‚ ์งœ

-- MySQL
NOW()

-- Oracle
SYSDATE

์กฐ๊ฑด๋ฌธ

-- MySQL
IF (์กฐ๊ฑด์‹, '์ฐธ๊ฐ’', '๊ฑฐ์ง“๊ฐ’')

-- Oracle
DECODE(COLUMN_NAME, '๊ฐ’', '์ฐธ๊ฐ’', '๊ฑฐ์ง“๊ฐ’')

-- ๊ณตํ†ต
CASE
	WHEN ์กฐ๊ฑด์‹
	THEN ๊ฒฐ๊ณผ๊ฐ’
	...
END

๋‚ ์งœ ํ˜•์‹

-- MySQL
DATE_FORMAT(๋‚ ์งœ Column, '%Y-%m-%d %H%i%s')

-- Oracle
TO_CHAR(๋‚ ์งœ Column, 'YYYYMMDD HH24MISS')

์ž๋™ ์ฆ๊ฐ“๊ฐ’

์˜ค๋ผํด์˜ ๊ฒฝ์šฐ Sequence ๋ผ๋Š” Object๋ฅผ ํ™œ์šฉํ•œ๋‹ค.

๋จผ์ € CREATE SEQUENCE ๋ฌธ์œผ๋กœ ์‹œํ€€์Šค ์˜ค๋ธŒ์ ํŠธ๋ฅผ ์ƒ์„ฑํ•œ ๋’ค, ํ•ด๋‹น ์‹œํ€€์Šค๋ช…์œผ๋กœ ํ•จ์ˆ˜๋ฅผ ํ˜ธ์ถœํ•˜์—ฌ ์‹ ๊ทœ ์ˆซ์ž๋ฅผ์ฑ„๋ฒˆํ•˜๋Š” ๋ฐฉ์‹์ด๋‹ค.

์ด๋•Œ SELECT ์‹œํ€€์Šค๋ช….nextval FROM dual ๊ตฌ๋ฌธ์œผ๋กœ ์‹ ๊ทœ ๋ฐ์ดํ„ฐ์˜ ์‹œํ€€์Šค ์ˆซ์ž๋ฅผ ๊ฐ€์ ธ์˜จ๋‹ค.

-- MySQL
AUTO_INCREMENT

CREATE TABLE(
	seq INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	title VARCHAR(20) NOT NULL
)
-- Oracle
CREATE SEQUENCE [์‹œํ€€์Šค๋ช…]
INCREMENT BY [์ฆ๊ฐ์ˆซ์ž]
START WITH [์‹œ์ž‘์ˆซ์ž]
NOMINVALUE OR MINVALUE [์ตœ์†Ÿ๊ฐ’]
NOMAXVALUE OR MAXVALUE [์ตœ๋Œ“๊ฐ’]
CYCLE OR NOCYCLE
CACHE OR NOCACHE

CREATE SEQUENCE ORACLE_SEQ_SAMPLE
INCREMENT BY 1
START WITH 1
MINVALUE 1
MAXVALUE 999999999
CYCLE
CACHE;

-- ๋‹ค์Œ ๊ฐ’ ์ฑ„๋ฒˆ ๋ฌธ๋ฒ•
SELECT ์‹œํ€€์Šค๋ช….NEXTVAL FROM dual;

SELECT ORACLE_SEQ_SAMPLE.NEXTVAL FROM DUAL;

๋ฌธ์ž ๊ฒฐํ•ฉ

-- MySQL
CONCAT(์—ด๊ฐ’ ๋˜๋Š” ๋ฌธ์ž์—ด, ์—ด๊ฐ’ ๋˜๋Š” ๋ฌธ์ž์—ด)

-- Oracle
1. ์—ด๊ฐ’ ๋˜๋Š” ๋ฌธ์ž์—ด || ์—ด๊ฐ’ ๋˜๋Š” ๋ฌธ์ž์—ด
2. CONCAT(์—ด๊ฐ’ ๋˜๋Š” ๋ฌธ์ž์—ด, ์—ด๊ฐ’ ๋˜๋Š” ๋ฌธ์ž์—ด)

๋ฌธ์ž ์ถ”์ถœ

-- MySQL
SUBSTRING(์—ด๊ฐ’ ๋˜๋Š” ๋ฌธ์ž์—ด, ์‹œ์ž‘ ์œ„์น˜, ์ถ”์ถœํ•˜๋ ค๋Š” ๋ฌธ์ž ๊ฐœ์ˆ˜)

-- Oracle
SUBSTR(์—ด๊ฐ’ ๋˜๋Š” ๋ฌธ์ž์—ด, ์‹œ์ž‘ ์œ„์น˜, ์ถ”์ถœํ•˜๋ ค๋Š” ๋ฌธ์ž ๊ฐœ์ˆ˜)
728x90
๋ฐ˜์‘ํ˜•