Mysqlのindex_MySQLの種類とメリット・デメリットを詳しく解説

Index は特別なファイル (InnoDB データ テーブルのインデックスはテーブル スペースのコンポーネントです) であり、データ テーブル内のすべてのレコードへの参照ポインターが含まれています。
注:
[1] インデックスは万能ではありません!インデックスを使用すると、データの取得操作は高速化できますが、データの変更操作は遅くなります。データ レコードが変更されるたびに、インデックスを更新する必要があります。特定のプログラムのこの欠点を補うために、多くの SQL コマンドには DELAY_KEY_WRITE 項目があります。このオプションの機能は、各新しいレコードが挿入され、各既存のレコードがこのコマンドによって変更された直後に MySQL がインデックスを更新しないようにすることです。インデックスの更新は、すべてのレコードが挿入/変更されるまで待機します。多くの新しいレコードをデータ テーブルに挿入する必要がある状況では、DELAY_KEY_WRITE オプションの役割は非常に明白です。
[2] さらに、インデックスはハード ディスク上でかなりのスペースを占有します。したがって、最も頻繁にクエリされ、最も頻繁に並べ替えられるデータ列のみにインデックスを付ける必要があります。データ列に多くの重複コンテンツが含まれている場合、インデックスを作成しても実際的な効果はほとんどないことに注意してください。
理論的には、データ テーブル内のフィールドごとにインデックスを構築することは可能ですが、MySQL では、同じデータ テーブル内のインデックスの総数が 16 に制限されています。

1. InnoDB データテーブルのインデックス

MyISAM データ テーブルと比較して、インデックスは InnoDB データにとってはるかに重要です。 InnoDB データ テーブルでは、インデックスが InnoDB データ テーブルにとってはるかに重要です。 InnoDB データ テーブルでは、インデックスはデータ レコードの検索に役割を果たすだけでなく、データ行レベルのロック メカニズムの基礎でもあります。 「データ行レベルのロック」とは、トランザクション操作の実行中に処理中の個々のレコードをロックして、他のユーザーがアクセスできないようにすることを意味します。このロックは、SELECT...LOCK IN SHARE MODE、SELECT...FOR UPDATE コマンド、INSERT、UPDATE、および DELETE コマンドに影響します (ただし、これらに限定されません)。
効率上の理由から、InnoDB データ テーブルの行レベルのロックは、実際にはデータ テーブル自体ではなくインデックスに対して発生します。明らかに、データ行レベルのロック メカニズムは、関連するデータ テーブルにロックに適したインデックスがある場合にのみ有効になります。

2. 制限事項

WEHERE 句のクエリ条件に不等号 (WHERE coloum != ...) がある場合、MySQL はインデックスを使用できません。
同様に、関数 (WHERE DAY(column) = ...) が WHERE 句のクエリ条件で使用されている場合、MySQL はインデックスを使用できません。
JOIN 操作 (複数のデータ テーブルからデータを抽出する必要がある場合) では、MySQL は主キーと外部キーのデータ型が同じ場合にのみインデックスを使用できます。
比較演算子 LIKE および REGEXP が WHERE 句のクエリ条件で使用されている場合、MySQL は検索テンプレートの最初の文字がワイルドカード文字でない場合にのみインデックスを使用できます。たとえば、クエリ条件が LIKE 'abc%' の場合、MySQL はインデックスを使用しますが、クエリ条件が LIKE '%abc' の場合、MySQL はインデックスを使用しません。
ORDER BY オペレーションでは、MySQL はソート条件がクエリ条件式ではない場合にのみインデックスを使用します。 (ただし、複数のデータテーブルを含むクエリでは、インデックスが使用可能であっても、それらのインデックスは ORDER BY の高速化に効果がありません)
データ列に多くの重複値が含まれている場合、インデックスが付けられていても、良い結果は得られません。たとえば、データ列に「0/1」や「Y/N」などの値のみが含まれる場合、インデックスを作成する必要はありません。

