1MK-VISUAL-EXPLAIN(1) User Contributed Perl Documentation MK-VISUAL-EXPLAIN(1)
2
6 mk-visual-explain - Format EXPLAIN output as a tree.
7
9 Usage: mk-visual-explain [OPTION...] [FILE...]
10 mk-visual-explain transforms EXPLAIN output into a tree representation
12 of the query plan. If FILE is given, input is read from the file(s).
13 With no FILE, or when FILE is -, read standard input.
14 Examples:
16 mk-visual-explain <file_containing_explain_output>
18 mk-visual-explain -c <file_containing_query>
20 mysql -e "explain select * from mysql.user" | mk-visual-explain
22
24 The following section is included to inform users about the potential
25 risks, whether known or unknown, of using this tool. The two main
26 categories of risks are those created by the nature of the tool (e.g.
27 read-only tools vs. read-write tools) and those created by bugs.
28 mk-visual-explain is read-only and very low-risk.
30 At the time of this release, we know of no bugs that could cause
32 serious harm to users.
33 The authoritative source for updated information is always the online
35 issue tracking system. Issues that affect this tool will be marked as
36 such. You can see a list of such issues at the following URL:
37 <http://www.maatkit.org/bugs/mk-visual-explain>.
38 See also "BUGS" for more information on filing bugs and getting help.
40
42 mk-visual-explain reverse-engineers MySQL's EXPLAIN output into a query
43 execution plan, which it then formats as a left-deep tree -- the same
44 way the plan is represented inside MySQL. It is possible to do this by
45 hand, or to read EXPLAIN's output directly, but it requires patience
46 and expertise. Many people find a tree representation more
47 understandable.
48 You can pipe input into mk-visual-explain or specify a filename at the
50 command line, including the magical '-' filename, which will read from
51 standard input. It can do two things with the input: parse it for
52 something that looks like EXPLAIN output, or connect to a MySQL
53 instance and run EXPLAIN on the input.
54 When parsing its input, mk-visual-explain understands three formats:
56 tabular like that shown in the mysql command-line client, vertical like
57 that created by using the \G line terminator in the mysql command-line
58 client, and tab separated. It ignores any lines it doesn't know how to
59 parse.
60 When executing the input, mk-visual-explain replaces everything in the
62 input up to the first SELECT keyword with 'EXPLAIN SELECT,' and then
63 executes the result. You must specify "--connect" to execute the input
64 as a query.
65 Either way, it builds a tree from the result set and prints it to
67 standard output. For the following query,
68 select * from sakila.film_actor join sakila.film using(film_id);
70 mk-visual-explain generates this query plan:
72 JOIN
74 +- Bookmark lookup
75 | +- Table
76 | | table film_actor
77 | | possible_keys idx_fk_film_id
78 | +- Index lookup
79 | key film_actor->idx_fk_film_id
80 | possible_keys idx_fk_film_id
81 | key_len 2
82 | ref sakila.film.film_id
83 | rows 2
84 +- Table scan
85 rows 952
86 +- Table
87 table film
88 possible_keys PRIMARY
89 The query plan is left-deep, depth-first search, and the tree's root is
91 the output node -- the last step in the execution plan. In other
92 words, read it like this:
93 1. Table scan the 'film' table, which accesses an estimated 952 rows.
95 2. For each row, find matching rows by doing an index lookup into the
97 film_actor->idx_fk_film_id index with the value from
98 sakila.film.film_id, then a bookmark lookup into the film_actor
99 table.
100 For more information on how to read EXPLAIN output, please see
102 <http://dev.mysql.com/doc/en/explain.html>, and this talk titled "Query
103 Optimizer Internals and What's New in the MySQL 5.2 Optimizer," from
104 Timour Katchaounov, one of the MySQL developers:
105 <http://maatkit.org/presentations/katchaounov_timour.pdf>.
106
108 This program is actually a runnable module, not just an ordinary Perl
109 script. In fact, there are two modules embedded in it. This makes
110 unit testing easy, but it also makes it easy for you to use the parsing
111 and tree-building functionality if you want.
112 The ExplainParser package accepts a string and parses whatever it
114 thinks looks like EXPLAIN output from it. The synopsis is as follows:
115 require "mk-visual-explain";
117 my $p = ExplainParser->new();
118 my $rows = $p->parse("some text");
119 # $rows is an arrayref of hashrefs.
120 The ExplainTree package accepts a set of rows and turns it into a tree.
122 For convenience, you can also have it delegate to ExplainParser and
123 parse text for you. Here's the synopsis:
124 require "mk-visual-explain";
126 my $e = ExplainTree->new();
127 my $tree = $e->parse("some text", \%options);
128 my $output = $e->pretty_print($tree);
129 print $tree;
130
132 This section explains the algorithm that converts EXPLAIN into a tree.
133 You may be interested in reading this if you want to understand EXPLAIN
134 more fully, or trying to figure out how this works, but otherwise this
135 section will probably not make your life richer.
136 The tree can be built by examining the id, select_type, and table
138 columns of each row. Here's what I know about them:
139 The id column is the sequential number of the select. This does not
141 indicate nesting; it just comes from counting SELECT from the left of
142 the SQL statement. It's like capturing parentheses in a regular
143 expression. A UNION RESULT row doesn't have an id, because it isn't a
144 SELECT. The source code actually refers to UNIONs as a fake_lex, as I
145 recall.
146 If two adjacent rows have the same id value, they are joined with the
148 standard single-sweep multi-join method.
149 The select_type column tells a) that a new sub-scope has opened b) what
151 kind of relationship the row has to the previous row c) what kind of
152 operation the row represents.
153 • SIMPLE means there are no subqueries or unions in the whole query.
155 • PRIMARY means there are, but this is the outermost SELECT.
157 • [DEPENDENT] UNION means this result is UNIONed with the previous
159 result (not row; a result might encompass more than one row).
160 • UNION RESULT terminates a set of UNIONed results.
162 • [DEPENDENT|UNCACHEABLE] SUBQUERY means a new sub-scope is opening.
164 This is the kind of subquery that happens in a WHERE clause, SELECT
165 list or whatnot; it does not return a so-called "derived table."
166 • DERIVED is a subquery in the FROM clause.
168 Tables that are JOINed all have the same select_type. For example, if
170 you JOIN three tables inside a dependent subquery, they'll all say the
171 same thing: DEPENDENT SUBQUERY.
172 The table column usually specifies the table name or alias, but may
174 also say <derivedN> or <unionN,N...N>. If it says <derivedN>, the row
175 represents an access to the temporary table that holds the result of
176 the subquery whose id is N. If it says <unionN,..N> it's the same
177 thing, but it refers to the results it UNIONs together.
178 Finally, order matters. If a row's id is less than the one before it,
180 I think that means it is dependent on something other than the one
181 before it. For example,
182 explain select
184 (select 1 from sakila.film),
185 (select 2 from sakila.film_actor),
186 (select 3 from sakila.actor);
187 | id | select_type | table |
189 +----+-------------+------------+
190 | 1 | PRIMARY | NULL |
191 | 4 | SUBQUERY | actor |
192 | 3 | SUBQUERY | film_actor |
193 | 2 | SUBQUERY | film |
194 If the results were in order 2-3-4, I think that would mean 3 is a
196 subquery of 2, 4 is a subquery of 3. As it is, this means 4 is a
197 subquery of the nearest previous recent row with a smaller id, which is
198 1. Likewise for 3 and 2.
199 This structure is hard to programatically build into a tree for the
201 same reason it's hard to understand by inspection: there are both
202 forward and backward references. <derivedN> is a forward reference to
203 selectN, while <unionM,N> is a backward reference to selectM and
204 selectN. That makes recursion and other tree-building algorithms hard
205 to get right (NOTE: after implementation, I now see how it would be
206 possible to deal with both forward and backward references, but I have
207 no motivation to change something that works). Consider the following:
208 select * from (
210 select 1 from sakila.actor as actor_1
211 union
212 select 1 from sakila.actor as actor_2
213 ) as der_1
214 union
215 select * from (
216 select 1 from sakila.actor as actor_3
217 union all
218 select 1 from sakila.actor as actor_4
219 ) as der_2;
220 | id | select_type | table |
222 +------+--------------+------------+
223 | 1 | PRIMARY | <derived2> |
224 | 2 | DERIVED | actor_1 |
225 | 3 | UNION | actor_2 |
226 | NULL | UNION RESULT | <union2,3> |
227 | 4 | UNION | <derived5> |
228 | 5 | DERIVED | actor_3 |
229 | 6 | UNION | actor_4 |
230 | NULL | UNION RESULT | <union5,6> |
231 | NULL | UNION RESULT | <union1,4> |
232 This would be a lot easier to work with if it looked like this (I've
234 bracketed the id on rows I moved):
235 | id | select_type | table |
237 +------+--------------+------------+
238 | [1] | UNION RESULT | <union1,4> |
239 | 1 | PRIMARY | <derived2> |
240 | [2] | UNION RESULT | <union2,3> |
241 | 2 | DERIVED | actor_1 |
242 | 3 | UNION | actor_2 |
243 | 4 | UNION | <derived5> |
244 | [5] | UNION RESULT | <union5,6> |
245 | 5 | DERIVED | actor_3 |
246 | 6 | UNION | actor_4 |
247 In fact, why not re-number all the ids, so the PRIMARY row becomes 2,
249 and so on? That would make it even easier to read. Unfortunately that
250 would also have the effect of destroying the meaning of the id column,
251 which I think is important to preserve in the final tree. Also, though
252 it makes it easier to read, it doesn't make it easier to manipulate
253 programmatically; so it's fine to leave them numbered as they are.
254 The goal of re-ordering is to make it easier to figure out which rows
256 are children of which rows in the execution plan. Given the reordered
257 list and some row whose table is <union...> or <derived>, it is easy to
258 find the beginning of the slice of rows that should be child nodes in
259 the tree: you just look for the first row whose ID is the same as the
260 first number in the table.
261 The next question is how to find the last row that should be a child
263 node of a UNION or DERIVED. I'll start with DERIVED, because the
264 solution makes UNION easy.
265 Consider how MySQL numbers the SELECTs sequentially according to their
267 position in the SQL, left-to-right. Since a DERIVED table encloses
268 everything within it in a scope, which becomes a temporary table, there
269 are only two things to think about: its child subqueries and unions (if
270 any), and its next siblings in the scope that encloses it. Its
271 children will all have an id greater than it does, by definition, so
272 any later rows with a smaller id terminate the scope.
273 Here's an example. The middle derived table here has a subquery and a
275 UNION to make it a little more complex for the example.
276 explain select 1
278 from (
279 select film_id from sakila.film limit 1
280 ) as der_1
281 join (
282 select film_id, actor_id, (select count(*) from sakila.rental) as r
283 from sakila.film_actor limit 1
284 union all
285 select 1, 1, 1 from sakila.film_actor as dummy
286 ) as der_2 using (film_id)
287 join (
288 select actor_id from sakila.actor limit 1
289 ) as der_3 using (actor_id);
290 Here's the output of EXPLAIN:
292 | id | select_type | table |
294 | 1 | PRIMARY | <derived2> |
295 | 1 | PRIMARY | <derived6> |
296 | 1 | PRIMARY | <derived3> |
297 | 6 | DERIVED | actor |
298 | 3 | DERIVED | film_actor |
299 | 4 | SUBQUERY | rental |
300 | 5 | UNION | dummy |
301 | NULL | UNION RESULT | <union3,5> |
302 | 2 | DERIVED | film |
303 The siblings all have id 1, and the middle one I care about is
305 derived3. (Notice MySQL doesn't execute them in the order I defined
306 them, which is fine). Now notice that MySQL prints out the rows in the
307 opposite order I defined the subqueries: 6, 3, 2. It always seems to
308 do this, and there might be other methods of finding the scope
309 boundaries including looking for the lower boundary of the next largest
310 sibling, but this is a good enough heuristic. I am forced to rely on
311 it for non-DERIVED subqueries, so I rely on it here too. Therefore, I
312 decide that everything greater than or equal to 3 belongs to the
313 DERIVED scope.
314 The rule for UNION is simple: they consume the entire enclosing scope,
316 and to find the component parts of each one, you find each part's
317 beginning as referred to in the <unionN,...> definition, and its end is
318 either just before the next one, or if it's the last part, the end is
319 the end of the scope.
320 This is only simple because UNION consumes the entire scope, which is
322 either the entire statement, or the scope of a DERIVED table. This is
323 because a UNION cannot be a sibling of another UNION or a table,
324 DERIVED or not. (Try writing such a statement if you don't see it
325 intuitively). Therefore, you can just find the enclosing scope's
326 boundaries, and the rest is easy. Notice in the example above, the
327 UNION is over <union3,5>, which includes the row with id 4 -- it
328 includes every row between 3 and 5.
329 Finally, there are non-derived subqueries to deal with as well. In
331 this case I can't look at siblings to find the end of the scope as I
332 did for DERIVED. I have to trust that MySQL executes depth-first.
333 Here's an example:
334 explain
336 select actor_id,
337 (
338 select count(film_id)
339 + (select count(*) from sakila.film)
340 from sakila.film join sakila.film_actor using(film_id)
341 where exists(
342 select * from sakila.actor
343 where sakila.actor.actor_id = sakila.film_actor.actor_id
344 )
345 )
346 from sakila.actor;
347 | id | select_type | table |
349 | 1 | PRIMARY | actor |
350 | 2 | SUBQUERY | film |
351 | 2 | SUBQUERY | film_actor |
352 | 4 | DEPENDENT SUBQUERY | actor |
353 | 3 | SUBQUERY | film |
354 In order, the tree should be built like this:
356 • See row 1.
358 • See row 2. It's a higher id than 1, so it's a subquery, along with
360 every other row whose id is greater than 2.
361 • Inside this scope, see 2 and 2 and JOIN them. See 4. It's a
363 higher id than 2, so it's again a subquery; recurse. After that,
364 see 3, which is also higher; recurse.
365 But the only reason the nested subquery didn't include select 3 is
367 because select 4 came first. In other words, if EXPLAIN looked like
368 this,
369 | id | select_type | table |
371 | 1 | PRIMARY | actor |
372 | 2 | SUBQUERY | film |
373 | 2 | SUBQUERY | film_actor |
374 | 3 | SUBQUERY | film |
375 | 4 | DEPENDENT SUBQUERY | actor |
376 I would be forced to assume upon seeing select 3 that select 4 is a
378 subquery of it, rather than just being the next sibling in the
379 enclosing scope. If this is ever wrong, then the algorithm is wrong,
380 and I don't see what could be done about it.
381 UNION is a little more complicated than just "the entire scope is a
383 UNION," because the UNION might itself be inside an enclosing scope
384 that's only indicated by the first item inside the UNION. There are
385 only three kinds of enclosing scopes: UNION, DERIVED, and SUBQUERY. A
386 UNION can't enclose a UNION, and a DERIVED has its own "scope markers,"
387 but a SUBQUERY can wholly enclose a UNION, like this strange example on
388 the empty table t1:
389 explain select * from t1 where not exists(
391 (select t11.i from t1 t11) union (select t12.i from t1 t12));
392 | id | select_type | table | Extra |
394 +------+--------------+------------+--------------------------------+
395 | 1 | PRIMARY | t1 | const row not found |
396 | 2 | SUBQUERY | NULL | No tables used |
397 | 3 | SUBQUERY | NULL | no matching row in const table |
398 | 4 | UNION | t12 | const row not found |
399 | NULL | UNION RESULT | <union2,4> | |
400 The UNION's backward references might make it look like the UNION
402 encloses the subquery, but studying the query makes it clear this isn't
403 the case. So when a UNION's first row says SUBQUERY, it is this
404 special case.
405 By the way, I don't fully understand this query plan; there are 4
407 numbered SELECT in the plan, but only 3 in the query. The parens
408 around the UNIONs are meaningful. Removing them will make the EXPLAIN
409 different. Please tell me how and why this works if you know.
410 Armed with this knowledge, it's possible to use recursion to turn the
412 parent-child relationship between all the rows into a tree representing
413 the execution plan.
414 MySQL prints the rows in execution order, even the forward and backward
416 references. At any given scope, the rows are processed as a left-deep
417 tree. MySQL does not do "bushy" execution plans. It begins with a
418 table, finds a matching row in the next table, and continues till the
419 last table, when it emits a row. When it runs out, it backtracks till
420 it can find the next row and repeats. There are subtleties of course,
421 but this is the basic plan. This is why MySQL transforms all RIGHT
422 OUTER JOINs into LEFT OUTER JOINs and cannot do FULL OUTER JOIN.
423 This means in any given scope, say
425 | id | select_type | table |
427 | 1 | SIMPLE | tbl1 |
428 | 1 | SIMPLE | tbl2 |
429 | 1 | SIMPLE | tbl3 |
430 The execution plan looks like a depth-first traversal of this tree:
432 JOIN
434 / \
435 JOIN tbl3
436 / \
437 tbl1 tbl2
438 The JOIN might not be a JOIN. It might be a subquery, for example.
440 This comes from the type column of EXPLAIN. The documentation says
441 this is a "join type," but I think "access type" is more accurate,
442 because it's "how MySQL accesses rows."
443 mk-visual-explain decorates the tree significantly more than just
445 turning rows into nodes. Each node may get a series of transformations
446 that turn it into a subtree of more than one node. For example, an
447 index scan not marked with 'Using index' must do a bookmark lookup into
448 the table rows; that is a three-node subtree. However, after the above
449 node-ordering and scoping stuff, the rest of the process is pretty
450 simple.
451
453 This tool accepts additional command-line arguments. Refer to the
454 "SYNOPSIS" and usage information for details.
455 --ask-pass
457 Prompt for a password when connecting to MySQL.
458 --charset
460 short form: -A; type: string
461 Default character set. If the value is utf8, sets Perl's binmode
463 on STDOUT to utf8, passes the mysql_enable_utf8 option to
464 DBD::mysql, and runs SET NAMES UTF8 after connecting to MySQL. Any
465 other value sets binmode on STDOUT without the utf8 layer, and runs
466 SET NAMES after connecting to MySQL.
467 --clustered-pk
469 Assume that PRIMARY KEY index accesses don't need to do a bookmark
470 lookup to retrieve rows. This is the case for InnoDB.
471 --config
473 type: Array
474 Read this comma-separated list of config files; if specified, this
476 must be the first option on the command line.
477 --connect
479 Treat input as a query, and obtain EXPLAIN output by connecting to
480 a MySQL instance and running EXPLAIN on the query. When this
481 option is given, mk-visual-explain uses the other connection-
482 specific options such as "--user" to connect to the MySQL instance.
483 If you have a .my.cnf file, it will read it, so you may not need to
484 specify any connection-specific options.
485 --database
487 short form: -D; type: string
488 Connect to this database.
490 --defaults-file
492 short form: -F; type: string
493 Only read mysql options from the given file. You must give an
495 absolute pathname.
496 --format
498 type: string; default: tree
499 Set output format.
501 The default is a terse pretty-printed tree. The valid values are:
503 value meaning
505 ===== =======
506 tree Pretty-printed terse tree.
507 dump Data::Dumper output (see L<Data::Dumper> for more).
508 --help
510 Show help and exit.
511 --host
513 short form: -h; type: string
514 Connect to host.
516 --password
518 short form: -p; type: string
519 Password to use when connecting.
521 --pid
523 type: string
524 Create the given PID file. The file contains the process ID of the
526 script. The PID file is removed when the script exits. Before
527 starting, the script checks if the PID file already exists. If it
528 does not, then the script creates and writes its own PID to it. If
529 it does, then the script checks the following: if the file contains
530 a PID and a process is running with that PID, then the script dies;
531 or, if there is no process running with that PID, then the script
532 overwrites the file with its own PID and starts; else, if the file
533 contains no PID, then the script dies.
534 --port
536 short form: -P; type: int
537 Port number to use for connection.
539 --set-vars
541 type: string; default: wait_timeout=10000
542 Set these MySQL variables. Immediately after connecting to MySQL,
544 this string will be appended to SET and executed.
545 --socket
547 short form: -S; type: string
548 Socket file to use for connection.
550 --user
552 short form: -u; type: string
553 User for login if not current user.
555 --version
557 Show version and exit.
558
560 These DSN options are used to create a DSN. Each option is given like
561 "option=value". The options are case-sensitive, so P and p are not the
562 same option. There cannot be whitespace before or after the "=" and if
563 the value contains whitespace it must be quoted. DSN options are
564 comma-separated. See the maatkit manpage for full details.
565 • A
567 dsn: charset; copy: yes
569 Default character set.
571 • D
573 dsn: database; copy: yes
575 Default database.
577 • F
579 dsn: mysql_read_default_file; copy: yes
581 Only read default options from the given file
583 • h
585 dsn: host; copy: yes
587 Connect to host.
589 • p
591 dsn: password; copy: yes
593 Password to use when connecting.
595 • P
597 dsn: port; copy: yes
599 Port number to use for connection.
601 • S
603 dsn: mysql_socket; copy: yes
605 Socket file to use for connection.
607 • u
609 dsn: user; copy: yes
611 User for login if not current user.
613
615 You can download Maatkit from Google Code at
616 <http://code.google.com/p/maatkit/>, or you can get any of the tools
617 easily with a command like the following:
618 wget http://www.maatkit.org/get/toolname
620 or
621 wget http://www.maatkit.org/trunk/toolname
622 Where "toolname" can be replaced with the name (or fragment of a name)
624 of any of the Maatkit tools. Once downloaded, they're ready to run; no
625 installation is needed. The first URL gets the latest released version
626 of the tool, and the second gets the latest trunk code from Subversion.
627
629 The environment variable "MKDEBUG" enables verbose debugging output in
630 all of the Maatkit tools:
631 MKDEBUG=1 mk-....
633
635 You need Perl, DBI, DBD::mysql, and some core packages that ought to be
636 installed in any reasonably new version of Perl.
637
639 For a list of known bugs see
640 <http://www.maatkit.org/bugs/mk-visual-explain>.
641 Please use Google Code Issues and Groups to report bugs or request
643 support: <http://code.google.com/p/maatkit/>. You can also join
644 #maatkit on Freenode to discuss Maatkit.
645 Please include the complete command-line used to reproduce the problem
647 you are seeing, the version of all MySQL servers involved, the complete
648 output of the tool when run with "--version", and if possible,
649 debugging output produced by running with the "MKDEBUG=1" environment
650 variable.
651
653 This program is copyright 2007-2011 Baron Schwartz. Feedback and
654 improvements are welcome.
655 THIS PROGRAM IS PROVIDED "AS IS" AND WITHOUT ANY EXPRESS OR IMPLIED
657 WARRANTIES, INCLUDING, WITHOUT LIMITATION, THE IMPLIED WARRANTIES OF
658 MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.
659 This program is free software; you can redistribute it and/or modify it
661 under the terms of the GNU General Public License as published by the
662 Free Software Foundation, version 2; OR the Perl Artistic License. On
663 UNIX and similar systems, you can issue `man perlgpl' or `man
664 perlartistic' to read these licenses.
665 You should have received a copy of the GNU General Public License along
667 with this program; if not, write to the Free Software Foundation, Inc.,
668 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA.
669
671 See also mk-query-profiler.
672
674 Baron "Xaprb" Schwartz
675
677 This tool is part of Maatkit, a toolkit for power users of MySQL.
678 Maatkit was created by Baron Schwartz; Baron and Daniel Nichter are the
679 primary code contributors. Both are employed by Percona. Financial
680 support for Maatkit development is primarily provided by Percona and
681 its clients.
682
684 This manual page documents Ver 1.0.22 Distrib 7540 $Revision: 7477 $.
685perl v5.36.0 2022-07-21 MK-VISUAL-EXPLAIN(1)