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;

+-----------+----------+----------------------------------------------------------------------------------------------------+
| DATA_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
+-------------+-----------+------------------------------------------------------------------------------+
| DATA_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 datanode=dn1 sql=select id,accountno from account where userid=2;
explain2会将sql语句加上explain下发到指定的datanode执行,并把节点上explain的结果返回调用者。

results matching ""

    No results matching ""