MySQLインデックス設定方法 – HeidiSQLでデータベースらくらく開発

前回は、プラグインテーブルと作者テーブルを結合するところまで行いました

MySQLテーブル結合 – HeidiSQLでデータベースらくらく開発
MySQLの主なテーブル結合処理についての紹介です。 LEFT JOIN 結合を使って、参照されなくなったレコードを 削除する方法も紹介しています (^^)

今回は、結合したテーブルを様々な条件で高速に抽出するためのインデックスについてです

数万件のデータから条件を指定してデータを抽出するとインデックス(KEY)を設定していないと普通に3秒ぐらいかかったりします

1回だけなら問題ありませんが、これが Webサービスとなると厳しいです (>_<)

100倍高速化して 30ms 程度にすることを目標に HeidiSQL を使ったインデックスの設定とチューニングを行っていきます (^^)

 

インデックス(KEY)

インデックス

テーブルのデータとキーの値を紐付ける機能で書籍でいえば索引に相当します。

データベーステーブルでは、このインデックスがレコードの追加、更新、削除時に自動的に更新されることで、データ検索を高速に行うことを可能とします

但し、インデックスは闇雲に設定しても効果があるとは限りません。MySQLはそこまで賢くないので、インデックスを沢山設定しても使われるのは1つだけです

複合インデックス

複数のインデックスを使いたい場合は、複合インデックスという1つのインデックスに複数のフィールドを含める方法を使う必要があります

複数インデックスは、下記の様にインデックスに付ける名前を指定したあとカッコ内にフィールドをカンマ区切りで指定します

KEY indexname (field1,field2)

このフィールドの指定順はとても大切で、左端にあるインデックスから順に使われるということなので、SELECTクエリー中のインデックスと順番が一致している必要があります

インデックスの名前

インデックスには名前を付けます

単独のインデックスならフィールド名のままの名前でも分かりますが、複合インデックスの場合は名前を付けておかないと判別しにくくなります

インデックス名により、後で紹介する EXPLAINでの確認やプラグラム内で使う場合も分かりやすくなります

 テーブルサイズに注意

インデックスはデータを高速に抽出するのに必要ですが、データの追加更新はその分時間がかかります

インデックスは多ければ良いというものでもありませんし、使えば使うほどテーブルサイズも大きくなりますのでバランスが大事です

varchar 等の可変データをインデックスにする場合は必要最低限のサイズを指定することでサイズの節約になります

 

それではさっそくインデックスを設定していきますが、その前に、下記サイトがとても参考になりますので一読しておくことをお勧めします

 

HeidiSQLでインデックス設定

通常は SQL の CREATE TABLE で作成するインデックスを記述しておけば良いのですが、どのようにインデックスを作成すればどのぐらい効果があるのか確認しながら設定していきます

カットアンドトライで行っていくわけですが、HeidiSQLを使うとGUI操作で簡単にインデックスの追加、変更、削除が出来るのでお勧めです

ポイント

  1. データを絞る
  2. 全ソートに注意
  3. パターン検索 LIKE は使わない
  4. 使用インデックスを推奨 USE INDEX する

この4つのポイントを守れば、うまくいくはずです (^^)

一番大事なのは、数万件のデータをいかに絞り込むかという部分です

ソート処理がないのが理想ですが、数万件のデータを数百件に絞り込めるなら、後は少量のソート処理が必要となっても気にするほどではありません

それでは実際に HeidiSQL でインデックスの効果を確認していきます

 

検索用インデックスが未設定の場合

作者テーブルとプラグインテーブルを内部結合して、SNS カテゴリーのプラグインをダウンロード順に抽出してみます

SQL を実行してみると、クエリー履歴で 2.2 秒程度かかったのが確認出来ました。予想通り遅いです (>_<)

Category_sort_nokey

 EXPLAINで確認

インデックスを設定していないので ALL が表示されています

Category_sort_nokey_explain

EXPLAINを紹介していなかったのでここで簡単に紹介しておきます

EXPLAIN という、SELECT文でどのようにインデックスが使われているのか、データベース内部動作の情報を表示コマンドがあります

このコマンドを使って、どのインデックスが使われたのか、検索されるレコード数がどのくらいなのかが判ります

注目するのは、type, key です

type が ALL だとフルテーブルスキャンが行われインデックスが使われていない状態を表します

key には、使われるインデックスが示されます

 

EXPLAIN の詳細は下記サイトを参照して下さい

MySQL :: MySQL 5.6 リファレンスマニュアル :: 8.8.2 EXPLAIN 出力フォーマット

 

HeidiSQLでインデックス設定

HeidiSQLからGUI操作で簡単にインデックスの追加、変更、削除が出来ます

フィールド右クリックしてインデックスを追加します

HeidiSQL_KEY追加

保存ボタンをクリックすると反映されます

HeidiSQL_KEY保存

インデックス名や長さを変えたり、カラムの追加で 複合インデックスも簡単に作れます

HeidiSQL_複合KEY追加1

HeidiSQL_複合KEY追加2

これで、インデックスチューニングのカットアンドトライ作業が楽に出来ます (^^)

 

単独インデックス

それでは、まずはカテゴリー(optag)単独でインデックスを設定して実行してみます

Category_sort_singlekey

100ms 程度で実行できました

何回か実行させると ばらつきはあるのですが 30ms 前後で実行できる感じなのでほぼ目標は達成です

EXPLAINで確認してみます

Category_sort_singlekey_explain

type が ALL から ref に変わり、インデックスに optag が使われたことが確認出来ますがソートも行われています

 

複合インデックス

次に カテゴリーとダウンロード数を複合インデックスにして実行してみます。またカテゴリーキーが少し長いので長さを指定して短くします

Category_sort_complexkey

わずかですが少し速くなったようです

EXPLAINで確認してみます

Category_sort_complexkey_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 を使うとお手軽にインデックスの設定、チューニングが出来ることを紹介いたしました

次回は、テーブルデータのインポート/エクスポートについて紹介する予定です

 


まとめ記事紹介

search star user home refresh tag chevron-left chevron-right exclamation-triangle calendar comment folder thumb-tack navicon angle-double-up angle-double-down angle-up angle-down quote-left googleplus facebook instagram twitter rss