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