是否有方法像Oracle树一样显示MYsql解释计划？

Question

示例查询：

create table t1(c1 int primary key);

MySQL

mysql> explain select c1 from t1;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | index | NULL          | PRIMARY | 4       | NULL |    1 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

甲骨文：

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2822583898

------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |     1 |    13 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T_DILANG |     1 |    13 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------

Note
-----

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   - dynamic sampling used for this statement (level=2)

12 rows selected.

可以从tree获得很多信息，比如解释计划。如果MySQL也能证明这一点，那就太好了。

更新：

使用，pt-视觉解释，它运行良好。

命令行输出：

mysql> explain select c1 from t1, A, B where c1 > 0 group by c1 having count(1) > 2 limit 1;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                                                     |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------------------------------------------+
|  1 | SIMPLE      | t1    | NULL       | index | PRIMARY       | PRIMARY | 4       | NULL |    1 |   100.00 | Using where; Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | A     | NULL       | index | NULL          | idx7    | 4       | NULL |    1 |   100.00 | Using index; Using join buffer (Block Nested Loop)        |
|  1 | SIMPLE      | B     | NULL       | index | NULL          | idx7    | 4       | NULL |    1 |   100.00 | Using index; Using join buffer (Block Nested Loop)        |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------------------------------------------+
3 rows in set, 1 warning (0.00 sec)

来自pt-visual-explain的输出

$mysql -e "explain select c1 from t1, A, B where c1 > 0 group by c1 having count(1) > 2 limit 1" | pt-visual-explain
Filesort
+- TEMPORARY
   table          temporary(t1,A,B)
   +- JOIN
      +- Join buffer
      |  +- Index scan
      |     key            B->idx7
      |     key_len        4
      |     rows           1
      +- JOIN
         +- Join buffer
         |  +- Index scan
         |     key            A->idx7
         |     key_len        4
         |     rows           1
         +- Filter with WHERE
            +- Index scan
               key            t1->PRIMARY
               possible_keys  PRIMARY
               key_len        4
               rows           1

Answer 1

新的MySQL工作台附带了，它显示了解释查询的流程图。除了命令行或mysql之外，没有太多内容。

https://dev.mysql.com/doc/workbench/en/wb-performance-explain.html

如果您仍然喜欢命令行，则可以使用Percona的工具，但是它需要将解释计划作为给定的输入。

mysql -e“从t1中解释选择c1”

https://www.percona.com/doc/percona-toolkit/2.2/pt-visual-explain.html