Using query governance - ApsaraDB RDS - Alibaba Cloud Documentation Center

Prerequisites

Your instance must be one of the following editions:
- RDS MySQL 8.4 High-availability Edition or Cluster Edition
- RDS MySQL 8.0 High-availability Edition, three-node Enterprise Edition, or Cluster Edition
- RDS MySQL 5.7 High-availability Edition, three-node Enterprise Edition, or Cluster Edition
- RDS MySQL 5.6 High-availability Edition
- RDS MySQL 5.5 High-availability Edition
This feature currently supports database instances in the Chinese mainland, China (Hong Kong), and Singapore regions.
Note
Support for database instances in the China (Hong Kong) and Singapore regions was added on April 1, 2023.

Limitations

Query governance uses T+1 offline analysis. If you tag a query as Optimization Not Required, the change in the Optimizable SQL count appears the next day.
You cannot disable the predefined SQL tags in DAS.
For each database instance, DAS analyzes only the top 200 slow SQL queries ranked by execution count. Therefore, the maximum Slow SQL Templates is 200.

Key concepts

Term	Description
Optimizable SQL	After excluding the SQL statements that do not require optimization from all slow SQL statements, the remaining statements are the Optimizable SQL.
SQL Not Requiring Optimization	Includes SQL queries automatically tagged by DAS as Ignored and queries manually tagged by users as Optimization Not Required.
Ignored	DAS automatically tags SQL statements as Optimization Not Required, such as those starting with `show`, `create`, `xa`, `commit`, `rollback`, `select sleep`, or `explain`.

Procedure

Go to the Instances page. In the top navigation bar, select the region in which the RDS instance resides. Then, find the RDS instance and click the ID of the instance.
In the navigation pane on the left, choose Autonomy Services> Slow Query Logs.
Click the Query Governance tab.
On the Query Governance page, view the analysis results.
You can view query governance results for a specific instance by filtering by time range, region, and engine, or by entering an instance ID.
- Query Governance Overview: View the result data after the system tags and classifies SQL templates.
  
  Note
  DAS counts Failed SQL Executions only on instances for which DAS Enterprise Edition is enabled.
- Query Governance Trend: View the trend of query governance results over the selected time range.
- Top Rankings: View the Best-performing Instances and Worst-performing Instances lists.
  - Worst-performing Instances: Ranks instances in descending order of their slow query execution counts to help you quickly identify the most problematic instances.
  - Best-performing Instances: Ranks instances in ascending order based on the change in their slow query execution counts. A negative value indicates a decrease in slow query executions (good optimization results), while a positive value indicates an increase. This helps you quickly identify the instances with the best governance results.
  DAS recommends that you focus on the rankings for Optimizable SQL.
- SQL to Be Optimized: You can set filter conditions to identify the SQL statements that require governance.
  Note
  These filters are combined with AND logic.
  
  Separate multiple database names with commas (,) to combine them with OR logic.
  
  Separate multiple SQL keywords with spaces to combine them with AND logic.
  
  Separate multiple database usernames with commas (,) to combine them with OR logic.
  
  Selecting multiple rule tags combines them with OR logic.
  - In the Actions column for a target SQL sample, click Suggestions to view detailed governance suggestions.
  - In the Actions column for a target SQL sample, click Add Tag to manually tag the SQL statement. For more information about the tags, see Manual Tagging.
    
    You can also select and tag multiple SQL statements at once.
  - In the Actions column for a target SQL sample, click Samples to view the details of the slow query log sample for the SQL statement.
  - In the Actions column for a target SQL sample, click Trend to view the slow query log analysis details for the SQL statement. For more information about slow query log analysis, see Slow query logs.
  You can export and share the data of optimizable SQL statements as needed. For more information, see best practices.
- Failed SQL: You can set filter conditions to identify the SQL statements that you want to view.
  Note
  
  DAS counts Failed SQL only on instances with DAS Enterprise Edition enabled.
  
  You can filter by database name and SQL keyword. These two filters are combined by using AND logic.
  
  Multiple database names are separated by commas (,) and are combined by using OR logic.
  
  Multiple SQL keywords are separated by spaces and are combined by using AND logic.
  In the Actions column for a target SQL sample, click Samples to view the sample details for the SQL statement.

Best practices

Tagging for optimization

The core governance logic classifies overall slow SQL queries into two categories: Optimization Not Required and Optimizable SQL. You can use the following SQL tags to quickly filter and manage these queries based on priority.

