SQL Expert Skill

PostgreSQL, MySQL, SQLite 및 SQL Server 전반에 걸친 SQL 데이터베이스의 작성, 최적화 및 관리를 위한 전문가 가이드입니다.

핵심 역량 (Core Capabilities)

이 SKILL을 통해 다음을 수행할 수 있습니다:

• JOIN, 서브쿼리, CTE 및 윈도우 함수를 포함한 복잡한 SQL 쿼리 작성
• EXPLAIN 실행 계획 및 인덱스 권장 사항을 활용한 느린 쿼리 최적화
• 올바른 정규화(1NF, 2NF, 3NF, BCNF)를 적용한 데이터베이스 스키마 설계
• 쿼리 성능을 위한 효과적인 인덱스 생성
• 롤백 지원을 포함한 안전한 데이터베이스 마이그레이션 작성
• SQL 에러 디버깅 및 에러 메시지 해석
• 적절한 격리 수준(isolation levels)을 적용한 트랜잭션 처리
• JSON/JSONB 데이터 타입 활용
• 테스트를 위한 샘플 데이터 생성
• 데이터베이스 다이얼렉트 간 변환 (PostgreSQL ↔ MySQL ↔ SQLite)

---

지원하는 데이터베이스 시스템 (Supported Database Systems)

PostgreSQL

적합한 사례: 복잡한 쿼리, JSON 데이터, 고급 기능, ACID 준수

pip install psycopg2-binary sqlalchemy

MySQL/MariaDB

적합한 사례: 웹 애플리케이션, WordPress, 읽기 비중이 높은 워크로드

pip install mysql-connector-python sqlalchemy

SQLite

적합한 사례: 로컬 개발, 임베디드 데이터베이스, 테스트

pip install sqlite3  # Python 내장

SQL Server

적합한 사례: 엔터프라이즈 애플리케이션, Windows 환경

pip install pyodbc sqlalchemy

---

쿼리 작성 (Query Writing)

JOIN을 포함한 기본 SELECT

-- 필터링이 포함된 단순 SELECT
SELECT
    column1,
    column2,
    column3
FROM
    table_name
WHERE
    condition = 'value'
    AND another_condition > 100
ORDER BY
    column1 DESC
LIMIT 10;

-- INNER JOIN
SELECT
    users.name,
    orders.order_date,
    orders.total_amount
FROM
    users
INNER JOIN
    orders ON users.id = orders.user_id
WHERE
    orders.status = 'completed';

-- LEFT JOIN (주문이 없는 사용자를 포함하여 모두 조회)
SELECT
    users.name,
    COUNT(orders.id) as order_count,
    COALESCE(SUM(orders.total_amount), 0) as total_spent
FROM
    users
LEFT JOIN
    orders ON users.id = orders.user_id
GROUP BY
    users.id, users.name;

서브쿼리 및 CTE (Common Table Expression)

-- WHERE 절의 서브쿼리
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- CTE (가독성 향상을 위해 권장)
WITH high_value_customers AS (
    SELECT
        user_id,
        SUM(total_amount) as lifetime_value
    FROM orders
    GROUP BY user_id
    HAVING SUM(total_amount) > 1000
)
SELECT
    users.name,
    users.email,
    hvc.lifetime_value
FROM users
INNER JOIN high_value_customers hvc ON users.id = hvc.user_id;

윈도우 함수 (Window Functions)

-- 그룹 내 순위 지정
SELECT
    name,
    department,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) as salary_rank
FROM
    employees;

-- 누계 (Running totals)
SELECT
    order_date,
    total_amount,
    SUM(total_amount) OVER (ORDER BY order_date) as running_total
FROM
    orders;

-- 이동 평균 (Moving averages)
SELECT
    order_date,
    total_amount,
    AVG(total_amount) OVER (
        ORDER BY order_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) as moving_avg_7days
FROM
    daily_sales;

더 복잡한 쿼리 패턴은 examples/complex_queries.sql을 참조하세요.

---

쿼리 최적화 (Query Optimization)

EXPLAIN 활용

