김영한 데이터베이스 기본편 강의 메모

인프런의 김영한 데이터베이스 기본편 강의를 들으며 메모한 내용이다.

db를 설계할 때는 데이터 일관성, 효율성과 중복 방지를 위해 정규화를 한다. 그런데 정보를 얻기 위해 쿼리를 짤 때면 그렇게 정규화로 분리된 테이블을 합쳐 의미있는 정보를 얻고 보여줘야 할 때가 있다. 이를 위해 쓰는 게 join

inner join
양쪽 테이블에 공통으로 존재하는 값만 데이터로 보여준다. 기준이 되는 컬럼의 값이 서로 일치하는 행만 짝지어줌.

두 테이블 모두에 존재하는 컬럼이며 같은 값을 가진 row끼리만 매칭되어 합쳐짐. 이런 이유로 어떤 순서로 join하든 inner join은 결과가 늘 같다.

SELECT 컬럼1, 컬럼2, ...
FROM 테이블A
INNER JOIN 테이블B
ON 테이블A.연결컬럼 = 테이블B.연결컬럼;

그냥 join만 쓰면 inner join으로 작성하므로 실제로는 생략도 많이 함

그런데 join만 쓰면 필드가 너무 많아지기 때문에 select로 필요한 컬럼만 가져올 때가 많다
각 테이블에 있는 컬럼명이 다를 수 있기 때문에 컬럼명을 꼭 적어준다. where로 조건절을 줄 때도 마찬가지
물론 select로 필드를 선택할 때 만약 둘 중 하나의 테이블에만 있는 필드일 경우 생략할 수 있기는 하다.(확실하게 한 군데만 있는 컬럼이기 때문) 하지만 가독성을 위해 테이블명을 적어 주도록 하자. 두곳 모두 존재하는 컬럼명일 경우 테이블명을 명시 안 하면 에러 발생.

join의 실행 순서(논리적인 순서)

  1. from/join으로 합친 테이블들을 합쳐 큰 가상 테이블을 만든다
  2. where 절 필터링
  3. select로 필터링된 결과 중 특정 컬럼만 추출

물론 db 내부에 쿼리 최적화기가 좀 더 효율적인 방식으로 실행한다. 예를 들어 where를 먼저 처리하고 join을 하면 더 빠르다든지.
이런 최적화 방식을 이해하면 같은 결과를 얻으면서도 빠른 실행이 가능하지만 일단 논리적인 순서가 위와 같다는 것

내부 join의 순서 #

inner join은 어떤 순서로 작성해도 쿼리 결과는 똑같다. 하지만 가독성을 생각하면 중심이 되는 데이터 -> 좀 덜 중심인 데이터 순서로 join을 작성하는 게 낫다. 그게 쿼리를 이해하기 더 쉬움

또 외부 join의 경우 순서가 영향이 크다.

tip #

테이블 별칭 : as로 지정
테이블의 별칭 as는 생략할 때가 많다. from orders o 같은 걸로 쓰고는 한다.

단 컬럼 별칭에서는 as를 사용한다. 여러 컬럼을 나열하거나 복잡한 함수를 사용할 때는 as가 없으면 코드 의도 전달이 명확해지지 않기 때문이다.

외부 join #

inner join을 이용하면 짝이 맞는 데이터(두 테이블에 특정 속성이 모두 있는 데이터)를 찾을 수 있다. 하지만 "고객별 주문 횟수" 를 구해야 한다면? 한번도 주문하지 않은 고객은 inner join으로 고객별 주문 횟수를 불러올 수 없다. 주문 테이블에 해당 고객 id가 없으니까.

그래서 outer join은 특정 테이블 데이터는 on 조건이 맞지 않아도 기본적으로 모두 결과에 포함한다.
left, right가 있고 full outer join도 있는데 잘 쓰진 않는다. mysql에선 지원 안 함

outer join의 결과물을 만들 때 데이터가 없는 컬럼은 null로 채워준다.

where 같은 걸로 null을 필터링하려면 null은 늘 is null로 검색. =로는 안된다.

left outer join의 경우 교집합뿐 아니라 left outer join 명령의 왼쪽에 있는 테이블(기준 테이블)에만 속한 요소들도 join 결과에 넣어준다. 비슷하게 right outer join은 명령어 오른쪽에 있는 녀석들 다 포함.

left outer join은 왼쪽에 있는 게 기준 테이블, right outer join은 join 오른쪽에 있는 게 기준 테이블.

테이블 순서만 바꾸면 left outer join이랑 right outer join을 바꿔 쓸 수 있다. 따라서 실제로는 left outer join을 이용해서 테이블을 왼쪽에서 오른쪽으로 붙여 나가는 식으로 생각하는 게 쉽기 때문이다.

from 테이블1 left outer join 테이블2 on 조건 left outer join 테이블3...

outer join의 결과가 더 늘어나는 경우 #

기준 테이블의 한 행이 다른 쪽의 여러 행과 연결될 수 있다면 행이 늘어난다.

서브쿼리 #

여러 단계의 쿼리를 하나의 쿼리 안에서 논리적으로 해결할 수 있게 해줌. 하나의 sql 쿼리 안에 포함된 또다른 select 쿼리. 괄호로 감싸짐.

db는 서브쿼리의 실행 결과를 바깥쪽의 메인쿼리에 전달하여 메인 쿼리가 최종 작업을 수행하도록 한다. 대략 이런 느낌.

select name, price from products where price > (select avg(price) from products);

where 외에 다양한 곳에서 쓰일 수 있다. 반환하는 행/컬럼 수에 따라 종류가 나뉘고 위치에 따라 역할도 결정됨. join이 테이블을 수평으로 붙여나가며 정보를 넓혀나가는 거라면 서브쿼리는 쿼리 내에 논리적인 단계를 만듬.

스칼라 서브쿼리 #

여러 쿼리를 하나로 합쳐서 코드가 간결해지고 요청도 한번에 처리되어서 실시간성도 좋아지고, 쿼리를 2번 이상 날릴 필요도 없어진다.

주의: 스칼라 서브쿼리를 사용 시, >같은 거랑 사용하게 되기에 서브쿼리 결과가 반드시 단 하나의 행만 반환해야 한다. 예를 들어 다음과 같은 걸 보자. 서브쿼리가 단일 주소를 반환하므로 잘 실행된다.

select name, address
from users
where address=(select u.address
from orders o
join users u on o.user_id=u.user_id
where o.order_id=1);

단 서브쿼리를 select * from orders o~로 바꾸면, 서브쿼리의 반환값이 1개 이상의 컬럼을 포함하므로 오류가 된다.

스칼라 서브쿼리는 반드시 단일 행, 단일 컬럼의 결과를 내야 한다

만약 여러 행을 리턴하는 서브쿼리가 필요하면 다중 행 서브쿼리(in 등과 함께 사용)를 써야 함

다중행 서브쿼리 #

=같은 단일 행 연산자가 아니라 in, any, all같은 목록을 다룰 수 있는 연산자를 써야 함. 다음은 전자기기 상품의 product_id들을 구하는 서브쿼리를 써서 그 서브쿼리 결과에 대한 주문을 구하는 쿼리다. 하나의 컬럼을 가진 여러 행을 쓰는 걸 볼 수 있다.

