Today I Learned

# Inner Join

  • 모든 데이터를 하나의 테이블에 저장한다면 아래와 같은 문제들이 발생한다.
    1. 데이터 중복(Redundancy)
      • 같은 값의 데이터가 여러 행에 걸쳐 반복 저장된다. 이는 저장공간의 큰 낭비이다.
    2. 갱신 이상(Update Anomaly)
      • 고객이 이메일 주소를 변경한 경우 모든 행에 걸쳐 변경된 이메일 주소를 모두 변경해야 한다.
      • 누락되는 경우 데이터 일관성이 깨진다.
    3. 삽입 이상 (Insertion Anomaly)
      • 데이터 관계에 의해 삽입이 불가능한 시나리오가 존재한다.
      • 주문 테이블 + 상품 정보 테이블이 합쳐져있다면, 새로운 상품을 등록해야 할때 주문이 발생해야 등록 가능한 비정상 상태가 된다.
    4. 삭제 이상 (Deletion Anomaly)
      • 유저 주문 기록을 삭제하려고 하는데도 유저 정보 자체가 영구적으로 삭제될 수 있다.
  • 이러한 문제 해결을 위해 DB 설계 시 정규화 과정을 거친다.
  • 데이터 조회 시 정규화로 인해 흩어진 데이터들을 다시 모아야 하는데, 이때 사용하는 기술이 조인이다.
  • PK와 FK를 통해 이들을 합친다.
  • 내부 조인(INNER JOIN)
    • 두 테이블을 연결할때 양쪽 테이블에 모두 공통으로 존재하는 데이터만을 결과로 보여준다.
    • 기준이 되는 컬럼 값이 서로 일치하는 행들만 보여준다.
SELECT 컬럼1, 컬럼2..
FROM 테이블 A
INNER JOIN 테이블 B
ON 테이블A.연결컬럼 = 테이블B.연결컬럼
  • ON을 통해 두 테이블을 어떤 조건으로 연결할지 명시한다.
    • JOIN은 기본적으로 내부 조인으로 동작하기 때문에 INNER는 생략해도 된다.
  • 내부 조인은 두 테이블에 대해 ON에 명시된 연결 컬럼을 기준으로 옆으로 길게 붙여버린 테이블을 생성해준다.
  • 조인 시 같은 이름의 컬럼이 두 테이블에 모두 있는 경우 SELECT에서 테이블명까지 명시해야 한다.
  • 조인 쿼리의 논리적 순서는 다음과 같다.
    1. FROM / JOIN: 조회할 테이블과 조인할 테이블을 연결한다.
    2. WHERE를 통해 필터링한다.
    3. SELECT를 통해 조회 결과를 반환한다.
  • 내부 조인은 교집합을 찾는 것과 동일하다.
  • 내부 조인은 양방향이다.
    • A -> B로 조인할 수 있다면 B -> A로 조인하는 것도 가능하며, 결과 역시 동일하다.
    • A 교집합 B가 B 교집합 A와 동일한 것과 같다.
    • 결과는 동일하지만 쿼리를 읽는 사람 입장에서 FROM A JOIN B라고 하면, A 테이블이 중심이 되는 것으로 읽기 쉽다.
    • 조회 결과의 중심이 될 테이블을 FROM에 넣어주면 된다.

테이블 별칭

  • 실무에서는 테이블 별칭을 자주 사용한다.
SELECT
    u.user_id,
    u.name,
    o.order_date
FROM orders o
JOIN users u ON o.user_id = u.user_id
WHERE o.status = 'COMPLETED';
  • FROM orders AS o로 작성해도 되지만, AS를 생략하는 것이 일반적이다.
  • 컬럼 별칭 시 사용하는 AS는 생략하지 않고 쓰는 것이 일반적이다.

# OUTER JOIN

  • INNER JOIN은 짝이 맞는 데이터만 보여준다.
  • OUTER JOIN을 통해 한쪽 테이블에만 존재하는 데이터도 결과에 포함시킬 수 있다.
  • LEFT OUTER JOIN: 집합에서 교집합 + 왼쪽 집합 나머지 요소를 포함하는 것과 같은 형태이다.
    • 일단 왼쪽 테이블의 모든 데이터는 결과에 포함한다.
    • 오른쪽 테이블에 짝이 맞지 않는 데이터가 있으면 그 자리는 NULL로 채운다.
  • RIGHT OUTER JOIN: 집합에서 교집합 + 오른쪽 집합 나머지 요소를 포함하는 것과 같은 형태이다.
    • 중간에 위치한 OUTER 키워드는 생략 가능하다.
    • 일단 오른쪽 테이블의 모든 데이터는 결과에 포함한다.
    • 왼쪽 테이블에 짝이 맞지 않는 데이터가 있으면 그 자리는 NULL로 채운다.
  • FULL OUTER JOIN으로 모든 요소를 포함하는 조인도 가능하지만 잘 사용하지는 않는다.
  • 보통 LEFT JOIN을 주로 사용하는 것이 직관적이다.
  • 조인은 부모 -> 자식으로의 조인인지, 자식 -> 부모로의 조인인지 방향에 따라 결과 행 개수가 늘어날 수 있고 줄어들 수 있다.
    • 부모는 PK, 자식은 FK를 갖는다.
    • PK는 절대 중복될 수 없지만 FK는 중복될 수 있다.
    • 부모 -> 자식으로 조인되는 경우 결과 행의 개수가 늘어날 수 있다.
    • 자식 -> 부모로 조인되는 경우 행의 개수가 절대 더 늘어날 수 없다.
  • 조인은 주로 PK FK 관계 기반으로 하는 경우가 많지만, 두 테이블의 특정 열 값의 동일성을 가지고도 처리 가능하다.

# SELF JOIN

id name manager_id
1 김회장 NULL
2 박사장 1
3 이부장 2
4 최과장 3
5 정대리 4
6 홍사원 4
  • 위와 같이 테이블 내에서 한 row가 다른 row의 아이디를 참조하고 있다.
  • 이때 테이블 별칭을 활용하여 같은 테이블인데도 다른 테이블로 인식하도록 하여 조인할 수가 있다.
    • 이렇게 되면 하나의 테이블을 자기 자신과 조인할 수 있게 되며, 이를 SELF JOIN이라 한다.
SELECT
    e.name AS employee_name,
    m.name AS manager_name
FROM employees e
JOIN employees m ON e.manager_id = m.employee_id
  • 이런식으로 조인을 설계할 수 있다.

# CROSS JOIN

  • INNER, OUTER, SELF는 모두 ON 관계를 기반으로 이미 존재하는 관계를 찾아내는 작업이다.
    • 쉽게말해 짝을 어떻게 맞추느냐는 것이다.
  • 크로스 조인은 한쪽 테이블 모든 행을 다른쪽 테이블 모든 행과 하나씩 전부 연결하는 단순한 조인이다.
    • 이를 수학에서 카테시안 곱 연산이라고 한다.
    • A테이블에 M개의 행, B테이블에 N개의 행이 있으면 M x N개의 행을 갖는 최종 결과물이 산출된다.
SELECT *
FROM colors
CROSS JOIN sizes;

INSERT INTO product_options (product_name, size, color)
SELECT
    CONCAT('기본티셔츠-', c.color, '-', s.size) AS product_name,
    s.size,
    c.color
FROM sizes AS s
CROSS JOIN
    colors AS c;
  • 이런식으로 SELECT 결과의 컬럼 순서와 타입이 일치할 때, VALUES 대신 SELECT한 결과를 통째로 INSERT하는 것도 가능하다. (INSERT INTO ... SELECT)

