MySQL :: MySQL 5.1 リファレンスマニュアル :: 6.4.5 MySQLにおけるインデックスの使用

MySQL 5.1 リファレンスマニュアル :: 6 最適化 :: 6.4 データベース構造の最適化 :: 6.4.5 MySQLにおけるインデックスの使用

Section Navigation [Toggle]

6.4.5. MySQLにおけるインデックスの使用

インデックスは通常迅速に特定のカラム値の行を検索するのに使用されます。インデックスがない場合、関連する行を検索する場合ＭｙＳＱＬは最初の行から始め全テーブルを読まなけれ場なりません。テーブルが大きいほど、コストがかかります。検索しているカラムのインデックスをテーブルが含んでいる場合、ＭｙＳＱＬは全てのデータを検索せずに、データファイルの途中でシークポジションを迅速に決定します。テーブルに1000行ある場合、連続して読む場合よりも少なくとも100倍の速度で処理が行われます。行のほとんどにアクセスしなければならない場合、連続して読むほうがより高速です。というのも、これはディスクシークの最小化が行われるためです。

ほとんどのＭｙＳＱＬインデックスは(PRIMARY KEY、UNIQUE、INDEX、そしてFULLTEXT) はB-treesに保存されています。R-treesを使用する空間データ型インデックスは例外で、MEMORY テーブルでもハッシュインデックスがサポートされています。

文字列のプリフィックスとエンドスペースは自動的に圧縮されます。項12.1.7. 「CREATE INDEX 構文」を参照してください。

一般的に、インデックスは以下のように使用されます。ハッシュインデックスの特徴は(MEMORYテーブルで使用される) このセクションの終わりに説明されています。

MySQLはこれらの演算についてインデックスを使用します。

WHERE節にマッチする行を迅速に検索する場合。
行を考慮に入れない場合。複数のインデックス間を選択できる場合、ＭｙＳＱＬは通常最小行数を検索するインデックスを使用します。
結合実行時に、他のテーブルから行を取得する場合。
特定のインデックス化されたカラムkey_colに対して、MIN()あるいはMAX()値を検索する場合。これはインデックス内でkey_colより前に発生する全てのキーパーツで、WHERE key_part_N = 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が後続する場合、キーは逆の順序で読まれます。項6.2.12. 「ORDER BY最適化」を参照してください。
データ行を参照せず値を取得するためにクエリが最適化される場合もあります。クエリがあるキーの最も左側のプリフィックスを形成し、かつテーブル内で数値のみのカラムを使用する場合、選択された値は高速化を図るため、インデックスツリーから取得されることもあります。
```
SELECT key_part3 FROM tbl_name 
  WHERE key_part1=1
```

例えば次のSELECTステートメントを発行したとします。

mysql> SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;

col1とcol2上で複合カラムインデックスが存在する場合、適当な行は直接取得されます。col1とcol2に別々のシングルカラムインデックスが存在する場合、オプティマイザはどのインデックスがより少ない行を検索するかを決定することで、最も制限力のあるインデックスを検索します。また、そのインデックスを使用して行を取得します。

テーブルに複合インデックスがある場合、オプティマイザではインデックスの左端の先頭部分のいずれかをレコードの検索に使用できます。たとえば、(col1, col2, col3)に 3 カラムのインデックスがある場合、(col1)、(col1, col2)そして(col1, col2, col3)に対して、インデックスの検索機能を使用できます。

カラムがインデックスの左端の先頭部分を構成していない場合、MySQL では、部分インデックスを使用できなくなります。以下のSELECTステートメントがあります。

SELECT * FROM tbl_name WHERE col1=val1;
SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;

SELECT * FROM tbl_name WHERE col2=val2;
SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;

インデックスが(col1, col2, col3)に存在する場合、最初のクエリだけがインデックスを使用できます。3つめと4つめのクエリには、インデックス化したカラムが必要ですが、(col2)と(col2, col3) は (col1, col2, col3)の左端のプリフィックスではありません。

=, >, >=、<, <=あるいはBETWEEN演算子を使用する表現のカラム比較に、B-treeインデックスが使用可能です。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値は定数ではありません。

もし... LIKE '%文字列%'そして文字列は3文字より長い場合、ＭｙＳＱＬは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
    /* index = 1 OR index = 2 */
... WHERE index=1 OR A=10 AND index=2
    /* optimized like "index_part1='hello'" */
... WHERE index_part1='hello' AND index_part3=5
    /* Can use index on index1 but not on index2 or index3 */
... WHERE index1=1 AND index2=2 OR index1=3 AND index3=3;

次の WHERE節ではインデックスが使用されません。

    /* index_part1 is not used */
... WHERE index_part2=1 AND index_part3=2

    /*  Index is not used in both parts of the WHERE clause  */
... WHERE index=1 OR A=10

    /* No index spans all rows  */
... WHERE index_part1=1 OR index_part2=10

MySQL では利用可能な場合でもインデックスが使用されない場合があることに注意してください。この一例として、インデックスの使用によって、MySQL がテーブルの 30% を超えるレコードにアクセスする必要が生じる場合が挙げられます（この場合は、必要なシークが大幅に減少するため、テーブルスキャンのほうが高速になる可能性が高くなります）。ただしこのクエリに、レコードの一部のみを取り出す LIMITが使用されている場合、結果で返される少数のレコードを迅速に検索できるため、MySQL はインデックスを使用します。

ハッシュインデックスは先ほど挙げたインデックスとは特徴が異なります。

= or <=>演算子を使用する等価比較にのみ使用されます。(ただし非常に 高速です)<のように値の範囲を検索する比較演算子には使用されません。
オプティマイザはORDER BYオペレーション速度を上げるためにハッシュインデックスを使用することはできません。(このようなインデックスは順序どおりに次のエントリーを検索することはできません)
MySQLは2つの値の間にある行の数を判別することはできません。(どのインデックスを使用するかを決定する上、範囲オプティマイザによって使用されます)MyISAMテーブルをハッシュインデックスを含むMEMORYテーブルに変換した場合、これは一部のクエリに影響を与えるかもしれません。
行の検索には自然キーのみが使用できます。(B-tree インデックスでは、どのキーの左端の先頭部を使用しても行を検索できます。)

MySQL Enterprise. どのインデックスが要求されるか、あるいは最も効率的な—実際のテーブル使用率が指標となるか、正確に推測するのは困難です。MySQL Network Monitoring and Advisory Service はこの問題に関する専門的なアドバイスを提供します。詳細は http://www-jp.mysql.com/products/enterprise/advisors.html をご覧ください。

Top / Previous / Next / Up / Table of Contents