select * from orders
where product_id in (select product_id from products where category='전자기기')
order by order_id

in 연산자는 특정 컬럼의 값이 괄호 안에 있는 목록에 포함되는지를 확인하는 역할을 한다. any, all은 말 그대로다. any (서브쿼리)는 서브쿼리 결과 중 뭐 하나라도 만족하는 것. 예를 들어 > any(서브쿼리)라면 서브쿼리 결과 중 최소 1개 이상보다 큰 값을 뜻하는 것이다.

근데 실제로는 min(), max()같은 집계 함수를 쓰는 걸로 대체 가능하고 또 더 좋을 때가 많다. 예를 들어 > any()는 결국 해석해 보면 서브쿼리 중 최솟값보다 크면 되는 거라서, min()으로 대체 가능하다! 실제론 min, max를 더 많이 쓴다.

다중 컬럼 서브쿼리 #

하나의 행을 반환하지만 거기의 여러 컬럼(select하는 항목이 여러 개)이 포함되어 있는 서브쿼리를 다루려면 어떻게 할까?

일종의 튜플 비교처럼 다루면 된다.

select *
from orders
where (user_id, status) = (select user_id, status from orders where order_id=3);

=과 함께 다중 컬럼 서브쿼리를 사용할 때는 서브쿼리 결과가 반드시 하나의 행이어야 함! 일종의 튜플이니까.

다중 컬럼 서브쿼리 결과가 여러 행일 땐 in 등을 사용해야 한다. 다중 행 서브쿼리와 같음.

다중 컬럼 서브쿼리는 두 개 이상의 속성이 조합되어서 특정 의미를 가질 때, 그 조합 자체를 조건으로 사용하여 데이터를 조회하는 간결하고 강력한 방법을 제공한다. 사용법은 =나 in, any등을 쓰는 단일 행 서브쿼리와 같다.

상관 서브쿼리 #

원래 우리는 where 서브쿼리를 써서, 메인 쿼리와 독립적으로 실행된 결과를 필터 조건으로 사용했다. 즉 서브쿼리 결과 계산 -> 메인 쿼리에서 그걸 사용하는 식이었다.

근데 서브쿼리가 메인쿼리에서 현재 처리중인 행의 특정 값을 알아야 할 경우엔 어떻게 할까? 예를 들어 "자신이 속한 카테고리의 평균 가격 이상의 상품들을 찾아라" 그럼 처리중인 행의 카테고리를 알고 서브쿼리가 계속 달라져야 함. 어떡하지?

상관 서브쿼리로 해결 가능. 메인쿼리와 서브쿼리가 서로 연관관계를 맺고 동작한다!

실행 과정

-> 즉 서브쿼리가 메인 행 수만큼 실행될 수 있다.

이런 식으로 쓴다. 서브쿼리가 실행될 때 메인쿼리에서 현재 실행중인 행과의 연관된 조건을 넣는 것. join이랑 비슷하다.

select *
from products p1
where price >= (select avg(p2.price) from products p2 where p2.category=p1.category)

근데 문제는 메인쿼리의 행 수만큼 서브쿼리가 반복해서 실행된다는 것이다. 성능이 중요할 때는 좋지 않다.

앞서 in을 활용해서 행이 여러 개인 서브쿼리에 대한 조회를 할 수 있다는 걸 배웠다. 예를 들어 이런식으로, 서브쿼리 결과에 대해 in으로 조회할 수 있다.

SELECT product_id, name, price
FROM products
WHERE product_id IN (SELECT DISTINCT product_id FROM orders);

그런데 행이 엄청 많은 테이블의 경우 성능 문제를 일으킬 수 있다. 서브쿼리의 결과 목록 전체를 메모리에 저장한 후 메인쿼리 각 행과 비교해야 하기 때문이다. 이때 사용할 수 있는 게 exists

exists는 서브쿼리 결과에는 관심이 없고 서브쿼리 결과가 단 하나라도 있는지만 반환한다. 있으면 true, 없으면 false. 이러면 만약 하나라도 있으면 쿼리를 바로 종료할 수 있어서 효율적.

다음과 같이 연관 서브쿼리를 이용해서 in을 쓰는 쿼리문을 exists로 다시 짤 수 있다. exists는 결과 데이터가 뭐든지 전혀 신경쓰지 않고 행 존재만 보기 때문에 select 1처럼 상수를 사용해서 불필요한 데이터 조회를 피한다.

select product_id, name, price
from products p
where exists (select 1 from orders o where o.product_id=p.product_id)

상관 서브쿼리가 느리기는 하지만 exists는 shortcut이 가능하므로, 서브쿼리 결과가 되는 테이블이 클 경우 exists가 더 효율적일 수 있다. 반면 서브쿼리 결과가 작다면 in이 직관적이며 빠르기까지 할 수 있다.

상관 서브쿼리는 메인쿼리 행 수만큼 반복 실행될 수 있어서 메인쿼리가 다루는 데이터 양이 많아지면 쿼리 성능이 크게 하락 가능성.

많은 경우 상관 서브쿼리는 join, group by로 같은 결과를 내도록 재작성 가능할 때가 많다. 옵티마이저도 join을 더 효율적으로 처리할 경우가 많다(심지어 옵티마이저가 상관 서브쿼리를 알아서 join으로 풀기도 한다). 단 상관 서브쿼리를 써야 할 때는 exists 최적화를 한번 생각해보자.

select 서브쿼리 #

서브쿼리가 select 내에 있으면 그 자체로 하나의 컬럼처럼 동작함. 이 경우 일종의 값이 되는 것이므로 스칼라 서브쿼리를 사용해야 함. 예를 들어 각 상품 행에 평균 가격을 같이 보여주는 등의 비상관 서브쿼리를 사용하거나..그런데 select 서브쿼리의 진짜 힘은 상관 서브쿼리에서 나온다.

예를 들어 각 상품 별로 총 주문횟수를 함께 보여주는 등의 조작이 가능하다. count가 들어 있는 상관 서브쿼리를 넣어주는 것.

select
	product_id,
    name,
    price,
    (select count(*) from orders o where o.product_id=p.product_id) as order_count
from products p;

단 성능에 주의하라! 대부분은 이것도 join과 group by 등으로 해결할 수 있다. 예를 들어 위의 쿼리를 다시 쓰면 이렇다. join한 테이블들을 product_id, name, price로 group by 한 후 count하는 것.

select
	p.product_id,
    p.name,
    p.price,
    count(o.order_id) as order_count
from products p
left join orders o on p.product_id=o.product_id
group by p.product_id, p.name, p.price;

그럼에도 서브쿼리는 join이 너무 복잡하거나 다른 테이블에서 간단한 정보 하나만 조회해 올 경우, 정보가 적을 경우 등에 여전히 유용하다.

테이블 서브쿼리 #