# 서브쿼리

  • 두 단계의 쿼리를 하나의 작업 단위로 묶을때 서브쿼리를 사용한다.
  • 바깥쪽 메인 쿼리가 실행되기 전, 괄호 안의 서브쿼리가 먼저 실행된다.
    • 이후 서브쿼리 실행결과를 바깥 메인쿼리에 전달하여 메인쿼리가 해당 결과를 사용한다.
-- example
SELECT name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);
  • 서브쿼리는 다양한 위치에서 활용된다.
  • 반환하는 행과 컬럼 수에 따라 종류가 나뉘어지고, 사용 위치와 연산자에 따라 역할이 결정된다.
분류 기준 종류 위치 / 특징 예시
위치 기준 WHERE 서브쿼리 조건 필터링에 사용 WHERE id = (SELECT ...)
FROM 서브쿼리 임시 테이블처럼 사용 (인라인 뷰) FROM (SELECT ...) t
SELECT 서브쿼리 컬럼 값으로 사용 (스칼라) SELECT (SELECT ...) AS x
동작 방식 단일행 서브쿼리 결과가 1행 1열 =, <, > 연산자 사용
다중행 서브쿼리 결과가 여러 행 IN, ANY, ALL, EXISTS
다중열 서브쿼리 결과가 여러 열 (col1, col2) IN (...)
상관 서브쿼리 외부 쿼리를 참조, 행마다 반복 실행 WHERE o.id = u.id

# 스칼라 서브쿼리

  • 1x1 결과가 반환되는 서브쿼리를 스칼라 서브쿼리라 한다.
SELECT
    name,
    address
FROM users
WHERE address = (
    SELECT
        u.address
    FROM users u
    JOIN orders o ON u.user_id = o.user_id
    WHERE o.order_id = 1
)
  • 스칼라 쿼리는 서브쿼리 결과가 반드시 무슨 일이 있어도 하나의 행만 반환해야 한다.
  • address = (결과 1, 결과 2) 비교를 할 수가 없기 때문이다.

# 다중 행 서브쿼리

SELECT *
FROM orders
WHERE product_id IN (
    SELECT product_id
    FROM products
    WHERE category = '전자기기'
)
  • IN 연산을 활용하여 특정 컬럼값이 서브쿼리 반환 결과로 나온 여러 행 내에 속하는지 확인 가능하다.

ANY, ALL + 비교연산

  • ANY, ALL은 비교연산자와 함께 사용되어 서브쿼리가 반환한 여러 값들과 비교하는 역할을 한다.
  • > ANY (서브쿼리): 서브쿼리가 반환한 결과값 중 최솟값보다 크면 참이다.
  • > ALL (서브쿼리): 서브쿼리가 반환한 결과값 중 최댓값보다 크면 참이다.
  • < ANY (서브쿼리): 서브쿼리가 반환한 결과값 중 최댓값보다 작으면 참이다.
  • < ALL (서브쿼리): 서브쿼리가 반환한 결과값 중 최솟값보다 작으면 참이다.
  • = ANY (서브쿼리): IN과 동일한 역할이다.
-- price > 100과 동일
WHERE price > ANY (100, 200, 300)

-- price > 300과 동일
WHERE price > ALL (100, 200, 300)

ANY ALL vs MIN MAX

  • ANY ALL은 IN이나 MIN, MAX로 대체할 수 있는 경우가 많고, 집계함수를 쓰는 것이 더 직관적인 쿼리가 될 경우가 많다.

# 다중 컬럼 서브쿼리

  • 하나의 행을 반환하지만 여러 컬럼을 담을 때 다중 컬럼 서브쿼리를 사용한다.
SELECT *
FROM orders
WHERE (user_id, status) = (
    SELECT o.user_id, o.status
    FROM orders o
    WHERE o.order_id = 3
)
  • 다중 컬럼 데이터는 WHERE절에서 여러 컬럼을 괄호 안에 묶어 분해할 수 있다.
  • 다중 컬럼 서브쿼리 역시 반드시 하나의 행이어야 한다.
  • 만약 결과가 여러 행인 경우 IN 연산자를 사용해야 한다.
SELECT
    o.order_id,
    o.user_id,
    o.order_date,
    u.name,
    p.name
FROM orders o
JOIN products p ON o.product_id = p.product_id
JOIN users u ON o.user_id = u.user_id
WHERE (o.user_id, o.order_date) IN (
    SELECT user_id, MIN(order_date)
    FROM orders
    GROUP BY user_id
)

# 상관 서브쿼리

  • 상관 서브쿼리는 외부 서브쿼리의 값을 내부 서브쿼리에서 참조하는 쿼리이다.
  • 자신이 속한 카테고리의 평균 이상인 상품들을 찾으시오
    • 위와 같은 쿼리의 경우 각 행을 순회하면서 각 행마다의 카테고리에 해당하는 모든 상품들을 모아 평균값을 계산해야 한다.
  • 상관 서브쿼리 동작 방식을 정리하면 다음과 같다.
    1. 메인 쿼리가 행을 읽는다.
    2. 읽어들인 행의 값을 서브쿼리에 전달한 뒤 서브쿼리가 실행된다.
    3. 서브쿼리 결과를 활용하여 메인쿼리 WHERE 조건을 판단한다.
    4. 다음 행을 읽으면서 1~3번 과정을 계속 반복한다.
  • 비상관 서브쿼리의 경우 서브쿼리 1회 실행 후 해당 결과를 메인쿼리가 사용하고 끝난다.
SELECT
    *
FROM products p1
WHERE p1.price >= (
    SELECT AVG(p2.price)
    FROM products p2
    WHERE
        p2.category = p1.category
)
  • p1의 카테고리 값을 서브쿼리에서 참조중인 것을 볼 수 있다.
SELECT
    *
FROM products
WHERE
    product_id IN (SELECT DISTINCT product_id FROM orders);
  • 위 쿼리 수행 과정에서 IN 비교연산 시 서브쿼리 처리 결과를 메모리에 잠시 보관해야 한다.
  • 사실 위 쿼리 목적 자체는 서브쿼리 결과 내에 product_id가 포함되었는지 여부만 중요하기 때문에 EXISTS를 쓰는 것이 더 효율적이다.
    • EXISTS는 서브쿼리 결과 행이 1개 이상이면 TRUE / 0개면 FALSE이다.
SELECT
    *
FROM products p
WHERE EXISTS (
    SELECT
        1
    FROM orders o
    WHERE o.product_id = p.product_id
);
  • SELECT 1은 반환 결과를 신경쓰지 않겠다는 관례적 표현이다.
  • EXISTS에 의한 서브쿼리 실행은 조건에 맞는 행을 찾자마자 즉시 결과를 반환한다.
  • NOT EXISTS를 쓰면 존재하지 않음을 확인할 수 있다.

# SELECT 서브쿼리

  • WHERE에서 사용하는 서브쿼리는 필터링할 조건을 동적으로 만들어내는 것에 그 목적이 있었다.

  • SELECT 내에 포함되는 서브쿼리는 그 자체로 하나의 컬럼으로 동작하게 된다.

    • SELECT 서브쿼리는 반드시 1x1의 스칼라 서브쿼리를 사용해야 한다.

  • SELECT 서브쿼리는 비상관, 상관 서브쿼리 모두 수행 가능하다.

    • 비상관 서브쿼리부터 확인해보자.
    • 메인쿼리 실행 전 SELECT 서브쿼리를 먼저 단 한번만 실행한다.
    • 계산된 값을 기억한 뒤 메인쿼리를 실행하고, 메인쿼리 수행 과정에서 행을 새로 가져올때마다 계산해둔 값을 그대로 추가한다.
SELECT
    name,
    price,
    (SELECT AVG(price) FROM products) AS avg_price
FROM products;
  • SELECT 상관 서브쿼리는 다음과 같다.
