Percona Took Kitでビジュアルに実行計画を確認しよう

こんにちは、マイケルです。 チーム内ではDBおじさんとか呼ばれていますが、前職まではOracleなどの商用製品を扱っていたので、MySQLについてはまだまだ勉強中です。 そんな私が普段からとても不満に思っているのが、MySQLのEXPLAINコマンドです。 本来、実行計画はツリー構造であるはずなのに、MySQLのEXPLAINの結果は表形式で表示されるため、ツリーがわかりにくいと常々思っていました。 もちろん、MySQL WorkbenchのビジュアルEXPLAIN機能を使えばツリーで表示されますが、こちらはExtra列の情報などが表示されません。

そんなことを思いながら日々過ごしていたのですが、とあるトラブルについて調査していたときに、Percona Tool Kitなるサードパーティ製品があることを教えて頂きました。 なにやらMasterとSlaveでデータにズレがあっても同期ができるだとか(上記のトラブルはこれだったんですが)、slow-queryログの分析ができるだとか、ALTER TABLEをロック無しで実行できるだとか、MySQLの標準機能だけでは届かない、ムズ痒い所に手が届くようなツールっぽいです。 公式サイト( https://www.percona.com/ )のドキュメントを眺めていたところ、 pt-visual-explain という機能が目についたので色々試してみました。

前提

Percona Tool Kitはサードパーティ製品なので、当然MySQL Serverをインストールしただけでは入っていません。 別途インストールが必要です。 非常に便利な機能が沢山あるので、開発用サーバにはもちろん、本番用DBサーバにも事前に導入しておくことをお勧めします。 導入や使い方は公式ドキュメント( https://www.percona.com/doc/percona-toolkit/LATEST/index.html )を御覧ください。

サンプルテーブルの構造

実験用に手元のMySQL 5.6.24に以下のようなテーブルを作成しました。

所在地(location)テーブル、部署(dept)テーブル、社員(emp)テーブルです。 記事の最下部に、テーブル作成のDDL及びDMLを置いておきます。 手元で確認したい方はどうぞお使いください(主にうちの社員)

MySQLのEXPLAIN(単純なJOIN)

上記、3つのテーブルをJOINしたときのEXPLAINを確認してみます。

EXPLAIN SELECT
    e.emp_no,
    e.emp_name,
    e.dept_no,
    d.dept_name,
    d.location_no,
    l.location_name
FROM
    emp e
INNER JOIN
    dept d
ON
    e.dept_no = d.dept_no
INNER JOIN
    location l
ON
    d.location_no = l.location_no;

SQLとしてはWHEREの絞り込みなしで、3つのテーブルをJOINするだけです。

結果は以下の通り。

+----+-------------+-------+------+-------------------------+-----------------+---------+--------------------+------+-------+
| id | select_type | table | type | possible_keys           | key             | key_len | ref                | rows | Extra |
+----+-------------+-------+------+-------------------------+-----------------+---------+--------------------+------+-------+
|  1 | SIMPLE      | l     | ALL  | PRIMARY                 | NULL            | NULL    | NULL               |    2 | NULL  |
|  1 | SIMPLE      | d     | ref  | PRIMARY,dept_loc_no_idx | dept_loc_no_idx | 5       | test.l.location_no |    1 | NULL  |
|  1 | SIMPLE      | e     | ref  | emp_dept_no_idx         | emp_dept_no_idx | 5       | test.d.dept_no     |    2 | NULL  |
+----+-------------+-------+------+-------------------------+-----------------+---------+--------------------+------+-------+

こうなりました。 ちょっとテキストの折り返しがひどいのでキャプチャした画像も置いときます。

今回はWHERE句の絞り込みがありませんので、必ずどこかのテーブルでフルスキャン(テーブル全件を舐める)が必要です。 Nested Loop Joinでは、最初にフェッチされるテーブルの行数が少ないほうが有利ですから、今回はlocationテーブルからスタートするのは予想通りです。 また、今回はJOINの条件で使われる列にはINDEXを作成していますので、順当にlocation → dept → emp とJOINをしていくはずです。 ということで、EXPLAINの結果は、上から順に読んでいけばいいと思えます。(id列の値が同じ場合の話。異なるid列の値がある場合は、id列の小さい順で処理されるとのこと)

では次の例ではどうでしょう。

EXPLAIN SELECT
    e.emp_no,
    e.emp_name,
    e.dept_no,
    d.dept_name,
    d.location_no,
    l.location_name
FROM
    emp e
INNER JOIN
    dept d
ON
    e.dept_no = d.dept_no
INNER JOIN
    location l
ON
    d.location_no = l.location_no
WHERE
    d.dept_no = 10;

先程のSQLにWHERE句の絞り込みを追加しています。

ちなみにこの図、SQLで扱うテーブルのER図に、JOINの条件とかWHEREの絞り込み条件なんかを一緒に書き込んでます。 ごちゃっとしたSQLの解読が必要なときに、頭のなかを整理するのに便利なのでお勧めです。 結果は以下の通り。

+----+-------------+-------+-------+-------------------------+-----------------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys           | key             | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+-------------------------+-----------------+---------+-------+------+-------+
|  1 | SIMPLE      | d     | const | PRIMARY,dept_loc_no_idx | PRIMARY         | 4       | const |    1 | NULL  |
|  1 | SIMPLE      | l     | const | PRIMARY                 | PRIMARY         | 4       | const |    1 | NULL  |
|  1 | SIMPLE      | e     | ref   | emp_dept_no_idx         | emp_dept_no_idx | 5       | const |    5 | NULL  |
+----+-------------+-------+-------+-------------------------+-----------------+---------+-------+------+-------+

まずSQLで変わった点は、WHERE句に d.dept_no = 10 を追加したところだけです。 deptテーブルのdept_no列はPRIMARY KEYですから、等価条件で絞り込めば最大で1行しか結果を返しません。 つまり、Nested Loop Joinにおける起点のテーブルに最適なはずです。 EXPLAINの結果をみると、同じid列の値で上から順に、 dept → location → emp とJOINしており、やはり上から順に実行しているらしいと見て取れます。

では次に、WHERE句の条件を変更してみます。

EXPLAIN SELECT
    e.emp_no,
    e.emp_name,
    e.dept_no,
    d.dept_name,
    d.location_no,
    l.location_name
FROM
    emp e
INNER JOIN
    dept d
ON
    e.dept_no = d.dept_no
INNER JOIN
    location l
ON
    d.location_no = l.location_no
WHERE
    e.dept_no = 10;

絞り込みの対象をempテーブルへ変えました。 結果は以下の通り。

+----+-------------+-------+-------+-------------------------+-----------------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys           | key             | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+-------------------------+-----------------+---------+-------+------+-------+
|  1 | SIMPLE      | d     | const | PRIMARY,dept_loc_no_idx | PRIMARY         | 4       | const |    1 | NULL  |
|  1 | SIMPLE      | l     | const | PRIMARY                 | PRIMARY         | 4       | const |    1 | NULL  |
|  1 | SIMPLE      | e     | ref   | emp_dept_no_idx         | emp_dept_no_idx | 5       | const |    5 | NULL  |
+----+-------------+-------+-------+-------------------------+-----------------+---------+-------+------+-------+

empテーブルのdept_noにはINDEXがありますが、NON UNIQUEですので、 emp.dept_no=10 で絞り込んだ結果が複数行戻ります。 なので、少ない行のテーブルを起点のJOINをしようとする場合、empテーブルからスタートするのが最適かどうか微妙なところになります。 EXPLAINの結果を見ると、上から順に dept → location → emp と処理されたように見えます。

ではここで、EXLAINの中身をもう少し詳しくみてみます。 まず、一番上で処理されているdeptテーブルへのアクセスです。

+----+-------------+-------+-------+-------------------------+-----------------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys           | key             | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+-------------------------+-----------------+---------+-------+------+-------+
|  1 | SIMPLE      | d     | const | PRIMARY,dept_loc_no_idx | PRIMARY         | 4       | const |    1 | NULL  |
+----+-------------+-------+-------+-------------------------+-----------------+---------+-------+------+-------+

key にPRIMARY とありますので、dept_no列のINDEXを使用したということを表しています。 これっておかしくないですか? WHERE句の条件は empテーブルのdept_no列が10のもので絞り込みました。 それなのに、いきなりdeptテーブルのdept_no列が10のものを探そうとして、このINDEXを使ったように見えます。 本当にdeptテーブルから処理がスタートしているんでしょうか。

一方で、最後の行にあるempテーブルへの処理はこうなってます。

+----+-------------+-------+-------+-------------------------+-----------------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys           | key             | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+-------------------------+-----------------+---------+-------+------+-------+
|  1 | SIMPLE      | e     | ref   | emp_dept_no_idx         | emp_dept_no_idx | 5       | const |    5 | NULL  |
+----+-------------+-------+-------+-------------------------+-----------------+---------+-------+------+-------+

empテーブルもdept_no列のINDEXを使っています。 この場合、上の業から順に処理した場合でも、empテーブルから処理した場合でも、どちらで考えてもこのINDEXを使うことはおかしくありません。

このような実行計画をみると、これは本当に上から順に読んで正解なのか?という疑問が出てきます。 公式ドキュメントにも、読む順番についての記述が見当たらないので、ずっと不安に思っていたところでした。 (もしドキュメントに説明があったら教えてください。)

Percona Too Kitのpt-visual-explainで見てみる

ここでPercona Took Kitを試してみます。 細かい使い方は公式ドキュメントを参照( https://www.percona.com/doc/percona-toolkit/LATEST/pt-visual-explain.html

まずは、deptテーブルで絞り込んだパターン 一々SQLをファイルに保存して、pt-visual-explainから呼び出すのが面倒なので、ワンライナーで渡しちゃいます。

mysql -u hoge -phuga dbname -e "EXPLAIN SELECT e.emp_no, e.emp_name, e.dept_no, d.dept_name, d.location_no, l.location_name FROM emp e INNER JOIN dept d ON e.dept_no = d.dept_no INNER JOIN location l ON d.location_no = l.location_no WHERE d.dept_no = 10" | pt-visual-explain

JOIN
+- Bookmark lookup
|  +- Table
|  |  table          e
|  |  possible_keys  emp_dept_no_idx
|  +- Index lookup
|     key            e->emp_dept_no_idx
|     possible_keys  emp_dept_no_idx
|     key_len        5
|     ref            const
|     rows           5
+- JOIN
   +- Bookmark lookup
   |  +- Table
   |  |  table          l
   |  |  possible_keys  PRIMARY
   |  +- Constant index lookup
   |     key            l->PRIMARY
   |     possible_keys  PRIMARY
   |     key_len        4
   |     ref            const
   |     rows           1
   +- Bookmark lookup
      +- Table
      |  table          d
      |  possible_keys  PRIMARY,dept_loc_no_idx
      +- Constant index lookup
         key            d->PRIMARY
         possible_keys  PRIMARY,dept_loc_no_idx
         key_len        4
         ref            const
         rows           1

おお、見事にツリー構造で表示されました。 実行計画のツリーは、ツリーの深い部分から処理されます。 ということはlocationテーブルとdeptテーブルのJOINをやってから、その結果とempテーブルをJOINしているということがわかります。 そして、locationテーブルとdeptテーブルでは、deptテーブルの方が下に出力されていますので、おそらく同じ階層の高さなら下から見ていけば良さそうです。(たまたまかもしれませんが) つまりこういう順での処理ですね。 1. deptテーブルから「dept_no = 10」のWHERE句の条件に合致する行を、PRIMARYのINDEXを使って1行取得 2. 1の結果の中のlocation_no列の値を使って、locationテーブルからPRIMARYのINDEXを使って1行取得 3. 1と2の結果をJOIN 4. 3の結果の中のdept_no列の値を使って、empテーブルからdept_no列のINDEXを使って5行取得 5. 3と4の結果を都度JOIN

とてもわかりやすい出力です。

では、WHERE句の条件をempテーブルのdept_noに対するものに変えてみます。

mysql -u hoge -phuga dbname -e "EXPLAIN SELECT e.emp_no, e.emp_name, e.dept_no, d.dept_name, d.location_no, l.location_name FROM emp e INNER JOIN dept d ON e.dept_no = d.dept_no INNER JOIN location l ON d.location_no = l.location_no WHERE e.dept_no = 10" | pt-visual-explain

結果は以下の通り。

JOIN
+- Bookmark lookup
|  +- Table
|  |  table          e
|  |  possible_keys  emp_dept_no_idx
|  +- Index lookup
|     key            e->emp_dept_no_idx
|     possible_keys  emp_dept_no_idx
|     key_len        5
|     ref            const
|     rows           5
+- JOIN
   +- Bookmark lookup
   |  +- Table
   |  |  table          l
   |  |  possible_keys  PRIMARY
   |  +- Constant index lookup
   |     key            l->PRIMARY
   |     possible_keys  PRIMARY
   |     key_len        4
   |     ref            const
   |     rows           1
   +- Bookmark lookup
      +- Table
      |  table          d
      |  possible_keys  PRIMARY,dept_loc_no_idx
      +- Constant index lookup
         key            d->PRIMARY
         possible_keys  PRIMARY,dept_loc_no_idx
         key_len        4
         ref            const
         rows           1

MySQLのEXPLAINコマンドで確認したように、やはりdeptテーブルからスタートしているようです。 つまり、先程の結果はやはりid列が同じものの中で、上から順に読んで正解だったということになります。

+----+-------------+-------+-------+-------------------------+-----------------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys           | key             | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+-------------------------+-----------------+---------+-------+------+-------+
|  1 | SIMPLE      | d     | const | PRIMARY,dept_loc_no_idx | PRIMARY         | 4       | const |    1 | NULL  |
|  1 | SIMPLE      | l     | const | PRIMARY                 | PRIMARY         | 4       | const |    1 | NULL  |
|  1 | SIMPLE      | e     | ref   | emp_dept_no_idx         | emp_dept_no_idx | 5       | const |    5 | NULL  |
+----+-------------+-------+-------+-------------------------+-----------------+---------+-------+------+-------+

なるほどなるほど。 ツリー表示されるとわかりやすいなー。

WHERE句で指定されたテーブルは?

はい、ここでまた先程の疑問です。 今回のWHERE句は 「WHERE e.dept_no = 10」 です。 empテーブルに対して絞り込みをしています。

それなのに、実行計画を見るとdeptテーブルから先に絞り込んでいます。 これは何が起きているのか。

という点については、実はoptimizer_traceという機能で、MySQLオプティマイザがどのようにSQLを最適化しているのかをみれば確認できますが、これについては別の記事で紹介したいと思います。 簡単にだけ言うと、以下のようなことをMySQLが考えて、SQLを組み替えてくれています。

  1. WHERE e.dept_no =10 っていう条件あるじゃん?
  2. empとdeptのJOINの条件が e.dept_no = d.dept_no じゃん?
  3. ということは、e.dept_no = 10 の結果でJOINするんだから、 WHERE d.dept_no = 10 と読み替えても一緒じゃん?

という感じで、今回はdeptテーブルからスタートした方が効率がよいと判断しているようです。 賢いですねー。 我々が多少へっぽこなSQLを書いても考えてくれているんですね。

まとめ

今回は、MySQLのEXPLAINの結果をツリー形式で表示してくれる pt-visual-explain というツールを紹介しました。 得られる情報としては、標準のEXPLAINと同じですが、ツリー構造が把握できる点と、お手軽に使える点がお勧めです。

EXPLAINの読み方については、処理順や、どのINDEXを使っているかだけでなく、他にも注目するべき情報が色々とありますが、そのあたりも別の記事で紹介できたらと思います。 また、このEXLPAINの結果を見た上で、どのような考え方でSQLを最適化していくのか、どのようなINDEXチューニングをすればよいのかもいつか説明したいと考えています。(主に弊社若手開発メンバー向けに)

サンプルスクリプト

CREATE TABLE location (
    location_no INT NOT NULL PRIMARY KEY,
    location_name VARCHAR(20));

INSERT INTO location VALUES(10, 'Shinjuku');
INSERT INTO location VALUES(20, 'Oomiya');
INSERT INTO location VALUES(30, 'Umeda');


CREATE TABLE dept (
    dept_no INT NOT NULL PRIMARY KEY,
    dept_name VARCHAR(30) NOT NULL,
    location_no INT);

CREATE INDEX dept_loc_no_idx ON dept(location_no);

INSERT INTO dept VALUES(10, 'Soumu', 10);
INSERT INTO dept VALUES(20, 'Keiri', 10);
INSERT INTO dept VALUES(30, 'Marketing', 20);
INSERT INTO dept VALUES(40, 'Eigyou', 30);
INSERT INTO dept VALUES(50, 'Kaihatsu', 30);


CREATE TABLE emp (
    emp_no INT NOT NULL PRIMARY KEY,
    emp_name VARCHAR(20) NOT NULL,
    tel VARCHAR(15),
    salary INT,
    dept_no INT);

CREATE INDEX emp_dept_no_idx ON emp(dept_no);

INSERT INTO emp VALUES(1, 'Furuse', '03-1111-2222', '1000000', 10);
INSERT INTO emp VALUES(2, 'Nakatake', '03-1111-2223', '500000', 20);
INSERT INTO emp VALUES(3, 'Maeyama', '03-1111-2224', '500000', 30);
INSERT INTO emp VALUES(4, 'Nakamura', '03-1111-2225', '500000', 40);
INSERT INTO emp VALUES(5, 'Takahashi', '03-1111-2226', '500000', 50);
INSERT INTO emp VALUES(6, 'Suzuki', '03-1111-2227', '500000', 10);
INSERT INTO emp VALUES(7, 'Yamada', '03-1111-2228', '500000', 20);
INSERT INTO emp VALUES(8, 'Yoshida', '03-1111-2229', '500000', 30);
INSERT INTO emp VALUES(9, 'Tanaka', '03-1111-2210', '500000', 40);
INSERT INTO emp VALUES(10, 'Satou', '03-1111-2222', '500000', 50);
INSERT INTO emp VALUES(11, 'Kondou', '03-1112-2222', '500000', 10);
INSERT INTO emp VALUES(12, 'Takeda', '03-1113-2222', '500000', 20);
INSERT INTO emp VALUES(13, 'Sasaki', '03-1114-2222', '500000', 30);
INSERT INTO emp VALUES(14, 'Aihara', '03-1115-2222', '500000', 40);
INSERT INTO emp VALUES(15, 'Katou', '03-1116-2222', '500000', 50);
INSERT INTO emp VALUES(16, 'Kimura', '03-1117-2222', '500000', 10);
INSERT INTO emp VALUES(17, 'Kobayashi', '03-1118-2222', '500000', 20);
INSERT INTO emp VALUES(18, 'Tachibana', '03-1119-2222', '500000', 30);
INSERT INTO emp VALUES(19, 'Chiba', '03-1110-2222', '500000', 40);
INSERT INTO emp VALUES(20, 'Tsuchida', '03-1111-2222', '500000', 40);
INSERT INTO emp VALUES(21, 'Tohno', '03-2222-2222', '500000', 10);
INSERT INTO emp VALUES(22, 'Nakano', '03-3333-2222', '500000', 20);
INSERT INTO emp VALUES(23, 'Nukumizu', '03-4444-2222', '500000', 30);
INSERT INTO emp VALUES(24, 'Nomura', '03-5555-2222', '500000', 40);