🗄️
데이터베이스 설계 & 튜닝 명령어 치트시트
이 트랙 37개 모듈의 핵심 명령어·단축키 441개를 한 곳에 모았습니다. 실무 중 “그 명령어 뭐였지?” 할 때 검색해 바로 꺼내 쓰세요. 각 모듈 제목을 누르면 해당 강의로 이동합니다.
441개 명령 · 37개 모듈
| 구문/명령 | 용도 | 예 |
|---|---|---|
BEGIN / COMMIT / ROLLBACK | 트랜잭션 경계(원자성) | 이체: 두 UPDATE를 한 단위로 묶기 |
UPDATE ... SET ... | 데이터 변경 | UPDATE accounts SET balance = balance - 100000 WHERE id=1 |
CREATE TABLE + 제약 | 무결성 규칙을 테이블에 정의 | PRIMARY KEY·NOT NULL·UNIQUE·CHECK 조합 |
CHECK (...) | 잘못된 값 차단 | age INT CHECK (age >= 0 AND age <= 150) |
UNIQUE / PRIMARY KEY | 중복·식별자 제약 | email VARCHAR(255) UNIQUE NOT NULL |
CREATE INDEX | 검색 성능(O(log n)) | CREATE INDEX idx_users_email ON users(email) |
EXPLAIN | Seq Scan vs Index Scan 확인 | EXPLAIN SELECT * FROM users WHERE email = ... |
원자적 UPDATE ... WHERE 조건 | lost update 방지(재고 차감) | UPDATE products SET stock = stock - 1 WHERE id = ? AND stock > 0 |
SELECT ... FOR UPDATE | 읽는 행을 잠그고 갱신 | 강한 직렬화가 필요할 때 |
낙관적 락(version) | 충돌 감지 후 재시도 | UPDATE ... SET version = version + 1 WHERE id = ? AND version = 7 |
| 구문/명령 | 용도 | 예 |
|---|---|---|
CREATE TABLE | 엔티티를 테이블로 생성 | 부모(참조 대상)부터 순서대로 생성 |
SERIAL PRIMARY KEY | 자동 증가 기본키 | id SERIAL PRIMARY KEY |
REFERENCES (FK) | 1:N 관계선을 외래키로 구현 | category_id INT NOT NULL REFERENCES categories(id) |
ON DELETE CASCADE / SET NULL | 부모 삭제 시 자식 처리 정책 | REFERENCES orders(id) ON DELETE CASCADE |
| 자기참조 FK | 계층(트리) 구조 표현 | parent_id INT REFERENCES categories(id) |
UNIQUE(col) | 1:1·중복 방지 | email VARCHAR(255) UNIQUE NOT NULL |
복합 UNIQUE(a, b) | N:M 중간 테이블 중복 방지 | UNIQUE(order_id, product_id) |
CHECK (...) | 값 범위·집합 제약 | CHECK (price >= 0), CHECK (status IN ('pending', ...)) |
NOT NULL / DEFAULT | 필수값·기본값 | status VARCHAR(20) NOT NULL DEFAULT 'pending' |
\d table (psql) | PK·FK·UNIQUE 제약 확인 | \d order_items(제약이 모두 걸렸는지 검증) |
CREATE INDEX ON child(fk_col) | 자식 FK 컬럼 인덱스(삭제 성능) | CREATE INDEX ON products(category_id) |
| 구문/명령 | 용도 | 예 |
|---|---|---|
CREATE SCHEMA | DB 안에 네임스페이스 생성 | CREATE SCHEMA analytics; → analytics.users로 이름 충돌 격리 |
CREATE TABLE | 테이블 정의(컬럼·제약) | CREATE TABLE products (id SERIAL PRIMARY KEY, price DECIMAL(12,2) CHECK (price >= 0)); |
SERIAL PRIMARY KEY | 자동 증가 기본키 | id SERIAL PRIMARY KEY (nextval로 자동 채움) |
REFERENCES ... ON DELETE | 외래키 + 삭제 정책 | category_id INT REFERENCES categories(id) ON DELETE RESTRICT |
NOT NULL / UNIQUE / DEFAULT / CHECK | 컬럼 제약 | status VARCHAR(20) NOT NULL DEFAULT 'active' CHECK (status IN ('active','inactive')) |
CREATE INDEX | 조회 컬럼 인덱스 | CREATE INDEX idx_products_status ON products(status); (대형 테이블은 CREATE INDEX CONCURRENTLY) |
\d / DESCRIBE | 테이블 구조 확인 | PostgreSQL \d products / MySQL DESCRIBE products; |
ALTER TABLE ... ADD COLUMN | 컬럼 추가 | ALTER TABLE products ADD COLUMN weight_kg DECIMAL(8,3); (nullable은 거의 즉시) |
ALTER TABLE ... RENAME COLUMN | 컬럼 이름 변경 | ALTER TABLE products RENAME COLUMN description TO product_description; |
ALTER TABLE ... ALTER COLUMN | 타입·기본값·NOT NULL 변경 | 안전 3단계: SET DEFAULT 0 → UPDATE ... WHERE col IS NULL → SET NOT NULL |
ALTER TABLE ... ADD/DROP CONSTRAINT | 제약 추가·삭제 | ALTER TABLE products ADD CONSTRAINT chk_price CHECK (price >= 0); |
DROP TABLE | 테이블·데이터 삭제 | DROP TABLE IF EXISTS products; / FK 참조까지 DROP TABLE products CASCADE; |
TRUNCATE TABLE | 구조 유지, 데이터 전체 삭제 | TRUNCATE TABLE products RESTART IDENTITY; (DELETE보다 빠름) |
SET lock_timeout | DDL 락 대기 상한(무중단 마이그레이션) | 마이그레이션 앞에 SET lock_timeout = '3s'; |
| 구문/명령 | 용도 | 예 |
|---|---|---|
INT vs BIGINT | 정수 범위 선택(오버플로 예방) | 대량 PK·카운터는 BIGINT(약 ±922경) |
SERIAL / BIGSERIAL | 자동 증가 PK | id BIGSERIAL PRIMARY KEY |
DECIMAL(p, s) / NUMERIC | 금액 등 정확한 십진 계산 | amount DECIMAL(15, 2)(금액에 FLOAT 금지) |
FLOAT / DOUBLE PRECISION | 근사값 허용 과학·통계값 | temperature DOUBLE PRECISION |
CHAR(n) / VARCHAR(n) / TEXT | 고정·가변·무제한 문자열 | country_code CHAR(2), name VARCHAR(100), description TEXT |
DATE / TIMESTAMP / TIMESTAMPTZ | 날짜·시간·타임존 인식 시각 | 생성시각은 TIMESTAMPTZ(UTC 저장·세션 변환) |
SET TIME ZONE | 세션 타임존 변경(변환 확인) | SET TIME ZONE 'America/New_York' |
EXTRACT / INTERVAL / age() | 날짜 연산 | birthdate + INTERVAL '18 years', age(birthdate) |
DATE_TRUNC('month', ...) | 기간 단위 절삭 후 GROUP BY | DATE_TRUNC('month', created_at) |
BOOLEAN / JSONB | 참·거짓 / 인덱스 가능한 JSON | is_active BOOLEAN DEFAULT TRUE, metadata JSONB |
CREATE TYPE ... AS ENUM | 값을 특정 문자열 집합으로 제한 | CREATE TYPE order_status AS ENUM ('pending', ...) |
UUID (v4 vs v7) | 전역 고유 ID(삽입 패턴 주의) | 순차형 UUID v7/ULID로 무작위 삽입 회피 |
| 구문/명령 | 용도 | 예 |
|---|---|---|
BIGSERIAL PRIMARY KEY | 자동 증가 정수 대리키 | id BIGSERIAL PRIMARY KEY |
UUID ... DEFAULT gen_random_uuid() | 분산 환경 대리키 | id UUID PRIMARY KEY DEFAULT gen_random_uuid() |
... NOT NULL UNIQUE | 비즈니스 식별자 별도 관리 | email VARCHAR(255) NOT NULL UNIQUE |
PRIMARY KEY (a, b) | 복합 PK(M:N 중간 테이블) | PRIMARY KEY (user_id, role_id) |
FOREIGN KEY ... REFERENCES | 참조 무결성(고아 레코드 방지) | FOREIGN KEY (user_id) REFERENCES users(id) |
ON DELETE RESTRICT/CASCADE/SET NULL | 부모 삭제 시 자식 처리 전파 | REFERENCES users(id) ON DELETE CASCADE |
ON UPDATE CASCADE | 부모 PK 변경 시 자식 연쇄 갱신 | 자연키 변경 비용 대응 |
ALTER TABLE ADD/DROP CONSTRAINT | 기존 테이블에 FK 추가·재선언 | ADD CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users(id) |
DEFERRABLE INITIALLY DEFERRED | 커밋 시점에만 FK 검사(순환 참조) | SET CONSTRAINTS fk_manager DEFERRED |
soft delete (deleted_at) | CASCADE 대신 논리 삭제 | UPDATE users SET deleted_at = NOW() 후 WHERE deleted_at IS NULL |
CREATE INDEX ON child(fk_col) | 자식 FK 인덱스(부모 삭제 지연 방지) | CREATE INDEX idx_orders_user_id ON orders(user_id) |
DELETE ... WHERE NOT EXISTS (...) | NULL 영향을 피한 고아 레코드 정리(검토·백업 후) | DELETE FROM orders o WHERE NOT EXISTS (SELECT 1 FROM users u WHERE u.id = o.user_id) |
| 구문/명령 | 용도 | 예 |
|---|---|---|
CREATE TABLE ... PRIMARY KEY | 정규화로 분리한 테이블 정의 | CREATE TABLE professors (id INT PRIMARY KEY, ...); |
복합 PK PRIMARY KEY (a, b) | 연결(교차) 테이블의 키 | PRIMARY KEY (order_id, product_id) (enrollments) |
REFERENCES 부모(컬럼) | 분리한 테이블을 FK로 연결 | zip_code VARCHAR(10) REFERENCES zip_codes(zip_code) |
COUNT(DISTINCT 컬럼) + HAVING | 이행 종속 중복 진단(한 키에 값 2개) | GROUP BY zip_code HAVING COUNT(DISTINCT city) > 1 |
COUNT(*) + GROUP BY + ORDER BY | 중복 저장 규모(낭비) 측정 | GROUP BY zip_code, city ORDER BY dup_rows DESC |
| 역정규화 복제 컬럼 | 읽기용 JOIN 제거(중복 의도 허용) | orders에 customer_name·customer_email 복사 |
| Materialized View | 미리 JOIN한 결과를 캐싱 | 과도한 JOIN의 읽기 전용 대안 |
EXPLAIN ANALYZE | 역정규화 전 병목 먼저 측정 | JOIN 병목 확인 후 선택적 역정규화 |
| 구문/명령 | 용도 | 예 |
|---|---|---|
SELECT … FROM | 열·테이블 지정 조회 | SELECT id, name, email FROM users |
WHERE | 행 필터 조건 | WHERE deleted_at IS NULL AND role = 'admin' |
BETWEEN a AND b | 범위(경계값 포함) | WHERE price BETWEEN 10000 AND 50000 |
IN (…) | 목록 중 하나 | WHERE status IN ('pending', 'shipped') |
LIKE | 문자열 패턴(접두사만 인덱스) | WHERE name LIKE '김%' |
ORDER BY … LIMIT / OFFSET | 정렬·페이지네이션 | ORDER BY created_at DESC LIMIT 10 OFFSET 40 |
DISTINCT | 중복 제거 | SELECT DISTINCT category FROM products |
INSERT INTO … VALUES | 단건·다건 삽입 | INSERT INTO users (name, email) VALUES ('홍','h@x.com'), ('김','k@x.com') |
ON CONFLICT DO … | 중복 시 무시/갱신 | INSERT … ON CONFLICT (email) DO NOTHING |
… RETURNING | 삽입 행 값 즉시 반환 | INSERT … RETURNING id |
UPDATE … SET … WHERE | 조건부 수정(WHERE 필수) | UPDATE users SET email = ? WHERE id = 42 |
DELETE FROM … WHERE | 조건부 삭제 | DELETE FROM logs WHERE created_at < NOW() - INTERVAL '90 days' |
TRUNCATE / DROP TABLE | 전체 행 즉시 삭제 / 테이블 제거 | TRUNCATE TABLE temp_import |
BEGIN / COMMIT / ROLLBACK | 트랜잭션으로 안전 실행 | BEGIN; UPDATE …; SELECT …; COMMIT; |
| 구문/명령 | 용도 | 예 |
|---|---|---|
BEGIN / START TRANSACTION | 트랜잭션 시작 | BEGIN; (MySQL은 START TRANSACTION;도 가능) |
COMMIT | 변경 확정 | 출금·입금 두 UPDATE를 묶은 뒤 COMMIT; |
ROLLBACK | 변경 전체 취소 | UPDATE가 0행이면 COMMIT 대신 ROLLBACK; |
SAVEPOINT | 트랜잭션 중간 체크포인트 | SAVEPOINT items_added; |
ROLLBACK TO SAVEPOINT | 특정 지점까지만 부분 롤백 | ROLLBACK TO SAVEPOINT items_added; (주문은 유지, 쿠폰 UPDATE만 취소) |
CHECK 제약 | 일관성(C)을 DB 레벨에서 강제 | CONSTRAINT chk_balance_positive CHECK (balance >= 0) |
SET SESSION TRANSACTION ISOLATION LEVEL | 세션 격리 수준 지정 | SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; |
BEGIN ISOLATION LEVEL ... | 트랜잭션 단위 격리 지정 | BEGIN ISOLATION LEVEL SERIALIZABLE; |
SELECT ... FOR UPDATE | 조회 행 잠금(이중 결제 방지) | SELECT balance FROM accounts WHERE id=1 FOR UPDATE; |
SHOW TRANSACTION ISOLATION LEVEL | 현재 격리 수준 확인 | 의도와 다르면 명시적 BEGIN ISOLATION LEVEL ... 사용 |
LASTVAL() | 직전 INSERT의 시퀀스 값 | INSERT INTO order_items (...) VALUES (LASTVAL(), 42, 2); |
synchronous_commit | 커밋 내구성(D) 강도 조절 | 기본 on(WAL flush 대기) vs off(최신 커밋 유실 위험) |
| 구문/명령 | 용도 | 예 |
|---|---|---|
CREATE INDEX | 기본 B-Tree 인덱스 생성 | CREATE INDEX idx_users_email ON users (email); |
CREATE UNIQUE INDEX | 중복 방지 + 조회 가속 동시 확보 | CREATE UNIQUE INDEX idx_users_email ON users (email); |
복합 인덱스 (a, b, c) | 선두 컬럼 규칙 — 앞 컬럼부터 사용 | CREATE INDEX ... ON orders (user_id, status, created_at DESC); |
부분 인덱스 ... WHERE | 특정 조건 행만 인덱싱 | CREATE INDEX ... ON orders (created_at) WHERE status = 'pending'; |
... (col DESC) | 내림차순 정렬 쿼리 가속 | CREATE INDEX ... ON posts (created_at DESC); |
CREATE INDEX CONCURRENTLY | 운영 중 테이블 락 없이 생성 | 대용량 테이블 무중단 인덱스 추가 |
USING gin(... gin_trgm_ops) | 전방 와일드카드(%키워드) 검색용 | CREATE INDEX idx_name_trgm ON products USING gin(name gin_trgm_ops); |
EXPLAIN ANALYZE | Seq Scan → Index Scan 전환 확인 | EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'a@b.c'; |
\d 테이블 / pg_indexes | 테이블의 인덱스 목록·정의 확인 | SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'users'; |
pg_stat_user_indexes(idx_scan) | 미사용 인덱스(삭제 후보) 탐지 | ... WHERE idx_scan = 0 |
REINDEX INDEX CONCURRENTLY | bloat 인덱스 온라인 재구축 | REINDEX INDEX CONCURRENTLY idx_users_email; |
DROP INDEX | 미사용 인덱스 제거 | DROP INDEX idx_old_unused_index; |
fillfactor | 갱신 잦은 테이블 페이지 여유 확보 | 페이지 분할·index bloat 예방 |
| 구문/명령 | 용도 | 예 |
|---|---|---|
UUID PRIMARY KEY DEFAULT gen_random_uuid() | Write 모델 분산 친화 PK | id UUID PRIMARY KEY DEFAULT gen_random_uuid() |
REFERENCES (FK) + CHECK | 정규화된 Write DB 무결성 | order_id UUID REFERENCES orders(id), CHECK (quantity > 0) |
TEXT[] 배열 컬럼 | 비정규화 Read 모델에 미리 합침 | product_names TEXT[](JOIN 없이 조회) |
CREATE MATERIALIZED VIEW | 조회용 스냅샷 사전 집계 | CREATE MATERIALIZED VIEW order_summary AS SELECT ... |
REFRESH MATERIALIZED VIEW CONCURRENTLY | 잠금 최소로 뷰 갱신 | 소규모 팀 CQRS 시작점 |
CREATE PUBLICATION ... FOR TABLE | 논리 복제 발행자(Write DB) | CREATE PUBLICATION orders_pub FOR TABLE orders, order_items |
CREATE SUBSCRIPTION ... PUBLICATION | 논리 복제 구독자(Read DB) | CREATE SUBSCRIPTION orders_sub CONNECTION '...' PUBLICATION orders_pub |
JOIN / LEFT JOIN + GROUP BY | Read 뷰 구성용 집계 | JOIN users ... LEFT JOIN order_items ... GROUP BY ... |
pg_replication_lag | 복제 지연(stale read) 감시 | 지연 임계 초과 시 Read 라우팅 차단 |
| 구문/명령 | 용도 | 예 |
|---|---|---|
psql "postgresql://..." | 연결 문자열 한 줄로 접속 | psql "postgresql://readonly:pass@prod-db:5432/mydb?sslmode=require" |
\l / \c | DB 목록 / 다른 DB로 연결 전환 | \l 후 \c shop_db |
\dt | 현재 DB 테이블 목록 | \dt public.* (스키마 전체) |
\d 테이블 | 컬럼·타입·인덱스·제약 구조 보기 | \d users |
\di \dv \df \du \dn | 인덱스·뷰·함수·롤·스키마 목록 | \di(인덱스), \du(Role), \dn(스키마) |
\timing | 쿼리 실행 시간 측정 ON/OFF | \timing 켜고 SELECT 실행 |
\COPY | 클라이언트 파일로 CSV 입출력 | \COPY orders TO '/tmp/o.csv' WITH CSV HEADER; |
\i \e \o | SQL 파일 실행·에디터 편집·결과 저장 | \i migrate.sql, \o out.txt |
\r | 입력 중인 쿼리 버퍼 리셋(Ctrl+C 대신) | 멀티라인 취소는 \r, 종료는 \q |
EXPLAIN ANALYZE | 실행계획+실측 시간으로 Seq Scan 진단 | EXPLAIN ANALYZE SELECT ... |
pg_size_pretty(pg_total_relation_size()) | 테이블+인덱스 총 크기 확인 | SELECT pg_size_pretty(pg_total_relation_size('orders')); |
pg_stat_activity | 실행 중·대기 쿼리, idle in transaction 확인 | SELECT * FROM pg_stat_activity WHERE state = 'idle in transaction'; |
pgcli / mycli | 자동완성·구문강조 강화 CLI | pgcli postgresql://.../mydb, mycli -u root mydb |
| 구문/명령 | 용도 | 예 |
|---|---|---|
INNER JOIN ... ON | 양쪽 매칭되는 행만(교집합) | FROM customers c INNER JOIN orders o ON c.id = o.customer_id |
LEFT JOIN ... ON | 왼쪽 전체 보존, 없으면 NULL | 주문 없는 고객도 목록에 포함 |
RIGHT JOIN / FULL OUTER JOIN | 오른쪽 전체 / 양쪽 전체(합집합) | FULL은 마이그레이션 불일치 감사에 유용 |
CROSS JOIN | 모든 행 조합(카테시안 곱) | FROM sizes s CROSS JOIN colors c (SKU 조합표) |
| SELF JOIN(별칭 2개) | 같은 테이블로 계층 관계 조회 | FROM employees e LEFT JOIN employees m ON e.manager_id = m.id |
USING (컬럼) | 조인 컬럼명이 같을 때 ON 간결화 | JOIN orders o USING (customer_id) — 컬럼 1번만 출력 |
필터를 ON에 두기 | LEFT JOIN에서 NULL 행 보존 | LEFT JOIN orders o ON c.id = o.customer_id AND o.order_date >= '2024-01-01' |
WHERE 오른쪽.id IS NULL | 안티 조인 — 매칭 없는 행만 추출 | 한 번도 주문 안 한 고객 뽑기 |
COALESCE(SUM(...), 0) + GROUP BY | LEFT JOIN 집계에서 NULL을 0으로 | COALESCE(SUM(o.amount), 0) 총구매금액 |
| 다중 JOIN 체이닝 | 3개 이상 테이블 결합 | orders → customers → order_items → products 순 JOIN |
| FK 컬럼 인덱스 | JOIN을 O(log N)으로 가속 | CREATE INDEX idx_orders_customer_id ON orders(customer_id); |
EXPLAIN ANALYZE | 조인 알고리즘·loops 확인 | Nested Loop / Hash / Merge 판별 |
| 구문/명령 | 용도 | 예 |
|---|---|---|
스칼라 서브쿼리(SELECT절) | 행마다 값 하나 계산 | SELECT name, (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) FROM users u |
인라인 뷰(FROM절) | 서브쿼리 결과를 테이블처럼 | FROM (SELECT category_id, AVG(price) FROM products GROUP BY 1) t |
IN (서브쿼리) | 목록 포함 필터 | WHERE customer_id IN (SELECT customer_id FROM orders WHERE …) |
EXISTS | 존재 여부(첫 행에서 중단) | WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id) |
NOT EXISTS | 제외(NULL 안전·안티조인) | WHERE NOT EXISTS (SELECT 1 FROM blacklist b WHERE b.customer_id = o.customer_id) |
NOT IN | 제외(서브쿼리 NULL 주의) | WHERE id NOT IN (SELECT id FROM …) — 컬럼이 NOT NULL일 때만 |
WITH name AS (…) | CTE로 단계 분리·이름 부여 | WITH active AS (SELECT …) SELECT * FROM active |
| 다단계 CTE(쉼표 연결) | 앞 CTE를 뒤 CTE가 참조 | WITH a AS (…), b AS (SELECT … FROM a) SELECT … |
WITH RECURSIVE | 계층형(조직도·트리) 순회 | WITH RECURSIVE t AS (앵커 UNION ALL 재귀 JOIN t) SELECT … |
NOT MATERIALIZED | CTE 인라인 최적화 허용(PG12+) | WITH r AS NOT MATERIALIZED (…) SELECT … WHERE … |
LEFT JOIN … IS NULL | 안티조인(제외) | LEFT JOIN w ON w.cid = c.cid WHERE w.cid IS NULL |
AVG(…) OVER (PARTITION BY …) | 상관 서브쿼리 대체(단일 패스) | AVG(salary) OVER (PARTITION BY department_id) |
| 구문/명령 | 용도 | 예 |
|---|---|---|
| 범위 조건(함수 래핑 대신) | 인덱스 타는 날짜 필터 | WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01' (YEAR(created_at)=2024 대체) |
| 표현식 인덱스 | 함수 조건을 인덱스로 | CREATE INDEX ON users (UPPER(name)) → WHERE UPPER(name) = 'HONG' |
접두사 LIKE / 전문검색 | 앞 % 회피 | name LIKE '삼성%', to_tsvector('korean', name) @@ to_tsquery('korean', '노트북') |
| 타입 일치 비교 | 암묵적 형변환 회피 | WHERE phone = '01012345678' (문자열 리터럴) |
UNION ALL (OR 대신) | 각 브랜치 인덱스 활용 | … WHERE status='active' UNION ALL … WHERE status='vip' |
| 컬럼 명시(SELECT * 대신) | 네트워크↓·커버링 인덱스 | SELECT order_id, status, total_amount FROM orders WHERE customer_id = 42 |
| Keyset 페이지네이션 | OFFSET 대신 커서 기반 | WHERE created_at < ? OR (created_at = ? AND id < ?) ORDER BY created_at DESC, id DESC LIMIT 20 |
JOIN (N+1 대신) | 목록+상세를 1쿼리로 | SELECT p.id, p.title, u.name FROM posts p JOIN users u ON p.author_id = u.id |
배치 DELETE | 대량 삭제를 소분·커밋 | DELETE FROM logs WHERE id IN (SELECT id FROM logs WHERE created_at < … LIMIT 5000) |
EXPLAIN (ANALYZE, BUFFERS) | 인덱스 무력화·풀스캔 진단 | EXPLAIN ANALYZE SELECT … (type=ALL / Seq Scan 확인) |
pg_stat_user_tables | 죽은 튜플·테이블 팽창 확인 | SELECT relname, n_dead_tup FROM pg_stat_user_tables WHERE relname = 'logs' |
| 구문/명령 | 용도 | 예 |
|---|---|---|
COUNT(*) / COUNT(col) | 전체 행 수 / NULL 제외 행 수 | 5행 중 2행이 NULL이면 COUNT(*)=5, COUNT(amount)=3 |
COUNT(DISTINCT col) | 고유값 개수 | COUNT(DISTINCT user_id) (고유 구매자 수) |
SUM/AVG/MIN/MAX | 합계·평균·최소·최대(NULL 무시) | SUM(amount), ROUND(AVG(amount), 0) |
COALESCE(col, 0) | NULL을 0으로 바꿔 집계 | SUM(COALESCE(amount, 0)) |
GROUP BY | 지정 컬럼 단위로 묶기 | GROUP BY category / GROUP BY 1, 2(위치 참조) |
HAVING | 집계 결과로 그룹 필터 | HAVING SUM(amount) > 1000000 |
WHERE | 그룹핑 전 개별 행 필터 | WHERE status != 'cancelled'(집계함수 사용 불가) |
DATE_TRUNC / TO_CHAR / EXTRACT | 날짜를 집계 단위로 자르기 | DATE_TRUNC('month', created_at), TO_CHAR(created_at, 'YYYY-MM') |
GROUP BY ROLLUP(...) | 소계+총계 자동 생성 | GROUP BY ROLLUP(category, status) |
GROUP BY CUBE(...) | 모든 조합 소계 생성 | GROUP BY CUBE(category, status) |
GROUPING(col) | 소계 행(NULL) 구분·라벨링 | CASE WHEN GROUPING(category)=1 THEN '전체' ELSE category END |
FILTER (WHERE ...) | 조건부 집계를 한 스캔에 | COUNT(*) FILTER (WHERE status='completed') |
EXPLAIN (ANALYZE, BUFFERS) | 집계 실행계획·스필 확인 | HashAggregate의 Batches≥2·Disk Usage 확인 |
SET work_mem | 해시 집계 디스크 스필 방지 | SET work_mem = '128MB'(리포트 세션 한시 상향) |
| 구문/명령 | 용도 | 예 |
|---|---|---|
IS NULL / IS NOT NULL | NULL 여부 검사(= NULL 금지) | WHERE deleted_at IS NULL |
COALESCE(a, b, ...) | NULL 아닌 첫 값 반환(기본값·폴백) | COALESCE(mobile_phone, office_phone, '연락 불가') |
NULLIF(a, b) | a=b이면 NULL(0으로 나누기 방지) | total_sales / NULLIF(total_orders, 0) |
IS DISTINCT FROM | NULL 안전 비교(항상 TRUE/FALSE) | old_email IS DISTINCT FROM new_email |
NOT EXISTS (NOT IN 대신) | NULL에 안전한 제외 조건 | WHERE NOT EXISTS (SELECT 1 FROM blacklist b WHERE b.customer_id = o.customer_id) |
LEFT JOIN ... IS NULL | 반조인 패턴(NOT IN 대체) | LEFT JOIN blacklist b ON o.customer_id = b.customer_id WHERE b.customer_id IS NULL |
COUNT(*) vs COUNT(col) | 전체 vs NULL 제외 집계 | 두 값의 차이 = 해당 컬럼 NULL 행 수 |
AVG(COALESCE(col, 0)) | NULL을 0으로 간주해 집계 | AVG(salary)는 NULL 제외 평균 |
ORDER BY ... NULLS FIRST/LAST | 정렬 시 NULL 위치 명시 | ORDER BY last_login DESC NULLS LAST |
... != v OR col IS NULL | != 필터에서 NULL 행 포함 | WHERE department != '개발팀' OR department IS NULL |
CHECK (... AND col IS NOT NULL) | CHECK가 UNKNOWN 통과하는 것 방지 | CHECK (price > 0 AND price IS NOT NULL) |
| 부분 유니크 인덱스 | UNIQUE의 NULL 중복 허용 차단 | CREATE UNIQUE INDEX ... ON users(email) WHERE email IS NOT NULL |
| 구문/명령 | 용도 | 예 |
|---|---|---|
TRIM / LTRIM / RTRIM | 앞뒤(또는 지정 문자) 제거 | TRIM(' hi '), TRIM(BOTH '0' FROM '00420') |
UPPER / LOWER / INITCAP | 대소문자 정규화 | LOWER(email) (대소문자 무시 비교·유니크) |
SUBSTRING / SUBSTR | 부분 문자열 추출 | SUBSTRING('Hello' FROM 1 FOR 3) |
SPLIT_PART | 구분자 기준 N번째 토큰 | SPLIT_PART(email, '@', 2) (도메인 추출) |
REPLACE | 문자열 치환 | REPLACE(phone, '-', '') |
CONCAT / CONCAT_WS / || | 문자열 연결(NULL 처리 주의) | CONCAT_WS(', ', 시, 구, 동) (NULL 건너뜀) |
REGEXP_REPLACE | 정규식 치환('g'=전체) | REGEXP_REPLACE(phone, '[^0-9]', '', 'g') |
NOW / CURRENT_DATE | 현재 시각/날짜 | WHERE created_at >= NOW() - INTERVAL '30 days' |
DATE_TRUNC | 시간 단위 내림(월·일 집계) | GROUP BY DATE_TRUNC('month', created_at) |
EXTRACT | 연·월·요일·epoch 추출 | EXTRACT(EPOCH FROM (expires_at - NOW())) |
AGE / INTERVAL | 경과 기간·날짜 연산 | AGE(NOW(), created_at), ts + INTERVAL '7 days' |
TO_CHAR | 날짜·숫자 포맷 문자열 | TO_CHAR(created_at, 'YYYY-MM') |
… AT TIME ZONE | 타임존 변환 | created_at AT TIME ZONE 'Asia/Seoul' |
generate_series + COALESCE | 빈 구간 채우기(gap filling) | generate_series(…, interval '1 month') LEFT JOIN … COALESCE(revenue, 0) |
| 구문/명령 | 용도 | 예 |
|---|---|---|
CREATE VIEW | 복잡한 쿼리에 이름 붙이기 | CREATE VIEW v_order_summary AS SELECT ...; (컬럼 명시, SELECT * 지양) |
CREATE OR REPLACE VIEW | 기존 뷰 정의 교체 | CREATE OR REPLACE VIEW v_order_summary AS SELECT ...; |
DROP VIEW | 뷰 삭제 | DROP VIEW IF EXISTS v_order_summary; / 의존 객체까지 ... CASCADE; |
GRANT / REVOKE SELECT | 뷰로 권한 제어 | GRANT SELECT ON v_users_public TO analyst_role; + REVOKE SELECT ON users FROM analyst_role; |
CREATE MATERIALIZED VIEW | 결과를 디스크에 캐싱 | CREATE MATERIALIZED VIEW mv_monthly_revenue AS SELECT ... WITH DATA; |
CREATE UNIQUE INDEX (MV) | CONCURRENTLY 갱신의 전제 조건 | CREATE UNIQUE INDEX ON mv_monthly_revenue (month, category); |
REFRESH MATERIALIZED VIEW | MV 최신화(stale 해소) | 무중단: REFRESH MATERIALIZED VIEW CONCURRENTLY mv_monthly_revenue; |
pg_matviews | MV 마지막 갱신 시각 확인 | SELECT matviewname, last_refresh FROM pg_matviews; |
CREATE PROCEDURE | 여러 SQL을 트랜잭션 단위로 | CREATE OR REPLACE PROCEDURE proc_cancel_order(...) LANGUAGE plpgsql AS $$ ... $$; |
CALL | 프로시저 실행 | CALL proc_cancel_order(12345, '고객 요청으로 취소'); |
CREATE FUNCTION | 값 반환·SELECT 내 사용 | CREATE OR REPLACE FUNCTION fn_calculate_discount(...) RETURNS NUMERIC ...; → SELECT fn_calculate_discount(price,'gold') |
RAISE EXCEPTION | 프로시저 내 오류 처리 | RAISE EXCEPTION '완료된 주문은 취소할 수 없습니다'; |
CREATE TRIGGER | 이벤트 발생 시 자동 실행 | CREATE TRIGGER trg_users_audit AFTER INSERT OR UPDATE OR DELETE ON users FOR EACH ROW EXECUTE FUNCTION fn_audit_log(); |
| 구문/명령 | 용도 | 예 |
|---|---|---|
EXPLAIN | 실행계획만(쿼리 미실행) | EXPLAIN SELECT * FROM orders WHERE user_id = 42 |
EXPLAIN ANALYZE | 실제 실행 + actual time/rows | EXPLAIN ANALYZE SELECT … |
EXPLAIN (ANALYZE, BUFFERS) | 디스크 I/O(버퍼 히트/미스)까지 | EXPLAIN (ANALYZE, BUFFERS) SELECT … |
pg_stat_statements | 서버 전체 병목 쿼리 발굴 | SELECT query, calls, mean_exec_time FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10 |
CREATE INDEX | 인덱스 생성(생성 중 쓰기 락) | CREATE INDEX idx_orders_user ON orders (user_id) |
CREATE INDEX CONCURRENTLY | 무중단 인덱스 생성 | CREATE INDEX CONCURRENTLY idx_orders_user ON orders (user_id, created_at DESC) |
부분 인덱스 … WHERE | 조건 행만 인덱싱(크기·갱신비용↓) | CREATE INDEX … ON orders (created_at DESC) WHERE status = 'completed' |
DROP INDEX CONCURRENTLY | INVALID 인덱스 무중단 제거 | DROP INDEX CONCURRENTLY idx_orders_user |
ANALYZE / VACUUM ANALYZE | 통계 갱신(추정 정확도↑) | VACUUM ANALYZE orders |
pg_stat_progress_create_index | 인덱스 생성 진행률 | SELECT phase, blocks_done, blocks_total FROM pg_stat_progress_create_index |
pg_indexes | 인덱스 목록·정의·INVALID 확인 | SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'orders' |
SET enable_seqscan = OFF | (테스트)인덱스 스캔 강제 | SET enable_seqscan = OFF; EXPLAIN SELECT … |
| 구문/명령 | 용도 | 예 |
|---|---|---|
| 인접 목록(자기참조 FK) | 계층을 parent_id로 표현 | parent_id INT REFERENCES categories(id) |
WITH RECURSIVE | 트리 전체 순회(자손 조회) | WITH RECURSIVE t AS (앵커 UNION ALL 자식 JOIN t) SELECT … |
경로 열거 + LIKE | 재귀 없이 자손 조회 | WHERE path LIKE '/1/2/%' (+ text_pattern_ops 인덱스) |
| 중첩 집합(lft/rgt) | 범위로 자손 조회 | WHERE lft BETWEEN 2 AND 7 |
| 연결 테이블 복합 PK | M:N·중복 자동 차단 | PRIMARY KEY (article_id, tag_id) |
ON CONFLICT DO NOTHING | 중복 연결 무시 삽입 | INSERT INTO article_tags VALUES (42, 1) ON CONFLICT DO NOTHING |
GROUP BY + HAVING COUNT(DISTINCT) | 'AND 태그'를 모두 가진 글 | … WHERE t.slug IN (…) GROUP BY a.id HAVING COUNT(DISTINCT t.slug) = 3 |
소프트 삭제 UPDATE | 물리삭제 대신 표시 | UPDATE articles SET deleted_at = NOW() WHERE id = 42 |
WHERE deleted_at IS NULL | 삭제 행 제외 조회 | SELECT * FROM articles WHERE deleted_at IS NULL |
| 부분 인덱스 | 살아있는 행만 인덱싱 | CREATE INDEX … ON articles (author_id, created_at DESC) WHERE deleted_at IS NULL |
| 원자적 카운터 증가 | lost update 방지 | UPDATE tags SET tag_count = tag_count + 1 WHERE id = ? |
CREATE TRIGGER | 카운터 갱신 경로 일원화 | CREATE TRIGGER trg AFTER INSERT OR DELETE ON article_tags … |
| 구문/명령 | 용도 | 예 |
|---|---|---|
OVER (PARTITION BY ... ORDER BY ...) | 윈도우(그룹·순서) 정의 | AVG(price) OVER (PARTITION BY category_id) — 행 유지하며 그룹 평균 |
ROW_NUMBER() | 동점도 고유 번호(페이지네이션) | ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY price DESC) |
RANK() | 동점 동일 순위, 다음 건너뜀(1,2,2,4) | RANK() OVER (ORDER BY sales DESC) — 스포츠 순위표 |
DENSE_RANK() | 동점 동일 순위, 연속(1,2,2,3) | DENSE_RANK() OVER (ORDER BY price DESC) — 등급/티어 |
NTILE(n) | n등분 분위수 번호 | NTILE(4) OVER (ORDER BY price DESC) — 상위 25% 추출 |
LAG(col, offset, default) | 이전 행 값(전월 대비) | LAG(revenue, 1, 0) OVER (ORDER BY month) → revenue - LAG(...) |
LEAD(...) | 다음 행 값 | LEAD(revenue) OVER (ORDER BY month) |
SUM() OVER (ORDER BY ...) | 누적합(Running Total) | SUM(daily_revenue) OVER (ORDER BY order_date) |
ROWS BETWEEN N PRECEDING AND CURRENT ROW | 이동평균 프레임 | AVG(...) OVER (ORDER BY sale_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) — 7일 이동평균 |
FIRST_VALUE / LAST_VALUE | 윈도우 경계 값 | LAST_VALUE(name) OVER (... ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) |
WITH ranked AS (...) (CTE) | 윈도우 결과를 WHERE로 필터 | 윈도우는 WHERE 직접 참조 불가 → CTE로 감싼 뒤 WHERE rn <= 3 |
EXPLAIN (ANALYZE, BUFFERS) | Sort 비용·디스크 spill 진단 | Sort Method: external merge Disk면 복합 인덱스/work_mem 조정 |
| 구문/명령 | 용도 | 예 |
|---|---|---|
Multi-row INSERT ... VALUES | 여러 행을 한 문장에 적재 | INSERT INTO products(...) VALUES (...),(...),(...) (1,000~5,000건 단위) |
\COPY / COPY ... FROM | CSV 대량 적재(가장 빠름) | \COPY products(name,price,stock) FROM 'p.csv' CSV |
LOAD DATA INFILE (MySQL) | CSV 파일 직접 적재 | LOAD DATA INFILE '...' INTO TABLE products FIELDS TERMINATED BY ',' |
\timing on (psql) | 단건 vs 벌크 실행 시간 측정 | 개선 배수 = 단건 ms ÷ 벌크 ms |
ORDER BY id LIMIT N 루프 | 대량 UPDATE/DELETE 청크 분할 | WHERE ... ORDER BY id LIMIT 10000 반복 + 중간 커밋 |
GET DIAGNOSTICS ... = ROW_COUNT | 배치 처리 건수로 종료 판단 | EXIT WHEN updated_count = 0 |
ON CONFLICT (col) DO UPDATE | UPSERT(있으면 갱신) | ... DO UPDATE SET view_count = daily_stats.view_count + EXCLUDED.view_count |
ON CONFLICT ... DO NOTHING | 중복 무시 삽입 | INSERT ... ON CONFLICT (id) DO NOTHING |
ON DUPLICATE KEY UPDATE (MySQL) | MySQL UPSERT | ... ON DUPLICATE KEY UPDATE view_count = view_count + new_row.view_count |
CREATE/DROP INDEX CONCURRENTLY | 잠금 없이 인덱스 재생성 | 대량 적재 전 DROP → 적재 → CREATE |
SET lock_timeout / statement_timeout | 배치가 서비스를 막지 않게 | SET lock_timeout='5s'(락 대기 시 실패 → 재시도) |
SET SESSION ... ISOLATION LEVEL READ COMMITTED | MySQL 갭 락 회피 | 벌크 INSERT·범위 UPDATE 데드락 완화 |
VACUUM (ANALYZE) | 대량 변경 후 죽은 튜플 회수 | VACUUM (ANALYZE) products(부풀기·통계 갱신) |
| 구문/명령 | 용도 | 예 |
|---|---|---|
joinedload / selectinload (SQLAlchemy) | N+1 해결 eager loading | session.query(Post).options(joinedload(Post.comments)) |
include (Prisma) | 연관 데이터 eager loading | prisma.user.findMany({ include: { posts: true } }) |
JOIN FETCH / @EntityGraph (JPA) | N+1 해결 fetch join | @Query("SELECT u FROM User u JOIN FETCH u.posts") |
relations (TypeORM) | 연관 데이터 eager loading | findOne({ where: {...}, relations: { posts: true } }) |
echo=True / show-sql / DEBUG | 생성 SQL 로깅(N+1 진단) | create_engine(url, echo=True), spring.jpa.show-sql=true, DEBUG="prisma:query" |
FetchType.LAZY (JPA) | 지연 로딩으로 불필요 JOIN 제거 | @OneToMany(fetch = FetchType.LAZY) |
select(User).where(...) (SQLAlchemy 2.0) | 2.0 스타일 조회 | session.execute(select(User).where(User.email == "a@b.com")) |
prisma migrate dev 후 prisma generate | 마이그레이션 적용 후 클라이언트 재생성 | 순서 반대면 타입-DB 스키마 불일치 |
alembic revision --autogenerate / upgrade head | SQLAlchemy 마이그레이션(Alembic) | 모델 변경분 자동 감지·적용 |
$queryRaw (Prisma) / NativeQuery (JPA) | ORM으로 어려운 복잡 쿼리 탈출구 | 복잡 집계·윈도우 함수는 Raw SQL |
@DynamicUpdate (JPA) | 변경된 필드만 UPDATE | dirty checking의 전체 컬럼 UPDATE 방지 |
spring.jpa.open-in-view=false | OSIV 끄기(커넥션 점유 축소) | 커넥션 획득 타임아웃 방지 |
| 구문/명령 | 용도 | 예 |
|---|---|---|
Flyway V{n}__desc.sql | 버전 번호 붙은 마이그레이션 파일 | V2__add_email_to_users.sql (적용 후 수정 금지) |
Flyway R__desc.sql | 내용 바뀔 때마다 재실행(뷰·함수) | R__create_user_summary_view.sql |
flyway_schema_history 조회 | 적용 이력·체크섬·성공 여부 확인 | SELECT version, checksum, success FROM flyway_schema_history ORDER BY installed_rank; |
prisma migrate dev / deploy | 개발 생성 / 운영 적용 | npx prisma migrate deploy, ... migrate status |
ALTER TABLE ADD COLUMN | 컬럼 추가(1단계는 nullable로) | ALTER TABLE users ADD COLUMN display_name VARCHAR(100); |
UPDATE ... WHERE ... IS NULL | 기존 행 백필(2단계) | UPDATE users SET display_name = username WHERE display_name IS NULL; |
ALTER COLUMN ... SET NOT NULL | 백필 후 제약 추가(3단계) | 즉시 NOT NULL은 기존 NULL 행 때문에 실패 |
ADD COLUMN ... NOT NULL DEFAULT | PG 11+ 테이블 재작성 없는 컬럼 추가 | ... ADD COLUMN tier TEXT NOT NULL DEFAULT 'standard'; |
CREATE INDEX CONCURRENTLY | 운영 테이블 락 없이 인덱스 생성 | 트랜잭션 블록 밖에서 단독 실행 |
DROP INDEX CONCURRENTLY | 실패로 남은 INVALID 인덱스 정리 | DROP INDEX CONCURRENTLY idx_orders_user_id; |
배치 백필 DO $$ ... LOOP | 대량 UPDATE를 나눠 커밋 | LIMIT 1000 + GET DIAGNOSTICS + PERFORM pg_sleep(0.1) |
백필 검증 SELECT count(*) | NULL 잔여 0 확인 후 제약 적용 | SELECT count(*) FROM orders WHERE shipping_address IS NULL; |
| Expand-Contract 3단계 | 무중단 스키마 변경 순서 | 새 컬럼 추가 → 코드 배포 → 구 컬럼 삭제 |
| 구문/명령 | 용도 | 예 |
|---|---|---|
-> vs ->> | jsonb 반환 vs 텍스트 반환(WHERE 비교) | WHERE metadata ->> 'brand' = 'Samsung' |
#>> '{a,b}' | 중첩 경로 텍스트 추출 | metadata #>> '{specs, ram}' |
@> (JSONB/배열 포함) | 포함 검색(GIN 인덱스 활용) | WHERE metadata @> '{"brand": "LG"}' |
? / && | 키 존재 여부 / 배열 교집합 | metadata -> 'specs' ? 'ram', tags && ARRAY['sql'] |
jsonb_set / || / - | JSONB 부분 수정·병합·키 삭제 | settings || '{"language":"ko"}' |
CREATE INDEX ... USING GIN (col) | JSONB·배열·tsvector 인덱스 | USING GIN (metadata jsonb_path_ops) |
ON CONFLICT ... DO UPDATE | UPSERT(EXCLUDED=삽입 시도한 새 행) | DO UPDATE SET view_count = page_views.view_count + EXCLUDED.view_count |
ON CONFLICT ... DO NOTHING | 중복 충돌 시 무시 | INSERT ... ON CONFLICT (email) DO NOTHING |
to_tsvector @@ to_tsquery | 전문검색 매칭 | to_tsvector('english', body) @@ to_tsquery('english', 'a & b') |
setweight / ts_rank | 필드 가중치 부여·관련도 정렬 | setweight(to_tsvector(...), 'A') |
pg_trgm + gin_trgm_ops | 한국어 ILIKE·유사도 검색 | WHERE title ILIKE '%데이터베이스%' |
gen_random_uuid() / CREATE TYPE ... AS ENUM | UUID PK·ENUM 타입 | id UUID DEFAULT gen_random_uuid() |
CREATE INDEX CONCURRENTLY | 운영 중 락 없이 인덱스 생성 | 대용량 테이블 무중단 인덱싱 |
| 구문/명령 | 용도 | 예 |
|---|---|---|
SHOW TABLE STATUS / SHOW CREATE TABLE | 엔진·Collation·Auto_increment 확인 | SHOW TABLE STATUS WHERE Name = 'users'; |
ALTER TABLE ... ENGINE = InnoDB | MyISAM → InnoDB 전환 | ALTER TABLE legacy_table ENGINE = InnoDB; |
CHARACTER SET utf8mb4 COLLATE ... | 진짜 UTF-8(이모지 4바이트) 저장 | ... CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci |
ALTER ... CONVERT TO CHARACTER SET | 기존 테이블 utf8 → utf8mb4 일괄 변환 | ALTER TABLE t CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; |
... ON a.x = b.x COLLATE ... | Collation 충돌(1267) 임시 우회 | JOIN 조건에 명시적 COLLATE 지정 |
SHOW VARIABLES LIKE '...' | InnoDB 설정 조회 | SHOW VARIABLES LIKE 'innodb_buffer_pool_size'; |
INSERT ... ON DUPLICATE KEY UPDATE | MySQL Upsert(PG의 ON CONFLICT) | ... VALUES(42,1) ON DUPLICATE KEY UPDATE login_count = login_count + 1 |
VALUES(컬럼) | Upsert에서 삽입하려던 값 참조 | stock = stock + VALUES(stock) |
SET sql_mode = 'ONLY_FULL_GROUP_BY,...' | 느슨한 GROUP BY 차단 | 5.7 이하 비결정적 결과 방지 |
SHOW FULL PROCESSLIST / KILL | 실행 중 쿼리 확인·강제 종료 | KILL QUERY 12345;, KILL 12345; |
EXPLAIN(type 컬럼) | 실행 방식 진단 | type: ALL(Full Scan)이면 인덱스 추가 |
SHOW ENGINE INNODB STATUS | 데드락(1213) 원인 락 분석 | LATEST DETECTED DEADLOCK 섹션 확인 |
CREATE SEQUENCE / NEXTVAL | MariaDB 시퀀스로 AUTO_INCREMENT 갭 우회 | CREATE SEQUENCE order_seq START WITH 1000; |
| 구문/명령 | 용도 | 예 |
|---|---|---|
CREATE TABLE + 제약 (RDBMS) | 엄격한 스키마·무결성 | CREATE TABLE orders (… status VARCHAR CHECK (status IN ('pending','paid',…))) |
BEGIN … COMMIT (RDBMS) | ACID 트랜잭션 | BEGIN; UPDATE inventory …; INSERT INTO orders …; COMMIT; |
다중 JOIN + 집계 (RDBMS) | 복잡한 관계 분석 | … FROM orders o JOIN order_items oi … GROUP BY 1, 2 |
insertMany (MongoDB 문서) | 유연한 JSON 문서 저장 | db.products.insertMany([{ name:'노트북', specs:{ cpu:'M3' } }]) |
SET … EX / ZADD (Redis KV) | 캐시·세션·실시간 순위표 | SET session:1 '…' EX 1800, ZREVRANGE leaderboard 0 9 WITHSCORES |
PRIMARY KEY (a, b) (Cassandra CQL) | 넓은 행·시계열 쓰기 | PRIMARY KEY (device_id, timestamp) WITH CLUSTERING ORDER BY (timestamp DESC) |
MATCH … RETURN (Neo4j Cypher) | 관계 탐색 | MATCH (u)-[:FOLLOWS*2..3]->(r) RETURN r.name |
PRIMARY KEY … USING HASH (NewSQL) | 쓰기 핫스팟 분산 | PRIMARY KEY (id) USING HASH WITH (bucket_count = 16) |
| 구문/명령 | 용도 | 예 |
|---|---|---|
insertOne / insertMany | 문서 삽입(단건/다건) | db.products.insertMany([{ name: "마우스", price: 45000 }, ...]) |
find(query, projection) | 조건 조회 + 반환 필드 선택 | db.products.find({ price: { $gte: 50000 } }, { name: 1, _id: 0 }) |
$gte·$lte·$gt·$in | 비교·포함 쿼리 연산자 | { stock: { $gt: 0 }, tags: { $in: ["keyboard"] } } |
.sort().skip().limit() | 정렬·페이지네이션 | .find().sort({ price: -1 }).skip(20).limit(10) |
updateOne + $set | 부분 필드 수정(문서 통째 교체 방지) | updateOne({ _id }, { $set: { price: 95000 } }) |
$inc / $push | 값 증감 / 배열에 추가 | { $inc: { stock: 50 } }, { $push: { tags: "perf" } } |
deleteOne / deleteMany | 문서 삭제 | db.products.deleteMany({ stock: 0 }) |
aggregate([$match, $group]) | 집계 파이프라인(SQL GROUP BY) | [{ $match: { stock: { $gt: 0 } } }, { $group: { _id: "$brand", avg: { $avg: "$price" } } }] |
$lookup + $unwind | 컬렉션 조인(LEFT OUTER JOIN) | { $lookup: { from: "customers", localField: "customerId", foreignField: "_id", as: "customer" } } |
createIndex({ f: 1 }) | 인덱스·복합 인덱스 생성 | createIndex({ customerId: 1, createdAt: -1 }) (ESR 순서) |
createIndex(..., { expireAfterSeconds }) | TTL 인덱스(자동 만료) | db.sessions.createIndex({ createdAt: 1 }, { expireAfterSeconds: 3600 }) |
{ f: "text" } + $text | 전문 검색 인덱스·검색 | find({ $text: { $search: "MongoDB 성능" } }) |
explain("executionStats") | 실행계획 확인(IXSCAN/COLLSCAN/SORT) | find(...).sort(...).explain("executionStats") |
createCollection(validator) | 스키마 검증 강제 | $jsonSchema: { required: ["email", "name"] } |
| 구문/명령 | 용도 | 예 |
|---|---|---|
SET key val EX 초 / GET | 문자열 저장(TTL 포함)·조회 | SET product:123 '{...}' EX 300 |
INCR / INCRBY / DECR | 원자적 카운터(조회수·레이트리밋) | INCR page_view:article:42 |
EXPIRE / TTL / PERSIST | 만료 설정·확인·해제 | EXPIRE session:abc 1800, TTL key |
LPUSH / RPOP / BRPOP | List 큐(FIFO·블로킹 소비) | LPUSH job_queue '{...}' 후 BRPOP job_queue 0 |
LTRIM / LRANGE | 최근 N개 유지·범위 조회 | LTRIM recent:user:42 0 9 |
HSET / HGET / HGETALL | Hash 필드 단위 저장·조회 | HSET session:abc user_id 42, HGETALL session:abc |
HINCRBY | Hash 필드 원자적 증감 | HINCRBY user:42 login_count 1 |
SADD / SISMEMBER / SCARD | Set 중복 없는 집합·존재·개수 | SADD likes:post:42 user:7 |
SINTERSTORE | 집합 교집합(공통 팔로워 등) | SINTERSTORE common_followers followers:alice followers:bob |
ZADD / ZREVRANGE / ZINCRBY | Sorted Set 랭킹·상위 N·점수 증감 | ZREVRANGE leaderboard 0 2 WITHSCORES |
SET key val NX EX 초 | 분산 락(첫 요청만 획득) | r.set(lock, "1", nx=True, ex=5) (Cache Stampede 방지) |
SUBSCRIBE / PUBLISH | Pub/Sub 실시간 메시지 전파 | PUBLISH notifications:user:42 '{...}' |
SCAN / HSCAN (KEYS 대신) | 커서 방식 비블로킹 순회 | 운영에선 KEYS * 금지 → SCAN 0 MATCH prefix:* |
UNLINK (DEL 대신) | 큰 키 비동기 삭제 | 동기 DEL의 이벤트 루프 블로킹 회피 |
SLOWLOG GET / --bigkeys | 느린 명령·큰 키 진단 | SLOWLOG GET 20, redis-cli --bigkeys |
| 구문/명령 | 용도 | 예 |
|---|---|---|
psql / mongo / redis-cli | 워크로드별로 다른 DB 접속 클라이언트 | 관계형은 psql, 문서는 mongo, 캐시는 redis-cli |
Redis ZREVRANGE ... WITHSCORES | Sorted Set으로 최신 피드 O(log N) 조회 | ZREVRANGE user:42:feed 0 99 WITHSCORES |
Redis INCR | 조회수·시청자 수 같은 고빈도 카운터 | INCR live:1234:viewers (매초 RDBMS UPDATE 대신) |
Redis keys "*" | 저장된 키 확인(운영 남용 주의) | redis-cli keys "*" — 재시작 후 비면 영속성 미설정 |
redis.conf appendonly / appendfsync | Redis AOF 영속성 활성화 | appendonly yes / appendfsync everysec |
MongoDB $lookup | 문서 DB에서 JOIN 대응(임시 대응책) | 임베딩 후 관계형 쿼리가 필요해질 때 |
MongoDB $unwind + $match | 임베딩 배열을 펼쳐 집계·조건 조회 | 컬렉션 전체 스캔 비용 주의 |
GROUP BY / SUM() 집계 | 분석 쿼리 규모로 DB 선택 판단 | 억 단위 집계면 ClickHouse·BigQuery 고려 |
| 구문/명령 | 용도 | 예 |
|---|---|---|
Prepared Statement(파라미터 바인딩) | SQL Injection 근본 차단 — 쿼리 구조와 데이터 분리 | psycopg2 cursor.execute("... WHERE username = %s", (name,)) |
$1 / ? / :param 플레이스홀더 | 언어별 파라미터 바인딩 | node-pg $1, JDBC setString(1, ...), JPQL :username |
CREATE USER ... WITH PASSWORD | 앱 전용 계정 생성 | CREATE USER app_user WITH PASSWORD '...'; |
GRANT ... ON ALL TABLES IN SCHEMA | DML만 최소 권한 부여 | GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user; |
REVOKE CREATE ON SCHEMA | 앱 계정의 DDL(테이블 생성) 차단 | REVOKE CREATE ON SCHEMA public FROM app_user; |
ALTER DEFAULT PRIVILEGES | 앞으로 만들 테이블에도 권한 자동 적용 | ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ... TO app_user; |
CREATE ROLE + GRANT 롤 TO 계정 | 권한을 그룹으로 묶어 일괄 관리·회수 | CREATE ROLE app_readonly; GRANT app_readonly TO readonly_user; |
\dp 테이블 | psql에서 테이블별 권한 확인 | \dp users — app_user에 DDL 없어야 정상 |
SELECT usename, usesuper FROM pg_user | 앱 계정이 superuser인지 점검 | ... WHERE usesuper = true; (앱 계정이 나오면 위험) |
pgp_sym_encrypt() / pgp_sym_decrypt() | pgcrypto AES-256 컬럼 암호화 | pgp_sym_encrypt('4111...', current_setting('app.encryption_key')) |
ALTER SYSTEM SET pgaudit.log | 감사 로그 대상 지정 후 reload | ALTER SYSTEM SET pgaudit.log = 'write, ddl, role'; SELECT pg_reload_conf(); |
sslmode=require / SHOW ssl | 전송 구간 TLS 강제·확인 | postgresql://.../db?sslmode=require, SHOW ssl; |
| 구문/명령 | 용도 | 예 |
|---|---|---|
SHOW transaction_isolation | 현재 격리 수준 확인(PostgreSQL) | SHOW transaction_isolation; → read committed |
SELECT @@transaction_isolation | 현재 격리 수준 확인(MySQL) | 세션 SELECT @@session.transaction_isolation; / 전역 @@global.transaction_isolation |
SET TRANSACTION ISOLATION LEVEL | 격리 수준 변경(PostgreSQL) | SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; |
SET SESSION TRANSACTION ISOLATION LEVEL | 세션 격리 변경(MySQL) | SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; |
BEGIN ... COMMIT | 트랜잭션 시작·확정 | 격리 실습은 터미널 2개로 동시 재현: BEGIN; ... COMMIT; |
BEGIN + SET TRANSACTION ... | 특정 트랜잭션만 격리 상향 | BEGIN; SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; ... COMMIT; |
SELECT ... FOR UPDATE | 조회 행 잠금(재고·잔액) | SELECT stock FROM products WHERE id=42 FOR UPDATE; → UPDATE ... WHERE id=42 AND stock>0 |
pg_blocking_pids() | 누가 누구를 막는지(PostgreSQL) | ... ON blocking.pid = ANY(pg_blocking_pids(blocked.pid)) |
SHOW ENGINE INNODB STATUS | 최근 데드락 상세(MySQL) | SHOW ENGINE INNODB STATUS\G → "LATEST DETECTED DEADLOCK" 섹션 |
SET GLOBAL innodb_print_all_deadlocks | 데드락 로그 자동 기록(MySQL) | SET GLOBAL innodb_print_all_deadlocks = ON; (my.cnf에도 추가) |
idle_in_transaction_session_timeout | 잊힌 트랜잭션 자동 종료 | 오래된 idle in transaction이 VACUUM 경계선을 붙들 때 설정 |
| 구문/명령 | 용도 | 예 |
|---|---|---|
SELECT ... FOR UPDATE | 비관적 락(읽는 순간 행 잠금) | SELECT * FROM inventory WHERE product_id = 42 FOR UPDATE; |
UPDATE ... WHERE version = ? | 낙관적 락 충돌 감지 조건 | UPDATE inventory SET stock = stock - 1, version = version + 1 WHERE product_id = 42 AND version = 7; |
| affected rows(rowcount) 검사 | 0이면 충돌 → 재시도 신호 | if result == 0: raise OptimisticLockError |
version INTEGER DEFAULT 0 | 낙관적 락용 버전 컬럼(정수 권장) | CHECK (stock >= 0)과 함께 최후 방어선 |
WHERE ... AND enrolled < capacity | version + 업무 조건 이중 안전장치 | 정원 초과·충돌을 한 UPDATE로 차단 |
BEGIN / COMMIT / ROLLBACK | 트랜잭션 경계(재시도 전 롤백 필수) | 충돌 시 session.rollback() 후 재조회 |
@Version (JPA) | 엔티티 저장 시 version 자동 검사 | UPDATE에 WHERE version=?·version+1 자동 부착 |
version_id_col (SQLAlchemy) | 커밋 시 version 검사(StaleDataError) | __mapper_args__ = {"version_id_col": version} |
$executeRaw (Prisma) | 낙관적 락 직접 구현(공식 미지원) | WHERE id = ${id} AND version = ${version} |
| 지수 백오프 + 지터 | 재시도 폭풍 방지 | delay = base * 2 ** attempt + random() |
EntityManager.clear() / refresh | bulk UPDATE 후 낡은 version 폐기 | 우회 경로의 낙관적 락 무력화 방지 |
| 구문/명령 | 용도 | 예 |
|---|---|---|
EXPLAIN | 실행 없이 예상 계획 확인 | type=ALL·key=NULL이면 Full Table Scan |
EXPLAIN ANALYZE | 실제 실행 + 예상/실측 비교 | EXPLAIN ANALYZE SELECT ... \G |
| 복합 인덱스(등호→정렬 순) | 컬럼 순서로 Full Scan·filesort 제거 | CREATE INDEX ix ON orders (user_id, status, created_at DESC) |
Covering Index (Using index) | 테이블 접근 없이 인덱스만으로 응답 | 조회에 필요한 모든 컬럼을 인덱스에 포함 |
ANALYZE 테이블 | 통계 갱신(예상 vs 실제 rows 괴리 해소) | 통계가 오래되면 잘못된 계획 선택 |
SHOW INDEX FROM t | 현재 인덱스 확인 | SHOW INDEX FROM orders |
USE INDEX (...) | 옵티마이저에 인덱스 힌트(테스트용) | 낮은 카디널리티로 인덱스 무시될 때 |
ALGORITHM=INPLACE, LOCK=NONE | 무중단 온라인 DDL 인덱스 추가 | ALTER TABLE orders ADD INDEX ... ALGORITHM=INPLACE, LOCK=NONE |
pt-online-schema-change / gh-ost | 초대형 테이블 안전 DDL | --chunk-size·--max-load로 부하 제어 |
slow_query_log / long_query_time | 느린 쿼리 수집 | SET GLOBAL long_query_time = 0.5 |
events_statements_summary_by_digest | 누적 기준 슬로우 쿼리 TOP N | ORDER BY SUM_TIMER_WAIT DESC |
/*+ MAX_EXECUTION_TIME(0) */ | 쿼리별 타임아웃 힌트 | 배치 쿼리 제한 해제 |
CREATE STATISTICS / 히스토그램 | 상관 컬럼 카디널리티 보정 | 잘못된 Nested Loop → Hash Join 유도 |
| 구문/명령 | 용도 | 예 |
|---|---|---|
SHOW REPLICA STATUS\G | MySQL 복제 상태·지연 확인 | Replica_IO_Running / Replica_SQL_Running = Yes 확인 |
Seconds_Behind_Source | MySQL 복제 지연(초) | 0 정상 / 60s↑ 위험 / NULL 복제 중단 |
pg_stat_replication | PostgreSQL 복제 지연 | SELECT client_addr, state, replay_lag FROM pg_stat_replication |
pg_wal_lsn_diff | 복제 지연을 바이트로 계산 | pg_wal_lsn_diff(sent_lsn, replay_lsn) AS lag_bytes |
@@GLOBAL.gtid_executed | 노드별 GTID 비교(errant 탐지) | SELECT @@GLOBAL.gtid_executed (두 노드 대조) |
CHANGE REPLICATION SOURCE | Replica가 볼 Primary 지정 | 스플릿 브레인 후 errant 노드엔 되붙이지 말고 rebuild |
slave_parallel_workers | 병렬 복제로 지연 완화 | Primary 쓰기 폭주로 SQL 스레드가 못 따라갈 때 |
| 읽기 라우팅 강제(ORM) | 쓰기 직후 읽기는 Primary | Django User.objects.using('default').get(…) |
CloudWatch ReplicaLag | RDS 복제 지연 알람 | ReplicaLag > 30s → SNS 알림 |
| 구문/명령 | 용도 | 예 |
|---|---|---|
SHOW shared_preload_libraries | pg_stat_statements 로드 확인 | 없으면 postgresql.conf에 추가 후 재시작 |
CREATE EXTENSION IF NOT EXISTS pg_stat_statements | 쿼리 통계 확장 설치 | DB당 1회 |
SELECT ... FROM pg_stat_statements ORDER BY total_exec_time DESC | 자원 최다 소비 쿼리 | pct_total 40%면 그 쿼리 1순위 최적화 |
SELECT pg_stat_statements_reset() | 분석 사이클 통계 초기화 | 일정 시간 다시 수집 후 재분석 |
SET GLOBAL slow_query_log='ON' (MySQL) | 슬로우 쿼리 로그 활성화 | SET GLOBAL long_query_time = 1(초) |
SHOW VARIABLES LIKE 'slow_query%' (MySQL) | 슬로우 로그 설정 확인 | long_query_time·로그 파일 경로 |
mysqldumpslow -s at -t 10 | 슬로우 로그 요약 | -s at(평균)·-s t(총시간) 정렬 |
pg_blocking_pids(pid) + pg_stat_activity | 잠금 대기·원인 세션 추적 | blocked/blocking 쿼리 함께 조회 |
pg_terminate_backend(pid) | 원인 세션 강제 종료 | wait_seconds 큰 blocking_pid 종료 |
SELECT ... FROM pg_locks WHERE relation IS NOT NULL | 잠금 타입별 현황 | mode·granted별 집계 |
information_schema.innodb_trx (MySQL) | InnoDB 트랜잭션·락 대기 | trx_state = 'LOCK WAIT' 확인 |
SHOW max_connections / SELECT COUNT(*) FROM pg_stat_activity | 연결 한도·현재 수 | 슬롯 소진(FATAL) 대응 |
| 구문/명령 | 용도 | 예 |
|---|---|---|
SELECT state, count(*) FROM pg_stat_activity GROUP BY state | 연결 상태 분포(풀 고갈 진단) | idle in transaction이 쌓였는지 확인 |
... WHERE state='idle in transaction' ORDER BY idle_duration | 오래 묶인 세션 추적 | now() - state_change AS idle_duration |
pg_terminate_backend(pid) | 문제 세션 강제 종료(임시 조치) | 5분 이상 idle in transaction 세션 정리 |
SHOW max_connections | DB 최대 연결 수 확인 | 총 풀 크기 ≤ max_connections × 0.8 |
| PgBouncer 관리 접속 | 풀러 상태 조회용 접속 | psql -h 127.0.0.1 -p 6432 -U pgbouncer pgbouncer |
SHOW POOLS (PgBouncer) | 클라이언트·서버 연결·대기 확인 | cl_waiting·maxwait로 풀 부족 판단 |
SHOW STATS / SHOW CLIENTS (PgBouncer) | 쿼리 통계·클라이언트 목록 | total_query_time, avg_query_time |
SET default_pool_size=...; RELOAD; (PgBouncer) | 풀 크기 조정·설정 재적용 | 고갈 시 25로 복구 후 RELOAD |
SHOW VARIABLES LIKE 'wait_timeout' (MySQL) | DB 연결 수명 확인 | max-lifetime을 이 값보다 짧게(-30초) |
SHOW tcp_keepalives_idle (PostgreSQL) | keepalive 설정 확인 | 방화벽 끊김·좀비 연결 점검 |
pgbench -c N -j M -T S | 커넥션 풀 부하 테스트 | pgbench -c 200 -j 8 -T 60 mydb(직접 vs PgBouncer TPS) |
SELECT usename, passwd FROM pg_shadow WHERE usename='app' | SCRAM 해시 추출(userlist 갱신) | PgBouncer 인증 실패 복구 |