Course All lectures for JA purposes - Lecture: データベース内のキー

5.1 はじめに

インターネットには、リレーショナルデータベースでキーをどのように選択して使用するかについての独断的な教訓が溢れています。場合によっては、論争がホリバーに発展することさえあります。自然キーを使用すべきか、それとも人工キーを使用すべきか? 整数または UUID を自動インクリメントしますか?

64 の記事を読み、5 冊の本のセクションをめくり、IRC と StackOverflow について大量の質問をした後、私 (元の記事の著者である Joe "begriffs" Nelson) はパズルのピースを組み立てたようです。敵対者を和解させることができるようになりました。実際、重要な論争の多くは、他人の視点の誤解から生じています。

問題を分解して、最後に元に戻しましょう。まず、「キー」とは何ですか?という質問をしてみましょう。

主キーのことはしばらく忘れましょう。より一般的なアイデアに興味があるのです。キーは、行内に重複する値を持たない列 (列) です。また、列は還元不可能に一意である必要があります。つまり、列のサブセットにはこのような一意性はありません。

しかしその前に、いくつかの理論を説明します。

主キー

主キーテーブル内の行を識別するために直接使用されます。次の制限に従う必要があります。

主キーは常に一意である必要があります。
これは常にテーブル内に存在し、値を持っている必要があります。
値を頻繁に変更しないでください。理想的には、値をまったく変更しないでください。

通常、主キーはテーブルの単一の列を表しますが、複数の列で構成される複合キーにすることもできます。

複合キー

カスタムキー- テーブルの各行を一意に識別する属性 (列) の組み合わせ。すべての列、複数の列、または 1 つの列を指定できます。この場合、これらの属性の値を含む行を繰り返すべきではありません。

潜在的な鍵

候補キー- リレーション (テーブル) の最小の複合キー、つまり、いくつかの条件を満たす属性のセットを表します。

Ireducibility : 削減することはできません。可能な最小限の属性セットが含まれます。
一意性: 行の変更に関係なく、一意の値を持つ必要があります。
値の存在: null 値があってはなりません。つまり、値が必要です。

5.2 主キーの奇妙なケース

前のセクションで「キー」と呼んだものは、一般に「候補キー」と呼ばれます。「候補」という用語は、そのようなすべてのキーが「主キー」（主キー）の名誉ある役割をめぐって競合し、残りのキーには「代替キー」（代替キー）が割り当てられることを意味します。

SQL 実装がキーとリレーショナルモデル間の不一致を克服するには時間がかかり、初期のデータベースは主キーという低レベルの概念を対象としていました。このようなデータベースの主キーは、シーケンシャルストレージメディア上の行の物理的な位置を識別するために必要でした。ジョー・セルコはそれを次のように説明しています。

「キー」という用語は、シーケンシャルファイルシステム上であらゆる処理操作を実行するために必要なファイルソートキーを意味しました。パンチカードのセットは 1 つだけの順序で読み取られました。戻ることは不可能でした。初期のテープドライブは同じ動作を模倣しており、双方向アクセスを許可していませんでした。つまり、元の Sybase SQL Server では、前の行を読み取るためにテーブルを先頭まで「巻き戻す」必要がありました。

最新の SQL では、情報の物理的な表現に焦点を当てる必要はなく、テーブルは関係をモデル化し、行の内部順序はまったく重要ではありません。ただし、現在でも SQL サーバーはデフォルトで主キーのクラスター化インデックスを作成し、古い伝統に従って行の順序を物理的に配置します。

ほとんどのデータベースでは、主キーは過去のものとなり、反映または物理的な場所以上のものは提供されません。たとえば、PostgreSQL テーブルでは、主キーを宣言すると、自動的に制約が適用されNOT NULL、デフォルトの外部キーが定義されます。さらに、主キーは演算子にとって優先される列ですJOIN。

