If slow SQL queries occur in your database 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 types described in the following table.
Database type
Region
ApsaraDB RDS for MySQL High-availability Edition, Enterprise Edition, or Cluster Edition
PolarDB for MySQL Cluster Edition
NotePolarDB for MySQL Cluster Edition that has one node is not supported.
ApsaraDB MyBase for MySQL High-availability Edition
China (Hangzhou), China (Shanghai), China (Shenzhen), China (Heyuan), China (Guangzhou), China (Qingdao), China (Beijing), China (Zhangjiakou), China (Hohhot), China (Ulanqab), China (Nanjing - Local Region), China (Fuzhou - Local Region), China (Chengdu), China (Zhengzhou - Local Region), China (Hong Kong), Japan (Tokyo), South Korea (Seoul), Singapore, Malaysia (Kuala Lumpur), Indonesia (Jakarta), Philippines (Manila), Thailand (Bangkok), UAE (Dubai), SAU (Riyadh - Partner Region), Germany (Frankfurt), US (Silicon Valley), US (Virginia), and UK (London)
ApsaraDB RDS for PostgreSQL
China (Hangzhou), China (Shanghai), China (Shenzhen), China (Heyuan), China (Guangzhou), China (Qingdao), China (Beijing), China (Zhangjiakou), China (Hohhot), China (Ulanqab), China (Chengdu), China (Hong Kong), Japan (Tokyo), Singapore, Malaysia (Kuala Lumpur), Indonesia (Jakarta), Philippines (Manila), UAE (Dubai), Germany (Frankfurt), US (Silicon Valley), US (Virginia), and UK (London)
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.
Benefits
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.
Limits
The automatic SQL optimization feature is not supported for SQL queries on tables that use X-Engine.
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 tab of the Autonomous Function Management panel, enable the autonomy service. On the Optimization and Throttling tab, select Automatic Index Creation and Deletion. Then, you can select one of the following options:
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.
In the Alert Configuration section, configure an alert template and subscribe to alert notifications. This helps you understand the status of an automatic SQL optimization event at the earliest opportunity.
The system recommends an alert template and adds alert rules for the required autonomy events in the alert template. You can configure the alert template as prompted.
NoteIf you have configured an alert template for your database instance, you must add alert rules for the required autonomy events to the alert template as prompted.
If you need to configure an alert template and alert rules for your database instance, follow the instructions in Configure alert templates and Configure alert rules.
In the Select Alert Contact Group step, select an alert contact group. You can perform the following operations:
Click Add Contact to add an alert contact.
Click Create Contact Group to create an alert contact group.
Find the alert contact that you want to manage and click Edit or Remove in the Actions column to modify the information about the alert contact or remove the alert contact.
For more information, see Manage alert contacts.
Click Submit Configuration. In the message that appears, confirm the configurations and 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. |