最近のプログラム開発はSpringなどのFrameworkを使っていることが多いと思います。

Frameworkの機能としてlog4などでアプリケーションログが出力されており、データアクセス処理のSQLやその実行時間を確認することができます。

まずはアプリケーションログから問題となるSQLを探しましょう。


問題の定義は、

　・SQLの実行時間が長い。

　・SQLの実行頻度が多い。

もしアプリケーションログを出力していない、またはアプリケーションログからデータアクセス処理を追うことができない場合は、デバッガー等を使用して問題のデータアクセス箇所を見つけましょう。

1.SQLの実行時間が長い

問題となったSQLの実行計画を取得し、さらに調査します。

2.SQLの実行頻度が多い

ループ処理の中でデータアクセス処理を行っていないかプログラムのロジックを確認してみてください。

データアクセスの実行頻度が少なくなるように、プログラムのロジックを再検討します。


例

・1回のデータアクセス(SQL)で対象データを全件取得する。

・1回のINSERT文で複数件のデータを追加する。

※FrameworkのORマッパーを使用している場合、SQLを直接記述できないので修正が難しい場合があるかもしれません…

1.実行計画とは

ユーザーが発行したSQLを解析し、決定したデータに対してアクセス方法や計算方法を検討した計画のことを言います。

DBMSはSQLを実行した経験(実行したときのデータ量、アクセス方法、所要時間など)を統計情報として保存しており、その統計情報から最適な方法をオプティマイザが判断しています。

2.実行計画の表示方法

MySQL WorkbenchやA5:SQL Mk-2などから下記文法を実行します。

EXPLAIN [※対象SQL]

3.実行計画の読み方

実行計画は下記のように表形式で表示されます。

各項目について説明します。


・id
SELECT文の識別子を表します。SQL内のSELECT文の連番です。

・select_type
SELECT文の種類を表します。

　SIMLE：サブクエリやユニオンが含まれないSQL
　
　SUBQUERY：サブクエリのSELECT文

　PRIMARY：ユニオンクエリの1個目のSELECT文

　UNION：ユニオンクエリの2個目以降のSELECT文

・table
出力行のテーブル名を表します。テーブルにエイリアスが指定されている場合はエイリアスが出力されます。

・partitions
クエリが参照したパーティション名を表します。テーブルがパーティション化されている場合のみ表示され、パーティション化されていない場合はNULLになります。

・type
テーブルの結合方法を表します。

・ALL
フルテーブルスキャン(テーブルのデータを全件読んでいる。)を表します。一番遅いアクセス方法です。

・index
フルインデックススキャン(インデックスのデータを全件読んでいる。)を表します。２番目に遅いアクセス方法です。

・range
インデックスを使用して範囲検索していることを表します。

・const
主キーやユニークキーを使用して検索していることを表します。一番速いアクセス方法です。

・possible_keys
テーブルの行検索に使用可能なインデックスを表します。

・key
実際に検索で使用されたインデックスを表します。

・key_len
実際に検索で使用されたインデックスの長さを表します。

・ref
行検索のときにインデックスと比較される項目や値を表します。

・rows
実行のために調査する行数を表します。行数が多い場合は推定値(見積値)となり、実際に読み込んだ行数とは異なります。

・filtered
フィルタ処理される行数の割合を表します。

・Extra
実行計画の追加情報を表します。

・Using where
WHERE句で検索条件が指定され、インデックスだけで検索条件を適用できないことを表します。

・Using filesort
クイックソートが使用されていることを表します。

・Using index
typeカラムがindexでインデックスがカバリングインデックスであることを表します。

・Uses index
typeカラムがindexでインデックスがカバリングインデックスではないことを表します。

4.実行計画の注目ポイント

・type列
type列に"ALL"や"index"がある場合、テーブルのデータを全件読み込んでいることを示してします。

この場合の対策は、テーブルのインデックス追加、主キーやインデックスの項目追加や項目の順序の変更を検討します。

・key列
key列がNULLの場合、主キーやインデックスが使用されていないことを示しています。

また、意図していた主キーやインデックスが表示されていない場合、SQLの条件に主キーやインデックスが合っていないことを示してします。

この場合の対策も、テーブルのインデックス追加、主

1.主キー、またはインデックスを見直す

SQLのWHERE句やFROM句のJOINの条件、ORDER BY句に合わせた主キーやインデックスを追加します。

既にインデックスが存在する場合はインデックスの項目の順序を変更し最適化します。

2.データアクセス処理を見直す

データアクセス処理のデータ取得条件やテーブルの結合条件を以下の観点で修正します。

・テーブルの主キーやインデックスの項目に合っていない箇所を修正する。

・条件が緩く余計なデータを取得している箇所を修正する。

・主キーやインデックスの項目にSQLのWHERE句やFROM句のJOIN条件、ORDER BY句を合わせる。（またはSQLのWHERE句やFROM句のJOIN条件、ORDER BY句に主キーやインデックスの項目を合わせる。）

・実行計画を確認し、type列に"ALL"や"index"が無いようにする。

・余計なデータを取得しないWHERE句やFROM句のJOIN条件にする。

・ループ処理中にデータアクセス処理を実行しないロジックにする。