MariaDBの実行計画を参照する

Last updated at 2020-12-08Posted at 2020-12-08

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)などの情報はあるのですが、どこで時間がかかっているのかが素人には分かりにくいです。

何かオプションがあるのかなと思いドキュメントを読むとありました。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がどう考えてクエリを処理しようとしているは分かってきましたが、実際に実行した結果がどうなのかをもう少し知りたいところです。

ドキュメントを読みすすめるとお隣のページに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は実際に絞り込まれた割合(%)のようです。

これでオプティマイザーの見積もりと実際の乖離が分かるようになりました。

でもやっぱりかかっている時間が知りたいので探してみると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を調べられます。