from 절에서 나오는 서브쿼리. 실행 결과가 하나의 가상 테이블처럼 사용되어서 테이블 서브쿼리라고도 하고 인라인 뷰(즉석에서 정의되는 가상 테이블-뷰-와 같다는 의미)라고도 한다. 집계나 그룹핑된 결과를 다시 한번 조인하거나 필터링할 때 유용.

문제: 각 상품 카테고리별로, 가장 비싼 상품의 이름과 가격을 조회하기

group by로는 힘들다. 카테고리별로 가장 비싼 상품 가격까지는 가능한데 이름까지 조회하는 게 어렵다.

select category, max(price)
from products group by category;

group by에 name을 추가하면 또 name까지 해서 grouping되어서 문제다! 이를 from에 테이블 서브쿼리를 넣어 해결. 테이블 서브쿼리를 기존 product 테이블과 join해서 카테고리가 같고, max_price(mp)와 같은 가격을 갖는 걸 기준으로 join함. cmp(category max price라고 한다) 가상 테이블과 join한 걸 볼 수 있다.

select p.product_id, p.name, p.price, p.category
from products p
join (
	select category, max(price) as mp
    from products
    group by category) as cmp
on p.category=cmp.category and p.price=cmp.mp

이때 from 절의 서브쿼리는 테이블과 같은 것이므로 반드시 별칭을 가져야 한다.

from 절의 상관 서브쿼리는? lateral이라고 있기는 한데 너무 복잡하고 성능 때문에 잘 사용 안 한다. 꼭 써야 할 일이 있다면 lateral을 검색해보자.

join vs 서브쿼리 #

서브쿼리를 통해 특정 결과를 모두 찾고 in등으로 맞는 행을 찾는 방식, 그리고 join을 통해 필요한 테이블을 모두 연결한 후 원하는 조건을 필터링하는 방식 2가지로 모두 풀 수 있는 경우가 많다.

일반적으로는 join이 성능이 더 좋거나 최소한 동일하다. join은 쿼리 옵티마이저에게 더 많은 정보를, 따라서 더 많은 선택지를 제공하기 때문에 최적화하기 더 좋다. 반면 서브쿼리는 과거 DB에서는 단계적으로 실행되는 경우가 많았어서 서브쿼리 -> 메인쿼리 실행 순서로 이어져서 비효율을 만들 수 있었다.

다만 요즘 DB 옵티마이저는 똑똑해서, 간단한 IN 서브쿼리는 내부적으로 join으로 최적화하는 경우가 많다. 단 항상 가능한 건 아니라서 실행계획 확인하는 게 좋다. 쿼리 옵티마이저는 성능 최적화 강의에서 자세히 다룸.

join vs 서브쿼리의 가독성은 주관적인 영역이 있다. 서브쿼리는 논리적 단계를 명확히 구분하는 장점이 있고 join은 필요한 모든 데이터 소스를 한번에 보여줌. join이 여러 테이블 컬럼을 한번에 조회할 시 구조적으로 더 깔끔하기도 함.

성능이 의심되면 분석하고 측정하라!

union #

두 쿼리의 결과 집합을 합쳐주는(완전한 중복 행은 제거) 키워드. join이 테이블을 수평으로 붙인다면 이건 수직으로 늘리는 느낌

만약 중복 행을 허용해야 한다면 UNION ALL을 쓰면 된다. UNION ALL은 완전히 같은 행이 있더라도 제거하지 않는다. 그냥 두 결과를 이어붙이는 것뿐.

성능상 UNION ALL이 중복 제거가 없기 때문에 성능상 훨씬 빠르다. 따라서 성능을 생각해서 중복 제거에 대한 명확한 요구사항이 있을 때만 UNION ALL 대신 UNION을 사용.

UNION을 정렬하려면?

이름이 다른 컬럼의 경우 컬럼에 별칭 달아서 통합하는 게 좋다.

case문 #

데이터 자체를 동적으로 가공하고 새로운 의미를 부여한다. 특정 조건에 따라 다른 값을 출력하게 만드는 등과 같은 것. 예를 들어 10만원 이상은 고가, 미만은 저가라고 표시한다든지.

case ~ when~ else~ end

WHEN 값1 then 결과1
처럼 단순히 값과 비교한다

when 조건1 then 결과1 처럼 조건을 비교한다
이때 위에서부터 비교하기 때문에 위에서 조건에 걸리면 아래는 검색 안 한다는 것에 주의하자. if, else if랑 같음. 따라서 조건 배치 순서를 주의하여, if/else if를 쓰는 것과 똑같이 생각해서 쓰자.

다음과 같이 group by에 case문을 쓰면 case문을 이용해 새로운 분류 조건을 만들 수 있다. 새로운 기준으로 데이터를 분류하고 count같은 집계함수를 통해 그룹별 통계를 낼 수 있는 것.

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
CASE
WHEN YEAR(birth_date) >= 1990 THEN '1990년대생'
WHEN YEAR(birth_date) >= 1980 THEN '1980년대생'
ELSE '그 이전 출생'
END;

group by를 먼저 하고 select를 먼저 한다. 근데 요즘 db들은 좋아져서 select에서 쓴 별칭을 group by에서도 쓸 수 있을 때가 많음

조건부 집계 #

case문이 sum, count 등에 들어가서 더 강력한 집계를 할 수 있게 해준다. count는 NULL이 아닌 모든 값을 세는 특징을 이용하든가 아니면 sum에서 then~else를 쓴다. 서브쿼리는 메인쿼리 매 행마다 실행되기 때문에 이게 더 효율적일 때가 많음. 이건 테이블을 1번만 읽어서 효율적으로 처리하므로

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랑 함께 쓰면 더 좋다. group by를 한 다음 select를 써서 예를 들면, 카테고리별로 뭔가 주문 현황과 분류를 본다든지

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 o.product_id=p.product_id
group by p.category

join이랑 같이 써서, 조인하고 특정 조건으로 묶은 테이블에서 특정 집계를 하는 데 쓸 수도 있다.

select
	u.name as user_name,
    count(o.order_id) as total_orders,
    sum(case when p.category='전자기기' then 1 else 0 end) as electronics_orders,
    sum(case when p.category='도서' then 1 else 0 end) as book_orders,
    sum(case when p.category='패션' then 1 else 0 end) as fashion_orders
from users u
left join orders o on u.user_id=o.user_id
left join products p on o.product_id=p.product_id
group by u.name;

count(case ...), sum(case ...)과 같은 패턴을 주로 사용한다.

근데 이렇게 데이터를 뽑는 쿼리들은 많은데 재무팀이나 마케팅팀에서 매일 필요로 할 수 있다. 또 어떤 팀은 어떤 테이블에 접근할 수 있는 권한이 없을 수도 있다..근데 이런 정보를 보여주고 유용한 쿼리들을 평소에 갖다쓰게 어디 저장해 놓고 쓰든가 할 순 없을까? -> view

뷰 #

뷰는 실제 데이터를 가지고 있지 않은 가상의 테이블. 실제로는 바탕화면의 바로가기처럼, DB에 이름과 함께 저장된 select 쿼리문 같은 것.

일종의 쿼리문 shortcut이므로 뷰를 실행하면 언제나 테이블의 최신 상태를 조회한다. 따라서 최신성 보장.

