명령은 네 종류뿐입니다

표준 SQL과 방언

SQL에는 ANSI/ISO 표준이 있지만, 완벽히 지키는 DB는 없습니다. 각 DB는 표준을 대부분 따르면서 자기만의 문법(방언, dialect)을 얹습니다. 이 가이드는 표준에 가까운 문법을 기본으로 쓰고, 차이가 나는 지점마다 네 DB를 나란히 보여 줍니다.

SELECT / FROM / WHERE
JOIN / GROUP BY / HAVING
ORDER BY / CASE
CREATE TABLE / 제약조건
트랜잭션 (COMMIT·ROLLBACK)
윈도우 함수 · CTE

페이징 (LIMIT vs FETCH)
문자열 결합 (|| vs CONCAT)
날짜 함수 이름
자동 증가 컬럼
UPSERT 문법
데이터 타입 이름

-- 전체 컬럼, 전체 행
SELECT * FROM member;

-- 필요한 컬럼만 (실무에서는 이쪽을 씁니다)
SELECT id, name, city FROM member;

-- 별칭(alias) — 결과에 보일 이름을 바꿉니다
SELECT id     AS 회원번호,
       name   AS 이름,
       city   AS 지역
FROM   member;

-- 계산해서 보여주기
SELECT name, price, price * 0.1 AS 부가세
FROM   product;

-- 중복 제거
SELECT DISTINCT city FROM member;
SELECT DISTINCT city, grade FROM member;   -- 두 컬럼 조합 기준 중복 제거

별칭에 공백이나 한글을 쓰려면

-- 표준 · PostgreSQL · Oracle · SQLite : 큰따옴표
SELECT name AS "회원 이름" FROM member;

-- MySQL : 백틱도 됩니다 (큰따옴표는 설정에 따라 문자열로 해석될 수 있음)
SELECT name AS `회원 이름` FROM member;

주석

-- 한 줄 주석 (하이픈 두 개 + 공백)

/* 여러 줄 주석
   이렇게 감쌉니다 */

SELECT name  -- 줄 끝에도 쓸 수 있습니다
FROM   member;

-- 비교
SELECT * FROM product WHERE price >= 50000;
SELECT * FROM member  WHERE city = '서울';
SELECT * FROM member  WHERE city <> '서울';     -- != 도 대부분 됩니다

-- 범위
SELECT * FROM product WHERE price BETWEEN 20000 AND 50000;   -- 양쪽 끝 포함

-- 목록
SELECT * FROM member WHERE city IN ('서울','부산');
SELECT * FROM member WHERE grade NOT IN ('BASIC');

-- 패턴 (문자열)
SELECT * FROM product WHERE name LIKE '%마우스%';   -- 포함
SELECT * FROM product WHERE name LIKE 'USB%';      -- 로 시작
SELECT * FROM member  WHERE email LIKE '%@example.com';

-- 빈 값
SELECT * FROM member WHERE email IS NULL;
SELECT * FROM member WHERE email IS NOT NULL;

-- 여러 조건 조합
SELECT * FROM product
WHERE  category = '주변기기'
  AND  price < 40000
  AND  stock > 0;

AND · OR 를 섞을 때는 괄호를 치세요

SELECT * FROM member
WHERE  city = '서울'
   OR  city = '부산'
  AND  grade = 'GOLD';

-- AND 가 먼저 계산되므로 실제로는
-- 서울 전부  +  (부산 AND GOLD)

SELECT * FROM member
WHERE  (city = '서울' OR city = '부산')
  AND  grade = 'GOLD';

-- 서울이거나 부산이면서, GOLD 인 사람

LIKE 의 함정

대소문자 구분

-- 기본은 구분함
WHERE name = 'Kim'      -- 'kim' 안 걸림
WHERE name ILIKE 'kim%' -- 대소문자 무시 LIKE
WHERE LOWER(name) = 'kim'

-- 기본 콜레이션이 _ci 라 구분 안 함
WHERE name = 'kim'      -- 'Kim' 도 걸림
-- 구분하려면
WHERE name = 'Kim' COLLATE utf8mb4_bin

-- 기본은 구분함
WHERE UPPER(name) = 'KIM'
-- 세션 단위로 끄기 (10gR2+)
ALTER SESSION SET
  NLS_COMP=LINGUISTIC NLS_SORT=BINARY_CI;

-- ASCII 만 대소문자 무시 지원
WHERE name = 'kim' COLLATE NOCASE
-- 한글·유니코드는 구분함

-- 오름차순 (기본값, ASC 는 생략 가능)
SELECT * FROM product ORDER BY price;

-- 내림차순
SELECT * FROM product ORDER BY price DESC;

-- 여러 기준 — 앞의 것이 같을 때 뒤의 것으로
SELECT * FROM member ORDER BY city, joined_at DESC;

-- 별칭으로 정렬 (ORDER BY 는 SELECT 다음이라 가능합니다)
SELECT name, price * 0.9 AS 할인가
FROM   product
ORDER  BY 할인가 DESC;

NULL 은 어디에 놓일까

상위 N건 — DB마다 문법이 다릅니다

SELECT * FROM product
ORDER BY price DESC
LIMIT 3 OFFSET 0;

SELECT * FROM product
ORDER BY price DESC
LIMIT 3;          -- LIMIT 0, 3 도 동일

SELECT * FROM product
ORDER BY price DESC
FETCH FIRST 3 ROWS ONLY;

-- 11g 이하는 ROWNUM 을 씁니다 (o05 참고)

SELECT * FROM product
ORDER BY price DESC
LIMIT 3;

모르는 값끼리는 비교할 수 없습니다

-- ✗ 절대 참이 되지 않습니다 (결과 0행)
SELECT * FROM member WHERE email = NULL;

-- ✓ 이렇게 씁니다
SELECT * FROM member WHERE email IS NULL;
SELECT * FROM member WHERE email IS NOT NULL;

계산에 섞이면 전부 NULL이 됩니다

SELECT 1000 + NULL;        -- NULL  (0 이 아닙니다)
SELECT '주문-' || NULL;     -- NULL  (Oracle 은 예외적으로 '주문-')

-- 그래서 이렇게 방어합니다
SELECT COALESCE(email, '(이메일 없음)') FROM member;   -- 표준 · 어디서나
SELECT NVL(email, '(없음)')  FROM member;              -- Oracle
SELECT IFNULL(email, '(없음)') FROM member;            -- MySQL · SQLite

집계 함수는 NULL을 빼고 셉니다

-- 회원 5명 중 email 이 있는 사람은 4명 (정바람만 NULL)
SELECT COUNT(*)     AS 전체,       -- 5   ← 행의 개수 (NULL 이든 아니든)
       COUNT(email) AS 이메일있음  -- 4   ← NULL 이 아닌 값의 개수
FROM   member;

-- 평균도 마찬가지 — NULL 인 행은 분모에서도 빠집니다
SELECT COUNT(*)   AS 상품수,      -- 5
       AVG(price) AS 평균가        -- price 가 NULL 인 상품이 있다면 그 행은 제외
FROM   product;

가장 악명 높은 함정 — NOT IN 과 NULL

-- 서브쿼리 결과에 NULL 이 하나라도 있으면
SELECT * FROM member
WHERE  id NOT IN (SELECT member_id FROM orders);

-- member_id 에 NULL 이 섞이는 순간
-- 전체 결과가 0행이 됩니다

SELECT * FROM member m
WHERE  NOT EXISTS (
  SELECT 1 FROM orders o
  WHERE  o.member_id = m.id
);

-- NULL 이 있어도 정상 동작하고
-- 보통 더 빠릅니다

빈 문자열과 NULL은 다릅니다 — 오라클만 빼고

숫자

문자

날짜 · 시간

그 밖에 알아 두면 좋은 것

• BOOLEAN — PostgreSQL·SQLite는 지원. Oracle은 23ai부터 지원(이전에는 NUMBER(1)). MySQL의 BOOLEAN 은 사실 TINYINT(1) 의 별칭입니다.
• UUID — PostgreSQL은 전용 타입, MySQL은 BINARY(16) 권장(문자열로 넣으면 인덱스가 커집니다).
• JSON / JSONB — 스키마가 자주 바뀌는 데이터용. PostgreSQL의 JSONB가 가장 강력합니다 → 🐘 p07.
• ENUM — 값 목록을 타입으로 강제. 편하지만 값 추가 시 DDL이 필요해 실무에서는 코드 테이블을 선호합니다.

-- INSERT : 컬럼을 명시하는 습관을 들이세요
INSERT INTO member (id, name, email, grade, city, joined_at)
VALUES (6, '한바다', 'han@example.com', 'BASIC', '광주', '2026-08-01');

-- 여러 건 한 번에 (Oracle 은 23ai 부터 · 그 전은 INSERT ALL)
INSERT INTO member (id, name, grade, city, joined_at) VALUES
 (7, '오하늘', 'BASIC', '서울', '2026-08-02'),
 (8, '유들판', 'BASIC', '대구', '2026-08-03');

-- 조회 결과를 그대로 넣기 (백업 테이블 만들 때 유용)
INSERT INTO member_archive (id, name, city)
SELECT id, name, city FROM member WHERE joined_at < '2025-01-01';

-- UPDATE : WHERE 를 반드시
UPDATE member
SET    grade = 'GOLD'
WHERE  id = 3;

-- 여러 컬럼 동시에
UPDATE product
SET    price = price * 1.1,
       stock = stock - 1
WHERE  id = 10;

-- 다른 테이블 값을 참고해 갱신 (문법이 DB마다 다릅니다)
-- PostgreSQL
UPDATE orders o
SET    status = 'VIP'
FROM   member m
WHERE  m.id = o.member_id AND m.grade = 'GOLD';

-- MySQL
UPDATE orders o
JOIN   member m ON m.id = o.member_id
SET    o.status = 'VIP'
WHERE  m.grade = 'GOLD';

-- DELETE
DELETE FROM order_item WHERE order_id = 1003;
DELETE FROM orders     WHERE id = 1003;

-- 전부 지우기 (되돌릴 수 있음)
DELETE FROM orders;

-- 표를 비우기 (빠르지만 대부분 되돌릴 수 없음)
TRUNCATE TABLE orders;

-- ③ 의 실제 모습
BEGIN;

UPDATE member SET grade = 'GOLD' WHERE id = 3;
-- UPDATE 1     ← 1행. 예상과 같으면

COMMIT;
-- 예상과 다르면 ROLLBACK;

CREATE TABLE coupon (
    id          INTEGER       PRIMARY KEY,              -- 유일 + NOT NULL
    code        VARCHAR(20)   NOT NULL UNIQUE,          -- 중복 금지
    member_id   INTEGER       NOT NULL,
    discount    DECIMAL(5,2)  NOT NULL
                CHECK (discount > 0 AND discount <= 50),-- 값 범위 강제
    used        CHAR(1)       NOT NULL DEFAULT 'N',
    expires_at  DATE          NOT NULL,
    CONSTRAINT fk_coupon_member                          -- 이름을 붙이면 에러 추적이 쉽습니다
        FOREIGN KEY (member_id) REFERENCES member(id)
        ON DELETE CASCADE
);

외래키의 ON DELETE 옵션

자동 증가 컬럼 — 4-DB 대조

id INT GENERATED ALWAYS AS IDENTITY
                 PRIMARY KEY
-- 구버전 관례: id SERIAL PRIMARY KEY

id INT AUTO_INCREMENT PRIMARY KEY

id NUMBER GENERATED ALWAYS AS IDENTITY
                    PRIMARY KEY
-- 11g 이하: 시퀀스 + 트리거 (o08)

id INTEGER PRIMARY KEY
-- 이것만으로 자동 증가합니다
-- AUTOINCREMENT 는 대부분 불필요 (s05)

나중에 구조 바꾸기 (ALTER)

ALTER TABLE member ADD COLUMN phone VARCHAR(20);
ALTER TABLE member ALTER COLUMN phone TYPE VARCHAR(30);   -- PostgreSQL
ALTER TABLE member MODIFY phone VARCHAR(30);              -- MySQL · Oracle
ALTER TABLE member DROP COLUMN phone;

ALTER TABLE orders ADD CONSTRAINT chk_status
      CHECK (status IN ('PAID','SHIPPED','CANCELED'));

-- INNER JOIN : 양쪽에 다 있는 것만 (JOIN 만 써도 INNER 입니다)
SELECT m.name, o.id AS order_no
FROM   member m
JOIN   orders o ON o.member_id = m.id;

-- LEFT JOIN : 왼쪽은 전부 (실무에서 가장 많이 씁니다)
SELECT m.name, o.id AS order_no
FROM   member m
LEFT JOIN orders o ON o.member_id = m.id;

-- 주문이 한 번도 없는 회원 찾기 — LEFT JOIN 의 대표 활용
SELECT m.name
FROM   member m
LEFT JOIN orders o ON o.member_id = m.id
WHERE  o.id IS NULL;

-- 세 개 이상 붙이기
SELECT m.name, p.name AS product, oi.qty
FROM   member m
JOIN   orders o      ON o.member_id  = m.id
JOIN   order_item oi ON oi.order_id  = o.id
JOIN   product p     ON p.id         = oi.product_id
WHERE  o.status = 'PAID';

JOIN 하면 행이 늘어납니다

회원 1명에게 주문이 3건 있으면, 조인 결과에서 그 회원은 3번 나옵니다. 그래서 조인 후 SUM() 을 하면 값이 부풀 수 있습니다 — 실무에서 가장 흔한 집계 오류입니다.

-- 주문이 여러 건이면 회원 정보가 반복되어
-- 포인트가 주문 수만큼 더해집니다
SELECT m.name,
       SUM(m.point) AS 포인트,   -- 부풀어 오름!
       SUM(oi.qty)  AS 수량
FROM member m
JOIN orders o      ON o.member_id = m.id
JOIN order_item oi ON oi.order_id = o.id
GROUP BY m.name;

SELECT m.name, m.point, t.수량
FROM   member m
JOIN  (SELECT o.member_id, SUM(oi.qty) AS 수량
       FROM   orders o
       JOIN   order_item oi ON oi.order_id = o.id
       GROUP  BY o.member_id) t
  ON   t.member_id = m.id;

집계 함수 다섯 개

-- 전체 집계 (GROUP BY 없이)
SELECT COUNT(*) AS 상품수, AVG(price) AS 평균가, MAX(price) AS 최고가
FROM   product;

-- 카테고리별로 묶어서
SELECT category,
       COUNT(*)   AS 상품수,
       AVG(price) AS 평균가,
       SUM(stock) AS 총재고
FROM   product
GROUP  BY category
ORDER  BY 평균가 DESC;

-- 두 컬럼으로 묶기
SELECT city, grade, COUNT(*) AS 인원
FROM   member
GROUP  BY city, grade
ORDER  BY city, grade;

-- 조인 + 집계 — 회원별 총 구매액
SELECT m.name,
       COUNT(DISTINCT o.id)          AS 주문건수,
       SUM(oi.qty * oi.unit_price)   AS 총구매액
FROM   member m
JOIN   orders o      ON o.member_id = m.id
JOIN   order_item oi ON oi.order_id = o.id
WHERE  o.status <> 'CANCELED'
GROUP  BY m.name
ORDER  BY 총구매액 DESC;

COUNT(*) vs COUNT(컬럼) vs COUNT(DISTINCT)

SELECT COUNT(*)              AS 전체행,      -- 5
       COUNT(email)          AS 이메일보유,  -- 4  (NULL 제외)
       COUNT(DISTINCT city)  AS 지역수       -- 3  (서울·부산·대전)
FROM   member;

-- "취소되지 않은 주문만 대상으로, 총 구매액 30만원 넘는 회원"
SELECT m.name, SUM(oi.qty * oi.unit_price) AS 총구매액
FROM   member m
JOIN   orders o      ON o.member_id = m.id
JOIN   order_item oi ON oi.order_id = o.id
WHERE  o.status <> 'CANCELED'          -- ← 행 단위 조건 (묶기 전)
GROUP  BY m.name
HAVING SUM(oi.qty * oi.unit_price) > 300000   -- ← 그룹 조건 (묶은 뒤)
ORDER  BY 총구매액 DESC;

