運営サイトのあるページの表示時間が遅くなったので、原因を調べてみるとデータベースのMySQLが原因であるようなので対策をしてみました。
■ 現状分析
サイト公開当初は、サイトのページの表示時間に問題はなかったが、MySQLのあるテーブルのレコード数が20万件を超えたあたりから、表示が遅くなり始める。
開発環境のXAMPPで確認してみると、なんと10秒前後も表示に時間がかかっており明らかにおかしい。
実際の運用サーバーでも、表示に1秒前後かかっており、こちらも以前と比べてだいぶ遅くなっている。
■ MySQLの速度を改善するための対策
これまで、MySQLの速度の改善をしたことがないので、まずはネットを検索して情報収集。すると、3点ほど役立ちそうな改善策を発見。
1. テーブルにインデックス(index)を作成
2. インデックスを作成したら、WHERE条件句の最初にインデックスを張ったカラムを記述
3. SQL文の要素をバッククオートやクオートで適切にくくる
■ 対策した結果
上記の3つの対策をすべて試してみましたが、結論からいうと、上記1.に従い、とりあえず適切なカラムにインデックスを作成するだけで、速度が約10倍速くなりました。
つまり、開発環境のXAMPPでは、表示に約1秒、実運用サーバーでは、約0.1秒ほどに改善しました。なお、実運用サーバーではキャッシュが効いているようで、実際には、0.025秒ほどになってます。
■ 上記1. の方法について
インデックスの作成は、簡単です。phpMyAdminからは、テーブルの構造を表示して、インデックスを作成したいカラムの所定の場所にある、「インデックスを追加」をクリックすることで作成できます。
また、コマンドを使ってもクリエイト・インデックス構文などを使えば作成できるようです。
ポイントは、インデックスを作成するカラムを適切に選択すること。検索条件に使用するカラムであり、インデックスによる効果が高いカラムを選択します。
デメリットとしては、インデックスを作成するとその分だけ容量が増えてしまうことがあります。これは速度とトレードオフなので仕方ないです。約20万件のレコードで20mbあったものが、インデックスを作成したことで、約5mb増えて25mbほどになりました。
■ 上記2. の方法について
インデックスを作成したあと、当該テーブルを検索するSQLも見直しました。ネット上では、インデックスを作成したカラムを、条件句であるWHEREの最初に置くとインデックスが効き速度が上がる旨の記述を何回か見かけたので、そのようにしてみました。
ただ、この点については、それほど変化はないように思います。インデックスを作成した時点で約10倍速くなったので、この点はあまり関係がないのかもしれません。
ただ、SQLを書き換えても多少面倒なだけで、それほどデメリットはないので、WHERE句の最初にインデックスのカラムがくるように書き換えておきました。
■ 上記3. の方法について
これは、SQL文を適切なバッククオート、及び、クオートでくくるとSQL文の構文解析にかかる時間が短縮され検索速度があがるというものでしたが、試してみてもそれほど効果は感じられませんでした。
テーブル固有の値はバッククオートでくくり、ユーザー入力値等はクオートでくくるという書式であり、phpMyAdmin上でSQLを実行すると表示されるSQLの書式です。
この方法については、あえて採用しませんでした。理由は、書式がMySQLに固有の場合である可能性があること。他のサイトでは、この書式だと、SQLite等でエラーが出る旨の記述がありました。万一後から書き直すことになると面倒なので、この点は現状維持にしておきました。
■ 結論
とりあえず、インデックスを作成することで、速度が改善したので改善策は一応成功したといえます。
ただ、レコードが増えていくにつれて、適切に対策を講じないと、また遅くなる可能性があるので、5万件か10万件レコードが増えるたびに速度の確認をした方が良さそうです。
なお、インデックスは、一つのカラムだけでなく、複数のカラムにも作成できるので、次回確認時に速度が遅くなっていたら、別のカラムにもインデックスを作成したいと思います。今回は、一つのカラムにインデックスを作成したことで、満足できる結果なので一つのインデックスのみにしておきました。
以上です。