ログシステム設計に関する面接問題

逆順ページネーションを用いたログ一覧ページの実装方法

目標：「発生時刻で逆順に表示し、スクロールでページ遷移（SNS風）して最新の50件を取得する」ログシステムの設計を実現する。

要件概要

  • 各ログは発生時刻（タイムスタンプ）と内容（最大500文字）を持つ。
  • 最新の50件を表示し、下方向へのスクロールによるページ遷移をサポートする。
  • ページ遷移時に重複や漏れがないようにする（特に同一時刻のログや並行書き込みがある場合）。

Q1：ログテーブルの設計方法

CREATE TABLE log_entry (
    log_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '自動増分プライマリキー、並列処理時のタイブレイカーとして使用',
    event_time DATETIME(3) NOT NULL COMMENT 'イベント発生日時、ミリ秒精度のDATETIME(3)を推奨',
    message VARCHAR(500) NOT NULL COMMENT 'ログメッセージ（最大500文字）',
    INDEX idx_time_id (event_time, log_id) -- 時間とIDの組み合わせインデックス（時間重複時のソート対応）
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

設計ポイント

  • log_id（自動増分プライマリキー）：event_timeは一意保証されないため、並列処理時のタイブレイカーとして使用。時間が同じ場合に二次的な識別子として機能（重複回避の鍵）。
  • event_time DATETIME(3)：ミリ秒精度の日時型を使用することで、同一秒でのログ衝突確率を低減。正確なソートに適した設計。
  • 複合インデックス (event_time, log_id)：ORDER BY event_time DESC, log_id DESCおよびカーソルベースのクエリをサポートし、インデックス走査が可能。
  • messageフィールドはインデックスに含めない（インデックス膨張を防ぐため）。短いメッセージが必要な場合のみ考慮。

Q2：フロントエンドから送信するパラメータ

カーソルベース（cursor-based / keyset）ページネーションを採用。フロントエンドは前回取得した最後のログの位置（カーソル）を渡す必要がある：

  • last_time（nullable）：前回取得した最後のログのevent_time、ISO8601形式またはYYYY-MM-DD HH:MM:SS[.sss]
  • last_id（nullable）：前回取得した最後のログのlog_id
  • page_size（optional、default 50）：ページサイズ

ルール：

  • 最初のページ：last_time、last_idはnullまたは送信しない
  • 次のページへ：last_time + last_idを送信し、より古いレコードを取得

提案：バックエンドは不透明なカーソルトークンを返却（last_timeとlast_idをJSONエンコードしてbase64エンコード）。フロントエンドはこのトークンを返すだけでよい。これにより内部実装を隠蔽可能。

例（最初のページ）：

GET /api/logs?page_size=50

例（次のページ）：

GET /api/logs?last_time=2025-08-30T10:00:00.123Z&last_id=12345&page_size=50

または

GET /api/logs?cursor=eyJsYXN0X3RpbWUiOiAiMjAyNS0wOC0zMFQxMDowMDA6MDAuMTIzWiIsICJsYXN0X2lkIjogMTIzNDV9

Q3：バックエンドの処理ロジックとSQL

event_timeとlog_idの複合ソート（event_time DESC, log_id DESC）とカーソルベースのWHERE条件を使用。OFFSETの使用を避ける。

1) 最初のページ（カーソルなし）

SELECT log_id, event_time, message
FROM log_entry
ORDER BY event_time DESC, log_id DESC
LIMIT ?; -- 例: 50

2) 次のページ（カーソルあり：last_time、last_id）

クエリ条件：カーソル以降（より古い）のレコードを取得。論理的には「前回の最後のレコードより厳密に小さい順序位置」を取得。

SQL（パラメータ化）：

SELECT log_id, event_time, message
FROM log_entry
WHERE (event_time < ?)
   OR (event_time = ? AND log_id < ?)
ORDER BY event_time DESC, log_id DESC
LIMIT ?;

パラメータ順序例：(last_time, last_time, last_id, page_size)

