All Products
Search
Document Center

Database Autonomy Service:Automatic SQL optimization

Last Updated:May 21, 2024

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.

    Note

    Automatic 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

  1. Log on to the DAS console.

  2. In the left-side navigation pane, click Instance Monitoring.

  3. On the page that appears, find the database instance that you want to manage and click the instance ID. The instance details page appears.

  4. On the instance details page, click Autonomy Center in the left-side navigation pane.

  5. On the Autonomy Center tab, click Autonomy Service Settings in the upper-right corner.

  6. 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.

      Note

      DAS creates indexes within the maintenance window of your database instance.

  7. Click OK.

What to do next

  1. On the Autonomy Center page, you can view optimization events that occurred within a specific period of time.dd

  2. 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.ss

References

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

Related API 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.