쏚쏚스포이드

MySQL의 시간 표현형식인 DATETIME, TIMESTAMP
두 표현형식에 대해서 알아보자

* 해당 글에서 말하는 local time이란 서버컴퓨터에 설정되어 있는 날짜 기준 시각을 의미

TIMESTAMP

• 저장 용량 : 4Byte
• 저장 범위 : 1970-01-01 ~ 2038-01-19
• 시간대 영향 : 서버 Timezone에 따라 변환
• 내부 저장 방식 : UNIX 시간(초) 기반
• 주의 사항 : 2038년 문제, timezone 혼동

특징

자동 시간대 관리

저장 : 시간 저장할 때 서버의 timezone → UTC로 변환되어 저장

조회 : 저장된 UTC → 현재 세션/서버의 timezone 기준으로 보여줌

장점

글로벌 서비스에서 사용자 시간대별로 시간 값을 표시할 때, TIMESTAMP는 자동으로 변환되므로 별도의 시간대 계산 로직 없이도 사용자에게 맞는 현지 시간을 보여줄 수 있다.

2038년 문제

TIMESTAMP는 4바이트 날짜 표현 데이터로써 초 단위로 저장하기에, 표현 가능한 범위가 -2147483548 ~ 2147483647이다. 해당 값의 최대값이 2038-01-19 03:14:07 UTC 이기에, 이후 시간 표현에 대해서 문제가 생긴다.

timezone 혼동

• 서버로 요청하는 클라이언트의 local time이 변경되었을 경우

  • ex) 2025-05-05 10:00:00 → 2025-05-05 01:00:00

  • Asia/Seoul UTC +9* 타임존을 사용하고 있는 서버에서 서버의 TIMEZONE 설정을 UTC로 변경

    ⇒ 기존에 +9시간으로 나오던 시간이 +9가 안된 채로 보여줌

• 같은 TIMESTAMP 값을 서로 다른 환경에서 비교할 때 오류

  • ex) A 서버 (Asia/Seoul)와 B 서버 (UTC)가 서로 TIMESTAMP 데이터를 주고받는 상황
  • A 서버 : 2025-05-05 12:00:00 UTC +9
  • B 서버 : 2025-05-05 03:00:00 UTC

DATETIME

• 저장 용량 : 8Byte
• 저장 범위 : 1000-01-01 ~ 9999-12-31
• 시간대 영향 : 입력된 값 그대로 저장
• 내부 저장 방식 : 구조화된 날짜.시간 필드
• 주의 사항 : UTC 저장이 필요하면 직접 처리해야함

특징

8바이트 구조화된 방식으로 날짜·시간 정보를 별도 필드에 저장하며, 값 변환 없이 그대로 저장된다. 예: 9999-12-31과 같은 값도 안정적으로 표현 가능 (약 47비트 수준이지만 여유 있게 8바이트 확보)

장점

• 서버 환경, 클라이언트 지역, 타임존 설정과 무관하게 항상 일정한 값 유지
• TIMESTAMP와 다르게 8바이트의 넓은 범위 저장 가능
• 타임존에 영향받지 않으며, 타임존 로직은 애플리케이션에서 명시적으로 제어 가능

기준 시간 지정 시 별도의 로직 필요

글로벌 서비스에서 DATETIME을 사용할 경우, 사용자 시간대에 맞게 표시하려면 애플리케이션에서 명시적인 기준 시간(예: UTC) 변환 로직이 필요하다.

이번에 쿼리문 성능개선 업무를 진행하며 판단한 기준을 정리
MySQL + INNO DB 스토리지 엔진 기준입니다. 다른 데이터베이스는 조금 다를 수 있습니다

//이번 예시인 example_table

CREATE TABLE example_table (
    id INT PRIMARY KEY,
    column1 INT,
    column2 INT,
    grade VARCHAR(255),
    score INT
);

인덱스를 걸지 않고도 성능 개선이 가능한 경우

특정 상황에서는 INDEX가 없어도 쿼리문의 변형만으로 성능개선이 가능하다.

