tencent cloud

TDSQL Boundless

ドキュメントTDSQL BoundlessPerformance TuningSQL TuningUnderstand Execution Plan

Understand Execution Plan

ダウンロード
フォーカスモード
フォントサイズ
最終更新日: 2026-05-26 17:37:40
You can use EXPLAIN to view the execution plan of a query. TDSQL Boundless, like MySQL, supports three formats for displaying execution plans: TRADITIONAL, TREE, and JSON.

TRADITIONAL Format

The default display format outputs the execution plan as a table, where each row represents a table in the SELECT statement. The order of the rows indicates the sequence in which tables are read and JOIN operations are performed during query execution.
Take the following query as an example:
tdsql> explain select * from t1, t2 where t1.a = t2.a;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                      |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 | NULL                                       |
|  1 | SIMPLE      | t2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where; Using join buffer (hash join) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
The execution plan can be interpreted as follows: performing a full table scan on both table t1 and table t2, with an expected return of 3 rows from each table. The tables are then joined in the order of t1 JOIN t2, yielding 3 * 3 * 33.33 / 100 = 3 rows of join results.
For more detailed explanations of the table fields, refer to the MySQL documentation.

TREE Format

The execution plan is displayed as a tree, allowing the query execution logic to be more intuitively visualized. The corresponding TREE format for the example above is:
tdsql> explain format=tree select * from t1, t2 where t1.a = t2.a;
+---------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                       |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Inner hash join (t2.a = t1.a)  (cost=6.28 rows=3)
    -> Table scan on t2  (cost=0.88 rows=3)
    -> Hash
        -> Table scan on t1  (cost=2.84 rows=3)
|
+---------------------------------------------------------------------------------------------------------------------------------------------------------------+
Compared to the information provided by the TRADITIONAL format, it also shows that the algorithm chosen for t1 JOIN t2 is Hash Join, with t1 as the build side and t2 as the probe side, and also displays the cost information of the execution plan, as well as the join condition expression t2.a = t1.a used in the JOIN.

JSON Format

The execution plan is output in JSON format. The corresponding JSON output for the example above is:
tdsql> explain format=json select * from t1, t2 where t1.a = t2.a;
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "6.27"
    },
    "nested_loop": [
      {
        "table": {
          "table_name": "t1",
          "access_type": "ALL",
          "rows_examined_per_scan": 3,
          "rows_produced_per_join": 3,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "2.54",
            "eval_cost": "0.30",
            "prefix_cost": "2.84",
            "data_read_per_join": "48"
          },
          "used_columns": [
            "a",
            "b"
          ]
        }
      },
      {
        "table": {
          "table_name": "t2",
          "access_type": "ALL",
          "rows_examined_per_scan": 3,
          "rows_produced_per_join": 3,
          "filtered": "33.33",
          "using_join_buffer": "hash join",
          "cost_info": {
            "read_cost": "2.54",
            "eval_cost": "0.30",
            "prefix_cost": "6.28",
            "data_read_per_join": "48"
          },
          "used_columns": [
            "a",
            "b"
          ],
          "attached_condition": "(`test`.`t2`.`a` = `test`.`t1`.`a`)"
        }
      }
    ]
  }
} |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
It can be considered a hybrid of the TRADITIONAL and TREE formats, additionally providing richer information, including more detailed cost breakdowns and which specific columns are returned by each table.

EXPLAIN FOR CONNECTION

To view the execution plan of a running query, you can use the EXPLAIN FOR CONNECTION statement, provided that you know the connection ID of that query. This connection ID can be obtained by using CONNECTION_ID() within that connection, or by executing SHOW PROCESSLIST in any connection. For example:
tdsql> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
|         1048611 |
+-----------------+

tdsql> show processlist;
+---------+------------+-------+-------------------+---------------------+--------------------+---------+------+------------------------+------------------+---------+-----------+---------------+
| Id      | Tid        | Mem   | User              | Host                | db                 | Command | Time | State                  | Info             | Time_ms | Rows_sent | Rows_examined |
+---------+------------+-------+-------------------+---------------------+--------------------+---------+------+------------------------+------------------+---------+-----------+---------------+
| 1048611 | 4294970988 | 16384 | test              | 127.0.0.1:40318     | test               | Query   |    0 | init                   | show processlist |       0 |         0 |             0 |
+---------+------------+-------+-------------------+---------------------+--------------------+---------+------+------------------------+------------------+---------+-----------+---------------+

tdsql> explain for connection 1048611;
ERROR 3012 (HY000): EXPLAIN FOR CONNECTION command is supported only for SELECT/UPDATE/INSERT/DELETE/REPLACE. txid: 0. sql-node: node-1-001. error-store-node: nil
In the example, since the current connection is not executing a DML statement that can display the execution plan with EXPLAIN, EXPLAIN FOR CONNECTION therefore reports an error.
Note that there may be cases where the execution plan seen through EXPLAIN differs from the actual execution plan used when the query runs without EXPLAIN. Possible reasons for this include:
Parameter settings vary across different connections, especially optimizer parameters.
Different connections may see statistical information that is not identical due to time variations, leading to differing row number estimates and consequently different execution plans;
In certain places within the optimizer code, EXPLAIN statements and non-EXPLAIN statements take different paths;
In such cases, using EXPLAIN FOR CONNECTION to view the execution plan actually used by the query execution can be helpful;

