날짜 함수, 쿼리 최적화

MySQL vs. PostgreSQL 날짜 연산 비교

기능	MySQL	PostgreSQL
현재 날짜 가져오기	CURDATE()	CURRENT_DATE
현재 시간 가져오기	CURTIME()	CURRENT_TIME
현재 날짜 및 시간 가져오기	NOW() 또는 CURRENT_TIMESTAMP	NOW() 또는 CURRENT_TIMESTAMP
날짜에서 연, 월, 일 추출	YEAR(date), MONTH(date), DAY(date)	EXTRACT(YEAR FROM date), EXTRACT(MONTH FROM date), EXTRACT(DAY FROM date)
시간에서 시, 분, 초 추출	HOUR(time), MINUTE(time), SECOND(time)	EXTRACT(HOUR FROM time), EXTRACT(MINUTE FROM time), EXTRACT(SECOND FROM time)
날짜 차이 계산(일 단위)	DATEDIFF(date1, date2)	AGE(date1, date2) 또는 date1 - date2 (interval)
시간 차이 계산(초 단위)	TIMESTAMPDIFF(SECOND, time1, time2)	EXTRACT(EPOCH FROM (time1 - time2))
날짜/시간 더하기	DATE_ADD(date, INTERVAL n UNIT) 또는 ADDDATE(date, INTERVAL n UNIT)	date + INTERVAL 'n UNIT'
날짜/시간 빼기	DATE_SUB(date, INTERVAL n UNIT) 또는 SUBDATE(date, INTERVAL n UNIT)	date - INTERVAL 'n UNIT'
날짜를 문자열로 변환	DATE_FORMAT(date, '%Y-%m-%d %H:%i:%s')	TO_CHAR(date, 'YYYY-MM-DD HH24:MI:SS')
문자열을 날짜로 변환	STR_TO_DATE(string, '%Y-%m-%d')	TO_DATE(string, 'YYYY-MM-DD')
문자열을 타임스탬프로 변환	STR_TO_DATE(string, '%Y-%m-%d %H:%i:%s')	TO_TIMESTAMP(string, 'YYYY-MM-DD HH24:MI:SS')

 

 

 

쿼리 최적화 - 아마존

Amazon Athena – 10가지 성능 향상 팁 | Amazon Web Services

1️⃣ 데이터 파티셔닝

데이터를 날짜, 국가, 지역 등의 컬럼을 기준으로 파티셔닝하면

쿼리 시 스캔해야 하는 데이터 양을 줄여 성능을 향상시킬 수 있습니다.

 

⚠️ 과도한 파티션은 피하기

 

 

2️⃣ 데이터 압축

Gzip, Snappy 사용해 성능 향상

 

→ 압축 비율이 높을수록 데이터 압축 및 압축 해제에 더 많은 CPU가 필요하다

 

3️⃣ 적절한 파일 크기 유지

 

→ 데이터를 병렬적으로 읽거나 데이터 블록을 순차적으로 읽을 수 있을 때, 쿼리를 효율적으로 실행한다.

그러나 파일 크기가 128MB 보다 작으면 추가 작업에 시간을 더 쓸 수 있다.

 

 

4️⃣ 컬럼 데이터 저장소 생성 최적화

Parquet/ORC은 열 단위 압축, 다양한 인코딩, 데이터 유형에 기반한 압축 및 술어 푸시다운을 사용하여 효율적으로 데이터를 저장하는 기능을 제공

 

 

5️⃣ ORDER BY 최적화

 

 

6️⃣ Join 최적화

두 테이블을 Join 할 때 왼쪽에 큰 테이블을 지정하고 오른쪽에 작은 테이블을 지정

 

 

 

예외적으로 여러 테이블을 Join할 때

 

⚠️ SQL 실행 계획 확인하기!!

 

 

7️⃣ GROUP BY 최적화

• 카디널리티가 높은 컬럼을 우선적으로 정렬
  
  • 분산이 균등한 컬럼을 먼저 배치하면 병렬 처리가 최적화
  • ex) GROUP BY state, gender → state
• 숫자 데이터를 사용하여 비교 연산 최적화
  • 문자열보다 숫자가 비교 연산 속도가 빠르고 메모리 사용이 적음
  • GROUP BY user_id(int) > GROUP BY user_email(str)
