SQL インデックスの作成（CREATE INDEX 文）

このチュートリアルでは、テーブル上にインデックスを作成してデータベースのパフォーマンスを向上させる方法を学びます。

インデックスとは何ですか？

インデックスは、テーブルに関連付けられたデータ構造で、1つのまたは複数の列（インデックスキー）の値に基づいてテーブルの行への高速アクセスを提供するものです。

仮にあなたのデータベースにcustomers表，そして、以下の文を使って名前がアルファベットAで始まるすべての顧客を見つけたいです。

SELECT　cust_id,　cust_name,　address　FROM　customers　
WHERE　cust_name　LIKE　'A%';

このような顧客を見つけるために、サーバーは以下のように}}customersテーブルの各行をスキャンし、名前の列の内容を確認します。たとえ行が数行しかないテーブルであれば、通常は正常に動作しますが、例えばテーブルに一百万行が含まれている場合、クエリの回答に必要な時間を想像してみてください。この場合、テーブルにインデックスを適用することで処理速度を向上させることができます。

インデックスの作成

以下のCREATE INDEX文を使用してインデックスを作成できます：

CREATE INDEX index_name ON table_name (column_name);

例えば、顧客テーブルのname列にインデックスを作成するには、以下を使用します：

CREATE INDEX cust_name_idx ON customers (cust_name);

デフォルトでは、インデックスは重複のエントリを許可し、エントリを昇順にソートします。ユニークなインデックスエントリを要求する場合は、CREATEの後にUNIQUEキーワードを追加します。以下のようになります：

CREATE UNIQUE INDEX cust_name_idx　
ON customers (cust_name);

MySQLでは、以下のように特定のテーブル上の利用可能なインデックスを確認できます：

mysql> SHOW INDEXES FROM customers \G

多列インデックスの作成

例えば、データベースに既に命名されたテーブルがあれば、ユーザー列を持つfirst_nameおよびlast_nameそして、これらの列を使用してよくアクセスする場合は、以下の図のように、2つの列のインデックスを一緒に構築してパフォーマンスを向上させることができます：

CREATE INDEX user_name_idx ON users (first_name, last_name);

インデックスの欠点

インデックスは慎重に作成する必要があります。なぜなら、テーブルに行を追加、更新、または削除するたびに、そのテーブル上のすべてのインデックスを変更する必要があるからです。したがって、インデックスが多いほど、サーバーが行う作業が増え、最終的にはパフォーマンスが低下するためです。

インデックスを作成する際に守ることができるいくつかの基本的な原則は以下の通りです：

• よく使用されるデータの検索にインデックスを作成してください。

• 検索キーとして使用されていない列にはインデックスを作成しないでください。

• 結合性能を向上させるために使用されるインデックス列。

• NULL値が含まれる列を多く含まないようにしてください。

同様に、小さなテーブルにはインデックスが不要です。なぜなら、小さなテーブルの場合、サーバーがテーブルをスキャンするのが通常、インデックスを確認するよりも速いからです。

インデックス削除

不要なインデックスを削除するために以下の文を使用できます。

DROP INDEX index_name ON table_name;

以下の文は、customersテーブルからインデックス削除cust_name_idx。

DROP INDEX cust_name_idx ON customers;

さらに、テーブルを削除します、その場合、すべての関連インデックスも削除されます。