クラスター化インデックスと非クラスター化インデックスは実際にはどういう意味ですか?

2009年08月09日に質問されました。  ·  閲覧回数 810k回  ·  ソース

P.K picture
2009年08月09日

私はDBへの露出が限られており、アプリケーションプログラマーとしてDBを使用しただけです。 ClusteredNon clustered indexesについて知りたいのですが。 私はグーグルで検索しました、そして私が見つけたものは:

クラスター化インデックスは、テーブル内のレコードが物理的に格納される方法を並べ替える特殊なタイプのインデックスです。 クラスター化インデックスのリーフノードには、データページが含まれています。 非クラスター化インデックスのリーフノードは、データページで構成されていません。

SOで見つけたのは、クラスター化インデックスと非クラスター化インデックスの違い

誰かがこれを平易な英語で説明できますか?

回答

Shiraz Bhaiji picture
2009年08月10日
1154

クラスター化インデックスを使用すると、行はインデックスと同じ順序でディスクに物理的に格納されます。 したがって、クラスター化インデックスは1つしか存在できません。

非クラスター化インデックスには、物理​​行へのポインターを持つ2番目のリストがあります。 クラスター化されていないインデックスを多数持つことができますが、新しいインデックスを作成するたびに、新しいレコードの書き込みにかかる時間が長くなります。

すべての列を取得する場合は、通常、クラスター化インデックスから読み取る方が高速です。 最初にインデックスに移動してからテーブルに移動する必要はありません。

データを再配置する必要がある場合、クラスター化インデックスを使用したテーブルへの書き込みは遅くなる可能性があります。

user151323 picture
2009年08月10日
606

クラスター化インデックスとは、実際に互いに近い値をディスクに格納するようにデータベースに指示していることを意味します。 これには、クラスター化インデックス値のある範囲に分類されるレコードの迅速なスキャン/取得という利点があります。

たとえば、CustomerとOrderの2つのテーブルがあります。

Customer
----------
ID
Name
Address

Order
----------
ID
CustomerID
Price

特定の顧客のすべての注文をすばやく取得する場合は、Orderテーブルの「CustomerID」列にクラスター化インデックスを作成することをお勧めします。 このようにして、同じCustomerIDを持つレコードは、ディスク上に互いに近くに物理的に保存され(クラスター化され)、取得が高速化されます。

PS CustomerIDのインデックスは明らかに一意ではないため、インデックスを「一意化」するために2番目のフィールドを追加するか、データベースに処理させる必要がありますが、それは別の話です。

複数のインデックスについて。 データが物理的にどのように配置されるかを定義するため、テーブルごとに1つのクラスター化インデックスのみを持つことができます。 例えを望むなら、たくさんのテーブルがある大きな部屋を想像してみてください。 これらのテーブルを配置して複数の行を形成することも、すべてをまとめて大きな会議テーブルを形成することもできますが、両方を同時に行うことはできません。 テーブルには他のインデックスを含めることができます。それらはクラスター化インデックスのエントリを指し、クラスター化インデックスは最終的に実際のデータの場所を示します。

Martin Smith picture
2014年06月29日
322

SQL Serverでは、クラスター化インデックスと非クラスター化インデックスの両方の行指向のストレージがBツリーとして編成されます。

enter image description here

画像ソース

クラスタ化インデックスと非クラスタ化インデックスとの間の主な違いは、クラスタ化インデックスのリーフレベルが表であることです。 これには2つの意味があります。

  1. クラスター化インデックスリーフページの行には、テーブル内の(スパースでない)列ごとに何かが常に含まれています(値または実際の値へのポインター)。
  2. クラスター化インデックスは、テーブルのプライマリコピーです。

非クラスター化インデックスは、 INCLUDE句(SQL Server 2005以降)を使用してすべての非キー列を明示的に含めることでポイント1を実行することもできますが、これらは2次表現であり、データの別のコピーが常に存在します(テーブル自体)。

CREATE TABLE T
(
A INT,
B INT,
C INT,
D INT
)

CREATE UNIQUE CLUSTERED INDEX ci ON T(A, B)
CREATE UNIQUE NONCLUSTERED INDEX nci ON T(A, B) INCLUDE (C, D)

上記の2つのインデックスはほぼ同じです。 キー列A, B値を含む上位レベルのインデックスページとA, B, C, Dを含むリーフレベルページ

データ行自体は1つの順序でしかソートできないため、テーブルごとに1つのクラスター化インデックスしか存在できません。

オンラインのSQLServerブックからの上記の引用は、多くの混乱を引き起こします

私の意見では、それはとしてはるかに良い言い回しになるでしょう。

クラスタ化インデックスのリーフレベルの行がテーブルの行であるため、テーブルごとに1つだけクラスタ化インデックスが存在する場合があります。

