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
1 pt-visual-explain [OPTIONS] [FILES]
Parameter
1 --clustered-pk -- For innodb,it allows primary key index access not to use bookmark lookup. 2 --format -- Set the type of output(default "tree",others "dump"). 3 --connect -- Specify a followed file which contains a query and output result of explain on the query. 4 --database -- Specify which database to connect. 5 --host -- Specify connection hostname. 6 --port -- Specify connection port. 7 --user -- Specify connection user. 8 --password -- Specify connection password. 9 --socket -- Specify connection socket.
Examples
Create test table and insert rows into them(you can use procedure to do this).
1 root@localhost:mysql3306.sock [zlm]>show create table customerG 2 *************************** 1. row *************************** 3 Table: customer 4 Create Table: CREATE TABLE `customer` ( 5 `id` int(10) unsigned NOT NULL AUTO_INCREMENT, 6 `order_id` int(10) unsigned NOT NULL DEFAULT `0`, 7 `name` varchar(10) NOT NULL DEFAULT ``, 8 `gender` enum(`male`,`female`) NOT NULL, 9 PRIMARY KEY (`id`) 10 ) ENGINE=InnoDB AUTO_INCREMENT=20001 DEFAULT CHARSET=utf8mb4 11 1 row in set (0.00 sec) 12 13 root@localhost:mysql3306.sock [zlm]>show create table goodsG 14 *************************** 1. row *************************** 15 Table: goods 16 Create Table: CREATE TABLE `goods` ( 17 `id` int(10) unsigned NOT NULL AUTO_INCREMENT, 18 `order_id` int(10) unsigned NOT NULL, 19 `goodsname` varchar(10) NOT NULL DEFAULT ``, 20 PRIMARY KEY (`id`) 21 ) ENGINE=InnoDB AUTO_INCREMENT=5001 DEFAULT CHARSET=utf8mb4 22 1 row in set (0.00 sec)
Generate a tree using a file which contains a query statement.
1 [root@zlm1 18:34:29 ~] 2 #echo "select count(*) from customer join goods using(order_id);" > query1.sql 3 4 [root@zlm1 18:51:58 ~] 5 #pt-visual-explain -h192.168.56.100 -P3306 -urepl -prepl4slave -Dzlm --connect query1.sql 6 JOIN 7 +- Join buffer 8 | +- Filter with WHERE 9 | +- Table scan -- It means "customer" is a drived table,do full table scan. 10 | rows 19844 11 | +- Table 12 | table customer 13 +- Table scan -- It means "goods" is a drive table,do full table scan,too. 14 rows 5000 15 +- Table 16 table goods 17 18 [root@zlm1 18:52:04 ~] 19 #
Compare the original explain result with the output above.
1 root@localhost:mysql3306.sock [zlm]>explain select count(*) from customer join goods using(order_id); 2 +----+-------------+----------+------------+------+---------------+------+---------+------+-------+----------+----------------------------------------------------+ 3 | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | 4 +----+-------------+----------+------------+------+---------------+------+---------+------+-------+----------+----------------------------------------------------+ 5 | 1 | SIMPLE | goods | NULL | ALL | NULL | NULL | NULL | NULL | 5000 | 100.00 | NULL | 6 | 1 | SIMPLE | customer | NULL | ALL | NULL | NULL | NULL | NULL | 19844 | 10.00 | Using where; Using join buffer (Block Nested Loop) | 7 +----+-------------+----------+------------+------+---------------+------+---------+------+-------+----------+----------------------------------------------------+ 8 2 rows in set, 1 warning (0.00 sec) 9 10 ###The output of explain is compatiable with the output of tree above.###
Generate a tree using a file which contains a explain output.
1 [root@zlm1 19:13:36 ~] 2 #mysql -e "use zlm;explain select count(*) from customer join goods where goods.goodsname=`cellphone`;" > explain1.log 3 4 [root@zlm1 19:13:42 ~] 5 #pt-visual-explain -h192.168.56.100 -P3306 -urepl -prepl4slave explain1.log 6 JOIN 7 +- Join buffer 8 | +- Index scan -- It means "customer" is a drive table,do index scan with primary. 9 | key customer->PRIMARY 10 | key_len 4 11 | rows 19844 12 +- Filter with WHERE 13 +- Table scan -- It means "goods" is a drive table,do full table scan,too. 14 rows 5000 15 +- Table 16 table goods 17 18 [root@zlm1 19:13:46 ~] 19 #
Compare the original explain result with the output above.
1 root@localhost:mysql3306.sock [zlm]>explain select count(*) from customer join goods where goods.goodsname=`cellphone`; 2 +----+-------------+----------+------------+-------+---------------+---------+---------+------+-------+----------+----------------------------------------------------+ 3 | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | 4 +----+-------------+----------+------------+-------+---------------+---------+---------+------+-------+----------+----------------------------------------------------+ 5 | 1 | SIMPLE | goods | NULL | ALL | NULL | NULL | NULL | NULL | 5000 | 10.00 | Using where | 6 | 1 | SIMPLE | customer | NULL | index | NULL | PRIMARY | 4 | NULL | 19844 | 100.00 | Using index; Using join buffer (Block Nested Loop) | 7 +----+-------------+----------+------------+-------+---------------+---------+---------+------+-------+----------+----------------------------------------------------+ 8 2 rows in set, 1 warning (0.00 sec)
Generate a tree using standard input of MySQL command line with “-e” parameter.
1 [root@zlm1 19:23:49 ~] 2 #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 3 JOIN 4 +- Join buffer 5 | +- Filter with WHERE 6 | +- Table scan 7 | rows 5000 8 | +- Table 9 | table g -- Show table with alias "g" and it`s a dirved table,do full table scan. 10 +- Filter with WHERE 11 +- Bookmark lookup -- If you`re using only innodb table,this kind of lookup will lead to bad performance. 12 +- Table 13 | table c -- Show table with alias "c" and it`s a drive table,do index range scan. 14 | possible_keys PRIMARY 15 +- Index range scan 16 key c->PRIMARY 17 possible_keys PRIMARY 18 key_len 4 19 rows 5 20 21 [root@zlm1 19:24:10 ~] 22 #select c.name,c.gender,g.goodsname from goods g,customer c where c.order_id=g.order_id and c.id<=5; 23 +------+--------+-----------+ 24 | name | gender | goodsname | 25 +------+--------+-----------+ 26 | zlm | male | tv | 27 | zlm | male | tv | 28 | zlm | male | tv | 29 | zlm | male | tv | 30 | zlm | male | tv | 31 | zlm | male | cd | 32 | zlm | male | cd | 33 | zlm | male | cd | 34 | zlm | male | cd | 35 | zlm | male | cd | 36 | zlm | male | dvd | 37 | zlm | male | dvd | 38 | zlm | male | dvd | 39 | zlm | male | dvd | 40 | zlm | male | dvd | 41 | zlm | male | cellphone | 42 | zlm | male | cellphone | 43 | zlm | male | cellphone | 44 | zlm | male | cellphone | 45 | zlm | male | cellphone | 46 | zlm | male | computer | 47 | zlm | male | computer | 48 | zlm | male | computer | 49 | zlm | male | computer | 50 | zlm | male | computer | 51 +------+--------+-----------+ 52 25 rows in set (0.00 sec)
Compare the original explain result with the output above.
1 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; 2 +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------------------------+ 3 | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | 4 +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------------------------+ 5 | 1 | SIMPLE | c | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 5 | 100.00 | Using where | 6 | 1 | SIMPLE | g | NULL | ALL | NULL | NULL | NULL | NULL | 5000 | 10.00 | Using where; Using join buffer (Block Nested Loop) | 7 +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------------------------+ 8 2 rows in set, 1 warning (0.00 sec)
As the test tables are both innodb tables,use “–clustered-pk” option is recommended.
1 [root@zlm1 19:26:04 ~] 2 #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 3 JOIN 4 +- Join buffer 5 | +- Filter with WHERE 6 | +- Table scan 7 | rows 5000 8 | +- Table 9 | table g 10 +- Filter with WHERE 11 +- Index range scan -- This time the "bookmark lookup" is missing.It will lookup by pk directly what is more efficient way. 12 key c->PRIMARY 13 possible_keys PRIMARY 14 key_len 4 15 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.