通常インデックス、一意インデックス、プライマリインデックス

1. 通常のインデックス

通常のインデックス (キーワード KEY または INDEX で定義されたインデックス) の唯一のタスクは、データへのアクセスを高速化することです。したがって、インデックスは、クエリ条件 (WHERE 列 = ...) または並べ替え条件 (ORDER BY 列) で最も頻繁に出現するデータ列に対してのみ作成する必要があります。可能な限り、インデックスを作成するには、最も整然とした最もコンパクトなデータを含むデータ列 (整数型のデータ列など) を選択する必要があります。

2. ユニークなインデックス
通常のインデックスでは、インデックス付きデータ列に重複した値を含めることができます。たとえば、人々は同じ名前を持つ可能性があるため、同じ「従業員プロフィール」データ テーブルに同じ名前が 2 回以上現れることがあります。
特定のデータ列に互いに異なる値のみが含まれると判断できる場合は、このデータ列のインデックスを作成するときに、キーワード UNIQUE を使用して一意のインデックスとして定義する必要があります。この利点は次のとおりです。まず、MySQL によるこのインデックスの管理が簡素化され、インデックスがより効率的になります。次に、新しいレコードがデータ テーブルに挿入されるときに、MySQL が新しいレコード内のこのフィールドの値を自動的にチェックします。このフィールドがすでにレコードに存在している場合、MySQL は新しいレコードの挿入を拒否します。言い換えれば、一意のインデックスによりデータ レコードの一意性が保証されます。実際、多くの場合、一意のインデックスを作成する目的は、アクセス速度を上げることではなく、単にデータの重複を避けることです。

3. メインインデックス

これまでに何度も強調しましたが、主キー フィールドに対してインデックスを作成する必要があります。このインデックスはいわゆる「主インデックス」です。プライマリ インデックスとユニーク インデックスの唯一の違いは、プライマリ インデックスを定義するときに使用されるキーワードが UNIQUE ではなく PRIMARY であることです。

4. 外部キーインデックス

外部キーフィールドに外部キー制約が定義されている場合、MySQL は内部インデックスを定義して、最も効率的な方法で外部キー制約を管理および使用できるようにします。

5. 複合インデックス

インデックスは、INDEX(columnA, columnB) インデックスなど、複数のデータ列をカバーできます。このインデックスの特徴は、MySQL がこのようなインデックスを選択的に使用できることです。クエリ操作で columnA データ列のインデックスのみを使用する必要がある場合は、複合インデックス INDEX (columnA、columnB) を使用できます。ただし、この使用法は、複合インデックスで最初にランク付けされているデータ列の組み合わせにのみ適用されます。たとえば、INDEX(A, B, C) は、A または (A, B) のインデックスとして使用できますが、B、C または (B, C) のインデックスとして使用することはできません。

6. インデックスの長さ

CHAR および VARCHAR 型のデータ列のインデックスを定義する場合、インデックスの長さを指定した文字数に制限できます (この数は、このフィールドで許可される最大文字数より小さくなければなりません)。この利点は、サイズが小さく、検索速度が速いインデックス ファイルを生成できることです。ほとんどのアプリケーションでは、データベース内の文字列データは主にさまざまな名前に基づいています。インデックスの長さを 10 ～ 15 文字に設定するだけで、検索範囲をいくつかのデータ レコードに絞り込むことができます。
BLOB および TEXT タイプのデータ列のインデックスを作成する場合、インデックスの長さを制限する必要があります。MySQL で許可される最大インデックス長は 255 文字です。
全文インデックス

テキスト フィールドの通常のインデックスは、フィールド コンテンツの先頭に表示される文字列 (つまり、フィールド コンテンツの先頭の文字) の取得を高速化することしかできません。フィールドに複数の単語または複数の単語で構成される大きなテキストが格納されている場合、通常のインデックスはほとんど役に立ちません。この種の取得は、LIKE %word% の形式で行われることが多く、MySQL では非常に複雑で、処理する必要があるデータの量が多い場合、応答時間が非常に長くなります。
このような場合に、全文インデックスが役立ちます。このタイプのインデックスを生成するとき、MySQL はテキスト内に出現するすべての単語のリストを作成し、クエリ操作はこのリストに基づいて関連するデータ レコードを取得します。全文インデックスはデータテーブルと一緒に作成することも、後で必要に応じて使用することもできます