SELECT
    name,
    (
        SELECT
            COUNT(*)
        FROM orders o
        WHERE o.product_id = p.product_id
    ) AS total_count
FROM products p;
  • 외부 메인쿼리 테이블의 id값을 참조하고 있음을 볼 수 있다.

# 테이블 서브쿼리

  • WHERE는 동적 필터, SELECT는 새로운 컬럼으로 서브쿼리의 역할이 있음을 알 수 있다.
  • 서브쿼리는 FROM절에도 위치할 수 있는데, 하나의 독립된 가상 테이블처럼 역할을 하게 된다.
  • 인라인에서 정의되는 가상 테이블(뷰)와 같다고 하여 인라인 뷰라고도 불린다.
SELECT
--    p.name,
    MAX(p.price)
FROM products p
GROUP BY p.category;
  • 위 쿼리에서 name 컬럼 주석을 해제하고 수행하면 에러가 발생한다.
    • 동일 카테고리에서 서로 다른 이름의 상품들이 존재할 수 있기 때문이다.
  • 동일 카테고리 최고가를 찾고 해당 가격과 일치하는 상품을 찾아야 한다.
SELECT
    p.product_id,
    p.name,
    p.price
FROM products p
JOIN (
    SELECT
        p.category,
        MAX(p.price) AS max_price
    FROM products p
    GROUP BY p.category
) AS cmp
ON p.category = cmp.category AND p.price = cmp.max_price
  • 위와 같이 임시 테이블을 하나 생성하여 별칭을 추가한 뒤 조인 연산을 하는 것이 가능하다.

# JOIN vs 서브쿼리

  • 서브쿼리로 풀 수 있는 문제는 JOIN으로도 풀 수 있는 경우가 많다.
-- 조인 사용
SELECT
    o.order_id
FROM orders o
JOIN users u ON u.user_id = o.user_id
WHERE u.address LIKE '서울%';

-- 서브쿼리 사용
SELECT
    o.order_id
FROM orders o
WHERE o.user_id IN (
    SELECT
        u.user_id
    FROM users u
    WHERE u.address LIKE '서울%'
);
  • 두 방법중 무엇을 선택해야할지에 대한 기준은 다음과 같다.
    1. 성능: 일반적으로 JOIN이 서브쿼리보다 더 좋거나 최소 동일하다.
      • JOIN 연산이 쿼리 옵티마이저에게 테이블에 대한 전체적인 그림을 미리 보여주기 때문에 최적화가 더 수월하다.
      • 반면 서브쿼리는 동적으로, 단계적으로 실행되는 경우가 많다.
      • 최근 DB 옵티마이저 성능이 매우 개선되어 두 쿼리의 성능은 비슷할 확률이 높다.
    2. 가독성
      • 서브쿼리는 복잡한 로직을 이해하기 쉽게 만들어준다.
      • 조인은 필요한 데이터소스를 한 눈에 보여주고, 여러 테이블 컬럼을 참조할때는 더 깔끔하다.
  • 조인을 먼저 고려하고, 서브쿼리 가독성이 더 좋다면 서브쿼리를 택하자.
  • EXISTS를 활용하자.
  • 성능 의심이 되면 EXPLAIN을 사용해야 한다.

# UNION

  • JOIN은 옆으로 붙여서 더 많은 정보를 가진 컬럼들을 만드는 기술이다.
  • UNION은 데이터 집합을 아래로 붙여서 더 많은 행을 갖는 집합으로 만드는 기술이다.
SELECT
    name,
    email
FROM users
UNION
SELECT
    name,
    email
FROM retired_users;
  • UNION 연결시 규칙은 다음과 같다.
    1. 모든 SELECT문은 컬럼 갯수가 동일해야 한다.
    2. SELECT의 같은 위치에 있는 컬럼들은 호환 가능한 데이터 타입이어야 한다.
    3. 최종 결과 컬럼 이름은 첫번째 SELECT 컬럼이름을 따라간다.
  • UNION은 내부적으로 완전히 중복되는 행은 자동으로 제거해준다.

# UNION ALL

  • UNION ALL은 두 조회 결과를 합칠때 중복 제거를 하지 않는다.
  • 위와 같은 이유로 UNION ALL이 UNION보다 훨씬 빠르다.
  • 비즈니스 요구사항으로 중복 제거가 필수적인 경우에만 UNION을 사용하고, 이외에는 UNION ALL을 먼저 고려하는 것이 좋다.

# UNION 정렬

  • 유니온 쿼리 시 정렬은 가장 마지막에 ORDER BY로 단 한번만 사용해야 한다.
  • 또한, ORDER BY에 사용할 컬럼명은 유니온 시 첫번째 연산 시 사용한 컬럼의 별칭을 사용해야 한다.

컬럼에 하드코딩 고정 값 넣기

SELECT
    created_at AS `이벤트_날짜`,
    '고객 가입',
    name
FROM users
  • 위와 같이 직접 값을 넣을 수 있다.

# CASE

  • SELECT, JOIN, UNION, WHERE 등은 조회하거나, 테이블을 합치거나, 특정 조건으로 걸러내는 방법들이었다.
  • 이들은 데이터의 구조를 바꾸거나 범위를 한정하는 기술이다.
  • CASE는 데이터 자체를 동적으로 가공하고 새로운 의미를 부여하는 기술이다.
    • 상품목록 조회 시 가격대에 따라 고가, 중가, 저가와 같이 알아보기 쉬운 등급을 표시하고싶다면?
  • 애플리케이션 레벨에서 처리하는 것도 가능하지만, 쿼리 자체적으로도 가능하다.

# 단순 CASE문 (Simple CASE Expression)

  • 단순 케이스문은 특정 하나의 컬럼이나 표현식 값에 따라 결과를 다르게 하고 싶을때 사용한다.
CASE 비교대상_컬럼_또는_표현식
    WHEN 값1 THEN 결과1
    ..
    ELSE 그_외의_결과
END
  • 위에서 아래 순서대로 조건을 평가하며, 가장 먼저 일치하는 WHEN 절을 만나면 즉시 결과를 반환하고 평가를 종료한다.

# 검색 CASE문 (Searched CASE Expression)

  • 하나의 특정 값을 비교하는 대신, 각 WHEN 절에 독립적인 조건식을 사용하여 복잡한 논리 구현 시 사용한다.
CASE
    WHEN 조건1 THEN 결과1
    WHEN 조건2 THEN 결과2
    ELSE 그_외의_결과
END

-- 예시
CASE
    WHEN price >= 100000 THEN '고가'
    WHEN ..
    ELSE '저가'
END

CASE 주의사항

  • 실행 순서는 위에서부터 시작되고, 조건 만족시 즉시 반환된다.
  • 비교연산자 사용시 조기 리턴으로 인해 더 빡빡한 조건 적용이 안될 수 있다.

정렬 시 CASE 사용

  • SELECT 외에도 ORDER BY에도 케이스문을 사용할 수 있다.
ORDER BY
    CASE
        WHEN price >= 100000 THEN 1
        WHEN price >= 30000 THEN 2
        ELSE 3
    END ASC, -- 1차 정렬 기준
    price DESC; -- 2차 정렬 기준
  • 커스텀 정렬 기준을 CASE문으로 작성할 수 있다.

# CASE 그룹핑

  • CASE문은 집계함수나 GROUP BY와 결합될때 강력하다.
SELECT
    CASE
        WHEN YEAR(birth_date) >= 1990 THEN '1990년대생'
        WHEN YEAR(birth_date) >= 1980 THEN '1980년대생'
        ELSE '그 이전 출생'
    END AS birth_decade,
    COUNT(*) AS customer_count
