メインコンテンツまでスキップ

Partition

Overview​

RDBにおけるパーティションについて記載しているセクション。
インデックスなどデータベースにおいて非常に頻繁に使う、使い勝手の良いチューニング手段で遅いクエリを見つけた時はまずインデックスで改善できないかを考える。
しかしデータベースは長い歴史の中で他のチューニング手段も発達させてきた。

パーティション​

パーティションは、テーブルのある列をキーとしてデータの物理配置をキーごとにまとめる機能。
これによってパーティションキーをWHERE条件に指定して検索するクエリの物理的な読み込みデータ量を減らし、読み込み処理を高速化する。

注記

更新処理に対しては特に恩恵はない。

注意

パーティションは、データ配置を物理的に移動させることから一つのテーブルに一つしかパーティションキーは指定できない。
複数のキーでデータ配置を決めるコンポジットパーティションという機能もあるが、その場合もキーは固定する。

パーティションプルーニング​

パーティショニング戦略​

データベースのパーティショニング戦略を検討する際は、以下の点を考慮することが重要。

  • パーティションの種類: レンジ（範囲）、リスト、ハッシュなど、さまざまなパーティションタイプがある。
  • パーティションのキー: どのカラムを基にパーティションを分割するか。
  • クエリのパフォーマンス: 特定のクエリがパーティション化されたデータにどのように影響するか。
  • メンテナンスの容易性: パーティション化によりメンテナンスが複雑にならないか。

また、実際にパーティショニングを行う前に、データの成長予測、クエリパターン、アプリケーションの要件を詳細に分析することが不可欠です。

デメリット​

パーティショニングには制約もあり、とくに外部キー制約を持つテーブルにパーティションを適用する場合、データベースシステムによってはサポートされていないか、制限がある可能性。
たとえば、PostgreSQLでは外部キー制約を持つテーブルのパーティショニングは制限されている。
外部キーを持つテーブルにパーティションを適用する際は、データ整合性を保ちつつパフォーマンスを改善するための工夫が必要になる。

パーティションが外部キー制約を持てない理由​

パーティションテーブルは、外部キー整合性を保証する仕組み（InnoDB の FKインデックス管理）と両立できない構造をしている

InnoDB の外部キーの仕組み

InnoDB では外部キーをサポートしていますが、それは内部的に以下のようなチェックを常に行っている

  1. 親テーブル（参照先）の PRIMARY KEY または UNIQUE KEY に対して子テーブルの外部キー列が常に整合しているかチェック。
  2. 外部キーを持つ行を挿入・更新・削除するたびに、InnoDB の インデックス構造をロックして検証。
  3. 外部キーは「テーブル全体」で一貫して管理される。（= どの行も同じB-treeインデックスで整合性を保証する）

パーティションテーブルの仕組み

PARTITION BY RANGE などを使うと、1つのテーブルが**複数の物理テーブル（パーティション）**に分割される。

  • 各パーティションが独自のB-treeインデックスを持つ。
  • 実際には「パーティションごとに別ファイル」として管理される。
  • MySQL はクエリ時に「どのパーティションに属するか」を動的に決めてアクセス。

外部キーをチェックするためにはテーブル全体で親子整合性を確認する必要がある だがパーティション構造だと行がどのパーティションにあるかを調べないと、全体の整合性を確認できない

つまり

  • 子テーブルの行がどのパーティションに属しているか
  • 親テーブルの行がどのパーティションに属しているか

を毎回確認しないと、外部キー制約を正しく検証できない。

MySQL の InnoDB 実装では、この「パーティションをまたぐインデックスの一貫管理」をサポートしていないため、外部キーの参照・検証ロジックを組み合わせることができない、という制約がある。

💡 対策・回避の一般的アプローチ

目的 回避策 整合性を守りたい アプリケーション層で明示的にバリデーション（Railsのbefore_saveなど） パフォーマンス重視 FKを完全に外して fail-open 設計に（監査/ログ用途など） 集約的な参照保証が必要 外部キー制約は非パーティションテーブル（マスタ）側にのみ設ける 分割したいが制約もほしい MySQLではなくPostgreSQL（分割+FK対応）を使う検討もあり

⸻

🧠 まとめ

要素 内容 原因 外部キー整合性チェックがパーティション単位のインデックス構造と矛盾するため 技術的制約 InnoDB が「全パーティションにまたがるFKインデックス」をサポートしていない 状況 MySQL 8.0 現在も未対応（将来的に対応予定はあるが難航） 対応策 外部キーを外す／アプリ側で整合性チェック／非パーティションテーブルに分離

⸻

もし希望があれば、 「Railsで外部キー制約を使わず、アプリ側で参照整合性を担保する実装パターン（ActiveRecord::validate_with）」 のコード例も出せます。出しておきます？

パーティション種類​

いくつかある。

  • LISTパーティショニング
  • RANGEパーティショニング（履歴が永遠に重なるテーブルの場合はパーティションを設定してあげる。月毎など）
  • HASHパーティショニング

キモは「なぜ UNIQUE にパーティション列を含めなきゃいけないのか」「それが設計にどう効くのか」。

  1. MySQLのパーティション×UNIQUEの鉄則 • MySQL（InnoDB）のパーティションインデックスは“ローカル”だけ → 各パーティションごとに別のインデックスがあるイメージ。“グローバルインデックス”が無い。 • そのため UNIQUE / PRIMARY KEY は、パーティション列をすべて含んでいないと張れない。 例）PARTITION BY RANGE COLUMNS(received_at) の場合、 UNIQUEには received_at（or それと等価な生成列 received_month）を含める必要がある。

