접속 로그를 날짜로 잘라 내려받기 — 운영 감사 데이터 추출

운영자가 “이번 달 접속 내역”을 버튼 하나로 내려받는 기능은 감사(audit)의 기본이다. 작업 자체는 단순한 SELECT지만, 로그 테이블은 가장 빠르게 커지는 테이블이고 추출 쿼리는 가장 무거운 쿼리가 되기 쉽다. 핵심은 세 가지다 — 날짜 범위를 인덱스가 타게 거는 법, 제외 필터를 안전하게 거는 법, 운영 DB를 보호하는 법.

날짜 범위는 인덱스를 타야 한다

가장 흔한 실수는 날짜 컬럼을 함수로 감싸는 것이다.

-- 인덱스 무력화: created_at에 함수가 걸려 풀스캔
SELECT * FROM access_log
WHERE DATE(created_at) BETWEEN '2022-10-01' AND '2022-10-08';

DATE(created_at)는 모든 행을 계산해야 비교가 되므로 인덱스를 못 탄다. 컬럼은 가공하지 말고, 경계는 반열린 구간(>= 시작, < 다음날)으로 잡는다.

SELECT user_id, ip, created_at
FROM access_log
WHERE created_at >= '2022-10-01 00:00:00'
  AND created_at <  '2022-10-09 00:00:00';   -- 끝날 +1, 미만

BETWEEN ... '2022-10-08'는 그날 00:00:00까지만 포함해 하루치가 새므로, 추출용에선 반열린 구간이 안전하다. 이 패턴이라야 created_at 인덱스의 범위 스캔이 동작한다.

제외 필터와 인덱스 선두 컬럼

운영 추출에선 내부 계정·테스트 IP를 빼는 제외 조건이 흔하다.

WHERE created_at >= ? AND created_at < ?
  AND user_id NOT IN (SELECT user_id FROM internal_account)
  AND ip <> '127.0.0.1'

여기서 인덱스 설계의 핵심: 로그 테이블의 인덱스는 시간 컬럼을 선두에 둔다. 추출·통계·정리 쿼리가 전부 시간 범위로 출발하기 때문이다. (created_at) 단일 인덱스만으로도 범위 스캔이 되고, 자주 함께 거는 조건이 있으면 (created_at, user_id) 복합으로 확장한다. 반대로 선두를 user_id로 잡으면 기간 추출이 인덱스를 못 탄다.

NOT IN 제외는 대상이 많으면 부담이므로, 내부 계정이 소수면 LEFT JOIN ... WHERE x.id IS NULL 안티조인으로 바꾸는 편이 안정적이다.

운영 DB에 주는 부하 — 분리 전략

한 달치 로그를 한 번에 긁으면 수백만 행을 읽고, 그 사이 버퍼풀이 로그 데이터로 오염되어 다른 운영 쿼리가 느려진다. 추출은 다음 중 하나로 운영 트래픽과 분리한다.

• 읽기 복제본(replica)에서 추출 — 운영 마스터의 부하를 건드리지 않는다.
• 청크 분할 — 일자 단위로 끊어 여러 번 읽고 스트리밍으로 내려준다(한 트랜잭션·한 ResultSet에 다 담지 않는다).
• readOnly 트랜잭션 — 추출은 쓰기가 없으므로 읽기 전용으로 열어 불필요한 락·플러시를 피한다.

핵심 요약

• 날짜 컬럼은 함수로 감싸지 말고, 경계는 반열린 구간(>=, <)으로.
• 로그 테이블 인덱스는 시간 컬럼을 선두에 둔다.
• 대량 추출은 복제본/청크/readOnly로 운영 트래픽과 분리한다.

면접 한 줄: “로그 추출 쿼리가 인덱스를 안 타는 흔한 원인은?” → DATE()·DATE_FORMAT() 등 컬럼에 함수를 씌워 sargable 조건이 깨진 경우.