tencent cloud

TDSQL Boundless

Release Notes
Product Introduction
Overview
Scenarios
Product Architecture
Instance Types
Compatibility Notes
Kernel Features
Kernel Overview
Kernel Version Release Notes
Functionality Features
Performance Features
Billing
Billing Overview
Purchase Method
Pricing Details
Renewal
Overdue Payments
Refund
Getting Started
Creating an Instance
Connect to Instances
User Guide
Data Migration
Data Subscription
Instance Management
Configuration Change
Parameter Configuration
Account Management
Security Group
Backup and Restoration
Database Auditing
Tag Management
Use Cases
Technical Evolution and Usage Practices of Online DDL
Lock Mechanism Analysis and Troubleshooting Practices
Data Intelligent Scheduling and Related Practices for Performance Optimization
TDSQL Boundless Selection Guide and Practical Tutorial
Developer Guide
Developer Guide (MySQL Compatibility Mode)
Developer Guide (HBase Compatibility Mode)
Performance Tuning
Performance Tuning Overview
SQL Tuning
DDL Tuning
Performance White Paper
Performance Overview
TPC-C Test
Sysbench Test
API Documentation
History
Introduction
API Category
Making API Requests
Instance APIs
Security Group APIs
Task APIs
Backup APIs
Rollback APIs
Parameter APIs
Database APIs
Data Types
Error Codes
General Reference
System Architecture
SQL Reference
Database Parameter Description
TPC-H benchmark data model reference
Error Code Information
Security and Compliance
FAQs
Agreements
Service Level Agreement
Terms of Service
Privacy Policy
Data Processing And Security Agreement
Contact Us
Glossary
문서TDSQL BoundlessPerformance TuningSQL TuningUnderstand Execution Plan

Understand Execution Plan

PDF
포커스 모드
폰트 크기
마지막 업데이트 시간: 2026-03-23 16:02:11
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

Through EXPLAIN, users can interpret how the executor will execute the query. However, EXPLAIN merely displays the execution plan selected by the optimizer for the query without actually executing it. To identify high-latency components in this execution logic that cause overall query slowness, EXPLAIN ANALYZE is required. It executes the query according to the displayed plan and collects time consumption statistics for each component along with the number of rows returned. The EXPLAIN ANALYZE result for the example 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)
|
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
It shows that both the scans of table t1 and t2 indeed returned 3 rows of data, consistent with the estimates. The JOIN result between t1 and t2 also consists of 3 rows. Producing the first JOIN row took 1.459 ms, while completing the entire JOIN operation took 1.481 ms. By analyzing the time consumption of each component in EXPLAIN ANALYZE, users can identify the bottleneck of the query and perform targeted optimization.
In addition, TDSQL provides the more informative EXPLAIN ANALYZE VERBOSE feature, which can additionally display the types of RPCs involved in the query, their execution status, and the query's memory usage. The EXPLAIN ANALYZE VERBOSE output for the above query 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 |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
It displays the following information: the query's memory overhead is 16 kB, and it includes two LocalScanRecord RPCs, with a total duration of 0.266323 ms. The fastest RPC took 0.081208 ms, while the slowest took 0.185115 ms. No errors or retries were encountered during the RPC executions. These runtime details help users pinpoint the specific sources of query overhead at a finer granularity.
Sometimes the EXPLAIN ANALYZE of a slow query doesn't appear slow, indicating the time cost may not lie in execution; or EXPLAIN ANALYZE is indeed slow, but its execution plan might be correct and only slowed down by other queries; in such cases, analyzing query latency at a broader level becomes necessary. The SHOW PROFILE tool can be utilized here, 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)
It can provide the time consumption of each step the query undergoes on the database server side, not limited to execution;
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

도움말 및 지원

문제 해결에 도움이 되었나요?

피드백