들어가며
데이터 분석가로 업무를 하다 보면 슬슬 쿼리의 정확도(쿼리를 통해 원하는 데이터를 정확하게 추출하였는가) 이외에 쿼리 최적화에 대해 관심을 갖게 된다. 자칫 잘못하면 매우 큰 비용의 쿼리를 사용하게 되거나(omg...), 작성한 쿼리가 한참 돌면서 매우 많은 슬롯을 사용하게 되는 문제가 발생하기 때문이다.
- 큰 비용의 쿼리를 사용한다 = 물리적으로 돈이 들어감
- 많은 슬롯을 사용한다 = 가상의 빅쿼리 CPU를 많이 사용. 특정 상황이 아니면 물리적으로 비용이 발생하지 않지만 슬롯 관리를 못할 경우 쿼리 속도가 매우 느려지고, 다른 사람의 자원을 잡아먹게 될 수도 있어 문제가 발생할 수 있음.
이런 문제를 일으키면 금쪽이 데이터 분석가가 될 수 있기 때문에 쿼리 최적화와 관련하여 노력했던 부분을 간략히 소개하는 시간을 가져보려 한다.
다만, 정확한 이론적 근거에 의한 쿼리 최적화라기보다는, 써보니까 이렇다더라 하는 느낌이 강하긴 하다. (그래서 제목이 야매 쿼리 최적화다.)
보다 자세한 이론적 근거는 빅쿼리(BigQuery) 공식 사이트에 자세한 설명이 있으니 해당 링크를 참고하면 좋을 것 같다.
자, 그러면..!
조회 비용 줄이기
1. 필요한 컬럼만 사용한다.
제일 기본 중의 기본은 쿼리 작성 시 필요한 컬럼을 명시하는 것이다.
SELECT
*
FROM `project_id.mydataset.mytable`
위와 같은 형식으로 와일드카드(*)를 사용하기 보다는
SELECT
id,
name,
address,
job
FROM `project_id.mydataset.mytable`
이렇게 필요한 컬럼을 명시하는 것을 권장한다. (웬만큼 귀찮지 않다면 와일드카드보다는 필드 이름을 명시하자.)
빅쿼리는 열 기반 데이터베이스인데 대용량 데이터를 처리하기 위해 열 기반으로 만들었다고 하나, 자세한 이론적 근거는 잘 모른다....(그것이 야매니까.. 🙃)
확실한 건 열 기반 데이터베이스에서 쿼리로 조회하는 컬럼 개수가 늘어나면 늘어날수록 비용도 함께 늘어난다는 것이다.
와일드카드(*)로 조회하면 모든 열을 다 조회해버리기 때문에 최악의 경우 어마어마한 비용 폭탄을 맞을 수 있다.
2. 파티셔닝 테이블인 경우, 파티셔닝 필터링을 활용하자.
파티셔닝이란..! 테이블에서 필요한 부분만 조회할 수 있도록 데이터를 작게 쪼개어주는 것을 의미한다.
공식적인 설명(빅쿼리 공식 가이드)을 가져오면 아래와 같다.
파티션을 나눈 테이블은 파티션이라는 세그먼트로 분할되어 데이터를 보다 쉽게 관리하고 쿼리할 수 있게 해줍니다. 큰 테이블을 작은 파티션으로 나누면 쿼리 성능을 높일 수 있으며 쿼리에서 읽는 바이트 수를 줄여 비용을 제어할 수 있습니다. 테이블을 세분화하는 데 사용되는 파티션 열을 지정하여 테이블을 파티션으로 나눕니다.
파티셔닝으로 범위를 지정하면 테이블에서 필요한 부분만 조회할 수 있어 쿼리 성능을 높이고, 쿼리 비용을 절약할 수 있다.
예를 들어 날짜가 들어 있는 테이블이고, 쿼리를 사용하는 사용자들이 일반적으로 날짜를 지정하여 데이터를 조회한다고 가정해 보자. 이 경우, 파티셔닝된 특정 날짜를 범위로 지정하여 데이터를 조회했을 때 비용 이득을 볼 수 있다. (쓸데없는 날짜는 가져오지 않기 때문)
3. 미리보기를 사용한다.
빅쿼리에서 "테이블 미리보기"를 누르면 데이터가 어떻게 생겼는지 미리 파악할 수 있다. (아래 사진에서는 PREVIEW)
단순히 테이블이 어떻게 생겼는지를 파악하려면 스키마나 미리보기를 활용하는 편이 낫다. 미리보기를 활용하면 전체 테이블을 조회하는 쿼리를 실행하지 않고(쿼리 비용을 사용하지 않고) 스키마와 일부 샘플 데이터를 확인할 수 있어 비용 절감이 가능하기 때문이다.
다만 미리보기 상태에서는 데이터를 복사한다거나, 대략적인 분포를 확인하기는 어렵기 때문에 특정 파티셔닝만 샘플로 조회하기도 한다. (예. 날짜가 파티셔닝으로 걸려 있고, 날짜를 하루만 지정해서 조회)
조회속도 줄이기
1. JOIN을 하기 전에 데이터의 레코드를 최대한 줄인다.
이 방법은 가장 기본이면서도, 가장 확실한 효과를 보여주는 방법이다.
업무를 하면서 대시보드 속도가 느려 골치가 아팠던 일이 있었는데... 이런저런 방법을 다 시도해도 차도가 없다가, 데이터 엔지니어 분께서 "JOIN을 하기 전에 먼저 필터링을 거는 방법"으로 고쳐주셨던 기억이 있다. (흑역사)
예를 들면 이런 식이다. score_info라는 테이블과 subject_info 라는 테이블을 JOIN 하는 상황을 가정해 보자. 이 중 subject_id가 1, 2, 3인 경우만 가져와서 쓰려고 한다.
그러면 쿼리를 이렇게 쓸 수 있지만,
-- 아쉬운 쿼리 예시
with score_info AS (
SELECT
id,
subject_id,
score
FROM data_1
),
subject_info AS (
SELECT
subject_id,
subject_name
FROM data_2
)
SELECT
*
FROM score_info
LEFT JOIN subject_info
using (subject_id)
# 필터링을 조인 이후에 진행
WHERE subject_id IN (1,2,3)
이보다는 먼저 필터링이 가능하다면, 필터링을 통해 데이터의 크기를 줄인 상태에서 JOIN을 하는 편이 성능이 좋다는 뜻이다.
-- JOIN 전에 데이터를 줄여온 예시
with score_info AS (
SELECT
id,
subject_id,
score
FROM data_1
WHERE subject_id IN (1,2,3) # JOIN하기 전에 필터링을 먼저 해서 레코드의 크기를 줄인다.
),
subject_info AS (
SELECT
subject_id,
subject_name
FROM data_2
)
SELECT
*
FROM score_info
LEFT JOIN subject_info
using (subject_id)
2. 브로드캐스트 JOIN을 하려고 최대한 노력하자.
큰 테이블을 왼쪽에 두고, 작은 테이블을 오른쪽에 둘 경우 브로드캐스트 조인이 되어 성능 상 이득을 볼 수 있다고 한다. (원리는 잘 모름..)
브로드캐스트 조인은 작은 테이블의 모든 데이터를 큰 테이블을 처리하는 각 슬롯으로 보냅니다. 브로드캐스트 조인을 먼저 수행하는 것이 좋습니다.
라고 공식 가이드에 설명되어 있기는 한데, 어쨌든 브로드캐스트 조인이 좋다고 하니 웬만하면 이렇게 써보도록 하자.
3. 셀프 조인은 무조건 방지해야 한다? 아닐 수도 있다.
공식 가이드에서는 셀프조인(자체 조인) 대신 window 함수나 PIVOT 연산자를 사용하는 것을 권장하고 있다.
셀프 조인은 동일한 테이블을 JOIN해서 사용하는 것을 의미하는데, 이것을 하지 말라고 하는 이유는 비교적 명쾌하다.
셀프 조인을 하게 되면, 최종 데이터는 데이터 레코드 수(N) * 데이터 레코드 수(N) = N의 제곱만큼 늘어날 수 있어 성능이 저하될 수 있다는 것이다.
그래서 셀프 조인을 피하기 위해 윈도우 함수를 사용하곤 하는데.... 윈도우 함수에 걸어둔 파티션의 그룹의 종류가 꽤 많다면, 윈도우 함수와 셀프 조인의 성능에 큰 차이가 없기도 했다.
가령 PARTITON BY로 걸어둔 필드의 카디널리티가 꽤 높다면(unique한 개수가 꽤 많다면) 당연히 그룹의 수도 엄청나게 늘어나게 되고, 그 그룹의 수가 레코드 수와 거의 차이가 없을 정도로 많다면 당연히 내부에서 연산 작업을 많이 진행하게 된다.
거기에 윈도우 함수 안에서 정렬(ORDER BY)까지 한다면, 오히려 윈도우 함수가 셀프 조인보다 성능이 떨어지기도 했다. 정렬은 꽤 비싼 연산이기 때문이다.
파티션으로 건 필드의 카디널리티가 높고, 거기에 정렬까지 해야 하는 윈도우 함수라면 성능이 떨어지기 때문에, 어떤 상황에서는 셀프 조인이 그나마 더 나아지기도 한다. 따라서 셀프 조인을 무조건적인 악으로 취급하기에는 좀 곤란할 것 같다.
다만, PIVOT이나 윈도우 함수를 사용할 수 있다면 선택의 폭이 더 넓어지는 것이므로 알아두는 것이 좋긴 하다.
나가며
결국 요악하면.. 성능을 높이기 위해서는 최대한 필요한 데이터만 가져와야 한다는 것이다.
공식 가이드에는 이런 저런 쿼리 최적화에 대한 방법론이 적혀 있지만, 제일 크게 도움을 봤던 건 데이터의 크기를 최대한 줄이는 것이었다. 큰걸 바꿔야 티가 많이 나는데, 레코드 수를 줄이는 게 가장 파급력이 크기 때문에 이게 제일 효과적이지 않았나 싶다.
데이터를 최대한 줄이고 나서도, 쿼리 최적화가 필요하다면 다시 공식 가이드를 찾아보는 게 순서일 것 같고,
때에 따라서는 쿼리 최적화를 시도할 것이 아니라, 어느 정도 쿼리로 집계해놓은 데이터를 적재해서 사용한다거나 하는 식으로 아예 다른 방법을 고려해야 할 수도 있지 않을까 싶다.
아무튼 금쪽이 데이터 분석가가 되지 않기 위한 최소한의 노력은 기울여보자! 는 다짐으로 글을 마무리해본다.
'Tools > SQL' 카테고리의 다른 글
| bigquery - ARRAY와 관련된 몇 가지 활용 팁 (SAFE_OFFSET, STRING_AGG, ML.NGRAMS) (0) | 2023.04.08 |
|---|
