Executing SQL and Viewing Execution Plans

Recent Pages

Executing SQL and Viewing Execution Plans

Last updated: 2024-12-17 16:59:43

The read-only analysis engine is compatible with MySQL protocols and syntax. You can access the read-only analysis engine to execute SQL statements through the MySQL client or MySQL linker in development languages.
The read-only analysis engine is a read-only instance, so the executable SQL statements are mainly SELECT statements. DML and DDL statements cannot be executed. Additionally, queries are also strictly restricted by object permissions. If the access account does not have the SELECT permission on an object, it cannot view the object.
During SQL execution, you can directly use MySQL syntax for editing and then execute the statements in the read-only analysis engine. However, sometimes the execution results may not meet expectations, and in such cases, you can use the EXPLAIN feature to view the execution plan.
EXPLAIN Feature and Use
The EXPLAIN feature enables you to view the execution plan selected for executing the query statements in the read-only analysis engine. This plan is the optimal query plan finally selected after multiple stages of optimization by the internal optimizer. When you view the execution plan, the SQL statements will not be actually executed, but only the execution plan is output.
An example of EXPLAIN is as follows:
EXPLAIN <SELECT_STATMENT>
﻿
explain select * from t1 left join t2 on t1.id = t2.id;
The returned result is as follows:
+---------------------------------------------------------------------------------+
| query plan                                                                      |
+---------------------------------------------------------------------------------+
| ============================================                                    |
| |ID|OPERATOR                 |NAME|EST.ROWS|                                    |
| --------------------------------------------                                    |
| |0 |HASH JOIN                |    |12500.00|                                    |
| |1 |├─TABLE FULL SCAN (B)    |t2  |10000.00|                                    |
| |2 |└─TABLE FULL SCAN (P)    |t1  |10000.00|                                    |
| --------------------------------------------                                    |
| Details:                                                                        |
| -------------------------------------                                           |
|   0 - JOIN TYPE: left outer join, EQUAL: [eq(singleton.t1.id, singleton.t2.id)] |
|   1 - STORAGE: LIBRASTORE, BLOCK OFFSET: 1                                      |
|   2 - STORAGE: LIBRASTORE, BLOCK OFFSET: 1                                      |
+---------------------------------------------------------------------------------+
Interpretation of EXPLAIN Results
The returned results of EXPLAIN contain the following fields:
ID is the operator number displayed in the plan, starting from 0 and incrementing sequentially for ease of viewing the details.
OPERATOR is the name of the operator, indicating the operation at each step in the SQL execution process.
NAME shows the information about the accessed tables, partitions, and so on in the inquiry.
EST.ROWS shows the number of rows to be processed by each operator, which is estimated based on the statistics by the read-only analysis engine. If no statistics are available, it will be calculated and shown based on a default value.
Operator Introduction
Operators are specific steps executed to return query results. The table below introduces the names and features of various operators currently supported by the read-only analysis engine:
Operator Name
Operator Introduction
SORT
The SORT operator is used to sort the input data.
TOPN
If an ORDER BY clause is followed by a LIMIT clause, the optimizer will further optimize the execution plan, generating a TOP-N SORT operator and using heap sort to select the TOP-N data.
LIMIT
The LIMIT operator is used to restrict the number of rows in data output, same as the LIMIT operator feature in MySQL.
FILTER
The FILTER operator is used to filter data based on specified predicate conditions, commonly appearing in WHERE/HAVING/ON clauses.
HASH JOIN
HASH JOIN is used to perform JOIN operations on large datasets. The optimizer uses two tables from a dataset. The smaller table is used to build a HASH table in memory based on JOIN conditions, and then the database scans the larger dataset and probes the HASH table to find rows that meet the JOIN conditions.
COLUMN READ
Late materialization operator. The read-only analysis engine supports pushing down partial filter conditions to the TableScan operator, which means first scanning the column data related to the filter conditions, filtering to get the matching rows, and then scanning other column data of these rows for subsequent computation, so as to reduce the IO scans and computation amount of data processing.
TABLE FULL SCAN
Performs a full-table scan on the target table.
UINION
The UNION operator is used to perform a union operation on the result sets of two queries.
WINDOW FUNCTION
The WINDOW FUNCTION operator is used to implement the analytic functions (also called window functions) in SQL and obtain the operation results for related rows within the window. The window function can return multiple rows per group, where each row shows the results of logical operations based on the window.
HASH GROUP BY
The GROUP BY operator is mainly used for grouping and aggregation operations in SQL. Common aggregation functions (SUM/MAX/MIN/AVG/COUNT/STDDEV) are completed by assigning a GROUP BY operator.
PROJECTION
The Projection operator corresponds to the SELECT list in SQL statements. Its feature is mapping each input data entry as new output data.
EXCHANGE RECEIVER
The data receiver operator is used for receiving data during data exchange across compute nodes when MPP queries are executed.
EXCHAGE SENDER
The data sender operator is used for sending data during data exchange across compute nodes when MPP queries are executed.
Detail Information
In addition to the information displayed in the above result table, you can also see a Detail item below. This item shows some additional information of each operator in the format of n - detail info, where n indicates the ID of each operator, followed by the detail information of each operator. The table below provides the detail information shown for each operator.
Operator Name
Detail Information
SORT
ORDER BY KEY: The sort key used by the sort operator.
TOPN
ORDER BY KEY: The sort key used by the sort operator.
OFFSET: The offset specified by the Limit operation.
COUNT: The number of preserved rows specified by the Limit operation.
LIMIT
OFFSET: The offset specified by the Limit operation.
COUNT: The number of preserved rows specified by the Limit operation.
FILTER
CONDITIONS: The predicate condition used by the FILTER operator for filtering data.
HASH JOIN
JOIN TYPE: The type of the current JOIN, such as inner join, left outer join, semi join, etc.
NON EQUAL: Whether the current JOIN is a Cartesian product. If this field is missing, it means that the current JOIN is not a Cartesian product.
EQUAL: The Equal condition used for joining two tables.
OTHER COND: The Not Equal condition used for joining two tables.
Additionally, in the above EXPLAIN results, the operator names with the IDs 1 and 2 are followed by (B) and (P) tags. These tags indicate the Build and Probe sides during the HASH JOIN operation. B stands for Build, and P stands for Probe.
COLUMN READ
COLUMN READ: The names of columns read by late materialization.
TABLE FULL SCAN
STORAGE: The underlying storage types read, currently supporting two types, LIBRASTORE and TDSQL.
BLOCK OFFSET: The number of the query block where the current table is located in the entire SQL statement, used to assist with Hint usage.
UINION
N/A
WINDOW FUNCTION
WINDOW FUNC DESC: The name of the window function.
PARTITION BY: The partition key.
ORDER BY: The sort key used for sorting.
FRAME: The window definition of the window function.
HASH GROUP BY
GROUP BY: The Group By key specified for executing the aggregation function.
AGG FUNCS: The aggregation function specified in SQL.
PROJECTION
EXPRS: The expression list executed by the PROJECTION operator, mainly including the cast function and various scalar functions.
EXCHANGE RECEIVER
N/A
EXCHAGE SENDER
ExchangeType: The method used for data exchange:
PASS: Sends data of multiple nodes to one node.
BCJ: Broadcasts data of one node to multiple nodes. For example, during JOIN, the build table data is broadcast to each node for the JOIN operation.
HASH: Shuffles the data using a HASH function and then distributes it to each node. For example, it is used to shuffle the data of both tables for redistribution during JOIN.
HASH (BY PARTITION): When two tables are joined and the join key of one table is the partition key, the other table is shuffled according to the distribution mode of the table with the join key as the partition key.
In addition to the basic information of operators mentioned in the above table, when the runtime filter operator is enabled and the plan contains a HASH JOIN, both sides of the HASH JOIN operator may contain the following two types of special detail information.
Probe Runtime Filters: When this information is present on the operator, it indicates that the current operator has applied a runtime filter to filter redundant data.
Build Runtime Filters: This information only appears on the JOIN operator, indicating that a runtime filter is generated on the build side of the current JOIN operator to pre-filter redundant data on the Probe side. For specific usage and optimization of the runtime filter, and the role of displayed information, see Runtime Filter Use Instructions.
Different operators return different information after EXPLAIN execution. You can use Optimizer Hints to control the behavior of the optimizer, thereby controlling the selection of physical operators.
For example, /* HASH_JOIN_PROBE(t1) */ indicates that the optimizer will force using the t1 table as the Probe table in the HASH JOIN.
﻿

