You can use the slow query log feature to diagnose and optimize SQL statements. An ApsaraDB RDS for MySQL instance is used as an example in this topic to describe how to optimize SQL statements.

Prerequisites

  • This feature is available only for ApsaraDB RDS for MySQL databases, self-managed MySQL databases, PolarDB for MySQL databases, and ApsaraDB for MongoDB databases.
  • An ApsaraDB RDS for MySQL instance is connected to Database Autonomy Service (DAS). For more information, see Access an RDS database instance and Access self-managed instances.
  • The instance is in the Accessed state.

Optimize SQL statements on the Slow Log Statistics tab

  1. On the Slow Log Statistics tab, find the SQL template. In the Actions column on the right side of the tab, click Optimize.
    Note For more information about how to go to the Slow Log Statistics tab, see Analyze slow query logs.
  2. In the SQL Diagnostic Optimization dialog box, 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 accept the SQL diagnosis suggestions, click OK.
    Note DAS performs SQL diagnosis 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 diagnosis is performed. After the diagnosis is complete, the SQL diagnosis engine provides diagnosis results, optimization suggestions, and expected optimization benefits. You can determine whether to accept the suggestions based on the diagnosis results.

Optimize SQL statements on the Instance Sessions page

  1. On the Instance Sessions page, select the SQL statement and click Optimize.
    Note For more information about how to go to the Instance Sessions page, see View the session statistics of an ApsaraDB RDS for MySQL instance.
  2. In the SQL Diagnostic Optimization dialog box, click Copy in the upper-right corner and paste the optimized SQL statements to the database client or DMS for execution. If you do not accept the SQL diagnosis suggestions, click OK.
  3. In the left-side navigation pane, click Request Diagnostic History to view the optimization history of the current instance. For example, you can view the SQL statements and the start time of the diagnosis.