Web Artisan Blog - ウェブ アルチザン ブログ

MySQL:インデックスまとめメモ

SQL

2006年01月11日

アフィリエイトから企業サイト分析まで。訪問者の動きがまるわかりのアクセス解析サービス「リサーチアルチザンプロ」

LINEやSkypeみたいなビデオ通話がブラウザだけでできる!オンライン通話サービス「EZ-Interview - イージーインタビュー」

Research Artisanの開発に伴い、MySQLのインデックス絡みで色々苦戦したので、ここらでメモっておきます。まずはマニュアルから。。

MySQLでのインデックスの使用
インデックスは、カラムが特定の値をもつレコードの迅速な検索に使用されます。インデックスがないと、MySQLがレコードを見つけるために、最初のレコードから開始し、テーブル全体を読み取るとが必要になります。テーブルが大きくなると、これにコストがかかります。クエリ対象のカラムにインデックスがあると、MySQLは全てのデータを探すことなく、データファイルの途中にあるシーク対象ポジションを迅速に取得することができます。テーブルに1000レコードある場合、シーケンシャルに読み取る場合と比較して少なくとも100倍は高速化できます。1000レコードのほとんどすべてにアクセスする必要がある場合は、ディスクシークが最小になるため、シーケンシャルに読むほうが速くなることに注意してください。


つまり、インデックスを使った方がパフォーマンスは上がる場合が多いけど、1000件程度の少ないデータであれば、インデックスを使わない方が一般的には速いとされてる、って事です。
つまり、基本的にはインデックスを使った方がパフォーマンスが上がる事が多い、但し、例えば1000件程度のデータに対し、結果的に約1000件抽出するようなクエリーにおいては、その限りではない、っていうような事です。
※はてなブックマークコメント等でもツッコまれましたが、よくよく読み返せば書いてる事間違ってました。。訂正しておきます。


で、そもそもインデックスって?の場合は、以下のサイトなんかが参考になります。ここに書かれてるキーワードはポイントでしょうね。

インデックスの基礎知識
■NULL値
■!=(Not Equals)の使用
■列に対する関数や演算子の使用
■LIKE句の使用
■ソートの使用
■グルーピングの使用
○結合インデックス
○インデックスマージ



MySQLでインデックスを使用する際の注意点としてまず上げられるのが、”MySQLは1つのクエリーで1つのテーブルに対し、1つのインデックスしか機能しない”という点です。上記リンクの”○インデックスマージ”は、MySQLに当てはまりません。つまり、あるテーブルの各列毎に一所懸命インデックスを定義しても、クエリーを発行した際は、そのうちの一つのインデックスしか利用されないという事です。(どれが利用されるかはクエリーに寄りますけどね)

しかし、テーブルには1つ以上のインデックスを使用したくなる局面が絶対出てきます。そういう場合は、”複合インデックス”を使えばいいのです。

複合インデックス
MySQLでは複数のカラムに対するインデックスを作成できます。インデックスは最大15カラムで構成できます(CHARおよびVARCHARカラムではカラムの先頭部分をインデックスの部分として使用することもできます)。複数カラムのインデックス(複合インデックス)は、インデックス化されたカラムの値を連結することによって生成された値が含まれ、ソート化された配列と見なすことができます。


つまり、インデックスにしたい各列を繋ぎ合わせて、一つのインデックスとできるという事です(15カラムまでの制限はありますが)。複合インデックスは、その順番にも左右されるので、各クエリーのWHERE句と比較して決定した方がいいでしょうね。

インデックスは、定義すればそれで終わりでなく、ちゃんと理想通りにインデックスが使用されてクエリーが発行されているかをチェックしないといけません。”EXPLAIN構文”でね。

EXPLAIN構文(SELECTに関する情報の取得)
EXPLAINを利用すると、より速くレコードを検索するSELECTを得るために、どの時テーブルにインデックスを追加しなければならないかを確認できます。


ちなみに、

最適化方法の選択に影響を及ぼすキーの、カーディナリティなどのテーブル統計を更新するために、ANALYZE TABLEを定期的に実行する必要があります。


と書いてある通り、対象のテーブルにANALYZE TABLEしてあげないと、正しく確認できません。特に”カーディナリティ”の値が更新されるので、これは定期的にしてあげた方がいいでしょう。

”カーディナリティ”
レコード中の一意な値の個数を指します。例えば、性別データ(男、女)のように値の種類が少ない場合には、カーディナリティが低いといいます。逆に社員番号のようにほとんどの値が一意である場合は、カーディナリティが高いといいます。



EXPLAINでインデックスの状況を見ても、結局どうなればいいのかというのがわからなくなったりします。そんな時は以下のサイトが参考になります。

MySQLの最適化
MySQLがindexを活用する時
フィールド値を定数と比較するとき(WHERE name = "hogehoge")
フィールド値全体でJOINするとき(WHERE a.name = b.name)
フィールド値の範囲を求める時
LIKEで文字列の先頭が固定な時
MIN(),MAX()(複数要素indexの同一firstfieldでsecondfieldのmin,maxでも有効)
文字列のプレフィックスをもとにしたORDER BY,GROUP BY
WHEREのすべてのフィールドがindexの一部の場合(DBまったく参照されず)

indexが使われない時
LIKEがワイルドカードで始まる時
DB全体を読んだ方が早いとMySQLが判断した時
通常はindexはORDER BYには使われない
WHEREとORDER BYのフィールドが違う時にはどちらかしか使われない

Extrasで見たくないもの:using filesort(余分なソート)、using temporary(一時ファイルの作成)
Extrasで見たいもの:using index(DB本体を読む必要なし)、Where used(type:ALLとの組み合わせだとindex作成推奨)

SELECTの最適化
LEFT JOIN, STRAIGHT JOINとUSINGの組み合わせはWHEREより早い
WHERE field INはかなり早い
WHEREが使えるならHAVINGは使うな(HAVINGはindex使わず)
なるべく簡単なステートメントを(オプティマイザが働きやすい)


using filesort、using temporaryなんかを使いまくってるクエリーは最悪だという事です。インデックスを考えて、ORDER BYやGROUP BYを使わないと最悪な結果に繋がります。
また、「インデックス自体に昇順、降順指定はできないの?」という疑問も湧いてくるかもですが、MySQL4.1の時点では定義自体はできますが、無視されて全て昇順でインデックスが貼られます。

後、TEXT型とか桁数の多いカラム型に対しても、255桁までならインデックスを部分的に定義できます。ただ、これを複合インデックスに絡めて定義はできないようですね。

・・と、まあこのように、短にインデックスといっても間違い無く使わなければ、逆にレスポンス悪化にも繋がります。しかし、うまく使えてる時の速さは感動すら覚える時がありますね。そもそもMySQLはトランザクションなDBよりクエリーに重点を置いたWebに適したDBだと思いますので、単純なテーブルであればインデックスを使うまでもないですが、できれば駆使して極めていきたいところです。。

Web面接に最適。ブラウザだけでビデオ/音声通話ができるオンライン通話サービス。「EZ-Interview - イージーインタビュー」
訪問者録画機能も付いたシンプルで高機能なリアルタイムアクセス解析「リサーチアルチザンプロ」