Contact Us

Contact our sales team or business advisors to help your business.

Technical Support

Open a ticket if you're looking for further assistance. Our Ticket is 7x24 avaliable.

7x24 Phone Support

tencent cloud

Recent Pages

Executing SQL and Viewing Execution Plans

EXPLAIN Feature and Use

Interpretation of EXPLAIN Results

Operator Introduction

Detail Information

Was this page helpful?

Was this page helpful?

Operator Name	Operator Introduction
SORT	The SORT operator is used to sort the input data.
TOPN	If an ORDER BY clause is followed by a LIMIT clause, the optimizer will further optimize the execution plan, generating a TOP-N SORT operator and using heap sort to select the TOP-N data.
LIMIT	The LIMIT operator is used to restrict the number of rows in data output, same as the LIMIT operator feature in MySQL.
FILTER	The FILTER operator is used to filter data based on specified predicate conditions, commonly appearing in WHERE/HAVING/ON clauses.
HASH JOIN	HASH JOIN is used to perform JOIN operations on large datasets. The optimizer uses two tables from a dataset. The smaller table is used to build a HASH table in memory based on JOIN conditions, and then the database scans the larger dataset and probes the HASH table to find rows that meet the JOIN conditions.
COLUMN READ	Late materialization operator. The read-only analysis engine supports pushing down partial filter conditions to the TableScan operator, which means first scanning the column data related to the filter conditions, filtering to get the matching rows, and then scanning other column data of these rows for subsequent computation, so as to reduce the IO scans and computation amount of data processing.
TABLE FULL SCAN	Performs a full-table scan on the target table.
UINION	The UNION operator is used to perform a union operation on the result sets of two queries.
WINDOW FUNCTION	The WINDOW FUNCTION operator is used to implement the analytic functions (also called window functions) in SQL and obtain the operation results for related rows within the window. The window function can return multiple rows per group, where each row shows the results of logical operations based on the window.
HASH GROUP BY	The GROUP BY operator is mainly used for grouping and aggregation operations in SQL. Common aggregation functions (SUM/MAX/MIN/AVG/COUNT/STDDEV) are completed by assigning a GROUP BY operator.
PROJECTION	The Projection operator corresponds to the SELECT list in SQL statements. Its feature is mapping each input data entry as new output data.
EXCHANGE RECEIVER	The data receiver operator is used for receiving data during data exchange across compute nodes when MPP queries are executed.
EXCHAGE SENDER	The data sender operator is used for sending data during data exchange across compute nodes when MPP queries are executed.

tencent cloud

Sign Up

Log in

Recent Pages

Executing SQL and Viewing Execution Plans

EXPLAIN Feature and Use

Interpretation of EXPLAIN Results

Operator Introduction

Detail Information

Was this page helpful?

Was this page helpful?