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

 *************************** . row ***************************

        Table: customer

 Create Table: CREATE TABLE `customer` (

   `id` int() unsigned NOT NULL AUTO_INCREMENT,

   `order_id` int() unsigned NOT NULL DEFAULT '',

   `name` varchar() NOT NULL DEFAULT '',

   `gender` enum('male','female') NOT NULL,

   PRIMARY KEY (`id`)

 ) ENGINE=InnoDB AUTO_INCREMENT= DEFAULT CHARSET=utf8mb4

  row in set (0.00 sec)

 root@localhost:mysql3306.sock [zlm]>show create table goods\G

 *************************** . row ***************************

        Table: goods

 Create Table: CREATE TABLE `goods` (

   `id` int() unsigned NOT NULL AUTO_INCREMENT,

   `order_id` int() unsigned NOT NULL,

   `goodsname` varchar() NOT NULL DEFAULT '',

   PRIMARY KEY (`id`)

 ) ENGINE=InnoDB AUTO_INCREMENT= DEFAULT CHARSET=utf8mb4

  row in set (0.00 sec)

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

 [root@zlm1 :: ~]

 #echo "select count(*) from customer join goods using(order_id);" > query1.sql

 [root@zlm1 :: ~]

 #pt-visual-explain -h192.168.56. -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

 |        +- Table

 |           table          customer

 +- Table scan -- It means "goods" is a drive table,do full table scan,too.

    rows

    +- Table

       table          goods

 [root@zlm1 :: ~]

 #

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                                              |

 +----+-------------+----------+------------+------+---------------+------+---------+------+-------+----------+----------------------------------------------------+

 |   | SIMPLE      | goods    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   |   100.00 | NULL                                               |

 |   | SIMPLE      | customer | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  |    10.00 | Using where; Using join buffer (Block Nested Loop) |

 +----+-------------+----------+------------+------+---------------+------+---------+------+-------+----------+----------------------------------------------------+

  rows in set,  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 :: ~]

 #mysql -e "use zlm;explain select count(*) from customer join goods where goods.goodsname='cellphone';" > explain1.log

 [root@zlm1 :: ~]

 #pt-visual-explain -h192.168.56. -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

 |     rows

 +- Filter with WHERE

    +- Table scan -- It means "goods" is a drive table,do full table scan,too.

       rows

       +- Table

          table          goods

 [root@zlm1 :: ~]

 #

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                                              |

 +----+-------------+----------+------------+-------+---------------+---------+---------+------+-------+----------+----------------------------------------------------+

 |   | SIMPLE      | goods    | NULL       | ALL   | NULL          | NULL    | NULL    | NULL |   |    10.00 | Using where                                        |

 |   | SIMPLE      | customer | NULL       | index | NULL          | PRIMARY |        | NULL |  |   100.00 | Using index; Using join buffer (Block Nested Loop) |

 +----+-------------+----------+------------+-------+---------------+---------+---------+------+-------+----------+----------------------------------------------------+

  rows in set,  warning (0.00 sec)

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

 [root@zlm1 :: ~]

 #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

 |        +- 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

          rows           

 [root@zlm1 :: ~]

 #select c.name,c.gender,g.goodsname from goods g,customer c where c.order_id=g.order_id and c.id<=;

 +------+--------+-----------+

 | 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  |

 +------+--------+-----------+

  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<=;

 +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------------------------+

 | id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                                              |

 +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------------------------+

 |   | SIMPLE      | c     | NULL       | range | PRIMARY       | PRIMARY |        | NULL |     |   100.00 | Using where                                        |

 |   | SIMPLE      | g     | NULL       | ALL   | NULL          | NULL    | NULL    | NULL |  |    10.00 | Using where; Using join buffer (Block Nested Loop) |

 +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------------------------+

  rows in set,  warning (0.00 sec)

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

 [root@zlm1 :: ~]

 #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

 |        +- 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

       rows

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.

巴特西

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

最新文章

热门文章