-- 쿼리 성능 분석
EXPLAIN ANALYZE
SELECT
    users.name,
    COUNT(orders.id) as order_count
FROM users
LEFT JOIN orders ON users.id = orders.user_id
GROUP BY users.id, users.name;

-- 확인할 내용:
-- - Seq Scan (나쁨) vs Index Scan (좋음)
-- - 높은 비용(cost) 수치
-- - 처리되는 대량의 행 수(row counts)

빠른 최적화 팁

-- 나쁨: 인덱스 컬럼에 함수 사용
SELECT * FROM users WHERE LOWER(email) = 'user@example.com';

-- 좋음: 인덱스 컬럼을 가공하지 않음
SELECT * FROM users WHERE email = LOWER('user@example.com');

-- 나쁨: SELECT * 사용
SELECT * FROM large_table WHERE id = 123;

-- 좋음: 필요한 컬럼만 선택
SELECT id, name, email FROM large_table WHERE id = 123;

포괄적인 최적화 기법은 references/query-optimization.md를 참조하세요.

---

스키마 설계 (Schema Design)

정규화 원칙 (Normalization Principles)

제1정규형 (1NF): 반복되는 그룹 제거, 원자성(atomic) 확보

-- 좋음: 주문 항목을 위한 별도 테이블 구성
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_name VARCHAR(100)
);

CREATE TABLE order_items (
    order_item_id INT PRIMARY KEY,
    order_id INT REFERENCES orders(order_id),
    product_name VARCHAR(100)
);

제2정규형 (2NF): 기본키가 아닌 모든 속성이 기본키 전체에 의존해야 함

-- 좋음: 제품 정보를 분리하여 관리
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    product_price DECIMAL(10, 2)
);

CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT,
    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

제3정규형 (3NF): 이행적 종속성(transitive dependency)이 없어야 함

일반적인 스키마 패턴

일대다 (One-to-Many):

CREATE TABLE authors (
    author_id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100) UNIQUE
);

CREATE TABLE books (
    book_id INT PRIMARY KEY,
    title VARCHAR(200),
    author_id INT NOT NULL,
    published_date DATE,
    FOREIGN KEY (author_id) REFERENCES authors(author_id)
);

다대다 (Many-to-Many):

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE courses (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(100)
);

-- 교차(Junction) 테이블
CREATE TABLE enrollments (
    enrollment_id INT PRIMARY KEY,
    student_id INT NOT NULL,
    course_id INT NOT NULL,
    enrollment_date DATE,
    grade CHAR(2),
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id),
    UNIQUE (student_id, course_id)
);

더 많은 스키마 패턴은 examples/schema_examples.sql을 참조하세요.

---

인덱스 및 성능 (Indexes and Performance)

인덱스 생성

-- 단일 컬럼 인덱스
CREATE INDEX idx_users_email ON users(email);

-- 복합 인덱스 (컬럼 순서가 중요합니다!)
CREATE INDEX idx_orders_user_date ON orders(user_id, order_date);

-- 고유 인덱스
CREATE UNIQUE INDEX idx_users_username ON users(username);

-- 부분 인덱스 (PostgreSQL)
CREATE INDEX idx_active_users ON users(email) WHERE status = 'active';

인덱스 가이드라인

인덱스가 필요한 경우:

• ✅ WHERE 절에 사용되는 컬럼
• ✅ JOIN 조건에 사용되는 컬럼
• ✅ ORDER BY에 사용되는 컬럼
• ✅ 외래 키(Foreign key) 컬럼

인덱스를 피해야 하는 경우:

• ❌ 아주 작은 테이블 (< 1000행)
• ❌ 선택도(selectivity)가 낮은 컬럼 (예: boolean 필드)
• ❌ 업데이트가 매우 빈번한 컬럼

상세한 인덱스 전략은 references/indexes-performance.md를 참조하세요.

---

마이그레이션 (Migrations)

안전한 마이그레이션 패턴

-- 1단계: nullable 컬럼으로 추가
ALTER TABLE users ADD COLUMN status VARCHAR(20);

-- 2단계: 기존 데이터 채우기
UPDATE users SET status = 'active' WHERE status IS NULL;

