Percona-Tookit工具包之pt-visual-explain

 

Preface

 

    As usual we will check the MySQL executed plan of SQL query by execute "explain select ... ;".It's a simple way to get the information of executed plan.Furthermore,we can also get a json format execution plan by execute "explain format=json select ... ;" for more detail of SQL query.Alternatively,we can also get another kind of execution plan organized by a tree modality.Well,what is that then?

 

Introduce

 

    pt-visual-explain relies on MySQL explain.It provides a easy-to-understand way by truning original explain output into a tree modaity.The tree is left-deep and depth-first(see it from bottom to roof).Its parameters are very simple(almost least in most of the tools in Percona-Toolkit).Let's see the details.

 

Procedure

 

Usage

pt-visual-explain [OPTIONS] [FILES]

 

Parameter

--clustered-pk -- For innodb,it allows primary key index access not to use bookmark lookup.
--format -- Set the type of output(default "tree",others "dump").
--connect -- Specify a followed file which contains a query and output result of explain on the query.
--database -- Specify which database to connect.
--host -- Specify connection hostname.
--port -- Specify connection port.
--user -- Specify connection user.
--password -- Specify connection password.
--socket -- Specify connection socket.

 

Examples

 

Create test table and insert rows into them(you can use procedure to do this).

root@localhost:mysql3306.sock [zlm]>show create table customer\G
*************************** 1. row ***************************
       Table: customer
Create Table: CREATE TABLE `customer` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `order_id` int(10) unsigned NOT NULL DEFAULT '0',
  `name` varchar(10) NOT NULL DEFAULT '',
  `gender` enum('male','female') NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=20001 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

root@localhost:mysql3306.sock [zlm]>show create table goods\G
*************************** 1. row ***************************
       Table: goods
Create Table: CREATE TABLE `goods` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `order_id` int(10) unsigned NOT NULL,
  `goodsname` varchar(10) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5001 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

 

Generate a tree using a file which contains a query statement.

[root@zlm1 18:34:29 ~]
#echo "select count(*) from customer join goods using(order_id);" > query1.sql

[root@zlm1 18:51:58 ~]
#pt-visual-explain -h192.168.56.100 -P3306 -urepl -prepl4slave -Dzlm --connect query1.sql
JOIN
+- Join buffer
|  +- Filter with WHERE
|     +- Table scan  -- It means "customer" is a drived table,do full table scan.
|        rows           19844
|        +- Table
|           table          customer
+- Table scan -- It means "goods" is a drive table,do full table scan,too.
   rows           5000
   +- Table
      table          goods

[root@zlm1 18:52:04 ~]
#

 

Compare the original explain result with the output above.

root@localhost:mysql3306.sock [zlm]>explain select count(*) from customer join goods using(order_id);
+----+-------------+----------+------------+------+---------------+------+---------+------+-------+----------+----------------------------------------------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra                                              |
+----+-------------+----------+------------+------+---------------+------+---------+------+-------+----------+----------------------------------------------------+
|  1 | SIMPLE      | goods    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  5000 |   100.00 | NULL                                               |
|  1 | SIMPLE      | customer | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 19844 |    10.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+----------+------------+------+---------------+------+---------+------+-------+----------+----------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

###The output of explain is compatiable with the output of tree above.###

 

Generate a tree using a file which contains a explain output.

[root@zlm1 19:13:36 ~]
#mysql -e "use zlm;explain select count(*) from customer join goods where goods.goodsname='cellphone';" > explain1.log

[root@zlm1 19:13:42 ~]
#pt-visual-explain -h192.168.56.100 -P3306 -urepl -prepl4slave explain1.log
JOIN
+- Join buffer
|  +- Index scan -- It means "customer" is a drive table,do index scan with primary.
|     key            customer->PRIMARY
|     key_len        4
|     rows           19844
+- Filter with WHERE
   +- Table scan -- It means "goods" is a drive table,do full table scan,too.
      rows           5000
      +- Table
         table          goods

[root@zlm1 19:13:46 ~]
#

 

Compare the original explain result with the output above.