この本のオンライン見積もりは正しくありませんが、非クラスター化インデックスとクラスター化インデックスの両方の「ソート」は物理的ではなく論理的であることを明確にする必要があります。 リンクリストに従ってリーフレベルでページを読み取り、スロット配列の順序でページの行を読み取ると、インデックス行がソートされた順序で読み取られますが、物理的にはページがソートされない場合があります。 クラスター化インデックスを使用すると、行は常にインデックスキーと同じ順序でディスクに物理的に格納されるという一般的な考えが偽です。

これはばかげた実装になります。 たとえば、行が4GBのテーブルの中央に挿入された場合、SQL Serverは、新しく挿入された行用のスペースを確保するために、ファイル内の2GBのデータをコピーする必要ありませ

代わりに、ページ分割が発生します。 クラスター化インデックスと非クラスター化インデックスの両方のリーフレベルの各ページには、論理キー順に次のページと前のページのアドレス( File: Page )があります。 これらのページは、連続しているか、キー順になっている必要はありません。

たとえば、リンクされたページチェーンは1:2000 <-> 1:157 <-> 1:7053

ページ分割が発生すると、ファイルグループ内の任意の場所から新しいページが割り当てられます(小さなテーブルの場合は混合エクステント、そのオブジェクトに属する空でない均一エクステント、または新しく割り当てられた均一エクステントのいずれかから)。 ファイルグループに複数のファイルが含まれている場合、これは同じファイルに含まれていない可能性があります。

論理的な順序と隣接性が理想的な物理バージョンとどの程度異なるかは、論理的な断片化の程度です。

新しく作成された単一ファイルのデータベースで、次のように実行しました。

CREATE TABLE T
  (
     X TINYINT NOT NULL,
     Y CHAR(3000) NULL
  );

CREATE CLUSTERED INDEX ix
  ON T(X);

GO

--Insert 100 rows with values 1 - 100 in random order
DECLARE @C1 AS CURSOR,
        @X  AS INT

SET @C1 = CURSOR FAST_FORWARD
FOR SELECT number
    FROM   master..spt_values
    WHERE  type = 'P'
           AND number BETWEEN 1 AND 100
    ORDER  BY CRYPT_GEN_RANDOM(4)

OPEN @C1;

FETCH NEXT FROM @C1 INTO @X;

WHILE @@FETCH_STATUS = 0
  BEGIN
      INSERT INTO T (X)
      VALUES        (@X);

      FETCH NEXT FROM @C1 INTO @X;
  END

次に、ページレイアウトを確認しました

SELECT page_id,
       X,
       geometry::Point(page_id, X, 0).STBuffer(1)
FROM   T
       CROSS APPLY sys.fn_PhysLocCracker( %% physloc %% )
ORDER  BY page_id

結果はいたるところにありました。 キー順の最初の行(値1-下の矢印で強調表示)は、ほぼ最後の物理ページにありました。

enter image description here

インデックスを再構築または再編成して論理的順序と物理的順序の相関関係を高めることにより、断片化を削減または削除できます。

実行後

ALTER INDEX ix ON T REBUILD;

私は次のものを手に入れました

enter image description here

テーブルにクラスター化されたインデックスがない場合、それはヒープと呼ばれます。

非クラスター化インデックスは、ヒープまたはクラスター化インデックスのいずれかに構築できます。 それらには常に、ベーステーブルに戻る行ロケーターが含まれています。 ヒープの場合、これは物理的な行識別子(rid)であり、3つのコンポーネント(ファイル:ページ:スロット)で構成されます。 クラスター化インデックスの場合、行ロケーターは論理的です(クラスター化インデックスキー)。

後者の場合、非クラスター化インデックスにすでに自然にCIキー列がNCIキー列またはINCLUDE -d列として含まれている場合、何も追加されません。 それ以外の場合、欠落しているCIキー列はサイレントにNCIに追加されます。

SQL Serverは、キー列が両方のタイプのインデックスに対して一意であることを常に保証します。 ただし、一意として宣言されていないインデックスに対してこれが適用されるメカニズムは、2つのインデックスタイプ間で異なります。

クラスター化インデックスは、既存の行と重複するキー値を持つすべての行にuniquifier追加されます。 これは単なる昇順の整数です。

一意として宣言されていない非クラスター化インデックスの場合、SQLServerは非クラスター化インデックスキーに行ロケーターをサイレントに追加します。 これは、実際に重複している行だけでなく、すべての行に適用されます。

クラスター化された命名法とクラスター化されていない命名法は、列ストアのインデックスにも使用されます。 SQLServer列ストアの機能強化に関する論文

列ストアデータは実際にはどのキーでも「クラスター化」されていませんが、プライマリインデックスをクラスター化インデックスとして参照するという従来のSQLServerの規則を維持することにしました。

