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

Prerequisites

The version of the cluster is PolarDB for MySQL 5.7 or PolarDB for MySQL 8.0.

Features

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

  • Optimizer Hint

    Based on applicable scopes and hint objects, optimizer hints are classified into global-level hint, table-level hint, index-level hint, and join-order hint. For more information, visit the MySQL official website.

  • Index Hint

    Index hints are classified by type or range. For more information, visit the MySQL official website.

Introduction to the outline table

AliSQL 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 lists the parameters and descriptions of the preceding statement.

Parameter Description
Id The ID of the outline table.
Schema_name The name of the database.
Digest The 64-byte statement digest hash value that is calculated by Digest_text. For more information, see STATEMENT_DIGEST().
Digest_text The 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.
Scope This parameter is only required 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 (No) and Y (Yes). Default value: 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 the statement outline

PolarDB provides six local rules in the DBMS_OUTLN package. You can use these rules to manage the statement outline, as shown in the following sections.
  • add_optimizer_outline

    To add an optimizer hint, execute the following statement:

    dbms_outln.add_optimizer_outline('<Schema_name>','<Digest>','<query_block>','<hint>','<query>');

    Example:

    CALL DBMS_OUTLN.add_optimizer_outline("outline_db", '', 1, '/*+ MAX_EXECUTION_TIME(1000) */',
                                          "select * from t1 where id = 1");
    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.
  • add_index_outline

    To add an index hint, execute the following statement:

    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.

    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'");
  • preview_outline

    To query the match result of the SQL statement by using the statement outline, which can be used for manual verification, execute the following statement:

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

    Example:

    ​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.00 sec)​
  • show_outline

    To query the hits of the statement outline in memory, execute the following statement:

    dbms_outln.show_outline();

    Example:

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

    The following table lists the HIT and OVERFLOW parameters.

    Parameter Description
    HIT The number of times that the statement outline hits the destination query block or table.
    OVERFLOW The number of times that the statement outline does not hit the destination query block or table.
  • del_outline

    To delete a statement outline from the memory or a table, execute the following statement:

    dbms_outln.del_outline(<Id>);

    Example:

    ​mysql> call dbms_outln.del_outline(32);
    Note If the statement outline that you want to delete does not exist, the system returns an error. You can execute the SHOW WARNINGS; statement to view the error message.
    ​mysql> call dbms_outln.del_outline(1000);
    Query OK, 0 rows affected, 2 warnings (0.00 sec)
    
    mysql> 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

    If you update the statement outline in the outline table, you need to execute the following statement to make the statement outline take effect.

    dbms_outln.flush_outline(); 

    Example:

    mysql> update mysql.outline set Position = 1 where Id = 18;
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    
    ​mysql> call dbms_outln.flush_outline(); 
    Query OK, 0 rows affected (0.01 sec)​

Test the feature

You can check whether the statement outline takes effect by using one of the following methods:
  • Execute the preview_outline statement.
    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)
  • Execute the explain statement.
    mysql> 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)
    
    mysql> 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)