【MySQL】クエリチューニング・Explainテクニック

MySQLのパフォーマンスチューニングで重要な、Explainを使ったクエリ調査に関するテクニックをまとめてみました。

Explain結果のカラム概要（一部）

• key_len
  • 利用したキーの長さ（バイト）
• rows
  • 実行計画上で検査するレコード数
  • 統計情報と実際のデータの分布に解離がある場合、実際に検査するレコード数とは解離する
• extra
  • 後述

Extraについて

公式リファレンス

dev.mysql.com

よくあるやつで注意すべきものをピックアップ

Using filesort

最後に追加でクイックソートが発生している

ソート対象の行が多いと遅くなる
結果レコード数が多い場合注意が必要

Using temporary

ソートのために一時テーブルを利用している

考えられるケース

• 集計関数を利用した結果を対象にしたソート
• 昇順と降順が混じったソート

where句での絞り込み後の結果サイズによっては遅くなる

インデックスを指定してExplain

インデックスの候補が複数ある場合、それぞれでExplainして比較できる。
複数指定可能で、その中から最適と思われるインデックスが選択される。

explain
select c1 from t1
use index(idx_hogehoge)
where <略>

実際にqueryの実行もできる

select c1 from t1
use index(idx_hogehoge)
where <略>

実際の検査レコード数を確認

explainのrowsで確認できるが、あくまで統計情報からの予測でしかない。
実際に確認した方が無難。

Handler_% ステータス変数で確認することが可能。

ステータス変数とはMySQLの内部ステータスで、セッションスコープとグローバルスコープがある。 それぞれ show session status, show global status で表示させられる。

また、 flush status でクリア可能（セッションスコープ変数のみクリアされる模様）

flush status;

select c1 from t1
where <略>;

show session status like "Handler_%";

Handler_read_next が検査レコード数。

他にセッションスコープ変数にはどんなものがあるのか（ Handler_ は省略）

• commit
• delete
• discover
• external_lock
• mrr_init
• prepare
• read_first
• read_key
• read_last
• read_next
• read_prev
• read_rnd
• read_rnd_next
• rollback
• savepoint
• savepoint_rollback
• update
• write

リファレンス

dev.mysql.com

explainのrowsと実際の検査レコード数が解離している場合

統計情報を疑うべし

統計情報を更新してみる

analyze table t1;

解離している理由として考えられるもの

1. 統計情報が更新されていない

   • テーブル全体の10%以上（詳しくは設定を確認）が更新されると統計情報が再作成される
   • トラフィックがある環境であれば、頻繁に作成されている

2. 統計情報のデータ量が少なすぎる

   • サンプリングされるのは1インデックスあたり20ページ（ページのデフォルトは16kB）
   • インデックスが数MBであれば十分な精度だが、サイズが大きくなると精度が悪くなる。
   • 1インデックスあたりのサンプリングページ数を innodb_stats_persistent_sample_pages オプションで指定。もしくは alter table でテーブルごとに指定可能。

そのほかにExplainで可能なこと

• delete, insert, replace, updateもexplainが実行できる
• explain format=json とすると結果がjson形式で返ってくる
• explain for connection n のnに show processlist のidを指定することで、実行中のステートメントをexplain可能

クエリのボトルネックを調査

show profile でクエリのどの工程に時間がかかっているのか調査できる

MySQL5.6以降では非推奨らしい
-> performance_schemaが推奨

利用方法

プロファイルを実行するように設定

set session profilling = 1;

直前のクエリをプロファイルする場合

show profile;

過去のクエリをプロファイルする場合

show profiles;

show profile for query <id>;