관계형 데이터베이스·SQL과 트랜잭션

01테이블·키·제약조건으로 자료의 관계를 정의하기

• 테이블·행테이블의 행은 개별 자료, 열은 이름과 자료형을 가진 속성이다.
• 기본키·행기본키는 행을 유일하게 식별한다. 외래키는 같은 테이블 또는 다른 테이블의 참조 가능한 키와 연결되어 참조 무결성을 검사한다. 같은 테이블에서 부모 행을 가리키는 자기 참조도 가능하다.
• NOT NULLNOT NULL은 값이 없음을 허용할지, UNIQUE는 중복을 허용할지, CHECK는 조건을 만족해야 하는지를 표현한다.
• CHECK와 NULL이 예시의 amount CHECK (amount >= 0)만으로 NULL이 금지되지는 않는다. PostgreSQL에서 CHECK는 false를 거부하므로 값이 필수인 설계라면 NOT NULL을 별도로 선언한다.
• DB 제약조건의 필요성애플리케이션에서만 유효성을 검사하면 동시 요청이나 다른 접속 경로가 그 규칙을 우회할 수 있으므로 핵심 데이터 불변 조건은 DB 제약으로도 표현한다.

  • 예를 들어 orders의 customer_id가 customers에 존재해야 한다면 외래키가 관계의 일관성을 검사한다.
• 열이름 문자열을 모든 주문에 복사해 연결하면 같은 이름의 다른 사람이나 이름 변경을 처리하기 어렵다.

  • 적절한 키로 연결하고 필요한 중복은 목적을 알고 선택한다.
• 스키마스키마는 이런 논리 구조이고 파일 시스템의 디렉터리 이름과 같은 개념은 아니다.
• 정규화정규화는 불필요한 중복과 갱신 이상을 줄이기 위한 설계 방법이다.
• 테이블모든 정보를 한 테이블에 넣는 편의와 실제 변경·조회 요구 사이의 비용을 함께 고려한다.

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name VARCHAR(80) NOT NULL
);
CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    amount DECIMAL(12,2) CHECK (amount >= 0)
);

교육용 예시. 실제 요청·발송·설정 변경을 실행한 결과가 아니다.

02SELECT의 행 선택·열 선택·JOIN·집계

• WHERE·행WHERE는 조건에 맞는 행을 선택하고 SELECT 목록은 결과의 열이나 표현식을 정한다.
• JOIN의 결합 조건JOIN은 지정한 관계 조건으로 두 입력의 행을 결합한다.
• 잘못된 조합 수조건이 없거나 잘못되면 조합 수가 예상보다 크게 늘어날 수 있다.
• GROUP BYGROUP BY는 같은 그룹 값을 묶고 SUM·COUNT 등은 그룹별 결과를 계산한다.
• ORDER BYORDER BY를 지정하지 않은 결과의 행 순서는 일반적으로 보장하지 않는다.
• JOIN·집계 예제아래 쿼리는 고객과 주문을 고객 키로 연결한 뒤 고객별 주문액을 계산한다.
• INNER·LEFT JOININNER JOIN이므로 주문이 없는 고객은 결과에 나타나지 않는다. 주문 없는 고객까지 포함하려면 LEFT JOIN과 NULL 처리의 의미를 따로 설계한다.
• 열NULL은 빈 문자열이나 숫자 0과 다르며 비교에 IS NULL을 사용한다.

  • COUNT(*)와 COUNT(column)도 NULL을 세는 방식이 다르다.

SELECT c.id, c.name, SUM(o.amount) AS total
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY total DESC;

교육용 예시. 실제 요청·발송·설정 변경을 실행한 결과가 아니다.

03SQL 문장에서 실행 계획과 데이터 접근까지

