When slow or inefficient SQL queries affect your application's performance, you must optimize them. Database Autonomy Service (DAS) supports automatic SQL optimization. It automatically identifies problematic queries, generates index recommendations, and creates indexes without causing table locks, offering a more efficient alternative to traditional optimization methods.
How it works
It uses the native online DDL (Data Definition Language) feature of the MySQL kernel. This process prevents the long-running table locks that can occur with conventional indexing methods. For more information, see SQL optimization technology.
Prerequisites
|
Database |
Region |
|
China (Hangzhou), China (Shanghai), China (Shenzhen), China (Heyuan), China (Guangzhou), China (Qingdao), China (Beijing), China (Zhangjiakou), China (Hohhot), China (Ulanqab), China (Nanjing - Local Region - Being Discontinued), China (Fuzhou - Local Region - Being Discontinued), 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) |
Billing
You can subscribe to the DAS O&M service to use the automatic SQL optimization feature. For information about the fees for the DAS O&M service, see O&M Service (formerly Economy Edition).
Limitations
DAS does not support the diagnosis and optimization of SQL queries on tables that use the X-Engine.
Procedure
Log on to the DAS console.
In the navigation pane on the left, click .
Find the target instance and click the instance ID to open the instance details page.
In the navigation pane on the left, click Autonomy Center.
On the Autonomy Center page, click Autonomy Service Settings in the upper-right corner.
-
In the tab, turn on the autonomous function switch. Then, on the Optimization and Throttling tab, select Automatic Index Creation and Deletion.
-
SQL Diagnostics Only: DAS provides only recommendations and does not create or modify any index.
-
SQL Diagnostics and Automatic Index Creation: DAS automatically implements the optimization recommendations.
NoteDAS will create an index within the database instance's maintenance window or maintenance window.
-
-
Click OK.
-
Configure an Alert Templates to receive notifications about automatic SQL optimization events.
The system recommends an alert template and adds alert rules for the corresponding autonomy events. You can follow the prompts to complete the configuration.
Note-
If you have already configured an alert template for the instance, follow the prompts to add alert rules for the relevant autonomy events to the existing template.
-
If you need to manually set up an alert template and alert rules, see Configure alert templates and Configure alert rules.
-
-
Select an Alert Contact Groups for notifications.
-
Click Add Contact to add a new alert contact.
-
Click Create Contact Group to add a new alert contact group.
-
To modify or delete an existing contact, click Modify or Remove next to the target contact.
For more information, see Manage alert contacts.
-
-
Click Submit Configuration and confirm the alert settings in the subsequent dialog box.
Next steps
-
On the Autonomy Center page, view automatic SQL optimization events from a specified time range. Then, select Optimization Events in the filter bar, and find the Slow SQL Diagnosis (Diagnostic Optimization) event in the event list below.
-
Click Details. On the Root Cause Analysis and Suggestions tab of the Slow SQL Diagnosis (Diagnostic Optimization) panel, you can view information in the Problematic SQL Statement and SQL Optimization sections. The SQL Optimization section provides details on Index Suggestions and Statement Optimization.
References
To manually optimize a target SQL statement, see SQL optimization.
FAQ
-
Q: Does automatic index creation by DAS cause long-running table locks?
A: No. DAS uses native online DDL operations to create an index. This method is designed to prevent long-running table locks.
-
Q: Why does DAS not optimize a slow SQL query that is detected in the console?
A: Not all slow log entries are optimized. DAS implements an optimization only after it verifies that a recommendation is available for the current SQL statement and that applying the optimization will significantly improve performance. If the SQL statement already has a suitable index, or if the potential performance gain is insignificant, DAS does not apply the optimization.
API reference
|
API |
Description |
|
Enables, modifies, or disables the automatic SQL optimization feature for multiple instances. |
|
|
Queries the diagnostic recommendations generated by the automatic SQL optimization feature of DAS. |
|
|
Queries statistics on automatic SQL optimization tasks within a specified time range, including the total number of tasks and the maximum performance gain. |