理由 UNIQUE チェックをするとき、MySQL は「どのパーティションを見れば良いか」を決めたい。 パーティション列を含まない UNIQUE（例：UNIQUE(event_id)）だと、どのパーティションにも同じ event_id があるかもしれず、全パーティションを横断検索しなきゃいけない＝構造的にNG。 → だから **UNIQUE には必ずパーティション列を含めて“そのパーティション内で完結する一意性”**にする必要がある。

⸻

  1. じゃあ、event_id を UNIQUE にするには？ • 月次パーティションを received_at で切るなら、 UNIQUE(provider, source_account, event_id, received_month)（= 月内一意）にする。 • received_month は CAST(DATE_FORMAT(received_at,'%Y-%m-01') AS DATE) の 生成列でOK（パーティション列と等価）。 • こうすると、その月のパーティションだけを見れば重複検証できる。 • 注意：event_id が NULL の場合、MySQL の UNIQUE は NULL を“全部別物”扱いするので、NULL は重複許容になる（仕様通り）。 → NULL を重複不可にしたいなら、アプリで NULL を入れない or **別のキー（hash等）**で補う。

⸻

  1. 「全期間で絶対一意」にしたい場合の選択肢

月次パーティションと “全期間一意” は構造的に相性が悪い（前述の理由）。選べるのは：

A. 月内一意で割り切る（推奨・シンプル） • UNIQUE(provider, source_account, event_id, received_month) • 月またぎ再送は別パーティションなのでDB的には許容。 • 実務的には、アプリ側で occurrence を弾く設計と合わせれば十分。

B. レジストリ（目録）テーブルで全期間一意を担保 • webhook_event_registry(provider, source_account, event_id) に UNIQUE。 • まずレジストリへ INSERT IGNORE → 成功なら本体 webhook_events へもINSERT。 • 真に全期間一意になる。一方で I/Oが1回増えるのと、レジストリの可用性が要件になる。

C. パーティション設計を “event_id基準”に変える（非推奨） • 例：PARTITION BY HASH(event_id) なら UNIQUE(event_id) が理屈上いける。 • ただし 月次DROPができなくなる（運用目的のローテが崩れる）。 • 監査・アーカイブ運用の観点でデメリットが大きいので通常はやらない。

まとめ：月ベースでDROP運用したいなら A or B。 A：シンプル（多くの現場で十分）／B：全期間一意がハード要件なら採用。

⸻

  1. インデックス設計の具体（webhook_events向け）

4.1 “最後の砦”の一意制約（任意）

UNIQUE KEY uq_provider_src_event_month (provider, source_account, event_id, received_month)

• 並行到着の競合やアプリの弾き漏れをDBで静かに防ぐ。 • 月内だけ守れればOK（ログ用途）。 • source_account を入れるのは、**複数アカウント運用（Stripeの複数 acct）**でID衝突を避けるため。

4.2 よく効く検索系BTREE • ユーザー軸で最新を見たい： KEY (user_id, received_at) • ユーザー×プロダクト軸： KEY (user_id, product_id, received_at) • プロバイダ×タイプで直近調査： KEY (provider, event_type, received_at) • アカウント単位で時系列： KEY (source_account, received_at) • event_id 直指定でトレース： KEY (event_id, received_at)

コツ • 範囲条件（期間絞り）がほぼ必ず付くなら received_at はキー末尾に置く（インデックスレンジスキャンが効きやすい）。 • 並び順は通常の昇順でOK（MySQL 8 は逆順スキャンも速い）。必要なら received_at DESC の降順インデックスも可。 • 文字列カラムは utf8mb4_bin なのでprefix index は基本不要。サイズが気になるときだけ event_type(64) などに落とせば良い。

4.3 監査ハッシュ（HMAC） • 改ざん検証はバッチで全件（またはサンプリング）検証するので、基本 インデックス不要。 • ad-hoc 検索で使うなら KEY (payload_hash) を追加。

  1. 受信基準とパーティションプルーニング • パーティションは received_at（or received_month） で切る。 • クエリで received_at BETWEEN ... を入れるとプルーニングが効いて、該当月のパーティションだけ読む＝速い/ロックも小さい。 • 逆に WHERE event_id = ? の単独条件だけだと、パーティションを跨いで探す（遅い）可能性がある。 → 実務では「event_id 直指定のときも received_at を絞る」 or 「パーティション数が少ないので許容」かの判断になる。

  2. 典型パターン別・“どう張るか”早見表

要件 推奨 UNIQUE 備考 月次DROPで軽く運用／ログ用途 (provider, source_account, event_id, received_month) 月内一意で十分。アプリ弾きと併用 全期間で絶対一意にしたい なし（本体）＋ レジストリ表で UNIQUE webhook_event_registry(provider, source_account, event_id) 全期間一意＆パーティション欠かせない 設計再考 HASH(event_id) 等はローテ運用と相性最悪

⸻

  1. 迷いポイントに対する指針 • 「event_id を UNIQUE にしたい」 → “月内一意”でOKか？ → YESなら received_month を含めて張る。 → **NO（全期間で一意）**なら レジストリで担保する。 • 「高速に月次ローテしたい」 → パーティションは必ず received_at 基準。 → その代償として、UNIQUE は月内一意になる（= 設計上のトレードオフ）。

⸻

結論（今回の設計に落とすと） • UNIQUE（任意・最後の砦）： UNIQUE (provider, source_account, event_id, received_month) • BTREE： • (user_id, product_id, received_at) • (user_id, received_at) • (provider, event_type, received_at) • (source_account, received_at) • (event_id, received_at) • （必要なら）(payload_hash)

これで

  • 月次DROPの運用を優先しながら、
  • アプリの弾き漏れ対策も置けて、
  • 観測クエリは速い構成になります。

さらに深掘るなら、実際に想定クエリを書いて「このインデックスを使うか？」の EXPLAIN で確認してもいいよ。

Resource​

に最終更新