UTC와 KST의 전쟁: Stored Procedure로 인덱스 살린 이야기

분석 쿼리 한 줄 때문에 운영 DB CPU가 천장을 치는 경험, 다들 한 번씩 있을 거다.

우리도 있었다. 그리고 그 원인이 의외로 단순했다. 시간대 변환.

UTC로 저장된 데이터를 KST 기준으로 집계하려고 했을 뿐인데, 그게 인덱스를 완전히 무력화했다. 풀스캔이 일어났고, 분석 쿼리 한 번에 운영 DB가 흔들렸다. 이번 글은 이걸 어떻게 풀었는지에 대한 정리.

발단: UTC 저장, KST 집계

우리 운영 DB는 모든 timestamp를 UTC로 저장한다. 글로벌 서비스를 고려해 일관된 시간대를 쓰는 건 표준이고, 우리도 그렇게 하고 있었다.

근데 분석 요구사항은 KST 기준이다. "5월 5일 KST 하루치 메시지 수 집계해줘" 같은 요청.

처음엔 단순하게 풀었다.

SELECT COUNT(*) FROM messages WHERE DATE_FORMAT(createdAt + INTERVAL 9 HOUR, '%Y-%m-%d') = '2024-05-05';

createdAt은 UTC. KST로 변환(9시간 더하기)하고, DATE_FORMAT으로 날짜 부분만 뽑아서 비교. 결과는 맞다.

근데 EXPLAIN을 보면 type이 ALL이다. 풀스캔.

왜 인덱스가 안 먹는가

createdAt에 인덱스를 걸어놨는데도 안 먹는다. 이유는 간단하다.

컬럼에 함수가 적용된 순간, 그 컬럼에 걸린 일반 인덱스는 사용되지 않는다.

이걸 흔히 "SARGable 쿼리가 아니다"라고 부른다. SARGable은 Search ARGument able의 줄임말. 인덱스가 활용 가능한 형태로 쿼리가 작성됐는지를 가리키는 용어다.

createdAt = '2024-05-05'는 SARGable. 인덱스 잘 탄다.

DATE_FORMAT(createdAt + ..., '%Y-%m-%d') = '2024-05-05'는 SARGable이 아니다. 함수가 적용된 값을 비교해야 하니까 모든 행을 다 보고 함수 결과를 계산해야 한다.

첫 번째 대안: 범위 쿼리

가장 먼저 떠올린 우회법은 범위 쿼리다.

SELECT COUNT(*) FROM messages WHERE createdAt >= '2024-05-04 15:00:00'  -- 5월 5일 KST 00:00
  AND createdAt <  '2024-05-05 15:00:00'; -- 5월 6일 KST 00:00

이건 인덱스 잘 탄다. 풀스캔 안 한다.

근데 운영하면서 짜증나는 게 누적됐다.

• 매번 KST → UTC 시간 계산해야 함 (특히 자정 넘어가는 경계 헷갈림)
• 한국 기준 "5월 5일 하루"가 UTC 기준 "5월 4일 15시 ~ 5월 5일 15시"라는 게 직관적이지 않음
• 쿼리 짜다가 한 시간 잘못 적으면 통계가 완전히 어긋남
• 분석가나 비개발자가 이 쿼리를 보면 더 헷갈림

매일 같은 형태로 반복되는 쿼리인데, 사람이 매번 시간 계산을 해야 한다는 게 좀 비효율적이었다.

두 번째 대안: Generated Column (검토했지만 안 함)

MySQL 5.7부터는 generated column이라는 기능이 있다. 다른 컬럼의 값을 계산해서 자동으로 채워주는 가상 컬럼. 함수 기반 인덱스도 일부 지원한다.

ALTER TABLE messages ADD COLUMN createdAtKST DATETIME GENERATED ALWAYS AS (createdAt + INTERVAL 9 HOUR) STORED;

CREATE INDEX idx_messages_createdAtKST ON messages(createdAtKST);

