서론
주문 테이블의 데이터가 수백만 건을 넘어가면서 조회 성능이 급격히 저하되기 시작했다.
특히 백오피스에서 제공하는 “오늘 주문”, “이번 달 주문” 과 같은 기간 필터를 사용한 조회 쿼리에서 성능 문제가 두드러지게 나타났다.
문제가 되는 쿼리를 확인해보니 대부분 다음과 같은 패턴을 사용하고 있었다.
WHERE DATE_FORMAT(ORDER_DATETIME, '%Y%m%d') = '20250103'해당 쿼리에 대해 EXPLAIN을 실행해보니, timestamp 컬럼에 인덱스가 존재함에도 불구하고 풀 테이블 스캔이 발생하고 있었다.
“인덱스가 있는데 왜 사용되지 않을까?”라는 의문에서 문제 분석을 시작하게 되었다.
이 글에서는 해당 문제의 원인과, 대용량 주문 테이블에서 날짜 검색 성능을 개선하기 위해 적용한 해결 방법을 정리해보고자 한다.
본론
주문 일자를 기준으로 한 조회 성능 저하의 핵심 원인은 timestamp 컬럼 자체가 아니라, 컬럼에 함수를 적용한 검색 방식에 있었다.
문제가 되었던 쿼리는 다음과 같다.
WHERE DATE_FORMAT(ORDER_DATETIME, '%Y%m%d') = '20250103'이 쿼리는 단순한 일자 비교처럼 보이지만, 내부적으로는 모든 row에 대해 DATE_FORMAT() 함수가 실행된다.
MySQL은 각 row의 ORDER_DATETIME 값을 20250103 형태로 변환한 뒤 비교를 수행하며, 이 과정에서 기존 timestamp 컬럼에 설정된 인덱스는 전혀 활용되지 않는다.
인덱스는 2025-01-03 14:22:10과 같은 원본 값 기준으로 정렬되어 있다.
하지만 컬럼에 함수가 적용되면, 인덱스에 존재하지 않는 새로운 값이 만들어지기 때문에 MySQL은 해당 값이 인덱스의 어느 위치에 있는지 판단할 수 없게 된다.
그 결과, 옵티마이저는 인덱스 사용을 포기하고 테이블 전체를 스캔하게 되며, 풀스캔이 발생한다.
이처럼 컬럼에 함수나 연산이 적용된 조건은 Non-Sargable 하다고 하며,
반대로 컬럼을 있는 그대로 비교하는 조건은 Sargable 하여 이 경우에만 인덱스를 정상적으로 활용할 수 있다.
예를 들어 다음과 같은 조건들은 인덱스를 사용할 수 없다.
WHERE DATE_FORMAT(ORDER_DATETIME, '%Y%m%d') = '20250103'
WHERE YEAR(ORDER_DATETIME) = 2025
WHERE ORDER_DATETIME + INTERVAL 1 DAY > NOW()반면, 컬럼 자체를 범위로 비교하면 인덱스를 사용할 수 있다.
WHERE ORDER_DATETIME
BETWEEN '2025-01-03 00:00:00' AND '2025-01-03 23:59:59'다만 실무 환경에서는 이러한 문자열 기반 범위 조건조차 날짜 경계 계산, 중복 로직, timezone 처리 등의 이유로 관리 비용이 발생한다.
특히 백오피스처럼 반복적인 날짜 검색이 많은 환경에서는, 보다 단순하고 일관된 검색 방식이 필요했다.
이를 해결하기 위해 검색 전용 컬럼을 분리하는 전략을 선택했다.
ORDER_DATETIME timestamp default now() comment '주문일자'ORDER_DATETIME_SEARCH bigint
AS (CAST(DATE_FORMAT(ORDER_DATETIME, '%Y%m%d%H%i%s') AS UNSIGNED))
STORED
COMMENT '주문일자-검색용'ORDER_DATETIME은 실제 주문 시점을 기록하는 원본 데이터로 유지하고,
이를 기반으로 숫자 형태의 ORDER_DATETIME_SEARCH 컬럼을 자동 생성하도록 했다.
이 컬럼은 20250103142210처럼 시간까지 포함한 숫자 형태이기 때문에, 범위 검색과 정렬에 매우 유리하다.
두 컬럼의 역할은 명확히 분리된다.
ORDER_DATETIME
→ 사람이 읽기 쉽고, 시간 계산이나 로그 성격의 데이터에 적합한 원본 컬럼ORDER_DATETIME_SEARCH
→ 검색과 정렬 성능을 극대화하기 위한 순수 인덱스용 컬럼
이 검색용 컬럼은 Generated Column으로 생성했다.
Generated Column은 다른 컬럼 값을 기반으로 자동 계산되는 컬럼으로, 직접 값을 INSERT 또는 UPDATE 할 수 없다.
ORDER_DATETIME 값이 변경되면, 이에 종속된 ORDER_DATETIME_SEARCH 값도 자동으로 갱신된다.
MySQL의 Generated Column에는 VIRTUAL과 STORED 두 가지 방식이 있는데, 나는 STORED 방식을 선택했다.
STORED 컬럼은 디스크에 실제 값이 저장되기 때문에 조회 시마다 계산할 필요가 없으며, 그 결과 CPU 부담을 줄일 수 있다.
또한 B-Tree 인덱스를 완전히 활용할 수 있어 범위 검색과 정렬에서 안정적인 성능을 보장한다.
반대로 VIRTUAL 컬럼은 디스크에 값을 저장하지 않고, 조회 시점마다 정의된 표현식을 계산해 생성된다.
저장 공간을 사용하지 않는다는 장점이 있지만, 조회가 반복되거나 대량 데이터에 대해 조건절·정렬에 사용될 경우 계산 비용이 누적되어 병목이 될 수 있다.
또한 인덱스 활용 측면에서도 STORED 방식보다 제약이 존재한다.
결과적으로 VIRTUAL 컬럼은 계산 비용이 크지 않고 조회 빈도가 낮은 경우에 적합하며,
대량 데이터에서 검색·정렬 성능이 핵심인 주문 테이블과 같은 환경에서는 STORED 방식이 더 안정적인 선택이 된다.
물론 STORED 방식에도 단점은 있다.
bigint 컬럼 하나당 row마다 8 bytes의 저장 공간이 추가된다.
하지만 500만 건 기준으로 약 40MB 수준이며, 이는 검색 성능 개선 효과에 비하면 충분히 감수할 수 있는 비용이었다.
실제로 성능을 비교해보면 차이는 명확하다.
DATE_FORMAT()을 사용한 쿼리는 실행 계획에서 type: ALL로 표시되며, 약 500만 건 전체를 스캔했고 실행 시간은 3초 이상이 소요되었다.
반면 ORDER_DATETIME_SEARCH 컬럼에 범위 조건을 적용한 쿼리는 type: range로 인덱스 범위 스캔을 수행했으며, 실제로 읽는 row 수는 약 15,000건에 불과했다. 실행 시간은 약 0.02초 수준이었다.
결과적으로 약 160배 이상의 성능 차이가 발생 하게 되었다.
아직도 모르는게 너무 많다.. 더 더욱 겸손 해야겠다.