kmote picture
2016年10月27日
156

これは非常に古い質問だと思いますが、上記の細かい答えを説明するのに役立つアナロジーを提供したいと思いました。

クラスター化インデックス

公共図書館に足を踏み入れると、本がすべて特定の順序で配置されていることがわかります(おそらくデューイ十進法(DDS))。 これは、本の「クラスター化インデックス」に対応します。 必要な本のDDS#が005.7565 F736s場合、 001-099などのラベルが付いた本棚の行を見つけることから始めます。 (スタックの最後にあるこのエンドキャップ記号は、インデックスの「中間ノード」に対応します。)最終的に、 005.7450 - 005.7600というラベルの付いた特定の棚にドリルダウンし、 DDS#を指定すると、その時点で本が見つかります。

非クラスター化インデックス

しかし、本のDDS番号を記憶したまま図書館に来なかった場合は、支援するために2番目の索引が必要になります。 昔は、図書館の正面に「カードカタログ」と呼ばれる素晴らしい箪笥がありました。 その中には何千枚もの3x5カードがありました-本ごとに1枚、アルファベット順に(おそらくタイトルで)ソートされています。 これは「非クラスター化インデックス」に対応します。 これらのカードカタログは階層構造で編成されていたため、各引き出しには、含まれているカードの範囲(たとえば、 Ka - Kl 、つまり「中間ノード」)のラベルが付けられます。 もう一度、本が見つかるまでドリルインしますが、この場合、インデックス番号が付いたカードだけがあり

もちろん、図書館員がすべてのカードをコピーして、別のカードカタログで異なる順序で並べ替えることを妨げるものは何もありません。 (通常、このようなカタログは少なくとも2つあります。1つは作成者名でソートされ、もう1つはタイトルでソートされます。)原則として、これらの「非クラスター化」インデックスは必要な数だけ持つことができます。

Anirudh Sood picture
2013年01月21日
73

クラスター化インデックスと非クラスター化インデックスのいくつかの特徴を以下に示します。

クラスター化インデックス

  1. クラスタ化インデックスは、SQLテーブルの行を一意に識別するインデックスです。
  2. すべてのテーブルは、1つのクラスター化インデックスを持つことができます。
  3. 複数の列をカバーするクラスター化インデックスを作成できます。 例: create Index index_name(col1, col2, col.....)
  4. デフォルトでは、主キーを持つ列にはすでにクラスター化インデックスがあります。

非クラスター化インデックス

  1. 非クラスター化インデックスは、単純なインデックスのようなものです。 これらは、データを高速に取得するために使用されます。 一意のデータがあるかどうかわからない。
Dan Diplo picture
2009年08月10日
50

非常に単純で非技術的な経験則では、クラスター化インデックスは通常、主キー(または少なくとも一意の列)に使用され、非クラスター化は他の状況(おそらく外部キー)に使用されます。 。 実際、SQL Serverは、デフォルトで主キー列にクラスター化インデックスを作成します。 ご存知のとおり、クラスター化インデックスは、データがディスク上で物理的に並べ替えられる方法に関連しているため、ほとんどの状況で総合的に選択できます。

abdul rehman kk picture
2018年01月22日
47

クラスター化インデックス

クラスター化インデックスは、テーブル内のDATAの物理的な順序を決定します。このため、テーブルにはクラスター化インデックスが1つしかありません。

  • "辞書"他のインデックスは必要ありません、すでに単語によるインデックス

非クラスター化インデックス

非クラスター化インデックスは、ブックのインデックスに類似しています。データは1か所に保存されます。 インデックスは別の場所に格納されており、インデックスにはデータの格納場所へのポインタがあります。このため、テーブルには複数の非クラスタ化インデックスがあります。

  • 見つめている「化学の本」には、章の場所を指す別のインデックスがあり、「終了」には、共通の単語の場所を指す別のインデックスがあります。
marvelTracker picture
2017年08月28日
6

クラスター化インデックス

クラスター化インデックスは、キー値に基づいてデータ行をテーブルまたはビューに並べ替えて格納します。 これらは、インデックス定義に含まれる列です。 データ行自体は1つの順序でしかソートできないため、テーブルごとに1つのクラスター化インデックスしか存在できません。

テーブルのデータ行がソートされた順序で格納されるのは、テーブルにクラスター化インデックスが含まれている場合のみです。 テーブルにクラスター化インデックスがある場合、そのテーブルはクラスター化テーブルと呼ばれます。 テーブルにクラスター化インデックスがない場合、そのデータ行はヒープと呼ばれる順序付けられていない構造に格納されます。

非クラスター化

