SQL => インデックス

前書き

索引は、データベースの問合せを最適化するために、特定の順序で配置された表の内容へのポインタを含むデータ構造です。それらはブックの索引に似ています。ここでは、ページ（表の行）はページ番号で索引付けされています。

いくつかのタイプの索引が存在し、表に作成することができます。クエリのWHERE句、JOIN句、またはORDER BY句で使用される列にインデックスが存在する場合、クエリのパフォーマンスが大幅に向上します。

備考

索引は、列に基づいて表の行をソートすることによって読取り照会を高速化する方法です。

例のような小さなデータベースでは、索引の効果は目に見えませんが、行数が多いとパフォーマンスが大幅に向上します。テーブルのすべての行をチェックする代わりに、サーバーはインデックスに対してバイナリ検索を実行できます。

インデックスを作成する際のトレードオフは、書き込み速度とデータベースサイズです。インデックスを格納するにはスペースが必要です。また、INSERTが完了するか列が更新されるたびに、索引を更新する必要があります。これは、SELECTクエリでテーブル全体をスキャンするのと同じくらい高価な操作ではありませんが、それはまだ留意しておくべきことです。

インデックスの作成

CREATE INDEX ix_cars_employee_id ON Cars (EmployeeId);

これにより、テーブルCarsの列EmployeeIdの索引が作成されます。このインデックスは、次のように、サーバーにEmployeeIdの値をソートまたは選択するように要求するクエリの速度を向上させます。

SELECT * FROM Cars WHERE EmployeeId = 1

索引には、次のように複数の列を含めることができます。

CREATE INDEX ix_cars_e_c_o_ids ON Cars (EmployeeId, CarId, OwnerId);

この場合、索引は、一連の条件が同じ方法で順序付けされている場合は、すべての包含列をソートまたは選択するように照会する照会に役立ちます。つまり、データを取得するときに、テーブル全体を調べるのではなく、インデックスを使用して取得する行を見つけることができます。

たとえば、次の場合は2番目のインデックスを使用します。

SELECT * FROM Cars WHERE EmployeeId = 1 Order by CarId DESC

ただし、順序が異なる場合、インデックスには次のような利点がありません。

SELECT * FROM Cars WHERE OwnerId = 17 Order by CarId DESC

OwnerId = 17アイテムを見つけるために、データベースがEmployeeIdとCarIDのすべての値にわたってインデックス全体を取得しなければならないため、インデックスはそれほど有用ではありません。

（インデックスはまだ使用されている可能性がありますが、クエリオプティマイザがインデックスを取得してOwnerIdでフィルタリングし、必要な行のみを取得することが、テーブルが大きい場合は特に完全なテーブルを取得するよりも高速です）

クラスタ化された、一意の、ソート済みのインデックス

索引には、作成時または既存の索引を変更することによって設定できるいくつかの特性があります。

CREATE CLUSTERED INDEX ix_clust_employee_id ON Employees(EmployeeId, Email);

上記のSQL文は、Employeesに新しいクラスタ化インデックスを作成します。クラスタ化インデックスは、テーブルの実際の構造を決定するインデックスです。テーブル自体がインデックスの構造に一致するようにソートされます。これは、表にクラスター化された索引が1つしかないことを意味します。クラスタード・インデックスがすでにテーブルに存在する場合、上記のステートメントは失敗します。（クラスタ化されたインデックスのないテーブルは、ヒープとも呼ばれます）。

CREATE UNIQUE INDEX uq_customers_email ON Customers(Email);

これにより、テーブルCustomersの列Emailの一意のインデックスが作成されます。このインデックスは、通常のインデックスのようにクエリを高速化するとともに、その列のすべての電子メールアドレスを一意にするよう強制します。行が一意でない電子メール値で挿入または更新された場合、挿入または更新はデフォルトで失敗します。

CREATE UNIQUE INDEX ix_eid_desc ON Customers(EmployeeID);

これにより、EmployeeIDが一意でなければならないテーブル制約も作成される、Customersのインデックスが作成されます。（列が現在一意でない場合は失敗します。この場合は、IDを共有する従業員がいる場合です）。

CREATE INDEX ix_eid_desc ON Customers(EmployeeID Desc);

これにより、降順でソートされた索引が作成されます。デフォルトでは、インデックスは（MSSQLサーバでは少なくとも）昇順ですが、変更することができます。

一意のインデックスを挿入する

UPDATE Customers SET Email = "[email protected]" WHERE id = 1;

Customersの Email列にユニークなインデックスが設定されていると、これは失敗します。ただし、この場合、別の動作を定義できます。

UPDATE Customers SET Email = "[email protected]" WHERE id = 1 ON DUPLICATE KEY;

SAP ASE：ドロップインデックス

このコマンドは、テーブル内のインデックスを削除します。これはSAP ASEサーバで機能します。

構文：

DROP INDEX [table name].[index name]

例：

DROP INDEX Cars.index_1

並べ替えられたインデックス

