Use the SQL optimization feature - Database Autonomy Service

Database Autonomy Service (DAS) provides an SQL optimization feature that automatically generates diagnosis results, optimization suggestions, and expected performance gains. Use these results to decide whether to accept the suggestions. This topic describes how to use the SQL optimization feature.

Prerequisites

The database engine is:
- ApsaraDB RDS for MySQL
- MyBase MySQL
- PolarDB for MySQL
  Note
  Single-node clusters (formerly standalone instances) of PolarDB for MySQL are not supported.
- PolarDB-X 2.0
  Note
  PolarDB-X 2.0 instances with major version 5.4.13 and a minor version within the range of [16415631,16504348] are not supported. To view the version of a PolarDB-X 2.0 instance, see View and upgrade an instance version.
- MongoDB
The destination instance is connected to DAS. For more information, see Connect a database instance to DAS.
The accessed state of the destination instance is Normal Access.

Feature limits

Diagnosis and optimization of SQL statements that use X-Engine tables are not supported.
In PolarDB-X, if you execute an SQL statement using Prepared Statements, the slow query log records the statement template, such as select * from test where a = ? and b = ?, and the bound parameters, such as params: [1, 2], separately. Because this format cannot be directly executed as a valid SQL statement, analysis or optimization capabilities that rely on the original statement may be limited.

Create a visual SQL analysis on the Slow Log Analysis page

This feature renders complex SQL execution flows in a graphical way. The graphical interface provides a clear view of the query execution path, the efficiency of each node, and potential performance bottlenecks. This helps you quickly locate and optimize issues in scenarios such as slow SQL statement optimization, pre-release code review, and self-checks.

Important

Currently, SQL optimization on the Slow Log Analysis page is supported only for instances of ApsaraDB RDS for MySQL 5.6, 5.7, and 8.0 and PolarDB for MySQL 5.6, 5.7, and 8.0.

Log on to the DAS console.
In the navigation pane on the left, click Intelligent O&M Center > Instance Monitoring .
Find the target instance and click the instance ID to open the instance details page.
In the navigation pane on the left, click Request Analysis > Slow Logs.
On the Slow Log Analysis page:
- On the Slow Log Statistics tab, find the target SQL template and click Optimize in the Actions column.
- On the Slow Log Details tab, find the target SQL statement and click Optimize in the Actions column.
In the dialog box that appears, click Create Plan.
Select an execution node type.
- Standby node (default): The secondary node of the current instance. It is mainly used for regular query analysis.
- Event node: The business node where the SQL statement was actually executed. It is suitable for troubleshooting and optimization.
Click Confirm Create to create a graphical execution plan.

Graphical execution plan details

Important

The graphical execution plan reflects the execution status at the current time, not a historical record.
The visualization matrix changes based on the complexity of the SQL statement. If the graph is complex, use the scaling tools to adjust the display ratio or click the reset button to return to the initial view.

In a graphical execution plan, the execution order is from bottom to top and from left to right. The bottom-most node is the starting point of the query. The data processing flow is displayed layer by layer upwards until the final query result is output. The following figure provides an example.

Node color meanings

Efficient nodes (green): Indicate efficient access methods, such as system, const, eq_ref, ref, ref_or_null, and index_merge.
Moderately efficient nodes (yellow): Indicate suboptimal access methods, such as fulltext, unique_subquery, index_subquery, and range.
Inefficient nodes (red): Indicate inefficient access methods that should be prioritized for optimization, such as all and index.

Node display information

Top: Displays the node type, such as TABLE_SCAN or INDEX_SCAN.
Left: Displays the cost. This is a relative metric estimated by the optimizer, which considers factors such as CPU, memory, and disk I/O.
Right: Displays the estimated number of returned rows to help evaluate the data processing volume.

Optimization suggestions

Prioritize optimizing the inefficient nodes marked in red to significantly improve query performance.

Glossary