主キーは、他のキーを宣言する可能性をオーバーライドしません。同時に、キーがプライマリとして割り当てられていない場合でも、テーブルは正常に機能します。いずれにせよ、雷はあなたに落ちることはありません。

5.3 自然キーの検索

上で説明したキーは、誰もキーを作成したくないとしても、それ自体が興味深いモデル化されたオブジェクトのプロパティであるため、「ナチュラル」と呼ばれます。

考えられる自然キーをテーブルで調べるときに最初に覚えておくべきことは、賢くなりすぎないようにすることです。StackExchange のユーザー sqlvogel は次のアドバイスを提供しています。

特定のキーが一意ではない可能性があるという仮定の状況を思いつくため、「自然な」キーを選択するのが難しい人もいます。彼らはその仕事の意味そのものを理解していません。キーの意味は、属性が特定のテーブル内で常に一意である必要があり、常に一意になるというルールを定義することです。この表には、特定のよく理解されたコンテキスト (「主題領域」または「談話領域」) のデータが含まれており、唯一の意味は、その特定の領域における制限の適用です。

実践してみると、列が使用可能な値で一意であり、考えられるシナリオでも一意のままである場合、キー制約を導入する必要があることがわかります。必要に応じて、制限を解除することもできます (これが気になる場合は、以下でキーの安定性について説明します)。

たとえば、趣味クラブのメンバーのデータベースは、、の 2 つの列に一意性がある可能性がありfirst_nameますlast_name。データの量が少ない場合、重複が発生する可能性は低く、実際の競合が発生する前に、そのようなキーを使用することは非常に合理的です。

データベースが成長し、情報量が増加すると、自然キーの選択がより困難になる可能性があります。私たちが保存するデータは外部現実を単純化したものであり、時間の経過とともに変化する座標など、世界のオブジェクトを区別するいくつかの側面は含まれていません。物体にコードが欠けている場合、空間配置や重量や包装のわずかな違いから、缶飲料 2 つやオートミール 2 箱をどのように区別できるでしょうか?

そのため、標準化団体は独自のマークを作成し、製品に適用します。車両には車両識別番号 (VIN)が刻印され、書籍にはISBNが印刷され、食品のパッケージにはUPC が付いています。これらの数字が自然ではないと思われるかもしれません。では、なぜこれらを自然キーと呼ぶのでしょうか?

データベース内の固有のプロパティの自然性または人工性は、外の世界と比較して決まります。標準化団体や政府機関で作成されたときには人工的だったキーは、全世界の標準になったり、物体に印刷されたりするため、私たちにとって自然なものになります。

通貨、言語、金融商品、化学物質、医療診断など、さまざまなテーマに対して業界標準、公的標準、国際標準が多数存在します。自然キーとしてよく使用される値の一部を次に示します。

ISO 3166 国コード
ISO 639 言語コード
ISO 4217に準拠した通貨コード
株式記号 ISIN
UPC/EAN、VIN、GTIN、ISBN
ログイン名
メールアドレス
部屋番号
ネットワークMACアドレス
地球表面上の点の緯度、経度

可能かつ合理的な場合は常にキーを宣言することをお勧めします。場合によってはテーブルごとに複数のキーを宣言することもできます。ただし、上記のすべてには例外がある可能性があることに注意してください。

データベースの条件によっては許容される場合もありますが、誰もが電子メールアドレスを持っているわけではありません。また、電子メールアドレスを時々変更する人もいます。(キーの安定性については後で詳しく説明します。)
ISIN の銘柄記号は時々変更されます。たとえば、記号 GOOG および GOOGL は、Google から Alphabet への会社の再編を正確に表しません。TWTR と TWTRQ では、一部の投資家が Twitter の IPO 中に誤って後者を購入したように、混乱が生じることがあります。
社会保障番号は米国国民のみが使用し、プライバシー制限があり、死後も再利用されます。さらに、文書が盗まれた後、新しい番号を取得する可能性があります。最後に、同じ番号で個人と所得税識別子の両方を識別できます。
郵便番号は都市にとって適切な選択ではありません。一部の都市には共通のインデックスがあり、逆も同様で、1 つの都市に複数のインデックスがあります。

