pt-visual-explain

MySQL の EXPLAIN を見やすくしてくれるツール

• Percona Toolkit collection: pt-visual-explain - MySQL Performance Blog
• pt-visual-explain — Percona Toolkit Documentation

お手軽に使ってみる

準備

$ wget http://percona.com/get/pt-visual-explain
$ chmod +x ./pt-visual-explain

テストで使うテーブル

$ # new_table
$ mysql -h ${MYSQL_HOST} -uadmin sample -e 'select * from new_table'
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | foo  |   1 |
|  2 | bar  |   2 |
|  3 | bazz |   3 |
+----+------+-----+
$ # new_table2
$ mysql -h ${MYSQL_HOST} -uadmin sample -e 'select * from new_table2'
+------+--------+
| name | profit |
+------+--------+
| bar  |   5000 |
| bazz |   3200 |
| foo  |  10000 |
+------+--------+

普通の EXPALIN

$ mysql -h ${MYSQL_HOST} -uadmin sample \
-e 'explain select new_table.name, new_table.age, new_table2.profit from new_table left join new_table2 on new_table.name = new_table2.name'
+----+-------------+------------+--------+---------------+---------+---------+-----------------------+------+-------+
| id | select_type | table      | type   | possible_keys | key     | key_len | ref                   | rows | Extra |
+----+-------------+------------+--------+---------------+---------+---------+-----------------------+------+-------+
|  1 | SIMPLE      | new_table  | ALL    | NULL          | NULL    | NULL    | NULL                  |    3 |       |
|  1 | SIMPLE      | new_table2 | eq_ref | PRIMARY       | PRIMARY | 182     | sample.new_table.name |    1 |       |
+----+-------------+------------+--------+---------------+---------+---------+-----------------------+------+-------+

EXPAIN の結果を pt-visual-explain に食わせた場合

$ mysql -h ${MYSQL_HOST} -uadmin sample \
-e 'explain select new_table.name, new_table.age, new_table2.profit from new_table left join new_table2 on new_table.name = new_table2.name' \
| ./pt-visual-explain 

JOIN
+- Bookmark lookup
|  +- Table
|  |  table          new_table2
|  |  possible_keys  PRIMARY
|  +- Unique index lookup
|     key            new_table2->PRIMARY
|     possible_keys  PRIMARY
|     key_len        182
|     ref            sample.new_table.name
|     rows           1
+- Table scan
   rows           3
   +- Table
      table          new_table