English terms	Definition	Technical explanation
QUERY_BLOCK	Query block	A semantic unit of an SQL statement. Each independent query or subquery forms a query block. It is identified by `select_id` in the EXPLAIN output.
ATTACHED_SUBQUERIES	Attached subquery	A subquery attached to a `WHERE, HAVING, or ON` clause using predicates such as `EXISTS, IN, or ANY`. It has a logical dependency on the main query.
CORRELATED_SUBQUERY	Correlated subquery	A nested query that references columns from the outer query. It requires binding to external context values during execution, which can lead to O(n²) complexity.
NON_CORRELATED_SUBQUERY	Non-correlated subquery	A self-contained subquery that can be executed independently of the outer query. The optimizer usually computes it in advance and materializes it as a constant.
MATERIALIZED_FROM_SUBQUERY	Materialized subquery	An optimization policy introduced in MySQL 5.6 and later. It persists the subquery result to an in-memory temporary table. It is often associated with a derived table in the `subqueryN` format.
OPTIMIZED_AWAY_SUBQUERIES	Optimized-away subquery	A subquery that is completely removed after query rewrite optimization, such as a constant subquery pushdown. It does not appear in the final execution plan.
QUERY_SPECIFICATIONS	Query specification structure	The syntax elements that describe a complete query. It is a complete semantic unit composed of components such as the SELECT list, FROM clause, and filter conditions.
SELECT_LIST_SUBQUERIES	SELECT list subquery	A scalar subquery that appears in the projection column (SELECT field list). It must return a single scalar value for each iteration.
INDEX_SCAN	Index scan	A data access pattern that uses a B+ tree index. It can be a forward or reverse scan depending on the scan direction. It may include operations that retrieve table data using the index.
TABLE_SCAN	Full table scan	When no suitable index is available or when the data to be accessed exceeds a certain threshold, the optimizer chooses to scan all pages of the clustered index. The performance is directly related to the data volume of the table.
ORDERING_OPERATION	Result set sorting	An ORDER BY operation on a result set that uses an explicit sorting algorithm, such as filesort. It may use memory or temporary disk files.
NESTED_LOOP	Nested loop join	The most basic implementation of a table join algorithm. It iterates through the matching rows of the inner table for each row of the outer table. It is efficient when the join predicate provides effective filtering.
DUPLICATES_REMOVAL	Result deduplication	An operation that implements DISTINCT semantics. It may be implemented using a unique index on a temporary table or by sorting and then filtering. The cost depends on the data distribution.
WINDOWING_OPERATION	Window function calculation	An analytic function calculation, such as `ROW_NUMBER or RANK`, performed on the data window defined by the OVER() clause. It may require sorting the full dataset.
TABLE	Base table reference	A physical storage object that is directly accessed in the execution plan. It contains information such as the table name, alias, and access method (for example, `const, system, or range`).

Perform SQL optimization on the Instance Session page

Important

Currently, SQL optimization on the Instance Session page is not supported for self-managed MySQL, MongoDB, and ApsaraDB RDS for PostgreSQL database instances.

Log on to the DAS console.
In the navigation pane on the left, click Intelligent O&M Center > Instance Monitoring .
Find the target instance and click the instance ID to open the instance details page.
In the navigation pane on the left, click Instance Sessions.
In the Instance Sessions area, select the session that you want to optimize and click Optimize.
In the SQL Diagnostic Optimization dialog box, view the SQL diagnosis results.
If you accept the suggestion, click Copy in the upper-right corner of the page and paste the optimized SQL statement into a database client or DMS to execute it. If you do not accept the suggestion, click Cancel to end the diagnosis.
Note
DAS diagnoses the SQL statement based on its complexity, the data volume of the corresponding table, and the database payload. The diagnosis may take more than 20 seconds. After the diagnosis is complete, the SQL diagnostics engine provides the diagnosis results, optimization suggestions, and expected performance gains. You can use the diagnosis results to decide whether to accept the suggestions.

View SQL diagnosis history

Log on to the DAS console.
In the navigation pane on the left, click Intelligent O&M Center > Instance Monitoring .
Find the target instance and click the instance ID to open the instance details page.
In the navigation pane on the left, click Request Diagnostic History. View the SQL diagnosis history of the current instance, such as the SQL content, diagnosis status, diagnosis time, and diagnosis results.