isdnetworks
Go back

볼 때마다 계산할지 미리 계산할지

재고 요약 화면이 느렸다. 상품별로 창고 여러 곳의 재고를 합쳐서 보여 주는 화면인데 들어갈 때마다 3초쯤 걸린다.

Table of contents

Open Table of contents

볼 때마다 계산하고 있었다

SELECT p.product_no, p.name,
       SUM(s.qty) AS total_qty,
       SUM(CASE WHEN s.warehouse_no = 1 THEN s.qty ELSE 0 END) AS wh1,
       SUM(CASE WHEN s.warehouse_no = 2 THEN s.qty ELSE 0 END) AS wh2
FROM product p
LEFT JOIN stock s ON s.product_no = p.product_no
WHERE p.use_yn = 'Y'
GROUP BY p.product_no
ORDER BY p.name

EXPLAIN 을 떠 보니 화면을 열 때마다 GROUP BY product_no 로 상품 4천 개와 재고 행 3만 개를 전부 집계하고 있었다. 화면을 열 때마다 같은 SUM(qty) 를 다시 낸다.

같은 계산을 매번 내고 있다면 한 번만 내고 저장해 둘 수 있다. 다만 그렇게 하면 화면에 나오는 값이 지금 값이 아니게 된다.

얼마나 자주 바뀌나

미리 계산해 둘지 정하려면 두 가지를 봐야 했다. 얼마나 자주 보는지는 access_log 로 세니 하루 200번쯤이었고 담당자 몇 명이 수시로 봤다. 얼마나 자주 바뀌는지는 재고 변동이 하루 300건쯤으로 주문과 입고와 조정이 섞여 있었다.

보는 것과 바뀌는 것이 비슷한 수준이다. 이 비율이 판단을 갈랐다.

상황나은 쪽
보는 게 훨씬 많다미리 계산
바뀌는 게 훨씬 많다볼 때 계산
비슷하다다른 걸 본다

비슷하니 다른 것을 봐야 했다. 결과가 얼마나 최신이어야 하는가다.

재고 요약은 실시간일 필요가 없었다. 정확한 수치는 상세 화면에서 보고 요약은 몇 분 늦어도 된다고 했다.

요약 테이블을 만들었다

CREATE TABLE stock_summary (
  product_no INT NOT NULL,
  total_qty  INT NOT NULL DEFAULT 0,
  wh1_qty    INT NOT NULL DEFAULT 0,
  wh2_qty    INT NOT NULL DEFAULT 0,
  upd_date   DATETIME NOT NULL,
  PRIMARY KEY (product_no)
);

stock_summary 를 5분마다 갱신한다.

REPLACE INTO stock_summary (product_no, total_qty, wh1_qty, wh2_qty, upd_date)
SELECT s.product_no,
       SUM(s.qty),
       SUM(CASE WHEN s.warehouse_no = 1 THEN s.qty ELSE 0 END),
       SUM(CASE WHEN s.warehouse_no = 2 THEN s.qty ELSE 0 END),
       NOW()
FROM stock s
GROUP BY s.product_no;

화면 쿼리는 조인 하나로 끝난다.

SELECT p.product_no, p.name, ss.total_qty, ss.wh1_qty, ss.wh2_qty
FROM product p
LEFT JOIN stock_summary ss ON ss.product_no = p.product_no
WHERE p.use_yn = 'Y'
ORDER BY p.name

GROUP BY 가 사라지고 3초가 0.2초가 됐다.

기준 시각 표시

미리 계산한 값은 최신이 아니다. 그것을 화면에 표시했다.

$last = $this->db->select_max('upd_date')->get('stock_summary')->row();
echo "재고 기준 시각: " . $last->upd_date;

upd_date 가 없으면 담당자가 방금 처리한 입고가 안 보일 때 시스템이 잘못됐다고 생각한다. 기준 시각이 보이면 5분 뒤에 반영되겠구나로 이해한다.

이것을 감추면 사용자가 그 값을 지금 값으로 믿는다. 최신이 아니라는 것을 알면 중요한 판단을 할 때는 상세 화면을 본다.

검증 — 갱신이 멈춘 상태와 재계산 비용

갱신 작업이 죽으면 화면은 정상으로 보이는데 값이 안 바뀐다. 값이 0 이 되거나 비는 것이 아니라 그대로 남아서 조용히 낡는다.

if (strtotime($last->upd_date) < time() - 900) {
    echo '<span class="warn">재고 정보가 15분 이상 갱신되지 않았습니다</span>';
}

upd_date 와 현재 시각의 차이가 15분을 넘으면 화면에 띄운다. 미리 계산하는 구조에는 갱신이 멈춘 상태를 알아채는 장치가 같이 필요했다.

5분마다 3만 행을 전부 집계하는 것도 비용이다. 바뀐 것만 갱신하는 방법도 봤다.

SELECT DISTINCT product_no FROM stock_history
WHERE reg_date >= DATE_SUB(NOW(), INTERVAL 6 MINUTE);

stock_history 에서 최근에 바뀐 상품만 골라 그것만 다시 계산하고 INSERT ... ON DUPLICATE KEY UPDATE 로 넣는다. 5분이 아니라 6분으로 잡은 것은 경계에서 놓치지 않으려는 것이다.

이 방식은 변동 기록이 정확해야 성립한다. 기록 없이 값이 바뀌는 경로가 하나라도 있으면 그 상품은 영원히 안 갱신된다.

그래서 하루 한 번은 전체를 다시 계산하게 두 가지를 같이 뒀다. 전체 재계산을 TRUNCATE 뒤 INSERT ... SELECT 로 하면 그 사이에 화면이 빈 표를 보므로 새 표에 담고 RENAME TABLE 로 바꿔 끼웠다.

정리

Share this post on:
Share this post via WhatsApp Share this post on Facebook Share this post on X Share this post via Telegram Share this post on Pinterest Share this post via email
Previous Post
운영과 시험이 한 서버에 섞여 있었다
Next Post
수수료 계산이 곱셈 두 번이었다