ID	Tag name	Level	Recommendation	Description
NEW_SQL	New slow SQL	Critical	✔️	A slow SQL query that has not appeared in the past seven days.
DAS_IGNORE	DAS ignored	Notice	➖	DAS automatically tags the query as not requiring optimization. Examples include SQL statements that start with `show`, `create`, `xa`, `commit`, `rollback`, `select sleep`, or `explain`.
FUZZY_LIKE	Fuzzy LIKE query	Notice	➖	A `like` clause is used for fuzzy matching, which prevents the use of an index.
HAS_EXPR	Contains expression	Notice	➖	The query contains an expression. The column involved in the calculation cannot use an index.
LARGE_IN_LIST	Large IN list	Notice	➖	The `IN` clause contains more than 200 elements, which is likely generated by a program. Indexes cannot be used on the involved columns.
SELECT_STAR	SELECT full column	Notice	➖	When you use `SELECT`, specify only the required fields to avoid performance issues and resource waste caused by returning unnecessary columns.
INDEX_ADVISOR	Index suggestion	Notice	✔️	A suggestion to create an index to accelerate SQL execution.
COMPLEX_JOIN	Complex JOIN	Notice	➖	Do not `join` more than three tables. The fields that you `join` must have identical data types. For multi-table joins, ensure that the join fields are indexed.
CROSS_DB	Cross-database query	Notice	➖	When you migrate databases and tables across instances, cross-database queries may become unavailable.
SUBQUERY	Contains subquery	Notice	➖	The query contains a subquery. Consider rewriting it with a `JOIN` for better performance.
DEEP_PAGING	Deep paging	Critical	➖	The query uses a `LIMIT` clause for deep paging. We recommend rewriting the query with a join.
WITHOUT_PREDICATE	No predicate	Critical	✔️	The statement contains no predicates. Check whether it involves a full table scan.
NULL_COMPARE	NULL match error	Critical	➖	Use `IS NULL` or `IS NOT NULL` to check for a `NULL` value, because a direct comparison between a value and NULL always results in NULL.
COUNT_NOT_STAR	Improper COUNT syntax	Critical	➖	Use `COUNT()` instead of `count`(column_name) or `count`(constant). `count()` is the standard syntax for counting rows as defined in SQL92. It is database-independent and is not affected by `NULL` or non-`NULL` values. `count`(column_name) does not count rows where the column has a `NULL` value.
LARGE_ROWS_EXAMINED	Excessive rows scanned	Notice	➖	The query scans more than 50,000 rows on average. Excessive scanning consumes more resources, which slows down the query and can impact overall database performance. Note Consider using more selective filter conditions.
LARGE_ROWS_SENT	Excessive rows returned	Notice	➖	The query returns more than 5,000 rows on average. Consider reducing the number of returned rows.
NO_ADVICE	No suggestion	Notice	➖	No analysis suggestion is available.
PERIOD_SQL	Periodic SQL	Notice	➖	Executed at a fixed time every day.

DAS recommends that you focus on Optimizable SQL. You can use the table below to tag SQL statements as Optimization Not Required to continuously reduce the number of statements in the Optimizable SQL list. The following SQL tags support manual tagging.

ID	Tag name	Level	Description
USER_IGNORE	No need to optimize	Notice	After you add this tag, DAS excludes the query from the next day's Optimizable SQL statistics.
DAS_IMPORTANT	Important SQL	Notice	Marks the SQL query as important.
DAS_NOT_IMPORTANT	Not important SQL	Notice	Marks the SQL query as not important.
DAS_IN_PLAN	Optimization planned	Notice	Marks the SQL query as scheduled for optimization.

Data export

You can click Create Export Task to download all the data currently displayed in the SQL Details view.
Note
The data from an export task is retained for three days.
You can create separate download tasks by using different filters, such as by instance or rule. These tasks can then be assigned to different team members for optimization.
You can also select specific SQL records. The export task will then include only the selected records.

Share data

To help you quickly share filtered slow SQL queries with the responsible team members, DAS provides two sharing options:

You can select multiple SQL records and click Batch Sharing. The system generates a short link. The generated link shows only the selected SQL records to users who have DAS console permissions.
You can also click the Export button next to Share. The system generates a short link. Users with DAS console permissions who open the link will see all the details currently displayed on your page, with the same filters applied.