Skip to content

Latest commit

 

History

History
62 lines (55 loc) · 5.12 KB

2.17_explain.md

File metadata and controls

62 lines (55 loc) · 5.12 KB
Raw

2.17 执行计划

2.17.1 执行计划的意义

对执行计划进行分析，可以了解中间件和节点是否对SQL语句生成了最优的执行计划，是否有优化的空间，从而为SQL优化提供重要的参考信息。

2.17.2 执行计划的分类

dble的执行计划分为两个层次：dble层的执行计划与节点层的执行计划。
dble层的执行计划: 在SQL语句执行前，dble会根据SQL语句的基本信息，判断该SQL语句应该在哪些节点上执行，将SQL改写成在节点上执行的具体形式，并决定采用何种策略进行数据合并与计算等。
节点层的执行计划: 就是原生的MySQL执行计划。

2.17.3 dble层的执行计划

dble用EXPLAIN指令来查看dble层的执行计划。如： explain select id,accountno from account where userid=2;
EXPLAIN指令的执行结果包括语句下发的节点，实际下发的SQL语句和数据的合并操作的信息。这些信息是系统静态分析产生的，并没有真正的执行语句。

另外，复杂查询的查询计划也会有所反映，可以通过计划来优化查询语句
如

mysql> explain select * from sharding_two_node a inner join sharding_four_node b on a.id =b.id;

+---------------+----------+----------------------------------------------------------------------------------------------------+
| SHARDING_NODE | TYPE     | SQL/REF                                                                                            |
+---------------+----------+----------------------------------------------------------------------------------------------------+
| dn1.0         | BASE SQL | select `a`.`id`,`a`.`c_char`,`a`.`ts`,`a`.`si` from  `sharding_two_node` `a` ORDER BY `a`.`id` ASC |
| dn2.0         | BASE SQL | select `a`.`id`,`a`.`c_char`,`a`.`ts`,`a`.`si` from  `sharding_two_node` `a` ORDER BY `a`.`id` ASC |
| dn1.1         | BASE SQL | select `b`.`id`,`b`.`c_flag`,`b`.`c_decimal` from  `sharding_four_node` `b` ORDER BY `b`.`id` ASC  |
| dn2.1         | BASE SQL | select `b`.`id`,`b`.`c_flag`,`b`.`c_decimal` from  `sharding_four_node` `b` ORDER BY `b`.`id` ASC  |
| dn3.0         | BASE SQL | select `b`.`id`,`b`.`c_flag`,`b`.`c_decimal` from  `sharding_four_node` `b` ORDER BY `b`.`id` ASC  |
| dn4.0         | BASE SQL | select `b`.`id`,`b`.`c_flag`,`b`.`c_decimal` from  `sharding_four_node` `b` ORDER BY `b`.`id` ASC  |
| merge.1       | MERGE    | dn1.0, dn2.0                                                                                       |
| merge.2       | MERGE    | dn1.1, dn2.1, dn3.0, dn4.0                                                                         |
| join.1        | JOIN     | merge.1, merge.2                                                                                   |
+---------------+----------+----------------------------------------------------------------------------------------------------+
9 rows in set (0.00 sec)

再举例如:

mysql> explain select id from single union all select b.si from sharding_four_node a inner join  sharding_two_node b on a.id =b.id
+-----------------+-----------+------------------------------------------------------------------------------+
| SHARDING_NODE   | TYPE      | SQL/REF                                                                      |
+-----------------+-----------+------------------------------------------------------------------------------+
| dn1.0           | BASE SQL  | select `single`.`id` from  `single`                                          |
| dn1.1           | BASE SQL  | select `a`.`id` from  `sharding_four_node` `a` ORDER BY `a`.`id` ASC         |
| dn2.0           | BASE SQL  | select `a`.`id` from  `sharding_four_node` `a` ORDER BY `a`.`id` ASC         |
| dn3.0           | BASE SQL  | select `a`.`id` from  `sharding_four_node` `a` ORDER BY `a`.`id` ASC         |
| dn4.0           | BASE SQL  | select `a`.`id` from  `sharding_four_node` `a` ORDER BY `a`.`id` ASC         |
| dn1.2           | BASE SQL  | select `b`.`si`,`b`.`id` from  `sharding_two_node` `b` ORDER BY `b`.`id` ASC |
| dn2.1           | BASE SQL  | select `b`.`si`,`b`.`id` from  `sharding_two_node` `b` ORDER BY `b`.`id` ASC |
| merge.2         | MERGE     | dn1.1, dn2.0, dn3.0, dn4.0                                                   |
| merge.3         | MERGE     | dn1.2, dn2.1                                                                 |
| join.1          | JOIN      | merge.2, merge.3                                                             |
| merge.1         | MERGE     | dn1.0                                                                        |
| union_all.1     | UNION_ALL | join.1, merge.1                                                              |
+-----------------+-----------+------------------------------------------------------------------------------+
12 rows in set (0.01 sec)

2.17.4 节点层的执行计划

通过EXPLAIN2命令可查看指定节点上的执行计划。如：
explain2 shardingnode=dn1 sql=select id,accountno from account where userid=2;
explain2会将sql语句加上explain下发到指定的shardingnode执行，并把节点上explain的结果返回调用者。