使用EXPLAIN查看DN执行计划

EXPLAIN TREE_EXECUTE

mysql> EXPLAIN TREE_EXECUTE SELECT col1 FROM exe_tb WHERE col1 < 6;
+------------------------+------------------------------------------------------------+
| TABLE                  | PHYSICAL_PLAN                                              |
+------------------------+------------------------------------------------------------+
| [[exe_tb_58Db_00001]]
 | -> Filter: (exe_tb.col1 < 6)  (cost=202.75 rows=2000)
    -> Index scan on exe_tb using auto_shard_key_col1  (cost=202.75 rows=2000)
 |
+------------------------+------------------------------------------------------------+

PHYSICAL_PLAN列以层级缩进的形式展示了执行计划树，执行流程从内层（缩进最深）向外层进行。在本例中，执行顺序为：

1. -> Index scan on exe_tb using col1: 通过col1索引扫描数据。

2. -> Filter: (exe_tb.col1 < 6): 将索引扫描的结果进行过滤。

每一层都包含了优化器估算的成本（cost）和行数（rows），便于直观理解数据处理流程和各环节的开销。

EXPLAIN JSON_EXECUTE

mysql> EXPLAIN JSON_EXECUTE SELECT col1 FROM exe_tb WHERE col1 < 6;
+------------------------+------------------------------------------+
| TABLE                  | PHYSICAL_PLAN                            |
+------------------------+------------------------------------------+
| [[exe_tb_58Db_00001]]
 | {
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "202.75"
    },
    "table": {
      "table_name": "exe_tb",
      "access_type": "index",
      "possible_keys": [
        "auto_shard_key_col1"
      ],
      "key": "auto_shard_key_col1",
      "used_key_parts": [
        "col1"
      ],
      "key_length": "5",
      "rows_examined_per_scan": 2000,
      "rows_produced_per_join": 2000,
      "filtered": "100.00",
      "using_index": true,
      "cost_info": {
        "read_cost": "2.75",
        "eval_cost": "200.00",
        "prefix_cost": "202.75",
        "data_read_per_join": "31K"
      },
      "used_columns": [
        "col1"
      ],
      "attached_condition": "(`db0_p00000`.`exe_tb`.`col1` < 6)"
    }
  }
} |
+------------------------+------------------------------------------+

PHYSICAL_PLAN列提供了一个详细的JSON对象，这对于深入分析优化器行为非常有用。关键字段包括：

• cost_info: 包含详细的查询成本（query_cost）、读成本（read_cost）、评估成本（eval_cost）等。

• access_type: 表的访问方式，此处为index。

• key: 实际选择的索引。

• rows_examined_per_scan: 预估扫描的行数。

该格式提供了最全面的优化器估算信息，适合进行底层分析。

EXPLAIN ANALYZE_EXECUTE

mysql> EXPLAIN ANALYZE_EXECUTE SELECT col1 FROM exe_tb WHERE col1 < 6;
+------------------------+-----------------------------------------------------------------------------------------------------------+
| TABLE                  | PHYSICAL_PLAN                                                                                             |
+------------------------+-----------------------------------------------------------------------------------------------------------+
| [[exe_tb_58Db_00001]]
 | -> Filter: (exe_tb.col1 < 6)  (cost=202.75 rows=2000) (actual time=0.028..0.655 rows=2000 loops=1)
    -> Covering index scan on exe_tb using auto_shard_key_col1  (cost=202.75 rows=2000) (actual time=0.026..0.468 rows=2000 loops=1)
 |
+------------------------+-----------------------------------------------------------------------------------------------------------+

PHYSICAL_PLAN列展示了DN上的执行计划树。其中actual time显示了该算子实际执行的耗时（启动耗时..总耗时），rows显示了实际返回的行数。这些信息对于分析具体算子的性能瓶颈至关重要。