편리하고, 원본 테이블 접근 권한도 숨겨서 보안성도 확보 가능하고 일종의 추상화 계층 역할도 한다.

create view 뷰이름 as (select 쿼리문);
-- 괄호는 생략도 가능

보통 v_ prefix로 뷰임을 나타낸다.

이렇게 view를 만든 다음 select * from 뷰이름 처림 실제 테이블처럼 쓸 수도 있다. where, order by 같은 거 전부 가능하다. 애초에 서브쿼리 alias 같은 것이기 때문.

뷰 수정 같은 경우 형식은 비슷한데 create 대신 alter 로 비꾸고 새로운 뷰로 만들 select 쿼리문을 뒤에 붙여주면 된다.

alter view 뷰이름 as (select 쿼리문);

삭제는 당연히 drop view 뷰이름

물론 뷰는 쿼리 내부 로직을 몰라도 바로 사용 가능하니 편리하고, 재사용도 쉽고, 보안성도 좋다. 권한 관리상 민감한 정보만 빼고 볼 수 있는 권한만 줘서 특정 직원들에게는 적당한 정보만 줄 수 있다든지. 또 물리적인 테이블 변화를 숨겨주는 추상화 계층 역할도 함

단점은, 내부 로직을 모르고도 쓸 수 있다는 장점이 단점으로도 작용하는데 성능 부하를 숨기는 역할을 해버릴 수도 있다. 사용자는 한줄쿼리라고 생각하지만 실제론 엄청 부하를 갖는 쿼리일 수도.

뷰는 대부분 조회용이라 원본 테이블 편집은 힘들다. 아주 단순한 뷰의 경우 원본 테이블 수정에도 수정을 가할 수 있지만 어려우므로, 기본적으로 조회에만 쓰는 문법이라고 생각하자.

인덱스 #

데이터가 많아지면 아주 단순한 조회조차도 오래 걸린다. 그래서 인덱스라는 걸 건다. 인덱스를 걸면 데이터가 인덱스를 기준으로 정렬되어 있음 -> full table scan을 하는 것에 비해 원하는 데이터를 아주 빠르게 찾을 수 있다. 인덱스는 데이터를 늘 정렬 상태로 보관.

테이블 전체를 스캔하는 대신 먼저 인덱스를 기반으로 찾아간다. 찾아낸 인덱스 항목에서 실제 데이터 항 위치 확인. 해당 위치를 이용해서 바로 원하는 데이터를 가져옴

mysql의 경우 클러스터 인덱스와 보조 인덱스가 있음. 클러스터 인덱스는 원본 데이터 자체를 인덱스에 같이 보관. 보조 인덱스는 원본 데이터의 PK를 같이 보관.

create index 인덱스명 on 테이블명(컬럼명);
-- 예시: create index idx_items_item_name on items(item_name)
show index from 테이블명; -- 해당 테이블에 있는 인덱스 보여주기

show index를 하면 내가 인덱스를 걸지 않은 컬럼들도 나오기도 한다. fk_ 로 시작하는 인덱스 등이 있다.

mysql의 경우 pk, fk, unique인 컬럼에 대해 자동으로 인덱스를 건다. pk, fk는 애초에 자주 탐색이 되기 때문에 만들어 준다. 그럼 unique는?

unique에 인덱스 거는 이유는 효율성 때문이다. unique가 걸린 컬럼의 경우 데이터 삽입 시 유일성 확인을 해야 하는데 만약 index가 없으면 그때마다 유일성 확인을 위해 풀 테이블 스캔을 해야 하기 때문이다.

쿼리 옵티마이저가 인덱스를 쓰는지 알고 싶을 때 explain을 쿼리 앞에 붙이면 됨

보통 idx_ prefix를 붙임

카디널리티: 인덱스에 저장된 유니크한 값 개수에 대한 추정치. cardinality가 높으면 중복도가 낮다는 의미. cardinality가 높아야 고유한 값들이 대부분이기 때문에 인덱스 성능이 좋게 된다. ex: 남자/여자만 구분하는 컬럼이라면 당연히 유니크한 값 개수가 적다. 이럴 경우 카디널리티가 매우 낮기에 '성별' 컬럼에 인덱스를 걸어도 쿼리 성능에 별 도움이 안 된다.

삭제는 당연히 drop index 인덱스명 on 테이블명

인덱스 구현은 주로 트리 자료구조(B트리) 사용. O(logN)으로 탐색

인덱스 사용을 확인하기 #

DB는 쿼리 옵티마이저를 이용해서 쿼리 실행에 대한 계획을 세운다. explain을 이용해서 쿼리의 실행 계획을 볼 수 있고, 인덱스가 실제로 쿼리에 사용되는지도 확인할 수 있다.

explain 쿼리 -- 쿼리 실행 계획을 알려줌

explain의 결과로는 다음과 같은 값들이 나온다.
type: DB가 테이블에 접근하는 방식. ALL이면 풀 테이블 스캔
key: 쿼리를 실행할 때 사용한 인덱스. NULL이면 사용한 인덱스가 없다는 거
rows: 옵티마이저가 쿼리 처리를 위해 탐색할 걸로 예측하는 행의 수. 예측 정보라 완전 정확하진 않을 수 있다.
filtered: where 조건으로 필터링 후 남을 것으로 예측하는 행의 수(물론 예측이므로 틀릴 수 있다)
extra: 기타 정보. where를 통해 필터링을 수행했다든가 하는, 쿼리 실행의 '비고' 같은 느낌

인덱스와 동등 비교 #

인덱스는 언제 쓰이는가?

동등 비교 #

먼저 동등비교부터 한다. 동등비교에 인덱스를 사용할 경우 explain의 type에서 ref가 나옴

create index 를 통해 인덱스를 만들고 해당 인덱스를 사용하는 쿼리를 explain으로 보면 type이 ref인 걸 볼 수 있다.

type이 ref: 인덱스를 사용해 동등 비교(=)로 데이터를 찾았다.
possible_keys: 현재 쿼리에서 사용가능한 키 후보. key는 실제로 쿼리 옵티마이저가 해당 쿼리에 사용한 키.
rows는 1, filtered는 100: 단 1개의 행만 읽으면 된다는 예측, 인덱스를 통해 찾은 행을 100% 선택한다는 뜻. 효율이 엄청 올라갔다.(물론 이건 예측값이므로 환경 따라 다를 수 있음)
extra: null로 바뀌었음. 인덱스 단계에서 모든 검색 조건이 충족되어 추가 작업이 필요없었다는 뜻

범위 검색 #

between등의 범위 검색의 경우에도 인덱스가 없을 때 explain으로 쿼리 실행 계획을 보면 비슷하게 풀테이블스캔을 하는 걸 볼 수 있다.

이제 범위검색에 쓸 컬럼을 index 지정하고 explain으로 확인해 보면 type이 range로 바뀌고 다른 부분도 매우 효율적으로 바뀐 걸 볼 수 있다. rows(쿼리가 스캔할 걸로 예상하는 행의 수)도 25 -> 5로 줄었다.