FROM users
GROUP BY
    birth_decade;
  • 표준 쿼리 처리 순서로는 GROUP BY가 먼저 실행되지만, 모던 DBMS들은 별칭 사용을 허용한다.

# CASE 조건부 집계

  • CASE문이 SUM, COUNT 내부로 들어가 동작을 할 수도 있다.
  • 이를 조건부 집계라고 한다.
  • 하나의 쿼리로 컬럼 상태값 분류를 하고,각 분류별 카운팅을 하려면? 거기에 총 상태값들을 합친 카운트까지 붙이고싶다면?
SELECT
    status,
    COUNT(*)
FROM orders
GROUP BY status
UNION
SELECT
    'total_count',
    COUNT(*)
FROM orders;
  • 이런식으로 UNION 처리를 해도된다.
    • 그러나 테이블을 transpose하여 피봇팅 테이블 형태로 각 분류 자체가 컬럼이 되도록 하려면, 서브쿼리 SELECT문을 활용해야 한다.
    • 이 경우 각 컬럼별로 orders 테이블을 모두 읽어들여야 하므로 성능 문제가 크다.
  • 위의 문제 해결을 위해 CASE를 품은 집계함수의 다양한 패턴이 존재한다.
    1. COUNT(CASE WHEN status = 'A' THEN 1 END)
      • CASE문은 ELSE가 없으면 어느 케이스에도 해당되지 않는 값을 NULL로 처리한다.
      • 이를 활용하여 특정 케이스에 대해서만 카운팅할 수 있다.
    2. SUM(CASE WHEN status = 'A' THEN 1 ELSE 0 END)
      • ELSE를 추가하여 케이스에 해당되지 않는 값을 0으로 처리한다.
SELECT
    COUNT(*) AS total_orders,
    SUM(CASE WHEN status = 'COMPLETED' THEN 1 ELSE 0 END) AS completed_count,
    SUM(CASE WHEN status = 'SHIPPED' THEN 1 ELSE 0 END) AS shipped_count,
    SUM(CASE WHEN status = 'PENDING' THEN 1 ELSE 0 END) AS pending_count
FROM orders;
  • GROUP BY와 조합하여 각 그룹으로 구분된 데이터들에 대해 집계처리를 할 수도 있다.
    • 그룹화가 먼저 되고, 그룹화된 각 그룹 내에서 집계를 하게 된다.
SELECT
    p.category,
    COUNT(*) AS total_orders,
    SUM(CASE WHEN status = 'COMPLETED' THEN 1 ELSE 0 END) AS completed_count,
    SUM(CASE WHEN status = 'SHIPPED' THEN 1 ELSE 0 END) AS shipped_count,
    SUM(CASE WHEN status = 'PENDING' THEN 1 ELSE 0 END) AS pending_count
FROM orders o
JOIN products p ON p.product_id = o.product_id
GROUP BY p.category;

# View

  • 뷰는 실제 데이터를 가지고 있지 않은 가상의 테이블이다. 그 실체는 데이터베이스에 이름과 함께 저장된 하나의 SELECT 쿼리문이다.
  • 복잡한 쿼리 실행없이 SELECT * FROM 직접_생성한_뷰 형태로 쿼리 결과를 받아오면 된다.
    • 뷰는 우리가 조회할 때마다 항상 최신 상태의 원본 테이블을 기준으로 쿼리가 실행된다.
    • 따라서 뷰의 데이터는 항상 최신 상태를 유지한다.
  • 뷰는 편리성 외에, 원본 테이블에 대한 접근권한을 주지 않고 뷰를 통해서만 제한된 데이터에 접근할 수 있게 설계가 가능하다.
  • 하나의 함수와 비슷하게 논리적 독립성도 갖고 있다.

# CREATE VIEW

CREATE VIEW 뷰이름
AS SELECT 쿼리문;
  • 뷰는 일반적으로 v_혹은 view_와 같은 접두사를 붙여 뷰라는 것을 명시하는 경우가 많다.
  • 아래는 예시 쿼리이다.
CREATE VIEW v_category_order_status AS
SELECT
    p.category,
    COUNT(*) AS total_orders,
    SUM(CASE WHEN o.status = 'COMPLETED' THEN 1 ELSE 0 END) AS completed_count,
    SUM(CASE WHEN o.status = 'SHIPPED' THEN 1 ELSE 0 END) AS shipped_count,
    SUM(CASE WHEN o.status = 'PENDING' THEN 1 ELSE 0 END) AS pending_count
FROM orders o
JOIN products p ON o.product_id = p.product_id
GROUP BY
    p.category;

# 뷰 조회하기 - SELECT

  • SELECT * FROM 뷰_이름;
  • 뷰 또한 테이블처럼 다룰 수 있으므로 WHERE, ORDER BY와 같은 연산 적용이 가능하다.

# 뷰 수정하기 - ALTER VIEW

  • 컬럼 정보, 데이터 구조 수정 등이 가능하다.
  • ALTER TABLE과 유사하다.
ALTER VIEW 뷰_이름 AS
SELECT
    ...

# 뷰 삭제하기 - DROP VIEW

  • 뷰를 삭제한다.
  • 뷰를 삭제한다고 해서 원본 데이터에는 전혀 영향이 없다.
DROP VIEW 뷰_이름;

# 뷰 사용 시 주의사항

  • 뷰 내부에서 실제 실행되는 쿼리의 성능을 잘 알고 사용해야 한다.
    • 내부 쿼리 동작이 무거운 경우, 뷰의 편리성만 믿고 계속해서 실행하는 경우 시스템에 큰 부하를 줄 수 있다.
    • 비슷한 이유로 뷰 위에 뷰를 쌓는 구조도 피해야 한다.
  • 뷰는 기본적으로 조회용이며, GROUP BY, DISTINCT와 같은 연산이 적용된 뷰는 데이터 수정이 어려운 경우가 많다.

# 인덱스

  • 데이터 규모가 큰 경우 단순 WHERE절 기반으로 쿼리를 하는 것도 상당한 시간이 소요된다.
SELECT * FROM items WHERE item_name = '게이밍 노트북';
  • 이 경우 검색이 느린 이유는 풀 테이블(FULL TABLE SCAN) 스캔이다.
  • item_name컬럼의 게이밍 노트북이라는 값이 어디에 있는지 알 수 있는 힌트가 전혀 없다.
  • 풀 테이블 스캔은 O(n) 복잡도를 갖는다.
  • 일반적인 기업용 서버 환경에서 (SSD 기반) 행당 1KB 데이터를 가진 테이블의 예상 응답시간은 다음과 같다.
    1. 100만건
      • 총 데이터 크기: 약 1GB
      • 예상 응답시간: 수 초
    2. 1,000만건
      • 총 데이터 크기: 약 10GB
      • 예상 응답시간: 수초 ~ 1분 내외
    3. 1억건
      • 총 데이터 크기: 100GB
      • 수십초 이상
  • 풀테이블 스캔은 매우 비용이 높은 작업이기에 서비스 핵심 기능에서 가급적 풀테이블 스캔이 발생하지 않도록 설계해야 한다.
    1. 인덱스 활용: WHERE절에 자주 사용되는 검색 조건 컬럼에는 인덱스를 생성하여, 풀테이블 스캔을 방지해야 한다.
    2. 실행계획 확인: 쿼리 실행 전 실행계획(Execution Plan)을 확인하여 풀테이블 스캔이 발생하는지 점검해야 한다.
    3. 풀스캔이 불가피한 작업이라면 서비스 이용자가 적은 시간대를 선택한다.

