skillup

技術ブログ

Database

SQLの高速化について&explain

投稿日:

本日はSQLの高速化について。

高速化といってもさまざまなテクがあると思うのですが、代表的な考え方に関して。

高速化に関して

index

一番効果がでやすいのがこれですね。MySQLではテーブルを設計するときにindexというものを付与することができます。

簡単にいうとレコードをあるフィールドで整理することです。

これは辞書の索引と同じです。

例えば英単語を探す場合、辞書では探しやすいようにアルファベット順(A,B,C・・)に整列されています。

こうすることでどんな単語であっても短時間で探すことが可能になります。もし英単語がアルファベット順でなくランダムに並んでいたら、とんでもない時間がかかるでしょう。

MySQLのフィールドもこのようにあるフィールドに対して索引機能を付けることができます。主に数字系のレコード(idがらみ)に対して貼っておくと検索機能が向上します。

ただしindexにはデメリットもあります。”整理”を行うということは当然それをする作業が発生しますので、データの新規作成と更新に関しては逆に遅くなります。

さきほども書きましたが、id系のフィールドに対して貼っておくとよいでしょう。下記リンクのindexが適切か否かの記事が参考になります。

http://kiyotakakubo.hatenablog.com/entry/20101117/1289952549

ディスクアクセスを減らす

これは大まかな方針ですね。処理が遅くなる一番の原因はディスクIOです。そのためいかにデータベースのアクセスを減らすかを考えましょう。先ほどのバルクインサートも同様の理屈で早くなります。

SELECTでは*を使わない

列名では必ず個別の列名を書きましょう。そうでないとすべての列をメモリ上に置くことになり、遅くなります。COUNTも同様です。

できるだけ冗長な結合条件を書く

JOINでテーブル間を結合するときは条件を一気に減らせるような結合条件を書くようにしましょう。そのほうが早く対象レコードを絞り込むことができます。

後方一致はなるべく避ける

検索にlikeを使った場合、後方一致はindexが利かなくなるので非常に遅くなります。

インデックスが使われているかをしっかり見る

特に複数キーの場合使われていると思っていても使われていないパターンもあるので、要注意です。explainで確認しましょう。

大量のINSERT文はバルクインサートを

大量のINSERT文は1行1行書くのではなく、INSERT() VALUES(),()・・として一気にまとめて挿入をしましょう。データが多い場合はこちらのほうが圧倒的に早いようです。原因としてディスクIOが減るからです。

調査

MySQLが遅いとなった時にそれを調査する技術、知識が必要になります。主に使われるのは下記のような技術です。

スロークエリログ

遅いSQLがそもそもどれかわからないときにはどのSQLに時間がかかっているのかを検出する必要があります。

プログラムで1つ1つ処理前と処理後の時間差を調べるのもいいですが、設定ファイルに書き込んでおくことでMySQLが自動で検出してくれる方法があります。特に難しいことはなく、設定ファイルに書き込みをするだけです。

今回は使いませんでしたが、ノウハウとして知っておいたほうがいいのでメモを。

といってもリンク任せですが・・・汗

下記リンク

http://pk-brothers.com/568/
http://linuxserver.jp/%E3%82%B5%E3%83%BC%E3%83%90%E6%A7%8B%E7%AF%89/db/mysql/%E3%82%B9%E3%83%AD%E3%83%BC%E3%82%AF%E3%82%A8%E3%83%AA%E3%83%AD%E3%82%B0

explain

遅いSQLの特定方法がわかったら次は具体的なSQLの問題点を見る必要があります。

その時に使えるのがexplainというコマンドです。

これをクエリの前に記述することによってSQLがどのように発行されているのかを見ることができます。

詳しい見方はリンクを参考に

http://nippondanji.blogspot.jp/2009/03/mysqlexplain.html
http://labs.opentone.co.jp/?p=1985
http://woshidan.hatenablog.com/entry/2015/06/20/165817

  • 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かと思います。

その他高速化に関する参考リンク

もっといろいろと知りたい方は下記のリンクなんぞを。

http://pg123-undo.blogspot.jp/2011/11/sql.html#a7

http://memo.sonorilo.net/post/187525676/sql%E6%96%87%E3%82%92%E6%9C%80%E9%80%9F%E3%81%AB%E3%81%99%E3%82%8B11%E3%81%AE%E3%83%9D%E3%82%A4%E3%83%B3%E3%83%88

-Database
-

執筆者:


comment

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

関連記事

no image

SQL 集計関数の利用

先日同様、ある程度複雑な問題のSQLに関して。出典はSQL実践入門。 問題 下記のようなテーブル(テーブル名)で より古い年のデータが存在しない場合 NULL 直近の年のデータより売り上げが伸びた場合 …

no image

SQL基礎 条件式はunionよりもcaseで

複雑な条件式があったときにcase式を使うことでパフォーマンスを向上させることができます。 ※一般にunionを使うよりも高速なことが多い。 例1 ある条件により別の列を使いたいとき、 [crayon …

no image

SQL基礎 手続き型言語と集合思考の言語

どんな仕事でもそうだと思いますが、長年惰性で使っていると日常の作業はなんとかできてるけど、実は深く理解していない&効率のいいやり方を知らない、ということが結構あります。 私の場合、ちょっと前にCSSを …

no image

MySQLのLIMIT,OFFSETに関して&explainの見方など

自作のWEBアプリを作っていたところSELECT句が異常に遅いケースがありました。 発見までにかなり時間がかかったんですが、不可思議な現象としてはOFFSETが小さいときと大きいときで検索スピードが全 …

no image

サブクエリ 移動平均など

前回の応用編です。 日付、入出金、残高はできましたが、ここからさらに、現在のレコードから3行以内(3行あれば3行、なければそれ以内でできるだけ)のレコードの合計値を出す計算を考えます。 結果だけ先に書 …