30代専業主婦の独学エンジニア挑戦ブログ

実務未経験の30歳の専業主婦が独学でエンジニアを目指すブログです

SQLパフォーマンスチューニングについて

DB SQL

勉強記録

以前にDB設計の勉強をしているときに軽く学んだ程度だったので、今日はSQLパフォーマンスチューニングについて詳しく勉強しました！

パフォーマンスチューニングとは？

システムの処理性能や信頼性を高めるために、システムの動作環境を最適化すること。

パフォーマンスチューニングの大まかな流れ

パフォーマンスに関する目標を設定する

ユーザーはどれくらなのか。要求の数とサイズや、目標とするCPU使用率などの目標を設定する。

パフォーマンス メトリックの測定

パフォーマンスの目標が決定したら、パフォーマンス目標を数値で示すために使用されるメトリックを測定する。

システムにおけるボトルネックの特定
ボトルネックによる影響の軽減のためにチューニングを行う

現在の環境をチューニングして、設定したパフォーマンスの目標に対するボトルネックの影響を軽減させる。
このとき大事なのは、ボトルネックの影響を排除するのではなく軽減するということ！

チューニングはSQLだけでなく、アプリケーションのチューニングやDBのチューニングなど様々...

チューニングのヒント

パフォーマンスチューニングは特攻薬ではない
チューニングの目的は、設定したパフォーマンス目標を満たすこと。
つまり、すべてのボトルネックを排除することではない。（あくまで最初に設定したパフォーマンスに関する目標を満たすためにチューニングする）
インデックス貼ってないとか、設計ミスレベルでの障害の可能性もある

SQLパフォーマンスチューニング

SQLパフォーマンスチューニングとは

スロークエリを見つけて、改善していくこと。

スロークエリを改善するには・・・

インデックスを張る

WHEREだけでなく、ソートのときにもインデックスが重要
インデックスを貼った列をORDER BYやGROUP BYに使用する
また、オプティマイザが間違ってインデックスを使ってくれないときもある
→インデックスを強制指定する　FORCE INDEX

EXPLAIN（超重要！)

EXPLAINコマンドで、実行計画を見ることができる
※実行計画書とはどのようにSQLを実行するのかの計画書
（ユーザーからSQLが投げられたら、まずパーサがSQL文に間違いがないかチェック。そのあとオプティマイザが実行計画を作成）
詳しくは後半で・・・

LIMITに注意

全スキャンにならないように、絞り込んだサブテーブルを使うなどする

Select For Updateに注意

SelectしたデータをUpdateするとき、ロックがかかる。
量が多いとロックのせいで遅くなってしまう。

トランザクションを解除するまでに、余計な処理をいれない

クエリの生成処理はトランザクションを張る前にやる。
余計な処理が入っていると、トランザクションによるロック待ちが長くなってしまい、クエリが詰まりやすくなる。

パーティショニング

テーブルを物理的に分割。
レコード数が増えればそれだけクエリも遅くなる

シャーディング

水平分割

正規化

垂直分割する。やりすぎるとJOINが大量になるので、レコード数が多いとデメリットにもなる。

ORMが生成するSQLを確認

ORMがバグっていて変なSQLを生成していることもある

複合インデックスを正しく使う

うまくインデックスが効いてない場合もあるので、インデックスが効かないケースを調べる＆覚える

LIKE句でワイルドカードが文字列の前方についている

インデックスの走査においてLIKEが有効なのは、ワイルドカードの前まで！
ワイルドカードより後ろはインデックスが無効となり、クエリが重たくなる。

EXPLAINの見方

こちらの記事を参考に勉強しました！
ひとつひとつ解説してあって、めちゃくちゃわかりやすい！
nippondanji.blogspot.com

まずはスロークエリをみつける

（MySQLの場合）
MySQLの設定ファイルである、my.cnfの設定を見直す。
slow_query_logをONにすれば、スロークエリが出力される

EXPLAINで実行計画をみる

SQL文の一番前にEXPLAINをつけることで、そのSQLの実行計画をみることができる。

id/select_type

select_typeはクエリの種類を表すもの

type（重要！）

対象テーブルに対してどのような方法でアクセスするかを示す

  • const・・・PRIMARY KEYまたはUNIQUEインデックスのルックアップによるアクセス。一番早い
  • eq_ref・・・JOINにおいてPRIARY KEYまたはUNIQUE KEYが利用される時のアクセス。constと似ているが「JOINで」というのが特徴。
  • ref・・・PRIMARYまたはUNIQUEでないインデックスを使って等価検索（WHERE key = value）を行った時に使われるアクセス。
  • range・・・インデックスを用いた範囲検索。
  • index・・・フルインデックススキャン。
  • ALL・・・フルテーブルスキャン。

const,eq_ref,refだと良い
indexまたはALLを見かけたら、とても遅いのでチューニングする

possible_keys

オプティマイザがテーブルのアクセスに利用可能なインデックス候補としてあげたキーの一覧

key

オプティマイザによって選択されたキー

key_len

選択されたキーの長さ

ref

検索条件で、keyと比較されている値やカラムの種類

rows (重要！）

そのテーブルからフェッチされる行数の見積もり。
実際にフェッチされる正確な行数ではない。indexはってないと、ここがすごい数に・・・・

Extra （超重要！）
  • Using where・・・だいたいこれになっていることが多い。WHERE句に検索条件が指定されており、なおかつインデックスを見ただけではWHERE句の条件を全て適用することが出来ない場合に表示される。
  • Using index・・・クエリがインデックスだけを用いて解決できることを示す。
  • Using filesort・・・filesortでソートを行っていることを示す

などなど

Using filesort ソートするときにindexを張ってないとでる
ソートのときにもindexが大事

ポイント

どのテーブルのアクセスがもっとも遅いのかを見る。
Extraはオプティマイザの挙動を示すものだから、ここを見ればオプティマイザが各々のテーブルへのアクセスに何を実行しているのかがわかる。

（参考サイト）
http://otndnld.oracle.co.jp/document/products/wls/docs103/perform/basics.html
MySQLクエリパフォーマンス改善簡易まとめ - Qiita
なぜ、SQLは重たくなるのか？──『SQLパフォーマンス詳解』の翻訳者が教える原因と対策 - エンジニアHub｜若手Webエンジニアのキャリアを考える！
MySQLパフォーマンスチューニング -my.cnfの見直し- - Qiita