Use and manage the statement outline feature - PolarDB - Alibaba Cloud Documentation Center

The execution plan of SQL statements in the production environment constantly changes. Therefore, databases can be unstable. PolarDB for MySQL provides the statement outline feature that uses optimizer hints and index hints to ensure the stability of execution plans. PolarDB for MySQL also provides the DBMS_OUTLN package. You can use the package to enable the statement outline feature. This topic describes how to use and manage the statement outline feature.

Prerequisites

Your cluster is a PolarDB cluster that runs one of the following versions:

PolarDB for MySQL 5.6 whose revision version is 5.6.1.0.36 or later.
PolarDB for MySQL 5.7 whose revision version is 5.7.1.0.2 or later.
PolarDB for MySQL 8.0.1 whose revision version is 8.0.1.1.1 or later.
PolarDB for MySQL 8.0.2.

For information about how to check the version of a cluster, see Query the engine version.

Hints

The statement outline feature supports the following types of hints provided by MySQL 8.0:

Optimizer Hints
Optimizer hints are classified into table-level optimizer hints, index-level optimizer hints, and join-order optimizer hints based on applicable scopes and hint objects. For more information, see Optimizer Hints.
Note
Optimizer hints are not supported in PolarDB for MySQL 5.6 clusters.
Index Hints
Index hints are classified based on types and scopes. For more information, see Index Hints.

Parameters

Log on to the PolarDB console. On the parameter configuration page, you can configure the opt_outline_enabled parameter described in the following table to enable or disable the statement outline feature. For more information, see Specify cluster and node parameters.

Parameter

Level

Description

loose_opt_outline_enabled

Global

Specifies whether to enable the statement outline feature. Valid values:

ON (default)
OFF

Outline tables

PolarDB uses a system table named outline to store hints. The system automatically creates the table on startup. The following statement is used to create the system table:

CREATE TABLE `mysql`.`outline` (
  `Id` bigint(20) NOT NULL AUTO_INCREMENT,
  `Schema_name` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `Digest` varchar(64) COLLATE utf8_bin NOT NULL,
  `Digest_text` longtext COLLATE utf8_bin,
  `Type` enum('IGNORE INDEX','USE INDEX','FORCE INDEX','OPTIMIZER') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `Scope` enum('','FOR JOIN','FOR ORDER BY','FOR GROUP BY') CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT '',
  `State` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'Y',
  `Position` bigint(20) NOT NULL,
  `Hint` text COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`Id`)
) /*!50100 TABLESPACE `mysql` */ ENGINE=InnoDB
DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0 COMMENT='Statement outline'

The following table describes the parameters in the statement.

Parameter	Description
Id	The ID of the outline table.
Schema_name	The name of the database that belongs to the account.
Digest	The 64-byte statement digest hash value that is calculated based on the value of the Digest_text parameter. For more information, see STATEMENT_DIGEST().
Digest_text	The digest of the SQL statement.
Type	For optimizer hints, the hint type is OPTIMIZER. For index hints, the hint type is USE INDEX, FORCE INDEX, or IGNORE INDEX.
Scope	This parameter is required only for index hints. Valid values: FOR GROUP BY FOR ORDER BY FOR JOIN Note An empty string indicates index hints of all types.
State	Specifies whether the rule is enabled. Valid values: N Y (default)
Position	For optimizer hints, the Position parameter specifies query blocks because all optimizer hints are applied to query blocks. The Position value is a numeric identifier starting from 1 that identifies the position of the keyword that is applied by hints. For index hints, the Position value is a numeric identifier starting from 1 that identifies the position of the table that is applied by hints.
Hint	For optimizer hints, the Hint parameter specifies a hint string. Example: `/+ MAX_EXECUTION_TIME(1000) /`. For index hints, the Hint parameter specifies a list of index names. Example: `ind_1,ind_2`.

Note

If the Schema_name parameter is specified, the Schema_name and Digest values of the SQL statement must be the same as the Schema_name and Digest values in the statement outline rule. Otherwise, the statement outline rule does not take effect.
If the Schema_name parameter is left empty, the Digest value of the SQL statement must be the same as the Digest value in the statement outline rule. Otherwise, the statement outline rule does not take effect.

Manage statement outline rules

Note

You can manage statement outline rules only on the primary node. After the operation is complete, the changes are automatically synchronized to other nodes.

PolarDB provides six local rules in the DBMS_OUTLN package. You can use the rules to manage statement outline rules.