EXPLAIN ANALYZE

The EXPLAIN statement is used to view the execution plan selected by the optimizer for an SQL statement. This statement only displays the plan and does not actually execute the query.
EXPLAIN ANALYZE executes the query according to the execution plan and outputs runtime statistics, including the actual time spent, rows returned, and loop counts for each operator. This information can be used to identify the major costs during query execution.
The EXPLAIN ANALYZE output for the query above is:
tdsql> explain analyze select * from t1, t2 where t1.a = t2.a;
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                     |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Inner hash join (t2.a = t1.a)  (cost=6.28 rows=3) (actual time=1.459..1.481 rows=3 loops=1)
    -> Table scan on t2  (cost=0.88 rows=3) (actual time=0.528..0.548 rows=3 loops=1)
    -> Hash
        -> Table scan on t1  (cost=2.84 rows=3) (actual time=0.841..0.863 rows=3 loops=1)
|
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
The above result indicates:
A table scan was performed on both t1 and t2.
The actual number of rows returned for both t1 and t2 is 3, which matches the optimizer's row estimate.
An Inner hash join was performed on t1 and t2, and 3 rows were actually returned.
The time for the JOIN operator to produce the first row of results is 1.459 ms.
The time for the JOIN operator to complete execution is 1.481 ms.
By comparing the actual time, rows, and loops of each operator, you can identify the main time-consuming parts of a query and perform targeted optimization accordingly.

EXPLAIN ANALYZE VERBOSE

TDSQL Boundless also supports EXPLAIN ANALYZE VERBOSE, which outputs more detailed runtime information, including RPC statistics and memory usage.
The EXPLAIN ANALYZE VERBOSE output for the query above is:
tdsql> explain analyze verbose select * from t1, t2 where t1.a = t2.a;
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Inner hash join (t2.a = t1.a)  (cost=6.28 rows=3) (actual time=0.511..0.533 rows=3 loops=1)
   Chunk pair files: 0, memory usage: 16kB
    -> Table scan on t2  (cost=0.88 rows=3) (actual time=0.167..0.185 rows=3 loops=1)
    -> Hash
        -> Table scan on t1  (cost=2.84 rows=3) (actual time=0.262..0.283 rows=3 loops=1)
  RPC statistics: leader
    -> LocalScanRecord=latency(ms): 2,0.266323,0.081208...0.185115, retry_count: 0, retry_interval_all(ms): 0.000000, failure_count: 0 |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
The above result indicates:
The memory usage is 16 kB.
The query contains two LocalScanRecord RPCs.
The total RPC duration is 0.266323 ms.
The minimum duration for a single RPC is 0.081208 ms, and the maximum duration is 0.185115 ms.
No retries or failures occurred during the RPC execution.
EXPLAIN ANALYZE VERBOSE is suitable for scenarios that require further analysis of RPC overhead, memory usage, and operator execution details.

SHOW PROFILE

In some scenarios, the time consumption of a slow query is not necessarily concentrated in the operator execution phase of the execution plan. For example:
EXPLAIN ANALYZE shows a low execution time, but the actual query remains slow.
EXPLAIN ANALYZE indicates that the query is slow, but the execution plan itself shows no obvious issues.
The query may be affected by other slow queries, resource contention, or server-side processing stages.
At this point, you can use SHOW PROFILE to view the time consumption of the query at each stage on the database server side. For example:
tdsql> set profiling = 1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

tdsql> select * from t1 where a > 0;
+------+------+
| a    | b    |
+------+------+
|    1 |    1 |
|    2 |    2 |
|    3 |    3 |
+------+------+
3 rows in set (0.00 sec)

tdsql> show profiles;
+----------+------------+------------------------------+
| Query_ID | Duration   | Query                        |
+----------+------------+------------------------------+
|        1 | 0.00195300 | select * from t1 where a > 0 |
+----------+------------+------------------------------+
1 row in set, 1 warning (0.00 sec)

tdsql> show profile for query 1;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000204 |
| Executing hook on transaction  | 0.000004 |
| starting                       | 0.000031 |
| checking permissions           | 0.000014 |
| Opening tables                 | 0.000095 |
| init                           | 0.000009 |
| System lock                    | 0.000035 |
| optimizing                     | 0.000020 |
| statistics                     | 0.000076 |
| Wait gts rsp                   | 0.000354 |
| preparing                      | 0.000082 |
| executing                      | 0.000849 |
| end                            | 0.000007 |
| query end                      | 0.000005 |
| waiting for handler commit     | 0.000028 |
| closing tables                 | 0.000061 |
| freeing items                  | 0.000079 |
| cleaning up                    | 0.000004 |
+--------------------------------+----------+
18 rows in set, 1 warning (0.01 sec)
SHOW PROFILE can be used to analyze the stage-wise time consumption of a query within the server-side processing pipeline, which is not limited to the Executor execution phase.
In addition, other tools available for analyzing query latency and bottlenecks include SPAN_TRACE, slow query logs, and so on. Comprehensive use of these tools can help identify genuine slow queries and pinpoint where the time is consumed.