root@localhost:mysql3306.sock [zlm]>explain select count(*) from customer join goods where goods.goodsname='cellphone';
+----+-------------+----------+------------+-------+---------------+---------+---------+------+-------+----------+----------------------------------------------------+
| id | select_type | table    | partitions | type  | possible_keys | key     | key_len | ref  | rows  | filtered | Extra                                              |
+----+-------------+----------+------------+-------+---------------+---------+---------+------+-------+----------+----------------------------------------------------+
|  1 | SIMPLE      | goods    | NULL       | ALL   | NULL          | NULL    | NULL    | NULL |  5000 |    10.00 | Using where                                        |
|  1 | SIMPLE      | customer | NULL       | index | NULL          | PRIMARY | 4       | NULL | 19844 |   100.00 | Using index; Using join buffer (Block Nested Loop) |
+----+-------------+----------+------------+-------+---------------+---------+---------+------+-------+----------+----------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

 

Generate a tree using standard input of MySQL command line with "-e" parameter.

[root@zlm1 19:23:49 ~]
#mysql -e "use zlm;explain select c.name,c.gender,g.goodsname from goods g,customer c where c.order_id=g.order_id and c.id<=5;" | pt-visual-explain
JOIN
+- Join buffer
|  +- Filter with WHERE
|     +- Table scan
|        rows           5000
|        +- Table
|           table          g -- Show table with alias "g" and it's a dirved table,do full table scan.
+- Filter with WHERE
   +- Bookmark lookup -- If you're using only innodb table,this kind of lookup will lead to bad performance.
      +- Table
      |  table          c -- Show table with alias "c" and it's a drive table,do index range scan.
      |  possible_keys  PRIMARY
      +- Index range scan
         key            c->PRIMARY
         possible_keys  PRIMARY
         key_len        4
         rows           5

[root@zlm1 19:24:10 ~]
#select c.name,c.gender,g.goodsname from goods g,customer c where c.order_id=g.order_id and c.id<=5;
+------+--------+-----------+
| name | gender | goodsname |
+------+--------+-----------+
| zlm  | male   | tv        |
| zlm  | male   | tv        |
| zlm  | male   | tv        |
| zlm  | male   | tv        |
| zlm  | male   | tv        |
| zlm  | male   | cd        |
| zlm  | male   | cd        |
| zlm  | male   | cd        |
| zlm  | male   | cd        |
| zlm  | male   | cd        |
| zlm  | male   | dvd       |
| zlm  | male   | dvd       |
| zlm  | male   | dvd       |
| zlm  | male   | dvd       |
| zlm  | male   | dvd       |
| zlm  | male   | cellphone |
| zlm  | male   | cellphone |
| zlm  | male   | cellphone |
| zlm  | male   | cellphone |
| zlm  | male   | cellphone |
| zlm  | male   | computer  |
| zlm  | male   | computer  |
| zlm  | male   | computer  |
| zlm  | male   | computer  |
| zlm  | male   | computer  |
+------+--------+-----------+
25 rows in set (0.00 sec)

 

Compare the original explain result with the output above.

root@localhost:mysql3306.sock [zlm]>explain select c.name,c.gender,g.goodsname from goods g,customer c where c.order_id=g.order_id and c.id<=5;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | c     | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    5 |   100.00 | Using where                                        |
|  1 | SIMPLE      | g     | NULL       | ALL   | NULL          | NULL    | NULL    | NULL | 5000 |    10.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

 

As the test tables are both innodb tables,use “--clustered-pk" option is recommended.

 

[root@zlm1 19:26:04 ~]
#mysql -e "use zlm;explain select c.name,c.gender,g.goodsname from goods g,customer c where c.order_id=g.order_id and c.id<=5;" | pt-visual-explain --clustered-pk
JOIN
+- Join buffer
|  +- Filter with WHERE
|     +- Table scan
|        rows           5000
|        +- Table
|           table          g
+- Filter with WHERE
   +- Index range scan -- This time the "bookmark lookup" is missing.It will lookup by pk directly what is more efficient way.
      key            c->PRIMARY
      possible_keys  PRIMARY
      key_len        4
      rows           5

 

Summary

• The "--clustered-pk" is only for innodb case to avoid bookmark lookup.
• If you specify the "--connect" option, a file contains SQL query need to be used,too.
• pt-visual-explain depends on explain of MySQL and provides several ways to generate trees.
• The information of pt-visual-explain is limited,if you want to get more details such as "cost_info","query_cost",etc.You'd better use json format of original MySQL explain.