Database Autonomy Service (DAS) provides the SQL diagnostics feature. This feature allows DAS to perform diagnostics on SQL statements, provide index optimization suggestions, and then create indexes for your database instance based on these suggestions. You can enable automatic SQL diagnostics and index creation in the DAS console.

Prerequisites

  • A database instance is connected to DAS. The database instance is an ApsaraDB RDS for MySQL instance of the High-availability Edition or the Enterprise Edition or a PolarDB for MySQL cluster of the Cluster Edition or the Archive Database Edition.
  • The autonomy service is enabled for the database instance. For more information, see Autonomy center.
  • SQL Explorer is enabled for the database instance. For more information, see SQL Explorer.
    Note
    • After slow SQL queries are optimized, the overall performance of the database instance may degrade. To ensure that the automatic SQL optimization feature optimizes only abnormal SQL statements, we recommend that you enable SQL Explorer.
    • If you do not enable SQL Explorer, you can use only SQL diagnostics but not automatic index creation.

Background information

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. Find the database instance that you want to manage and click the instance ID. The instance details page appears.
  4. In the left-side navigation pane, click Autonomy Center.
  5. In the upper-right corner of the Autonomy Center page, click Autonomy Service Settings switch.
    Settings
  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 diagnosis only: DAS performs daily diagnostics on SQL statements and provides index optimization suggestions. However, DAS does not create indexes on your database instance.
    • SQL diagnosis and automatic index creation: DAS performs daily diagnostics on SQL statements, provides index optimization suggestions, and then creates indexes on your database instance based on these suggestions.
  7. Click OK.

Related API operations

GetSqlOptimizeAdvice

Best practices

What to do next

  1. On the Autonomy center page in the DAS console, you can view optimization events that occurred during a specified time period. dd
  2. On the right side of Slow SQL Statement Diagnostics (Diagnostics and Optimization), click Details. Then, on the Root Cause Analysis and Suggestions tab, you can view information about Problematic SQL Statements, SQL Statement Optimization, Index Recommendation, and Statement Optimization. ss