# 인덱스 소개

  • 인덱스는 특정 컬럼의 데이터를 기반으로 생성되는 원본 테이블과 별개의 특수한 자료구조이다.
  • 특정 컬럼의 값과 해당 값을 가진 실제 데이터 행의 위치를 한 쌍으로 저장한다.
  • 인덱스 내부 데이터는 항상 정렬된 상태를 유지한다.
  • 인덱스는 클러스터 인덱스(Clustered Index)와 보조 인덱스(Secondary Index) 두 종류로 구분된다.
    • 클러스터 인덱스: PK를 기반으로 만드는 인덱스이다.
      • 원본 데이터 자체를 인덱스에 함께 보관하여 원본 데이터를 빠르게 찾을 수 있다.
    • 보조 인덱스: 원본 데이터 PK를 함께 보관한다.
      • 위의 PK값으로 클러스터 인덱스를 통해 데이터를 조회한다.
  • 이진트리 자료구조를 기반으로 데이터를 정렬하는데, 이때 Balanced Tree로 저장하여 한쪽 방향의 노드로만 데이터들이 저장되지 않도록 한다.
    • 최악의 경우 O(N) 시간복잡도가 나올 수 있음.
    • 이진 탐색트리를 통한 조회는 O(logN)으로 매우 좋은 성능을 보인다.
  • 먼저 인덱스를 생성할 컬럼의 값을 정렬하고, 정렬된 컬럼 각 값의 원본데이터 위치를 저장한다.
  • B-Tree는 조회 성능 개선 뿐 아니라, 노드별로 자식 노드를 더 많이 가질 수 있기 때문에 디스크 I/O 횟수도 줄여준다.
    • 알고리즘적 개선은 하드웨어 레이어 계층 차이를 뛰어넘을 수 없다.

# 인덱스 생성: CREATE INDEX

CREATE INDEX 인덱스이름 ON 테이블이름 (컬럼1, 컬럼2 .. );
  • idx_테이블명_컬럼명 형태로 인덱스 이름을 짓는 것이 일반적이다.
  • 쿼리 실행시 테이블의 모든 컬럼값을 읽어 정렬한 뒤 인덱스를 디스크에 생성한다.

# 인덱스 정보 확인: SHOW INDEX

컬럼 설명
Key_name 인덱스 이름 (PRIMARY, 또는 지정한 이름)
Column_name 인덱스가 걸린 컬럼 이름
Table 테이블 이름
Non_unique 0 = 유니크 인덱스, 1 = 중복 허용, 중복값이 포함될 수 있다는 의미
Cardinality 인덱스의 고유한 값의 갯수에 대한 추정치 (높을수록 중복도가 낮고, 인덱스 성능이 더 좋다고 봄)
Seq_in_index 복합 인덱스에서 컬럼 순서 (1, 2, 3...)
Collation 정렬 방식 (A = 오름차순, NULL = 정렬 없음)
Sub_part 부분 인덱스일 때 사용된 문자 수 (전체면 NULL)
Null YES = NULL 허용, '' = NOT NULL
Index_type 인덱스 구조 (BTREE, HASH, FULLTEXT 등)
  • MySQL은 PK / FK 제약조건을 설정하면 해당 컬럼에 대해서는 자동으로 인덱스를 생성해준다.
    • PK인 경우 PRIMARY 인덱스가 해당 컬럼에 대해 생성되어 있는 것을 볼 수 있을 것이다.

# 인덱스 삭제: DROP INDEX

  • 사용하지 않거나 쓰기 성능에 방해되는 인덱스는 삭제해야 한다.
DROP INDEX 인덱스이름 ON 테이블이름;

# 인덱스 사용 여부 검증: EXPLAIN

  • 데이터베이스는 쿼리를 어떻게 최적화하여 실행할지 계획하는 기능이 있다. 이를 쿼리 옵티마이저라고 한다.
  • 인덱스를 만들었다고 해도 쿼리 옵티마이저는 데이터 분포 / 쿼리 형태에 따라 풀스캔을 선택할 수 있다.
    • 인덱스가 실제로 쿼리에 사용되는지 확인하려면 EXPLAIN명령어를 쿼리문 앞에 붙인다.
  • EXPLAIN은 실제 쿼리를 실행하는 것이 아닌, 예측값들을 모아서 보여주는 명령어이다.
EXPLAIN SELECT * FROM items WHERE item_name = 'AA';
  • 출력되는 정보들을 정리하면 아래와 같다.
    • type값을 가장 중요하게 봐야한다. ALL 타입의 경우 테이블 풀 스캔이 일어난 것을 의미한다.
    • 인덱스 사용 시 ref, range등의 값이 표시된다.
컬럼 설명 주목 포인트
id 쿼리 실행 순서 숫자 클수록 먼저 실행
select_type SELECT 종류 SIMPLE, SUBQUERY, DERIVED 등
table 참조 테이블 -
type 접근 방식 ⭐ 가장 중요. 아래 별도 정리
possible_keys 사용 가능한 인덱스 목록 NULL이면 인덱스 없음
key 실제 사용된 인덱스 NULL이면 풀스캔
key_len 사용된 인덱스 길이(byte) 복합 인덱스에서 몇 개 컬럼 사용했는지 파악
rows 읽을 것으로 예상되는 행 수 낮을수록 좋음
filtered rows 중 조건에 맞는 비율(%) 100%에 가까울수록 좋음
Extra 추가 실행 정보 ⭐ 중요. 아래 별도 정리

# type 컬럼 — 성능 순서

type 설명 성능
system 테이블에 row가 1개 🟢 최상
const PK/UNIQUE로 단 1건 조회 🟢 최상
eq_ref JOIN에서 PK/UNIQUE 1건 매칭 🟢 매우 좋음
ref 인덱스로 여러 건 조회 🟡 좋음
range 인덱스 범위 스캔 (BETWEEN, >, <) 🟡 양호
index 인덱스 풀스캔 🔴 나쁨
ALL 테이블 풀스캔 🔴 최악

ALL 또는 index 가 나오면 인덱스 추가 또는 쿼리 튜닝 필요

# Extra 컬럼 — 주요 값

Extra 값 설명 성능
Using index 인덱스만으로 쿼리 해결 (커버링 인덱스) 🟢 좋음
Using where WHERE 조건으로 필터링 🟡 보통
Using filesort 정렬을 인덱스 없이 처리 🔴 나쁨
Using temporary 임시 테이블 생성 (GROUP BY 등) 🔴 나쁨
Using join buffer JOIN 시 버퍼 사용 (인덱스 없는 JOIN) 🔴 나쁨
NULL 특이사항 없음 🟢 정상

Using filesort + Using temporary 동시에 뜨면 즉시 튜닝 대상

# 인덱스와 동등 비교

  • 인덱스는 다음 상황들에서 사용된다.
    1. 동등 비교(=)
    2. 범위 검색(BETWEEN, <, >, LIKE..)
    3. ORDER BY를 통한 정렬
  • EXPLAIN type컬럼값이 ref인 경우 동등비교 조건이나 JOIN에서 인덱스를 사용했다는 의미이다.

LIKE 사용

  • LIKE절에서 인덱스를 사용하기 위해서는 와일드카드가(%) 검색어 뒤쪽에 위치해야 한다.
  • WHERE item_name LIKE 'test%';
    • 와일드카드가 앞에 있으면 시작점 불분명으로 인해 인덱스 활용이 불가능하다.
  • 내용 검색, 포함 검색 문제 해결을 위해 데이터베이스에서는 전문 검색(FULL-TEXT-Search) 기능을 제공한다.