SELECT category, COUNT(*)
FROM   product
WHERE  COUNT(*) > 2      -- ✗
GROUP  BY category;

SELECT category, COUNT(*)
FROM   product
GROUP  BY category
HAVING COUNT(*) > 2;     -- ✓

① WHERE 절 — 조건으로

-- 평균 가격보다 비싼 상품
SELECT name, price
FROM   product
WHERE  price > (SELECT AVG(price) FROM product);

-- 주문한 적 있는 회원 (IN)
SELECT name FROM member
WHERE  id IN (SELECT member_id FROM orders);

-- 같은 일을 EXISTS 로 — 보통 더 빠르고 NULL 에 안전합니다
SELECT name FROM member m
WHERE  EXISTS (SELECT 1 FROM orders o WHERE o.member_id = m.id);

② FROM 절 — 임시 표로 (인라인 뷰)

-- 회원별 구매액을 먼저 구하고, 그중 상위만
SELECT *
FROM  (SELECT m.id, m.name, SUM(oi.qty * oi.unit_price) AS 구매액
       FROM   member m
       JOIN   orders o      ON o.member_id = m.id
       JOIN   order_item oi ON oi.order_id = o.id
       GROUP  BY m.id, m.name) t
WHERE  t.구매액 > 200000
ORDER  BY t.구매액 DESC;

③ SELECT 절 — 값 하나로 (스칼라 서브쿼리)

SELECT m.name,
       (SELECT COUNT(*) FROM orders o WHERE o.member_id = m.id) AS 주문수
FROM   member m;

상관 서브쿼리 — 바깥 값을 참조하는 것

-- 카테고리 안에서 평균보다 비싼 상품
SELECT p.name, p.category, p.price
FROM   product p
WHERE  p.price > (SELECT AVG(p2.price)
                  FROM   product p2
                  WHERE  p2.category = p.category);   -- ← 바깥 p 를 참조

-- 같은 일을 윈도우 함수로 (보통 더 빠릅니다 · e01 참고)
SELECT name, category, price
FROM  (SELECT name, category, price,
              AVG(price) OVER (PARTITION BY category) AS 카테고리평균
       FROM   product) t
WHERE  price > 카테고리평균;

-- UNION : 합치고 중복 제거 (정렬 비용이 듭니다)
SELECT name FROM member WHERE city = '서울'
UNION
SELECT name FROM member WHERE grade = 'GOLD';

-- UNION ALL : 합치기만 (중복 제거 안 함 · 훨씬 빠름)
SELECT id, name, '2026-01' AS 월 FROM sales_202601
UNION ALL
SELECT id, name, '2026-02'       FROM sales_202602;

-- INTERSECT : 양쪽 모두에 있는 것
SELECT name FROM member WHERE city = '서울'
INTERSECT
SELECT name FROM member WHERE grade = 'GOLD';

-- EXCEPT : 앞에는 있고 뒤에는 없는 것 (Oracle 은 MINUS)
SELECT id FROM member
EXCEPT
SELECT member_id FROM orders;

실무 활용 — 데이터 대사

-- 마이그레이션 후 "옛 시스템에만 있는 회원" 찾기
SELECT id FROM legacy_member
EXCEPT
SELECT id FROM member;

-- 양쪽이 완전히 같은지 한 번에 확인 (두 방향 모두 0행이어야 함)
(SELECT * FROM a EXCEPT SELECT * FROM b)
UNION ALL
(SELECT * FROM b EXCEPT SELECT * FROM a);

문자열

날짜

숫자

SELECT ROUND(12345.678, 2)   AS 반올림,   -- 12345.68
       TRUNC(12345.678, 2)   AS 버림,     -- 12345.67  (MySQL 은 TRUNCATE)
       CEIL(12345.1)         AS 올림,     -- 12346
       FLOOR(12345.9)        AS 내림,     -- 12345
       ABS(-100)             AS 절댓값,
       MOD(10, 3)            AS 나머지,   -- 1
       POWER(2, 10)          AS 거듭제곱  -- 1024
FROM   dual;   -- Oracle 만 FROM dual, 나머지는 FROM 절 생략 가능

-- 조건형 CASE (가장 많이 씁니다)
SELECT name, price,
       CASE WHEN price >= 200000 THEN '고가'
            WHEN price >=  50000 THEN '중가'
            ELSE                      '저가'
       END AS 가격대
FROM   product;

-- 값 비교형 CASE (짧게 쓸 때)
SELECT name,
       CASE grade
            WHEN 'GOLD'   THEN '10% 할인'
            WHEN 'SILVER' THEN '5% 할인'
            ELSE               '할인 없음'
       END AS 혜택
FROM   member;

CASE + 집계 = 조건부 집계 (아주 유용합니다)

-- 한 번의 스캔으로 여러 조건의 개수를 동시에 셉니다
SELECT COUNT(*)                                            AS 전체주문,
       SUM(CASE WHEN status = 'PAID'     THEN 1 ELSE 0 END) AS 결제완료,
       SUM(CASE WHEN status = 'SHIPPED'  THEN 1 ELSE 0 END) AS 배송중,
       SUM(CASE WHEN status = 'CANCELED' THEN 1 ELSE 0 END) AS 취소
FROM   orders;

-- 표준 SQL 의 FILTER 절 (PostgreSQL·SQLite 지원 · 더 읽기 좋습니다)
SELECT COUNT(*)                             AS 전체주문,
       COUNT(*) FILTER (WHERE status='PAID') AS 결제완료,
       COUNT(*) FILTER (WHERE status='CANCELED') AS 취소
FROM   orders;

NULL 처리 함수

-- 0으로 나누기 방지 — NULLIF 의 대표 활용
SELECT name,
       ROUND(sold * 100.0 / NULLIF(stock + sold, 0), 1) AS 판매율
FROM   product;
-- 분모가 0이면 NULL 이 되어 에러 대신 빈 값이 나옵니다

CREATE VIEW v_member_sales AS
SELECT m.id, m.name, m.grade,
       COUNT(DISTINCT o.id)        AS 주문수,
       COALESCE(SUM(oi.qty * oi.unit_price), 0) AS 총구매액
FROM   member m
LEFT JOIN orders o      ON o.member_id = m.id AND o.status <> 'CANCELED'
LEFT JOIN order_item oi ON oi.order_id = o.id
GROUP  BY m.id, m.name, m.grade;

-- 이제 테이블처럼 씁니다
SELECT * FROM v_member_sales WHERE 총구매액 > 300000;

DROP VIEW v_member_sales;

뷰를 쓰는 세 가지 이유

• 복잡함 감추기 — 다섯 개 테이블 조인을 팀원이 매번 쓰지 않아도 됩니다
• 권한 통제 — 원본 테이블 접근은 막고, 필요한 컬럼만 담은 뷰만 열어 줍니다 (급여 테이블에서 주민번호를 뺀 뷰 등)
• 호환 유지 — 테이블 구조를 바꿔도 뷰가 옛 모양을 유지해 주면 앱을 안 고쳐도 됩니다

구체화 뷰 (Materialized View) — 결과를 저장해 두는 뷰

-- PostgreSQL · Oracle 지원 (MySQL·SQLite 는 없음 → 집계 테이블로 직접 구현)
CREATE MATERIALIZED VIEW mv_daily_sales AS
SELECT ordered_at, SUM(oi.qty * oi.unit_price) AS 매출
FROM   orders o JOIN order_item oi ON oi.order_id = o.id
GROUP  BY ordered_at;

-- 데이터는 갱신해 줘야 최신이 됩니다
REFRESH MATERIALIZED VIEW mv_daily_sales;                    -- PostgreSQL
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_daily_sales;       -- 잠금 없이 (유니크 인덱스 필요)

임시 테이블

-- 세션이 끝나면 자동으로 사라집니다
CREATE TEMPORARY TABLE tmp_target AS
SELECT id FROM member WHERE grade = 'GOLD';       -- PostgreSQL · MySQL · SQLite

-- Oracle 은 구조를 미리 만들고 데이터만 세션별로 격리합니다
CREATE GLOBAL TEMPORARY TABLE tmp_target (id NUMBER)
ON COMMIT PRESERVE ROWS;

BEGIN;                                   -- 트랜잭션 시작 (Oracle 은 자동 시작)

UPDATE account SET balance = balance - 10000 WHERE id = 1;   -- 출금
UPDATE account SET balance = balance + 10000 WHERE id = 2;   -- 입금

COMMIT;                                  -- 둘 다 확정
-- 문제가 있으면 ROLLBACK;  → 둘 다 없던 일로

SAVEPOINT — 부분 롤백

BEGIN;
INSERT INTO orders VALUES (2001, 1, '2026-08-05', 'PAID');

SAVEPOINT sp1;
INSERT INTO order_item VALUES (99, 2001, 999, 1, 1000);   -- 없는 상품 → 실패
ROLLBACK TO SAVEPOINT sp1;                                 -- 여기까지만 취소

INSERT INTO order_item VALUES (99, 2001, 10, 1, 49000);   -- 올바른 값으로 재시도
COMMIT;                                                    -- 주문 자체는 살아 있습니다

트랜잭션을 다룰 때의 세 가지 원칙

• 짧게 유지하세요. 트랜잭션 안에서 외부 API를 호출하거나 사용자 입력을 기다리면 그동안 락이 유지되어 다른 작업이 줄줄이 막힙니다.
• 순서를 통일하세요. A→B 순서로 잠그는 코드와 B→A 순서로 잠그는 코드가 만나면 데드락입니다(⚙️ t12).
• 실패를 가정하세요. 커밋은 실패할 수 있습니다. 앱 코드에서 예외 처리와 재시도를 반드시 넣으세요(🔌 a11).

-- 우리가 쓰는 순서
SELECT   category, COUNT(*) AS 개수
FROM     product
WHERE    price >= 20000
GROUP BY category
HAVING   COUNT(*) >= 2
ORDER BY 개수 DESC
LIMIT    5;

-- DB 가 실행하는 순서
-- ① FROM     product          표를 읽고
-- ② WHERE    price >= 20000   행을 거르고
-- ③ GROUP BY category         묶고
-- ④ HAVING   COUNT(*) >= 2    그룹을 거르고
-- ⑤ SELECT   category, COUNT(*) AS 개수   ← 별칭이 여기서 생김
-- ⑥ ORDER BY 개수 DESC        정렬하고
-- ⑦ LIMIT    5                자른다

이 순서로 설명되는 것들

-- ── 조회 ────────────────────────────────────────────
SELECT DISTINCT col1, col2 AS 별칭
FROM   table1 t1
JOIN   table2 t2 ON t2.fk = t1.id        -- INNER / LEFT / RIGHT / FULL / CROSS
WHERE  col1 = 'x'                        -- = <> > < >= <=
  AND  col2 BETWEEN 1 AND 10             --   IN · NOT IN · LIKE · IS NULL
GROUP  BY col1                           -- 묶기
HAVING COUNT(*) > 1                      -- 그룹 조건
ORDER  BY col1 DESC, id                  -- 정렬 (유일 컬럼을 마지막에)
LIMIT  10 OFFSET 20;                     -- Oracle: FETCH FIRST 10 ROWS ONLY

-- ── 변경 ────────────────────────────────────────────
INSERT INTO t (a,b) VALUES (1,'x'), (2,'y');
INSERT INTO t (a,b) SELECT a,b FROM src WHERE ...;
UPDATE t SET a = a + 1 WHERE id = 1;     -- ★ WHERE 필수
DELETE FROM t WHERE id = 1;              -- ★ WHERE 필수
TRUNCATE TABLE t;                        -- 전부 비우기 (되돌리기 어려움)

-- ── 구조 ────────────────────────────────────────────
CREATE TABLE t (
  id    INTEGER PRIMARY KEY,
  name  VARCHAR(50) NOT NULL,
  email VARCHAR(120) UNIQUE,
  amt   DECIMAL(12,2) NOT NULL DEFAULT 0 CHECK (amt >= 0),
  fk_id INTEGER REFERENCES other(id)
);
ALTER TABLE t ADD COLUMN c VARCHAR(10);
DROP TABLE t;
CREATE INDEX idx_t_name ON t(name);

-- ── 트랜잭션 ─────────────────────────────────────────
BEGIN;  ...  COMMIT;   /  ROLLBACK;   /  SAVEPOINT sp1;

-- ── 자주 쓰는 함수 ───────────────────────────────────
COALESCE(a, b)     NULL 이면 b
NULLIF(a, b)       같으면 NULL (0으로 나누기 방지)
CASE WHEN … THEN … ELSE … END
COUNT · SUM · AVG · MIN · MAX
UPPER · LOWER · TRIM · REPLACE · SUBSTR · LENGTH

헷갈릴 때 보는 판정표

SELECT category, AVG(price)
FROM   product
GROUP  BY category;

-- 카테고리별 1행씩만 남습니다
-- 개별 상품 이름은 볼 수 없습니다

SELECT name, category, price,
       AVG(price) OVER (PARTITION BY category)
         AS 카테고리평균
FROM   product;

-- 상품 5개가 그대로 나오면서
-- 각 행에 자기 카테고리 평균이 붙습니다

문법 구조

함수() OVER (
    PARTITION BY 그룹기준     -- 생략하면 전체가 한 덩어리
    ORDER BY 정렬기준         -- 순위·누적에 필요
    ROWS BETWEEN … AND …      -- 계산 범위(프레임) · e03 에서 자세히
)

쓸 수 있는 함수

• 집계형 — SUM · AVG · COUNT · MIN · MAX 를 그대로 OVER와 함께 씁니다
• 순위형 — ROW_NUMBER · RANK · DENSE_RANK · NTILE (e02)
• 이동형 — LAG · LEAD · FIRST_VALUE · LAST_VALUE · NTH_VALUE (e03)
• 분포형 — PERCENT_RANK · CUME_DIST · PERCENTILE_CONT

-- 동점을 보여주기 위해 '마우스패드 XL'(49000)을 하나 추가했다고 가정합니다
SELECT name, price,
       ROW_NUMBER() OVER (ORDER BY price DESC) AS rn,
       RANK()       OVER (ORDER BY price DESC) AS rnk,
       DENSE_RANK() OVER (ORDER BY price DESC) AS drnk
FROM   product;

가장 많이 쓰는 패턴 — 그룹별 상위 N

-- 카테고리별 최고가 상품 1개씩
SELECT category, name, price
FROM  (SELECT category, name, price,
              ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS rn
       FROM   product) t
WHERE  rn = 1;

-- 회원별 최근 주문 3건
SELECT member_id, id AS order_no, ordered_at
FROM  (SELECT member_id, id, ordered_at,
              ROW_NUMBER() OVER (PARTITION BY member_id ORDER BY ordered_at DESC) AS rn
       FROM   orders) t
WHERE  rn <= 3;

NTILE — 몇 등분으로 나누기

-- 구매액 기준 4등분해 등급 매기기
SELECT name, 총구매액,
       NTILE(4) OVER (ORDER BY 총구매액 DESC) AS 분위
FROM   v_member_sales;
-- 분위 1 = 상위 25%

LAG · LEAD — 앞뒤 행의 값 가져오기

SELECT 월, 매출,
       LAG(매출)  OVER (ORDER BY 월) AS 전월매출,
       매출 - LAG(매출) OVER (ORDER BY 월) AS 증감,
       ROUND( (매출 - LAG(매출) OVER (ORDER BY 월)) * 100.0
              / NULLIF(LAG(매출) OVER (ORDER BY 월), 0), 1) AS 증감률
FROM   monthly_sales
ORDER  BY 월;

-- 기본값을 주면 첫 행의 NULL 을 없앨 수 있습니다
LAG(매출, 1, 0) OVER (ORDER BY 월)     -- 1칸 앞, 없으면 0
LEAD(매출, 1, 0) OVER (ORDER BY 월)    -- 1칸 뒤

프레임 — 어디부터 어디까지 계산할지

