오래된 데이터 삭제 배치로 인한 주문 지연 문제

CREATE TABLE `orders` (
  `id` bigint unsigned NOT NULL DEFAULT '0',
  `user_id` int DEFAULT NULL,
  `order_status` varchar(30) NOT NULL DEFAULT 'CREATED',
  `is_paid` enum('Y','N') NOT NULL DEFAULT 'N',
  `is_cancelled` enum('Y','N') NOT NULL DEFAULT 'N',
  `is_delivered` enum('Y','N') NOT NULL DEFAULT 'N',
  `order_date` datetime DEFAULT NULL,
  `price` bigint DEFAULT '0',
  `amount` bigint DEFAULT '0',
  `sum_price` bigint GENERATED ALWAYS AS ((`price` * `amount`)) STORED,
  PRIMARY KEY (`id`),
  KEY `idx_status_date` (`order_status`,`order_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

 

WHERE order_status = ? AND order_date between A and B

쿼리 패턴 상 order_status 조건이 항상 포함되어
KEY `idx_status_date` (`order_status`,`order_date`)
누군가 이렇게 인덱스를 잡아놨고 그동안 아무 문제가 없었음

 
그러나, 매일 새벽에 오래된 주문을 삭제하는 배치가 돌 때, 주문이 느리다는 컴플레인이 접수됨

explain delete from orders where order_date < '2024-12-26';

 

 
배치는 약 20초 걸리고

 
새로운 주문 INSERT문은 19초 정도 걸렸음
 
 
1. 쿼리에서 order_status 하드 코딩 금지, status 케이스는 얼마든지 추가될 수 있어 하드 코딩은 안됨
 
2. 인덱스 구성은 변경 불가능, 쿼리 +어플리케이션 로직만 변경 가능함