MariaDBには、クエリの実行計画の参照方法がいろいろあって、MariaDB初心者が混乱したというお話です。
あくまで参照する方法なので、この記事を読んでも、実行計画を読み解く方法も、クエリをチューニングする方法もわかりません。とりあえず手がかりとなる情報を表示できるだけです。
##EXPLAIN
以前触ったことのあるDBMSの知識をもとに思いついたキーワードで検索するとありました。クエリの頭にこのキーワードをつけるだけのようです。
さっそくやってみます。（クエリは適当です）
EXPLAIN SELECT school.name, student.grade, student.name FROM student INNER JOIN school ON student.school_id = school.id WHERE grade = 3;
するとこんな感じ。（見にくいのでIDEで行列を入れ替えています）
想像していたものよりあっさりしています。検索の種類（type）、使用するインデックス(key)、keyで絞り込まれる行数の見積もり(row)などの情報はあるのですが、どこで時間がかかっているのかが素人には分かりにくいです。
##EXPLAIN EXTENDED
何かオプションがあるのかなと思いドキュメントを読むとありました。EXTENDEDキーワードをつけられるようです。
再チャレンジです。
EXPLAIN EXTENDED SELECT school.name, student.grade, student.name FROM student INNER JOIN school ON student.school_id = school.id WHERE grade = 3;
filtered（反転させた行）が追加されました。その条件で絞り込める割合(%)の見積もりのようです。
MariaDBがどう考えてクエリを処理しようとしているは分かってきましたが、実際に実行した結果がどうなのかをもう少し知りたいところです。
##ANALYZE
ドキュメントを読みすすめるとお隣のページにALNALYZEというものがありました。どうやらこっちのほうが強力そうです。
えいっ。
ANALYZE SELECT school.name, student.grade, student.name FROM student INNER JOIN school ON student.school_id = school.id WHERE grade = 3;
r_rowsとr_filtered（反転させた行）が追加されました。接頭辞のr_は多分realとかrealityの意味で、r_rowsは実際に抽出された行数、r_filteredは実際に絞り込まれた割合(%)のようです。
これでオプティマイザーの見積もりと実際の乖離が分かるようになりました。
##ANALYZE FORMAT=JSON
でもやっぱりかかっている時間が知りたいので探してみるとFORMAT=JSONをつけると分かるようです。FORMATなのにまさか情報が増えるとは思いませんでした。ドキュメントは別ページだし、完全に見落としていました。
ANALYZE FORMAT=JSON SELECT school.name, student.grade, student.name FROM student INNER JOIN school ON student.school_id = school.id WHERE grade = 3;
フォーマットが違うので雰囲気が変わりましたが、これまでの情報に加えていくつか情報が追加されています。その中にありましたr_total_time_ms
。上位のノードの時間は、下位の時間を包含するようです。
##結論
クエリの情報を詳細に知りたいときは、ANALYZE FORMAT=JSONを使っておけばよさそうです。
なお、ANALYZEはEXPLAINとは違い、実際にクエリを発行します。本番環境でいきなりUPDATE文やDELETE文をANALYZEしたりするとたぶん怒られます。
##その他
####調べるのを邪魔してくれたキーワードたち
-
EXPLAIN ANALYZE・・・ MariaDB 10.1以降はANALYZEに取って代わられたので使えません。 -
SHOW EXPLAIN・・・ スレッドIDを指定して実行中のクエリの実行計画を出力します。SHOW PROCESSLISTでスレッドIDを調べられます。
####試したバージョン
- MariaDB v10.2.21