注意：<演算子を使用し、前回の最後のレコードを再取得しないようにする。

なぜこれで時間重複を正しく処理できるのか？

複数のレコードがevent_timeが同じ場合、log_idを二次ソート条件としてAND log_id < ?の条件を加えることで、前回の最後のレコード以降（より古い）のレコードを正確にフィルタリング。重複や漏れがない。

例

以下のようなデータ（event_time DESC, log_id DESCの順序）：

log_id event_time message
100 2025-08-30 15:30:00.500 X
99 2025-08-30 15:30:00.500 Y
98 2025-08-30 15:29:59.200 Z
97 2025-08-30 15:29:58.000 W
... ... ...
  • 最初のリクエスト（カーソルなし）は前3件を返す：100(X), 99(Y), 98(Z)。カーソルは(last_time=2025-08-30 15:29:59.200, last_id=98)。
  • 次のリクエストのWHERE条件：
WHERE (event_time < '2025-08-30 15:29:59.200')
   OR (event_time = '2025-08-30 15:29:59.200' AND log_id < 98)

この条件でlog_id=97以下のレコードを取得。正しく重複せず取得可能。

なぜOFFSET（LIMIT 50 OFFSET n）を使わないのか？

  • ログのような継続的なデータ書き込みに対して、OFFSETは大きなオフセット値で非常に遅くなる。データベースはOFFSET分だけスキャンし、結果を捨てる必要がある。オフセット値が大きくなるにつれてコストが線形的に増加。
  • OFFSETは並行書き込みによって新しいデータが挿入された場合、ページネーションが不安定になる（重複や漏れが発生）。
  • **カーソルベースページネーション（Keyset Pagination）**は大規模リストのページネーションに最適な方法で、クエリが安定し、性能が良く、遅延も少ない。

エンジニアリングにおける補足と注意点

  1. 時間精度
  • ビジネスで1秒あたり大量のログが生成される場合は：
  • DATETIME(3) / DATETIME(6)またはBIGINTでミリ秒/マイクロ秒のタイムスタンプ（epoch_ms）を保存することを推奨。
  • 精度を高めることで、同じevent_timeのレコード数を減らす。ただし、log_idをタイブレイカーとして使用する必要がある。
  1. インデックス設計の詳細
  • 複合インデックス：(event_time, log_id)を作成。MySQL 8では明示的な降順インデックス(event_time DESC, log_id DESC)をサポートしているが、通常(event_time, log_id)でも十分。
  • messageフィールドをインデックスに含めない（長すぎるため）。時間範囲での純粋なクエリでmessageを取得する必要がある場合は、インデックスからlog_idを取得し、主テーブルから取得する（回表は正常）。
  1. 並行書き込みとリアルタイム性
  • ユーザーがスクロールしてページを遷移する際、バックエンドはより古いレコードを返す。新しいレコード（発生時刻が大きい）は後続のリクエストで返されない。これはユーザーの期待に合致（新しいデータは上部に表示される）。
  • ユーザーがページをスクロール中に新しいレコードを見たい場合は、フロントエンドで定期的に上部を更新するか、「N件の新しいレコードがあります。読み込む」などのインタラクションを提供する必要がある。
  1. 削除/更新の影響
  • 削除は、後続のページに「存在すべきが削除された」空きを生じるが、重複は起こらない。
  • event_timeの更新は、ソート順序を変更するため、ページネーションの体験が不安定になる可能性がある。
  1. 分散環境/複数書き込みノードでの注意点
  • ハイバースケーリングや複数書き込みノード環境では、AUTO_INCREMENTの単調性が破壊される可能性がある（ノードごとの自動増分間隔、または異なるデータベースインスタンスのlog_idが連続しない）。分散システムの場合：
  • グローバルに単調増加するシーケンス（データベース中央シーケンスやSnowflakeタイプのID）を使用し、log_idを信頼できるタイブレイカーとして使用。
  • または、event_timeの精度を高め、node_id/seqを組み合わせて複合ソートキーとして使用。
  1. 内部log_idを公開したくない場合の対処

