生产环境中，SQL语句的执行计划经常发生改变，导致数据库不稳定。PolarDB利用Optimizer Hints和Index Hints让MySQL稳定执行计划，该方法称为Statement Outline，并提供了工具包DBMS_OUTLN方便您快捷使用。本文将介绍如何使用和管理Statement Outline。
前提条件
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功能控制开关。取值范围如下：
|
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 |
|
Scope | 仅Index Hints需要提供该参数，分为如下三类：
说明 空串表示所有类型的Index Hints。 |
State | 本规则是否启用，取值范围：
|
Position |
|
Hint |
|
管理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
语法
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'");add_index_outline
语法
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 | 分为如下三类：
说明 空串表示所有类型的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'");preview_outline
语法
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)show_outline
语法
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或相应的表的次数。
del_outline
语法
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指定索引
语法
由于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');通过Index Hint指定索引
语法
通过使用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'");指定JOIN顺序
语法
该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)