MySQLインデックスの使用方法

MySQLインデックスの使用方法
インデックスは、特定の値のレコードをすばやく見つけるために使用されます.インデックスがない場合は、MySQLは1行目のレコードからテーブル全体を読み取り、レコードを取得する必要があります.テーブルが大きいほど、リソースの消費量が大きくなります.フィールドにインデックスがあれば、MySQLはすべてのデータを検索することなく、データファイルのどこからレコードを検索するかをすぐに決定します.テーブルに1000件のレコードがある場合、これは少なくとも順番にデータを読み取るより100倍速い.ほとんどの1000件のレコードにアクセスする必要がある場合は、ディスク検索が最小限に抑えられるため、シーケンス読み取りが高速になります.
ほとんどのMySQLインデックス(PRIMARY KEY,UNIQUE,INDEX,FULLTEXT)はBツリー方式で格納されています.空間タイプのフィールドのみがRツリーストレージを使用し、MEMORY(HEAP)テーブルはハッシュインデックスをサポートします.
文字列のデフォルトは、接頭辞と接尾辞のスペースを自動的に圧縮します.
通常、インデックスは以下のようにいくつかの場合に使用できます.ハッシュインデックス(MEMORYテーブル用)のユニークな点については後述する.
WHERE句にマッチした記録を早く見つけたい.
条件に基づいてレコードを除外します.複数のインデックスが選択可能であれば、MySQLは通常、最も少ないレコードが見つかるインデックスを選択します.
テーブル接続クエリーを作成するときに、他のテーブルからレコードを取得します.
指定したインデックスフィールドkey_colにMIN()またはMAX()の値が見つかりました.オプティマイザはインデックスをチェックします
　　key_colフィールドの前に、他のインデックス部分がWHERE keyを使用しているかどうかを確認します.part_# = constant句ということで、
MySQLは、MIN()またはMAX()式のインデックス検索を個別に行い、定数に置き換えます.すべての式が定数に置き換えられると、クエリはすぐに返されます.次のようになります.
　　SELECT MIN(key_part2),MAX(key_part2) FROM tbl_name WHERE key_part1=10;
テーブルをソートまたはグループ化し、使用可能な最左接頭辞インデックス上でグループ化またはソートする場合(ORDERなど)
　　BY key_part1, key_part2).すべてのインデックス部分がDESCでソートされている場合、インデックスは逆順序でソートされます.
クエリは、データの計算を必要とせずに直接結果を得るように最適化される場合があります.クエリがテーブル内の数値フィールドを使用し、このフィールドがインデックスの一番左の部分である場合、インデックスツリーからすぐに結果が得られます.
 
　

　SELECT key_part3 FROM tbl_name WHERE key_part1=1

 
 
次のSELECT文があるとします.
 
　　mysql> SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;
 
col 1とcol 2にマルチフィールドインデックスが1つあれば、対応するレコードを直接取得できます.col 1とcol 2にそれぞれ独立したインデックスがある場合、オプティマイザはまず制限が最も多いインデックスを見つけ、どのインデックスがより少ないレコードを見つけることができるかによって、どのインデックスを使用するかを決定します.
テーブルに複数のフィールドインデックスがある場合、インデックスの左端の接頭辞部分は、オプティマイザによってレコードを取得するために使用できます.たとえば、(col 1,col 2,col 3)にインデックスがある場合、フィールドの組合せ(col 1)、(col 1,col 2)、および(col 1,col 2,col 3)で検索するときにインデックスが使用されます.
MySQLでは、左のプレフィックス以外のインデックスの一部を使用できません.以下のSELECT文がある場合:
 
　　SELECT * FROM tbl_name WHERE col1=val1; 　　SELECT * FROM tbl_name WHERE col2=val2; 　　SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;
 
(col 1,col 2,col 3)にインデックスがある場合は、最初のクエリだけがインデックスに使用されます.2番目と3番目はインデックスフィールドを含むが、(col 2)および(col 2,col 3)はインデックス(col 1,col 2,col 3)の最も左のプレフィックス部分ではない.
インデックスは、フィールドに=,>,>=,<,<=,またはBETWEEN比較操作を行う場合にも使用されます.
MySQLは、LIKEの比較にもインデックスを使用することができます.LIKEのパラメータが非ワイルドカード文字から始まる固定文字列であれば.以下のSELECT文はインデックスを使用します.
 
　　SELECT * FROM tbl_name WHERE key_col LIKE 'Patrick%'; 　　SELECT * FROM tbl_name WHERE key_col LIKE 'Pat%_ck%';
 
最初のクエリでは、「Patrick」<=key_のみcol<'Patricl'レコードが取得されます.2番目のクエリでは、「Pat」<=key_のみが取得されます.col<'Pau'の記録.
以下のSELECT文はインデックスを使用しません.
 
　　SELECT * FROM tbl_name WHERE key_col LIKE '%Patrick%'; 　　SELECT * FROM tbl_name WHERE key_col LIKE other_col;
 
最初の文では、LIKEのパラメータはワイルドカードで始まります.2番目の文では、LIKEのパラメータは定数ではありません.
MySQL 4.0以降は、追加のLIKE最適化が行われます.使用する場合...LIKE'%string%'でstringが3文字を超えると、MySQLはTurbo Boyer-Mooreアルゴリズムでモードを初期化し、このモードを利用して検索を高速化します.
colでname IS NULL検索でもインデックスが使用されます.フィールドcol_の場合nameにインデックスがあると.
WHERE句のすべてのAND句にまたがっていないインデックスは、クエリを最適化するために使用されません.すなわち、インデックスを有効にするには、任意のAND文でインデックスの接頭辞フィールドを使用する必要があります.
次のWHERE句はインデックスを使用します.
 
　　... WHERE index_part1=1 AND index_part2=2 AND other_column=3 　　 　　... WHERE index=1 OR A=10 AND index=2 　　 　　... WHERE index_part1='hello' AND index_part3=5 　　 　　... WHERE index1=1 AND index2=2 OR index1=3 AND index3=3;
 
次のWHERE句はインデックスを使用しません.
 
　　 　　... WHERE index_part2=1 AND index_part3=2 　　 　　... WHERE index=1 OR A=10 　　 　　... WHERE index_part1=1 OR index_part2=10
 
利用可能なインデックスがあるにもかかわらず、MySQLでは使用されない場合があります.1つの場合、オプティマイザはインデックスを使用すると、より多くのテーブルレコードを取得する必要があると考えています(この場合、より少ない検索が必要なため、テーブルのスキャンがより速くなる可能性があります).それでも、一部のレコードのみを取得することをLIMITで制限するクエリがある場合、MySQLはインデックスを使用します.これにより、より少ないレコードをより速く取得して結果に返すことができるからです.
ハッシュインデックスのいくつかの異なる特性を以下に示します.
これらは=または<=>比較にのみ使用されます(ただし、すぐには使用されません).
オプティマイザは、ハッシュインデックスを使用してORDER BY操作を高速化できません(このインデックスは、次のレコードを順番に検索するために使用できません).
MySQLでは、2つの値の間にどれだけのレコードがあるかはほぼ判断できません(これは、範囲最適化プログラムによってどのインデックスを使用するかを決定します).これは、MyISAMテーブルタイプをハッシュインデックスを使用したMEMORYタイプに変更すると、いくつかのクエリに影響を与える可能性があります.
レコードの取得に使用できるのは、すべてのインデックスキーのみです(Bツリーインデックスの場合、任意の接頭辞部分インデックスもレコードの取得に使用できます).