カーソルをbase64エンコードされたJSON（例：{"last_time":"...","last_id":123}）としてエンカプセル化し、フロントエンドはcursor=...を送信。バックエンドはこのカーソルをデコード・解析する。これにより、log_idを直接公開しない。

例：完全なAPIとバックエンド疑似コード

REST API設計（簡潔）

  • GET /api/logs?page_size=50&cursor=<optional>
  • 戻り値：
{
  "items": [
    {"log_id":100,"event_time":"2025-08-30T15:30:00.500Z","message":"X"},
    {"log_id":99,"event_time":"2025-08-30T15:30:00.500Z","message":"Y"}
  ],
  "next_cursor": "eyJsYXN0X3RpbWUiOiAiMjAyNS0wOC0zMFQxNTozMDowMC41MDBaIiwgImxhc3RfaWQiOjg4fQ=="
}

バックエンド疑似コード（Java + JDBCスタイル）

// カーソルをデコード、または最初のページであればlastEventTime=null、lastLogId=nullに設定
if (lastEventTime == null) {
    sql = "SELECT log_id, event_time, message FROM log_entry ORDER BY event_time DESC, log_id DESC LIMIT ?";
    ps = conn.prepareStatement(sql);
    ps.setInt(1, pageSize);
} else {
    sql = "SELECT log_id, event_time, message FROM log_entry " +
          "WHERE (event_time < ?) OR (event_time = ? AND log_id < ?) " +
          "ORDER BY event_time DESC, log_id DESC LIMIT ?";
    ps = conn.prepareStatement(sql);
    ps.setTimestamp(1, lastEventTime);
    ps.setTimestamp(2, lastEventTime);
    ps.setLong(3, lastLogId);
    ps.setInt(4, pageSize);
}
ResultSet rs = ps.executeQuery();
// 結果を収集し、next_cursorを最後に取得された行から構築

フロントエンド（ブラウザfetch）例（簡略）

async function fetchLogs(cursor, pageSize = 50) {
  const params = new URLSearchParams();
  if (cursor) params.set('cursor', cursor);
  params.set('page_size', pageSize);
  const res = await fetch(`/api/logs?${params.toString()}`);
  return await res.json();
}

面接質問

  1. timeのみでページネーション（idを用いない）するとどのような問題が起きる？ 答え：複数のログが同じ時間を持つ場合、ページネーション時に重複や漏れが発生する。なぜなら、前回の最後のレコードのソート位置を一意に特定できないから。
  2. OFFSETは大規模なデータ量で不適切な理由は？ 答え：OFFSETはデータベースにOFFSET分だけスキャンし、結果を捨てる必要がある。オフセット値が大きくなるとコストが線形的に増加し、パフォーマンスが低下。並行書き込みがあると、重複や漏れが発生する。
  3. 内部のlog_idを隠蔽したい場合、カーソルをどのように設計すべきか？ 答え：(last_time, last_id)をJSON形式でシリアライズし、base64エンコードまたは署名。フロントエンドはこのcursor文字列を返すだけでよい。バックエンドはこのcursorをデコードし、クエリに使用する。

まとめ

  • 正しい方法：event_time（高精度）+ log_id（自動増分またはグローバル単調ID）を複合ソートキーとして使用し、カーソルベースのクエリWHERE (time < ?) OR (time = ? AND id < ?)を実行し、ORDER BY time DESC, id DESC LIMIT N。
  • なぜこうするか：安定したクエリ、重複や漏れがなく、OFFSETより性能が優れている（インデックス走査可能）。
  • エンジニアリングの提案：複合インデックス、適切な時間精度、不透明なカーソルの使用、トラフィックに応じたパーティショニング/ホット・コールド分離、必要に応じて専用ログエンジン（ES/ClickHouse）導入。

タグ: MySQL カーソルベースページネーション sql最適化 ログシステム設計 インデックス設計

6月24日 01:11 投稿

ホットタグ

©2026 異端開発室