# 인덱스와 정렬

  • 정렬작업은 비용이 많이 드는 작업이다.
  • 인덱스는 데이터가 특정 순서로 정렬된 자료구조이기 때문에, 이를 ORDER BY에 활용하면 정렬 작업 성능을 개선할 수 있다.
  • 데이터베이스는 정렬 시 filesort라는 정렬 작업을 진행하는데, 인덱스를 활용하면 이를 생략할 수 있다.
    • filesort는 메모리나 디스크를 사용하여 정렬하는 내부 프로세스이다.
  • 단일 컬럼 인덱스에서는 DESC와 같이 내림차순 정렬도 효율적으로 처리가 가능하다.
    • 데이터베이스 옵티마이저는 역방향 스캔을 지원한다.
    • 역방향 스캔시에도 filesort 과정이 생략된다.
  • 인덱스 생성 단계에서 애초에 내림차순으로 생성이 가능하다.
    • CREATE INDEX idx_index ON table (column DESC);

인덱스 손익분기점

  • 인덱스 사용 여부를 결정하는 기준은 손익 분기점이다.
    • 인덱스를 통해 데이터를 읽는 비용이 테이블 전체를 읽는 비용보다 높아지는 지점이다.
  • 인덱스 사용 비용: 인덱스 탐색 비용 + 인덱스에서 찾은 주소로 테이블에 접근하는 비용 (랜덤 I/O)
    • 랜덤 I/O의 경우 연속적 데이터를 읽는 것이 아니기 때문에, 하드웨어 레벨에서 동작해야 하는 부하가 존재한다.
    • 여기저기 흩어진 데이터를 랜덤으로 읽는다고 하여 랜덤 I/O라는 이름이 붙여졌다.
  • 풀 테이블 스캔 비용: 테이블 전체를 순차적으로 읽는 비용
  • 일반적으로 전체 데이터의 20~25% 이상 조회해야 하는 쿼리는 풀스캔이 더 낫다고 알려져있다.
EXPLAIN SELECT * FROM items WHERE price BETWEEN 50000 AND 100000;
EXPLAIN SELECT * FROM items WHERE price BETWEEN 1000 AND 100000;
  • 위 쿼리는 인덱스로 수행되고, 두번째 쿼리는 테이블 풀 스캔으로 수행된다.
  • 1000부터 실행되는 케이스에서 인덱스를 기반으로 원본테이블에 순차 참조하게 되는 비율이 20~25%를 넘어섰기 때문이다.
  • 참고로 적은 수의 데이터에서는 옵티마이저에 의해 인덱스가 동작하지 않을 확률이 높다.

# 커버링 인덱스

  • 기존 인덱스는 원본테이블에 다시 접근하는 랜덤 I/O 문제가 존재한다.
  • 테이블에 접근하는 참조 단계를 생략하기 위해 커버링 인덱스를 사용한다.
  • 커버링 인덱스란 쿼리에 필요한 모든 컬럼을 포함하고 있는 인덱스를 의미한다.
    • 특정한 명령어로 생성되는 인덱스라기 보다 상황적인 부분에 더 가깝다.
    • SELECT 절의 컬럼이 인덱스 컬럼과 PK만으로 충족될 때, 원본 테이블 랜덤 I/O 없이 인덱스만으로 쿼리가 완결되는 상황이면, 커버링 인덱스로서 역할을 하게 되는 것이다.
  • 인덱스는 원본 데이터와는 별개로 저장 공간을 차지한다. 쓰기작업 시 인덱스에도 변경사항이 반영되는 부하가 존재한다.
-- price, item_id가 인덱스 컬럼을 구성하고 있으므로 커버링 인덱스로 동작
EXPLAIN SELECT price, item_id FROM items WHERE price BETWEEN 50000 AND 100000;

-- name 컬럼이 추가되어 원본 테이블을 불가피하게 참조해야함.
EXPLAIN SELECT price, item_id, name FROM items WHERE price BETWEEN 50000 AND 100000;

CREATE INDEX idx_items_price_name ON items (price, item_name);
  • 위와 같이 두 컬럼을 모두 포함하는 인덱스를 생성한다.
  • 복합 인덱스는 컬럼 순서가 매우 중요하다.
    • 동등비교나 범위검색에 사용되는 컬럼을 앞에 두어야 효율적인 사용이 가능하다.
  • price가 같으면 item_name을 가지고 재정렬을 하는 구조이다.

# 복합 인덱스

  • 실제 운영 환경에서는 여러 조건을 조합하여 데이터를 검색하는 경우가 더 많다.
  • 이러한 다중 조건 쿼리 성능을 최적화하기 위해서는 복합 인덱스(Composite Index), 다중 컬럼 인덱스(Multi-column Index)를 사용하면 된다.
  • 복합 인덱스는 둘 이상의 컬럼을 묶어 하나의 인덱스로 만드는 것이다.
  • 복합 인덱스 사용 시에는 반드시 컬럼의 순서를 고려해야한다.
    • 컬럼 순서에 따라 성능이 좌지우지 될 수 있다.
  • 인덱스는 B-tree 구조 기반으로 사용되는데, 왼쪽컬럼부터 차례로 정렬된 형태를 갖는다.
  • 위와 같은 이유로 인덱스 생성 컬럼 순서에 따라, 조건 필터링 시 왼쪽 컬럼부터 순서대로 명시해야 하는데 이를 인덱스 왼쪽 접두어 규칙이라고 한다.
  • 예를 들어 (A,B,C) 순서로 인덱스를 생성했다면 WHERE 조건에 (A), (A, B), (A,B,C)로 조회를 하는 것이 효율적이다.
    • 앞 컬럼 없이는 뒷 컬럼 위치를 파악하지 못하게 되어 풀스캔이 발생한다.

복합 인덱스 대원칙

  • 인덱스는 순서대로 사용할것
  • 등호 조건은 앞으로, 범위 조건은 뒤로 보낼것
  • 정렬도 인덱스 순서를 따를 것
  • 복합 인덱스는 정렬 작업을 피할때 드러난다.
    • WHERE절과 ORDER BY의 정렬 방향이 인덱스 순서와 일치하면 데이터베이스는 filesort 작업을 생략하여 성능을 향상시킬 수 있다.
EXPLAIN SELECT * FROM items WHERE category = '전자기기' AND price > 100000 ORDER BY price;
  • items 테이블이 (category, price) 순서로 복합 인덱스가 정의되어 있다고 가정하자.
    • 위 경우 category 기준으로 인덱싱을 빠르게 한 뒤, 정렬된 price값을 가져올 수 있다.
    • 이미 두번째 컬럼 기준으로 이미 정렬이 되어있기 때문에 ORDER BY 없이도 정렬된 결과를 받아올 수 있다.
  • EXPLAIN 쿼리로 실행 계획을 할때, Extra에 Using filesort가 없도록 하는 것이 중요하다.

인덱스 범위조건과 성능

  • 위의 카테고리와 가격 예시에서 EXPLAIN SELECT * FROM items WHERE category >= '패션' AND price = 20000; 쿼리를 실행한다고 가정해보자.
  • 이때 category중 패션이라는 값을 갖는 행의 위치를 빠르게 찾아내고, 이후 price컬럼에서 20000이라는 값에 해당하는지 여부를 전체 스캔하게 된다.
    • category >= 패션인 행에서 price컬럼의 값이 정렬된 것이 아니기 때문에 해당 영역에서만 풀스캔이 이루어지는 것이다.
  • 이러한 이유로 최대한 =로 비교하는 조건절을 앞으로 빼는 것이 좋다.
  • 위의 경우, (price, category) 순서로 복합인덱스를 하나 더 추가하여 등호 조건을 앞으로 뺄수 있게끔 해줘야 한다.
    • 인덱스 추가 시 INSERT / UPDATE / DELETE 등의 쓰기 작업 시 부하와, 저장 공간 추가 소비에 의한 성능저하 문제를 고려해야 한다.