5.4 人工キー

キーが各行に一意の値を持つ列であることを考えると、キーを作成する 1 つの方法は不正行為です。各行に架空の一意の値を書き込むことができます。これらは人工キー、つまりデータまたはオブジェクトを参照するために使用される発明されたコードです。

コードがデータベース自体から生成され、データベースのユーザー以外には分からないことが非常に重要です。これが、人工キーと標準化された自然キーを区別するものです。

自然キーにはテーブル内の行の重複や不一致を防ぐという利点がありますが、人工キーは人間や他のシステムが行を参照しやすくなり、キーを使用しないため検索や結合が高速化されるため便利です。文字列 (または複数列) の比較。

サロゲート

人工キーはアンカーとして使用されます。ルールや列がどのように変更されても、1 つの行を常に同じ方法で識別できます。この目的で使用される人工キーは「代理キー」と呼ばれ、特別な注意が必要です。以下ではサロゲートについて検討します。

非サロゲート人工キーは、データベースの外部から行を参照する場合に便利です。人工キーは、データまたはオブジェクトを簡単に識別します。URL として指定したり、請求書に添付したり、電話で口述したり、銀行から入手したり、ナンバープレートに印刷したりすることができます。（車のナンバープレートは私たちにとっては自然な鍵ですが、政府によって人工鍵として設計されています。）

合成キーは、タイプミスやエラーを最小限に抑えるために、考えられる送信手段を考慮して選択する必要があります。キーは、話したり、印刷したものを読んだり、SMS で送信したり、手書きで読んだり、キーボードから入力したり、URL に埋め込んだりできることに注意してください。さらに、クレジットカード番号などの一部の人工キーには、特定のエラーが発生した場合でも少なくとも認識できるようにチェックサムが含まれています。

例:

米国のナンバープレートには、O や 0 などのあいまいな文字の使用に関する規則があります。
病院や薬局では医師の手書きの文字があるため、特に注意が必要です。
確認コードをテキストメッセージで送信しますか? GSM 03.38 文字セットを超えないでください。
任意のバイトデータをエンコードする Base64 とは異なり、Base32 は人間が古いコンピュータシステムで使用および処理するのに便利な限定された文字セットを使用します。
Proquint は、読み取り可能、書き込み可能、および発音可能な識別子です。これらは、明確に理解される子音と母音の PRO 発音可能な QUINT アップレットです。

人工キーを世界に紹介するとすぐに、人々は不思議なことにそれに特別な注意を払い始めることに注意してください。「泥棒」のナンバープレートや、悪名高い自動呪い生成ツールとなった発音可能な識別子を作成するシステムを見てください。

テンキーに限っても13階のようなタブーはあります。プロクイントは音声音節あたりの情報密度がより高いですが、受信者がキーが数字のみであることを知っている限り、URL、ピンキーボード、手書きのメモなど、さまざまな方法で数字も問題ありません。

ただし、公開数値キーでは順序を使用しないでください。これにより、リソース (/videos/1.mpeg、/videos/2.mpeg など) を探索でき、番号に関する情報が漏洩する可能性があるためです。データ。Feistel ネットを一連の数値に重ね合わせ、数値の順序を隠しながら一意性を維持します。

追加のキーを宣言することに反対する唯一の議論は、新しいキーごとに別の一意のインデックスが追加され、テーブルへの書き込みコストが増加することです。もちろん、データの正確性がどの程度重要であるかによって異なりますが、ほとんどの場合、キーは宣言される必要があります。

人工キーがある場合は、それを宣言することも価値があります。たとえば、組織には求職者 (応募者) と従業員 (従業員) がいます。各従業員はかつては候補者であり、独自の識別子によって候補者を参照します。これは従業員のキーでもある必要があります。別の例として、従業員 ID とログイン名を Employees の 2 つのキーとして設定できます。