EXPLAIN SELECT * FROM example_table WHERE grade = 'A' AND score = 30;

=>

+----+-------------+---------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table         | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | example_table | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  4   | 25.00    | Using where |
+----+-------------+---------------+------------+------+---------------+------+---------+------+------+----------+-------------+

type : ALL
=> Table Full Scan으로 해당 테이블 전체 데이터 스캔

• ‘AND’ 를 활용한 다중 WHERE절일 때
  • WHERE를 적용했을 때 고정적으로 더 적은 개수의 컬럼이 나오는 조건이 존재하는지
    • 위의 경우 학점과 점수의 연관 관계를 생각했을 때 score의 조건이 grade보다 더 적은 수의 컬럼으로 나오는게 명확

      example_table
      id	column1	column2	grade	score
      1	1	1	a	96
      2	1	1	a	97
      3	1	1	a	98
      4	1	1	a	99
      5	1	1	a	99
      6	1	1	b	92
      7	1	1	b	91
      8	1	1	b	90

  • 위의 조건에 맞는 컬럼(score)을 WHERE절의 앞에 위치 시킨다
    SELECT * FROM example_table WHERE score = 30 AND grade = 'A';

⇒ 물론 명확하지 않은 조건일 때는 해당 where절이 반드시 성능 향상을 시킬 것이라고 기대할 수 없으며, 인덱스를 활용하지 못할 때만 사용할 수 있는 방법

인덱스를 활용해 성능 개선

인덱스의 특징

• 인덱스는 해당 테이블의 빠른 조회를 위한 컬럼의 관련 데이터주소가 일방적으로 담긴 테이블이다
• 인덱스를 생성했을 때 기본적으로 정렬되어 있기에 이후 트리(B+Tree)탐색을 진행한다.
• 인덱스는 생성된 테이블에 대하여 기본키에 대한 인덱스가 하나 생성(예외인 경우 간혹 있음)되며, 추가로 인덱스 컬럼을 선정해 걸어둘 수 있다.

인덱스를 사용할 때 주의점

• 인덱스는 기존의 테이블에서 추가적으로 테이블을 생성하는 것이기 때문에 남발해서는 안됨
  • insert시 부하가 많이 올라감
• 인덱스가 복합적으로 있을 때는 DB가 자체적으로 판단해 여러 인덱스를 활용해 쿼리의 실행을 돕는다
  • 단, 쿼리의 실행 계획이 반드시 옳고 빠른 실행이 아닐 수 있음
• 인덱스 기능은 조회 에만 성능 향상 효과를 볼 수 있으며 삽입, 삭제, 갱신 시에 기존의 테이블 수정 + 인덱스 테이블 수정의 상황 때문에 조회 외의 작업도 빈번하게 일어난다면 전반적인 성능이 하락할 수도 있다

단일 컬럼 인덱스

• 하나의 컬럼만을 선택해 인덱스 테이블 생성
• 위의 WHERE절에서 이야기했던 것과 동일하게 선택성이 좋은 컬럼을 사용하는 것
  ex) grade가 아닌 score

다중 컬럼 인덱스

• 조건이 여러개 들어있는 조회문일 경우 단일 컬럼 인덱스보다 좋은 효율을 낼 수 있음
• 조회시 다중컬럼 인덱스에 없는 컬럼도 조회한다면 인덱스 테이블이 아닌 실제 테이블도 접근해야하기에 고려해서 설정해야 한다.
• 컬럼 순서가 중요
  • 선택성이 좋은 컬럼을 앞으로 구성
  • 범위 조건 컬럼보다 등치(=) 컬럼을 앞으로 구성
• 한개의 인덱스로 여러 sql을 커버할 수 있도록 설계하면 좋다
• 모든 조건을 인덱스로 구성하기엔 어려움이 있으니, 조건별로 카운트 해보고 적은 건수의 데이터 위주로 생성이 유리

ETC

쿼리를 실행하며 테스트 할 때는 캐시로 인해 명확한 판단이 힘들어질 수 있으므로 반드시 버퍼 캐시를 비워야함