-- 누적 매출
SELECT 일자, 매출,
       SUM(매출) OVER (ORDER BY 일자
                       ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS 누적매출
FROM   daily_sales;

-- 7일 이동평균 (그래프에서 노이즈를 걷어낼 때)
SELECT 일자, 매출,
       ROUND(AVG(매출) OVER (ORDER BY 일자
                             ROWS BETWEEN 6 PRECEDING AND CURRENT ROW), 0) AS 이동평균7일
FROM   daily_sales;

-- 전체 대비 비중
SELECT category, 매출,
       ROUND(매출 * 100.0 / SUM(매출) OVER (), 1) AS 비중
FROM   category_sales;

SELECT * FROM (
  SELECT t.*, RANK() OVER (ORDER BY 구매액 DESC) rk
  FROM (
    SELECT m.id, m.name,
           SUM(oi.qty*oi.unit_price) 구매액
    FROM member m
    JOIN orders o ON o.member_id=m.id
    JOIN order_item oi ON oi.order_id=o.id
    GROUP BY m.id, m.name
  ) t
) x
WHERE x.rk <= 3;

WITH 구매액 AS (
  SELECT m.id, m.name,
         SUM(oi.qty*oi.unit_price) AS 금액
  FROM   member m
  JOIN   orders o      ON o.member_id = m.id
  JOIN   order_item oi ON oi.order_id = o.id
  GROUP  BY m.id, m.name
),
순위 AS (
  SELECT *, RANK() OVER (ORDER BY 금액 DESC) AS rk
  FROM   구매액
)
SELECT * FROM 순위 WHERE rk <= 3;

CTE 를 여러 개 쓰고 서로 참조하기

WITH 유효주문 AS (
  SELECT * FROM orders WHERE status <> 'CANCELED'
),
회원별 AS (
  SELECT member_id, COUNT(*) AS 건수
  FROM   유효주문                      -- ← 앞의 CTE 를 그대로 참조
  GROUP  BY member_id
),
평균 AS (
  SELECT AVG(건수) AS 평균건수 FROM 회원별
)
SELECT m.name, r.건수, a.평균건수
FROM   회원별 r
JOIN   member m ON m.id = r.member_id
CROSS  JOIN 평균 a
WHERE  r.건수 > a.평균건수;

CTE 로 데이터를 바꾸기 (PostgreSQL)

-- 지운 행을 그대로 아카이브 테이블로 옮기기 — 한 트랜잭션에서
WITH deleted AS (
  DELETE FROM orders
  WHERE  ordered_at < '2024-01-01'
  RETURNING *
)
INSERT INTO orders_archive SELECT * FROM deleted;

-- 예제 데이터
CREATE TABLE category (
  id        INTEGER PRIMARY KEY,
  name      VARCHAR(50),
  parent_id INTEGER REFERENCES category(id)
);
INSERT INTO category VALUES
 (1,'전체',NULL), (2,'전자제품',1), (3,'주변기기',2),
 (4,'키보드',3),  (5,'마우스',3),   (6,'의류',1);

WITH RECURSIVE tree AS (
    -- ① 시작점 (앵커) — 최상위부터
    SELECT id, name, parent_id, 1 AS depth,
           CAST(name AS VARCHAR(200)) AS path
    FROM   category
    WHERE  parent_id IS NULL

    UNION ALL

    -- ② 반복 — 이전 결과의 자식을 계속 붙인다
    SELECT c.id, c.name, c.parent_id, t.depth + 1,
           CAST(t.path || ' > ' || c.name AS VARCHAR(200))
    FROM   category c
    JOIN   tree t ON t.id = c.parent_id
)
SELECT depth, path FROM tree ORDER BY path;

구조는 항상 같습니다

역방향 — 특정 노드의 조상 찾기

WITH RECURSIVE up AS (
    SELECT id, name, parent_id FROM category WHERE id = 4      -- '키보드'에서 출발
    UNION ALL
    SELECT c.id, c.name, c.parent_id
    FROM   category c
    JOIN   up u ON c.id = u.parent_id                          -- 부모를 따라 올라간다
)
SELECT * FROM up;
-- 키보드 → 주변기기 → 전자제품 → 전체

DB별 지원

-- ROLLUP : 계층적 소계 (지역 → 지역+등급 → 총계)
SELECT city, grade, COUNT(*) AS 인원
FROM   member
GROUP  BY ROLLUP (city, grade);

-- CUBE : 모든 조합 (지역별 · 등급별 · 지역+등급 · 총계)
SELECT city, grade, COUNT(*) FROM member GROUP BY CUBE (city, grade);

-- GROUPING SETS : 원하는 조합만 콕 집어서
SELECT city, grade, COUNT(*)
FROM   member
GROUP  BY GROUPING SETS ((city), (grade), ());   -- 지역별 · 등급별 · 총계

-- NULL 이 '소계'인지 '진짜 NULL'인지 구분하기
SELECT CASE WHEN GROUPING(city) = 1 THEN '전체' ELSE city END AS 지역,
       COUNT(*) AS 인원
FROM   member
GROUP  BY ROLLUP (city);

어디서나 되는 방법 — CASE 집계

SELECT category,
       SUM(CASE WHEN 월 = '01' THEN 매출 ELSE 0 END) AS "1월",
       SUM(CASE WHEN 월 = '02' THEN 매출 ELSE 0 END) AS "2월",
       SUM(CASE WHEN 월 = '03' THEN 매출 ELSE 0 END) AS "3월",
       SUM(매출) AS 합계
FROM   monthly_category_sales
GROUP  BY category;

Oracle 전용 — PIVOT / UNPIVOT

SELECT * FROM (
    SELECT category, 월, 매출 FROM monthly_category_sales
)
PIVOT (
    SUM(매출) FOR 월 IN ('01' AS "1월", '02' AS "2월", '03' AS "3월")
);

-- 반대로 가로를 세로로
SELECT * FROM sales_wide
UNPIVOT (매출 FOR 월 IN ("1월", "2월", "3월"));

PostgreSQL — crosstab 확장

CREATE EXTENSION IF NOT EXISTS tablefunc;

SELECT * FROM crosstab(
  'SELECT category, 월, 매출 FROM monthly_category_sales ORDER BY 1,2',
  'SELECT DISTINCT 월 FROM monthly_category_sales ORDER BY 1'
) AS ct(category TEXT, m01 NUMERIC, m02 NUMERIC, m03 NUMERIC);

INSERT INTO stock (product_id, qty)
VALUES (10, 5)
ON CONFLICT (product_id)
DO UPDATE SET qty = stock.qty + EXCLUDED.qty;

-- 있으면 무시하고 넘어가기
INSERT INTO stock (product_id, qty)
VALUES (10, 5)
ON CONFLICT DO NOTHING;

INSERT INTO stock (product_id, qty)
VALUES (10, 5)
ON DUPLICATE KEY UPDATE
    qty = qty + VALUES(qty);

-- MySQL 8.0.20+ 권장 문법
INSERT INTO stock (product_id, qty)
VALUES (10, 5) AS new
ON DUPLICATE KEY UPDATE qty = qty + new.qty;

MERGE INTO stock s
USING (SELECT 10 AS pid, 5 AS q FROM dual) x
   ON (s.product_id = x.pid)
WHEN MATCHED THEN
    UPDATE SET s.qty = s.qty + x.q
WHEN NOT MATCHED THEN
    INSERT (product_id, qty) VALUES (x.pid, x.q);

-- 3.24 미만에서 쓰던 방식
INSERT OR REPLACE INTO stock (product_id, qty)
VALUES (10, 5);

-- ⚠️ REPLACE 는 기존 행을 DELETE 후 INSERT 라
-- 적지 않은 컬럼이 기본값으로 날아갑니다

UPSERT 가 성립하려면 제약이 있어야 합니다

-- 이 UNIQUE 제약이 있어야 "충돌"이라는 판단이 가능합니다
ALTER TABLE stock ADD CONSTRAINT uq_stock_product UNIQUE (product_id);

-- 여러 컬럼 조합이 기준일 때
INSERT INTO daily_stat (dt, category, cnt)
VALUES (DATE '2026-08-05', '주변기기', 1)
ON CONFLICT (dt, category) DO UPDATE SET cnt = daily_stat.cnt + 1;

왜 UPSERT를 써야 하나 — 동시성

# 두 요청이 동시에 오면
row = db.query("SELECT … WHERE product_id=10")
if row:
    db.execute("UPDATE …")
else:
    db.execute("INSERT …")   # ← 둘 다 여기로 와서
                             #    유니크 제약 위반 or 중복 행

db.execute("""
  INSERT INTO stock (product_id, qty) VALUES (%s, %s)
  ON CONFLICT (product_id)
  DO UPDATE SET qty = stock.qty + EXCLUDED.qty
""", (10, 5))
# 동시에 들어와도 한 쪽은 INSERT, 한 쪽은 UPDATE

-- PostgreSQL · Oracle 12c+ : LATERAL
SELECT m.name, o.id AS order_no, o.ordered_at
FROM   member m
CROSS JOIN LATERAL (
    SELECT id, ordered_at
    FROM   orders
    WHERE  member_id = m.id           -- ← 바깥 m 을 참조할 수 있습니다
    ORDER  BY ordered_at DESC
    LIMIT  3                          -- Oracle 은 FETCH FIRST 3 ROWS ONLY
) o;

-- 주문이 없는 회원도 보고 싶다면
SELECT m.name, o.id, o.ordered_at
FROM   member m
LEFT JOIN LATERAL (
    SELECT id, ordered_at FROM orders
    WHERE  member_id = m.id ORDER BY ordered_at DESC LIMIT 3
) o ON TRUE;

DB별 대응

윈도우 함수로도 됩니다 — 어느 쪽이 빠를까

-- 어디서나 되는 대안
SELECT name, order_no, ordered_at
FROM  (SELECT m.name, o.id AS order_no, o.ordered_at,
              ROW_NUMBER() OVER (PARTITION BY m.id ORDER BY o.ordered_at DESC) AS rn
       FROM   member m JOIN orders o ON o.member_id = m.id) t
WHERE  rn <= 3;

정규식 — 패턴이 복잡할 때

전문 검색 — 본문에서 단어 찾기

-- 영어는 기본 지원
SELECT * FROM post
WHERE  to_tsvector('english', body)
       @@ to_tsquery('english', 'database & index');

CREATE INDEX idx_post_fts ON post
  USING GIN (to_tsvector('english', body));

-- 한국어는 pg_bigm · pg_trgm 확장 권장 (p09)

ALTER TABLE post
  ADD FULLTEXT INDEX ft_body (body)
  WITH PARSER ngram;          -- 한국어는 ngram 파서

SELECT * FROM post
WHERE  MATCH(body) AGAINST('데이터베이스' IN BOOLEAN MODE);

CREATE VIRTUAL TABLE post_fts
  USING fts5(title, body, tokenize='trigram');

INSERT INTO post_fts SELECT title, body FROM post;

SELECT * FROM post_fts WHERE post_fts MATCH '데이터베이스';

CREATE INDEX idx_post_ctx ON post(body)
  INDEXTYPE IS CTXSYS.CONTEXT;

SELECT * FROM post
WHERE  CONTAINS(body, '데이터베이스', 1) > 0;

members = db.query("SELECT id FROM member")
for m in members:                       # 10만 번
    orders = db.query(
        "SELECT * FROM orders WHERE member_id=%s", m.id)
    total = sum(o.amount for o in orders)
    db.execute(
        "UPDATE member SET total=%s WHERE id=%s",
        total, m.id)                    # 또 10만 번

# 왕복 20만 번 · 수십 분

UPDATE member m
SET    total = t.합계
FROM  (SELECT member_id, SUM(amount) AS 합계
       FROM   orders GROUP BY member_id) t
WHERE  t.member_id = m.id;

-- 왕복 1번 · 수 초

사고 전환 훈련 — 이렇게 바꿔 생각합니다

한 번에 하되, 너무 크면 잘라서

"한 번에"가 항상 옳은 것도 아닙니다. 수천만 건을 단일 트랜잭션으로 바꾸면 락이 오래 걸리고 로그가 폭증해 오히려 장애가 됩니다. 집합으로 생각하되 청크로 나눠 실행하는 것이 실무의 정답입니다 → 다음 섹션.

기본형 — PK 범위로 자르기

-- PostgreSQL : 반복 실행 (영향 행이 0이 될 때까지)
-- MySQL 은 IN (… LIMIT) 서브쿼리를 못 씁니다(ERROR 1235) → 아래 MySQL 형태로
UPDATE orders
SET    status = 'ARCHIVED'
WHERE  id IN (
    SELECT id FROM orders
    WHERE  status = 'PAID' AND ordered_at < '2024-01-01'
    ORDER  BY id
    LIMIT  10000
);

-- 삭제도 같은 방식
DELETE FROM log_table
WHERE  id IN (SELECT id FROM log_table
              WHERE created_at < '2025-01-01'
              ORDER BY id LIMIT 10000);

-- MySQL : 단일 테이블 UPDATE · DELETE 에 ORDER BY · LIMIT 를 직접 붙입니다
UPDATE orders SET status = 'ARCHIVED'
WHERE  status = 'PAID' AND ordered_at < '2024-01-01'
ORDER  BY id LIMIT 10000;
DELETE FROM log_table WHERE created_at < '2025-01-01'
ORDER  BY id LIMIT 10000;

-- PostgreSQL : 진행 상황을 돌려받으며 청크 처리
WITH target AS (
    SELECT id FROM orders
    WHERE  ordered_at < '2024-01-01' AND status = 'PAID'
    ORDER  BY id
    LIMIT  10000
    FOR UPDATE SKIP LOCKED          -- 다른 세션이 잡은 행은 건너뜀
)
UPDATE orders o
SET    status = 'ARCHIVED'
FROM   target t
WHERE  o.id = t.id
RETURNING o.id;

셸 스크립트로 반복 돌리기

#!/usr/bin/env bash
# 10000건씩 지우고 0.5초 쉬기 — 복제 지연을 만들지 않는 속도로
while : ; do
  n=$(psql -qtAX -d shop -c "
        DELETE FROM log_table
        WHERE id IN (SELECT id FROM log_table
                     WHERE created_at < '2025-01-01'
                     ORDER BY id LIMIT 10000)
        RETURNING 1" | wc -l)
  echo "$(date +%T)  삭제 $n 건"
  [ "$n" -eq 0 ] && break
  sleep 0.5                      # ← 이 쉬는 시간이 핵심입니다
done

더 좋은 방법 — 아예 다시 만들기

-- 전체의 80% 이상을 지워야 한다면 DELETE 보다 이쪽이 훨씬 빠릅니다
CREATE TABLE orders_new (LIKE orders INCLUDING ALL);   -- PostgreSQL
INSERT INTO orders_new SELECT * FROM orders WHERE ordered_at >= '2025-01-01';

BEGIN;
ALTER TABLE orders     RENAME TO orders_old;
ALTER TABLE orders_new RENAME TO orders;
COMMIT;

-- 확인 후
DROP TABLE orders_old;

특히 자주 나오는 세 가지 실제 수정 예

SELECT * FROM orders
WHERE  DATE_FORMAT(ordered_at,'%Y-%m') = '2026-01';

SELECT * FROM orders
WHERE  ordered_at >= '2026-01-01'
  AND  ordered_at <  '2026-02-01';

SELECT COUNT(*) FROM orders WHERE member_id = 1;
-- 결과가 0보다 크면 주문이 있다고 판단

SELECT EXISTS (
  SELECT 1 FROM orders WHERE member_id = 1
);

SELECT DISTINCT m.id, m.name
FROM   member m
JOIN   orders o ON o.member_id = m.id;
-- 주문 수만큼 중복돼서 DISTINCT 를 붙임

SELECT m.id, m.name
FROM   member m
WHERE  EXISTS (SELECT 1 FROM orders o
               WHERE o.member_id = m.id);

버전 흐름 (2026-08 기준)

• 11gR2 — 아직도 현장에 남아 있습니다. FETCH FIRST·IDENTITY가 없어 ROWNUM·시퀀스를 씁니다.
• 12c / 18c / 19c — 19c가 장기 지원(LTS) 버전이라 실무 점유율이 가장 높습니다. 멀티테넌트(CDB/PDB)가 기본이 됐습니다.
• 21c — 혁신 릴리스(단기 지원). JSON 전용 타입 등이 들어왔습니다.
• 23ai — 현재의 장기 지원 버전. BOOLEAN 타입, IF EXISTS, 벡터 검색, SQL의 도메인·주석 개선 등 오래 기다린 기능들이 들어왔습니다.

방법 ① Docker — 권장

# 공식 컨테이너 레지스트리 (별도 로그인 불필요)
docker run -d --name ora \
  -p 1521:1521 \
  -e ORACLE_PWD=devpass \
  -v oradata:/opt/oracle/oradata \
  container-registry.oracle.com/database/free:latest

# 기동에 2~5분 걸립니다. 아래가 보이면 준비 완료
docker logs -f ora | grep -i "DATABASE IS READY"

# 접속
docker exec -it ora sqlplus system/devpass@//localhost:1521/FREEPDB1

방법 ② 직접 설치 (Linux)

# Oracle Linux · RHEL 계열
sudo dnf install -y oracle-database-preinstall-23ai      # 커널 파라미터·계정 자동 설정
sudo dnf install -y oracle-database-free-23ai            # 다운로드한 RPM

# 초기 구성 (비밀번호를 물어봅니다)
sudo /etc/init.d/oracle-free-23ai configure

# 환경 변수 (~/.bashrc 에 추가)
export ORACLE_HOME=/opt/oracle/product/23ai/dbhomeFree
export ORACLE_SID=FREE
export PATH=$ORACLE_HOME/bin:$PATH

# 기동 확인
lsnrctl status
sqlplus / as sysdba

방법 ③ 접속만 필요할 때 — Instant Client

회사 서버의 오라클에 붙기만 하면 되는 경우, DB 전체를 설치할 필요가 없습니다. Oracle Instant Client(수십 MB) + SQL Developer(무료 GUI) 조합이면 충분합니다. 파이썬의 oracledb 드라이버는 Thin 모드를 지원해 Instant Client 없이도 붙습니다(🔌 a02).

설치 후 첫 확인

-- 버전
SELECT banner_full FROM v$version;

-- 지금 어디에 접속해 있나 (CDB? PDB?)
SHOW CON_NAME;
SELECT sys_context('USERENV','CON_NAME') FROM dual;

-- 사용 가능한 PDB 목록
SELECT name, open_mode FROM v$pdbs;

접속 문자열 세 가지 형태

# ① Easy Connect — 가장 간단 (서비스명 앞에 슬래시)
sqlplus system/devpass@//localhost:1521/FREEPDB1

# ② SID 방식 (구형) — 콜론 형식 host:1521:FREE 는 JDBC 전용(jdbc:oracle:thin:@localhost:1521:FREE)
#    sqlplus 에서 SID 로 붙으려면 접속 기술자를 직접 씁니다
sqlplus system/devpass@"(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SID=FREE)))"

# ③ tnsnames.ora 별칭
sqlplus system/devpass@ORCLPDB

# $ORACLE_HOME/network/admin/tnsnames.ora
ORCLPDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = db.example.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCLPDB)
    )
  )