인덱스 범위 검색은 범위 시작에 맞는 1개를 찾은 후(이진 탐색 원리로 굉장히 빠름) 범위가 끝나는(값이 조건을 벗어나는) 곳까지 해서 결과를 만든다. 인덱스에 따라 행들이 정렬되어 있기 때문에 이렇게 할 수 있고, 훨씬 효율적이다.

단 이런 인덱스 기준으로 정렬 순서가 맞춰 나오는 걸 DB에서 보장하는 건 아니므로 순서 보장이 필요할 시 / 혹은 정렬이 중요할 시 order by를 꼭 넣어야 한다.

인덱스와 LIKE 범위 검색 #

LIKE도 범위 검색에 인덱스를 활용할 수 있다.

LIKE 절에서 인덱스 사용시 검색어%처럼 %가 검색어 뒤에 와야 한다. 인덱스는 새로운 정렬 순서를 만드는 것이므로 당연하다. %검색어 로 검색시 문자열 정렬 여부는 검색 효율에 도움이 안되니까. 가나다순으로 정렬된 국어사전에서 단어를 찾는 걸 생각해 보면 납득 가능하다.

검색어%로 검색하는 쿼리를 explain해보면 type이 range인 걸 볼 수 있다. %검색어% 같은 걸 explain 해보면 모든 걸 스캔해봐야 해서 type이 ALL(즉 풀 테이블 스캔)이 나오는 걸 확인 가능.

따라서 LIKE %검색어% 같은 건 데이터가 많아지면 성능이 엄청 떨어짐. 이런 건 텍스트를 토큰으로 쪼개서 인덱싱하는 full text search(전문 검색)라는 걸 DB에서 지원한다. MATCH ... AGAINST를 쓴다. 필요하면 full text search 알아보기

인덱스와 정렬 #

DB에서 정렬(ORDER BY)는 꽤 무거운 작업 중 하나. 근데 인덱스는 이미 데이터를 정렬해 놓는다. 그럼 인덱스를 활용해 정렬을 최적화할 순 없을까? -> 인덱스를 잘 쓰면 filesort라는, 정렬 작업 중 일부를 피하기 가능. 별도의 정렬 과정 없이 이미 정렬된 걸 순서대로 읽기만 하면 되기 때문

먼저 인덱스가 없을 때 explain select * from items order by stock_quantity 같이 sort의 계획을 확인해보면 extra에 using filesort가 있는 걸 볼 수 있다.

price에 인덱스가 있어서 이미 price 순으로 정렬되어 있고 따라서 where에 맞는 데이터를 그대로 스캔하면 이미 정렬되어 있다. -> 인덱스를 활용한 order by 최적화!

select * from items where price between 50000 and 100000
order by price;

위 쿼리를 explain 해보면 "using index condition"이 뜨는 걸 볼 수 있다.

그런데 이렇게 인덱스 컬럼 기준으로 order by를 하는데, 인덱스를 역방향으로 조회하는 경우에는?(DESC 정렬)

-> DB 옵티마이저는 역방향 인덱스 스캔(Backward index scan)이 가능하기 때문에 이 경우에도 filesort는 일어나지 않고 빠르게 정렬 결과 만들기 가능.

이 쿼리를 explain하면?

explain select * from items where price between 50000 and 100000
order by price desc;

extra에 'Using index condition; Backward index scan'이 나온다.

다만 역방향보다는 정방향 스캔이 미세하게 조금 더 빠르다. 하드웨어의 prefetch 때문. 다만 이 성능 차이는 미미하기 때문에 order by에서 filesort를 피하는 게 정방향인지 역방향인지보다 수백 배 이상 더 중요하다.

만약 역방향 정렬이 많이 일어나서 정렬 방향과 일치하는 인덱스를 더 만들 수도 있다. (내림차순 인덱스) 다음과 같이 한다.

create index idx_items_price on items(price desc);

인덱스는 asc, desc 어느 방향으로든 만들기 가능. 그리고 인덱스 방향대로 읽어내려가는 게 조금은 더 빠르다.

이 내림차순 인덱스의 진가는 여러 컬럼의 다른 정렬 순서들을 쓰는 복잡한 쿼리가 자주 쓰일 때 발휘된다. 정렬 순서에 맞춰 잘 생성된 복합 인덱스는 쿼리 성능을 극적으로 향상시키기도 한다. 복합 인덱스를 만들면 인덱스 순서가 중요해짐.

인덱스 2 #

쿼리 옵티마이저 #

옵티마이저가 인덱스 사용이 오히려 비효율이라고 판단하면 인덱스를 포기하고 풀 테이블 스캔을 할 수도 있다. 이런 상황이 왜 발생하는가? -> 인덱스를 사용할 시 인덱스를 이용해서 실제 테이블의 흩어진 데이터에 랜덤 액세스를 해야 하기 때문이다. 따라서 db 옵티마이저는 인덱스로 데이터 읽기 vs 풀 테이블 스캔을 비교해서 손익분기점을 계산한다.

일반적으로 손익분기점은 전체 데이터 스캔의 20%~25% 이상을 조회해야 하는지에서 갈린다. 전체 데이터의 약 25% 이상을 조회해야 하는 쿼리의 경우 일반적으로 full table scan이 더 효율적이라고 알려져 있다.

순차 I/O의 경우 1페이지부터 순서대로 읽을 수 있는데 랜덤 I/O는 상대적으로 느리다. SSD의 경우 '여기서부터 N개 읽기' 같은 명령으로 순차 IO를 한번에 처리할 수 있고 HDD의 경우 데이터를 읽을 때마다 디스크 헤드가 물리적으로 이동해야 한다. 또한 캐시 지역성을 고려하면 디스크를 읽을 때 여러 개 한번에 읽어오니까 더욱더 그렇다.

explain select * from items where price between x and y

이런 쿼리 실행 계획에서 x,y 범위를 바꿔가면서 전체 데이터의 특정 비율 이상을 스캔하게 되면 key도 NULL이 되어 안쓰고, type도 ALL이 되고, filtered(예측되는 결과의 row 수)도 꽤 높아진 걸 볼 수 있다.

인덱스는 만능이 아니므로 쿼리 튜닝 시 explain으로 옵티마이저가 실제로 인덱스를 사용하는지 확인하자. 다만 데이터가 매우 적을 시 대부분 경우에 풀 테이블 스캔을 사용할 수 있기 때문에(데이터가 적으면 거의 늘 풀테이블 스캔이 빨라서) 테스트 데이터 갯수에 대해서도 생각하자.

FORCE INDEX 인덱스명을 사용하면 쿼리에서 인덱스를 강제로 적용하도록 할 수 있다. 물론 실무에서는 권장 안함. 옵티마이저가 최적의 인덱스를 선택할 수 없어지니까.

커버링 인덱스 #

인덱스를 썼는데도 랜덤 IO를 발생시키지 않을 수 있는 방법! 쿼리에 필요한 모든 컬럼이 인덱스에 포함되어 있으면 된다. "커버링 인덱스"

만약 select 쿼리에서 조회하는 모든 컬럼이 인덱스에 들어가면 커버링 인덱스가 되어 성능이 좋다. 이를 커버링 인덱스라 한다.

