Database Autonomy Service (DAS) provides the query governance feature. At 01:00:00 every day, the system automatically analyzes all slow queries that were executed on your database instances during the previous day and adds tags to SQL templates based on severity levels. This can help you categorize and optimize slow SQL queries. DAS also offers suggestions for optimizing SQL templates and allows you to export query governance data.

Prerequisites

  • The database instance that you want to manage by using query governance is an ApsaraDB RDS for MySQL instance, a PolarDB for MySQL instance, or an ApsaraDB RDS for PostgreSQL instance.
  • The database instance is connected to DAS. For information about how to connect database instances to DAS, see Connect an Alibaba Cloud database instance to DAS.

Limits

  • The query governance feature is implemented by analyzing offline data (T+1). If you add the No Need to Optimize tag to an SQL template, the SQL template is not removed from the list of Optimizable SQL Templates until the next day.
  • Tags added by the system cannot be changed or deleted.
    Note If you want to change specific tags that DAS adds to your SQL templates, submit a ticket.

Terms

Term Description
SQL templates that need to be optimized The SQL templates that cause slow queries and need to be optimized.
SQL templates that do not need to be optimized The SQL templates that cause slow queries and are automatically tagged with the Ignored tag by the system or are manually tagged with the No Need to Optimize tag.
SQL templates that are tagged with the Ignored tag The SQL templates that cause slow queries and are automatically tagged with the No Need to Optimize tag by the system. This type of SQL templates includes the SQL templates that start with the SHOW, CREATE, XA, COMMIT, ROLLBACK, SELECT SLEEP, or EXPLAIN statement.

Procedure

  1. Log on to the DAS console.
  2. In the left-side navigation pane, click Query Governance.
    ss
  3. On the Query Governance page, view the query governance results.

    You can specify the time and database engine, and enter the ID of the instance to view the query governance results of the specified instance displayed in the following sections:

    • Query Governance Overview: You can view the query governance results by category. The query governance feature classifies SQL templates that cause slow queries into two categories and you can add tags to SQL templates.
      Note Failed SQL Executions is displayed only for database instances for which DAS Professional Edition is enabled.
    • Query Governance Trend: You can view the change trend of the query governance results within a specified time range.
    • Top Rankings: You can view Best-performing Instances and Worst-performing Instances.
      • Worst-performing Instances: Database instances are displayed in descending order based on the number of slow SQL queries that are executed on the database instances. This can help you find the database instances on which the largest number of slow SQL queries are executed.
      • Best-performing Instances: Database instances are displayed in ascending order based on changes in the number of slow SQL queries that are executed on the database instances. A negative value indicates that the number of slow SQL queries reduces when compared with that of the previous day. A positive value indicates that the number of slow SQL queries increases when compared with that of the previous day. This can help you find the database instances on which SQL query optimization is most effective.

      We recommend that you pay immediate attention to the database instances on which the largest number of slow SQL queries are executed and the database instances on which SQL query optimization is most effective.

    • SQL to Be Optimized: You can set the displayed parameters to filter the SQL statements that you want to govern.
      Note You can specify database names, keywords, and tags to filter SQL templates. The database name filter, the keyword filter, and the tag filter are in the AND logical relation.
      • When you specify multiple database names, separate the database names with commas (,). The database names are in the OR logical relation.
      • You can specify multiple keywords and separate the keywords with spaces. The specified keywords are in the AND logical relation.
      • You can select multiple tags. The selected tags are in the OR logical relation.
      • Click Suggestions in the Actions column corresponding to an SQL sample to view detailed governance suggestions for the SQL sample.
      • Click Tagging in the Actions column corresponding to an SQL sample to add tags to the SQL sample. For more information about tags, see the tag description table.

        You can also select SQL samples to which you want to batch add tags.

      • Click Sample in the Actions column corresponding to an SQL sample to view the slow query log details of the SQL sample.
      • Click Trend in the Actions column corresponding to an SQL sample to view the slow log analysis details of the SQL sample. For more information about slow log analysis and how to perform the analysis, see Slow query log analysis.

      You can export and share the SQL data to be optimized based on your business requirements. For more information, see Best practices.

    • Failed SQL: allows you to specify filter conditions to filter SQL statements that you want to view.
      Note
      • Failed SQL is displayed only for database instances for which DAS Professional Edition is enabled.
      • You can specify database names and keywords to filter SQL templates. The database name filter and the keyword filter are in the AND logical relation.
        • When you specify multiple database names, separate the database names with commas (,). The database names are in the OR logical relation.
        • You can specify multiple keywords and separate the keywords with spaces. The specified keywords are in the AND logical relation.

      Click Sample in the Actions column corresponding to an SQL sample to view the details of the SQL sample.