非クラスター化インデックスは、データ行とは別の構造になっています。 非クラスター化インデックスには非クラスター化インデックスのキー値が含まれ、各キー値エントリには、キー値を含むデータ行へのポインターがあります。 非クラスター化インデックスのインデックス行からデータ行へのポインターは、行ロケーターと呼ばれます。 行ロケーターの構造は、データページがヒープに格納されているかクラスター化されたテーブルに格納されているかによって異なります。 ヒープの場合、行ロケーターは行へのポインターです。 クラスター化テーブルの場合、行ロケーターはクラスター化インデックスキーです。

非クラスター化インデックスのリーフレベルに非キー列を追加して、既存のインデックスキー制限をバイパスし、完全にカバーされたインデックス付きクエリを実行できます。 詳細については、「含まれる列を使用してインデックスを作成する」を参照してください。 インデックスキーの制限の詳細については、SQLServerの最大容量の仕様を参照してください。

参照: https

xxks-kkk picture
2018年12月10日
4

データベースシステムから15.6.1から取られた「クラスタリングインデックス」に関する教科書の定義を提供させてください

クラスタリングインデックスについても説明します。

定義を理解するために、教科書で提供されている例15.10を見てみましょう。

属性aソートされ、この順序で格納され、ブロックにパックされたリレーションR(a,b)は、確実にクラスター化されます。 aインデックスはクラスタリングインデックスです。これは、指定されたa値a1に対して、 aその値を持つすべてのタプルが連続しているためです。 したがって、図15.14に示すように、 a値a1を含む最初と最後のブロックを除いて、ブロックにパックされているように見えます。 ただし、 abの値がない限り、 b値が固定されたタプルがファイル全体に分散されるため、bのインデックスがクラスタリングされる可能性は低くなります。非常に密接に相関しています。

Fig 15.14

定義は、データブロックがディスク上で連続している必要があることを強制しないことに注意してください。 検索キーを持つタプルが可能な限り少ないデータブロックにパックされていることだけを示しています。

関連する概念はクラスター化された関係です。 タプルがそれらのタプルを保持できる数のブロックにパックされている場合、リレーションは「クラスター化」されます。 言い換えると、ディスクブロックの観点から、異なるリレーションのタプルが含まれている場合、それらのリレーションをクラスター化することはできません(つまり、他のディスクブロックのリレーションのタプルを他のディスクブロックと交換することで、そのようなリレーションを格納する方法がより充実しています。タプルは、現在のディスクブロックのリレーションに属していません)。 明らかに、上記の例のR(a,b)はクラスター化されています。

2つの概念を結び付けるために、クラスター化された関係には、クラスター化インデックスと非クラスター化インデックスを含めることができます。 ただし、非クラスター化リレーションの場合、インデックスがリレーションの主キーの上に構築されていない限り、クラスター化インデックスは使用できません。

単語としての「クラスター」は、データベースストレージ側のすべての抽象化レベル(タプル、ブロック、ファイルの3つの抽象化レベル)にスパム送信されます。 「クラスター化ファイル」と呼ばれる概念。ファイル(ブロックのグループ(1つ以上のディスクブロック)の抽象化)に、1つのリレーションまたは異なるリレーションのタプルが含まれるかどうかを記述します。 ファイルレベルであるため、クラスタリングインデックスの概念とは関係ありません。

ただし、一部の教材では、クラスター化されたファイルの定義に基づいてクラスター化インデックスを定義する

ファイルの属性Aのインデックスは、次の場合にクラスタリングインデックスになります。属性値A = aのすべてのタプルがデータファイルに順番に(=連続して)格納される

タプルを連続して保存することは、「タプルは、それらのタプルを保持できる数のブロックにパックされます」と言うことと同じです(一方がファイルについて話し、もう一方がディスクについて話す場合はわずかな違いがあります)。 これは、タプルを連続して保存することが、「それらのタプルを保持できる限りのブロックに詰める」ことを実現する方法だからです。

Abhishek picture
2018年11月19日
3

クラスター化インデックス:主キー制約は、クラスター化インデックスがテーブルにまだ存在しない場合、クラスター化インデックスを自動的に作成します。 クラスタ化インデックスの実際のデータは、インデックスのリーフレベルで保存できます。

非クラスター化インデックス:非クラスター化インデックスの実際のデータはリーフノードで直接検出されません。代わりに、実際のデータを指す行ロケーターの値しかないため、検索するために追加の手順を実行する必要があります。 非クラスター化インデックスは、クラスター化インデックスとしてソートできません。 テーブルごとに複数の非クラスター化インデックスが存在する可能性があります。実際には、使用しているSQLサーバーのバージョンによって異なります。 基本的に、SQL Server 2005では249の非クラスター化インデックスが許可され、2008、2016などの上記のバージョンでは、テーブルごとに999の非クラスター化インデックスが許可されます。