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

Prerequisites

The version of the PolarDB cluster must meet one of the following requirements:

  • A PolarDB for MySQL 5.6 cluster whose revision version is 5.6.1.0.36 or later.
  • A PolarDB for MySQL 5.7 cluster whose revision version is 5.7.1.0.2 or later.
  • A PolarDB for MySQL 8.0 cluster whose revision version is 8.0.1.1.1 or later.

For information about how to view the version of your cluster, see Query the engine version.

Hints

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

  • Optimizer Hints

    Based on applicable scopes and hint objects, optimizer hints are classified into table-level optimizer hints, index-level optimizer hints, and join-order optimizer hints. For more information, see Optimizer Hints.

    Note Optimizer hints are not supported on PolarDB for MySQL 5.6 clusters.
  • Index Hints

    Index hints are classified based on the types and scopes. For more information, see Index Hints.

The outline table

PolarDB uses a system table named outline to store hints. The system automatically creates the table when the system is started. The following statement is used to create this 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.

ParameterDescription
IdThe ID of the outline table.
Schema_nameThe name of the database.
DigestThe 64-byte statement digest hash value that is calculated by Digest_text. For more information, see STATEMENT_DIGEST().
Digest_textThe normalized statement 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.
ScopeThis 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.
StateSpecifies whether the rule is enabled. Default value: Y. Valid values:
  • N
  • Y
Position
  • For optimizer hints, the Position parameter specifies query blocks because all optimizer hints are applied to query blocks. The value of Position specifies the position of the keyword that is applied by hints. The value of the Position parameter starts from 1.
  • For index hints, the Position parameter is the position of the table. The value of Position specifies the position of the table that is applied by hints. The value starts from 1.
Hint
  • For optimizer hints, the Hint parameter specifies a hint string, such as /*+ MAX_EXECUTION_TIME(1000) */.
  • For index hints, the Hint parameter specifies a list of index names, such as ind_1,ind_2.

Manage statement outline rules

Note
  • If the Schema_nameparameter is not empty, the Schema_name and Digest values of the SQL statement must be consistent with the Schema_name and Digest values in the statement outline rule. Otherwise, the statement outline rule does not take effect.
  • If the Schema_nameparameter is empty, the Digest value of the SQL statement must be consistent with the Digest value in the statement outline rule.
PolarDB provides six local rules in the DBMS_OUTLN package. You can use these rules to manage statement outline rules.
  • add_optimizer_outline: adds optimizer hints.
    Syntax
    dbms_outln.add_optimizer_outline('<Schema_name>','<Digest>','<query_block>','<hint>','<query>');
    Note
    • add_optimizer_outline is not supported on PolarDB for MySQL 5.6 clusters.
    • You can enter the Digest or Query in the statement. If you enter a query statement for Query, DBMS_OUTLN calculates the values of Digest and Digest_text.
    Examples
    • The Schema_name and Digest values are consistent. 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", '36bebc61fce7e32b93926aec3fdd790dad5d895107e2d8d3848d1c60b74bcde6',
                                      1, '/*+ MAX_EXECUTION_TIME(1000) */', "");
      36bebc61fce7e32b93926aec3fdd790dad5d895107e2d8d3848d1c60b74bcde6 is the Digest value from the SELECT * FROM t1 WHERE id = 1 statement, so the two statements have the same results.
    • The Digest value is consistent. 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 enter the Digest or Query in the statement. If you enter a query statement for Query, DBMS_OUTLN calculates the values of Digest and Digest_text.
    Examples
    • The Schema_name and Digest values are consistent. 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, so the two statements have the same results.
    • The Digest value is consistent. 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 the result of a statement outline rule, which can be used for manual verification.
    Syntax
    dbms_outln.preview_outline('<Schema_name>','<Query>');
    Examples
    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: views the in-memory hits of a statement outline rule.
    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 memory hits of the statement outline rule. OVERFLOW indicates the number of failure to find the query block or table.
  • del_outline: deletes a statement outline rule from the memory or table,
    Syntax
    dbms_outln.del_outline(<Id>);
    Examples
    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: validates a statement outline rule again if you update the statement outline rule in the outline table.
    Syntax
    dbms_outln.flush_outline();
    Examples
    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)