커버링 인덱스의 경우 쿼리는 인덱스에서 끝나 버리고 원본 테이블에 접근도 안 한다. explain의 결과로는 Extra: Using index가 나온다. 쿼리에 필요한 모든 데이터를 인덱스에서만 읽어서 처리했다는 뜻. using where는 인덱스 내에서 where을 써서 필터링했다는 뜻.

근데 인덱스에 포함 안된 컬럼까지 조회하려면? 단순하다. 쿼리에 필요한 컬럼을 모두 포함하도록 인덱스를 만들면 됨. 이러면 item_name까지 select하는 쿼리도 커버링 인덱스로 동작함

create index idx_items_price_name on items(price, item_name);

커버링 인덱스 사용시 select 성능이 엄청 향상되고(랜덤IO 없어짐) count 쿼리도 최적화된다. 그러나 저장공간이 증가되고 쓰기 성능 저하. 특히 인덱스가 많고 복잡할수록 쓰기 작업 부하도 커짐

-> 조회가 쓰기보다 훨씬 많고, select에서 조회하는 컬럼 갯수가 적을 때 사용이 좋다.

복합 인덱스 #

여러 개 컬럼을 묶어서 하나의 인덱스로 만들기. 여러 조건을 조합해 검색하는 이런 쿼리의 성능을 올린다.

select * from items
where category='전자기기' and price >= 100000;

복합 인덱스는 동작 원리를 보면 1번째 컬럼으로 먼저 정렬하고 1번째 컬럼 값이 같을 경우 2번째 인덱스 컬럼 값으로 정렬하고..이런 식으로 순차적으로 적용한다. 따라서 복합 인덱스가 제 역할을 하려면 어떤 쿼리의 성능을 최적화할지에 따라 인덱스 컬럼 순서가 매우 중요하다. 이 순서나 원리를 제대로 적용하지 않아서 복합 인덱스는 많은 에러나 장애의 원인이 된다.

즉 원리를 생각하면 2번째 인덱스 컬럼부터는 제대로 활용하지 못하는 문제가 생긴다. 예를 들어 복합 인덱스가 (A, B)로 걸려 있는데 B로 검색할 경우 인덱스가 아무런 도움이 안됨. 기본적으로 A 컬럼으로 정렬되어 있기 때문. 이경우 옵티마이저가 풀 테이블 스캔 할 수도.

복합 인덱스 사용 대원칙. 쿼리가 어떻게 데이터를 가려내는지 생각하면 당연하다.

즉 order by 사용할 때 최적화를 위해서는 인덱스를 이용할 수 있는지 알아볼 수 있다.

복합 인덱스 사용 예시 #

items테이블에 카테고리, 가격으로 복합 인덱스 만들기

create index idx_items_category_price on items(category, price);

복합 인덱스 실패사례 #

앞서 이야기한 복합 인덱스 대원칙을 어겼을 때 발생함.

첫번째. 인덱스 순서 위반. 예를 들어 복합 인덱스에서 1번째 인덱스 컬럼 건너뛰고 2번째 인덱스 컬럼으로 검색할 경우. 인덱스가 (A, B)로 걸려 있는데 B로 검색하는 거. 인덱스는 기본적으로 A로, 그 다음에 B로 정렬되어 있기 때문에 B로만 탐색하면 인덱스가 도움이 안됨. 사전에서 2번째 글자를 기준으로 검색하는 거랑 같다고 생각하면 됨.

두번째. where의 등호 조건 컬럼은 복합 인덱스의 앞으로, 범위 조건을 쓸 컬럼은 뒤로. 만약 범위 조건을 먼저 쓰게 되면 범위를 기반으로 먼저 데이터를 스캔 후 스캔하는 레코드마다 등호 조건을 만족하는지 하나하나 검사해야 한다.

앞선 where 조건에 범위 조건을 거는 순간, 그 뒤 컬럼들은 인덱스의 정렬 조건을 제대로 누릴 수 없다. -> 인덱스 설계시 = 조건으로 사용할 컬럼을 range 조건으로 사용할 컬럼보다 복합 인덱스의 앞쪽에 배치하는 게 좋다.

예를 들어 다음과 같은 쿼리가 자주 쓰인다고 하자. select * from items where category>='패션' and price=20000

그럼 만약 등호 조건을 먼저 사용할 수 있다면 굉장히 인덱스 성능을 향상시킬 수 있다. 인덱스를 (price, category)로 걸어야 price의 등호 조건을 사용해서 성능 향상 가능.

create index idx_items_price_category on items(price, category);

이렇게 하고 나서 explain을 해보면 filtered가 100%(물론 예측값이지만)인 걸 볼 수 있다. 인덱스만 써서 다 필터링 완료한 것.

세번째. 범위 검색은 마지막에 한 번만 사용한다.

=를 쓸 인덱스를 앞에, 범위 검색을 쓸 인덱스를 복합 인덱스의 뒤쪽에! 그래야 먼저 데이터셋을 최대한 줄인 후 범위 검색을 한다. 즉 복합 인덱스를 설계 시 자주 사용될 쿼리를 예측하고 해당 쿼리 where 절에 맞게 등호 조건->범위 조건 순서로 구성해야 함.

그리고 인덱스를 계속 추가시 관리 비용이 들어가므로 기존 인덱스를 최대한 잘 활용하도록 해보자.

범위 조건 때문에 2번째 인덱스 컬럼을 활용하지 못하는 문제의 경우 IN으로 어느 정도 해결할 수 있음. mysql 옵티마이저는 IN을 하나의 큰 범위가 아니라 여러개의 동등비교 조건 묶음으로 취급하기 때문.

따라서 예를 들어 범위검색을 하는 값이 작다고 해보자. 카테고리 같은 경우 많지 않을 것이다. 예를 들어 where category >= '패션'같은 거. 그걸 where category in ('패션', '헬스') 처럼 바꿀 경우(물론 2개가 논리적으로 같다고 가정) in은 마치 동등 조건 여러개의 or처럼 동작한다.

즉 >같은 범위 검색은 연속 범위로 처리 -> 복합 인덱스 활용 불가. in은 여러 개별 지점에 대한 =의 묶음으로 처리됨. 따라서 범위 검색을 몇 개의 튜플에 대한 in으로 바꿀 수 있다면 인덱스를 활용할 수 있어서 아주 빨라짐.

따라서 범위가 한정적인 컬럼에는 이 in 트릭을 자주 사용한다.

복합 인덱스 정리 #

복합 인덱스 대원칙 재정리. 복합 인덱스는 전략적으로 설계해야 한다!

인덱스 설계의 기본 가이드라인 #

어디에 인덱스를 만들어야 하는가? 인덱스를 만드는 건 검색을 빠르게 하지만 쓰기 성능 저하, 메모리를 차지하는 등의 대가가 있으므로 적절한 곳에 만들어야 좋은 DB 설계다.