5.5 代理キー

すでに述べたように、重要なタイプの人工キーは「代理キー」と呼ばれます。他の人工キーのように簡潔で通過可能である必要はありませんが、文字列を常に識別する内部ラベルとして使用されます。これは SQL で使用されますが、アプリケーションは明示的にアクセスしません。

PostgreSQL のシステム列に精通している場合は、サロゲートをほぼデータベース実装パラメータ (ctid など) と考えることができますが、これは決して変更されません。サロゲート値は行ごとに 1 回選択され、その後は変更されません。

サロゲートキーは外部キーとして優れており、ON UPDATE RESTRICTサロゲートの不変性に一致するようにカスケード制約を指定する必要があります。

一方、ON UPDATE CASCADE最大限の柔軟性を提供するために、公開共有キーへの外部キーにはのマークを付ける必要があります。カスケード更新は周囲のトランザクションと同じ分離レベルで実行されるため、同時実行性の問題を心配する必要はありません。厳密な分離レベルを選択した場合、データベースは問題なく動作します。

代理キーを「自然」にしないでください。代理キーの値をエンドユーザーに示すか、さらに悪いことに、その値を (特にルックアップを通じて) 操作させると、実質的にキーに値を与えることになります。その後、データベースから表示されたキーが、他の人のデータベースの自然キーになる可能性があります。

外部システムに送信用に特別に設計された他の人工キーの使用を強制することで、サロゲートとの内部の参照整合性を維持しながら、変化するニーズに合わせてそれらのキーを必要に応じて変更できるようになります。

自動インクリメントINT/BIGINT

サロゲートキーの最も一般的な用途は、IDENTITYとも呼ばれる自動インクリメントの「bigserial」列です。(実際、PostgreSQL 10 は Oracle と同様に IDENTITY 構造をサポートするようになりました。CREATE TABLE を参照してください。)

ただし、自動インクリメントする整数は代理キーとしては適切な選択ではないと思います。この意見は不人気なので説明しましょう。

シリアルキーの欠点:

すべてのシーケンスが 1 から始まり徐々に増加する場合、異なるテーブルの行は同じキー値を持つことになります。JOINこのオプションは理想的ではありません。たとえば、クエリで誤って定数が混同され、予期しない結果が返されることを防ぐために、テーブル内で互いに素なキーのセットを使用することが望ましいです。(あるいは、交差がないことを保証するために、異なる素数の倍数から各シーケンスを構築することもできますが、これはかなり手間がかかります。)
nextval() 今日の分散 SQL でシーケンスを生成する呼び出しを行うと、システム全体が適切に拡張できなくなります。
連続キーも使用されているデータベースからデータを使用すると、連続値はシステム全体で一意ではないため、競合が発生します。
哲学的な観点から見ると、数字の連続的な増加は、行の順序が暗示されていた古いシステムに関連付けられています。ここで行を並べ替えたい場合は、タイムスタンプ列などのデータ内で意味のあるものを使用して明示的に並べ替えます。それ以外の場合は、第 1 正規形に違反します。
理由は弱いですが、これらの短い識別子は誰かに伝えたくなるものです。

UUID

別のオプションを見てみましょう。ランダムパターンに従って生成された大きな整数 (128 ビット) を使用します。このような汎用一意識別子 (UUID) を生成するアルゴリズムは、2 つの異なるプロセッサーで同時に実行している場合でも、同じ値を 2 回選択する確率は非常に低くなります。

その場合、UUID を代理キーとして使用するのは自然な選択のように思えますよね。行に独自の方法でラベルを付けたい場合は、独自のラベルに勝るものはありません。

では、なぜ誰もが PostgreSQL でそれらを使用しないのでしょうか? これには人為的な理由がいくつかあり、回避できる論理的な理由が 1 つあります。私の主張を説明するためにベンチマークを示します。