이론적으론 깔끔하다. 근데 우리는 안 썼다. 이유:

• 운영 테이블에 영향: messages는 비즈니스 핵심 테이블이다. 거기에 컬럼 추가하는 건 신중해야 한다. 마이그레이션 락도 걸리고.
• 유스케이스가 분석 전용: 비즈니스 로직은 UTC만 쓴다. 분석용 컬럼을 비즈니스 테이블에 박는 게 책임 분리 측면에서 어색했다.
• STORED vs VIRTUAL 트레이드오프: STORED면 디스크 공간 증가, VIRTUAL이면 인덱스 일부 한계.

우리의 선택: 분석 DB에 Stored Procedure로 컬럼 자동 추가

우리는 운영 DB와 분석 DB가 분리된 환경이라는 걸 활용했다 (이전 글의 CDC 파이프라인 결과). 분석 DB로 적재할 때 KST 변환 컬럼을 같이 만들면 된다.

DELIMITER //
CREATE PROCEDURE add_kst_columns_to_messages_mart()
BEGIN
  ALTER TABLE messages_mart
    ADD COLUMN createdAtKST DATETIME AS (DATE_ADD(createdAt, INTERVAL 9 HOUR)) STORED,
    ADD COLUMN createdDateKST DATE AS (DATE(DATE_ADD(createdAt, INTERVAL 9 HOUR))) STORED;

  CREATE INDEX idx_messages_mart_kst_dt ON messages_mart(createdAtKST);
  CREATE INDEX idx_messages_mart_kst_date ON messages_mart(createdDateKST);
END //
DELIMITER ;

(실제 우리 환경은 좀 더 복잡한데 컨셉만 보여주기 위해 단순화)

이제 분석 쿼리는 깔끔해진다.

SELECT COUNT(*) FROM messages_mart WHERE createdDateKST = '2024-05-05';

EXPLAIN 보면 인덱스 잘 탄다. 풀스캔 안 한다. 응답 시간 수초 → 수십 밀리초.

왜 이게 좋은가

이 구조의 장점은 운영 환경의 책임과 분석 환경의 책임이 깔끔하게 분리된다는 점이다.

• 운영 DB: 비즈니스 로직만 신경. UTC만 다룸. 컬럼 추가 없음.
• 분석 DB: 분석 친화적 구조. KST 컬럼 미리 계산되어 있음. 인덱스 다 걸려있음.

운영 DB에 변경을 안 가했기 때문에 비즈니스 사이드에서 신경 쓸 게 없다. 분석 DB는 어차피 동기화 과정에서 다 만들어지는 거라 추가 운영 부담도 거의 없다.

알아둘 점

• dateTime 컬럼만 추가하지 말고 date 컬럼도 같이. 일별 집계가 가장 많다면 yyyy-mm-dd 단위로만 잘라둔 컬럼이 인덱스 효율적이다.
• STORED 선택의 비용. 디스크 공간이 약간 늘어난다. 분석 DB는 어차피 비용 신경 안 쓰는 환경이라 우리는 OK였다.
• 다른 시간대 추가 요구. "이제 일본 시간 기준도 필요해요"라는 요구가 오면? 컬럼 더 추가하면 된다. 비즈니스 로직과 분리되어 있으니 부담 없다.

정리

분석 쿼리 인덱스 문제는 결국 "함수가 들어가면 인덱스 못 탄다"는 SQL 기초 원리로 귀결된다. 그걸 우회하는 방법이 여러 개 있는데, 우리는 "분석 DB에 미리 계산된 컬럼을 두자"를 택했다.

화려한 기술은 아니다. 다만 운영 환경에 영향 없이 분석 응답 시간을 수초에서 수십 밀리초로 줄였다. 결국 인덱스 잘 타게 만드는 게 성능 최적화의 큰 부분을 차지한다는 걸 이번에도 느꼈다.

CDC 파이프라인 시리즈 글은 일단 여기까지. 운영에서 부딪힌 다른 케이스들 더 정리되면 또 써보겠다.