本日はSQLの高速化について。
高速化といってもさまざまなテクがあると思うのですが、代表的な考え方に関して。
Contents
高速化に関して
index
一番効果がでやすいのがこれですね。MySQLではテーブルを設計するときにindexというものを付与することができます。
簡単にいうとレコードをあるフィールドで整理することです。
これは辞書の索引と同じです。
例えば英単語を探す場合、辞書では探しやすいようにアルファベット順(A,B,C・・)に整列されています。
こうすることでどんな単語であっても短時間で探すことが可能になります。もし英単語がアルファベット順でなくランダムに並んでいたら、とんでもない時間がかかるでしょう。
MySQLのフィールドもこのようにあるフィールドに対して索引機能を付けることができます。主に数字系のレコード(idがらみ)に対して貼っておくと検索機能が向上します。
ただしindexにはデメリットもあります。”整理”を行うということは当然それをする作業が発生しますので、データの新規作成と更新に関しては逆に遅くなります。
さきほども書きましたが、id系のフィールドに対して貼っておくとよいでしょう。下記リンクのindexが適切か否かの記事が参考になります。
MySQLパフォーマンスチューニングのためのインデックスの基礎知識
ディスクアクセスを減らす
これは大まかな方針ですね。処理が遅くなる一番の原因はディスクIOです。そのためいかにデータベースのアクセスを減らすかを考えましょう。先ほどのバルクインサートも同様の理屈で早くなります。
SELECTでは*を使わない
列名では必ず個別の列名を書きましょう。そうでないとすべての列をメモリ上に置くことになり、遅くなります。COUNTも同様です。
後方一致はなるべく避ける
検索にlikeを使った場合、後方一致はindexが利かなくなるので非常に遅くなります。
インデックスが使われているかをしっかり見る
特に複数キーの場合使われていると思っていても使われていないパターンもあるので、要注意です。explainで確認しましょう。
大量のINSERT文はバルクインサートを
大量のINSERT文は1行1行書くのではなく、INSERT() VALUES(),()・・として一気にまとめて挿入をしましょう。データが多い場合はこちらのほうが圧倒的に早いようです。原因としてディスクIOが減るからです。
調査
MySQLが遅いとなった時にそれを調査する技術、知識が必要になります。主に使われるのは下記のような技術です。
スロークエリログ
遅いSQLがそもそもどれかわからないときにはどのSQLに時間がかかっているのかを検出する必要があります。
プログラムで1つ1つ処理前と処理後の時間差を調べるのもいいですが、設定ファイルに書き込んでおくことでMySQLが自動で検出してくれる方法があります。特に難しいことはなく、設定ファイルに書き込みをするだけです。
今回は使いませんでしたが、ノウハウとして知っておいたほうがいいのでメモを。
といってもリンク任せですが・・・汗
下記リンク
MySQLで遅延クエリを吐き出すSlow Queryを設定する方法
処理が遅いSQL文をスロークエリログとして出力する
explain
遅いSQLの特定方法がわかったら次は具体的なSQLの問題点を見る必要があります。
その時に使えるのがexplainというコマンドです。
これをクエリの前に記述することによってSQLがどのように発行されているのかを見ることができます。
詳しい見方はリンクを参考に
漢のコンピューター道 MySQLのEXPLAINを徹底解説!!
MySQLでのSQLチューニングについて(EXPLAINの見方)
MySQLのExplainを確認する
- select_typeがALLになっている場合は全行を検索するため、なるべくさけましょう。constになっていれば最も最速
- 検索効率はすべての行のrowsのかけ算になるのでここの行が多いものは改善余地ありです。
- JOINでつなぐ場合はindexが張られていないもので結合する場合、遅くなります。indexが張られていないものを結合する場合、ユニークになる組み合わせだけでなく、その他にindexの張られたフィールドでのJOINも行いましょう。そのほうが検索するレコードが少なくなるため早くなります。
- where句で条件の絞り込みをする場合はなるべく制限の強いものから行いましょう。例えば年齢と性別で検索する場合、where sex =”” and age=”””とするよりはwhere age = “” and sex =”のほうが一般的には早いです。性別は2種類しかないことに比べ、年齢は0~150の間が一般なため、年齢で絞ったほうがレコード数が少なくなります。
基本はインデックスが正常に使われているか(select_typeがALLやindexになっていないか)とrowsで数量がしぼれているかの2つをみればOKかと思います。
その他高速化に関する参考リンク
もっといろいろと知りたい方は下記のリンクなんぞを。