スロークエリログを集計しやすくするツール
pt-query-digest
業務上での使用例
1日のスローログの件数を集計し、件数のみ通知する
→チューニングしやすいクエリの発見に役立つ
とりあえずEXPLAINする
クエリ
SELECT COUNT(some_column) FROM table WHERE some_column = '111'
explain結果からインデックスが何が使われてるか
possible_keys (使えそうなインデックス) : some_index, another_index
keys (使っているインデックス) : another_index
結局どっちのインデックスの方が良いかわからない....
↓
インデックスを効かせてみよう！！
インデックスを効かせる
SELECT COUNT(column) FROM USE INDEX(some_index) table WHERE column = '111'
実際にクエリが読み込んだ行数を知る
# ステータスクリア
FLUSH STATUS
# 調べたいSQL文
SELECT COUNT(column) FROM USE INDEX(some_index) table WHERE column = '111'
# 読み込み行数を表示
SHOW SESSION STATUS LIKE 'Handler\_%'
Handler_road_next に読み取り行数が記述される
ちなみに
FLUSH STATUSの権限がなかったら以下のようなコマンドで追加してあげてね！
GRANT RELOAD ON *.* TO '<user>'@'<host_name>';
RELOAD権限についてはこちら
https://dev.mysql.com/doc/refman/5.6/ja/privileges-provided.html
Explainで見た行数と乖離している場合
統計情報を元にオプティマイザーが検査すべき行数を判断しているため、
統計情報の「サンプリング値」が実際にデータ分布と著しく乖離している場合間違っている可能性が高い。
# 再度サンプリングする
ANALYZE TABLE
それでも変わらない場合は以下の二種類が考えられる(InnoDBの場合)
* 前回の統計情報の更新から累計して、テーブル全体の10%以上が更新された場合、バックグラウンドで統計情報を再作成してくれるため、既に更新してくれていた場合
* インデックス一つに対して、のサンプリングする行数は指定されている(InnoDBではデフォルトでは16kB)ため、インデックスのサイズが大きくなると精度が悪くなる。
- インデックスの容量が増えると同時に、サンプリング数も増やすべき innodb_status_persistent_sample_page オプションで指定する
そのExtraは本当に望ましいものなのか?
https://dev.mysql.com/doc/refman/5.6/ja/explain-output.html#explain-extra-information
Extraでよく見にする3つ
Using filesort : 行のフェッチを評価の後に追加でクイックソートが発生していることを示す
Where句で絞れるだけ絞らなければ出力結果の数に応じて計算量が増えていく。
なお、クイックソートのオーダーはO(n log n)
Using index
インデックス上に書かれた情報だけで情報の取り出しを終了したと言う意味。
通常MySQLのインデックスはB+Treeであるため、通常は以下の順番で探すが、最初の処理だけで情報をとり出せる
1. インデックス上から条件にマッチするリーフを探す
1. リーフ上に書かれた情報から行の位置を探す
1. 行をフェッチする
Using temporary
ソートのために、暗黙のテンポラリーテーブルを使用していることを示す
(暗黙のテンポラリーテーブル: CREATE TEMPORARY TABLEステートメントで作成するテンポラリーテーブルのこと)
使用する場合
- 集計関数を使用した結果でのソートは
- 昇順、降順が混じったソート
使用しない場合
- 単にインデックスがないカラムや、関数や演算子を使用したソートではクイックソート。
デメリット
- 一定のサイズ(
max_heap_table_size,tmp_table_sizezの小さい方)を超えるとMyISAM(5.7ではデフォルトでInnoDB)でストレージ上に固定されるため、性能が劣化する
EXPLAINの注意点
5.6の変更点
- DELETE, INSERT, REPLACE, UPDATEステートメントがEXPLAINできるようになった
-
EXPLAIN format=jsonがサポートされるようになったため、出力がJson形式で帰ってくるように無っった。 #### 5.7の変更点 - EXTENDEDキーワードとPARTITIONSキーワードがデフォルトで指定された状態になった。
-
EXPLAIN FOR CONNECTION n構文がサポートされた。nにshow processlistで出力されるidを指定すると、実行中のステートメントをEXPLAINできる
まとめ
- 実際に最適なインデックスを調べるためには、使えるインデックスを試して見ることが一番
-
Using filesort,Using temporaryは WHERE句で絞り込んだ後の結果セットが大きくなるほど、遅くなる