IN 절 활용

  • 범위 조건으로 인해 두번째 인덱스 컬럼을 활용하지 못하는 경우, 범위연산 대신 IN을 사용하여 해결되는 경우가 많다.
    • IN은 하나의 큰 범위가 아닌 여러개의 동등비교 조건의 묶음으로 인식된다.
EXPLAIN SELECT * FROM items WHERE category >= '패션' AND price = 20000;
  • price에 대해 인덱싱 수행
EXPLAIN SELECT * FROM items
WHERE category IN ('패션', '헬스/뷰티') AND price = 20000;
  • 정렬된 '패션' 데이터만을 부분 결과로 뽑고 price = 20000을 수행한 뒤, '헬스/뷰티'섹션에 대해 다시 정렬된 price를 뽑아 price = 20000을 수행한다.
  • 두개의 SELECT가 UNION ALL로 합쳐진 것이라 보면 된다.

# 인덱스 설계

  • 인덱스는 어떻게 만드느냐는 것보다 어디에 만드느냐가 더 중요하다.
  • 인덱스를 어디에 걸지 판단하는 가장 중요한 기준은 카디널리티(Cardinality)이다.
    • 카디널리티란 해당 컬럼에 저장된 값들의 고유성 정도를 나타내는 지표이다.
    • 카디널리티가 높음: 해당 컬럼에 중복되는 값이 거의 없음
    • 카디널리티가 낮음: 해당 컬럼에 중복되는 값이 많다.
  • 카디널리티가 높아야 한번의 검색에서 많은 수를 필터링 할 수 있다.
    • Btree 기준으로 한번의 검색에서 최대한 좁은 범위로 좁혀놔야 한다.
  • 인덱스 설계 가이드라인을 정리하면 다음과 같다.
    1. WHERE절에서 자주 사용되는 컬럼
    2. JOIN의 연결고리가 되는 컬럼 (FK)
      • MySQL은 자동으로 처리해주지만 PostgreSQL에서는 자동으로 처리해주지 않는다. (DBMS마다 다르다)
      • 조인 시 조인 컬럼 기준으로 빠르게 탐색을 해야한다.
    3. ORDER BY 절에서 자주 사용되는 컬럼
      • 정렬은 데이터 양이 많을 경우 매우 비용이 큰 작업이다.
      • 인덱스를 통해 정렬된 상태의 데이터를 빠르게 가져올 수 있으면 좋다.

# 인덱스 단점

  • 인덱스를 모든 컬럼에 걸게 되면 데이터베이스 성능이 최악으로 치닫게 된다.
  • 단점을 정리하면 다음과 같다.
    1. 인덱스는 Btree 구조를 가진 물리적 파일로 디스크에 저장된다. (추가 저장 공간이 필요)
      • 일반적으로 인덱스는 원본 테이블 크기의 10% 내외 공간을 추가로 차지한다고 알려져있다.
    2. 쓰기 성능이 저하된다.
      • SELECT 속도를 높이는 대가로, INSERT / UPDATE / DELETE 속도를 희생시킨다.
      • 데이터 변경이 이루어질때마다 모든 인덱스를 함께 수정한다.
  • 인덱스를 보험으로 생성하지 말고, 슬로우 쿼리 발견 시 개선하기 위한 목적으로 생성해야 한다.
  • 사용하지 않는 인덱스는 주기적으로 정리해야 한다.
  • 인덱스 컬럼은 가공하지 말고 원본상태 그대로 사용해야 한다.
    • WHERE SUBSTRING(item_name, 1, 5) = '게이밍'

# 데이터 무결성

  • 상품 가격에 마이너스 값이 들어가있거나, 주문수량에 -1값이 들어가있다면?
  • 이런식의 말이 되지 않는 데이터, 현실 세계에서 존재할 수 없는 데이터를 쓰레기 데이터라(Garbage Data) 한다.
  • 데이터 무결성 강제를 위해 테이블 특정 컬럼에 설정하는 규칙이 제약조건이다.(Constraint)
  • 제약 조건은 INSERT, UPDATE, DELETE를 할때 특정 규칙은 절대 어기면 안된다고 선언한다.

# 기본 제약조건

  1. NOT NULL
    • 해당 컬럼에 널값 저장을 허용하지 않는다.
    • email VARCHAR(255) NOT NULL
  2. UNIQUE
    • 중복 값 입력 방지
    • email VARCHAR(255) UNIQUE
  3. PRIMARY KEY
    • 행의 대표 식별자
    • NOT NULL과 UNIQUE 조건을 모두 포함
    • 테이블당 하나만 설정 가능
  4. DEFAULT
    • 기본값 설정
    • 특정 컬럼에 명시적인 값 설정을 하지 않은 경우 기본값으로 입력
    • status VARCHAR(50) DEFAULT 'PENDING'

# 외래키 제약조건

  • 데이터베이스의 힘은 관계에 있고, 이는 여러 테이블에 걸쳐 맺어진다.
  • 여기서 가장 중요한 참조 무결성 규칙이(Referential Integrity) 등장한다.
    • 자식 테이블에 INSERT / UPDATE시 부모 테이블에 존재하지 않는 컬럼 값을 자식 테이블 컬럼에 넣을려는 시도를 막는다.
    • 부모 테이블에서 DELETE / UPDATE시 자식 테이블에서 참조하는 값을 가진 행을 삭제하거나 변경하지 못하게 한다.

ON DELETE / ON UPDATE

  • 데이터베이스가 부모 데이터 삭제나 수정을 막는것이 기본값이고, 안전한 정책이다.
  • 만약 회원 탈퇴시 관련 모든 정보를 삭제해야 한다는 경우가 있는 경우, 예외적인 정책이 필요하다.
    • 이때 사용하는 것이 두 옵션이다.
  • RESTRICT(기본값): 자식 테이블에서 참조하는 행이 있으면 부모 테이블의 행을 삭제 및 수정할 수 없다.
  • CASCADE: 부모 테이블의 행이 삭제 및 수정되면 이를 참조하는 자식 테이블 행도 함께 자동으로 삭제 수정된다.
  • SET NULL: 부모 테이블의 행이 삭제 수정되면 자식 테이블 해당 외래 키 컬럼 값을 NULL로 변경한다.
    • 외래키 컬럼이 NULL 허용이어야 한다.
  • 대량의 데이터가 한번에 삭제될 수 있기 때문에 신중히 사용해야 한다.

# CHECK 제약 조건

  • 체크 제약조건은 데이터 내용 자체에 대한 규칙이다.
    • 특정 컬럼에 들어갈 수 없는 값의 범위나 조건을 직접 지정할 수 있다.
  • INSERT / UPDATE가 일어날때마다 지정된 조건식이 참인지를 검사한다.
CREATE TABLE products (
    product_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    category VARCHAR(100),
    price INT NOT NULL CHECK (price >= 0),
    stock_quantity INT NOT NULL CHECK (stock_quantity >= 0),
    discount_rate DECIMAL(5, 2) DEFAULT 0.00 CHECK (discount_rate BETWEEN 0.00 AND 100.00)
);
  • CONSTRAINT 제약조건_이름 CHECK (컬럼명 >= 10) 이와 같은 문법으로 작성하면 된다.

데이터 검증 레이어

  • 데이터 검증은 애플리케이션 레벨에서 하는 경우가 많다.
    • 에러 핸들링이 쉽고 훨씬 유연하기 때문이다.
  • DB 제약조건 위반으로 인한 에러처리가 복잡해진다.

