얼마나 두는지를 정하고 나서 지웠다

주문 테이블이 커져서 조회가 느려졌다. 오래된 것을 옮기려는데 얼마나 남겨야 하는지 몰랐다.

Open Table of contents

얼마나 있는지 보고 물어봤다
지우는 것이 아니라 옮기는 것이었다
넣고 나서 지우는 순서
검증 — 옮긴 뒤에 맞는지 봤다
정리

얼마나 있는지 보고 물어봤다

먼저 얼마나 있는지 셌다.

SELECT YEAR(reg_date) y, COUNT(*) FROM orders GROUP BY y;

2012    82,441
2013   214,882
2014   402,118

70만 건이었다. 조회는 대부분 최근 것이었다.

SELECT COUNT(*) FROM orders WHERE reg_date >= DATE_SUB(NOW(), INTERVAL 90 DAY);

104,220

지워도 되는지는 내가 정할 것이 아니라 물어봤다. 답이 여럿이었다.

운영팀    3개월이면 충분하다
회계      5년은 남아 있어야 한다
CS        문의가 오면 2년 전 것도 본다

셋이 달라서 가장 긴 것을 따라야 했다. 짧은 쪽에 맞추면 나중에 필요할 때 되돌릴 방법이 없다.

법으로 정해진 것도 있었다. 전자상거래 관련 기록은 보관 기간이 정해져 있어서 그것도 확인했다.

지우는 것이 아니라 옮기는 것이었다

물어보고 나니 대부분은 지울 것이 아니라 어디에 둘지의 문제였다.

최근 90일    orders          빠르게 조회
90일~5년     orders_archive  가끔 조회
5년 초과     파일로 보관 후 테이블에서 삭제

지우는 것은 5년 뒤이고 그 전에는 옮기기만 한다.

보관 표는 원본을 본떠 만들었다.

CREATE TABLE orders_archive LIKE orders;

이 구문은 컬럼 속성과 인덱스를 그대로 가져온다. 손으로 적으면 타입이나 길이가 미묘하게 달라지고 그러면 옮기는 도중에 값이 잘려 들어간다.

sql_mode 에 STRICT_TRANS_TABLES 가 없으면 그때 오류가 아니라 경고만 난다. 배치로 돌리면 그 경고를 아무도 안 본다.

다만 LIKE 가 전부를 가져오지는 않는다. 매뉴얼에 외래 키 정의는 복사하지 않는다고 적혀 있어서 필요하면 따로 걸어야 했다.

넣고 나서 지우는 순서

옮기는 것을 만들었다.

INSERT INTO orders_archive
SELECT * FROM orders WHERE reg_date < DATE_SUB(NOW(), INTERVAL 90 DAY);

DELETE FROM orders WHERE reg_date < DATE_SUB(NOW(), INTERVAL 90 DAY);

한 번에 하면 InnoDB 가 그만큼의 행을 오래 잠근다. 나눠서 했다.

do {
    $db->query("INSERT INTO orders_archive
                SELECT * FROM orders WHERE reg_date < ? ORDER BY no LIMIT 1000");
    $n = $db->query("DELETE FROM orders WHERE reg_date < ? ORDER BY no LIMIT 1000");
    usleep(200000);
} while ($n > 0);

1,000건씩 옮기고 잠깐 쉰다. 밤에 돌려 며칠에 걸쳐 옮겼다.

순서를 바꾸면 안 된다. 지우고 넣으면 그 사이에 죽었을 때 없어진다.

$db->beginTransaction();
$db->query("INSERT INTO orders_archive SELECT ... LIMIT 1000");
$db->query("DELETE FROM orders WHERE no IN (...)");
$db->commit();

한 트랜잭션에 넣으면 넣기가 실패할 때 지우기도 안 된다.

검증 — 옮긴 뒤에 맞는지 봤다

옮기고 나서 건수를 맞춰 봤다.

SELECT COUNT(*) FROM orders;            -- 104,220
SELECT COUNT(*) FROM orders_archive;    -- 595,221
-- 합 699,441. 옮기기 전 699,441

합이 맞았다. 금액도 맞춰 봤다.

SELECT SUM(paid_amount) FROM orders;
SELECT SUM(paid_amount) FROM orders_archive;

건수만 보면 값이 잘려도 모른다. 합계까지 맞아야 제대로 옮겨진 것이다.

옮기면 옛 주문이 안 보이므로 조회하는 쪽도 고쳤다.

public function find($orderNo) {
    $row = $this->db->row("SELECT * FROM orders WHERE no = ?", [$orderNo]);
    if (!$row) {
        $row = $this->db->row("SELECT * FROM orders_archive WHERE no = ?", [$orderNo]);
    }
    return $row;
}

없으면 보관 쪽을 본다. 부르는 쪽은 어디에 있는지 몰라도 되고 기간이 걸치는 목록은 UNION ALL 로 합쳤다.

정한 것도 적어 뒀다.

주문 데이터 보존 (2014-10)

orders          최근 90일
orders_archive  90일 ~ 5년
5년 초과        연 1회 파일로 내리고 테이블에서 삭제

기준
  운영 조회는 3개월. CS 문의는 2년. 회계·법정 보관 5년.
  가장 긴 것을 따라 5년으로 한다.

매일 새벽 3시에 옮긴다. 옮긴 건수를 로그에 남긴다.

5년치가 쌓이면 보관 표도 커진다.

ALTER TABLE orders_archive ADD INDEX ix_member (member_no, reg_date);

가끔 보는 것이라도 조회 조건에는 인덱스가 필요했다. 넣는 것은 배치가 밤에 하므로 인덱스가 있어도 상관없었다. 자주 넣는 표와 가끔 넣는 표는 인덱스 판단이 다르다.

정리

지우기 전에 얼마나 두어야 하는지 물어본다. 부서마다 답이 다르다
법으로 정해진 보관 기간이 있는지 확인한다
대부분은 지우는 것이 아니라 어디에 둘지의 문제다
보관 표는 CREATE TABLE ... LIKE 로 만든다
LIKE 는 인덱스는 가져오지만 외래 키는 안 가져온다
STRICT_TRANS_TABLES 가 없으면 값이 잘려도 경고만 난다
옮길 때는 LIMIT 으로 나눠서 한다. 한 번에 하면 오래 잠근다
넣고 나서 지우고 한 트랜잭션에 묶는다
조회하는 쪽에서 없으면 보관 쪽을 보게 한다
건수와 합계를 둘 다 맞춰 본다
가끔 넣는 표는 인덱스를 더 걸 수 있다