리스너 진단

lsnrctl status          # 어떤 서비스가 등록돼 있는지 확인 ← 여기가 핵심
lsnrctl start
lsnrctl stop

# 네트워크 경로 확인
tnsping ORCLPDB

SQL*Plus 에서 바로 쓰는 명령

SET LINESIZE 200          -- 한 줄 폭 (기본 80이라 결과가 접힙니다)
SET PAGESIZE 50           -- 헤더 반복 주기
SET SERVEROUTPUT ON       -- DBMS_OUTPUT.PUT_LINE 결과 보기
COLUMN name FORMAT A20    -- 컬럼 폭 지정

DESC member;              -- 테이블 구조
SHOW USER;                -- 지금 누구로 접속했나
SHOW CON_NAME;            -- 지금 어느 컨테이너인가

@script.sql               -- 파일 실행
SPOOL out.txt             -- 결과를 파일로
SPOOL OFF
EXIT

-- 지금 어디인지 먼저 확인
SHOW CON_NAME;                      -- CDB$ROOT 이면 root 입니다

-- PDB 로 이동
ALTER SESSION SET CONTAINER = FREEPDB1;
SHOW CON_NAME;                      -- FREEPDB1

-- 애플리케이션 계정 만들기 (PDB 안에서)
CREATE USER app IDENTIFIED BY "Strong#Pass1"
  DEFAULT TABLESPACE users
  TEMPORARY TABLESPACE temp
  QUOTA UNLIMITED ON users;         -- ★ 이게 없으면 INSERT 시 ORA-01950

GRANT CREATE SESSION      TO app;   -- 접속 권한 (없으면 로그인 자체가 안 됨)
GRANT CREATE TABLE        TO app;
GRANT CREATE SEQUENCE     TO app;
GRANT CREATE VIEW         TO app;
GRANT CREATE PROCEDURE    TO app;

권한 — 시스템 권한과 객체 권한

-- 시스템 권한 : "무엇을 할 수 있는가"
GRANT CREATE TABLE, CREATE VIEW TO app;

-- 객체 권한 : "어느 객체에 무엇을 할 수 있는가"
GRANT SELECT, INSERT, UPDATE ON hr.member TO app;
GRANT EXECUTE ON hr.pkg_order TO app;

-- 롤 : 권한 묶음
CREATE ROLE app_readonly;
GRANT SELECT ON hr.member TO app_readonly;
GRANT SELECT ON hr.orders TO app_readonly;
GRANT app_readonly TO analyst1, analyst2;

-- 확인
SELECT * FROM user_sys_privs;      -- 내가 가진 시스템 권한
SELECT * FROM user_tab_privs;      -- 내가 가진 객체 권한
SELECT * FROM session_privs;       -- 지금 세션에서 유효한 전체

스키마 = 계정

오라클에서는 계정 하나가 곧 스키마 하나입니다(PostgreSQL·MySQL과 다른 점). app 계정이 만든 테이블은 app.member 가 되고, 다른 계정이 쓰려면 스키마명을 붙이거나 시노님이 필요합니다.

-- 시노님 : 다른 스키마 객체에 별명 붙이기
CREATE SYNONYM member FOR hr.member;              -- 내 계정에서만
CREATE PUBLIC SYNONYM member FOR hr.member;       -- 전체 공개 (남용 주의)

DUAL — 계산 전용 가짜 테이블

-- 오라클은 SELECT 에 FROM 이 반드시 있어야 합니다
SELECT 1 + 1 FROM dual;
SELECT SYSDATE FROM dual;
SELECT seq_order.NEXTVAL FROM dual;

-- 다른 DB는 FROM 없이 됩니다
-- PostgreSQL/MySQL/SQLite:  SELECT 1 + 1;

ROWNUM — 결과에 매겨지는 임시 번호

SELECT ROWNUM, name FROM member WHERE ROWNUM <= 3;   -- 상위 3건

SELECT * FROM product
WHERE  ROWNUM <= 3
ORDER  BY price DESC;

-- ROWNUM 이 ORDER BY 보다 먼저 매겨져
-- "아무 3건을 뽑아서 정렬"한 결과입니다

SELECT * FROM (
    SELECT * FROM product ORDER BY price DESC
)
WHERE ROWNUM <= 3;

페이징 — 세 가지 방법

-- ① 12c 이상 (표준 문법) ★ 권장
SELECT * FROM product
ORDER  BY price DESC
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

-- ② 11g 이하 : 3중 서브쿼리 (현장 레거시의 표준형)
SELECT * FROM (
    SELECT a.*, ROWNUM rn FROM (
        SELECT * FROM product ORDER BY price DESC
    ) a
    WHERE ROWNUM <= 30            -- 끝 위치
)
WHERE rn > 20;                    -- 시작 위치

-- ③ 윈도우 함수 (가장 유연 · 그룹별 페이징도 가능)
SELECT * FROM (
    SELECT p.*, ROW_NUMBER() OVER (ORDER BY price DESC) rn
    FROM   product p
)
WHERE rn BETWEEN 21 AND 30;

-- DECODE 실전 — 짧지만 가독성은 CASE 가 낫습니다
SELECT name,
       DECODE(grade, 'GOLD','10%', 'SILVER','5%', '없음') AS 혜택
FROM   member;

-- LISTAGG — 회원별 주문번호를 한 줄로
SELECT m.name,
       LISTAGG(o.id, ', ') WITHIN GROUP (ORDER BY o.id) AS 주문목록
FROM   member m JOIN orders o ON o.member_id = m.id
GROUP  BY m.name;

오라클만의 특이점 — 빈 문자열 = NULL

SELECT CASE WHEN '' IS NULL THEN 'NULL 이다' ELSE '아니다' END FROM dual;
-- 오라클: 'NULL 이다'      ← 오라클만 이렇습니다
-- 그 외 : '아니다'

-- 그래서 이런 코드가 오라클에서만 다르게 동작합니다
SELECT COUNT(*) FROM member WHERE email = '';    -- 오라클: 항상 0건

SELECT * FROM orders
WHERE  ordered_at = SYSDATE;
-- 정확히 '지금 이 초'인 행만 걸립니다 → 0건

SELECT * FROM orders
WHERE  ordered_at BETWEEN '2026-01-01' AND '2026-01-31';
-- 1/31 00:00:00 까지만 → 하루가 통째로 빠짐

SELECT * FROM orders
WHERE  ordered_at >= TRUNC(SYSDATE)
  AND  ordered_at <  TRUNC(SYSDATE) + 1;

SELECT * FROM orders
WHERE  ordered_at >= DATE '2026-01-01'
  AND  ordered_at <  DATE '2026-02-01';

날짜 함수

SELECT SYSDATE                            AS 현재시각,      -- DB 서버 시각
       SYSTIMESTAMP                       AS 타임존포함,
       TRUNC(SYSDATE)                     AS 오늘자정,
       TRUNC(SYSDATE, 'MM')               AS 이번달1일,
       LAST_DAY(SYSDATE)                  AS 이번달말일,
       ADD_MONTHS(SYSDATE, 3)             AS 삼개월후,
       SYSDATE + 7                        AS 일주일후,      -- 숫자 = 일 단위
       SYSDATE + 1/24                     AS 한시간후,      -- 1/24 = 1시간
       MONTHS_BETWEEN(SYSDATE, DATE '2020-03-01') AS 근무개월   -- dual 에는 컬럼이 없어 리터럴로
FROM   dual;

-- 문자 ↔ 날짜
SELECT TO_DATE('2026-08-05 14:30', 'YYYY-MM-DD HH24:MI') FROM dual;
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS')          FROM dual;
SELECT TO_CHAR(SYSDATE, 'YYYY"년" MM"월" DD"일"')          FROM dual;

타임존이 필요하면 TIMESTAMP

CREATE TABLE event (
  id      NUMBER,
  at_utc  TIMESTAMP WITH TIME ZONE,          -- 타임존 정보 저장
  at_loc  TIMESTAMP WITH LOCAL TIME ZONE     -- 조회하는 세션 시간대로 자동 변환
);

SELECT SYSTIMESTAMP AT TIME ZONE 'UTC' FROM dual;
SELECT ordered_at + INTERVAL '3' DAY   FROM orders;
SELECT ordered_at + INTERVAL '2' MONTH FROM orders;

-- 시퀀스 만들기
CREATE SEQUENCE seq_member
  START WITH 1
  INCREMENT BY 1
  NOCACHE            -- 번호 건너뜀을 막고 싶으면 (성능은 떨어집니다)
  NOCYCLE;

-- 사용
INSERT INTO member (id, name) VALUES (seq_member.NEXTVAL, '김하늘');
SELECT seq_member.CURRVAL FROM dual;    -- 방금 쓴 값 (같은 세션에서만)

-- 12c 부터는 DEFAULT 로 쓸 수 있습니다
CREATE TABLE member (
  id   NUMBER DEFAULT seq_member.NEXTVAL PRIMARY KEY,
  name VARCHAR2(50)
);

IDENTITY — 12c 이상이면 이쪽이 간단합니다 12c+

CREATE TABLE member (
  id   NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,   -- 값을 직접 못 넣음
  name VARCHAR2(50)
);
-- 또는
  id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY  -- 직접 넣는 것도 허용
  id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY      -- NULL 넣으면 자동 채움

INSERT INTO member (name) VALUES ('김하늘');   -- id 는 자동

11g 이하 — 시퀀스 + 트리거 11g

CREATE OR REPLACE TRIGGER trg_member_id
BEFORE INSERT ON member
FOR EACH ROW
WHEN (NEW.id IS NULL)
BEGIN
  :NEW.id := seq_member.NEXTVAL;
END;
/

번호가 건너뛰는 이유

시퀀스는 성능을 위해 번호를 미리 메모리에 캐시합니다(기본 20개). DB를 재시작하거나 인스턴스가 죽으면 캐시에 남아 있던 번호가 사라집니다. 또 롤백해도 이미 뽑은 번호는 돌아오지 않습니다.

트리거 — 쓸 때와 안 쓸 때

-- 감사 로그 : 트리거의 정당한 용도
CREATE OR REPLACE TRIGGER trg_member_audit
AFTER UPDATE ON member
FOR EACH ROW
BEGIN
  INSERT INTO member_hist (member_id, old_grade, new_grade, changed_at, changed_by)
  VALUES (:OLD.id, :OLD.grade, :NEW.grade, SYSTIMESTAMP, SYS_CONTEXT('USERENV','SESSION_USER'));
END;
/

SELECT LEVEL,
       LPAD(' ', (LEVEL-1)*3) || name    AS 트리,
       SYS_CONNECT_BY_PATH(name, ' > ')  AS 경로,
       CONNECT_BY_ISLEAF                 AS 말단여부
FROM   category
START  WITH parent_id IS NULL            -- 출발점
CONNECT BY PRIOR id = parent_id          -- 부모→자식 연결 규칙
ORDER  SIBLINGS BY name;                 -- 형제끼리 정렬

방향 뒤집기 — 조상 찾기

-- '마우스'에서 최상위까지 거슬러 올라가기
SELECT LEVEL, name
FROM   category
START  WITH id = 5
CONNECT BY PRIOR parent_id = id;      -- PRIOR 위치가 반대

재귀 CTE 와 비교

SELECT LEVEL, name
FROM   category
START  WITH parent_id IS NULL
CONNECT BY PRIOR id = parent_id;

WITH tree (id, name, lvl) AS (
  SELECT id, name, 1 FROM category WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.name, t.lvl+1
  FROM   category c JOIN tree t ON t.id = c.parent_id
)
SELECT lvl, name FROM tree;

KEEP … DENSE_RANK FIRST/LAST — 그룹의 대표값

-- 부서별로 "가장 최근 입사자의 급여" 를 집계 한 번에
SELECT dept_id,
       MAX(salary) KEEP (DENSE_RANK FIRST ORDER BY hire_date DESC) AS 최근입사자급여,
       MIN(salary) KEEP (DENSE_RANK LAST  ORDER BY hire_date DESC) AS 최초입사자급여,
       COUNT(*) AS 인원
FROM   employee
GROUP  BY dept_id;

RATIO_TO_REPORT · 누적 분포

SELECT category, name, price,
       ROUND(RATIO_TO_REPORT(price) OVER (PARTITION BY category) * 100, 1) AS 카테고리내비중,
       ROUND(CUME_DIST() OVER (ORDER BY price) * 100, 1)                   AS 누적분포,
       PERCENT_RANK()   OVER (ORDER BY price)                              AS 백분위
FROM   product;

MODEL 절 — 스프레드시트처럼 계산

-- 전월 값을 참조해 누적 예측치를 만드는 등, 엑셀 수식 같은 계산
SELECT 월, 매출, 예측
FROM   monthly_sales
MODEL
  DIMENSION BY (월)
  MEASURES (매출, 0 AS 예측)
  RULES (
    예측[FOR 월 FROM 7 TO 12 INCREMENT 1] = 매출[CV()-1] * 1.05
  );

MATCH_RECOGNIZE — 행 패턴 인식 12c+

