English | 简体中文 | 繁體中文 | Русский язык | Français | Español | Português | Deutsch | 日本語 | 한국어 | Italiano | بالعربية
インデックスこれは特別なファイルです（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 column != …)がある場合、MySQLはインデックスを使用することができません。
同様に、WHERE子句のクエリ条件に関数（WHERE DAY(column) = …）を使用する場合、MySQLもインデックスを使用することができません。
JOIN操作（複数のデータテーブルからデータを抽出する必要がある場合）では、MySQLは主キーと外キーのデータタイプが同じ場合にのみインデックスを使用します。
WHERE子句のクエリ条件にLIKEおよびREGEXP比較演算子を使用する場合、MySQLは検索テンプレートの最初の文字がワイルドカードでない場合にのみインデックスを使用します。例えば、クエリ条件がLIKE 'abc%'の場合、MySQLはインデックスを使用します；一方、LIKE '%abc'の場合、MySQLはインデックスを使用しません。
ORDER BY操作では、MySQLはソート条件がクエリ条件表現でない場合にのみインデックスを使用します。（それでも、複数のデータテーブルクエリに関して、インデックスが利用可能であっても、それらのインデックスはORDER BYを高速化するためにあまり役には立ちません。）
データ列に多くの重複値が含まれている場合、インデックスが設定されていても効果が悪いことがあります。例えば、データ列に「0」など、净はずれな値しか含まない場合などです。/1「または」Y/Nなどの等値がなければ、インデックスを作成する必要はありません。
通常のインデックス、ユニークインデックス、および主インデックス
1通常のインデックス
通常のインデックス（KEYまたはINDEXキーワードで定義されたインデックス）の唯一のタスクは、データへのアクセス速度を高速化することです。したがって、クエリ条件（WHERE column = …）やソート条件（ORDER BY column）に最も頻繁に現れるデータ列にインデックスを作成するべきです。可能な限り、データが最も整然と、最もコンパクトなデータ列（例えば整数型のデータ列）を選択してインデックスを作成するべきです。
2ユニークインデックス
通常のインデックスは、インデックスとしてデータ列が重複する値を含むことを許可します。例えば、人間が同じ名前を持つ可能性があるため、同じ名前が「従業員個人情報」データテーブル内に2回またはそれ以上出现することがあります。
データ列が互いに異なる値しか含まないと確定できる場合、そのデータ列に対してインデックスを作成するときは、UNIQUEキーワードを使用してユニークインデックスとして定義することが推奨されます。この方法の利点は、まずMySQLがそのインデックスの管理を簡素化し、インデックスがより効率的になることです。次に、MySQLは新しいレコードがデータテーブルに挿入される際に、そのレコードのこのフィールドの値がすでにどのレコードのこのフィールドに存在するかを自動的にチェックします。もし存在する場合、MySQLはその新しいレコードの挿入を拒否します。つまり、ユニークインデックスはデータレコードのユニーク性を保証します。実際には、多くの状況でユニークインデックスを作成する目的はアクセス速度の向上ではなく、データの重複を避けるためです。
3. 主インデックス
前述のように、主キーフィールドに対してインデックスを作成する必要があることを何度も強調しました：これが「主インデックス」と呼ばれるインデックスです。主インデックスとユニークインデックスの唯一の違いは、定義時に使用されるキーワードがPRIMARYであるかUNIQUEであることです。
4. 外部キーインデックス
もし、外部キーフィールドに対して外部キー制約条件が定義されている場合、MySQLは最も効率的な方法で外部キー制約条件を管理および使用するために内部インデックスを定義します。
5. 複合インデックス
インデックスは、INDEX(columnA, columnB)のような複数のデータ列をカバーできます。このインデックスの特徴は、MySQLがそのようなインデックスの1つを選択的に使用できることです。もし、クエリ操作が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にとって複雑であり、大量のデータを処理する必要がある場合、応答時間が長くなります。
这类场合正是全文索引(full-text index)可以大显身手的地方。在生成这种类型的索引时，MySQL将把在文本中出现的所有单词创建为一份清单，查询操作将根据这份清单去检索有关的数据记录。全文索引即可以随数据表一同创建，也可以等日后有必要时再使用
下面这条命令添加：
ALTER TABLE tablename ADD FULLTEXT(column1, column2)
有了全文索引，就可以用SELECT查询命令去检索那些包含着一个或多个给定单词的数据记录了。下面是这类查询命令的基本语法：
SELECT * FROM tablename
WHERE MATCH(column1, column2) AGAINST(‘word1′, ‘word2′, ‘word3′)
上面这条命令将把column1和column2字段里有word1、word2和word3的数据记录全部查询出来。
注解：InnoDB数据表不支持全文索引。
查询和索引的优化
只有当数据库中已经有了足够多的测试数据时，它的性能测试结果才有实际参考价值。如果在测试数据库中只有几百条数据记录，它们往往在执行完第一条查询命令之后就被全部加载到内存中，这将使后续的查询命令都执行得非常快–不管有没有使用索引。只有当数据库中的记录超过了1000条、数据总量也超过了MySQL服务器上的内存总量时，数据库的性能测试结果才有意义。
在不确定应该在哪些数据列上创建索引的时候，人们从EXPLAIN SELECT命令那里往往可以获得一些帮助。这其实只是简单地给一条普通的SELECT命令加一个EXPLAIN关键字作为前缀而已。有了这个关键字，MySQL将不是去执行那条SELECT命令，而是去对它进行分析。MySQL将以表格的形式把查询的执行过程和用到的索引（如果有的话）等信息列出来。
在EXPLAIN命令的输出结果中，第1列是从数据库读取的数据表的名字，它们按照被读取的先后顺序排列。type列指定了本数据表与其他数据表之间的关联关系（JOIN）。在各种类型的关联关系中，效率最高的是system，然后依次是const、eq_ref、ref、range、index和All（All的意思是：对应于上一级数据表中的每一条记录，这个数据表中的所有记录都必须被读取一遍–这种情况往往可以用一个索引来避免）。
possible_keys データ列は MySQL がデータレコードを検索する際に選択できるさまざまな索引を示します。key データ列は MySQL が実際に選択した索引であり、その長さはバイト単位で key_len データ列に示されます。例えば、INTEGER データ列の索引の場合、そのバイト長度は...}}4複合索引を使用している場合、key_len データ列には MySQL が実際に使用したその一部が表示されます。一般的な規則として、key_len データ列の値が小さい方が良いです（これはより速いことを意味します）。
ref データ列は関連関係にある別のデータテーブルのデータ列の名前を示します。row データ列は MySQL がこのクエリを実行する際にこのデータテーブルから読み込むと予想されるデータ行の数です。row データ列のすべての数字の積は、このクエリが処理する必要のある組み合わせの数を概算するのに役立ちます。
最後に、extra データ列は JOIN 操作に関連するさらなる情報を提供します。例えば、MySQL がこのクエリを実行する際に一時的なデータテーブルを作成する必要がある場合は、extra 列に 'using temporary' と表示されます。
これでこの記事のすべての内容が終わりました。皆様の学習に役立つことを願っています。また、呐喊教程を多くのサポートをお願いします。
声明：この記事の内容はインターネットから取得しており、著作権者に帰属します。インターネットユーザーが自発的に貢献し、自己でアップロードしたものであり、このサイトは所有権を持ちません。また、人工的な編集は行われていません。著作権侵害が疑われる内容が見つかった場合は、以下のメールアドレスまでご連絡ください：notice#oldtoolbag.com（メールを送信する際には、#を@に置き換えてください。報告を行い、関連する証拠を提供してください。一旦確認がとれましたら、このサイトは即座に侵害疑いのコンテンツを削除します。）