如何使用和管理Statement Outline_云原生数据库 PolarDB(PolarDB)-阿里云帮助中心

前提条件

PolarDB集群版本需为如下版本之一：

PolarDB MySQL版5.6版本且小版本为5.6.1.0.36或以上。
PolarDB MySQL版5.7版本且小版本为5.7.1.0.2或以上。
PolarDB MySQL版8.0.1版本且小版本为8.0.1.1.1或以上。
PolarDB MySQL版8.0.2版本。

您可以通过查询版本号来确认集群版本。

功能设计

Statement Outline支持官方MySQL 8.0的所有Hint类型，分为如下两类：

Optimizer Hints
根据作用域和Hint对象，分为Table-Level Optimizer hints、Index-Level Optimizer hints、Join-Order Optimizer hints等，详情请参见Optimizer Hints。
说明
PolarDB MySQL版5.6版本暂不支持使用Optimizer Hints。
Index Hints
根据Index Hints的类型和范围进行分类，详情请参见Index Hints。

参数说明

您可以登录PolarDB控制台。在参数配置页面通过设置参数opt_outline_enabled的值来启用或禁用Statement Outline功能。设置参数的具体操作请参见设置集群参数和节点参数。

参数	级别	说明

参数

级别

说明

loose_opt_outline_enabled

Global

Statement Outline功能控制开关。取值范围如下：

ON（默认）：启用Statement Outline功能。
OFF：禁用Statement Outline功能。

Statement Outline表介绍

PolarDB内置了一张系统表outline来保存Hint，系统启动时会自动创建该表，无需您手动创建。该系统表的创建语句如下所示：

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'

参数说明如下：

参数	说明

参数	说明
Id	Outline ID。
Schema_name	数据库名称。
Digest	Digest_text进行hash计算得到的64个字节的hash字符串，详情请参见 STATEMENT_DIGEST()。
Digest_text	SQL语句的特征。
Type	Optimizer Hints中，Hint类型的取值为OPTIMIZER。 Index Hints中，Hint类型的取值为USE INDEX、FORCE INDEX或IGNORE INDEX。
Scope	仅Index Hints需要提供该参数，分为如下三类： FOR GROUP BY FOR ORDER BY FOR JOIN 说明空串表示所有类型的Index Hints。
State	本规则是否启用，取值范围： N Y（默认）
Position	Optimizer Hints中，Position表示Query Block，因为所有的Optimizer Hints必须作用到Query Block上，Position从1开始，Hint作用在语句的第几个关键字上，Position就是几。 Index Hints中，Position表示表的位置，也是从1开始，Hint作用在第几张表上，Position就是几。
Hint	Optimizer Hints中，Hint表示完整的Hint字符串，例如`/+ MAX_EXECUTION_TIME(1000) /`。 Index Hints中，Hint表示索引名字的列表，例如`ind_1,ind_2`。

管理Statement Outline

说明

当Schema_name非空时，您的SQL语句所在的Schema_name和该语句的Digest值，需要与Statement Outline规则中的Schema_name和Digest值同时匹配，Statement Outline才能生效。
当Schema_name为空串时，您的SQL语句的Digest值与Statement Outline规则中的Digest值匹配，Statement Outline即可生效。

为了便捷地管理Statement Outline，PolarDB在DBMS_OUTLN中定义了五个本地存储规则，详细说明如下：

add_optimizer_outline：增加Optimizer Hints。
add_index_outline：增加Index Hints。
preview_outline：查看匹配Statement Outline的情况，可用于手动验证。
show_outline：查看Statement Outline在内存中命中的情况。
del_outline：删除内存和表中的某一条Statement Outline。

add_optimizer_outline

add_index_outline

preview_outline

show_outline

del_outline

语法

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

参数说明

参数	说明
Schema_name	数据库名称。
Hint	Optimizer Hint中，Hint表示完整的Hint字符串，例如*/+MAX_EXECUTION_TIME(1000) /*。
Query	需要添加Statement Outline的原始SQL语句。

说明

PolarDB MySQL版5.6版本暂不支持使用add_optimizer_outline。

Query语句中需要使用引号时，需要在Query语句中给需要添加引号的部分添加单引号，并使用双引号包围Query。
Query语句使用单引号创建的Statement Outline在实际执行时，无论是使用单引号还是双引号，都能成功匹配Statement Outline。

示例

原始Query语句：

SELECT * FROM t1 WHERE name="Tom";

修改后Query语句：

SELECT * FROM t1 WHERE name='Tom';

增加Outline的Query语句：

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

语法

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

说明

Query为需要添加Statement Outline的原始SQL语句。
Digest和Query可以任选其一。如果填写Query，DBMS_OUTLN会计算Digest和Digest_text。

参数说明

参数	说明
Schema_name	数据库名称。
Digest	Digest_text进行hash计算得到的64字节的hash字符串，详情请参见 STATEMENT_DIGEST()。
Position	Position表示表的位置，也是从1开始，Hint作用在第几个表上，Position就是几。
Type	Index Hint中，Hint类型的取值为USE INDEX、FORCE INDEX或IGNORE INDEX。
Hint	Index Hint中，Hint表示索引名字的列表，例如ind_1,ind_2。
Scope	分为如下三类： FOR GROUP BY FOR ORDER BY FOR JOIN 说明空串表示所有类型的Index Hint。
Query	需要添加Statement Outline的原始SQL语句。

示例

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

语法

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

示例

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)

语法

dbms_outln.show_outline();

示例

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表示该Statement Outline命中的次数，OVERFLOW表示该Statement Outline没有找到Query Block或相应的表的次数。

语法

dbms_outln.del_outline(<Id>);

示例

CALL dbms_outln.del_outline(32);

说明

如果删除的规则不存在，系统会报相应的警告，您可以使用SHOW WARNINGS;查看警告内容。

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)

应用场景

通过Optimizer Hint指定索引

通过Index Hint指定索引

指定JOIN顺序

对语句单独设置变量

语法

说明

由于5.6版本不支持Hint，指定索引可以通过使用Index Outline来实现。

/*+ INDEX(table_name idx) */

示例

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

语法

说明

通过使用USE或FORCE INDEX的方式指定索引，通常适用于不支持提示（Hint）的 5.6版本。

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

Position参数代表可以指定Outline影响的表。初始值为1，Hint参数对应第几张表，同时Position参数值就为几。

示例

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

语法

说明

该Hint可以强制指定JOIN的顺序。并不需要所有表都出现在Hint中，被指定的表将优先进行JOIN，后续的表则由优化器自动决定。

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

示例

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');

语法

说明

可以设置只在该语句中生效的变量值。

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

示例

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

功能测试

您可以使用以下两种方式中的任意一种验证Statement Outline的效果。

通过preview_outline进行预览。

SQL命令如下：

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)

通过EXPLAIN查看。

说明

Extra 列中的结果仅在以下版本中显示：

PolarDB MySQL版8.0.1版本且小版本为8.0.1.0.34或以上。
PolarDB MySQL版8.0.1版本且小版本为8.0.2.2.27或以上。

SQL命令如下：

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

SQL命令如下：

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)