• DBDB는 연결을 인증하고 문장을 파싱하여 문법, 이름, 자료형, 객체 권한을 해석한다.
• 인덱스·JOIN옵티마이저는 통계와 사용 가능한 인덱스를 바탕으로 접근 경로와 JOIN 순서 등의 실행 계획을 선택한다.
• 스토리지 엔진의 접근스토리지 엔진은 버퍼 캐시와 데이터 페이지를 이용해 실제 행을 읽고 변경한다.
• SELECT·테이블모든 SELECT가 전체 테이블을 임시 View로 만든 뒤 권한을 검사하는 과정이라는 설명은 정확하지 않다.
• 인덱스·행인덱스는 특정 키로 필요한 행 위치를 빠르게 찾기 위한 추가 구조다.
• 열·인덱스B-tree 계열 인덱스는 범위·정렬 탐색에 쓰일 수 있지만 모든 조건에서 항상 선택되는 것은 아니다.
• 테이블·행작은 테이블이나 많은 행을 읽는 조건에서는 순차 탐색이 더 유리할 수 있다.
• 인덱스INSERT·UPDATE·DELETE는 관련 인덱스도 유지해야 하므로 인덱스를 무조건 많이 만드는 것은 읽기·쓰기 비용의 균형을 무시한다.
• EXPLAIN·ANALYZEEXPLAIN의 추정 계획과 실제 실행을 수반할 수 있는 EXPLAIN ANALYZE를 구별한다.

EXPLAIN SELECT * FROM orders WHERE customer_id = 7;
# 실제 문법과 출력 열은 DBMS에 따라 다르다.
# 예상 행 수, 접근 방식, 사용 인덱스를 계획으로 읽는다.

교육용 예시. 실제 요청·발송·설정 변경을 실행한 결과가 아니다.

04트랜잭션과 동시성: 재고 차감·주문 저장을 한 단위로

• Commit·Rollback 단위트랜잭션은 여러 변경을 한 논리 단위로 묶어 COMMIT 또는 ROLLBACK한다.
• 재고재고만 줄고 주문이 저장되지 않는 중간 상태가 최종 상태로 남지 않게 하는 것이 원자성의 예다.
• 일관성·격리·지속성일관성은 제약과 업무 규칙을 지킨 상태로 전환한다는 뜻이고, 격리는 동시에 실행되는 작업이 서로 관찰하는 범위를 다루며, 지속성은 성공 확정된 변경의 보존을 다룬다.

  • 각 특성은 엔진·설정·장애 모델에 영향을 받는다.
• 영향받은 행 수아래 조건부 UPDATE에서 영향받은 행이 1인지 확인한 뒤 주문을 저장한다.
• 재고·행재고가 없으면 0행이 바뀌므로 주문 생성을 진행하지 않고 되돌린다.
• SELECT·재고SELECT로 재고를 읽고 나중에 무조건 감소시키는 방식은 두 사용자가 같은 재고를 보고 경쟁할 수 있다.

  • 잠금·MVCC·격리 수준과 재시도 설계가 이 문제에 관여한다.
• Deadlock 후 재시도deadlock으로 한 트랜잭션이 중단되었다면 전체 업무 단위를 적절히 다시 시도해야 한다.

BEGIN;
UPDATE inventory SET stock = stock - 1
WHERE product_id = 42 AND stock > 0;
-- 애플리케이션이 영향받은 행 수 1을 확인한 경우에만 다음 단계
INSERT INTO orders (id, customer_id, amount) VALUES (1001, 7, 12000);
COMMIT;
-- 중간 단계 실패 경로에서는 COMMIT 대신 ROLLBACK

교육용 예시. 실제 요청·발송·설정 변경을 실행한 결과가 아니다.

08View·권한·매개변수 질의가 만드는 경계

• ViewView는 이름을 붙여 저장한 질의 정의로, 필요한 열이나 관계를 하나의 논리 객체로 제공할 수 있다.

  • 매 SELECT의 결과 집합을 모두 View라고 부르지 않는다.
• View·행View의 실행 권한과 기반 테이블 권한은 DBMS의 보안 모델에 따라 다르며 앱 계정에 관리자 권한을 주는 대신 필요한 작업만 부여한다.

  • 접속 성공과 특정 테이블의 수정 허용도 별개 단계다.
• 열SQL Injection은 입력값을 SQL 문자열에 결합해 값이 명령 구조로 해석될 때 발생한다.
• 매개변수매개변수 질의는 명령 구조와 데이터를 분리해 전달한다.
• 테이블값 바인딩은 테이블 이름·정렬 방향 같은 SQL 식별자나 키워드를 아무 위치에나 대체하는 기능이 아니므로 이런 선택은 허용 목록으로 구성한다.
• 매개변수 질의 예제아래는 Python DB-API의 SQLite 방식 물음표 자리표시자 예이며 다른 드라이버는 %s 등 규약이 다를 수 있다.

cursor.execute(
    "SELECT id, name FROM customers WHERE id = ?",
    (customer_id,),
)
rows = cursor.fetchall()
# customer_id는 SQL 코드 조각이 아니라 값으로 전달된다.

