📌 학습주제
1. 테이블을 집합으로 생각하기
2. *, % 사용 지양하기
3. 데이터 타입 확인하기
4. JOIN 시 유의할 점
5. 가독성 높이기
현업에서 실제 서비스에 사용하는 데이터는 규모가 매우 크기 때문에 기본적인 원칙을 지키지 않으면 여러 DB 문제를 일으킨다.
1. 테이블을 집합으로 생각하기
테이블은 일반적으로 집합 개념에 대응된다.
집합 내 다양한 원소들이 존재하고, 각 원소들의 특징은 컬럼값으로 구분할 수 있다.
원칙1. WHERE 구문 필터링을 통해 최대한 작게 만들어 놓고 JOIN 하기
: 필터링이 가능할 때 최대한 먼저 필터링 하여 집합의 크기를 줄여준 후에 다른 테이블과 JOIN하는 것이 좋다.
-- 느린 코드 (MySQL 8에서 실행가능)
WITH manager AS(
SELECT id, name, managing
FROM managers
),
clk AS(
SELECT product_id, user_name
FROM clicks
)
SELECT manager.name, managing, count(product_id) AS clk_cnt
FROM manager INNER JOIN clk ON manager.name = clk.user_name
WHERE id = 1
GROUP BY 1, 2
ORDER BY 3 DESC
-- 빠른 코드 (MySQL 8에서 실행가능)
WITH manager AS(
SELECT id, name, managing
FROM managers
WHERE id = 1
),
clk AS(
SELECT product_id, user_name
FROM clicks
)
SELECT manager.name, managing, count(product_id) AS clk_cnt
FROM manager INNER JOIN clk ON manager.name = clk.user_name
GROUP BY 1, 2
ORDER BY 3 DESC
** 쿼리 프로파일링
: 쿼리 실행에 얼마나 걸리는지, CPU는 얼마나 사용하는지 등 쿼리 실행에 대한 세부 내용을 확인하는 과정
SELECT @@profiling;0으로 뜨면 프로파일링이 꺼져 있다는 의미이기 대문에 아래 문장을 실행해서 켜준다.
SET @@profiling = 1;1으로 뜨면 프로파일링이 켜져 있다는 의미
쿼리별로 프로파일링을 확인해보려면 아래 문장을 실행한다.
show profiles;결과로 Query_ID(번호), Duration(실행시간), Query(쿼리문)를 확인할 수 있다.
2. *, % 사용 지양하기
테이블을 조회할 때 *과 % 기호를 제한적으로 사용해야 한다.
제한적인 규칙일수록 DB리소스를 절약할 수 있기 때문에 훨씬 효율적이다.
현업에서 업무를 하다보면 사내의 다양한 테이블들을 접하게 되는데, 회사 규모가 커지면 사내의 모든 테이블을 파악하기 힘들다. 새로운 테이블을 접했을 때, 해당 테이블 정보에 대한 문서가 상세히 적혀있다면 좋겠지만 그렇지 않은 경우가 대부분이다.
따라서 모르는 테이블을 살펴볼 때는 데이터의 일부를 조회하면서 이해도를 높이게 되는데,
이 때 지켜야 할 기본적인 원칙들이 있다.
원칙1. LIMIT을 걸고 조회하기
: 테이블에 무슨 내용이 있는지 파악하기 위해 쿼리를 실행하는 경우 LIMIT을 걸고 조회해보는 것이 안전하다.
SELECT product_id, category, name
FROM products
LIMIT 10
원칙2. 파티션이 있는 테이블인지 확인하고, 파티션을 필터 조건으로 걸고 조회하기
: 파티션으로 나뉘어져 있는 테이블의 경우, WHERE 구문으로 파티션을 지정해주는 것이 필수이다.
사용자 행동 로그 데이터의 경우 데이터가 쌓인느 양이 매우 많기 때문에 하루 단위로 파티션을 나누어 저장한다.
상품 노출 로그처럼 하루 단위도 너무 큰 경우에는 시간 단위로 파티션을 나누어 저장하기도 한다.
이러한 테이블을 파티션 없이 조회하면 쿼리가 엄청난 양으로 쌓이기 때문에 파티션을 필수로 지정하는 것이 좋다.
SELECT clk_index, user_name, product_id
FROM clicks
WHERE date = '20231104'
LIMIT 10
원칙3. 컬럼 수가 많은 테이블을 조회할 때 SELECT * 사용 지양하기
: SELECT *로 컬럼 전체를 조회하는 것보다 파악하고 싶은 컬럼만 지정해서 조회하는 것이 좋다.
원칙4. LIKE 사용 시 % 제한적으로 사용하기
임의의 문자열을 의미하는 %는 임의의 문자열 개수에 상관없이 포함되어야 하는 문자열만 있으면 되기 때문에 연산량이 커질 수 밖에 없다.
따라서 조회 대상이 큰 테이블의 경우 %를 제한적으로 사용하거나,
글자수를 알고 있는 경우 %보다는 _를 사용해서 임의의 문자 한글자 씩만 지정해서 조회하는 것이 바람직하다.
-- 시작/끝 문자열이 확실하다면 해당 위치에만 %를 사용한다.
SELECT product_id, name
FROM products
WHERE name LIKE '23FW%'
LIMIT 10
-- 글자수가 확실하다면 %보다 _ 사용이 바람직하다.
SELECT prodcut_id, name
FROM prodcuts
WHERE name LIKE '23FW__'
LIMIT 10
3. 데이터 타입 확인하기
SQL에서는 '묵시적 형 변환'을 제공한다.
'묵시적 형 변환'이란?
: 비교하고자 하는 두 값이 다른 타입일 때 DB가 알아서 두 타입을 맞춰준 후에 비교하는 것
묵시적 형 변환이 발생할 경우 생기는 문제는
1) 형 변환에 걸리는 시간만큼 쿼리가 비효율적이게 될 수 있다.
2) 정확한 타입을 확인하지 않은 채 쿼리가 복잡해지면 쿼리 결과가 원하는 대로 나오지 않았을 때 문제의 원인을 파악하는데 시간이 오래 소요될 수 있다.
기본적인 원칙을 지켜주어야 묵시적 형 변환이 일어나지 않는다.
원칙1. 비교 연산자 사용시 타입 잘 확인하기
: 비교 연산자를 사용할 때 데이터 타입을 잘 확인한 후에 동일한 타입의 값으로 비교하는 것이 바람직하다.
-- date 컬럼이 string 타입일 때 문자열과 비교해야 묵시적 형 변환이 일어나지 않는다.
SELECT count(1)
FROM clicks
WHERE date > '20231031'
원칙2. WHERE 구문에서 왼쪽 컬럼에 함수 적용 지양하기
: WHERE구문에서 왼쪽 컬럼에 함수를 적용할 경우 인덱스 사용이 불가능해지기 때문에 문제가 될 수 있다.
인덱스는 책 뒷면의 찾아보기와 같은 역할을 하는데, 데이터의 읽기 속도를 높여줄 수 있는 자료구조이다.
즉, 인덱스 사용 가능 여부에 따라 데이터 읽기 속도에 매우 차이가 나게 된다.
또한, 큰 테이블의 컬럼에 함수를 적용하면 함수 수행하는 시간만큼 쿼리가 더 무거워진다.
꼭 함수를 사용해야 한다면 상대적으로 작은 테이블의 컬럼에 함수를 적용하는 것이 바람직하다.
-- 이벤트와 관련된 상품을 클릭한 사람들 중에
이벤트 종료 전에 클릭이 얼마나 일어났는지 집계하기 위한 쿼리
WITH clk AS(
SELECT clk_index, event_index, date -- Dash(-)가 없는 형태로 날짜가 저장됨
FROM clicks
),
event AS(
SELECT event_index, DATE_FORMAT(event_end, '%Y%m%d') AS event_end_dt
FROM events
)
SELECT count(1)
FROM clk INNER JOIN event ON clk.event_index = event.event_index
WHERE date <= event_end_dt
만약 아래와 같이 WHERE 구문에서 date 컬럼에 DATE_FORMAT 함수를 적용할 경우 인덱스 사용이 불가하기 때문에 문제가 된다.
-- 이벤트와 관련된 상품을 클릭한 사람들 중에
이벤트 종료 전에 클릭이 얼마나 일어났는지 집계하기 위한 쿼리
WITH clk AS(
SELECT clk_index, event_index, date -- Dash(-)가 없는 형태로 날짜가 저장됨
FROM clicks
),
event AS(
SELECT event_index, event_end AS event_end_dt
FROM events
)
SELECT count(1)
FROM clk INNER JOIN event ON clk.event_index = event.event_index
WHERE DATE_FORMAT(date, '%Y-%m-%d') <= event_end_dt
4. JOIN시 유의할 점
JOIN은 연산량이 많기 때문에 JOIN 대상 테이블을 최대한 줄여놓고 시작하는 것이 좋다.
이외에도 기본적으로 유의해야 할 점들을 지키면 DB 작업을 효율적으로 할 수 있다.
원칙1. JOIN하는 테이블 간 관계 고려하기
두 테이블을 JOIN할 때 테이블 간 관계는 크게 3가지로 나뉠 수 있다.
1. 1:1 관계
: 왼쪽 테이블의 행 1개가 오른쪽 테이블의 행 1개에 대응되는 관계
2. 1:N 관계
: 왼쪽 테이블의 행 1개가 오른쪽 테이블의 행 여러 개에 대응되는 관계
3. N:N 관계
: 왼쪽 테이블의 행 1개가 오른쪽 테이블의 행 여러개에 대응되고, 오른쪽 테이블의 행 1개도 왼쪽 테이블의 행 여러개에 대응되는 관계
E-R Model
: 테이블은 엔티티(Entity)로 두고 테이블 간 관계를 Relationship으로 표현한 형태
원칙2. JOIN 대상 테이블에 대해 데이터 중복이 있는지 확인하기
데이터 특성상 중복이 발생하는 경우가 종종 있다.
만약 이를 확인하지 않고 분석을 수행하면 큰 테이블끼리 결합하는 경우 쿼리 속도가 매우 느려지거나 의도한 결과와 다른 결과를 얻을 수 있기 때문에 주의해야 한다.
원칙3. 여러 가지 쿼리 작성 방식을 고려하기
DB에서 원하는 결과를 얻기 위해 다양한 방식으로 쿼리를 작성할 수 있다.
같은 결과라도 해당 결과를 도출하기 위한 쿼리 작성 방식이 달라지는데, 상황에 따라 가장 효율적인 방식을 사용하는 것이 좋다.
아래 예제 쿼리는 모두 동일한 결과를 반환하지만 각각 실행되는 속도가 다르다.
예제는 유저별로 처음 클릭한 상품을 뽑기위한 쿼리이다.
-- (1) 실행시간 : 0.021초
SELECT user_name, product_id
FROM(
SELECT user_name, clk_index, product_id,
row_number() over(PARTITION BY user_name ORDER BY clk_index ASC) AS rownum
FROM clicks
) w
WHERE rownum = 1
-- (2) 실행시간 : 0.015초
WITH clk AS(
SELECT user_name, product_id, clk_index
FROM clicks
),
mini AS(
SELECT user_name, min(clk_index) AS min_idx
FROM clicks
GROUP BY 1
)
SELECT clk.user_name, clk.product_id
FROM clk INNER JOIN mini ON clk.user_name = mini.user_name AND clk.clk_index = min_idx
-- (3) 실행시간 : 0.016초
SELECT c.user_name, product_id
FROM clicks c
INNER JOIN (
SELECT user_name, min(clk_index) AS min_idx
FROM clicks
GROUP BY 1
) g
ON c.user_name = g.user_name
AND c.clk_index = min_idx
5. 가독성 높이기
Q. 쿼리 작성에 있어서 가독성이 중요한 이유는 무엇일까?
A. 쿼리를 보는 미래의 나 자신과 다른 사람들을 위해서이다.
사람마다 쿼리를 작성하는 스타일이 다르기 때문에 다른 사람이 작성한 쿼리를 보는 데는 어느 정도의 시간이 소요된다.
심지어 자기 자신이 작성한 쿼리더라도 시간이 지난 후 다시 보면 낯설게 보이기 마련이다.
따라서, 가독성이 좋게 쿼리를 작성해두면 이해하기가 훨씬 수월해진다.
또한, 완벽하게 이해하지 않고도 해당 쿼리에서 필요한 부분만 수정해서 사용할 수도 있다.
가독성을 높이기 위한 몇가지 규칙들이 존재한다. 잘 알아두고 현업에서 적용해보자!
원칙1. 서브쿼리 보다는 WITH 구문이 가독성이 좋다.
WITH 구문은 블록으로 구분되기 때문에
각 블록 안의 내용을 숙지한 후, 블록 바깥쪽에서 어떤식으로 결합이 이루어지는지 순차적으로 이해할 수 있다.
원칙2. WITH 절 사용시, 각 블록의 이름을 잘 지정해야 한다.
블록의 이름을 잘 지정해두면, 각 블록이 의미하는 바가 명확해져서 가독성도 높아진다.
원칙3. 쿼리가 복잡해지면 중간중간 주석을 작성하는 것이 좋다.
예제 쿼리를 통해 어떤 식으로 작성하면 가독성이 높아지는지 살펴보자.
WITH odr_cnt AS(
-- 고객별 구매 수
SELECT c.customer_id, count(distinct order_id) AS odr_cnt, sum(amount) AS total_purchase
FROM Customers c INNER JOIN Orders o ON c.customer_id = o.customer_id
GROUP BY 1
ORDER BY 2 DESC
),
ship_cnt AS(
-- 고객별 배송 수
SELECT c.customer_id, count(distinct shippping_id) AS ship_cnt
FROM Customers c INNER JOIN Shippings s ON c.customer_id = s.customer
WHERE status = 'Pending'
GROUP BY 1
ORDER BY 2 DESC
)
SELECT oc.customer_id, odr_cnt, total_purchase, COALESCE(ship_cnt, 0) AS shipping cnt
FROM odr_cnt oc LEFT JOIN ship_cnt sc ON oc.customer_id = sc.customer_id
