Use the SQL optimization feature - Database Autonomy Service

Database Autonomy Service (DAS) provides an SQL optimization feature that automatically generates diagnostic results, optimization suggestions, and estimated performance gains. You can review these results to decide whether to adopt the suggestions. This topic describes how to use the SQL optimization feature.

Prerequisites

The database engine is one of the following:
- RDS for MySQL
  
  Note
  The Basic and Cluster editions of RDS for MySQL are not supported.
- 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 in the range of [16415631,16504348] are not supported. To view the version of your PolarDB-X 2.0 instance, see View and upgrade an instance version.
- MongoDB
The target instance is connected to DAS. For more information, see Connect an instance to DAS.
The access status of the target instance is Normal Access.

Limitations

You cannot diagnose or optimize SQL statements for tables that use the X-Engine.
In PolarDB-X, if you execute an SQL statement by using a prepared statement, the slow query log records the statement template (for example, select * from test where a = ? and b = ?) and the bound parameters (for example, params: [1, 2]) separately. Because this format cannot be directly executed as a valid SQL statement, features that rely on the original statement for analysis or optimization may be limited.

Create visual SQL analysis from slow logs

This feature presents complex SQL execution flows in an intuitive, visual format. Its graphical interface clearly shows the query's execution path, node efficiency, and potential bottlenecks, helping you quickly identify and fix issues during SQL optimization, code reviews, and health checks.

Important

SQL optimization on the Slow Log Analysis page is supported only for RDS for MySQL 5.6, 5.7, and 8.0 and PolarDB for MySQL 5.6, 5.7, and 8.0 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 left-side navigation pane, 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 standby node of the current instance. This node is mainly used for routine query analysis.
- Event node: The business node where the SQL statement was actually executed. This node is suitable for troubleshooting and optimization.
Click Confirm Creation to create a Visual Execution Plan.

Visual execution plan

Important

The visual execution plan reflects the current execution status, not a historical record.
The visual matrix adapts to the complexity of the SQL statement. If the graph contains a large amount of content, use the zoom tool to adjust the display scale or click the reset button to return to the initial view.

In a visual execution plan, execution flows from bottom to top and from left to right. The node at the bottom is the query starting point. Data flows upward layer by layer until the final result is returned. Example:

Node colors

Efficient node (green): Indicates an efficient access method, including: system, const, eq_ref, ref, ref_or_null, and index_merge.
Moderately efficient node (yellow): Indicates a suboptimal access method, including: fulltext, unique_subquery, index_subquery, and range.
Inefficient node (red): Indicates an inefficient access method that should be prioritized for optimization, including: all and index.

Node details

Top: Displays the node type, such as TABLE_SCAN or INDEX_SCAN.
Left: The cost, a relative metric estimated by the optimizer based on factors like CPU, memory, and disk I/O.
Right: The estimated number of rows returned, which indicates the data processing volume.

Optimization suggestion

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

Glossary

Term	Common name	Description
QUERY_BLOCK	query block	A semantic unit of an SQL statement. Each independent query or subquery constitutes a query block, which is identified by `select_id` in the EXPLAIN output.
ATTACHED_SUBQUERIES	attached subquery	A subquery that is attached to a `WHERE`, `HAVING`, or `ON` clause by using predicates such as `EXISTS`, `IN`, or `ANY`. This type of subquery has a logical dependency on the main query.
CORRELATED_SUBQUERY	correlated subquery	A nested query that references columns from an outer query. It requires binding to external context values during execution and may 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 pre-calculates and materializes it as a constant.
MATERIALIZED_FROM_SUBQUERY	materialized subquery	An optimization strategy introduced in MySQL 5.6+. It persists the subquery result to an in-memory temporary table and 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	A complete semantic unit that describes the syntactic elements of a query, including the SELECT list, FROM clause, and filter conditions.
SELECT_LIST_SUBQUERIES	select list subquery	A scalar subquery that appears in the projection columns (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. Scans can be forward or reverse and may involve table lookups.
TABLE_SCAN	full table scan	An access method where the optimizer scans all pages of the clustered index. This occurs when no suitable index is available or when the proportion of data to be accessed exceeds a threshold. Performance degrades as data volume increases.
ORDERING_OPERATION	result set sorting	An ORDER BY operation that sorts the result set by using an explicit sorting algorithm, such as filesort. This operation may use memory or temporary disk files.
NESTED_LOOP	nested loop join	The most basic implementation of a table join algorithm. For each row in the outer table, it iterates through the matching rows in the inner table. This is efficient when the join predicate provides effective filtering.
DUPLICATES_REMOVAL	result deduplication	An operation that implements DISTINCT semantics. The underlying implementation may use a unique index on a temporary table or sort and then filter the results. The cost depends on the data distribution characteristics.
WINDOWING_OPERATION	window function calculation	An analytic function calculation (such as `ROW_NUMBER` or `RANK`) performed on a data window defined by an OVER() clause. This 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`).

Optimize SQL from the Instance Sessions page

Important

Self-managed MySQL, MongoDB, and RDS for PostgreSQL instances do not support SQL optimization on the Instance Sessions page.

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 left-side navigation pane, 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 that appears, view the SQL diagnosis results.

If you accept the suggestion, click Copy in the upper-right corner and paste the optimized SQL into your database client or DMS for execution. If you do not accept the suggestion, click Cancel to close the dialog box.

Note
DAS diagnoses SQL statements based on their complexity, table data volume, and database load. A diagnosis may take over 20 seconds. Afterward, the engine provides diagnostic results, optimization suggestions, and estimated performance gains for you to review and adopt.

After the diagnosis is complete, the page displays the original SQL, the execution plan (including columns such as select_type, type, and rows), the diagnostic results (including DDL statements for row-store index suggestions and index details), optimization benefits (for example, "Expected performance improvement: 0.55x" and "Low optimization gains"), and statement optimization 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 left-side navigation pane, click Request Diagnostic History. Here, you can view the instance's SQL diagnosis history, which includes details like the SQL content, diagnosis status, time, and results.