前回は、プラグインテーブルと作者テーブルを結合するところまで行いました
MySQLテーブル結合 – HeidiSQLでデータベースらくらく開発
セルティスラボ今回は、結合したテーブルを様々な条件で高速に抽出するためのインデックスについてです
数万件のデータから条件を指定してデータを抽出するとインデックス（KEY）を設定していないと普通に３秒ぐらいかかったりします
１回だけなら問題ありませんが、これが Webサービスとなると厳しいです (>_<)
１００倍高速化して 30ms 程度にすることを目標に HeidiSQL を使ったインデックスの設定とチューニングを行っていきます (^^)
インデックス（KEY）
インデックス
テーブルのデータとキーの値を紐付ける機能で書籍でいえば索引に相当します。
データベーステーブルでは、このインデックスがレコードの追加、更新、削除時に自動的に更新されることで、データ検索を高速に行うことを可能とします
但し、インデックスは闇雲に設定しても効果があるとは限りません。MySQLはそこまで賢くないので、インデックスを沢山設定しても使われるのは１つだけです
複合インデックス
複数のインデックスを使いたい場合は、複合インデックスという１つのインデックスに複数のフィールドを含める方法を使う必要があります
複数インデックスは、下記の様にインデックスに付ける名前を指定したあとカッコ内にフィールドをカンマ区切りで指定します
KEY indexname (field1,field2)
このフィールドの指定順はとても大切で、左端にあるインデックスから順に使われるということなので、SELECTクエリー中のインデックスと順番が一致している必要があります
インデックスの名前
インデックスには名前を付けます
単独のインデックスならフィールド名のままの名前でも分かりますが、複合インデックスの場合は名前を付けておかないと判別しにくくなります
インデックス名により、後で紹介する EXPLAINでの確認やプラグラム内で使う場合も分かりやすくなります
テーブルサイズに注意
インデックスはデータを高速に抽出するのに必要ですが、データの追加更新はその分時間がかかります
インデックスは多ければ良いというものでもありませんし、使えば使うほどテーブルサイズも大きくなりますのでバランスが大事です
varchar 等の可変データをインデックスにする場合は必要最低限のサイズを指定することでサイズの節約になります
それではさっそくインデックスを設定していきますが、その前に、下記サイトがとても参考になりますので一読しておくことをお勧めします
HeidiSQLでインデックス設定
通常は SQL の CREATE TABLE で作成するインデックスを記述しておけば良いのですが、どのようにインデックスを作成すればどのぐらい効果があるのか確認しながら設定していきます
カットアンドトライで行っていくわけですが、HeidiSQLを使うとGUI操作で簡単にインデックスの追加、変更、削除が出来るのでお勧めです
ポイント
- データを絞る
- 全ソートに注意
- パターン検索 LIKE は使わない
- 使用インデックスを推奨 USE INDEX する
この４つのポイントを守れば、うまくいくはずです (^^)
一番大事なのは、数万件のデータをいかに絞り込むかという部分です
ソート処理がないのが理想ですが、数万件のデータを数百件に絞り込めるなら、後は少量のソート処理が必要となっても気にするほどではありません
それでは実際に HeidiSQL でインデックスの効果を確認していきます
検索用インデックスが未設定の場合
作者テーブルとプラグインテーブルを内部結合して、SNS カテゴリーのプラグインをダウンロード順に抽出してみます
SQL を実行してみると、クエリー履歴で 2.2 秒程度かかったのが確認出来ました。予想通り遅いです (>_<)
EXPLAINで確認
インデックスを設定していないので ALL が表示されています
EXPLAINを紹介していなかったのでここで簡単に紹介しておきます
EXPLAIN という、SELECT文でどのようにインデックスが使われているのか、データベース内部動作の情報を表示コマンドがあります
このコマンドを使って、どのインデックスが使われたのか、検索されるレコード数がどのくらいなのかが判ります
注目するのは、type, key です
type が ALL だとフルテーブルスキャンが行われインデックスが使われていない状態を表します
key には、使われるインデックスが示されます
EXPLAIN の詳細は下記サイトを参照して下さい
dev.mysql.com
HeidiSQLでインデックス設定
HeidiSQLからGUI操作で簡単にインデックスの追加、変更、削除が出来ます
フィールド右クリックしてインデックスを追加します
保存ボタンをクリックすると反映されます
インデックス名や長さを変えたり、カラムの追加で 複合インデックスも簡単に作れます
これで、インデックスチューニングのカットアンドトライ作業が楽に出来ます (^^)
単独インデックス
それでは、まずはカテゴリー（optag）単独でインデックスを設定して実行してみます
100ms 程度で実行できました
何回か実行させると ばらつきはあるのですが 30ms 前後で実行できる感じなのでほぼ目標は達成です
EXPLAINで確認してみます
type が ALL から ref に変わり、インデックスに optag が使われたことが確認出来ますがソートも行われています
複合インデックス
次に カテゴリーとダウンロード数を複合インデックスにして実行してみます。またカテゴリーキーが少し長いので長さを指定して短くします
わずかですが少し速くなったようです
EXPLAINで確認してみます
Extra に表示されていた Using filesort が消えました
データ量との兼ね合いもあるのでしょうが、必ずしも劇的に速くなるわけではないようです。この程度の違いなら複合インデックスはテーブルサイズも大きくなるし、書き込み時の負荷もあるので、今回は使わなくてもよいような気もしてきました。
ケースバイケースですかね… (^^)
使用インデックスを指定
上記で行った作業を想定される検索条件毎に順次確認していきます
また、SQLの条件が複雑だったり、インデックスが増えてきたりすると期待したインデックスが使われない場合もありますので、USE INDEX でインデックスを指定しておきます
インデックスの指定はテーブルの後に USE INDEX で指定します
SELECT * FROM wp_plugins_ranking_author AS a INNER JOIN wp_plugins_ranking_base As p USE INDEX (cat_download) ON a.id = p.author_id WHERE p.optag = 'sns' ORDER BY p.downloaded DESC LIMIT 20
まとめ
今回のケースでは、データの絞り込みさえ出来れば、多少はソートが発生しても気にするほど遅くならないのが確認出来ました
実行時間に多少のばらつきはありますが、概ね目標は達成です (^^)
あとは、チューニングした CREATE TABELE のコードを dbDelta の記述ルールに合わせてテーブル作成プログラム側にも反映させとけばOKです
ちなみに dbDelta では、 INDEX ではなく KEY を使って指定する必要があるのと、複合インデックスの場合は、インデックスフィールドのカンマ区切りの後にスペースあるとエラーとなるので注意です
反映させたら動作確認しておくこともお忘れなく
以上
HeidiSQL を使うとお手軽にインデックスの設定、チューニングが出来ることを紹介いたしました
次回は、テーブルデータのインポート/エクスポートについて紹介する予定です