값들의 고유성(카디널리티)이 높아야 인덱스를 걸 때 좋다. 그래야 인덱스를 통해서 탐색 범위를 확 줄일 수 있기 때문이다. 예를 들어 is_active같은 건 true, false뿐이니까 카디널리티가 낮다. 카디널리티가 높아야 인덱스를 통한 데이터 식별력이 높아진다.

인덱스를 만들 만한 후보

인덱스 단점, 주의사항 #

인덱스의 트레이드오프

따라서 읽기 중심의 서비스에서 자주 읽히는 컬럼에 인덱스를 추가하는 게 좋고, 필요할 때만(설계시 읽기 쿼리 많을 걸로 예상, 혹은 슬로우 쿼리 발생 등) 인덱스를 만들고, 사용되지 않는 인덱스틑 삭제한다.
"사용되지 않는 데이터나 인덱스를 남겨두지 말자!"

인덱스를 만들 땐 정렬 쿼리를 고려해서, 발생하는 슬로우 쿼리들에서 해당 인덱스를 만들었을 때 정렬 결과를 그대로 쓸 수 있을지 고민해야 한다. 단순히 카디널리티만 고려하지 말고. 가령 is_active같은 컬럼은 0과 1밖에 없으니 카디널리티가 적다. 그러나 해당 컬럼이 인덱스로 들어가야 정렬을 쓸 수 있다면 is_active도 넣어줘야 인덱스가 제대로 쓰인다.

select * from items where category='전자기기' and is_active=true order by stock_quantity;

예를 들어 위의 경우 category, is_active, stock_quantity 로 인덱스를 걸면 되는데, 중간에 is_active를 인덱스에 안 넣으면 제대로 최적화가 안 될 것이다. b tree 내부 정렬을 생각해 보면 그럼.

데이터 무결성 #

DB가 데이터의 정확성과 일관성(가격이 음수가 되면 안된다든지)을 지키고 현실에 결코 존재할 수 없는 데이터가 들어가는 걸 막기 위해 데이터 무결성을 지켜야 한다. DB는 데이터를 안전하게 지켜야 한다!

이런 쓰레기 데이터가 DB에 저장되는 걸 막을 책임은 물론 1차적으로 애플리케이션에 있다. 하지만 애플리케이션에 버그가 있을 수도 있고, DB 직접 수정이 있을 수도 있다. -> DB에서도 데이터를 지키는 방법을 만들어야 한다!

이 데이터 무결성을 위해 제약 조건(constraint)을 건다.
constraint: insert, update, delete할 때 대상이 되는 데이터가 특정 규칙을 지키도록 하는 제한. primary key 같은 것도 제약 조건에 해당

DB 버그는 만약 생겼을 때 고치기가 훨씬 어렵기 때문에 제약 조건을 잘 설계하는 게 중요하다.

제약 조건들(단일 테이블 내의 데이터를 관리하는 constraint)

check은 insert, update할 때마다 지정 조건이 참인지 먼저 검사한다. 조건식이 참일 경우에만 실행하고 아닐 경우 에러. 예를 들어 이런 식

create table products {
    -- ...
    price int not null check(price>=0),
    stock_quantity int not null check(stock_quantity>=0)

    -- 물론 여기 constraint 키워드를 이용해 제한 조건 이름을 붙이고 check 조건을 줄 수도 있다
    constraint price_positive check(price>=0) -- 이런 식으로.
}

사실 이런 check이 필요한 비즈니스 데이터 로직같은 경우 보통 애플리케이션 코드에서 직접 처리한다. DB check 같은 경우 정말 핵심 데이터이고 간단한 로직에만 데이터 무결성에 대한 최후의 방어선으로 쓰는 게 좋다. 왜냐? 많은 데이터 제약이 사실 비즈니스 로직일 때가 많기 때문.

여러 테이블에 걸쳐서 있는 제약 조건도 있다.

참조 무결성: 두 테이블의 관계가 항상 유효하고 일관된 상태를 유지해야 한다. 이런 관계 무결성을 강제하는 게 바로 외래키(fk)

자식 테이블에 삽입/수정할 때 부모에 존재 안 하는 값을 쓰려는 시도, 자식테이블에서 fk로 참조하고 있는 값을 막 삭제할 수 없도록 하는 시도를 fk constraint가 차단한다.

삭제를 위해선 해당 부모 테이블 데이터를 참조하는 컬럼 전부 삭제 -> 부모 테이블 데이터 삭제.

이렇게 부모데이터의 수정을 무조건 막는 게 기본값 restrict. 하지만 비즈니스 규칙에 따라 다른 정책이 필요할 수 있다. 회원 탈퇴시 모든 주문 기록 삭제 등등. 따라서 restrict 외의 옵션도 있다.

constraint 외래키명 foreign key (컬럼명) references 부모테이블명(부모테이블컬럼) on delete(혹은 update 등등) cascade

cascade 로직 편하다. 그러나 의도치 않은 여러 데이터 삭제 유발 가능. 특히 복잡한 관계 테이블이 있을 경우 파급 효과 예측이 어렵다. 그래서 보통 실제로는 cascade 잘 안쓰고 애플리케이션에서 명시적으로 관련 데이터를 처리할 때가 더 많다. fk 쓸거면 db constraint로는 가급적 기본인 restrict를 쓰자고 한다.

트랜잭션 #

주문 정보 기록 + 재고 줄이기 등 절대 쪼개지면 안 되고 꼭 함께 수행되거나 함께 실패해야 하는 행위에 대한 규칙을 책임진다. all or nothing으로 처리되어야 하는 행위에 쓴다. 만약 주문 정보 기록이랑 재고 줄이기가 함께 처리되지 않을 경우, 주문은 됐는데 재고는 안 줄어드는 그런 "일관성 깨진(inconsistent)" 상태가 될 큰 위험이 있다.

위와 같이 함께 실행될 쿼리들을 트랜잭션으로 묶는다. 절대 쪼개지지 않고 모두 성공하거나 모두 실패하도록 하는 것.(atomic) 다 성공해야만 실제 DB에 영구적으로 반영. 하나라도 실패 시 트랜잭션 실패로 간주 후 원상 복구

트랜잭션 문법 #

DB에 어떻게 "여기부터 여기까지 하나의 트랜잭션"임을 알려줄까?

start transaction;

-- 업데이트 쿼리

commit;

start transaction을 하고 트랜잭션 쓰다가 rollback 하면 롤백된다.

mysql은 오토커밋이 활성회되어 있어서 각각의 sql문 하나하나를 각각의 트랜잭션으로 간주해 자동 commit. start transaction 하면 그 세션에서는 커밋/롤백까지 autocommit 잠시 비활성화.

ACID #

트랜잭션이 신뢰성을 갖추기 위해 지켜야 할 4가지

격리성 예시 #

만약에 격리성이 없는 상태에서 이걸 생각해보자. A가 상품 주문 + 재고 줄이기 트랜잭션을 실행 중이다. A가 커밋을 아직 하지 않은 상태에서 B가 상품의 재고를 조회한다면? 원래는 A가 재고를 하나 줄였으므로 재고가 1개가 되어야 하는데 B가 조회할 때 2개로 읽게 된다. 아직 A가 커밋을 안했으니까.

