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
The database instance that you want to manage is of one of the following types:
ApsaraDB RDS for MySQL High-availability Edition or Enterprise Edition
PolarDB for MySQL Cluster Edition or X-Engine Edition
ApsaraDB MyBase for MySQL High-availability Edition
ApsaraDB RDS for PostgreSQL High-availability Edition
SQL Explorer is enabled for the database instance. For more information, see SQL Explorer.
NoteAutomatic SQL optimization optimizes each SQL statement that affects the performance of your applications. After SQL Explorer is enabled, the system tracks the performance of optimized SQL statements and related SQL statements. If the performance deteriorates, the system automatically rolls back the optimized SQL statements to prevent the created indexes from affecting the performance of other SQL statements. If SQL Explorer is disabled, the system tracks only the performance of optimized SQL statements. Therefore, we recommend that you enable SQL Explorer to obtain more comprehensive performance optimization effects.
Feature description
Automatic index creation relies on the automatic SQL optimization feature and is implemented by using online DDL statements that are supported by the native MySQL kernel. This prevents table locking issues that may be caused by conventional indexing. For more information, see SQL optimization technology.
Procedure
Log on to the DAS console.
In the left-side navigation pane, click Instance Monitoring.
On the page that appears, find the database instance that you want to manage and click the instance ID. The instance details page appears.
On the instance details page, click Autonomy Center in the left-side navigation pane.
On the Autonomy Center tab, click Autonomy Service Settings in the upper-right corner.
On the Autonomous Function Settings tab of the Autonomous Function Management panel, enable the autonomy service. On the Optimization and Throttling tab, select Automatic Index Creation and Deletion.
SQL Diagnostics Only: DAS performs daily diagnostics on SQL statements and provides index optimization suggestions. However, DAS does not create indexes in your database instance.
SQL Diagnostics and Automatic Index Creation: DAS performs daily diagnostics on SQL statements, provides index optimization suggestions, and then creates indexes in your database instance based on the suggestions.
NoteDAS creates indexes within the maintenance window of your database instance.
Click OK.
What to do next
On the Autonomy Center page, you can view optimization events that occurred within a specific period of time.
On the right side of the Slow SQL Statement Diagnostics (Diagnostics and Optimization) section, click Details. Then, on the Root Cause Analysis and Suggestions tab, view information in the Problematic SQL Statement, SQL Statement Optimization, Index Recommendation, and Statement Optimization sections.
References
For more information about how to manually optimize an SQL statement, see SQL optimization.
Related API operations
Operation | Description |
Enables, modifies, or disables the automatic SQL optimization feature for multiple database instances at a time. | |
Queries the SQL optimization suggestions that are generated by the SQL diagnostics feature of DAS. | |
Queries statistics on automatic SQL optimization events within a period of time, such as the total number of optimization events and the maximum improvement. |