PolarDB:Statement Outline

Prerequisites

The edition of the instance is PolarDB-X Standard Edition, and the engine is MySQL 8.0.

Feature description

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

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

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

Introduction to the outline table

AliSQL uses a system table named outline to store hints. The instance system automatically creates the table when the system is started. You can execute the following statements to create the table:

CREATE TABLE `mysql`.`outline`(
 Id bigint AUTO_INCREMENT NOT NULL,
 Schema_name varchar(64) DEFAULT NULL,
 Digest varchar(64) NOT NULL,
 Digest_text longtext DEFAULT NULL,
 Type enum('IGNORE INDEX','USE INDEX','FORCE INDEX','OPTIMIZER') COLLATE utf8mb3_general_ci NOT NULL,
 Scope enum('','FOR JOIN','FOR ORDER BY','FOR GROUP BY') COLLATE utf8mb3_general_ci DEFAULT '',
 State enum('N','Y') COLLATE utf8mb3_general_ci DEFAULT 'Y' NOT NULL,
 Position bigint NOT NULL,
 Hint text NOT NULL,
 PRIMARY KEY Outline_id(id)
)engine=InnoDB STATS_PERSISTENT=0 CHARACTER SET utf8mb3 COLLATE utf8mb3_bin comment='Statement outline' TABLESPACE=mysql

The following table describes the parameters in the statement.

Manage the statement outline

AliSQL provides six management interfaces in the DBMS_OUTLN package. The following list describes the interfaces:

• add_optimizer_outline

  Add an optimizer hint. Sample command:

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

  Note

  You can enter 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.

  Example:

  mysql> call dbms_outln.add_optimizer_outline("outline_db", '', 1, '/*+ MAX_EXECUTION_TIME(1000) */',
                                        "select * from t1 where id = 1");

• add_index_outline

  Add an index hint. Sample command:

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

  Note

  You can enter 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.

  Example:

  mysql> 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, Sample command:

  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

  Query the hits of the statement outline in memory. Sample command:

  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 describes the HIT and OVERFLOW parameters.

  Parameter	Description
  HIT	The number of times that the statement outline finds the destination query block or table.
  OVERFLOW	The number of times that the statement outline does not find the destination query block or table.

• del_outline

  Delete a statement outline from the memory or a table. Sample command:

  dbms_outln.del_outline(<Id>);

  Example:

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

  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 modify the statement outline in the outline table, you need to execute the following statement so that the statement outline takes effect again. Sample command:

  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)

Testing

There are two methods to verify whether the statement outline takes effect.

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