👀 1. 들어가며

1.1. 상황

SQL 쿼리가 복잡하고 길어지다보니 조회하는데 시간이 많이 걸리기 시작했습니다. 쿼리 최적화에 대한 필요성을 느끼고, 성능 향상을 위한 체크리스트를 정리하는 스터디를 진행했어요. presto SQL 쿼리 성능 향상을 위한 10가지 체크리스트를 공유합니다.

1.2. 예상 독자

• presto SQL 쿼리를 사용 중인 1~3년차 데이터 분석가
• presto SQL 쿼리를 효율적으로 작성하고 있는지 체크리스트로 점검하고, 성능을 최적화하고자 하는 분들

✅ 2. presto SQL 쿼리 성능 향상을 위한 10가지 체크리스트

1. SELECT * 를 사용하지 말자.

SELECT *를 사용하면 모든 열을 가져오므로 불필요한 데이터를 전송하고 처리하게 된다. 필요한 열이 많을 수록 모두 나열하기 번거롭다는 이유로 SELECT *을 사용하는 경우가 많은데, 필요한 열을 선택하여 나열하는 것을 습관화하자!

👍GOOD: SELECT column1, column2 FROM table
👎BAD : SELECT * FROM table

2. OR로 연결된 LIKE절 보다는 하나의 regexp_like 표현식을 사용하자

문자열에서 많은 값을 필터링할 때는 LIKE보다 regexp_like를 사용하는 것이 좋다. LIKE절이 많으면 쿼리가 매우 느려질 수 있기때문에, regexp_like를 사용해보자

👍GOOD:
SELECT column1
FROM TABLE2
WHERE user_id REGEXP_LIKE(user_id, 'usr1|usr2|usr3|usr4')


👎BAD:
SELECT column1
FROM TABLE1
WHERE user_id LIKE '%usr1%'
   OR user_id LIKE '%usr2%'
   OR user_id LIKE '%usr3%'
   OR user_id LIKE '%usr4%'

3. WHERE조건에 별도의 연산을 걸지말자

WHER조건에서 별도의 연산을 걸거나 날짜를 VARCHAR로 CAST하는 등 수식을 걸면 Full Table Scan을 하면서 조건 충족 여부를 판단해야해서 비효율적일 수 있다.

👍GOOD:
SELECT T1.column1, T1.column2, T1.column3,  T2.column2
FROM TABLE1 T1
INNER JOIN TABLE2 T2
ON T1.column1 = T2.column1
WHERE T1.column2 BETWEEN 4 AND 5
AND DATE(T1.column3) = DATE '2024-02-03'

👎BAD:
SELECT T1.column1, T1.column2, T1.column3,  T2.column2
FROM TABLE1 T1
INNER JOIN TABLE2 T2
ON T1.column1 = T2.column1
WHERE FLOOR(T1.column2/2) = 3 
AND CAST(DATE(T1.column3) AS varchar) = '2024-02-03'

4. GROUP BY는 unique count가 많은 순서부터 배치하고, 문자열 대신 숫자로 컬럼을 지정해주자

GROUP BY를 할 때, unique count가 많은 열부터 배치하면 연산이 효율적으로 이루어져 성능이 향상될 수 있다.

예를 들어 잠실초등학교 학생의 학년과 거주하는 행정동이 담긴 테이블이 있다고 하자. 학년은 unique count가 6개이고, 행정동은 20개라고 하자. 학생들의 거주 행정동과 학년에 따른 인구조사를 한다고 할 때, GROUP BY 행정동, 학년으로 unique count가 많은 컬럼을 먼저 그룹화하면 그룹의 크기가 작아지고, 작은 그룹을 처리하는데 불필요한 계산이 감소한다.

👍GOOD:
SELECT dong, grade, count(*)
FROM TABLE1
group by dong, grade

👎BAD:
SELECT grade, dong, count(*)
FROM TABLE1
group by grade, dong

그리고, GROUP BY절 내에서는 문자열 대신 숫자를 사용하자. 숫자는 문자열보다 저장하는 데 더 적은 메모리가 필요하고 비교가 더 빨라 효율적이다.

👍GOOD: 
SELECT dong, grade, count(*)
FROM TABLE1
group by 1,	2

5.ORDER BY는 LIMIT과 함께 사용하고, 문자열 대신 숫자로 컬럼을 지정해주자

ORDER BY는 쿼리 결과를 정렬하기 위해, 모든 데이터를 한 worker로 보낸 후 정렬하므로 메모리 부담이 발생할 수 있고, 이로 인해 쿼리 실행 시간이 오래 걸릴 수 있다.

ORDER BY를 사용한다면, LIMIT 절을 사용하자. worker를 제한하여 정렬 비용을 크게 줄일 수 있다.

👍GOOD:
SELECT column1,	column2
FROM TABLE1 
ORDER BY column1
LIMIT 1000

👎BAD:
SELECT column1,	column2
FROM TABLE1 
ORDER BY column1

그리고, GROUP BY절을 사용할 때 처럼 문자열 대신 숫자로 컬럼을 지정해주자!

👍GOOD: 
SELECT column1,	column2
FROM TABLE1 
ORDER BY 1
LIMIT 1000