索引を検索する方法でソートされた索引を使用すると、検索時にSELECT文は追加ソートを行いません。

CREATE INDEX ix_scoreboard_score ON scoreboard (score DESC);

クエリを実行すると

SELECT * FROM scoreboard ORDER BY score DESC;

データベースシステムは、その順序でインデックスルックアップを行うことができるので、追加ソートは行いません。

索引の削除、または索引の無効化と再構築

DROP INDEX ix_cars_employee_id ON Cars;

DROPコマンドを使ってインデックスを削除することができます。この例ではしますDROPテーブル車の上にix_cars_employee_idと呼ばれる指標を。

これにより、インデックスが完全に削除され、インデックスがクラスタ化されている場合は、クラスタリングが削除されます。索引を再作成せずに再構築することはできません。遅くて計算コストがかかる可能性があります。また、インデックスを無効にすることもできます。

ALTER INDEX ix_cars_employee_id ON Cars DISABLE;

これにより、索引に関するメタデータとともに構造体を保持することができます。

重要な点として、これは索引統計を保持しているため、変更を容易に評価することができます。保証があれば、索引は後で完全に再作成するのではなく、再構築することができます。

ALTER INDEX ix_cars_employee_id ON Cars REBUILD;

NULLを許可する一意索引

CREATE UNIQUE INDEX idx_license_id 
   ON Person(DrivingLicenseID) WHERE DrivingLicenseID IS NOT NULL
GO

このスキーマは0..1の関係を可能にします - 人々はゼロまたは1つの運転免許を持つことができ、各免許は1人にしか属しません

インデックスの再構築

Bツリー索引は、データの更新/削除/挿入のために断片化することがあります。 SQLServerの用語では、内部（インデックスページは半分空です）と外部（論理ページ順序は物理的順序に対応していません）を持つことができます。索引の再構築は、索引の削除と再作成に非常によく似ています。

インデックスを再構築するには

ALTER INDEX index_name REBUILD;

デフォルトでは、索引の再構築はオフライン操作であり、表をロックしてDMLを防止しますが、多くのRDBMSではオンライン再構築が可能です。また、一部のDBベンダーは、 REORGANIZE （SQLServer）やCOALESCE / SHRINK SPACE （Oracle）などのインデックス再構築の代替案を提供しています。

クラスタ化インデックス

クラスタ化インデックスを使用する場合、テーブルの行は、クラスタ化インデックスが適用されている列によってソートされます。したがって、2つの異なる列で表を並べ替えることができないため、表にはクラスタ化された索引は1つしか存在できません。

一般に、大きなデータ・テーブルでの読込みを実行する場合は、クラスタード・インデックスを使用するのが最善です。クラスタード・インデックスの欠点は、表に書き込む際にデータを再編成する必要がある（頼りになる）ことです。

表にクラスタード・インデックスを作成する例Employee_Surname列の従業員：

CREATE CLUSTERED INDEX ix_employees_name ON Employees(Employee_Surname);

クラスタ化されていないインデックス

ノンクラスタード・インデックスは、テーブルとは別に保存されます。この構造体の各インデックスは、それが表すテーブル内の行へのポインタを含みます。

このポインタは行ロケータと呼ばれます。行ロケーターの構造は、データ・ページがヒープまたはクラスター表に保管されているかどうかによって異なります。ヒープの場合、行ロケータは行へのポインタです。クラスタ化された表の場合、行ロケータはクラスタ化された索引キーです。

表Employeesおよび列Employee_Surnameに非クラスタ化インデックスを作成する例：

CREATE NONCLUSTERED INDEX ix_employees_name ON Employees(Employee_Surname);

テーブルに複数のノンクラスタード・インデックスが存在することがあります。読み取り操作は、クラスタ化インデックスよりもクラスタ化されていないインデックスでは、最初にインデックスを作成する必要があるため、テーブルよりも遅くなります。ただし、書き込み操作には制限はありません。

部分インデックスまたはフィルタリングインデックス

SQL ServerとSQLiteでは、列のサブセットだけでなく、行のサブセットも含む索引を作成できます。

order_state_idがfinished（2）にorder_state_id equal 、 order_state_id equalがstarted（1）にorder_state_id equal安定した注文量を持つ注文が常に増加していると考えてください。

あなたのビジネスが次のようなクエリを利用している場合：

SELECT id, comment
  FROM orders
 WHERE order_state_id =  1
   AND product_id = @some_value;

部分的なインデックス作成では、未完成の注文だけを含めてインデックスを制限できます。

CREATE INDEX Started_Orders
          ON orders(product_id)
       WHERE order_state_id = 1;

このインデックスは、フィルタリングされていないインデックスよりも小さくなり、スペースが節約され、インデックスの更新コストが削減されます。

Modified text is an extract of the original Stack Overflow Documentation

ライセンスを受けた CC BY-SA 3.0

所属していない Stack Overflow

SQL
インデックス

サーチ…

前書き

備考