自動増分主キー（代理キー）=悪？

この記事では、PostgreSQL Experts IncのCEOであるJosh Berkusによる（ほとんど否定的な）記事を見ていきます。 データベーステーブルに代理キーを使用するには、使用しているのと同じINT NOT NULL AUTO_INCREMENT PRIMARY KEYを使用します。 実際、 ittoolboxに関する彼の記事の翻訳は無料で大幅に削減されます 。



この記事の後に、1つの古いプロジェクトで行われたこのトピックに関する私自身の間違いのレビューが続きます。 私は若くて愚かだったが、それは私を許しません。



正直なところ、この記事を読んで著者が誰であるかに気付かずに、私は彼がまだ誇張していると思ったので、一般的に、彼なしでどこでどのキーを使用するかを考えます。 それから私はもう少し考えて、私の古いプロジェクトの基本構造のダンプに入りました。 面白かった。



経験豊富なDBAである場合は、動揺しないようにおそらく通り抜ける必要があります。



しかし、まず最初に。 最初の非常に短縮された翻訳：





「サロゲート数値キーは、行番号を必要とする古いアプリケーションで保存するためのSQL89標準に該当しました。 その後、 ジョー・セルコとの会話の中で、 コッドは自分がこれを許可したことを後悔していると言った。



経験の浅い開発者は、サロゲートキーの使用がパフォーマンスを考慮した実用的な妥協であることを理解していないため、どこでも使用します。 データベースに関する本の著者でさえ、どのような場合でもすべてのテーブルに作成することをお勧めします。



リレーショナルデータベースの理論では、主キーの概念はありません。 すべてのデータベースキーの重要性は同じです。 主キーの概念は、唯一のキーがディスク上のタプルの順序を決定するという概念に基づいており、リレーショナル理論は、データの論理モデルではこれを無視する必要があることを示しています。 したがって、一般的に主キーは関係理論の違反です。



代理キーはまったく使用できないと言っているのではなく、その使用を悪用できないと言っています。



代理キーを使用する動機となる理由は何ですか？



複数列のキーで妥協します 。 通常、かなり説得力があります。 複数列キーと結合メカニズムを使用するSQLクエリの構文は、この種のクエリのパフォーマンスと同様、現在のところ多くの要望が残っています。 これらの問題が解決されると、この理由はなくなります。



データには実際のキーはありません 。 非常に悪い理由です。 その外観は、データベース全体の貧弱な設計と、開発者が作業するデータを実際に理解していないという事実の両方を示しています。



外部要件 。 通常は説得力があります。 通常、開発環境とデータベースツールはサロゲートキーのみをサポートします。 そして、あなたがこのツールがあなたが解決している問題に不可欠であると思うならば、まあ...



データの一貫性 。 通常は説得力があります。 しかし、あなたが本当に細心の注意を払って計画に従い、設計全体が慎重に計画されている場合にのみ。



SQL標準および優れた設計の原則の順守 。 非常に悪い理由です。 それは完全に無知に基づいています。 通常、大学で勉強している人のブログを読んでいる誰かがサロゲートキーの使用が業界の標準であると聞いたので、彼らは彼女をフォローします。 最新のSQL標準もリレーショナル理論自体にも、代理キーの言及すら含まれていないことに注意してください。



簡単な変更の可能性 。 不明。 実際、一部のDBMSはON UPDATE CASCADEを実行できないか、非効率的に実行できません（ところで、DBMSを変更する理由と考えてください）。 そして、この場合、この理由は重要です。 ただし、開発者は、レコーディングのキー[プライマリ]を変更せず、レコーディングのライフサイクル全体を通して同じままにする必要があると言うことがあります。 この声明は気にする価値はなく、もちろん、関係理論には完全に存在しないことに留意してください。



パフォーマンス 。 通常、悪い理由です。 はい、確かに、自然キーを使用すると、代理キーと比較してシステムの速度が大幅に低下する場合があります。 しかし、80％のケースでは、この声明は実際のテストに基づいていないため、そのような声明は根拠のないままです。 事前最適化は、データベース設計の多くの問題の原因です。



