Use and manage statement outlines - PolarDB - Alibaba Cloud Documentation Center

In production environments, the execution plans for SQL statements may frequently change, which results in database instability. PolarDB provides the statement outline feature, which allows you to use optimizer hints or index hints to stabilize the execution plans for SQL statements. PolarDB also provides the DBMS_OUTLN toolkit for efficient management of statement outlines. This topic describes how to use and manage statement outlines.

Supported versions

Your PolarDB cluster runs one of the following database engine 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 query the database engine version of a PolarDB 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 available for use 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

You can log on to the PolarDB console to enable or disable the statement outline feature by configuring the opt_outline_enabled parameter. For information about how to configure parameters, see Configure 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 table

PolarDB uses a built-in system table named outline to store hints. The system automatically creates the outline table on startup. The following statement is used to create the outline 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 preceding statement.

Parameter	Description
Id	The ID of each statement outline.
Schema_name	The database schema in which the SQL statement is executed.
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	The context to which the hint applies. This parameter is required only for index hints. Valid values: FOR GROUP BY FOR ORDER BY FOR JOIN Note An empty string specifies that the hint applies to all contexts.
State	Specifies whether to enable the hint. Valid values: N Y (default)
Position	For optimizer hints, the Position parameter specifies the number of the query block to which the optimizer hints apply. The value of the Position parameter is a numeric identifier that starts from 1. For index hints, the Position parameter specifies the number of the table to which the index hints apply. The value of the Position parameter is a numeric identifier that starts from 1.
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`.

Manage statement outlines

Note

A statement outline whose Schema_name parameter is not empty takes effect only if the values of the Schema_name and Digest parameters of the SQL statement match the values of the Schema_name and Digest parameters in the statement outline.
A statement outline whose Schema_name parameter is empty takes effect only if the value of the Digest parameter of the SQL statement matches the value of the Digest parameter in the statement outline.

To efficiently manage statement outlines, PolarDB provides a set of predefined procedures within the DBMS_OUTLN toolkit.

add_optimizer_outline: adds optimizer hints.
add_index_outline: adds index hints.
preview_outline: checks whether a SQL statement matches existing outlines. This is useful for manual validation.
show_outline: displays statement outlines that are matched and used in memory.
del_outline: deletes a statement outline from the memory and the outline table.

add_optimizer_outline

Syntax

dbms_outln.add_optimizer_outline('<Schema_name>','<Hint>','<query>');

Parameters

Parameter	Description
Schema_name	The database schema in which the SQL statement is executed.
Hint	The hint string, such as */+ MAX_EXECUTION_TIME(1000) /*.
Query	The original SQL statement for which you want to use a statement outline.

Note

The add_optimizer_outline procedure is not supported in PolarDB for MySQL 5.6 clusters.

If quoting is required for a part in the Query statement, use single quotation marks (') to enclose the part in the Query statement and use double quotation marks (") to enclose the Query statement.
Regardless of whether the Query statement uses single quotation marks (') or double quotation marks ("), the statement outline applies to the query only if the digest of the query matches the statement outline.

Examples

Original query statement:

SELECT * FROM t1 WHERE name="Tom";

Modified query statement:

SELECT * FROM t1 WHERE name='Tom';

A statement outline is used for the query statement:

CALL dbms_outln.add_optimizer_outline("", "/*+ max_execution_time(1000) */", "SELECT * FROM t1 WHERE name='Tom'");

add_index_outline

Syntax

dbms_outln.add_index_outline('<Schema_name>','<Digest>',<Position>,'<Type>','<Hint>','<Scope>','<Query>');

Note

The Query parameter specifies the original SQL statement for which you want to use a statement outline.
You can specify the Digest or Query parameter in the statement. If you specify a query statement, DBMS_OUTLN calculates the values of the Digest and Digest_text parameters.

Parameters

Parameter	Description
Schema_name	The database schema in which the SQL statement is executed.
Digest	The 64-byte statement digest hash value that is calculated based on the Digest_text parameter. For more information, see STATEMENT_DIGEST().
Position	The number of the table to which the index hints apply. The value of the Position parameter is a numeric identifier that starts from 1.
Type	The hint type. Valid values: USE INDEX, FORCE INDEX, or IGNORE INDEX.
Hint	A list of index names, such as ind_1,ind_2.
Scope	The context to which the hint applies. Valid values: FOR GROUP BY FOR ORDER BY FOR JOIN Note An empty string specifies that the hint applies to all contexts.
Query	The original SQL statement for which you want to use a statement outline.

Examples

CALL dbms_outln.add_index_outline('outline_db', '', 1, 'USE INDEX', 'ind_1', '', "SELECT * FROM t1 WHERE t1.col1 =1 AND t1.col2 ='xpchild'");

preview_outline

Syntax

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

Examples

mysql> 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)

show_outline

Syntax

dbms_outln.show_outline();

Examples

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. OVERFLOW indicates the number of times that the query block or table failed to be found.

del_outline

Syntax

dbms_outln.del_outline(<Id>);

Examples

CALL dbms_outln.del_outline(32);

Note

If the statement outline 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)

Scenarios

Use an optimizer hint to specify an index

Syntax

Note

PolarDB for MySQL 5.6 does not support hints. You can specify an index by using an index outline.

/*+ INDEX(table_name idx) */

Examples

CALL dbms_outln.add_optimizer_outline('test', '/*+ INDEX(t1 i_a) */', 'SELECT test.t1.a AS a FROM test.t1');

Specify indexes by using index hints

Syntax

Note

In versions that do not support hints such as PolarDB for MySQL 5.6, you can specify indexes by using the USE or FORCE INDEX clause.

CALL dbms_outln.add_index_outline('<Schema_name>','<Digest>',<Position>,'<Type>','<Hint>','<Scope>','<Query>');

The Position parameter specifies the table on which the statement outline takes effect. The initial value of the parameter is 1. The Hint parameter applies to the table specified by the Position parameter.

Examples

CALL dbms_outln.add_index_outline('outline_db', '', 1, 'USE INDEX', 'ind_1', '',
                                  "SELECT * FROM t1 WHERE t1.col1 =1 AND t1.col2 ='xpchild'");

Specify an order for JOIN operations

Syntax

Note

You can use the hint to force an order for JOIN operations in a query. You do not need to include all tables in the hint. The specified tables are joined first. The optimizer automatically determines the order of the remaining tables.

/*+ JOIN_PREFIX(t1, t2, ...) */

Examples

CALL dbms_outln.add_optimizer_outline('outline_db', '/*+ JOIN_PREFIX(it1, it2) */',
                                      'SELECT it3.id3, it2.i2, it1.id2
                                       FROM t3 it3, t1 it1, t2 it2
                                       WHERE it3.i3 = it1.id1
                                       AND it2.id2 = it1.id2
                                       GROUP BY it3.id3, it1.id2
                                       ) t, t2 ot
                                       WHERE ot.id2 = t.id2');

Configure a variable for a SQL statement

Syntax

Note

You can specify variable values that take effect only on a specific SQL statement.

/*+ SET_VAR(<var_name>=<var_value>) */

Examples

CALL dbms_outln.add_optimizer_outline('test', '/*+ SET_VAR(max_execution_time=1) */',
                                      'SELECT * from t1 ');

Testing

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

Execute the preview_outline statement. For more information, see preview_outline.

Sample SQL statement:

CALL dbms_outln.preview_outline('outline_db', "SELECT * FROM t1 WHERE t1.col1 =1 AND t1.col2 ='xpchild'");

Sample result:

+------------+------------------------------------------------------------------+------------+------------+-------+---------------------+
| 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.

Note

The results in the Extra column are displayed only in the following versions:

PolarDB for MySQL 8.0.1 whose revision version is 8.0.1.0.34 or later.
PolarDB for MySQL 8.0.1 whose revision version is 8.0.2.2.27 or later.

Sample SQL statement:

EXPLAIN SELECT * FROM t1 WHERE t1.col1 =1 AND t1.col2 ='xpchild';

Sample result:

+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+------------------------------+
| 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; Using outline 1 |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+------------------------------+
1 row in set, 1 warning (0.00 sec)

Sample SQL statement:

SHOW warnings;

Sample result:

+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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)