Use the automatic SQL optimization feature - ApsaraDB RDS

If slow SQL queries occur in your ApsaraDB RDS for MySQL instance or the SQL statements that are executed affect the performance of your applications, you can optimize the SQL statements. Database Autonomy Service (DAS) provides the automatic SQL optimization feature. The feature can automatically diagnose slow SQL queries, generate index optimization suggestions, and create indexes without table locks.

Prerequisites

Your RDS instance runs one of the following MySQL versions and RDS editions:

MySQL 8.0 on RDS High-availability Edition, RDS Enterprise Edition, or RDS Cluster Edition
MySQL 5.7 on RDS High-availability Edition, RDS Enterprise Edition, or RDS Cluster Edition
MySQL 5.6 on RDS High-availability Edition
MySQL 5.5 on RDS High-availability Edition

Note

After slow SQL queries are optimized, the overall performance of the RDS instance may degrade. To ensure that the automatic SQL optimization feature optimizes only abnormal SQL queries, we recommend that you enable the SQL Explorer and Audit feature.

Feature description

DAS uses online DDL statements that are supported by the native MySQL kernel to automatically create indexes. This prevents table locks that may be caused by the creation of regular indexes. For more information, see SQL optimization technology.

Procedure

Go to the Instances page. In the top navigation bar, select the region in which the RDS instance resides. Then, find the RDS instance and click the ID of the instance.
Use one of the following methods to go to the Autonomous Function Management panel:
- In the left-side navigation pane, choose Autonomy Services > Diagnostics. On the page that appears, click the Autonomy Center tab. On the Autonomy Center tab, click Autonomy Service Settings.
- In the left-side navigation pane, choose Autonomy Services > Dashboard. On the page that appears, click Autonomy Service Settings.
On the Autonomous Function Settings tab, enable the autonomy service and select Automatic Index Creation and Deletion on the Optimization and Throttling tab.
- SQL Diagnostics Only: DAS performs daily diagnostics on SQL statements and provides index optimization suggestions. However, DAS does not create indexes on your RDS instance.
- SQL Diagnostics and Automatic Index Creation: DAS performs daily diagnostics on SQL statements, provides index optimization suggestions, and then creates indexes on your RDS instance based on the suggestions.
  Note
  DAS creates indexes within the maintenance window that you specify for your RDS instance.
Click OK.

What to do next

On the Autonomy Center tab, view the optimization events that occurred within a specified time range.

Find the event that you want to view and click Details. On the Root Cause Analysis and Suggestions tab of the Slow SQL Statements Diagnostics (Diagnostics and Optimization) event, view information about Problematic SQL Statements, SQL Statement Optimization, Index Recommendation, and Statement Optimization.

References

For more information about how to manually optimize an SQL statement, see SQL optimization.

Related operations

Operation	Description
UpdateAutoSqlOptimizeStatus	Enables, modifies, or disables the automatic SQL optimization feature for multiple database instances at a time.
GetSqlOptimizeAdvice	Queries the SQL optimization suggestions that are generated by the SQL diagnostics feature of DAS.
GetInstanceSqlOptimizeStatistic	Queries statistics on automatic SQL optimization events within a period of time, such as the total number of optimization events and the maximum improvement.