Database Autonomy Service (DAS) provides the SQL optimization feature to automatically deliver diagnostics results, optimization suggestions, and expected optimization effect. You can determine whether to adopt the suggestions based on the diagnostics. This topic describes how to use the SQL optimization feature.

Prerequisites

  • The following database engines are supported:
    • ApsaraDB RDS for MySQL instance
    • Self-managed MySQL database
    • ApsaraDB for MongoDB instance
    • ApsaraDB RDS for PostgreSQL instance
    • PolarDB for MySQL cluster
  • DAS has accessed the database instance. For more information, see Access instances.
  • The database instance is in the Accessed state.

Optimize SQL statements on the Slow Logs page

  1. Log on to the DAS console.
  2. In the left-side navigation pane, click Instance Monitoring.
  3. On the page that appears, click the ID of the database instance that you want to manage. The instance details page appears.
  4. In the left-side navigation pane, choose Request Analysis > Slow Logs.
  5. On the page that appears, click the Slow Log Analysis tab.
    • In the lower part of the page, click Slow Log Statistics. Then, click Optimize in the Actions column corresponding to an SQL template.
    • In the lower part of the page, click Slow Log Details. Then, click Optimize in the Actions column corresponding to the SQL statement that you want to optimize.
  6. In the SQL Diagnostic Optimization dialog box, view the diagnostics results.

    If you want to adopt the SQL optimization suggestions, click Copy in the upper-right corner and paste the optimized SQL statements to the database client or Data Management (DMS) for execution. If you do not adopt the suggestions, click OK.

    Note DAS performs SQL diagnostics based on the complexity of SQL statements, the amount of data in the table, and the database load. Suggestions may be returned in more than 20 seconds after the SQL diagnostics is performed. After the diagnostics is complete, the SQL diagnostics engine provides diagnostics results, optimization suggestions, and expected optimization effect. You can determine whether to adopt the suggestions based on the diagnostics results.
    SQL diagnostic optimization

Optimize SQL statements on the instance sessions page

Note For MongoDB and ApsaraDB RDS for PostgreSQL instances, SQL optimization cannot be performed on the Instance Sessions page.
  1. Log on to the DAS console.
  2. In the left-side navigation pane, click Instance Monitoring.
  3. On the page that appears, click the ID of the database instance that you want to manage. The instance details page appears.
  4. In the left-side navigation pane, click Instance Sessions.
  5. In the Instance Sessions section, select the sessions for which you want to perform SQL optimization and click Optimize.
  6. In the SQL Diagnostic Optimization dialog box, view the diagnostics results.

    If you want to adopt the SQL optimization suggestions, click Copy in the upper-right corner and paste the optimized SQL statements to the database client or Data Management (DMS) for execution. If you do not adopt the suggestions, click OK.

    Note DAS performs SQL diagnostics based on the complexity of SQL statements, the amount of data in the table, and the database load. Suggestions may be returned in more than 20 seconds after the SQL diagnostics is performed. After the diagnostics is complete, the SQL diagnostics engine provides diagnostics results, optimization suggestions, and expected optimization effect. You can determine whether to adopt the suggestions based on the diagnostics results.
    SQL diagnostic optimization

View the request diagnostic history of SQL statements

  1. Log on to the DAS console.
  2. In the left-side navigation pane, click Instance Monitoring.
  3. On the page that appears, click the ID of the database instance that you want to manage. The instance details page appears.
  4. In the left-side navigation pane, click Request Diagnostic History. On the page that appears, you can view the diagnostic history such as the diagnostic state, start time, and actions of each SQL statement.