面接: 1日1000万件トランザクション履歴テーブルの設計

問題¶

1日1000万件のトランザクション履歴テーブルを設計せよ。何を考慮するか？

規模の計算¶

1日 1,000万件 = 約116 writes/sec（平均）
ピーク時（例: 平均の5倍）= 580 writes/sec
1件 ≈ 200バイトと仮定
1日のデータ量 = 1,000万 × 200byte = 2GB/日
1年 = 730GB ≈ 1TB弱

テーブル設計¶

CREATE TABLE transactions (
    id          BIGINT          NOT NULL,  -- 後述のシャードキー
    user_id     BIGINT          NOT NULL,
    amount      DECIMAL(18, 2)  NOT NULL,
    currency    CHAR(3)         NOT NULL,
    status      VARCHAR(20)     NOT NULL,  -- pending, completed, failed
    created_at  TIMESTAMP       NOT NULL DEFAULT NOW(),
    metadata    JSONB,                     -- 拡張用
    PRIMARY KEY (id, created_at)           -- パーティションキーに日付を含める
) PARTITION BY RANGE (created_at);         -- 日付でパーティション

-- 月ごとのパーティション
CREATE TABLE transactions_2026_05
    PARTITION OF transactions
    FOR VALUES FROM ('2026-05-01') TO ('2026-06-01');

パーティショニング¶

なぜパーティションが必要か¶

1年で数億件のデータに対してインデックスを使っても、テーブルが巨大すぎるとB-treeのサイズが膨らみクエリが遅くなる。パーティションで物理的にデータを分割すると、古い月のパーティションをスキャン対象から外せる（パーティションプルーニング）。

-- 2026年5月のデータだけをスキャン（他のパーティションは無視）
SELECT * FROM transactions
WHERE user_id = 42
  AND created_at BETWEEN '2026-05-01' AND '2026-05-31';

古いデータのアーカイブ¶

-- 1年以上前のパーティションをアーカイブに移動
ALTER TABLE transactions DETACH PARTITION transactions_2025_01;
-- → S3やBigQueryに移してコスト削減

インデックス設計¶

-- ユーザーの取引履歴を新しい順に取得するクエリが多い
CREATE INDEX idx_transactions_user_created
    ON transactions (user_id, created_at DESC);

-- ステータスで絞り込む場合（pending件数の監視など）
CREATE INDEX idx_transactions_status
    ON transactions (status, created_at DESC)
    WHERE status IN ('pending', 'failed');  -- 部分インデックス

注意: インデックスを増やすほど書き込みが遅くなる。ユースケースを確認してから追加する。

ID設計: SnowflakeID¶

AUTO_INCREMENT の整数IDは分散環境でシャーディングすると衝突する。 SnowflakeID（Twitter考案）を使う。

63 bit = タイムスタンプ(41) + データセンターID(5) + ワーカーID(5) + シーケンス(12)

時系列ソートが可能（IDで並び替えるとcreated_at順になる）
分散生成でも衝突しない
1ミリ秒に4096件まで生成可能

シャーディング（さらにスケールする場合）¶

書き込みが単一DBのキャパシティを超えたらシャーディング。

user_id % 4 でシャード分割
Shard 0: user_id が 0, 4, 8, ...
Shard 1: user_id が 1, 5, 9, ...
...

1ユーザーの全履歴が同一シャードに収まるので、ユーザー別クエリが1シャードで完結する。

面接でのポイント¶

まず計算: writes/sec、データ量/年を出す
パーティショニング: なぜ必要か、日付 or ユーザーIDで分けるか
インデックス: 読み取りパターンを聞いてから設計する
ID設計: AUTO_INCREMENTの問題とSnowflakeIDを知っている
アーカイブ戦略: 古いデータをどうするか（コスト意識）
書き込みvs読み取り: 履歴テーブルは書き込みが多いが、ユーザーが見るのは直近のデータが多い → インデックス戦略が変わる