まず、突飛な理由についてお話します。UUID はダッシュを付けた従来の 16 進表記で記述されているため、文字列であると考える人もいます5bd68e64-ff52-4f54-ace4-3cd9161c8b7f。実際、一部のデータベースにはコンパクト (128 ビット) uuid タイプがありませんが、PostgreSQL にはコンパクト (128 ビット) uuid タイプがあり、そのサイズは 2 です。つまり、bigintデータベース内の他の情報の量と比較すると、オーバーヘッドは無視できます。

UUID は扱いにくいという不当な非難もありますが、誰が UUID を発音し、入力し、読むのでしょうか? 人工キーが表示されるのは理にかなっていると述べましたが、(定義上) 誰もサロゲート UUID を表示すべきではありません。UUID は、開発者が psql で SQL コマンドを実行してシステムをデバッグすることによって処理される可能性がありますが、それだけです。また、開発者は、より便利なキーが指定されている場合は、それを使用して文字列を参照することもできます。

UUID の本当の問題は、高度にランダム化された値により、先行書き込みログ (WAL) への全ページ書き込みによる書き込み増幅が発生することです。ただし、実際のパフォーマンスの低下は、UUID 生成アルゴリズムに依存します。

書き込み増幅を測定してみましょう。実際のところ、問題は古いファイルシステムにあります。PostgreSQL がディスクに書き込むとき、ディスク上の「ページ」が変更されます。コンピュータの電源をオフにしても、ほとんどのファイルシステムは、データがディスクに安全に保存される前に書き込み成功を報告します。PostgreSQL がそのようなアクションが完了したと単純に認識すると、次回のシステム起動時にデータベースが破損します。

PostgreSQL は、ほとんどのオペレーティングシステム/ファイルシステム/ディスク構成の継続性を信頼できないため、データベースは変更されたディスクページの完全な状態を先行書き込みログに保存し、クラッシュの可能性から回復するために使用できます。UUID などの高度にランダム化された値のインデックス作成には通常、多数の異なるディスクページが必要となり、新しいエントリごとにページサイズ全体 (通常は 4 KB または 8 KB) が WAL に書き込まれることになります。いわゆるフルページライト（フルページライト、FPW）である。

一部の UUID 生成アルゴリズム (Twitter の「snowflake」や PostgreSQL の uuid-ossp 拡張機能の uuid_generate_v1() など) は、各マシン上で単調増加する値を生成します。このアプローチにより、書き込みがより少ないディスクページに統合され、FPW が削減されます。

5.6 結論と推奨事項

さまざまな種類のキーとその使用方法を説明したので、データベースでのキーの使用に関する推奨事項をリストしたいと思います。

各テーブルについて:

すべての自然キーを定義して宣言します。
デフォルト値がのUUID<table_name>_idタイプの代理キーを作成します。主キーとしてマークすることもできます。この識別子にテーブルの名前を追加すると、これが簡略化されます。の代わりに受け取ります。このキーをクライアントに渡さないでください。また、データベースの外部にまったく公開しないでください。uuid_generate_v1()JOINJOIN foo USING (bar_id)JOIN foo ON (foo.bar_id = bar.id)
を通過する中間テーブルの場合JOIN、すべての外部キー列を単一の複合主キーとして宣言します。
必要に応じて、URL またはその他の文字列参照表示で使用できる人工キーを追加します。Feistel グリッドまたはpg_bashids を使用して、自動インクリメントする整数をマスクします。
ON UPDATE RESTRICTサロゲート UUID を外部キーおよび人工外部キーとして使用して、カスケード制約を指定しますON UPDATE CASCADE。独自のロジックに基づいて自然キーを選択します。

このアプローチにより、内部キーの安定性が保証されると同時に、自然キーが許可され、さらには保護されます。さらに、目に見える人工キーは何にも取り付けられません。すべてを正しく理解したら、「主キー」だけにこだわることなく、キーを使用するあらゆる可能性を活用することができます。