ほぼすべてのテーブルで GUID を主キーとして使用するアプリケーションがあり、GUID を主キーとして使用するとパフォーマンスに問題があると読みました。正直なところ、問題は見たことがありませんが、新しいアプリケーションを開始しようとしており、引き続き GUID を主キーとして使用したいのですが、複合主キー (GUID とおそらく別のフィールド) を使用することを考えていました。
GUID を使用する理由は、「production」、「test」、「dev」データベースなどの異なる環境がある場合や、データベース間でデータを移行する場合に、管理しやすく便利だからです。
Entity Framework 4.3 を使用し、データベースに挿入する前にアプリケーション コードで Guid を割り当てたいと思います。(つまり、SQL で Guid を生成させたくありません)。
このアプローチに関連するパフォーマンスの低下を回避するために、GUID ベースの主キーを作成するためのベスト プラクティスは何ですか?
ベストアンサー1
GUID は主キーとして自然な選択のように思われるかもしれません。どうしても必要な場合は、テーブルの PRIMARY KEY に GUID を使用することもできます。ただし、GUID 列をクラスタリング キーとして使用することは絶対にお勧めしません。SQL Server では、特に指示しない限り、デフォルトでこれを行います。
実際には、次の 2 つの問題を区別する必要があります。
主キーは
INT
論理的な構成で、テーブル内のすべての行を一意かつ確実に識別する候補キーの 1 つです。 、 、文字列など、何でもかまいませんGUID
。シナリオに最も適したものを選択してください。クラスタリングキー(テーブル上の「クラスタ化インデックス」を定義する列) - これは物理的なストレージに関連するものであり、ここでは、小さく、安定しており、増加し続けるデータ型が最適な選択肢です (
INT
または、BIGINT
デフォルトのオプションです)。
デフォルトでは、SQL Server テーブルの主キーはクラスタリング キーとしても使用されますが、そのようにする必要はありません。以前の GUID ベースの主キー/クラスタリング キーを 2 つの別々のキー (GUID の主 (論理) キーと別の列のクラスタリング (順序付け) キー) に分割すると、パフォーマンスが大幅に向上することが個人的にわかりましたINT IDENTITY(1,1)
。
としてキンバリー・トリップ- インデックスの女王 - や他の人たちが何度も述べているGUID
ように、クラスタリング キーは最適ではありません。ランダム性のため、ページとインデックスの断片化が大量に発生し、全体的にパフォーマンスが低下します。
newsequentialid()
はい、 SQL Server 2005 以降にはあることは知っていますが、それでも完全にシーケンシャルではないため、同じ問題が発生します (ただし、問題はGUID
それほど顕著ではありません)。
次に、考慮すべき別の問題があります。テーブルのクラスタリング キーは、テーブルのすべての非クラスタ化インデックスのすべてのエントリにも追加されるため、できるだけ小さくする必要があります。通常、INT
20 億行を超える は、ほとんどのテーブルで十分なはずです。また、 をクラスタリング キーとして使用する場合と比較してGUID
、ディスクとサーバー メモリのストレージを数百メガバイト節約できます。
クイック計算 - INT
vs. をGUID
プライマリおよびクラスタリング キーとして使用:
- 1,000,000 行の基本テーブル (3.8 MB 対 15.26 MB)
- 6 つの非クラスター化インデックス (22.89 MB 対 91.55 MB)
合計: 25 MB 対 106 MB - これは 1 つのテーブルのみで発生します。
もう少し考えさせられるものがあります。Kimberly Tripp による素晴らしい記事です。ぜひ読んで、もう一度読んで、理解してください。まさに、SQL Server のインデックス作成の福音です。
- 主キーおよび/またはクラスター化キーとしての GUID
- クラスター化インデックスの議論は続く
- ますます増え続けるクラスタリング キー - クラスター化インデックスの議論が再び!
- ディスク容量は安いですが、それは問題ではありません。
PS: もちろん、数百行または数千行だけを扱っている場合、これらの引数のほとんどは実際にはあまり影響しません。ただし、数万行または数十万行、あるいは数百万行を数え始めると、これらのポイントは非常に重要になり、理解することが非常に重要になります。
更新:PKGUID
列を主キー (クラスタリング キーではない) として設定し、別の列MYINT
( INT IDENTITY
) をクラスタリング キーとして設定する場合は、次のようにします。
CREATE TABLE dbo.MyTable
(PKGUID UNIQUEIDENTIFIER NOT NULL,
MyINT INT IDENTITY(1,1) NOT NULL,
.... add more columns as needed ...... )
ALTER TABLE dbo.MyTable
ADD CONSTRAINT PK_MyTable
PRIMARY KEY NONCLUSTERED (PKGUID)
CREATE UNIQUE CLUSTERED INDEX CIX_MyTable ON dbo.MyTable(MyINT)
基本的には、制約を明示的に伝えるだけです(そうでない場合は、デフォルトでクラスター化インデックスとして作成されます)。次に、次のように定義される2番目のインデックスを作成します。PRIMARY KEY
NONCLUSTERED
CLUSTERED
これは機能します。パフォーマンスのために「再設計」する必要がある既存のシステムがある場合、これは有効なオプションです。新しいシステムの場合、ゼロから開始し、レプリケーション シナリオではない場合は、常にID INT IDENTITY(1,1)
クラスター化された主キーとして選択します。これは他のものよりもはるかに効率的です。