次のコマンドを追加します:
ALTER TABLE テーブル名 ADD FULLTEXT(column1, column2)

フルテキスト インデックスを使用すると、SELECT クエリ コマンドを使用して、1 つ以上の指定された単語を含むデータ レコードを取得できます。以下は、このタイプのクエリ コマンドの基本構文です:
SELECT * FROM テーブル名
WHERE MATCH(column1, column2) AGAINST(‘word1’, ‘word2’, ‘word3’)

上記のコマンドは、column1 フィールドと column2 フィールドに word1、word2、word3 を含むすべてのデータ レコードをクエリします。

注: InnoDB データ テーブルはフルテキスト インデックス作成をサポートしていません。

クエリとインデックスの最適化

データベースに十分なテストデータがある場合にのみ、そのパフォーマンステスト結果が実際の参考値になります。テスト データベースにデータ レコードが数百しかない場合、多くの場合、最初のクエリ コマンドの実行後にすべてのデータ レコードがメモリにロードされるため、インデックスが使用されているかどうかに関係なく、後続のクエリ コマンドが非常に高速に実行されます。データベースのパフォーマンス テストの結果は、データベース内のレコード数が 1,000 を超え、データの合計量が MySQL サーバーの合計メモリを超えた場合にのみ意味を持ちます。

インデックスの作成にどのデータ列を使用すればよいかわからない場合、多くの場合、EXPLAIN SELECT コマンドから助けを得ることができます。これは実際には、通常の SELECT コマンドの前に EXPLAIN キーワードを付けているだけです。このキーワードを使用すると、MySQL は SELECT コマンドを実行せずに分析します。 MySQL は、クエリの実行プロセスや使用されたインデックス (存在する場合) などの情報をテーブル形式でリストします。
EXPLAIN コマンドの出力では、列 1 はデータベースから読み取られたデータ テーブルの名前で、読み取られた順序で並べられています。 type 列は、このデータ テーブルと他のデータ テーブルの間の関連付け関係 (JOIN) を指定します。さまざまなタイプのリレーションシップの中で、最も効率的なのは system で、次に const、eq_ref、ref、range、index、All が続きます (All は、上位レベルのデータ テーブルの各レコードに対応し、このデータはテーブル内のすべてのレコードでなければなりません)読み取られる - これは多くの場合、インデックスを使用することで回避できます)。

possible_keys データ列は、MySQL がデータ レコードを検索するときに使用できるさまざまなインデックスを提供します。キー データ列は、MySQL によって実際に選択されるインデックスです。このインデックスの長さは key_len データ列にバイト単位で示されます。たとえば、INTEGER データ列のインデックスの場合、バイト長は 4 になります。複合インデックスが使用されている場合は、そのインデックスのどの部分が MySQL によって使用されているかを key_len データ列で確認することもできます。一般的なルールとして、key_len データ列の値が小さいほど優れています (高速であることを意味します)。
ref データ列は、リレーションシップ内の別のデータ テーブルのデータ列の名前を示します。行データ列は、このクエリの実行時に MySQL がこのデータ テーブルから読み取ると予想されるデータ行の数です。行データ列のすべての数値の積により、このクエリが処理する必要がある組み合わせの数がわかります。
最後に、追加のデータ列には、JOIN 操作に関する詳細情報が表示されます。たとえば、MySQL がこのクエリの実行時に一時データ テーブルを作成する必要がある場合、追加の列に一時を使用する単語が表示されます。

以上がこの記事の全内容です。皆様の学習に少しでもお役に立てれば幸いです。よろしくお願いいたします。