OPTIMIZER TRACE

The above information pertains to the execution plan ultimately selected by the optimizer and its performance. It helps pinpoint where exactly a slow query is lagging. Beyond this, users may also need to understand why the optimizer chose this slow plan—whether it genuinely had no better alternatives, whether evaluation deviations led to selecting the wrong plan, or whether it simply failed to discover a more optimal execution plan. This necessitates the Optimizer Trace feature, which retraces the optimizer's specific decision-making process for the execution plan.
Take a simple example to explain the usage of Optimizer Trace:
tdsql> set optimizer_trace='enabled=on';
Query OK, 0 rows affected (0.00 sec)

tdsql> explain select * from t1 where a > 0;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

tdsql> select * from information_schema.optimizer_trace;
+--------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------+-------------------------+
| QUERY                                | TRACE                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            | MISSING_BYTES_BEYOND_MAX_MEM_SIZE | INSUFFICIENT_PRIVILEGES |
+--------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------+-------------------------+
| explain select * from t1 where a > 0 | {
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select `t1`.`a` AS `a`,`t1`.`b` AS `b` from `t1` where (`t1`.`a` > 0)"
          }
        ]
      }
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
          {
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "(`t1`.`a` > 0)",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "(`t1`.`a` > 0)"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "(`t1`.`a` > 0)"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "(`t1`.`a` > 0)"
                }
              ]
            }
          },
          {
            "substitute_generated_columns": {
            }
          },
          {
            "table_dependencies": [
              {
                "table": "`t1`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ]
              }
            ]
          },
          {
            "ref_optimizer_key_uses": [
            ]
          },
          {
            "rows_estimation": [
              {
                "table": "`t1`",
                "table_scan": {
                  "rows": 3,
                  "cost": 2.5375
                }
              }
            ]
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [
                ],
                "table": "`t1`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "rows_to_scan": 3,
                      "filtering_effect": [
                      ],
                      "final_filtering_effect": 0.333333,
                      "access_type": "scan",
                      "resulting_rows": 1,
                      "cost": 2.8375,
                      "chosen": true
                    }
                  ]
                },
                "condition_filtering_pct": 100,
                "rows_for_plan": 1,
                "cost_for_plan": 2.8375,
                "chosen": true
              }
            ]
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": "(`t1`.`a` > 0)",
              "attached_conditions_computation": [
              ],
              "attached_conditions_summary": [
                {
                  "table": "`t1`",
                  "attached": "(`t1`.`a` > 0)"
                }
              ]
            }
          },
          {
            "force_batched_key_access": [
              {
                "table": "`t1`",
                "batched_key_access": true
              }
            ]
          },
          {
            "finalizing_table_conditions": [
              {
                "table": "`t1`",
                "original_table_condition": "(`t1`.`a` > 0)",
                "final_table_condition   ": "(`t1`.`a` > 0)"
              }
            ]
          },
          {
            "refine_plan": [
              {
                "table": "`t1`"
              }
            ]
          },
          {
            "engine_push_conditions": [
              {
                "table": "`t1`",
                "total_rows": 3,
                "index": "hidden pk",
                "condition_push": {
                },
                "single_table_push": {
                  "projection_push": {
                    "enabled": false,
                    "cause": "read field pct is less than tdsql_max_projection_pct"
                  }
                },
                "condition_pushed": false,
                "cause": "scan_rows < tdsql_push_down_threshold_rows"
              }
            ]
          }
        ]
      }
    },
    {
      "parallel_plan": {
        "select#": 1,
        "steps": [
          {
            "considering": {
              "chosen": false,
              "cause": "plan_cost_less_than_threshold"
            }
          }
        ]
      }
    },
    {
      "local_access_optimize": {
        "check_local": true,
        "tables": [
          {
            "table": "`t1`",
            "type": "scan",
            "strategy": "condidate_for_local",
            "info": "not support direct local"
          }
        ]
      }
    },
    {
      "join_explain": {
        "select#": 1,
        "steps": [
        ]
      }
    }
  ]
} |                                 0 |                       0 |
+--------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------+-------------------------+
1 row in set (0.01 sec)

tdsql> set optimizer_trace='enabled=off';
Query OK, 0 rows affected (0.00 sec)
It shows what operations and decisions the optimizer made at each stage, along with the underlying reasons. For instance, in the above example, the optimizer did not choose to push down the filter condition a > 0 to the storage layer, nor did it opt for a parallel scan on table t1, because it estimated that the number of rows of t1 was too low to exceed the trigger threshold for the corresponding optimization. It also refrained from performing column pruning in TDStore, as the proportion of columns required by the query relative to those in the primary key index exceeded tdsql_max_projection_pct.
前の記事へ: SQL Tuning Overview次の記事へ: SQL Tuning

ヘルプとサポート

この記事はお役に立ちましたか？

フィードバック