add_optimizer_outline: adds optimizer hints.
Syntax
- Syntax 1:
```
dbms_outln.add_optimizer_outline('<Schema_name>','<Digest>','<query_block>','<hint>','<query>');
```
- Syntax 2: This syntax lacks the Digest and query_block parameters present in Syntax 1. It leaves the Digest parameter unconfigured, and uses a default value of 1 for the query_block parameter.
```
dbms_outln.add_optimizer_outline('<Schema_name>','<hint>','<query>');
```
Note
- The add_optimizer_outline rule is not supported in PolarDB for MySQL 5.6 clusters.
- When you use Syntax 1, you can specify Digest or query in the statement. If you enter a query statement for the query parameter, DBMS_OUTLN calculates the values of the Digest and Digest_text parameters.
- If a quotation mark is required for a field in a query statement, add a single quotation mark (") to the field and enclose the query statement with a pair of double quotation marks ("").
Examples
- The Schema_name and Digest values of a query are the same as the corresponding values in the statement outline rule. Example:
```
CALL dbms_outln.add_optimizer_outline('outline_db', '', 1, '/*+ MAX_EXECUTION_TIME(1000) */',
                                      'SELECT * FROM t1 WHERE id = 1');
```
  The preceding statement is equivalent to the following statement:
```
CALL dbms_outln.add_optimizer_outline('outline_db', '/*+ MAX_EXECUTION_TIME(1000) */', 'SELECT * FROM t1 WHERE id = 1');
```
  The preceding statement is also equivalent to the following statement:
```
CALL dbms_outln.add_optimizer_outline('outline_db', '36bebc61fce7e32b93926aec3fdd790dad5d895107e2d8d3848d1c60b74bcde6',
                                      1, '/*+ MAX_EXECUTION_TIME(1000) */', '');
```
  36bebc61fce7e32b93926aec3fdd790dad5d895107e2d8d3848d1c60b74bcde6 is the Digest value from the SELECT * FROM t1 WHERE id = 1 statement. Therefore, the two statements have the same results.
- The Digest value of the SQL statement is the same as the corresponding value in the statement outline rule. Example:
```
CALL dbms_outln.add_optimizer_outline('', '', 1, '/*+ MAX_EXECUTION_TIME(1000) */',
                                      'SELECT * FROM t1 WHERE id = 1');
```
add_index_outline: adds index hints.
Syntax
```
dbms_outln.add_index_outline('<Schema_name>','<Digest>',<Position>,'<Type>','<Hint>','<Scope>','<Query>');
```
Note
You can specify Digest or Query in the statement. If you enter a query statement for the Query parameter, DBMS_OUTLN calculates the values of the Digest and Digest_text parameters.
Examples
- The Schema_name and Digest values of a query are the same as the corresponding values in the statement outline rule. Example:
```
CALL dbms_outln.add_index_outline('outline_db', '', 1, 'USE INDEX', 'ind_1', '',
                                "SELECT * FROM t1 WHERE t1.col1 =1 AND t1.col2 ='xpchild'");
```
  The preceding statement is equivalent to the following statement:
```
CALL dbms_outln.add_index_outline('outline_db', 'b4369611be7ab2d27c85897632576a04bc08f50b928a1d735b62d0a140628c4c', 1, 'USE INDEX', 'ind_1', '', "");
```
  b4369611be7ab2d27c85897632576a04bc08f50b928a1d735b62d0a140628c4c is the Digest value from the SELECT * FROM t1 WHERE t1.col1 =1 AND t1.col2 ='xpchild statement. Therefore, the two statements have the same results.
- The Digest value of the SQL statement is the same as the corresponding value in the statement outline rule. Example:
```
CALL dbms_outln.add_index_outline('', '', 1, 'USE INDEX', 'ind_1', '', "SELECT * FROM t1 WHERE t1.col1 =1 AND t1.col2 ='xpchild'");
```

preview_outline: checks how a statement outline rule matches a query, which can be used for manual verification.

Syntax

dbms_outln.preview_outline('<Schema_name>','<Query>');

Example

CALL dbms_outln.preview_outline('outline_db', "SELECT * FROM t1 WHERE t1.col1 =1 AND t1.col2 ='xpchild'");
+------------+------------------------------------------------------------------+------------+------------+-------+---------------------+
| SCHEMA     | DIGEST                                                           | BLOCK_TYPE | BLOCK_NAME | BLOCK | HINT                |
+------------+------------------------------------------------------------------+------------+------------+-------+---------------------+
| outline_db | b4369611be7ab2d27c85897632576a04bc08f50b928a1d735b62d0a140628c4c | TABLE      | t1         |     1 | USE INDEX (`ind_1`) |
+------------+------------------------------------------------------------------+------------+------------+-------+---------------------+
1 row in set (0.00 sec)

show_outline: displays the in-memory hits of a statement outline rule.

Syntax

dbms_outln.show_outline();

Example

CALL dbms_outln.show_outline();
+------+------------+------------------------------------------------------------------+-----------+-------+------+-------------------------------------------------------+------+----------+-------------------------------------------------------------------------------------+
| ID   | SCHEMA     | DIGEST                                                           | TYPE      | SCOPE | POS  | HINT                                                  | HIT  | OVERFLOW | DIGEST_TEXT                                                                         |
+------+------------+------------------------------------------------------------------+-----------+-------+------+-------------------------------------------------------+------+----------+-------------------------------------------------------------------------------------+
|   33 | outline_db | 36bebc61fce7e32b93926aec3fdd790dad5d895107e2d8d3848d1c60b74bcde6 | OPTIMIZER |       |    1 | /*+ SET_VAR(foreign_key_checks=OFF) */                |    1 |        0 | SELECT * FROM `t1` WHERE `id` = ?                                                   |
|   32 | outline_db | 36bebc61fce7e32b93926aec3fdd790dad5d895107e2d8d3848d1c60b74bcde6 | OPTIMIZER |       |    1 | /*+ MAX_EXECUTION_TIME(1000) */                       |    2 |        0 | SELECT * FROM `t1` WHERE `id` = ?                                                   |
|   34 | outline_db | d4dcef634a4a664518e5fb8a21c6ce9b79fccb44b773e86431eb67840975b649 | OPTIMIZER |       |    1 | /*+ BNL(t1,t2) */                                     |    1 |        0 | SELECT `t1` . `id` , `t2` . `id` FROM `t1` , `t2`                                   |
|   35 | outline_db | 5a726a609b6fbfb76bb8f9d2a24af913a2b9d07f015f2ee1f6f2d12dfad72e6f | OPTIMIZER |       |    2 |  /*+ QB_NAME(subq1) */                                |    2 |        0 | SELECT * FROM `t1` WHERE `t1` . `col1` IN ( SELECT `col1` FROM `t2` )               |
|   36 | outline_db | 5a726a609b6fbfb76bb8f9d2a24af913a2b9d07f015f2ee1f6f2d12dfad72e6f | OPTIMIZER |       |    1 | /*+ SEMIJOIN(@subq1 MATERIALIZATION, DUPSWEEDOUT) */  |    2 |        0 | SELECT * FROM `t1` WHERE `t1` . `col1` IN ( SELECT `col1` FROM `t2` )               |
|   30 | outline_db | b4369611be7ab2d27c85897632576a04bc08f50b928a1d735b62d0a140628c4c | USE INDEX |       |    1 | ind_1                                                 |    3 |        0 | SELECT * FROM `t1` WHERE `t1` . `col1` = ? AND `t1` . `col2` = ?                    |
|   31 | outline_db | 33c71541754093f78a1f2108795cfb45f8b15ec5d6bff76884f4461fb7f33419 | USE INDEX |       |    2 | ind_2                                                 |    1 |        0 | SELECT * FROM `t1` , `t2` WHERE `t1` . `col1` = `t2` . `col1` AND `t2` . `col2` = ? |
+------+------------+------------------------------------------------------------------+-----------+-------+------+-------------------------------------------------------+------+----------+-------------------------------------------------------------------------------------+
7 rows in set (0.00 sec)

HIT indicates the number of in-memory hits of the statement outline rule. OVERFLOW indicates the number of times that the query block or table failed to be found.

del_outline: deletes a statement outline rule from the memory or table.

Syntax

dbms_outln.del_outline(<Id>);

Example

CALL dbms_outln.del_outline(32);

Note

If the statement outline rule that you want to delete does not exist, an error is returned. You can execute the SHOW WARNINGS; statement to view the error message.

CALL dbms_outln.del_outline(1000);
Query OK, 0 rows affected, 2 warnings (0.00 sec)

SHOW WARNINGS;
+---------+------+----------------------------------------------+
| Level   | Code | Message                                      |
+---------+------+----------------------------------------------+
| Warning | 7521 | Statement outline 1000 is not found in table |
| Warning | 7521 | Statement outline 1000 is not found in cache |
+---------+------+----------------------------------------------+
2 rows in set (0.00 sec)

flush_outline: re-validates a statement outline rule if you update the statement outline rule in the outline table.

Syntax

dbms_outln.flush_outline();

Example

UPDATE mysql.outline SET Position = 1 WHERE Id = 18;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

CALL dbms_outln.flush_outline(); 
Query OK, 0 rows affected (0.01 sec)

Test the feature

You can check whether the statement outline rule takes effect by using one of the following methods:

Execute the preview_outline statement.

CALL dbms_outln.preview_outline('outline_db', "SELECT * FROM t1 WHERE t1.col1 =1 AND t1.col2 ='xpchild'");
+------------+------------------------------------------------------------------+------------+------------+-------+---------------------+
| SCHEMA     | DIGEST                                                           | BLOCK_TYPE | BLOCK_NAME | BLOCK | HINT                |
+------------+------------------------------------------------------------------+------------+------------+-------+---------------------+
| outline_db | b4369611be7ab2d27c85897632576a04bc08f50b928a1d735b62d0a140628c4c | TABLE      | t1         |     1 | USE INDEX (`ind_1`) |
+------------+------------------------------------------------------------------+------------+------------+-------+---------------------+
1 row in set (0.01 sec)

Execute the EXPLAIN statement.

EXPLAIN SELECT * FROM t1 WHERE t1.col1 =1 AND t1.col2 ='xpchild';
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key   | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | ref  | ind_1         | ind_1 | 5       | const |    1 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
SHOW WARNINGS;
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                                                                                 |
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select `outline_db`.`t1`.`id` AS `id`,`outline_db`.`t1`.`col1` AS `col1`,`outline_db`.`t1`.`col2` AS `col2` from `outline_db`.`t1` USE INDEX (`ind_1`) where ((`outline_db`.`t1`.`col1` = 1) and (`outline_db`.`t1`.`col2` = 'xpchild')) |
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)