Note: この記事は、3年以上前に書かれています。Webの進化は速い！情報の正確性は自己責任で判断してください。
『実践ハイパフォーマンスMySQL
- 複合インデックス
- インデックスによらないソート
- インデックスによる制約条件
- フルテキストインデックス
- ワイルドカードは重い
- 非常に多数の行に一致する場合
- インデックス情報の表示
読んだのは4章と5章。他はサーバ設定とかデータ分散とか、環境構築に近いディープな話。SQLレベルだとこの辺で十分でしょ。長いので、5章のまとめはまた後で。
マルチカラムインデックス
ところで、単に2つのインデックスを作成したのでは、だめなのだろうか。そうしてもかまわないが、MySQLは、両方のインデックスを同時に使用することはできない。この事実は、繰り返し述べるに値するほど重要である。「MySQLでは、1つのクエリを実行するとき、1つのテーブルにつき1つのインデックスしか使用できないのである。」
— 4.1.1インデックスの概念
あるクエリを実行するうえでテーブルに複数のインデックスが付いていた場合、 MySQL自身が経験的に判断して、より結果を絞り込めるだろうインデックスを１つだけ使用する。
MySQLではUNIONを除き、「1つのクエリを実行するとき、1つのテーブルにつき1つのつのインデックスしか利用できない」ため、特定のクエリにおいて1つのインデックスだけでは十分な性能を発揮できない場合、クエリの分割を検討する。あるいは既存のインデックスに（例えばソート対象の）カラムを追加したマルチカラムインデックスを使用する。
ALTER TABLE phone_book ADD INDEX (last_name, first_name);
合成インデックス、あるいは複合インデックスと呼ぶこともある。ここで、個々のインデックスの付いたカラムのことを「キーバーツ」と呼ぶことがある。複数のインデックスを同時に利用したい場合の苦肉の策としても用いられる。例えばあるクエリを実行したとき、ソート処理で長い時間が掛かったとする。その場合、既存のインデックスに２つめのインデックスを追加すると処理速度が向上することがある。
この場合、作成した複合インデックスは last_nameと first_name に対するインデックスになる。このインデックスは、last_name の範囲、または last_name と first_nameの両方の範囲の値を指定するクエリに使用できる。
SELECT * FROM test WHERE last_name = 'Widenius';SELECT * FROM test WHERE last_name = 'Widenius' AND first_name = 'Michael';SELECT * FROM test WHERE last_name = 'Widenius' AND first_name >= 'M' AND first_name < 'N';
ただし、以下のようなクエリには使用できない。
SELECT * FROM test WHERE first_name = 'Michael';SELECT * FROM test WHERE last_name = 'Widenius' OR first_name = 'Michael';
インデックスを選択する基準は最も左端のプレフィックスにある。
たとえば(col1, col2, col3)に 3 カラムのインデックスがある場合、(col1)、(col1, col2)そして(col1, col2, col3)に対して、インデックスの検索機能を使用できる。(col2)と(col2, col3) は合成インデックスの最初の部分ではないので、MySQLはインデックスを使用しない。
参照: 6.4.5. MySQLにおけるインデックスの使用, MySQL 5.1 リファレンスマニュアル
インデックスによらないソート
特に気をつけないといけないこと。インデックスを使用しないソートには時間が掛かる。
MySQLの弱点の1つはソートである。MySQLは一瞬のうちに15,000行を取り出すことができるが、特定の順序に並べる必要が発生した場合には、もっと長い時間が掛かる。
— 5.2.2 インデックスベースのソート
インデックスを用いないソートでは、適切な順序で行を返すために、行全体を再び処理しなければならない。解決策は概ね２つ。高速なCPUを追加するか、複合インデックスの利用すること。
ちなみにMySQLではインデックスのソート順を外部から制御することはできない。その代わり、必要に応じて「インデックスを逆順にたどる」ことができる。そのため、（ソートにインデックスを用いる限りは）昇順でも降順でもそこそこ速い。
インデックスによる制約条件
- インデックスでNULL値を使用しても、性能に影響を与えない。
- ユニークインデックスと主キーの違いは、NULLを格納できるかできないか、というだけ。
- 現状、インデックスを使用しないでユニークを強制する機能は、SQLレベルでは公開されていない。
ユニークインデックスを使用すると、特定のカラムで特定の値が一度しか出現しなくなる。 ただしNULLを除く。NULLは何度でも格納できる。NULLは常に特殊な場合であり、データベースサーバに「ここに不明な値が入っている」と伝える手段。
ALTER TABLE phone_book ADD UNIQUE (phone_number);
フルテキストインデックス
SELECT * FROM artivles WHERE body = "%database%";SELECT * FROM artivles WHERE match(body) against('database');
フルテキストインデックスを使用した場合、後者のほうが何千倍も高速。ただし、フルテキストインデックスは完全な単語を扱うため、単語の一部の文字列を検索する状況では役に立たない。
MySQLのMyISAMテーブル（デフォルト）にて、テキストフィールド（varchar,textなど）に対して使用できる。それぞれの単語ごとに１つのレコードが入っているため、フルテキストインデックスの容量は急速に大きくなるが、多くの場合、容量を犠牲にする価値はある。
ALTER TABLE artivles ADD FULLTEXT (body);
ワイルドカードは重い
SELECT * FROM page WHERE last_name like "%buffy%"
このクエリでは、テーブルの全ての行を調べる必要がある。そのため、インデックスを使用しない。しかも"buffy"の直後にカンマやピリオドがあった場合には、見つけることすらできない。
SELECT * FROM page WHERE last_name rlike "(son|ith)$";
同じ理由で、上記のような正規表現を利用した検索も遅い。
非常に多数の行に一致する場合
クエリに一致する行が多すぎる場合、性能が大幅に低下する場合がある。MySQLは、概ねテーブルの約30%以上の行が一致しそうだと判断したら、インデックスを使用しないでテーブルスキャンを実行する。
memo: こういう状況のためにLIMITがある。
インデックス情報の表示
- テーブルを再作成するのに必要なSQLコマンドを表示
SHOW CREATR TABLE- インデックスの情報を表示
- SHOW INDEXES FROM table
- インデックスデータを最適化する（再作成に長時間を要することがある）
- OPTIMIZE TABLE
Note: スパム対策が面倒なので、コメント投稿を廃止しました。以前のコメントは残します。
ご意見・ご要望はtwitter@sigwygかはてブコメントにて。