メガボリュームデータベースの場合、結果のテーブルサイズも重要になる場合があります。 ただし、このためにはベースが非常に大きくなければなりません。



結合またはソートのパフォーマンスは、主キーのタイプとそのコンポーネントの数に応じて、大量のデータでも重要になります。 しかし、私の経験では、この理由が呼ばれたとき、実際の計算やパフォーマンス測定の背後にあることはほとんどありません。 たとえば、 www.bricolage.ccは長年にわたってテーブルに14バイトの数値主キーを使用しています。 ただし、この場合、履歴で300万番目のレコードを持つユーザーが出現した後、パフォーマンスのために主キーを変更することに関する質問が発生したときに、クエリを書き換えることでこの問題を解決しました。 生産性の約10倍の増加が達成されました。



問題は代理キーの使用ではなく、それらの不正使用であることに注意してください。



非常に簡略化された翻訳の終わり。 オリジナル（プライマリKeyvilと呼ばれます）： it.toolbox.com/home/search.aspx?r=%22Primary+kevill%22&community= 1& contentType=5



翻訳で重要なものを見逃した場合は、それについて教えてください。 追加します。

今、私自身が考えていることについて少し。

しかし、この記事はこの問題に対して少しドラマチックに見えました。 後でパフォーマンスの問題を回避するためだけに、代理キーがより頻繁に選択されるように思われます。最近では、DBMS自体のレベルで植え付けられるほど誰もがそれらに慣れてきました。 たとえば、InnoDBでは、主キーを作成しない場合は、自分で作成します。 ところで、InnoDBの場合、主キーの選択は、クラスタリングが実行されるため、パフォーマンスの点で重大な結果をもたらします（したがって、自然キーの選択は状況を改善および悪化させる可能性があります）。



代理キーはあたかも代理キーが悪であるかのように聞こえるという事実にもかかわらず、著者は、問題はその使用ではなく乱用であると何度も強調しています。



この記事は、主キーの特別な候補を探すのではなく、単にINT NOT NULL AUTO_INCREMENT PRIMARY KEYフィールドを作成して、じっと座っているのが自然だといつも思っていたという意味で私の目を開きました。 もちろん、主キーとして任意の一意のキーを選択できることは知っていましたが、私はそれに集中しませんでした。 このデータベース行を本当にユニークにするのはなぜか、なぜそれが重要なのか、私は本当に考えもしませんでした。 結局のところ、無駄でした。



例として、小さな古いプロジェクトを紹介したいと思います。 いくつかのテーブルのみがあります。 最初はもっと大きなものを選びたかったのですが、それは無駄だと思います。 無駄にしか時間がかかりません。 誰もが自分の古いプロジェクトを開いて、説明された位置の観点からそれを見てみましょう。 実際、正義のために1つの間違いを実際に追加しました。 とにかくそれをやったでしょう。 偶然だけで助かりました。



プロジェクトは閉じた急流トラッカーです。 正規化やその他のあらゆる問題に注意を払わないようお願いします。 私が今それを書いたら、多分何か違うことをするでしょう。 代理キーに焦点を当てましょう。

データベース構造

pastebin.com/LstH8Xfx



最初に説明したいテーブルは、ログテーブルです。 一般的に、私は突然ミスを見たので、このケースが私を少し驚かせたのです。 非常に小さく、あまり注目に値しませんが、それでも、これは私が長年気付かなかった間違いです。 まったく気づかなかった。 ここでテキストを中断して、このテーブルの構造に戻ります。 ほら 私は見ていません。



このテーブルには、簡単な情報が格納されます。 IP、ユーザーID、イベントの発生日、およびそのテキスト。 はい、もちろん、テキストをコードで置き換えたり、さらに多くのことを実行することもできますが、今はそのことについてではありません。 記事を読んだ後、私はこの表を見て、そう思ったので、代理キーを作成しました。 しかし、実際のデータキーは何ですか？ 特定のテーブル行を一意にするものは何ですか？



