管理不要なDBパーティションの設計

TL;DR 主キーでパーティション化し、`created_at`ではなく、バックグラウンドサービスが観測された成長に基づいて境界を管理するようにします。クエリは既存のキーを使い続け、パーティションプルーニングは自動的に機能し、パーティション列はアプリケーションコードに漏れ出すことはありません。同じ「サービスが監視・調整する」パターンが、異なる操作を持つハッシュおよびリストパーティショニングにも適用されます。 パーティショニングのデプロイの1週間後、注文ダッシュボードの読み込みが遅くなり始めました。チームの最初の本能は、新しいインデックス戦略を非難することでした。実際の原因はEXPLAINに現れます：単なるSELECT * FROM orders WHERE id = 12345というクエリに対して、36行のPartitions: orders_p2025_01, orders_p2025_02, ...が表示されます。WHERE句に`created_at`が含まれておらず、`created_at`がパーティションキーであるため、プランはすべてのパーティションを読み取ります。かつて1回のインデックスプローブだったルックアップは、今や36回になりました。 提案される修正は、常に提案されるものです：ダッシュボードクエリに`created_at >= '2024-11-01'`を追加します。これは機能します。プランは1つのパーティションにドロップします。次に、監査ページも同様のことを行い、管理ツールも、移行スクリプトも同様です。3か月後、日付フィルターなしのSELECT FROM ordersをフラグする内部リンタルールができ、コードレビューには「パーティションフィルターを追加しましたか？」という標準チェックが含まれるようになります。パーティションキーはストレージの決定事項ではなくなり、すべてのクエリが遵守しなければならない契約になりました。忘れてもエラーは発生しません。ただ遅くなるだけです。 パーティションキーの問題 PostgreSQLとMySQLの両方で、プライマリキーまたはテーブル上のユニーク制約の一部にパーティションキーが必要です。そのルールは正確性のために存在します：プライマリキーがパーティションキーを含んでいない場合、データベースはすべてのパーティションをスキャンせずに一意性を強制できません。 その結果、`created_at`でパーティション化したい場合、もはやPRIMARY KEY (id)だけではいけません。PRIMARY KEY (id, created_at)が必要になります。日付列は、アプリケーションが必要としたかどうかにかかわらず、プライマリキーの一部になりました。 より微妙なコストは、`id`がデータベースの観点からはもはや一意ではないということです。一意性はタプル(id, created_at)で強制されます：データベースは、異なるタイムスタンプを持つ限り、同じidを持つ2つの行を喜んで受け入れます。アプリケーションはおそらく`id`をユニークとして扱いますが、スキーマにはそれを保証するものはありません。また、個別のUNIQUE (id)制約で保証を回復することもできません：MySQLとPostgreSQLの両方で、パーティションテーブル上のすべてのユニーク制約にパーティションキー列を含める必要があります。一意性のプロパティは事実上失われました。 これは単なる化粧品ではありません。オプティマイザーが生成することをいとわないクエリプランを変更します： PRIMARY KEY (id)の場合、WHERE id = 1は定数時間ルックアップです。MySQLのEXPLAINはこれをconstアクセスタイプとして表示します。オプティマイザーは一致する行が1つだけであることを知っており、実行者はそれを見つけた後に停止します。idでの結合はeq_refであり、最も速い結合アクセスタイプです。 PRIMARY KEY (id, created_at)の場合、同じクエリはrefルックアップになります：データベースの観点からは複数の行を返す可能性がある左端のインデックス列のプレフィックススキャンです。かつてeq_refだった結合はrefになります。カーディナリティ推定値は、保証された「1行」の仮定ではなく、インデックス統計にフォールバックするため、クエリツリーの上位でより悪いプランに向かう可能性があります。 古いconstプランを取り戻すには、すべてのルックアップで完全なプライマリキーを指定する必要があります： 1 2 3 4 5 -- かつてはconstルックアップ、今はrefルックアップ（多数の行の可能性あり） SELECT * FROM orders WHERE id = 1; -- constに戻るが、呼び出し元がcreated_atを知っている場合のみ SELECT * FROM orders WHERE id = 1 AND created_at = '2026-04-01 12:34:56'; これは、別の角度から見たパーティションプルーニングと同じ漏洩です：パーティションキーは、日付とは無関係だったクエリに、まずプルーニングを得るために、そして今や単一行アクセスを得るために、強制的に入り込みました。 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 -- パーティショニング前 CREATE TABLE orders ( id BIGINT AUTO_INCREMENT PRIMARY KEY, customer_id BIGINT NOT NULL, total_cents INT NOT NULL, created_at DATETIME NOT NULL ); -- パーティショニング後（月ごと） CREATE TABLE orders ( id BIGINT AUTO_INCREMENT, customer_id BIGINT NOT NULL, total_cents INT NOT NULL, created_at DATETIME NOT NULL, PRIMARY KEY (id, created_at) -- created_atがPKに強制された ) PARTITION BY RANGE (TO_DAYS(created_at)) ( PARTITION p202601 VALUES LESS THAN (TO_DAYS('2026-02-01')), PARTITION p202602 VALUES LESS THAN (TO_DAYS('2026-03-01')), ... ); この時点ではすべて機能しています。テーブルは挿入を受け付け、クエリは正しい結果を返します。パーティション境界は存在します。問題は、日付を含まないクエリを誰かが実行したときに初めて現れます。 パーティションプルーニングは、要求した場合にのみ機能します パーティションプルーニングは、パーティショニングを価値あるものにする最適化です。クエリのWHERE句がパーティションキーを制限すると、データベースは一致する可能性のあるパーティションをスキップできます。先週の注文に対するクエリは、先週のデータを含む1つまたは2つのパーティションのみを読み取ります。 その最適化は、パーティションキーがWHERE句に現れることに依存します。他のものでフィルタリングするクエリはプルーニングされません。すべてのパーティションをスキャンします。 1 2 3 4 5 -- このクエリはすべてのパーティションをスキャンします。36個あります。 SELECT * FROM orders WHERE id = 12345; -- こちらは単一のパーティションにプルーニングされます SELECT * FROM orders WHERE id = 12345 AND created_at >= '2026-03-01' AND created_at < '2026-04-01'; 最初のクエリは、常に発生する種類のルックアップです：プライマリキーで注文を取得します。パーティション化されていないテーブルでは、単一のインデックスシークです。プルーニングキーがWHERE句にないパーティション化されたテーブルでは、各パーティションに対して個別のインデックスプローブを実行します：1回のインデックスルックアップではなく36回のインデックスルックアップです。絶対的な用語ではまだ高速ですが、パーティショニングが導入された理由とは反対の、非パーティション化されたバージョンよりもはるかに悪いです。 チームが通常採用する「修正」は、テーブルに触れるすべてのクエリにパーティションキーを追加することです。それは漏洩する抽象化です。ストレージの決定は、すべての呼び出し元との契約になります：新しいコードはパーティションフィルターを記憶する必要があり、古いコードは監査される必要があり、ORMはこの周りに設定される必要があります。 エラーはなく、ただ遅くなるだけです プルーニングされるべきなのにプルーニングされないクエリは、依然として正しい結果を返します。プランはすべてのパーティションをスキャンするだけです。例外も警告も、アプリケーションログのフラグもありません。誰も読まないEXPLAINだけが、ダッシュボードがタイムアウトするまで表示されません。ほとんどのチームは、クエリ実行中にデータベースが表面化するものからではなく、パーティションデプロイ後にスロークエリログをレビューすることによって、失敗を発見します。 静的なパーティション境界はうまく経年変化しない もう1つうまくいかない傾向があるのは、テーブル作成時にパーティション境界をハードコーディングすることです。初期レイアウトは、その時点でのチームの成長予測を反映しています。6か月後、トラフィックパターンが変化し、一部のパーティションは他のパーティションの10倍大きくなり、`p_future`のキャッチオールパーティションがテーブルの半分を保持しています。 1 2 3 4 5 6 7 -- 作成時に定義：妥当に見える PARTITION p2026_q1 VALUES LESS THAN (100000000), PARTITION p2026_q2 VALUES LESS THAN (200000000), ... -- 6か月後：成長が加速し、p_futureはアクティブなワークロード全体になった PARTITION p_future VALUES LESS THAN MAXVALUE -- 8億行以上、増加中 パーティションを手動で分割および再分散することは、誰も所有したくない運用作業です。メンテナンスウィンドウのスケジュール設定、数百ギガバイトになる可能性のあるテーブルに対するALTER TABLE ... REORGANIZE PARTITIONの実行、アプリケーションチームとの調整、そして間違いをしないことが必要です。それはパフォーマンスインシデントが発生するまで行われず、その時点で修正は高価になります。 より良いアプローチの形状 主キーはすでに存在します。BIGINT AUTO_INCREMENTを使用するテーブルの場合、単調増加します：新しい行はより大きなIDを持ちます。それがレンジパーティショニングが必要とするプロパティです。主キーがパーティションキーです。 1 2 3 4 5 6