• SELECT 절에서 불필요한 컬럼 제외
  • GROUP BY 수행 시 필요한 컬럼만 선택하면 메모리 사용량이 줄어듦.
  • SELECT state, gender, count(*) → 최소한의 컬럼만 사용

 

8️⃣ LIKE 최적화

문자열 열에서 여러 값을 필터링 할 때는 일반적으로 LIKE 보다 여러 번 RegEx를 사용하는 것이 좋다.

LIKE 할 내용이 많고, 문자열 열이 RegEx를 사용하면 절약 효과가 커진다.

 

 

 

9️⃣ 근사 함수 사용

 

함수는 전체 문자열 대신 값의 고유 해시를 계산하여 메모리 사용을 최소화

단점은 2.3%의 표준 오류가 있다는 것이다.

 

 

🔟 필요한 칼럼만 사용

 

 

 

쿼리 최적화 - 빅쿼리

쿼리 계산 최적화  |  BigQuery  |  Google Cloud

 

SELECT * 사용 자제

• 데이터 미리보기 사용
• 특정 열을 쿼리: limit 써도 전체 쿼리를 읽음
• 파티션을 나눈 테이블 사용
  WHERE datetime = 2017-01-01
• select * except 로 제외할 열 사용

SELECT * EXCEPT (col1, col2, col5)
from mydataset

 

과도한 와일드 카드 테이블 사용 피하기

1. FROMbigquery-public-data.noaa_gsod.gsod194* 
2. FROM bigquery-public-data.noaa_gsod.*

 

JOIN 전 데이터 줄이기

# 1

WITH users_posts AS (
  SELECT *
  FROM
    `bigquery-public-data`.stackoverflow.comments AS c
  JOIN
    `bigquery-public-data`.stackoverflow.users AS u
  ON
    c.user_id = u.id
)
SELECT
  user_id,
  ANY_VALUE(display_name) AS display_name,
  ANY_VALUE(reputation) AS reputation,
  COUNT(text) AS comments_count
FROM users_posts
GROUP BY user_id
ORDER BY comments_count DESC
LIMIT 20;

 

# 2

WITH comments AS (
  SELECT
    user_id,
    COUNT(text) AS comments_count
  FROM
    `bigquery-public-data`.stackoverflow.comments
  WHERE
    user_id IS NOT NULL
  GROUP BY user_id
  ORDER BY comments_count DESC
  LIMIT 20
)
SELECT
  user_id,
  display_name,
  reputation,
  comments_count
FROM comments
JOIN
  `bigquery-public-data`.stackoverflow.users AS u
ON
  user_id = u.id
ORDER BY comments_count DESC;

두 번째 쿼리가 성능적으로 더 유리.

불필요한 데이터를 빼고 상위 20명만 조인함.

최적의 쿼리를 작성하려면 필요한 데이터만 먼저 필터링하고, 이후에 조인하는 방식이 일반적으로 가장 성능이 좋다.

 

WHERE 절 사용

쿼리가 반환하는 데이터 양을 제한하려면 WHERE 절 사용

STRING 또는 BYTE 보다 BOOL, INT, FLOAT, DATE이 빠르다.

가능한 경우 이러한 데이터 유형 중 하나를 사용하는 열을 사용하여 쿼리에서 반환되는 데이터 양을 줄이자

 

반복적인 변환 수행

SELECT 
    id,
    SUBSTRING_INDEX(email, '@', 1) AS username
FROM users;

🔽

 

ALTER TABLE users ADD COLUMN username VARCHAR(255);

UPDATE users 
SET username = SUBSTRING_INDEX(email, '@', 1);

SELECT id, username FROM users;

 

 

동일한 CTE에 대한 여러 평가 방지

CTE는 With 구문을 사용해서 임시적인 테이블을 생성하는 기능

하지만 일부 DBMS에서는 CTE 쿼리에서 여러 번 참조될 경우, 매번 재평가될 수 있다.

 

해결방법: 임시 테이블 또는 변수를 사용하여 CTE 결과를 저장하면 불 필요한 연산을 방지 가능하다.

한 번 계산한 값은 재사용하는 것이 최적화의 핵심