-- "3일 연속 매출 상승" 같은 패턴을 SQL 로 찾기
SELECT * FROM daily_sales
MATCH_RECOGNIZE (
  ORDER BY 일자
  MEASURES FIRST(UP.일자) AS 시작일, LAST(UP.일자) AS 종료일, COUNT(*) AS 일수
  ONE ROW PER MATCH
  PATTERN (UP{3,})
  DEFINE UP AS 매출 > PREV(매출)
);

SET SERVEROUTPUT ON      -- 출력을 보려면 먼저 켜야 합니다

DECLARE
    v_name    member.name%TYPE;          -- 컬럼과 같은 타입으로 (변경에 안전)
    v_count   NUMBER := 0;
    v_row     member%ROWTYPE;            -- 행 전체를 담는 변수
BEGIN
    SELECT name INTO v_name FROM member WHERE id = 1;
    DBMS_OUTPUT.PUT_LINE('이름: ' || v_name);

    SELECT COUNT(*) INTO v_count FROM orders WHERE member_id = 1;

    IF v_count > 5 THEN
        DBMS_OUTPUT.PUT_LINE('단골');
    ELSIF v_count > 0 THEN
        DBMS_OUTPUT.PUT_LINE('일반');
    ELSE
        DBMS_OUTPUT.PUT_LINE('주문 없음');
    END IF;

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('회원을 찾을 수 없습니다');
    WHEN TOO_MANY_ROWS THEN
        DBMS_OUTPUT.PUT_LINE('결과가 여러 건입니다');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('오류: ' || SQLCODE || ' ' || SQLERRM);
        RAISE;                            -- ★ 삼키지 말고 다시 던지세요
END;
/

커서 — 여러 행을 한 줄씩

-- 방법 ① 암시적 커서 FOR 루프 (가장 간결 · 권장)
BEGIN
  FOR r IN (SELECT id, name FROM member WHERE grade = 'GOLD') LOOP
      DBMS_OUTPUT.PUT_LINE(r.id || ' : ' || r.name);
  END LOOP;
END;
/

-- 방법 ② 명시적 커서 (파라미터가 필요할 때)
DECLARE
  CURSOR c_member(p_grade VARCHAR2) IS
      SELECT id, name FROM member WHERE grade = p_grade;
  v_id member.id%TYPE;  v_name member.name%TYPE;
BEGIN
  OPEN c_member('GOLD');
  LOOP
      FETCH c_member INTO v_id, v_name;
      EXIT WHEN c_member%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE(v_id || ' : ' || v_name);
  END LOOP;
  CLOSE c_member;
END;
/

프로시저와 함수

CREATE OR REPLACE PROCEDURE grade_up(p_id IN NUMBER, p_result OUT VARCHAR2) IS
BEGIN
    UPDATE member SET grade = 'GOLD' WHERE id = p_id;
    IF SQL%ROWCOUNT = 0 THEN
        p_result := '대상 없음';
    ELSE
        p_result := '성공';
        COMMIT;
    END IF;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        p_result := '실패: ' || SQLERRM;
        RAISE;
END;
/

-- 함수는 값을 돌려주고 SELECT 안에서도 쓸 수 있습니다
CREATE OR REPLACE FUNCTION total_amount(p_member_id NUMBER) RETURN NUMBER IS
    v_total NUMBER;
BEGIN
    SELECT NVL(SUM(oi.qty * oi.unit_price), 0) INTO v_total
    FROM   orders o JOIN order_item oi ON oi.order_id = o.id
    WHERE  o.member_id = p_member_id;
    RETURN v_total;
END;
/

SELECT name, total_amount(id) FROM member;

패키지 — 관련 프로시저를 묶기

-- 명세 (인터페이스)
CREATE OR REPLACE PACKAGE pkg_order IS
    PROCEDURE create_order(p_member_id NUMBER, p_order_id OUT NUMBER);
    FUNCTION  total_of(p_order_id NUMBER) RETURN NUMBER;
END pkg_order;
/

-- 본문 (구현)
CREATE OR REPLACE PACKAGE BODY pkg_order IS
    PROCEDURE create_order(p_member_id NUMBER, p_order_id OUT NUMBER) IS
    BEGIN
        INSERT INTO orders (id, member_id, ordered_at, status)
        VALUES (seq_order.NEXTVAL, p_member_id, SYSDATE, 'PAID')
        RETURNING id INTO p_order_id;
    END;

    FUNCTION total_of(p_order_id NUMBER) RETURN NUMBER IS
        v NUMBER;
    BEGIN
        SELECT SUM(qty * unit_price) INTO v FROM order_item WHERE order_id = p_order_id;
        RETURN NVL(v, 0);
    END;
END pkg_order;
/

BULK COLLECT · FORALL — 성능의 핵심

BEGIN
  FOR r IN (SELECT id FROM member WHERE grade='BASIC') LOOP
      UPDATE member SET grade='SILVER' WHERE id = r.id;
  END LOOP;
  COMMIT;
END;
/
-- 10만 건에 수 분

DECLARE
  TYPE t_ids IS TABLE OF member.id%TYPE;
  v_ids t_ids;
BEGIN
  SELECT id BULK COLLECT INTO v_ids
  FROM   member WHERE grade='BASIC';

  FORALL i IN 1 .. v_ids.COUNT
      UPDATE member SET grade='SILVER'
      WHERE  id = v_ids(i);
  COMMIT;
END;
/
-- 10만 건에 수 초

건수가 많으면 나눠서 담기 (LIMIT)

DECLARE
  CURSOR c IS SELECT id FROM member WHERE grade='BASIC';
  TYPE t_ids IS TABLE OF member.id%TYPE;
  v_ids t_ids;
BEGIN
  OPEN c;
  LOOP
    FETCH c BULK COLLECT INTO v_ids LIMIT 5000;   -- ★ 메모리 폭발 방지
    EXIT WHEN v_ids.COUNT = 0;

    FORALL i IN 1 .. v_ids.COUNT
        UPDATE member SET grade='SILVER' WHERE id = v_ids(i);
    COMMIT;                                        -- 청크마다 커밋
  END LOOP;
  CLOSE c;
END;
/

-- 일부가 실패해도 계속 진행하고 나중에 확인하기
BEGIN
  FORALL i IN 1 .. v_ids.COUNT SAVE EXCEPTIONS
      UPDATE member SET grade='SILVER' WHERE id = v_ids(i);
EXCEPTION
  WHEN OTHERS THEN
    FOR j IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP
      DBMS_OUTPUT.PUT_LINE(
        SQL%BULK_EXCEPTIONS(j).ERROR_INDEX || ' 번째 실패: ' ||
        SQLERRM(-SQL%BULK_EXCEPTIONS(j).ERROR_CODE));
    END LOOP;
END;
/

-- 힌트는 SELECT 바로 뒤 /*+ ... */ 안에 씁니다
SELECT /*+ INDEX(m idx_member_city) */ *
FROM   member m
WHERE  city = '서울';

SELECT /*+ FULL(o) PARALLEL(o, 4) */ COUNT(*)
FROM   orders o;

SELECT /*+ LEADING(m o) USE_NL(o) */ m.name, o.id
FROM   member m JOIN orders o ON o.member_id = m.id;

통계 수집

-- 특정 테이블
EXEC DBMS_STATS.GATHER_TABLE_STATS('APP', 'ORDERS');

-- 스키마 전체
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('APP');

-- 마지막 수집 시각 확인 ← 느려졌을 때 여기부터 봅니다
SELECT table_name, num_rows, last_analyzed
FROM   user_tables
ORDER  BY last_analyzed;

계획을 고정하는 방법 (힌트보다 나은 선택)

• SQL Plan Baseline — 좋은 계획을 등록해 두고 그것만 쓰게 합니다. 힌트를 코드에 박지 않아도 됩니다.
• SQL Profile — 옵티마이저의 추정을 보정합니다. SQL Tuning Advisor 가 만들어 줍니다.
• SQL Patch — 코드를 못 고치는 패키지 제품에 힌트를 외부에서 주입합니다.

① 실행계획 — 추정과 실제를 함께 보기

-- 추정 계획만 (실행하지 않음)
EXPLAIN PLAN FOR
SELECT * FROM orders WHERE member_id = 1;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- ★ 실제로 돌려 보고 추정 vs 실제 비교 (튜닝의 기본)
SELECT /*+ GATHER_PLAN_STATISTICS */ *
FROM   orders WHERE member_id = 1;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));

② 지금 뭐가 도는지 — V$ 뷰

-- 현재 활성 세션과 대기 이벤트
SELECT s.sid, s.serial#, s.username, s.status,
       s.event, s.wait_class, s.seconds_in_wait,
       q.sql_text
FROM   v$session s
LEFT   JOIN v$sql q ON q.sql_id = s.sql_id
WHERE  s.type = 'USER' AND s.status = 'ACTIVE'
ORDER  BY s.seconds_in_wait DESC;

-- 락을 기다리는 세션 (블로킹 체인)
SELECT blocking_session, sid, serial#, wait_class, seconds_in_wait
FROM   v$session
WHERE  blocking_session IS NOT NULL;

-- 문제 세션 강제 종료 (최후의 수단)
ALTER SYSTEM KILL SESSION '123,45678' IMMEDIATE;

③ AWR · ASH — 과거를 추적

# AWR 리포트 생성 (SQL*Plus 에서)
@?/rdbms/admin/awrrpt.sql        # 기간을 선택하면 HTML/텍스트 리포트 생성

# 특정 SQL 하나만
@?/rdbms/admin/awrsqrpt.sql

# ASH 리포트 (짧은 구간의 세션 샘플)
@?/rdbms/admin/ashrpt.sql

-- RANGE 파티션 + 자동 확장 (INTERVAL) ★ 실무에서 가장 많이 씁니다
CREATE TABLE orders (
    id          NUMBER,
    member_id   NUMBER,
    ordered_at  DATE NOT NULL,
    amount      NUMBER(12,2)
)
PARTITION BY RANGE (ordered_at)
INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'))     -- 새 달 데이터가 오면 파티션 자동 생성
(
    PARTITION p_2025 VALUES LESS THAN (DATE '2026-01-01')
);

-- 오래된 데이터 즉시 제거 (DELETE 와 비교할 수 없이 빠릅니다)
ALTER TABLE orders DROP PARTITION p_2025;

-- 특정 파티션만 조회
SELECT * FROM orders PARTITION (p_2025);

파티션 인덱스 — LOCAL vs GLOBAL

CREATE INDEX idx_ord_mem ON orders(member_id) LOCAL;

-- 장점: DROP PARTITION 해도 인덱스가 안 깨짐
-- 운영 부담이 적어 대부분 이쪽을 씁니다

CREATE INDEX idx_ord_mem ON orders(member_id) GLOBAL;

-- 파티션 키가 없는 조회는 빠르지만
-- DROP PARTITION 시 인덱스가 UNUSABLE 이 됩니다
-- (UPDATE INDEXES 절로 유지 가능하나 느림)

오라클의 인덱스 종류

-- 함수 기반 인덱스 (대소문자 무시 조회를 인덱스로)
CREATE INDEX idx_member_upper_name ON member (UPPER(name));
SELECT * FROM member WHERE UPPER(name) = 'KIM';    -- 이제 인덱스를 탑니다

-- 온라인으로 만들기 (서비스 중 락 최소화)
CREATE INDEX idx_ord_dt ON orders(ordered_at) ONLINE;

RMAN 기본

rman target /

RMAN> BACKUP DATABASE PLUS ARCHIVELOG;          # 전체 백업 + 아카이브 로그
RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE;      # 증분 백업
RMAN> LIST BACKUP SUMMARY;                      # 백업 목록
RMAN> VALIDATE DATABASE;                        # 백업본 검증 ← 정기적으로 하세요
RMAN> DELETE OBSOLETE;                          # 보존 정책 초과분 정리

# 복구
RMAN> RESTORE DATABASE;
RMAN> RECOVER DATABASE UNTIL TIME "TO_DATE('2026-08-05 14:00','YYYY-MM-DD HH24:MI')";

Flashback — 실수 되돌리기

-- 10분 전 데이터 보기
SELECT * FROM member AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '10' MINUTE);

-- 테이블을 통째로 되돌리기 (행 이동 허용 필요)
ALTER TABLE member ENABLE ROW MOVEMENT;
FLASHBACK TABLE member TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '10' MINUTE);

-- DROP 한 테이블 복구 (휴지통에서)
SELECT object_name, original_name FROM recyclebin;
FLASHBACK TABLE member TO BEFORE DROP;

난이도별 항목

반드시 확인할 동작 차이

도구

• ora2pg — 오라클 스키마·데이터·PL/SQL을 PostgreSQL로 변환. 변환 난이도 리포트를 먼저 뽑아 볼 수 있습니다
• AWS SCT / DMS — 클라우드 이전과 함께 갈 때
• orafce 확장 — PostgreSQL에서 오라클 함수(NVL·DECODE 등)를 흉내 내 코드 수정량을 줄입니다

언제 PostgreSQL이 아닌가

• 회사가 이미 오라클/MySQL로 돌아갈 때 — 기술적 우위보다 운영 경험이 중요합니다
• 단순 조회만 초당 수만 건 — MySQL이 더 가벼울 수 있습니다(다만 요즘은 차이가 크지 않습니다)
• 파일 하나로 끝내야 할 때 — SQLite
• DBA 없이 커넥션 수천 개를 다룰 때 — PgBouncer 같은 풀러를 반드시 붙여야 합니다

# Ubuntu · Debian — 공식 PGDG 저장소를 쓰면 최신 버전을 씁니다
sudo apt install -y postgresql-common
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh
sudo apt install -y postgresql-18

# RHEL · Rocky
sudo dnf install -y postgresql18-server
sudo /usr/pgsql-18/bin/postgresql-18-setup initdb
sudo systemctl enable --now postgresql-18

# macOS
brew install postgresql@18 && brew services start postgresql@18

# Docker
docker run -d --name pg -p 5432:5432 \
  -e POSTGRES_PASSWORD=devpass -e POSTGRES_DB=shop -e TZ=Asia/Seoul \
  -v pgdata:/var/lib/postgresql postgres:18   # 18+ 는 …/data 가 아니라 여기

중요한 파일 세 개

-- 위치를 잊었을 때 SQL 로 확인
SHOW config_file;
SHOW hba_file;
SHOW data_directory;

pg_hba.conf — 접속 규칙

# TYPE  DATABASE  USER  ADDRESS         METHOD
local   all       all                   peer          # 유닉스 소켓: OS 계정과 DB 계정이 같아야
host    all       all   127.0.0.1/32    scram-sha-256 # 로컬 TCP
host    shop      app   10.0.1.0/24     scram-sha-256 # 사설망의 app 계정만
hostssl all       all   0.0.0.0/0       scram-sha-256 # 외부는 SSL 필수로

# 위에서부터 순서대로 검사해 처음 일치하는 규칙을 씁니다 ← 순서가 중요
# 고친 뒤: sudo systemctl reload postgresql  (재시작 불필요)

CSV 입출력

# 내보내기 (클라이언트 파일로)
\copy (SELECT * FROM member WHERE grade='GOLD') TO 'gold.csv' WITH CSV HEADER

# 가져오기
\copy member FROM 'member.csv' WITH CSV HEADER

# 서버 파일 시스템을 쓰는 COPY 는 슈퍼유저 권한이 필요합니다
COPY member FROM '/tmp/member.csv' WITH (FORMAT csv, HEADER true);

셸에서 바로 실행

psql -d shop -c "SELECT count(*) FROM member;"     # 한 줄 실행
psql -d shop -f schema.sql                          # 파일 실행
psql -qtAX -d shop -c "SELECT count(*) FROM member" # 값만 (스크립트용)

# 접속 정보를 환경 변수로
export PGHOST=db.example.com PGPORT=5432 PGUSER=app PGDATABASE=shop
export PGPASSWORD=...        # 또는 ~/.pgpass 파일 (권장)

# ~/.pgpass  (권한은 반드시 600)
# host:port:database:user:password
db.example.com:5432:shop:app:strong-pass