6. 서브쿼리를 많이 사용하지 말자.

서브쿼리는 결과를 저장하고 다시 읽느라 비효율이 발생할 수 있다. 서브쿼리 중 JOIN으로 대체할 수 있는 부분이 있다면 JOIN으로 변경해서 사용하자. JOIN은 필요한 열만 선택하여 데이터를 가져와서 더 효율적으로 가져올 수 있다.

👍GOOD: 
SELECT t1.column1
FROM table1 t1
INNER JOIN table2 t2 
	ON t1.column2 = t2.column2

👎BAD: 
SELECT column1
FROM table1
WHERE column2 IN (SELECT column2 FROM table2)

7. 중첩된 서브쿼리 대신 WITH 문을 사용하자.

WITH 문을 사용하면 중첩된 서브쿼리를 최적화 하여 중복 작업을 피하고 성능을 향상시킬 수 있다.

👍GOOD:  
WITH subquery AS 
(
	SELECT column1
		, max(column2) 
    FROM table1
    GROUP BY 1
) 

SELECT * 
FROM subquery

👎BAD:
SELECT * 
FROM 
(
	SELECT column1
		, max(column2) 
    FROM table1
    GROUP BY 1
) subquery

8.UNION을 최대한 피하고 UNION ALL을 사용하자.

UNION은 중복을 제거하고 정렬 작업이 추가로 필요하다. UNION ALL은 중복을 처리하지 않고 데이터를 결합해 UNION보다 성능이 더 좋다.

👍GOOD: 
SELECT column1 
FROM table1 

UNION ALL 

SELECT column1 
FROM table2

👎BAD: 
SELECT column1 
FROM table1 

UNION 

SELECT column1 
FROM table2

9. JOIN 조건이 표현식이면 수정하자

JOIN 조건이 표현식인 경우 JOIN 작업이 매우 느려질 수 있다. 큰 테이블을 조인할 수록 더 느려지니 수정해주자.

👍GOOD: 
SELECT a.datestr, b.name
FROM table1 a
JOIN (
  SELECT name
  	, CAST((b.col1 * 1000 + b.col2 * 100) AS VARCHAR) AS col3
  FROM table2
) b ON a.col1 = b.col3

👎BAD: 
SELECT a.datestr, b.name
FROM table1 a
LEFT JOIN table2 b 
	ON a.col1 = CAST((b.col1 * 1000 + b.col2 * 100) AS VARCHAR);

10. EXPLAIN 쿼리를 사용해서 성능을 향상시키자

EXPLAIN 쿼리를 사용하면 쿼리 실행 계획을 분석하여 불필요한 조건자 또는 인덱스를 확인할 수 있다. 불필요한 부분을 제거하면 쿼리 성능이 향상된다.

EXPLAIN
   SELECT 
      c.c_name
   FROM tpch100.customer c
   WHERE c.c_custkey = CAST(RANDOM() * 1000 AS INT)
   AND c.c_custkey BETWEEN 1000 AND 2000
   AND c.c_custkey = 1500

👆EXPLAIN 쿼리를 사용하면 👇쿼리 실행 계획을 확인할 수 있다.

Query Plan
- Output[c_name] => [[c_name]]
    - RemoteExchange[GATHER] => [[c_name]]
        - ScanFilterProject[table = 
awsdatacatalog:HiveTableHandle{schemaName=tpch100, 
tableName=customer, analyzePartitionValues=Optional.empty}, 
filterPredicate = (("c_custkey" = 1500) AND ("c_custkey" = 
CAST(("random"() * 1E3) AS int)))] => [[c_name]]
                LAYOUT: tpch100.customer
                c_custkey := c_custkey:int:0:REGULAR
                c_name := c_name:string:1:REGULAR

filterPredicate부분을 보면, 원래 3개의 조건자를 2개의 조건자로 병합하고 적용 순서를 변경해서 제시하는 것을 볼 수 있다.

• BEFROE:
  c.c_custkey = CAST(RANDOM() * 1000 AS INT)
  AND c.c_custkey BETWEEN 1000 AND 2000
  AND c.c_custkey = 1500
• AFTER: (("c_custkey" = 1500) AND ("c_custkey" =
  CAST(("random"() * 1000) AS int)))

c.c_custkey BETWEEN 1000 AND 2000이 아무런 영향을 미치지 않는다는 의미로, 이 조건자를 제거해주는 방식으로 최적화 해줄 수 있다.

🙌 3. 정리하며

Presto SQL 쿼리를 작성할 때, 성능을 최적화하는 방법들을 다뤄보았습니다.쿼리가 길어질 수록 대용량의 데이터를 다룰수록 SQL 쿼리 최적화는 중요하므로 체크 리스트를 통해 나만의 SQL 쿼리를 최적화하는데 도움이 되기를 바랍니다!

📑 참고 자료

• Query Optimization for Presto on DoordaHost
• Presto Query Tuning
• Using EXPLAIN and EXPLAIN ANALYZE in Athena
• 10 Tips For Presto Query Performance Optimization
• 쿼리 최적화: 빠른 쿼리를 위한 7가지 체크리스트
• [AWS] Athena를 조금 더 효율적으로 쓰기 위한 쿼리가이드