Database Autonomy Service (DAS) provides the query governance feature. This feature uses offline data analysis to automatically analyze all slow queries that are executed on your database instances the previous day and adds tags to SQL templates at 01:00:00 every day. This can help you categorize SQL templates and optimize SQL templates based on the severity levels that tags indicate. DAS also provides suggestions to optimize SQL templates and provides the data export feature.

Prerequisites

You can use the query governance feature on database instances that meet the following requirements:
  • The database instances are ApsaraDB RDS for MySQL instances or ApsaraDB PolarDB MySQL-compatible edition instances.
  • The database instances are connected to DAS. For information about how to connect database instances to DAS, see Connect an Alibaba Cloud database instance to DAS.

Limits

  • DAS analyzes slow SQL queries offline on the day after the transaction date (T + 1 day). If you add the No optimization required tag to an SQL template before the system analyzes slow queries of the current day, the SQL template is not included in the SQL templates that need to be optimized.
  • Tags that the system automatically adds to SQL templates cannot be changed or deleted.
    Note If you want to change specific tags that DAS adds to your SQL templates, submit a ticket.

Categories of SQL templates

Category 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 optimization required tag.
SQL templates that are tagged with the Ignored tag The SQL templates that cause slow queries and are automatically tagged with the No optimization required tag by the system. This type of SQL templates include the SQL templates that start with the SHOW, CREATE, XA, COMMIT, ROLLBACK, SELECT SLEEP, or EXPLAIN command.

Enable automatic SQL throttling

  1. Log on to the DAS console.
  2. In the left-side navigation pane, click Query Governance.
    ss
  3. On the Query Governance page, you can view information about slow queries, such as tags that are added to SQL templates that cause slow queries, database instances on which the highest number of slow queries run, and the details of SQL templates that cause slow queries. You can also export the data of these SQL templates on this page.
    Note For more information, see Best practices.

Best practices

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

    The query governance feature categorizes SQL templates that cause slow queries to two types: 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 Tag name Severity level Immediate attention required Description
    NEW_SQL New slow SQL Warning Yes SQL templates that caused slow queries within the previous seven days.
    DAS_IGNORE Ignored Notification No SQL templates that are automatically tagged with the No optimization required tag. This type of SQL templates include the SQL templates that start with the SHOW, CREATE, XA, COMMIT, ROLLBACK, SELECT SLEEP, or EXPLAIN statement.
    FUZZY_LIKE LIKE fuzzy query Notification No SQL templates in which the LIKE operator is used to implement the fuzzy matching method. Indexes cannot be used in SQL queries that use the LIKE operator.
    HAS_EXPR Calculation expressions contained Notification No SQL templates that contain calculation expressions. Indexes cannot be used to query data in columns that are specified in the calculation expressions.
    LARGE_IN_LIST Large list query Notification No SQL templates in which more than 200 elements are specified for the IN operator. These SQL templates may be created by programs. Indexes cannot be used to query data in columns that are specified for the IN operator.
    SELECT_STAR SELECT full column query Notification No SQL templates in which one or more fields are specified in SELECT clauses, but no conditions are specified. If you do not specify conditions, the system wastes resources to query unnecessary data. This reduces the query performance.
    INDEX_ADVISOR Index recommendations Notification Yes 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 Notification No 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 Notification No 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 Sub-queries contained Notification No 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 page turning Warning No 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 Warning Yes 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 matching error Warning No SQL templates in which the ISNULL() function is used to check for NULL values. You cannot use the ISNULL() function 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 Warning No 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 can be executed on all types of databases and counts the number of data records including NULL values. The COUNT (Column name) syntax does not count NULL values.
    LARGE_ROWS_EXAMINED Excessive rows scanned Notification No 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 also affects other SQL queries.
    Note We recommend that you use more specific filter conditions in SQL statements.
    LARGE_ROWS_SENT Excessive rows returned Notification No SQL templates that are used in queries for which more than 5,000 rows of data on average are returned. We recommend that you reduce the number of returned rows.
    NO_ADVICE No suggestions Notification No No suggestions are provided.

    We recommend that you focus on the SQL templates that need to be optimized. Before the system analyzes slow queries every day, you can add one of the following tags to SQL templates. This can reduce the number of SQL templates that need to be optimized.

    ID Tag name Severity level Description
    USER_IGNORE No optimization required Notification After you add this tag to an SQL template, the SQL template is not contained in the SQL Templates to be Optimized list on the next day.
    DAS_IMPORTANT High-priority SQL statements Notification Add this tag to SQL templates that are of high priority.
    DAS_NOT_IMPORTANT Low-priority SQL statements Notification Add this tag to SQL templates that are of low priority.
    DAS_IN_PLAN Scheduling optimization Notification Add this tag to SQL templates for which an optimization plan is created.
  • View information that is displayed in the Top Data section.
    • Instances with Most Slow Queries: 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 highest number of slow SQL queries are executed.
    • Instances with Best Optimization Effects: 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 reduced when compared with that of the previous day. A positive value indicates that the number of slow SQL queries increased 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 focus on the database instances on which the highest number of slow SQL queries are executed and the database instances on which SQL query optimization is most effective.
  • View details of SQL templates.

    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 spaces. The database names are in the OR logical relation.
    • You can specify multiple keywords and separate 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.
  • Export data.
    • Click Export in the upper-right corner of the SQL details section to export data of the filtered SQL templates. Then, you can download the exported data.
      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 to 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 next to 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.