-- 로그인 가능한 역할 = 사용자
CREATE ROLE app LOGIN PASSWORD 'strong-pass';
-- CREATE USER app PASSWORD '…' 와 같습니다

-- 로그인 못 하는 역할 = 그룹
CREATE ROLE readonly;
GRANT readonly TO analyst1, analyst2;

-- 권한 부여
GRANT CONNECT ON DATABASE shop TO app;
GRANT USAGE   ON SCHEMA public TO app;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app;
GRANT USAGE   ON ALL SEQUENCES IN SCHEMA public TO app;   -- ★ 자동증가 컬럼에 필요

-- 앞으로 만들어질 객체에도 자동 적용 (이걸 빠뜨려서 자주 막힙니다)
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT USAGE ON SEQUENCES TO app;

스키마 — 한 DB 안의 네임스페이스

CREATE SCHEMA sales AUTHORIZATION app;
CREATE TABLE sales.orders (...);

-- search_path : 스키마를 안 적었을 때 찾는 순서
SHOW search_path;                    -- "$user", public
SET search_path TO sales, public;    -- 세션 단위
ALTER ROLE app SET search_path TO sales, public;   -- 계정 기본값으로

행 수준 보안 (RLS) — 같은 표에서 자기 데이터만

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

CREATE POLICY own_orders ON orders
  USING (member_id = current_setting('app.member_id')::int);

-- 애플리케이션에서 세션마다 설정
SET app.member_id = '42';
SELECT * FROM orders;    -- 42번 회원의 주문만 보입니다

RETURNING — 바꾼 행을 바로 돌려받기

-- INSERT 하고 생성된 id 를 바로 받기 (SELECT 를 또 할 필요 없음)
INSERT INTO member (name, email) VALUES ('한바다','han@example.com')
RETURNING id, name, joined_at;

-- UPDATE 한 행이 무엇이었는지 확인
UPDATE product SET stock = stock - 1
WHERE  id = 10 AND stock > 0
RETURNING id, name, stock;
-- 0행이 돌아오면 재고가 없었다는 뜻 → 애플리케이션에서 바로 분기 가능

-- DELETE 한 내용을 로그로 남기기
DELETE FROM cart WHERE member_id = 1 RETURNING *;

UPSERT — ON CONFLICT

-- 있으면 누적, 없으면 삽입
INSERT INTO stock (product_id, qty) VALUES (10, 5)
ON CONFLICT (product_id)
DO UPDATE SET qty = stock.qty + EXCLUDED.qty, updated_at = now();
--                    ↑ 기존 행        ↑ 넣으려던 새 값

-- 이미 있으면 조용히 넘어가기
INSERT INTO tag (name) VALUES ('sale') ON CONFLICT DO NOTHING;

-- 조건부 업데이트
INSERT INTO price (product_id, amount, updated_at) VALUES (10, 45000, now())
ON CONFLICT (product_id) DO UPDATE
  SET amount = EXCLUDED.amount, updated_at = EXCLUDED.updated_at
  WHERE price.amount <> EXCLUDED.amount;      -- 값이 바뀔 때만 UPDATE

DISTINCT ON — 키별 최신 1건 (PostgreSQL 전용)

-- 회원별 가장 최근 주문 1건
SELECT DISTINCT ON (member_id) member_id, id, ordered_at
FROM   orders
ORDER  BY member_id, ordered_at DESC;     -- ★ DISTINCT ON 컬럼이 ORDER BY 맨 앞이어야 합니다

SELECT DISTINCT ON (member_id)
       member_id, id, ordered_at
FROM   orders
ORDER  BY member_id, ordered_at DESC;

SELECT member_id, id, ordered_at
FROM  (SELECT *, ROW_NUMBER() OVER
         (PARTITION BY member_id ORDER BY ordered_at DESC) rn
       FROM orders) t
WHERE  rn = 1;

배열

CREATE TABLE post (
  id    SERIAL PRIMARY KEY,
  title TEXT,
  tags  TEXT[]                       -- 문자열 배열
);
INSERT INTO post (title, tags) VALUES ('DB 입문', ARRAY['db','sql','기초']);

SELECT * FROM post WHERE 'sql' = ANY(tags);        -- 포함 검색
SELECT * FROM post WHERE tags @> ARRAY['db','sql']; -- 둘 다 포함
SELECT title, unnest(tags) AS tag FROM post;        -- 행으로 펼치기
SELECT array_length(tags, 1) FROM post;

CREATE INDEX idx_post_tags ON post USING GIN (tags);   -- 배열 검색 인덱스

범위 타입 — 구간을 값으로

CREATE EXTENSION IF NOT EXISTS btree_gist;   -- room_id WITH = 를 GiST 에서 쓰려면 필요
CREATE TABLE reservation (
  id      SERIAL PRIMARY KEY,
  room_id INT NOT NULL,
  period  TSRANGE NOT NULL,          -- 시각 구간
  -- ★ 같은 방에 겹치는 예약을 DB가 막아 줍니다
  EXCLUDE USING GIST (room_id WITH =, period WITH &&)
);

INSERT INTO reservation (room_id, period)
VALUES (1, '[2026-08-05 10:00, 2026-08-05 12:00)');

-- 겹치면 여기서 에러 → 애플리케이션 검증 없이 정합성 보장
INSERT INTO reservation (room_id, period)
VALUES (1, '[2026-08-05 11:00, 2026-08-05 13:00)');   -- ✗ 충돌

ENUM · 도메인

CREATE TYPE order_status AS ENUM ('PAID','SHIPPED','CANCELED');
CREATE TABLE orders (id SERIAL, status order_status NOT NULL DEFAULT 'PAID');

ALTER TYPE order_status ADD VALUE 'REFUNDED';    -- 값 추가는 가능 (삭제는 어려움)

-- 도메인 : 제약을 붙인 재사용 타입
CREATE DOMAIN email AS TEXT CHECK (VALUE ~ '^[^@]+@[^@]+\.[^@]+$');
CREATE TABLE member (id SERIAL, mail email);

CREATE TABLE item (
  id   SERIAL PRIMARY KEY,
  sku  TEXT UNIQUE NOT NULL,
  data JSONB NOT NULL              -- 상품마다 다른 속성들
);

INSERT INTO item (sku, data) VALUES
 ('KB-01', '{"name":"무선 키보드","brand":"LG","price":49000,
             "spec":{"switch":"적축","layout":"텐키리스"},"tags":["무선","사무용"]}');

연산자 — 이 다섯 개면 됩니다

SELECT data->>'name'                AS name,
       (data->>'price')::int         AS price,
       data#>>'{spec,switch}'        AS switch
FROM   item
WHERE  data @> '{"brand":"LG"}'
  AND  (data->>'price')::int < 60000;

-- 배열 펼치기
SELECT sku, jsonb_array_elements_text(data->'tags') AS tag FROM item;

-- 값 수정 (경로 지정)
UPDATE item SET data = jsonb_set(data, '{price}', '45000') WHERE sku = 'KB-01';

-- 키 추가 · 병합
UPDATE item SET data = data || '{"discount":10}'::jsonb WHERE sku = 'KB-01';

-- 키 삭제
UPDATE item SET data = data - 'discount' WHERE sku = 'KB-01';

인덱스

-- ① 범용 GIN — @> · ? 연산자 전부 지원 (인덱스가 큼)
CREATE INDEX idx_item_data ON item USING GIN (data);

-- ② jsonb_path_ops — @> 전용이지만 훨씬 작고 빠릅니다
CREATE INDEX idx_item_data ON item USING GIN (data jsonb_path_ops);

-- ③ 특정 키만 자주 쓴다면 표현식 B-tree 인덱스가 최선
CREATE INDEX idx_item_brand ON item ((data->>'brand'));
SELECT * FROM item WHERE data->>'brand' = 'LG';   -- ← 이 형태여야 인덱스를 탑니다

-- 기본
CREATE INDEX idx_orders_member ON orders (member_id);

-- 복합 (컬럼 순서가 성능을 좌우합니다 → ⚙️ t05)
CREATE INDEX idx_orders_mem_dt ON orders (member_id, ordered_at DESC);

-- 부분 인덱스 : 조건에 맞는 행만 → 인덱스가 훨씬 작아집니다
CREATE INDEX idx_orders_active ON orders (ordered_at)
  WHERE status <> 'CANCELED';

-- 표현식 인덱스
CREATE INDEX idx_member_lower_email ON member (lower(email));
SELECT * FROM member WHERE lower(email) = 'a@b.com';   -- 이 형태여야 탑니다

-- 커버링 인덱스 (INCLUDE · 11+) : 테이블 접근 없이 인덱스만으로 응답
CREATE INDEX idx_orders_cover ON orders (member_id) INCLUDE (ordered_at, status);

-- BRIN : 1억 행 로그 테이블에 인덱스 크기 수 MB
CREATE INDEX idx_log_ts ON log_table USING BRIN (created_at);

인덱스 점검 쿼리

-- 안 쓰이는 인덱스 찾기 (지우면 쓰기 성능이 올라갑니다)
SELECT schemaname, relname, indexrelname, idx_scan,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM   pg_stat_user_indexes
WHERE  idx_scan = 0 AND indexrelid NOT IN (SELECT conindid FROM pg_constraint)
ORDER  BY pg_relation_size(indexrelid) DESC;

-- 테이블·인덱스 크기
SELECT relname,
       pg_size_pretty(pg_total_relation_size(relid)) AS total,
       pg_size_pretty(pg_relation_size(relid))       AS table_only
FROM   pg_catalog.pg_statio_user_tables
ORDER  BY pg_total_relation_size(relid) DESC LIMIT 20;

-- 중복 인덱스 후보 (컬럼 구성이 똑같은 인덱스 · 앞부분만 겹치는 것은 따로 확인)
SELECT indrelid::regclass AS tbl, array_agg(indexrelid::regclass) AS idxs
FROM   pg_index GROUP BY indrelid, indkey HAVING count(*) > 1;

기본 전문 검색 (영어 기준)

SELECT * FROM post
WHERE  to_tsvector('english', body) @@ to_tsquery('english', 'database & index');

-- 생성 컬럼 + GIN 인덱스 (12+)
ALTER TABLE post ADD COLUMN tsv tsvector
  GENERATED ALWAYS AS (to_tsvector('english', coalesce(title,'') || ' ' || coalesce(body,''))) STORED;
CREATE INDEX idx_post_tsv ON post USING GIN (tsv);

SELECT title, ts_rank(tsv, q) AS rank
FROM   post, to_tsquery('english','database') q
WHERE  tsv @@ q ORDER BY rank DESC;

실무에서 쓰는 세 가지 방법

-- pg_trgm : LIKE '%검색어%' 를 인덱스로
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_product_name_trgm ON product USING GIN (name gin_trgm_ops);

SELECT * FROM product WHERE name LIKE '%마우스%';     -- 이제 인덱스를 탑니다

-- 유사도 검색 (오타 보정)
SELECT name, similarity(name, '마우스패드') AS sim
FROM   product
WHERE  name % '마우스패드'                            -- 기본 임계값 0.3
ORDER  BY sim DESC;

CREATE OR REPLACE FUNCTION total_amount(p_member_id INT)
RETURNS NUMERIC AS $$
DECLARE
    v_total NUMERIC;
BEGIN
    SELECT COALESCE(SUM(oi.qty * oi.unit_price), 0) INTO v_total
    FROM   orders o JOIN order_item oi ON oi.order_id = o.id
    WHERE  o.member_id = p_member_id AND o.status <> 'CANCELED';
    RETURN v_total;
EXCEPTION
    WHEN OTHERS THEN
        RAISE NOTICE '오류: %', SQLERRM;
        RAISE;      -- RETURN 0 으로 삼키면 오류가 '구매액 0' 으로 둔갑합니다
END;
$$ LANGUAGE plpgsql;

SELECT name, total_amount(id) FROM member;

여러 행 반환

CREATE OR REPLACE FUNCTION top_members(p_limit INT)
RETURNS TABLE (id INT, name TEXT, total NUMERIC) AS $$
BEGIN
    RETURN QUERY
    SELECT m.id, m.name::text, total_amount(m.id)   -- 반환 타입과 정확히 맞춰야 합니다 (varchar → text)
    FROM   member m
    ORDER  BY total_amount(m.id) DESC
    LIMIT  p_limit;
END;
$$ LANGUAGE plpgsql;

SELECT * FROM top_members(3);

트리거 — 갱신 시각 자동 기록

CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at := now();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_member_updated
BEFORE UPDATE ON member
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

감사 로그 트리거

CREATE OR REPLACE FUNCTION audit_member()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO member_audit (member_id, before_data, after_data, changed_at, changed_by)
    VALUES (COALESCE(NEW.id, OLD.id), to_jsonb(OLD), to_jsonb(NEW), now(), current_user);
    RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_member_audit
AFTER INSERT OR UPDATE OR DELETE ON member
FOR EACH ROW EXECUTE FUNCTION audit_member();

수정하면 새 행이 생깁니다

-- 죽은 행이 얼마나 쌓였나 (운영에서 정기적으로 봐야 하는 쿼리)
SELECT relname,
       n_live_tup   AS 살아있는행,
       n_dead_tup   AS 죽은행,
       ROUND(n_dead_tup * 100.0 / NULLIF(n_live_tup + n_dead_tup, 0), 1) AS 죽은비율,
       last_autovacuum, last_autoanalyze
FROM   pg_stat_user_tables
ORDER  BY n_dead_tup DESC LIMIT 20;

autovacuum 튜닝

# postgresql.conf — 기본값은 작은 서버 기준이라 대용량에서는 못 따라갑니다
autovacuum_vacuum_scale_factor = 0.05      # 기본 0.2 → 5% 만 죽어도 청소
autovacuum_vacuum_cost_limit   = 2000      # 기본 200 → 청소 속도 상향
autovacuum_naptime             = 30s
autovacuum_max_workers         = 5

# 특정 테이블만 더 공격적으로 (변경이 잦은 테이블)
ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.02);

트랜잭션 ID 랩어라운드 — 서비스가 멈추는 사고

-- 남은 여유를 확인 (2억 정도면 경고, 1000만 이하면 비상)
SELECT datname, age(datfrozenxid) AS xid_age,
       2147483648 - age(datfrozenxid) AS 남은여유
FROM   pg_database ORDER BY xid_age DESC;

-- 오래 열린 트랜잭션 찾기 (VACUUM 을 막는 주범)
SELECT pid, usename, state, now() - xact_start AS 지속시간, query
FROM   pg_stat_activity
WHERE  xact_start IS NOT NULL AND now() - xact_start > interval '5 minutes'
ORDER  BY xact_start;

-- 방치된 복제 슬롯 (WAL 이 무한정 쌓입니다)
SELECT slot_name, active, pg_size_pretty(
       pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS 밀린양
FROM   pg_replication_slots;

-- 필요하면 정리
SELECT pg_terminate_backend(12345);
SELECT pg_drop_replication_slot('old_slot');

-- 추정만 (실행 안 함)
EXPLAIN SELECT * FROM orders WHERE member_id = 1;

-- ★ 실제로 실행하고 시간·버퍼까지 (튜닝의 기본형)
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, FORMAT TEXT)
SELECT m.name, count(*)
FROM   member m JOIN orders o ON o.member_id = m.id
GROUP  BY m.name;

읽는 법 — 이 네 가지만 보세요

주요 노드 이름

슬로우 쿼리 찾기 — pg_stat_statements

-- postgresql.conf: shared_preload_libraries = 'pg_stat_statements'  (재시작 필요)
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- 총 소요 시간 기준 상위 쿼리 ← 튜닝 대상 1순위
SELECT substring(query, 1, 80) AS query,
       calls,
       ROUND(total_exec_time::numeric, 1)      AS 총시간ms,
       ROUND(mean_exec_time::numeric, 2)       AS 평균ms,
       rows
FROM   pg_stat_statements
ORDER  BY total_exec_time DESC
LIMIT  20;

SELECT pg_stat_statements_reset();   -- 통계 초기화

