Mysqlシリーズ(5)インデックス関数

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

理論的には、データテーブル内のフィールドごとにインデックスを構築することは可能ですが、MySQL では同じデータテーブル内のインデックスの総数が 16 に制限されています。

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

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

2.制限事項

WHERE 句のクエリ条件に不等号 (WHERE 列 !=) がある場合、MySQL はインデックスを使用できません。同様に、WHERE 句のクエリ条件に関数 (WHERE DAY (column) =) が使用されている場合、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 (列A、列B) インデックスなど、複数のデータ列をカバーできます。この種のインデックスの特徴は、MySQL がそのようなインデックスを選択的に使用できることです。クエリ操作で columnA データ列のインデックスのみが必要な場合は、複合インデックス INDEX(columnA, columnB) を使用できます。ただし、この使用法は、複合インデックスで最初にランク付けされているデータ列の組み合わせにのみ適用されます。たとえば、INDEX (A, B, C) は、A または (A, B) へのインデックスとして使用できますが、B、C または (B, C) へのインデックスとしては使用できません。

インデックスの長さ

CHAR および VARCHAR 型のデータ列のインデックスを定義する場合、インデックスの長さを指定した文字数に制限できます (この数は、このフィールドで許可される最大文字数より小さくなければなりません) 。この利点は、サイズが小さく、検索速度が速いインデックス ファイルを生成できることです。ほとんどのアプリケーションでは、データベース内の文字列データは主にさまざまな名前に基づいています。インデックスの長さを 10 ～ 15 文字に設定するだけで、検索範囲をいくつかのデータ レコードに絞り込むことができます。 BLOB および TEXT タイプのデータ列のインデックスを作成する場合、インデックスの長さに制限を設ける必要があります。MySQL で許可される最大インデックスは、フルテキスト インデックスのテキスト フィールドに対する通常のインデックスであり、次の場所に表示される文字列のみを高速化できます。フィールド コンテンツの先頭 (つまり、フィールド コンテンツの先頭の文字) を使用して検索操作を実行します。フィールドに複数の単語または複数の単語で構成される大きなテキストが格納されている場合、通常のインデックスはほとんど役に立ちません。この取得は多くの場合 の形式で行われますが、これは MySQL にとって複雑であり、処理する必要があるデータの量が多い場合には応答時間が長くなる可能性があります。

このような状況では、全文インデックス (full-textindex) が本領を発揮します。このタイプのインデックスを生成する場合、MySQL はテキスト内に出現するすべての単語のリストを作成し、クエリ操作ではこのリストを使用して関連するデータ レコードを取得します。フルテキスト インデックスはデータ テーブルと一緒に作成することも、将来必要になったときに次のコマンドを使用して追加することもできます:

ALTER TABLE tablename ADD FULLTEXT (column1, column2) フルテキスト インデックスを使用すると、 SELECT クエリ コマンドを使用して、1 つ以上の指定された単語を含むデータ レコードを取得できます。このタイプのクエリ コマンドの基本構文は次のとおりです:

SELECT * FROM tablename

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 がこのクエリの実行時に一時データ テーブルを作成する必要がある場合、追加の列に usingtemporary という単語が表示されます。

上記は Mysql シリーズ (5) インデックス関数の内容です。さらに関連する内容については、PHP 中国語 Web サイト (www.php.cn) に注目してください。