答えはとても簡単です。 ユーザーIDとイベントが発生した時刻の組み合わせ。 そして、ここで突然反対側から状況を見ました。 私のほとんどすべての古いプロジェクトでは、DATETIMEフィールドを使用してログに時間を保存しています。 便利だからといって。 はい、私はそれが2番目に正確に保存され、完全に私に合っていることを知っていました。 さて、私が自然な鍵を探し始めたとき、それがどのような結果をもたらすかが突然思い浮かびました。 問題のトレントトラッカーの負荷は非常に高く、1秒以内に多くのことが起こります。 実際、このひどいサロゲートキーを持つログに、同じ時間にいくつかのイベントがあり、それらが非常にすばやく連続して発生した場合、最初に発生したイベントと最後に発生したイベントは、サロゲートキーの自動インクリメントによってのみ誘導されます。 日付情報フィールド自体は、そのようなことを正確に報告するために作成されたものですが、私には役立ちません。 また、イベント間の正確な間隔を特定することもできません。



一般的に、これはもちろん重要ではありません。 2つのイベント間の間隔を見つける必要がある確率は、いずれにしても1秒未満ですが、非常に小さいです。 しかし、私は常に、古いものから新しいものまで、すべてのプロジェクトを教育的なものと考えています。 デザインは少し異なる可能性があり、重要になる可能性があります。



私は、自然キーを見つけるという観点から問題を考えることは、多少異なる見解であると言いたいです。 この方法でプロジェクトの設計を見て、何が出るかを確認してください。



私の説明は混oticとしているようでした。 それにもかかわらず、私はあなたに私の考えを伝えることができたと思います。



これで、テーブルはピアになりました。 彼女はすでに、主な役割を単に要求する一意のキーを持っています。 1秒あたり何百もの挿入/削除がピアテーブルに行われ、主キーの形式で追加のインデックスを保持することは単に利益を生みません。 だから私はそれを排除しました。



セッションテーブル。 何らかの理由で、PHPセッションに完全に依存していませんでしたが、部分的に実装しました。 このテーブルの主キーはランダムな値です。 40文字のランダムシーケンスを使用するのは愚かであるだけでなく、ここではまったく必要ありません。 このテーブルのエントリの自然キーとして機能するものは何ですか？ このプロジェクトでは、ユーザーが複数のコンピューターから同時にログインすることは許可されていません。 あの user_id？ この値に関する他のすべてはセカンダリです。 この単純なステートメントから何が起こるかは今分析しません。 セッションテーブルを削除して別のメカニズムを実装するまでの多くのこと。 多くのオプションがあります。



トレントテーブルに移りましょう。



議論の主題を想像できるように、小さな余談。 私が開発したトレントトラッカーは、同時に、配布されたファイルの最初のシードでもありました。 トレントテーブルには、座っていたファイルに関する情報が保持されていました。 これらのファイルはサーバーファイルシステムにあり、対応する.torrentファイルは、1ファイル= 1トレントのスキームに従って作成され、ユーザーがダウンロードしました。 各トレントには、それを一意に識別するいわゆるinfo_hashがあります。



ピアテーブルのこのフィールドは、peer_info_hashと呼ばれます。 トレントテーブルでは、これはtorrent_info_hashフィールドです。 torrent_idはそこにありません。 絶対に。 ピアtorrent_idもテーブルに含まれていることに注意してください。 理由は明らかではありません。



さて、ユーザーテーブル。 ここでは間違いを犯すことはできなかったようです。 間違っていた。



許可システムのトレントトラッカーでは、ユーザーに固有の値を持つGETパラメーターが使用されます。 表では、これはuser_torrent_uidの値です。 だから、誰がこの値をあるバージョンまたは別のバージョンで自然なキーとして使用することを妨げたのでしょうか？ はい、変更できます。 非常にまれなケースです。 それで何？ 8バイトが長すぎる場合、賢い人がFlickrで行うように、通常のランダムなINTをテキストに変換できます。 それは可能でした...はい、多くのことが可能でした。



行くぞ すべてが明らかですよね？ :)