가장 간단한 방법은 A가 재고 줄이기까지 완료한 후 B가 읽을 수 있도록 하는 것. 그러나 B가 읽을 때 트랜잭션 실행 전, 원본 값을 읽도록 하면 된다. 즉 A가 트랜잭션을 실행하고 있을 때 그 중간 결과에 B가 간섭할 수 없도록 하는 것.

격리성 #

트랜잭션을 완전히 격리시키면 안전해지지만 성능이 낮아지는 트레이드오프가 있다. 한 줄로 줄을 서서 순서대로 실행되는 식이므로. 만약 수천 수만명이 동시에 접속하여 쿼리를 날리게 되는 쇼핑몰 같은 서비스라면 그렇게 할 수 없다. 모든 쿼리를 한 줄로 세워 실행하면 너무 느릴 테니까.

따라서 격리성 수준들이 있음. 격리 수준 높이면 정합성 오르지만 동시성 낮아짐. 격리 수준 낮아지면 성능 좋아지지만 데이터 정합성 내려감.

자세히는 DB 성능 최적화 강의 등에서 다룬다

격리 수준이 낮을 때 생기는 대표적인 동시성 문제들

dirty read: A 트랜잭션이 아직 커밋하지 않고 수정중인 데이터를 B 트랜잭션이 읽는 것
반복 불가능 읽기: 한 트랜잭션 내에서 같은 select를 2번 실행했는데 그 사이에 다른 트랜잭션이 커밋을 해서 2번의 읽기 값이 다름
유령 읽기: 한 트랜잭션 내에서 특정범위 데이터 2번 읽었을 때, 그사이에 다른 트랜잭션 커밋으로 인한 새로운 행이 생김.

해결을 위한 격리 수준들 #

mysql의 repeatable read에서는 팬텀 리드가 일부 발생할 수 있다고 히지만 innodb 엔진은 mvcc, 갭 락 기술을 통해 해당 수준에서도 팬텀 리드를 대부분 막아준다. 물론 완벽한 건 아니지만. innodb의 repeatable read는 표준의 serializable에 가까운 격리 수준을 제공한다.

select @@transaction_isolation; -- 격리 수준 확인
-- 격리 수준 변경(현재 세션만)
set session transaction isolation level read committed;
-- 격리 수준 변경(시스템 전체)
set global transaction isolation level read committed;

격리수준 선택 #

특별한 이유가 없다면 기본 격리수준인 repeatable read. REPEATABLE READ 는 데이터 정합성과 동시성 사이에서 매우 합리적인 균형을 제공.

단순 조회 위주고 트래픽이 엄청나다면 read uncommitted 고려 가능.

serializable은 정합성이 극도로 중요할 때 사용을 고려할 수 있는데, 성능이 매우 떨어지므로 실무에는 거의 사용 안 한다. 보통은 락(낙관적/비관적 락)같은 대안을 주로 사용한다.

프로시저 #

마치 함수처럼, 자주 사용되는 복잡한 작업 절차 자체를 DB의 하나의 프로그램처럼 저장해 두고 필요할 때마다 불러서 사용할 수 없을까?
-> 저장 프로시저, 저장 함수, 트리거
DB 내에서 저장되어 실행되는 프로그램 조각들

저장 프로시저: sql 작업 묶음
저장 함수: 특정 계산 수행 후 '하나의 값'을 반환. count()같은 함수를 직접 만든다고 생각하면 된다.
트리거: 특정 테이블의 특정 이벤트 발생시 자동으로 실행되도록 된 프로그램. 특정 조건 만족되면 실행되는데 예를 들어 주문이 실행되면 자동으로 배송 관련 문장을 실행하는 등.

사용 #

다만 실무에서는 요즘 이런 프로시저, 함수, 트리거 기능을 거의 사용은 안한다. 지금은 이런 게 있다는 정도만 알고 넘어가자.

create procedure 프로시저이름(인수들) begin ~ end 구문으로 만든다. 지우는 건 drop procedure ~

함수도 비슷하게 create function ~과 같은 문법으로 만든다. 프로시저와 달리 반드시 returns로 반환을 명시해야 한다. 이렇게 하면 select 쿼리 같은 곳에서 내장 함수처럼 호출할 수 있다. 이때 함수가 동일 입력 동일 결과(순수함수)함수일 경우 deterministic 키워드로 명시해서 옵티마이저에 도움을 줄 수 있다.

트리거도 마찬가지로 create trigger 로 만든다. before 같은 걸 이용해서 어떤 이벤트에 대해 어떤 시점에 해당 트리거를 실행할지 설정 가능하다.

이유: 왜 이런 것들을 현대에는 잘 사용하지 않는가?

이런 기능이 유용할 때도 있지만 비즈니스 로직을 DB에 넣는 건 신중해야 한다.

기타 문법 팁 #

select distinct를 쓰면 서로 다른 값들만 결과로 나오도록 할 수 있다.

select에 리터럴을 넣어서 행을 임의로 추가할 수 있다. join에서 유용함. 예를 들어 다음과 같이 하면 고객 테이블에서 온 건 고객 역할, 직원 테이블에서 온 건 직원 역할로 명확하게 구분 가능

select name as 이름, '고객' as 역할, email as 이메일
from users

union all
select name as 이름, '직원' as 역할, concat(name, '@my-shop.com') as 이메일
from employees
order by 이름

날짜 기준으로 필터링할 때는 2025-06-01 처럼 쓰면 뒤에 00:00:00 시간이 자동으로 붙으므로, 만약 6월 30일까지의 데이터를 필터링하고 싶다면 < '2025-07-01'처럼 써야 한다. 그래야 7월 1일이 되기 바로 이전까지 필터링됨

count(*)와 count(id)는 다르다. count(*)는 완전히 모든 행을 세고, count(id)는 id가 null이 아닌 행만 센다. 만약 고유한 id 값을 세고 싶다면 count(distinct id) 사용. 단 이런 중복 제거는 당연히 성능 이슈가 있다. id에 유니크 인덱스가 걸려 있다면 빠르겠지만 유니크 인덱스가 걸려 있다면 distinct를 쓸 필요가 없다. https://www.reddit.com/r/SQL/comments/1bp0mtw/count_vs_countid_what_is_the_difference/

그리고 count(*)와 count(id)가 다른 건 id가 nullable일 때인데, 따라서 outer join의 결과에서 null인 컬럼이 나올 때 쓰면 유용하다.

from을 굳이 테이블이나 서브쿼리, 뷰로 쓸 필요는 없다. 그냥 정적 값의 목록들에서도 from을 쓸 수 있다.

SELECT
    COUNT(*),
    COUNT(a),
    COUNT(DISTINCT a)
FROM
    (VALUES (0), (1), (2), (2), (NULL)) AS X(a)

혹은 아예 전체 테이블 구조를 상수들로 만들고 쓸 수도 있다.

SELECT p.ID, p.FirstName, p.LastName, p.BirthDate
FROM (
    VALUES
    (1,'John','Smith','1990-01-01'),
    (2,'Jane','Smith','1992-12-31')
) p (ID, FirstName, LastName, BirthDate)