-- 3단계: NOT NULL 제약 조건 부여
ALTER TABLE users ALTER COLUMN status SET NOT NULL;

-- 4단계: 새 행을 위한 기본값 설정
ALTER TABLE users ALTER COLUMN status SET DEFAULT 'active';

-- 롤백 계획
ALTER TABLE users DROP COLUMN status;

무중단 마이그레이션 (Zero-Downtime Migrations)

-- 좋음: 컬럼을 먼저 nullable로 추가한 뒤 데이터 채움
ALTER TABLE large_table ADD COLUMN new_column VARCHAR(100);

-- 대량 업데이트는 배치(batch) 단위로 수행
UPDATE large_table SET new_column = 'value' WHERE new_column IS NULL LIMIT 1000;
-- 완료될 때까지 반복

-- 그 다음 NOT NULL 설정
ALTER TABLE large_table ALTER COLUMN new_column SET NOT NULL;

추가 마이그레이션 패턴은 examples/migrations.sql을 참조하세요.

---

고급 패턴 (Advanced Patterns)

UPSERT (Insert or Update)

-- PostgreSQL
INSERT INTO users (user_id, name, email, updated_at)
VALUES (1, 'John Doe', 'john@example.com', NOW())
ON CONFLICT (user_id)
DO UPDATE SET
    name = EXCLUDED.name,
    email = EXCLUDED.email,
    updated_at = NOW();

-- MySQL
INSERT INTO users (user_id, name, email, updated_at)
VALUES (1, 'John Doe', 'john@example.com', NOW())
ON DUPLICATE KEY UPDATE
    name = VALUES(name),
    email = VALUES(email),
    updated_at = NOW();

재귀 CTE (Recursive CTEs)

-- 계층형 데이터 탐색
WITH RECURSIVE employee_hierarchy AS (
    -- Anchor: 최상위 직원
    SELECT id, name, manager_id, 1 as level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive: 이전 레벨에 보고하는 직원들
    SELECT e.id, e.name, e.manager_id, eh.level + 1
    FROM employees e
    INNER JOIN employee_hierarchy eh ON e.manager_id = eh.id
)
SELECT * FROM employee_hierarchy ORDER BY level, name;

피벗 테이블, JSON 작업 및 벌크 작업을 포함한 고급 패턴은 references/advanced-patterns.md를 참조하세요.

---

모범 사례 (Best Practices)

핵심 원칙

1. 항상 파라미터화된 쿼리를 사용하여 SQL 인젝션을 방지하세요.
2. 연관된 작업은 트랜잭션을 사용하여 원자성을 보장하세요.
3. 적절한 제약 조건을 추가하세요 (PRIMARY KEY, FOREIGN KEY, NOT NULL, CHECK).
4. 타임스탬프 컬럼 (created_at, updated_at)을 테이블에 포함하세요.
5. 테이블과 컬럼에 의미 있는 이름을 지으세요.
6. SELECT * 을 지양하고 필요한 컬럼만 명시하세요.
7. 조인 성능을 위해 외래 키에 인덱스를 고려하세요.
8. 가변 길이 문자열에는 CHAR 대신 VARCHAR를 사용하세요.
9. IS NULL / IS NOT NULL을 사용하여 NULL 값을 적절히 처리하세요.
10. 적절한 데이터 타입을 사용하세요 (금액은 FLOAT가 아닌 DECIMAL 권장).

모범 사례 적용 예시:

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT NOT NULL,
    order_date DATE NOT NULL DEFAULT CURRENT_DATE,
    total_amount DECIMAL(10, 2) CHECK (total_amount >= 0),
    status VARCHAR(20) CHECK (status IN ('pending', 'completed', 'cancelled')),
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status);

포괄적인 모범 사례는 references/best-practices.md를 참조하세요.

---

자주 발생하는 문제 (Common Pitfalls)

다음을 주의하세요:

1. N+1 쿼리 문제 - 쿼리가 반복문 안에서 실행되지 않도록 JOIN을 활용하세요.
2. LIMIT 부재 - 큰 테이블 탐색 시 LIMIT을 잊지 마세요.
3. 암시적 타입 변환 - 타입 불일치는 인덱스 사용을 방해할 수 있습니다.
4. EXISTS 대신 COUNT(*) 사용 - 존재 여부만 확인할 때는 EXISTS가 효율적입니다.
5. NULL 처리 실수 (NULL = NULL은 TRUE가 아니라 NULL입니다).
6. 임시방편으로 SELECT DISTINCT 사용 - 쿼리의 근본적인 원인을 고치는 대신 DISTINCT로 중복만 가리는 것은 위험합니다.
7. 연관 작업에서의 트랜잭션 누락.
8. 인덱스 컬럼 가공 - 인덱스 컬럼에 함수를 씌우면 인덱스를 탈 수 없습니다.

예시 - N+1 문제 피하기:

# 나쁨: N+1 쿼리
users = db.query("SELECT * FROM users")
for user in users:
    orders = db.query("SELECT * FROM orders WHERE user_id = ?", user.id)

# 좋음: JOIN을 이용한 단일 쿼리
result = db.query("""
    SELECT users.*, orders.*
    FROM users
    LEFT JOIN orders ON users.id = orders.user_id
""")

문제 해결 방법의 전체 목록은 references/common-pitfalls.md를 참조하세요.

---

헬퍼 스크립트 및 예시

활용 가능 리소스

헬퍼 스크립트 (scripts/):

• sql_helper.py - 쿼리 빌딩, 스키마 내성 조사(introspection), 인덱스 분석 및 마이그레이션 보조 유틸리티

예시 (examples/):

• complex_queries.sql - CTE, 윈도우 함수, 서브쿼리를 활용한 고급 쿼리 패턴
• schema_examples.sql - 다양한 유스케이스를 위한 전체 스키마 설계 예시
• migrations.sql - 안전한 마이그레이션 패턴 및 무중단 기법

참조 문서 (references/):

• query-optimization.md - 포괄적인 쿼리 최적화 기법 및 EXPLAIN 분석
• indexes-performance.md - 상세 인덱스 전략, 유지보수 및 모니터링
• advanced-patterns.md - UPSERT, 벌크 작업, 피벗 테이블, JSON 작업, 재귀 쿼리
• best-practices.md - 전체 SQL 모범 사례 가이드
• common-pitfalls.md - 일반적인 실수와 방지 방법

빠른 시작 가이드

1. 기본 쿼리는 위에서 보여준 패턴을 사용하세요.
2. 최적화가 필요한 경우 EXPLAIN으로 시작하고 references/query-optimization.md를 확인하세요.
3. 스키마 설계 시 정규화 패턴을 검토하고 examples/schema_examples.sql을 참조하세요.
4. 복잡한 시나리오는 references/advanced-patterns.md를 확인하세요.
5. 유틸리티가 필요한 경우 scripts/sql_helper.py를 활용하세요.

---

워크플로우 (Workflow)

SQL 데이터베이스 작업 시:

1. 요구 사항 파악 - 어떤 데이터를 조회하거나 저장해야 하는가?
2. 스키마 설계 - 정규화 적용 및 적절한 데이터 타입 선택
3. 인덱스 생성 - 외래 키 및 자주 조회되는 컬럼 인덱싱
4. 쿼리 작성 - 단순하게 시작하여 필요에 따라 복잡도 추가
5. 최적화 - EXPLAIN을 사용하여 병목 지점 식별
6. 테스트 - 샘플 데이터 및 에지 케이스(edge cases) 검증
7. 문서화 - 복잡한 쿼리에는 주석 추가

마이그레이션 시:

1. 변경 계획 수립 - 영향을 받는 테이블과 의존성 식별
2. 마이그레이션 작성 - Up/Down 마이그레이션 모두 작성
3. 복사본 테스트 - 개발용 데이터베이스에서 먼저 테스트
4. 백업 - 항상 마이그레이션 실행 전 백업 수행
5. 실행 - 트래픽이 적은 시간에 실행
6. 검증 - 실행 후 데이터 무결성 확인