-- 선언적 파티셔닝 (10+)
CREATE TABLE orders (
    id          BIGSERIAL,
    member_id   INT NOT NULL,
    ordered_at  DATE NOT NULL,
    amount      NUMERIC(12,2),
    PRIMARY KEY (id, ordered_at)          -- ★ 파티션 키가 PK 에 포함돼야 합니다
) PARTITION BY RANGE (ordered_at);

CREATE TABLE orders_2026_08 PARTITION OF orders
    FOR VALUES FROM ('2026-08-01') TO ('2026-09-01');
CREATE TABLE orders_2026_09 PARTITION OF orders
    FOR VALUES FROM ('2026-09-01') TO ('2026-10-01');

-- 범위 밖 데이터를 받아 줄 기본 파티션 (없으면 INSERT 에러)
CREATE TABLE orders_default PARTITION OF orders DEFAULT;

-- 인덱스는 부모에 만들면 자식에 자동 생성됩니다
CREATE INDEX idx_orders_member ON orders (member_id);

운영

-- 오래된 파티션 분리 (즉시 · 락 최소)
-- ※ DEFAULT 파티션이 있으면 CONCURRENTLY 를 못 씁니다 → 그때는 CONCURRENTLY 를 빼고
ALTER TABLE orders DETACH PARTITION orders_2025_01 CONCURRENTLY;
DROP TABLE orders_2025_01;

-- 기존 테이블을 파티션으로 편입
ALTER TABLE orders ATTACH PARTITION orders_2026_10
      FOR VALUES FROM ('2026-10-01') TO ('2026-11-01');

-- 파티션 목록·크기
SELECT c.relname, pg_size_pretty(pg_relation_size(c.oid))
FROM   pg_class c JOIN pg_inherits i ON i.inhrelid = c.oid
WHERE  i.inhparent = 'orders'::regclass ORDER BY c.relname;

-- 설치 (서버에 패키지가 깔려 있어야 합니다)
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION IF NOT EXISTS vector;

-- 사용 가능한 확장 목록
SELECT name, default_version, installed_version, comment
FROM   pg_available_extensions
WHERE  installed_version IS NOT NULL OR name LIKE 'pg_%'
ORDER  BY name;

pgvector — AI 검색을 PostgreSQL로

CREATE EXTENSION vector;

CREATE TABLE doc (
  id        SERIAL PRIMARY KEY,
  content   TEXT,
  embedding VECTOR(1536)                  -- 임베딩 모델의 차원 수
);

-- 근사 최근접 인덱스 (HNSW · 정확도와 속도의 균형)
CREATE INDEX ON doc USING hnsw (embedding vector_cosine_ops);

-- 가장 비슷한 문서 5건 (<=> 는 코사인 거리)
SELECT id, content, embedding <=> '[0.1, 0.2, …]'::vector AS distance
FROM   doc
ORDER  BY embedding <=> '[0.1, 0.2, …]'::vector
LIMIT  5;

postgres_fdw — 다른 DB를 내 것처럼

CREATE EXTENSION postgres_fdw;
CREATE SERVER remote_db FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (host 'other.example.com', dbname 'legacy', port '5432');
CREATE USER MAPPING FOR app SERVER remote_db OPTIONS (user 'app', password '…');

IMPORT FOREIGN SCHEMA public LIMIT TO (customer)
  FROM SERVER remote_db INTO staging;

SELECT * FROM staging.customer;   -- 원격 테이블을 로컬처럼 조인 가능

스트리밍 복제 구성 (요약)

# 주 서버 (postgresql.conf)
wal_level = replica
max_wal_senders = 10
wal_keep_size = 1GB

# pg_hba.conf 에 복제 접속 허용
# host  replication  repl_user  10.0.1.0/24  scram-sha-256

# 복제본 서버에서 : 주 서버를 통째로 복사해 오기
pg_basebackup -h primary.example.com -U repl_user -D /var/lib/postgresql/18/main \
              -Fp -Xs -P -R          # -R 이 standby.signal 과 접속 정보를 만들어 줍니다

sudo systemctl start postgresql

-- 주 서버에서 : 복제 상태와 지연 확인
SELECT client_addr, state, sync_state,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) AS 지연량
FROM   pg_stat_replication;

-- 복제본에서 : 얼마나 밀렸나
SELECT now() - pg_last_xact_replay_timestamp() AS 복제지연;

-- 승격 (주 서버 장애 시)
SELECT pg_promote();

논리 복제 — 부분 복제·무중단 업그레이드

-- 주 서버 (wal_level = logical 필요)
CREATE PUBLICATION pub_orders FOR TABLE orders, order_item;

-- 구독 서버
CREATE SUBSCRIPTION sub_orders
  CONNECTION 'host=primary dbname=shop user=repl password=…'
  PUBLICATION pub_orders;

-- 상태 확인
SELECT * FROM pg_stat_subscription;

논리 백업 — pg_dump

# 커스텀 포맷 (압축·병렬 복원 가능) ★ 권장
pg_dump -h localhost -U app -d shop -Fc -f shop.dump

# 병렬 덤프 (디렉터리 포맷)
pg_dump -d shop -Fd -j 4 -f shop_dir/

# 스키마만 · 데이터만
pg_dump -d shop --schema-only -f schema.sql
pg_dump -d shop --data-only   -f data.sql

# 복원
pg_restore -d shop_new -j 4 shop.dump
createdb shop_new && pg_restore -d shop_new shop.dump

# 클러스터 전체 (역할·테이블스페이스 포함)
pg_dumpall -f all.sql
pg_dumpall --globals-only -f globals.sql   # 계정 정보만

물리 백업 + PITR — 원하는 시점으로 복구

# postgresql.conf
wal_level = replica
archive_mode = on
archive_command = 'test ! -f /backup/wal/%f && cp %p /backup/wal/%f'
# 실무에서는 pgBackRest · WAL-G · barman 같은 도구를 씁니다

# 베이스 백업
pg_basebackup -D /backup/base -Ft -z -P

# 복구 : 데이터 디렉터리를 베이스로 되돌리고
#        WAL 을 원하는 시각까지만 재생
# postgresql.conf 에
#   restore_command = 'cp /backup/wal/%f %p'
#   recovery_target_time = '2026-08-05 13:59:00'
# 그리고 recovery.signal 파일을 만든 뒤 기동

메이저 버전 업그레이드

-- 지금 값 확인
SHOW shared_buffers;
SELECT name, setting, unit, source FROM pg_settings WHERE name LIKE '%mem%';

-- 재시작 없이 반영되는 항목
ALTER SYSTEM SET work_mem = '32MB';
SELECT pg_reload_conf();

-- 재시작이 필요한 항목 (pending_restart 로 확인)
SELECT name, setting, pending_restart FROM pg_settings WHERE pending_restart;

커넥션 풀러는 선택이 아닙니다

PostgreSQL은 커넥션 하나마다 OS 프로세스를 만듭니다. 커넥션 500개면 프로세스 500개이고, 각각 메모리를 차지합니다. PgBouncer 를 앞에 두면 앱은 수천 커넥션을 쓰더라도 실제 DB 커넥션은 수십 개로 유지됩니다.

# pgbouncer.ini
[databases]
shop = host=127.0.0.1 port=5432 dbname=shop

[pgbouncer]
listen_port = 6432
pool_mode = transaction          # ★ 대부분 이것 (커넥션을 트랜잭션 단위로 재사용)
max_client_conn = 2000
default_pool_size = 25           # 실제 DB 커넥션 수

돈 문제 — "MySQL은 유료, MariaDB는 무료"가 정확히 무슨 뜻인가

• MySQL을 그냥 서버에 설치해서 쓰는 것은 무료입니다 — Community 판이 GPL 라이선스로 공개되어 있어요. 개인 프로젝트·사내 서비스·웹 서비스 백엔드로 쓰는 데는 돈이 들지 않습니다
• 돈이 드는 경우는 따로 있습니다 — 내 제품 안에 MySQL을 담아서 팔면서 소스 공개(GPL)를 하기 싫을 때, 또는 오라클의 공식 기술 지원·전용 관리 도구(Enterprise 판)가 필요할 때. 이때 오라클에서 상용 라이선스를 사야 합니다. 이렇게 무료 판과 유료 판이 같이 있는 것을 "이중 라이선스"라고 불러요
• MariaDB는 유료 판이라는 것 자체가 없습니다 — 전 기능이 GPL 하나로 공개돼 있어서 어떤 상황에서도 오라클(또는 다른 회사)에 라이선스 비용을 낼 일이 없습니다. "완전 오픈소스"를 원하는 회사·공공기관이 MariaDB를 고르는 이유가 이것입니다
• 요약 — 웹 서비스 만드는 대부분의 경우 둘 다 공짜입니다. 차이가 나는 건 "DB를 제품에 내장해 파는" 소수의 경우와 유료 기술 지원 여부예요

어느 쪽을 고를까

• 이미 쓰는 쪽이 있으면 그대로 — 전환 이득보다 리스크가 큽니다
• 클라우드 관리형을 쓴다면 — AWS RDS·Aurora, GCP Cloud SQL 모두 양쪽을 지원하지만 Aurora MySQL 호환처럼 MySQL 쪽 생태계가 더 넓습니다
• 완전 오픈소스가 중요하면 — MariaDB (GPL 단일)
• 이 가이드의 SQL — 표시가 없으면 양쪽에서 동일하게 동작합니다. 다른 부분은 MariaDB 배지로 표시했습니다

# Ubuntu · Debian
sudo apt update && sudo apt install -y mysql-server
sudo systemctl enable --now mysql
sudo mysql_secure_installation        # root 비밀번호·익명계정 제거·원격 root 차단

# RHEL · Rocky
sudo dnf install -y mysql-server
sudo systemctl enable --now mysqld
sudo grep 'temporary password' /var/log/mysqld.log   # 초기 임시 비밀번호

# MariaDB
sudo apt install -y mariadb-server
sudo systemctl enable --now mariadb
sudo mariadb-secure-installation

# macOS
brew install mysql && brew services start mysql

# Docker (문자셋을 처음부터 잡아 두세요)
docker run -d --name my -p 3306:3306 \
  -e MYSQL_ROOT_PASSWORD=devpass -e MYSQL_DATABASE=shop -e TZ=Asia/Seoul \
  -v mydata:/var/lib/mysql \
  mysql:8.4 \
  --character-set-server=utf8mb4 --collation-server=utf8mb4_0900_ai_ci

우분투의 소켓 인증

# 우분투 기본 설치는 root 가 auth_socket 인증이라 sudo 로만 접속됩니다
sudo mysql

# 비밀번호 인증으로 바꾸려면
ALTER USER 'root'@'localhost'
  IDENTIFIED WITH caching_sha2_password BY 'strong-pass';
FLUSH PRIVILEGES;

설정 파일 위치

-- 실제로 읽힌 설정 확인
SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SELECT @@version, @@version_comment;

-- 계정 만들기 : 접속 위치가 다르면 다른 계정입니다
CREATE USER 'app'@'localhost'  IDENTIFIED BY 'strong-pass';   -- 서버 자신에서만
CREATE USER 'app'@'10.0.1.%'   IDENTIFIED BY 'strong-pass';   -- 사설망에서
CREATE USER 'app'@'%'          IDENTIFIED BY 'strong-pass';   -- 어디서나 (주의)

-- 권한
GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO 'app'@'10.0.1.%';
CREATE USER 'admin'@'localhost' IDENTIFIED BY 'admin-pass';   -- 8.0+ 는 GRANT 가 계정을 만들어 주지 않습니다
GRANT ALL PRIVILEGES ON shop.* TO 'admin'@'localhost';
FLUSH PRIVILEGES;

-- 확인
SELECT user, host, plugin FROM mysql.user;
SHOW GRANTS FOR 'app'@'10.0.1.%';

-- 회수·삭제
REVOKE DELETE ON shop.* FROM 'app'@'10.0.1.%';
DROP USER 'app'@'%';

인증 플러그인

클라이언트 사용법

mysql -h 127.0.0.1 -P 3306 -u app -p shop     # ★ localhost 대신 IP 를 쓰면 TCP 로 붙습니다
mysql -u app -p -e "SELECT count(*) FROM member;" shop
mysql -u app -p shop < schema.sql              # 파일 실행

# 접속 정보를 파일로 (비밀번호를 명령줄에 노출하지 않기)
# ~/.my.cnf   (권한 600)
# [client]
# user=app
# password=strong-pass
# host=127.0.0.1

SHOW DATABASES;
USE shop;
SHOW TABLES;
SHOW CREATE TABLE member\G      -- \G 는 세로 출력 (읽기 편합니다)
DESC member;
SHOW PROCESSLIST;               -- 지금 도는 쿼리
STATUS;                         -- 접속 정보·문자셋 한눈에

-- 현재 상태 점검 (여기 있는 값이 하나라도 utf8mb3 면 손봐야 합니다)
SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';

SELECT table_schema, table_name, table_collation
FROM   information_schema.tables
WHERE  table_schema = 'shop';

SELECT table_name, column_name, character_set_name, collation_name
FROM   information_schema.columns
WHERE  table_schema = 'shop' AND character_set_name IS NOT NULL;

네 곳을 모두 맞춰야 합니다

# my.cnf
[mysqld]
character-set-server = utf8mb4
collation-server     = utf8mb4_0900_ai_ci     # MariaDB 는 utf8mb4_uca1400_ai_ci 또는 _general_ci

[client]
default-character-set = utf8mb4

-- 기존 DB·테이블 변환 (테이블이 크면 시간이 오래 걸립니다)
ALTER DATABASE shop CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

ALTER TABLE member CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
-- ★ CONVERT TO 는 데이터를 실제로 변환합니다 (테이블 재작성)
--   DEFAULT CHARACTER SET 만 바꾸면 기존 컬럼은 그대로입니다 — 흔한 실수

콜레이션 — 정렬·비교 규칙

-- MyISAM 테이블 찾기
SELECT table_schema, table_name, engine
FROM   information_schema.tables
WHERE  engine = 'MyISAM' AND table_schema NOT IN ('mysql','information_schema','performance_schema');

-- 전환 (테이블 재작성이므로 큰 테이블은 온라인 DDL 도구를 쓰세요 → m13)
ALTER TABLE old_table ENGINE = InnoDB;

InnoDB 의 구조

• 클러스터드 인덱스 — 데이터가 기본키 순서대로 디스크에 저장됩니다(m09)
• 버퍼 풀 — 메모리 캐시. 가장 중요한 설정이며 RAM의 50~70%를 줍니다(m16)
• redo 로그 — 커밋을 보장하는 로그. ib_logfile
• undo 로그 — MVCC와 롤백용 옛 버전 저장
• MVCC — 읽기가 쓰기를 막지 않습니다(🧬 z05)

-- 테이블별 크기와 행 수 (근사치)
SELECT table_name,
       engine,
       table_rows,
       ROUND(data_length /1024/1024, 1) AS 데이터MB,
       ROUND(index_length/1024/1024, 1) AS 인덱스MB
FROM   information_schema.tables
WHERE  table_schema = 'shop'
ORDER  BY data_length + index_length DESC;