Best practices

  • Use tags to identify the SQL templates that need to be optimized.

    The query governance feature classifies SQL templates that cause slow queries into two categories: SQL templates that need to be optimized and SQL templates that do not need to be optimized. You can use tags to filter SQL templates and optimize SQL templates based on the severity levels that are indicated by the tags. The following table describes the tags that are supported by DAS.

    ID Name Severity level Immediate attention required Description
    NEW_SQL New Slow SQL Critical ️✔️ SQL templates that caused slow queries within the last seven days.
    DAS_IGNORE Ignored Normal SQL templates that are automatically tagged with the No Need to Optimize tag. This type of SQL templates includes the SQL templates that start with the SHOW, CREATE, XA, COMMIT, ROLLBACK, SELECT SLEEP, or EXPLAIN statement.
    FUZZY_LIKE LIKE Query Normal SQL templates in which the LIKE operator is used to implement fuzzy matching. Indexes cannot be used in queries that contain the LIKE operator.
    HAS_EXPR Expressions Contained Normal SQL templates that contain calculation expressions. Indexes cannot be used on the involved columns.
    LARGE_IN_LIST Large List Query Normal SQL templates in which more than 200 elements are specified for the IN operator. Such SQL templates may be automatically generated by programs. Indexes cannot be used on the involved columns.
    SELECT_STAR Full-column Query Normal SQL templates in which one or more fields are specified in SELECT clauses, but no conditions are specified. If you do not specify conditions, resources are wasted on querying unnecessary data. This reduces query performance.
    INDEX_ADVISOR Index Recommendation Normal ️✔️ SQL templates that are used to query data from tables that are not indexed. We recommend that you create and use indexes to accelerate SQL queries.
    COMPLEX_JOIN Complex JOIN Query Normal SQL templates in which the JOIN operator is used to join more than three tables. You cannot use the JOIN operator to join more than three tables. The data type of the specified fields in the tables that you want to join must be the same. Before you join multiple tables, make sure that you create indexes for the fields that you want to query in the tables.
    CROSS_DB Cross-database Query Normal SQL templates that are used to query data in different databases. After databases or tables are migrated from the original database instance to another database instance, cross-database queries may fail.
    SUBQUERY Subqueries Included Normal SQL templates that contain SQL statements for subqueries. We recommend that you rewrite the SQL templates and use the JOIN operator to make queries more efficient.
    DEEP_PAGING Deep Paging Critical SQL templates in which the LIMIT operator is used to implement the deep paging method. We recommend that you rewrite the templates and use the JOIN operator instead of the LIMIT operator.
    WITHOUT_PREDICATE No Predicates Critical ️✔️ SQL templates that do not contain predicates. Check whether your business requires full table scanning. If your business does not require full table scanning, rewrite the SQL templates.
    NULL_COMPARE Null Match Error Critical SQL templates in which the ISNULL() function is used to check for NULL values. The ISNULL() function cannot be used to find NULL values, because NULL is returned if one of the values is NULL. Rewrite the SQL templates.
    COUNT_NOT_STAR Invalid COUNT Syntax Critical SQL templates that contain invalid COUNT syntax. Use count(*) to query the number of data records. Do not use the COUNT (Column name) or COUNT (Constant) syntax. COUNT(*) is the standard syntax that is defined in SQL92 to query the number of data records. The COUNT(*) syntax counts the number of data records including NULL values and can be executed on all types of databases. The COUNT (Column name) syntax does not count NULL values.
    LARGE_ROWS_EXAMINED Excessive Scanned Rows Normal SQL templates that are used to scan more than 50,000 rows on average. A larger number of rows consume a larger amount of database resources. This causes the SQL queries to run slow and affects other SQL queries.
    Note We recommend that you use more specific filter conditions in SQL statements.
    LARGE_ROWS_SENT Excessive Returned Rows Normal SQL templates that are used in queries for which more than 5,000 rows of data are returned on average. We recommend that you modify the template to reduce the number of returned rows.
    NO_ADVICE No Suggestion Normal No suggestions are provided.
    PERIOD_SQL Periodic Normal SQL templates that are executed at a specific time point on a daily basis.

    We recommend that you pay immediate attention to the SQL templates that need to be optimized. Before the system analyzes slow queries every day, you can manually add one of the tags described in the following table to SQL templates. This can reduce the number of SQL templates that need to be optimized.

    ID Name Severity level Description
    USER_IGNORE No Need to Optimize Normal After you add this tag to an SQL template, the SQL template is not contained in the Optimizable SQL list on the next day.
    DAS_IMPORTANT High-priority SQL Normal Add this tag to SQL templates that are of high priority.
    DAS_NOT_IMPORTANT Low-priority SQL Normal Add this tag to SQL templates that are of low priority.
    DAS_IN_PLAN Optimize Later Normal Add this tag to SQL templates that are to be optimized later.
  • Export data.
    • Click Export in the upper-right corner of the SQL to Be Optimized section to export data of the filtered SQL templates.
      Note The exported data is available for download for three days.
    • You can specify different conditions to filter SQL templates. For example, you can filter SQL templates based on database instances or tags, create download tasks, and then assign different owners to optimize SQL templates based on database instances or tags.
    • You can also select multiple SQL templates and export the data of the selected SQL templates.
  • Share data.

    You can filter SQL templates and share the filtered SQL templates with the owners of the corresponding SQL templates. You can use one of the following methods to share data:

    • Select SQL templates and click Batch Sharing in the lower part of the section. The system generates an URL. On the page to which the URL directs, the information about the selected SQL templates is displayed. Users who are granted the permissions on DAS can visit the page to view the information about the selected SQL templates.
    • Specify conditions to filter SQL templates and click Share to the right of Export. The system generates an URL. On the page to which the URL directs, the information about the filtered SQL templates is displayed. Users who are granted the permissions on DAS can visit the page to view the information about all filtered SQL templates.

Related API operations