When slow queries degrade database performance, Database Autonomy Service (DAS) can identify the problematic statements, recommend optimal indexes, and create them automatically — without causing table locks. With automatic SQL optimization, you can:
Detect slow queries and get index recommendations automatically
Apply index changes without blocking reads or writes (uses MySQL online DDL)
Choose between advisory mode (recommendations only) or fully automatic mode
Receive alerts when optimization events occur
Review optimization history and results in the Autonomy Center
How it works
DAS uses the native online DDL functionality of the MySQL kernel to create indexes. This approach avoids the table locks that can occur with conventional DDL operations. Index creation is scheduled within the instance's Maintenance Period or Maintenance Window to minimize impact on production workloads.
DAS applies an optimization only when it verifies that a valid recommendation exists and will significantly improve performance. If a slow query already has an appropriate index, or if the projected performance gain is insignificant, DAS skips the optimization.
For background on the underlying technology, see SQL optimization technology.
Supported databases and regions
| Database engine | Supported regions |
|---|---|
| ApsaraDB RDS for MySQL High-availability Edition, Three-Node Enterprise Edition, or Cluster 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 - Decommissioning), China (Fuzhou - Local Region - Decommissioning), China (Chengdu), China (Zhengzhou), China (Hong Kong), Japan (Tokyo), South Korea (Seoul), Singapore, Malaysia (Kuala Lumpur), Indonesia (Jakarta), Philippines (Manila), Thailand (Bangkok), UAE (Dubai), Saudi Arabia (Riyadh), Germany (Frankfurt), US (Silicon Valley), US (Virginia), and UK (London) |
| PolarDB for MySQL Cluster Edition | Same as above |
| ApsaraDB MyBase for MySQL High-availability Edition | Same as above |
PolarDB for MySQL single-node clusters (formerly single-node instances) are not supported.
Billing
Automatic SQL optimization is available through Alibaba Cloud Managed Services. For pricing details, see Alibaba Cloud Managed Services (Formerly DAS Economy Edition).
Limitations
Automatic SQL optimization does not support queries on tables that use the X-Engine storage engine.
Enable automatic SQL optimization
Log on to the DAS console.
In the left navigation pane, 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 navigation pane, click Autonomy Center.
On the Autonomy Center page, click Autonomy Service Settings in the upper-right corner.
Under Autonomous Function Management > Autonomous Function Settings, turn on the main switch. Then, on the Optimization and Throttling tab, select the Automatic Index Creation and Deletion checkbox. Choose an operating mode:
SQL Diagnostics Only: DAS generates index recommendations without applying any changes. Use this mode to review recommendations before committing to automatic changes.
SQL Diagnostics and Automatic Index Creation: DAS automatically applies optimization recommendations. Use this mode when you want fully autonomous optimization.
DAS creates indexes during the instance's Maintenance Period or Maintenance Window.
Click OK.
Configure an Alert Template to receive notifications about optimization events. The system recommends an alert template and adds the required alert rule for autonomy events. You can use this recommended configuration.
If the instance already has an alert template configured, add an alert rule for autonomy events to the existing template when prompted. To create a new alert template or rule from scratch, see Configure an alert template and Configure an alert rule.
Select an Alert Contact Group for notifications. For more information, see Manage alert contacts.
Click Add Contact to add a new alert contact.
Click Create Contact Group to create a new contact group.
To modify or remove an existing contact, click Edit or Remove next to the contact.
Click Submit Configuration and confirm the settings.
Monitor optimization results
After enabling automatic SQL optimization, review events and outcomes in the Autonomy Center:
On the Autonomy Center page, view Automatic SQL optimization events within the selected time range.
Click Details to open the Slow SQL Statement Diagnostics (Diagnostics and Optimization) panel. On the Root Cause Analysis and Suggestions tab, review the Problematic SQL Statement and SQL Statement Optimization sections. The SQL Statement Optimization section includes Index Recommendation and Statement Optimization details.
FAQ
Does automatic index creation cause table locks?
No. DAS uses native online DDL operations to create indexes, which prevents long-term table locks.
Why didn't DAS optimize a slow query I can see in the console?
Not all slow queries are optimized. DAS applies a change only when it verifies that a valid recommendation exists and will significantly improve performance. If the query already has an appropriate index, or if the projected gain is insignificant, DAS skips the optimization.
API reference
Use the following APIs to manage automatic SQL optimization programmatically — for example, to enable the feature across multiple instances at once or to retrieve optimization statistics.
| API | Description |
|---|---|
| UpdateAutoSqlOptimizeStatus | Enables, modifies, or disables automatic SQL optimization for multiple instances at once. |
| GetSqlOptimizeAdvice | Queries optimization recommendations generated by the automatic SQL optimization feature. |
| GetInstanceSqlOptimizeStatistic | Retrieves statistics about automatic SQL optimization tasks for a specified time range, including total task count and maximum performance improvement. |
See also
To manually optimize a SQL statement, see SQL optimization.