-- 자동 증가
CREATE TABLE member (
  id    INT AUTO_INCREMENT PRIMARY KEY,
  name  VARCHAR(50) NOT NULL,
  email VARCHAR(120) UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO member (name) VALUES ('김하늘');
SELECT LAST_INSERT_ID();          -- 방금 생성된 id (같은 커넥션 안에서만 유효)

-- 시작값 변경
ALTER TABLE member AUTO_INCREMENT = 1000;

-- 페이징
SELECT * FROM product ORDER BY price DESC LIMIT 10;          -- 상위 10건
SELECT * FROM product ORDER BY price DESC LIMIT 20, 10;      -- 21번째부터 10건
SELECT * FROM product ORDER BY price DESC LIMIT 10 OFFSET 20; -- 위와 동일

-- NULL 처리
SELECT IFNULL(email, '(없음)')        FROM member;
SELECT COALESCE(email, phone, '(없음)') FROM member;   -- 여러 개면 COALESCE
SELECT NULLIF(a, b);
SELECT IF(grade='GOLD', '우수', '일반') FROM member;   -- MySQL 전용 IF 함수

-- 문자열
SELECT CONCAT(name, ' 님')            FROM member;    -- ★ || 는 OR 입니다
SELECT CONCAT_WS(' / ', city, grade)  FROM member;    -- 구분자로 연결 (NULL 무시)
SELECT GROUP_CONCAT(name ORDER BY id SEPARATOR ', ') FROM member;

sql_mode — 엄격 모드를 켜세요

SELECT @@sql_mode;

-- 권장 설정 (MySQL 8 기본값에 가깝습니다)
SET GLOBAL sql_mode =
 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
-- ※ 따옴표 안에 줄바꿈·공백을 넣지 마세요 — 모드 이름의 일부로 읽혀 ERROR 1231 이 날 수 있습니다

UPSERT

-- 있으면 누적, 없으면 삽입 (UNIQUE 또는 PK 가 있어야 합니다)
INSERT INTO stock (product_id, qty) VALUES (10, 5)
ON DUPLICATE KEY UPDATE qty = qty + VALUES(qty);

-- MySQL 8.0.20+ 권장 문법 (VALUES() 는 deprecated)
INSERT INTO stock (product_id, qty) VALUES (10, 5) AS new
ON DUPLICATE KEY UPDATE qty = stock.qty + new.qty;

-- 여러 행 한 번에
INSERT INTO stock (product_id, qty) VALUES (10,5),(11,3),(12,7) AS new
ON DUPLICATE KEY UPDATE qty = stock.qty + new.qty;

-- 있으면 무시
INSERT IGNORE INTO tag (name) VALUES ('sale');

GROUP_CONCAT — 여러 행을 한 줄로

SELECT m.name,
       GROUP_CONCAT(o.id ORDER BY o.id SEPARATOR ', ') AS 주문목록,
       COUNT(*) AS 건수
FROM   member m JOIN orders o ON o.member_id = m.id
GROUP  BY m.name;

그 밖의 유용한 문법

-- 여러 테이블 동시 UPDATE (조인 UPDATE)
UPDATE orders o
JOIN   member m ON m.id = o.member_id
SET    o.status = 'VIP'
WHERE  m.grade = 'GOLD';

-- 여러 테이블 동시 DELETE
DELETE oi FROM order_item oi
JOIN   orders o ON o.id = oi.order_id
WHERE  o.status = 'CANCELED';

-- INSERT ... SELECT
INSERT INTO member_archive SELECT * FROM member WHERE joined_at < '2024-01-01';

-- 정규식 (8.0+)
SELECT * FROM member WHERE name REGEXP '^김';
SELECT REGEXP_REPLACE(phone, '[^0-9]', '') FROM member;

-- JSON (8.0+)
SELECT data->>'$.brand' FROM item WHERE JSON_CONTAINS(data, '"LG"', '$.brand');

-- 시간대 설정
SELECT @@global.time_zone, @@session.time_zone;
SET time_zone = '+09:00';                       -- 세션
SET GLOBAL time_zone = 'Asia/Seoul';            -- 서버 (tzdata 로드 필요)

-- 시간대 데이터 로드 (한 번만)
-- mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql

-- 자주 쓰는 함수
SELECT NOW(), CURDATE(), CURTIME(), UTC_TIMESTAMP();
SELECT DATE_ADD(NOW(), INTERVAL 7 DAY);
SELECT DATE_SUB(NOW(), INTERVAL 1 MONTH);
SELECT DATEDIFF('2026-12-31', CURDATE())     AS 남은일수;
SELECT TIMESTAMPDIFF(HOUR, created_at, NOW()) AS 경과시간;
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');
SELECT STR_TO_DATE('2026-08-05', '%Y-%m-%d');
SELECT LAST_DAY(CURDATE())                    AS 이번달말일;
SELECT CONVERT_TZ(NOW(), '+09:00', '+00:00')  AS UTC로;

자동 갱신 컬럼

CREATE TABLE member (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  name       VARCHAR(50),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
-- UPDATE 할 때마다 updated_at 이 자동으로 갱신됩니다 (트리거 불필요)

UUID를 기본키로 쓰면 안 되는 이유

id CHAR(36) PRIMARY KEY    -- '9f8b...' 무작위

-- 문제
-- · 삽입 위치가 매번 달라 페이지 분할이 빈발
-- · 36바이트가 모든 보조 인덱스에 복사됨
-- · 버퍼 풀 효율 급락
-- 수천만 건에서 INSERT 성능이 몇 배 차이납니다

id BIGINT AUTO_INCREMENT PRIMARY KEY

-- 외부 노출용 UUID 가 필요하면 별도 컬럼
uuid BINARY(16) UNIQUE

-- 또는 시간 순 정렬되는 UUID v7 / ULID 를
-- BINARY(16) 로 저장 (문자열 X)

-- UUID 를 꼭 써야 한다면 : 문자열이 아니라 16바이트 이진으로
ALTER TABLE member ADD COLUMN uid BINARY(16) UNIQUE;
INSERT INTO member (uid) VALUES (UUID_TO_BIN(UUID(), 1));   -- 두 번째 인자 1 = 시간순 정렬
SELECT BIN_TO_UUID(uid, 1) FROM member;

기본키를 명시하지 않으면

InnoDB는 기본키가 없으면 첫 번째 NOT NULL UNIQUE 인덱스를 쓰고, 그것도 없으면 보이지 않는 6바이트 내부 키를 만듭니다. 이 경우 복제·백업·성능 모두 불리합니다. 모든 테이블에 기본키를 명시하세요.

-- 기본키 없는 테이블 찾기
SELECT t.table_schema, t.table_name
FROM   information_schema.tables t
LEFT   JOIN information_schema.table_constraints c
       ON c.table_schema = t.table_schema AND c.table_name = t.table_name
       AND c.constraint_type = 'PRIMARY KEY'
WHERE  t.table_schema = 'shop' AND c.constraint_name IS NULL;

EXPLAIN SELECT * FROM orders WHERE member_id = 1;

-- 8.0.18+ : 실제로 실행하고 실측치까지
EXPLAIN ANALYZE
SELECT m.name, COUNT(*) FROM member m JOIN orders o ON o.member_id = m.id GROUP BY m.name;

-- JSON 형식 (비용 정보 포함)
EXPLAIN FORMAT=JSON SELECT ...;

type — 좋은 순서대로

Extra — 여기 나오면 주의

옵티마이저 힌트 (8.0+)

SELECT /*+ INDEX(orders idx_ord_member) */ * FROM orders WHERE member_id = 1;
SELECT /*+ JOIN_ORDER(m, o) */ ...;
SELECT /*+ MAX_EXECUTION_TIME(1000) */ ...;      -- 1초 넘으면 중단 ← 유용합니다

-- 구식 인덱스 힌트도 여전히 동작합니다
SELECT * FROM orders USE INDEX (idx_ord_member) WHERE member_id = 1;
SELECT * FROM orders FORCE INDEX (idx_ord_member) WHERE member_id = 1;
SELECT * FROM orders IGNORE INDEX (idx_bad) WHERE member_id = 1;

통계와 슬로우 쿼리

ANALYZE TABLE orders;         -- 통계 갱신 (계획이 이상할 때 먼저)

-- 슬로우 쿼리 로그 켜기
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;                        -- 1초 이상
SET GLOBAL log_queries_not_using_indexes = 'ON';       -- 인덱스 미사용도 기록

-- performance_schema 로 상위 쿼리 뽑기 (sys 스키마가 편합니다)
-- x$ 뷰는 피코초 원본 숫자 (statement_analysis 는 '1.23 ms' 같은 문자열이라 계산 불가)
SELECT query, exec_count, ROUND(avg_latency/1e12, 3) AS 평균초,
       ROUND(total_latency/1e12, 1) AS 총초, rows_examined_avg
FROM   sys.x$statement_analysis
ORDER  BY total_latency DESC LIMIT 20;

-- 인덱스를 못 쓴 쿼리
SELECT * FROM sys.statements_with_full_table_scans LIMIT 20;

-- id 가 10, 20, 30 인 행만 있을 때
BEGIN;
SELECT * FROM t WHERE id BETWEEN 15 AND 25 FOR UPDATE;
-- 20 행에 레코드 락 + (10,20) (20,30) 구간에 갭 락
-- → 다른 세션이 id=17 을 INSERT 하려 하면 대기합니다 (존재하지도 않는 행인데!)

데드락 진단

-- 마지막 데드락 상세 (가장 중요한 명령)
SHOW ENGINE INNODB STATUS\G
-- 출력의 "LATEST DETECTED DEADLOCK" 섹션을 보세요

-- 지금 대기 중인 락 (8.0+)
SELECT r.trx_id AS 대기중, r.trx_mysql_thread_id AS 대기스레드, r.trx_query AS 대기쿼리,
       b.trx_id AS 차단중, b.trx_mysql_thread_id AS 차단스레드, b.trx_query AS 차단쿼리
FROM   performance_schema.data_lock_waits w
JOIN   information_schema.innodb_trx r ON r.trx_id = w.requesting_engine_transaction_id
JOIN   information_schema.innodb_trx b ON b.trx_id = w.blocking_engine_transaction_id;

-- 오래 열린 트랜잭션
SELECT trx_id, trx_state, trx_started,
       TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS 지속초, trx_query
FROM   information_schema.innodb_trx
ORDER  BY trx_started;

-- 강제 종료
KILL 12345;

데드락을 줄이는 네 가지

• 접근 순서를 통일하세요. 모든 트랜잭션이 A → B 순서로 잠그면 데드락이 원천적으로 안 생깁니다. 가장 효과적인 대책입니다.
• 트랜잭션을 짧게. 외부 API 호출·사용자 입력 대기를 트랜잭션 안에 두지 마세요.
• 적절한 인덱스. 인덱스가 없으면 훨씬 넓은 범위에 락이 걸립니다. WHERE status = 'X' 에 인덱스가 없으면 사실상 테이블 전체가 잠깁니다.
• 재시도를 코드에 넣으세요. 데드락은 완전히 없앨 수 없습니다. MySQL이 한쪽을 자동으로 롤백시키므로, 애플리케이션이 1~3회 재시도하면 대부분 성공합니다(🔌 a11).

-- 락 대기 시간 설정
SELECT @@innodb_lock_wait_timeout;      -- 기본 50초 — 너무 깁니다
SET SESSION innodb_lock_wait_timeout = 5;

-- 대기하지 않고 즉시 실패 / 잠긴 행은 건너뛰기 (8.0+)
SELECT * FROM orders WHERE id = 1 FOR UPDATE NOWAIT;
SELECT * FROM job_queue WHERE status='READY' LIMIT 10 FOR UPDATE SKIP LOCKED;

DELIMITER $$
-- ↑ 프로시저 안에 세미콜론이 있어서 구분자를 잠시 바꿉니다

CREATE PROCEDURE grade_up(IN p_id INT, OUT p_result VARCHAR(50))
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_result = '실패';
    END;

    START TRANSACTION;
      UPDATE member SET grade = 'GOLD' WHERE id = p_id;
      IF ROW_COUNT() = 0 THEN
          SET p_result = '대상 없음';
      ELSE
          SET p_result = '성공';
      END IF;
    COMMIT;
END$$

DELIMITER ;

CALL grade_up(3, @r);
SELECT @r;

트리거

DELIMITER $$
CREATE TRIGGER trg_member_audit
AFTER UPDATE ON member
FOR EACH ROW
BEGIN
    INSERT INTO member_audit (member_id, old_grade, new_grade, changed_at, changed_by)
    VALUES (OLD.id, OLD.grade, NEW.grade, NOW(), CURRENT_USER());
END$$
DELIMITER ;

SHOW TRIGGERS;
DROP TRIGGER trg_member_audit;

이벤트 스케줄러 — DB 안의 크론

SET GLOBAL event_scheduler = ON;    -- MySQL 8.0+ 는 기본 ON · 5.7 · MariaDB 는 기본 OFF

DELIMITER $$
CREATE EVENT ev_purge_log
ON SCHEDULE EVERY 1 DAY STARTS '2026-08-06 03:00:00'
DO
BEGIN
    DELETE FROM log_table WHERE created_at < NOW() - INTERVAL 90 DAY LIMIT 10000;
END$$
DELIMITER ;

SHOW EVENTS;

온라인 DDL — ALGORITHM 과 LOCK

-- 무엇이 가능한지 명시적으로 요구하고, 안 되면 에러를 받으세요
ALTER TABLE orders
  ADD COLUMN memo VARCHAR(200),
  ALGORITHM=INSTANT;              -- 즉시 (8.0+ · 메타데이터만 변경)

ALTER TABLE orders
  ADD INDEX idx_dt (ordered_at),
  ALGORITHM=INPLACE, LOCK=NONE;   -- 테이블 재작성 없이 · 읽기·쓰기 허용

-- ALGORITHM 을 안 적으면 MySQL 이 알아서 고르는데
-- COPY 가 선택되면 테이블 전체를 복사하며 오래 잠깁니다

대용량 스키마 변경 도구

pt-online-schema-change \
  --alter "ADD COLUMN memo VARCHAR(200)" \
  D=shop,t=orders \
  --max-load "Threads_running=25" \
  --critical-load "Threads_running=50" \
  --execute        # 먼저 --dry-run 으로 확인하세요

파티셔닝

CREATE TABLE orders (
    id         BIGINT AUTO_INCREMENT,
    member_id  INT NOT NULL,
    ordered_at DATE NOT NULL,
    PRIMARY KEY (id, ordered_at)          -- ★ 파티션 키가 모든 유니크 키에 포함돼야 합니다
)
PARTITION BY RANGE (TO_DAYS(ordered_at)) (
    PARTITION p2026_07 VALUES LESS THAN (TO_DAYS('2026-08-01')),
    PARTITION p2026_08 VALUES LESS THAN (TO_DAYS('2026-09-01')),
    PARTITION pmax     VALUES LESS THAN MAXVALUE
);

ALTER TABLE orders DROP PARTITION p2026_07;      -- 즉시 삭제
ALTER TABLE orders REORGANIZE PARTITION pmax INTO (
    PARTITION p2026_09 VALUES LESS THAN (TO_DAYS('2026-10-01')),
    PARTITION pmax     VALUES LESS THAN MAXVALUE
);

# 소스(Primary) my.cnf
server-id        = 1
log_bin          = mysql-bin
binlog_format    = ROW              # ★ ROW 를 쓰세요 (STATEMENT 는 비결정적 함수에서 어긋납니다)
gtid_mode        = ON
enforce_gtid_consistency = ON

# 레플리카 my.cnf
server-id        = 2
read_only        = ON
super_read_only  = ON               # 관리자도 실수로 못 쓰게

-- 소스에서 복제 계정
CREATE USER 'repl'@'10.0.1.%' IDENTIFIED BY 'repl-pass';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.0.1.%';

-- 레플리카에서 (8.0.23+ 용어)
CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='10.0.1.10', SOURCE_USER='repl', SOURCE_PASSWORD='repl-pass',
  SOURCE_AUTO_POSITION=1,
  GET_SOURCE_PUBLIC_KEY=1;   -- caching_sha2_password 계정을 SSL 없이 쓸 때 필요
START REPLICA;

-- 상태 확인 ★ 가장 중요한 명령
SHOW REPLICA STATUS\G
--   Replica_IO_Running: Yes
--   Replica_SQL_Running: Yes
--   Seconds_Behind_Source: 0      ← 이 값이 복제 지연(초)

지연 모니터링

-- Seconds_Behind_Source 는 부정확할 때가 있습니다
-- 하트비트 테이블을 두고 실제 지연을 재는 방식이 정확합니다 (pt-heartbeat)
SELECT TIMESTAMPDIFF(SECOND, ts, NOW()) AS 실제지연초 FROM heartbeat ORDER BY ts DESC LIMIT 1;
-- (NOW() - ts 는 MySQL 에서 초가 아니라 YYYYMMDDhhmmss 숫자끼리의 뺄셈이 됩니다)

-- 병렬 복제로 지연 줄이기 (8.0+)
SET GLOBAL replica_parallel_type = 'LOGICAL_CLOCK';
SET GLOBAL replica_parallel_workers = 8;
SET GLOBAL replica_preserve_commit_order = ON;