교육용 예시. 실제 요청·발송·설정 변경을 실행한 결과가 아니다.

09연결 확인과 오류 기록의 범위

• 테이블·트랜잭션아래 상수 조회는 연결과 기본 질의 처리를 확인하지만 실제 업무 테이블 권한이나 트랜잭션 성공까지 증명하지 않는다.
• 오류 응답의 정보 노출오류 응답에 질의문과 내부 구조를 노출하지 않고, 로그에서도 인증 정보와 개인정보를 보호한다.

SELECT 1 AS connection_check;

연결한 데이터베이스에서 상수 조회만 수행하는 기본 확인 예시이다.

10두 변경을 하나의 트랜잭션으로 묶기

• 두 변경의 일관성재고에서 한 개를 차감하고 주문 한 건을 기록하는 업무를 생각한다. 첫 변경만 성공하고 두 번째가 실패하면 재고와 주문이 서로 맞지 않는 상태가 될 수 있다.
• 성공 확정·중간 취소트랜잭션 안에서 필요한 조건을 확인하고 두 변경을 수행한 뒤 커밋하면 하나의 처리 단위로 확정할 수 있다. 오류가 발생하면 해당 트랜잭션의 변경을 롤백하여 중간 결과가 최종 상태로 남지 않도록 한다.
• 오류 후 상태 규칙실제 오류 뒤 처리 방식은 DBMS와 드라이버의 트랜잭션 상태 규칙에 맞춰야 한다.

  • 원자성만으로 모든 동시성 문제가 해결되지는 않는다.
• 동시 요청의 갱신 충돌두 요청이 같은 남은 재고를 읽은 뒤 각각 수정하면 설계에 따라 중복 판매나 갱신 손실이 생길 수 있다.
• 잠금·격리 수준조건부 갱신, 적절한 잠금, 격리 수준, 제약 조건을 조합하고 경쟁에서 실패한 요청의 재시도 정책을 정한다.
• 트랜잭션·스키마한 트랜잭션 안에서 지킨 일관성은 스키마 제약과 업무 로직이 올바르게 작성됐다는 전제에도 의존한다.
• ACID 보장 조건ACID라는 약어를 붙였다는 이유로 잘못된 업무 코드를 DB가 자동 교정하지는 않는다.

1. 트랜잭션 시작재고 변경과 주문 기록을 하나의 경계에 넣는다.
2. 재고 조건 확인·차감동시성 정책에 맞춰 재고를 차감하되 아직 커밋하지 않는다.
3. 주문 기록 시도주문 삽입에서 제약 조건 위반이 발생하는 상황을 가정한다.
4. 오류 전달애플리케이션이 오류와 트랜잭션 상태를 확인한다.
5. ROLLBACK이 트랜잭션에서 확정하지 않은 변경을 취소한다.
6. 취소 결과 확인중간 변경을 성공으로 보고하지 않고 해당 주문 실패를 처리한다.

11느린 질의와 잘못된 결과의 구분

• 관계와 결과 행 수같은 행이 예상보다 많이 나오는 경우에는 JOIN 조건과 관계의 다대일·다대다 구조를 먼저 확인한다.
• 인덱스로 못 고치는 오류성능이 느리다는 이유로 인덱스만 추가해도 잘못된 관계 연산은 고쳐지지 않는다.
• 선택도·실행 계획행 수와 필터 선택도, 정렬·집계 범위를 확인한 뒤 실행 계획의 실제 접근 방식을 읽는다.
• 결과와 처리 비용인덱스 사용 여부 자체가 목표가 아니라 필요한 결과를 적절한 비용으로 구하는 것이 목표다.

  • 작은 테이블에서는 전체 읽기가 합리적인 선택일 수도 있다.
• 잠금응답 지연은 질의 자체의 연산량뿐 아니라 잠금 대기, 연결 풀 고갈, 네트워크와 디스크 지연에서도 생긴다.
• 실행 시간·잠금 대기실행 시간과 대기 시간을 분리하고 오래 열린 트랜잭션이 다른 작업을 붙잡고 있는지 확인한다.
• 입력 값입력 값은 바인딩하고 식별자·정렬 방식처럼 질의 구조에 영향을 주는 요소는 허용 범위를 정한다.
• 실행 계획운영 DB에서 실행 계획을 확인할 때 실제 실행을 수반하는 옵션의 영향도 구분해야 한다.

  • 조회처럼 보이는 진단이 무거운 업무 질의를 재실행할 수 있다.