# 트랜잭션

  • 데이터 일관성은 데이터의 상태뿐만 아니라 데이터 변경 행위로부터도 깨질 수 있다.
  • 하나의 예시를 확인해보자.
    1. orders 테이블에 주문 정보를 INSERT한다.
    2. products 테이블에서 주문된 상품 재고 값을 -1한다.
  • 위의 작업은 절대 쪼개질 수 없는 하나의 작업 단위이다.
  • 트랜잭션이란 논리적으로 절대 쪼개질 수 없는 하나 이상의 데이터베이스 작업 묶음이다.(Unit of Work)
    • 트랜잭션으로 묶인 작업은 모두 다 성공해야만 결과를 실제 데이터베이스에 반영한다.
    • 작업그룹 내에서 하나의 작업이라도 실패하면 모든 작업들을 없었던 일로 되돌려놓는다.
    • All or Nothing 원칙이라고 불린다.
  • 트랜잭션 핵심 명령어는 다음과 같다.
    1. START TRANSACTION: 지금부터 트랜잭션을 시작하라는 명령어
      • BEGIN이라고도 사용한다.
    2. COMMIT: 트랜잭션 내 모든 작업이 성공했으니 디스크에 영구적으로 저장하라는 의미이다.
      • COMMIT 이후에는 되돌릴 수 없다.
    3. ROLLBACK: 문제가 발생하여 트랜잭션 내에서 실행한 모든 변경 사항을 취소하고 트랜잭션 시작 전 상태로 되돌리라는 의미이다.
SELECT * FROM accounts;

START TRANSACTION;
UPDATE accounts SET balance = balance - 10000 WHERE id = 1;
UPDATE accounts SET balance = balance + 10000 WHERE id = 2;
COMMIT;

START TRANSACTION;
UPDATE accounts SET balance = balance - 10000 WHERE id = 1;
ROLLBACK;

MySQL `autocommit`

  • MySQL은 오토커밋 모드가 활성화 되어있다.
  • 모든 SQL문을 각각의 트랜잭션으로 간주하여 성공즉시 자동으로 커밋한다.
    • 여러 문장을 트랜잭션으로 묶고싶은 경우 반드시 START TRANSACTION으로 명시를 해야한다.
    • 한번 트랜잭션이 시작되면 커밋이나 롤백을 만나기까지 오토커밋이 비활성화 된다.

암시적 커밋

  • MySQL에서 모든 DDL은 암시적 커밋을 발생시킨다.
  • 메타 데이터 변경시 데이터 일관성을 해친다고 판단하여 그 동안의 트랜잭션 처리 명령어들을 커밋해버린다.
  • 또한, 트랜잭션 내에서 새로운 트랜잭션을 생성하는 경우에도 이전 작업들이 커밋된다.
  • DBMS마다 처리 정책이 다르니 확인은 해야한다.

# 트랜잭션 ACID 속성

  • ACID는 트랜잭션이 신뢰성을 갖추도록 반드시 지켜야할 4가지 속성을 하나로 묶은 개념이다.
    1. Atomic (원자성)
      • 트랜잭션은 하나의 원자처럼 더 이상 쪼갤수 없는 논리적 단위이며, 전부 성공하거나 전부 실패한다 (All or nothing)
      • 과정의 완결성
    2. Consistency (일관성)
      • 트랜잭션이 성공적으로 완료되면 데이터베이스는 항상 일관된 상태를 유지해야 한다.
      • 트랜잭션 실행 결과가 모든 제약조건 등을 위반하지 않았음을 보장
      • 결과의 유효성
    3. Isolation (격리성)
      • 하나의 트랜잭션이 실행중일 때 다른 트랜잭션의 중간 결과에 끼어들어 간섭할 수 없다.
      • 각각의 트랜잭션이 순서대로 하나씩 실행되는 것처럼 느끼게 해주는 특성
      • 쓰기 작업이 포함된 트랜잭션 1을 실행중에 트랜잭션 2가 끼어들어 오염된 값을 읽게 되는 경우를 Dirty Read라고 한다.
    4. Durability (지속성)
      • 성공적으로 커밋된 트랜잭션 결과는 시스템 장애가 발생하더라도 영구적으로 보존된다.
      • HDD / SSD 등의 트랜잭션 로그에 기록되어 어떤 장애에도 살아남는다.

# 트랜잭션 격리 수준

  • 트랜잭션 서로가 모두 완전히 격리되어 순서대로 작업들을 실행한다면 성능 저하가 발생할 것이다.
  • 데이터 정합성(Correctness) vs 동시성 / 성능(Concurrency/Performance) 둘은 트레이드오프로 중간 지점을 잘 찾아야 한다.
  • 격리수준이 낮을 때 발생할 수 있는 문제들이다.
    1. 더티 리드(Dirty Read)
      • 한 트랜잭션이 커밋하지 않은 수정중인 데이터를 다른 트랜잭션이 읽는 것
      • 트랜잭션 A에서 100원 조회 -> 120원으로 쓰기 (B 트랜잭션이 READ) -> 트랜잭션 A ROLLBACK
      • B 트랜잭션은 유효하지 않은 값 120원을 읽어들인 셈이다.
    2. 반복 불가능 읽기 (Non-Repeatable Read)
      • 한 트랜잭션 내에서 같은 SELECT 쿼리를 실행했는데, 그 사이에 트랜잭션이 커밋하여 쿼리 결과가 다르게 나오는 현상
    3. 유령 읽기 (Phantom Read)
      • 한 트랜잭션 내에서 특정 범위 데이터를 두번 읽는데, 첫번째에서는 없었던 새로운 행이 두번째 조회에서 나타나는 현상
      • 트랜잭션 실행 과정에서 외부 트랜잭션의 커밋이 이루어짐
격리 수준 Dirty Read Non-repeatable Read Phantom Read 성능 주요 사용 DB
READ UNCOMMITTED ❌ 발생 ❌ 발생 ❌ 발생 가장 빠름 거의 미사용
READ COMMITTED ✅ 방지 ❌ 발생 ❌ 발생 빠름 PostgreSQL, Oracle 기본값
REPEATABLE READ ✅ 방지 ✅ 방지 ❌ 발생 보통 MySQL 기본값
SERIALIZABLE ✅ 방지 ✅ 방지 ✅ 방지 가장 느림 금융 등 정합성 최우선
  • DBMS마다 격리수준의 구현은 달라질 수 있으므로 참고만 하면 된다.

# 프로시저, 함수, 트리거

  • 자주 사용하는 복잡한 작업 절차 자체를 데이터베이스 안에 하나의 프로그램처럼 저장하여 필요할때마다 불러와서 실행하고 싶다면?
    • 저장 프로시저(Stored Procedure)
    • 저장 함수(Stored Function)
    • 트리거 (Trigger)
    • 위 3가지를 사용하면 된다.
  • 저장 프로시저
    • 이름이 부여된 SQL 작업묶음
    • 파라미터를 받아 작업을 처리 / IF문, LOOP문 같은 제어문도 사용 가능
    • CALL 프로시저이름(파라미터1, 파라미터2);
  • 저장 함수
    • 특정 계산 수행 후 반드시 하나의 값을 반환
    • 값을 반환해야한다는 특징이 프로시저와 다름
    • SELECT name, 함수(컬럼) FROM 테이블;
  • 트리거
    • 특정 테이블 이벤트 발생 시 자동으로 실행되도록 약속된 프로그램
    • INSERT, UPDATE, DELETE와 같은 이벤트가 조건으로 만족 시 실행
  • 애플리케이션 서버 성능이 부족할때 데이터베이스 내에서 로직을 자체적으로 실행하는게 더 효율적이던 시절이 있었다.
  • 여러 애플리케이션이 같은 데이터베이스 로직을 수행해야 할때 코드 사용을 중앙화 할 수 있다.
  • 테이블 수정 권한대신, 프로시저 EXECUTE 권한을 부여할 수 있다.

← Database 설계 및 모델링 1 →