本文介绍如何使用 Statement outline 功能。

支持版本

MySQL 8.0 版本，且内核版本为 3.0.6.1 及以上。

MySQL 5.7 版本，且内核版本为 2.0.6.2 及以上。

背景信息

Statement outline

实例运行过程中，一些 SQL 的执行计划可能会发生非预期的变化，造成稳定性风险。

例如：

在线业务使用 order by limit n 的用法，在 n 值比较小的时候频繁出现执行计划变动影响性能，

数据量变化时，统计数据也会发生改变。这些变化可能引起优化器的代价选择出现错误判断，选择了非最优的索引。

临时解决办法如实例通过调整 optimizer_switch 参数，或者通过修改业务 SQL 添加 hint 来绑定执行计划，对业务都不够友好。而 Statement outline 功能可将 SQL 的执行计划与 SQL 查询模板绑定，用户无需修改 SQL 即可将查询计划固化，避免出现上述风险。

术语说明

缩略语	英文全名	中文解释
Outline	Statement outline	执行计划固化，用于解决生产环境中 SQL 语句的执行计划经常会发生改变，而导致数据库不稳定的问题。
Package	Native package procedure framework	原生工具包，是一组预定义的存储过程（stored procedure）的逻辑集合，方便使用一个领域的相关存储过程。
Digest	SQL Digest	SQL 指纹，在 MySQL 8.0 中默认使用 SHA256 值，每条模板有唯一的值。
Digest Text	SQL Digest Text	SQL 模板，将 SQL 参数去除后的 SQL 语句。

相关参数

参数名	级别	取值范围	默认值	描述
opt_outline_enabled	GLOBAL	[ON\|OFF]	ON	用于设置 Statement outline 功能的开启与关闭。

前提条件

已创建实例，且实例状态为运行中。

Hint

MySQL 通过 hint 提供了语句维度的优化器控制，通过将 hint 添加至 SQL 语句，即可对 SQL 查询器的决策进行干预：

Statement outline 功能支持把两类 Hint 绑定至 SQL 模板中，从而达到查询计划固化的效果。支持的 Hint 类型与 MySQL 一致：

Index Hint

Optimizer Hint

SQL 语法接口

云数据库 veDB MySQL 版的内置了 Outline 相关的原生存储过程（Native Package）工具包，用户可通过调用 dbms_outline 相关接口进行 Outline 的操作。

insert_optimizer_outline

描述：增加 Optimizer Hints。

语法：

CALL dbms_outline.insert_optimizer_outline('<Schema_name>','<Digest>','<Query_block>','<Hint>','<Query>');

参数说明：

参数	说明
Schema_name	数据库名称。说明通过添加空<Schema_name>: ''，可将 Outline 应用于所有匹配的 Schema。
Digest	查询对应的 SQL 指纹。
Query_Block	需要应用该 Hint 的关键字（如 SELECT，UPDATE 等）编号，从 1 开始。
Hint	Hint 表示完整的 Hint 字符串，例如 /+ NO_ICP(t1, t2) /。
Query	需要添加 Statement outline 的原始 SQL 语句。

示例：

CALL dbms_outline.insert_optimizer_outline('outline_db', '', 1, '/*+ MAX_EXECUTION_TIME(1000) */', 'SELECT * FROM t1 WHERE id = 1');

insert_index_outline

描述：增加 Index Hints。

语法：

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

参数说明

参数	说明
Schema_name	数据库名称。说明通过添加空<Schema_name>: ''，可将 Outline 应用于所有匹配的 Schema。
Digest	查询对应的SQL 指纹。
Position	需要应用该 Hint 的表的位置，从1开始
Type	Index Hint 类型。取值为 USE INDEX、FORCE INDEX 或 IGNORE INDEX。
Hint	索引名字的列表，例如 idx_1,idx_2。
Scope	分为如下三类： FOR GROUP BY FOR ORDER BY FOR JOIN 说明空串表示所有类型的 Index Hints。
Query	需要添加 Statement outline 的原始 SQL 语句。

示例：

-- 使用原始 SQL 语句
CALL dbms_outline.insert_index_outline('outline_db', '', 1, 'FORCE INDEX', 'idx_1', '',"SELECT * FROM t1 WHERE t1.col1 = 1 AND t1.col2 = 'mytest'");
-- 使用 SQL 指纹
CALL dbms_outline.insert_index_outline('outline_db', 'b4369611be7ab2d27c85897632576a04bc08f50b928a1d735b62d0a140628c4c', 1, 'USE INDEX', 'idx_1', '', "");

check_outline

描述：验证 SQL 语句 Statement outline 的生效情况，如果找到了匹配值，该 SQL 模板将会应用指定的 Hint。

语法：

CALL dbms_outline.check_outline('<Schema_name>', '<Query>');

参数说明：

参数	说明
Schema_name	数据库名称。说明通过添加空 <Schema_name>: ''，可将 Outline 应用于所有匹配的 Schema。
Query	需要添加 Statement outline 的原始 SQL 语句。

示例：

CALL dbms_outline.check_outline('outline_db', "SELECT * FROM t1 WHERE t1.col1 =1 AND t1.col2 ='mytest'");
+------------+------------------------------------------------------------------+------------+------------+-------+---------------------+
| SCHEMA | DIGEST | BLOCK_TYPE | BLOCK_NAME | BLOCK | HINT |
+------------+------------------------------------------------------------------+------------+------------+-------+---------------------+
| outline_db | b4369611be7ab2d27c85897632576a04bc08f50b928a1d735b62d0a140628c4c | TABLE | t1 | 1 | USE INDEX (`idx_1`) |
+------------+------------------------------------------------------------------+------------+------------+-------+---------------------+

Explain：

通过 EXPLAIN 语句也可以查看 Outline 是否生效。

explain select * from t1 where t1.col1 =1 and t1.col2 ='mytest';
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | t1 | NULL | ref | idx_1 | idx_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 (`idx_1`) where ((`outline_db`.`t1`.`col1` = 1) and (`outline_db`.`t1`.`col2` = 'mytest')) |
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Explain Analyze：

MySQL 8.0 支持了 EXPLAIN ANALYZE 语法，允许用户实际执行查询并给出详细的执行信息。适用于需要对查询性能进行详细分析的场景。开启 Statement Outline 后，您可以在 Explain Analyze 中验证 outline 的性能。

mysql> explain analyze SELECT * FROM t1 WHERE t1.col1 =1 AND t1.col2 ='mytest';
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Filter: (t1.col2 = 'mytest') (cost=1.10 rows=1) (actual time=0.100..0.100 rows=0 loops=1) |
| -> Index lookup on t1 using idx_1 (col1=1) (cost=1.10 rows=1) (actual time=0.099..0.099 rows=0 loops=1) |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

display_outline

描述：查看系统中保存 Statement outline 以及生效情况。

语法：

CALL dbms_outline.display_outline();

示例：

CALL dbms_outline.display_outline();
+------+------------+------------------------------------------------------------------+-----------+-------+------+--------------------+------+----------+-----------------------------------------------------------------------------------------------------------------+
| ID | SCHEMA | DIGEST | TYPE | SCOPE | POS | HINT | HIT | OVERFLOW | DIGEST_TEXT |
+------+------------+------------------------------------------------------------------+-----------+-------+------+--------------------+------+----------+-----------------------------------------------------------------------------------------------------------------+
| 1 | outline_db | ab234071d1d3af13a775f45b532639203e1aa8c3ec63b289a75b9fb862c61c4c | USE INDEX | | 1 | idx_1 | 0 | 0 | SELECT * FROM `t1` WHERE `t1` . `id` = ? UNION SELECT * FROM `t1` WHERE `t1` . `col1` = ? AND `t1` . `col2` = ? |
| 2 | outline_db | ab234071d1d3af13a775f45b532639203e1aa8c3ec63b289a75b9fb862c61c4c | OPTIMIZER | | 2 | /*+ QB_NAME(q2) */ | 0 | 0 | SELECT * FROM `t1` WHERE `t1` . `id` = ? UNION SELECT * FROM `t1` WHERE `t1` . `col1` = ? AND `t1` . `col2` = ? |
+------+------------+------------------------------------------------------------------+-----------+-------+------+--------------------+------+----------+-----------------------------------------------------------------------------------------------------------------+

delete_outline

描述：删除内存和表中的某一条 Statement outline。

语法：

CALL dbms_outline.delete_outline(<Id>);

参数说明：

参数
说明
Id
Outline ID。

示例：

CALL dbms_outline.delete_outline(2);

如果删除的 Outline 不存在，会返回警告，使用 SHOW WARNINGS 查看警告内容。

-- 删除不存在的 outline
CALL dbms_outline.delete_outline(999999);
Query OK, 0 rows affected, 2 warnings (0.00 sec)
SHOW WARNINGS;
+---------+------+------------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------------+
| Warning | 7521 | Statement outline 999999 is not found in table |
| Warning | 7521 | Statement outline 999999 is not found in cache |
+---------+------+------------------------------------------------+
2 rows in set (0.00 sec)

常见场景

Index Hint

指定使用 t1 表的 idx_1 索引。

Optimizer Hint

使用 Nested-Loop join

示例

CALL dbms_outline.insert_optimizer_outline('outline_db', '', 1, '/*+ BNL(t1,t2) */',
"select t1.id, t2.id from t1,t2");
mysql> explain select t1.id, t2.id from t1,t2;
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+----------------------------------------------------+
| 1 | SIMPLE | t1 | NULL | index | NULL | ind_1 | 5 | NULL | 1 | 100.00 | Using index |
| 1 | SIMPLE | t2 | NULL | index | NULL | ind_1 | 5 | NULL | 1 | 100.00 | Using index; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+----------------------------------------------------+
2 rows in set, 1 warning (0.01 sec)
mysql> show warnings;
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note | 1003 | /* select#1 */ select /*+ BNL(`t1`@`select#1`) BNL(`t2`@`select#1`) */ `outline_db`.`t1`.`id` AS `id`,`outline_db`.`t2`.`id` AS `id` from `outline_db`.`t1` join `outline_db`.`t2` |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

示例说明：

BNL 表示 Block Nested Loop，这是一种表连接算法。其核心逻辑是：

将驱动表（如 t1）的数据按块（Block）加载到内存中。

在内存块中逐行与被驱动表（如 t2）的数据进行匹配。

适用于驱动表较小且被驱动表无高效索引的场景。

作用对象 BNL(t1, t2) 表示强制优化器在表 t1 和 t2 的关联操作中采用块嵌套循环连接算法。

子查询（subquery)相关的

设置子查询的 Query block 标识

CALL dbms_outline.insert_optimizer_outline('outline_db', '', 2, ' /*+ QB_NAME(subq1) */',
"SELECT * FROM t1 WHERE t1.col1 IN (SELECT col1 FROM t2)");

使用 SEMIJOIN 相关 HINT

CALL dbms_outline.insert_optimizer_outline('outline_db', '', 1, '/*+ SEMIJOIN(@subq1 MATERIALIZATION, DUPSWEEDOUT) */ ',
"SELECT * FROM t1 WHERE t1.col1 IN (SELECT col1 FROM t2)");

示例说明：

SEMIJOIN 表示该提示针对半连接操作，即通过子查询（如 IN 或 EXISTS）筛选主表数据时，仅匹配子查询中至少一条相关记录的场景。

@subq1 标识需要优化的子查询名称或别名，此处为上一个 Outline 指定的子查询名称。

MATERIALIZATION 物化策略：强制将子查询的结果临时存储在内存或磁盘中，避免重复计算。适用于子查询结果集较小但需要多次访问的场景。

DUPSWEEDOUT 去重策略：在子查询结果中消除重复值，确保主查询仅匹配唯一值。常用于子查询可能返回重复数据时优化性能。

效果验证

mysql> explain SELECT * FROM t1 WHERE t1.col1 IN (SELECT col1 FROM t2);
+----+--------------+-------------+------------+--------+---------------------+---------------------+---------+--------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------+-------------+------------+--------+---------------------+---------------------+---------+--------------------+------+----------+-------------+
| 1 | SIMPLE | t1 | NULL | ALL | ind_1 | NULL | NULL | NULL | 1 | 100.00 | Using where |
| 1 | SIMPLE | <subquery2> | NULL | eq_ref | <auto_distinct_key> | <auto_distinct_key> | 5 | outline_db.t1.col1 | 1 | 100.00 | NULL |
| 2 | MATERIALIZED | t2 | NULL | index | ind_1 | ind_1 | 5 | NULL | 1 | 100.00 | Using index |
+----+--------------+-------------+------------+--------+---------------------+---------------------+---------+--------------------+------+----------+-------------+
3 rows in set, 1 warning (0.00 sec)
mysql> show warnings;
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note | 1003 | /* select#1 */ select /*+ SEMIJOIN(@`subq1` MATERIALIZATION, DUPSWEEDOUT) */ `outline_db`.`t1`.`id` AS `id`,`outline_db`.`t1`.`col1` AS `col1`,`outline_db`.`t1`.`col2` AS `col2` from `outline_db`.`t1` semi join (`outline_db`.`t2`) where (`<subquery2>`.`col1` = `outline_db`.`t1`.`col1`) |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

给语句单独设置运行时变量

可以通过 SET_VAR 单独对语句设置变量。

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

Outline 示例：

设置最大查询时间 MAX_EXECUTION_TIME。

CALL dbms_outline.insert_optimizer_outline("outline_db", '', 1, '/*+ MAX_EXECUTION_TIME(1000) */',
"select * from t1 where id = 1");

设置外键约束 foreign_key_checks。

CALL dbms_outline.insert_optimizer_outline("outline_db", '', 1, '/*+ SET_VAR(foreign_key_checks=OFF) */',
"select * from t1 where id = 1");

效果验证

mysql> explain select * from t1 where id = 1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | no matching row in const table |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
1 row in set, 1 warning (0.01 sec)
mysql> show warnings;
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note | 1003 | /* select#1 */ select /*+ MAX_EXECUTION_TIME(1000) SET_VAR(foreign_key_checks='OFF') */ NULL AS `id`,NULL AS `col1`,NULL AS `col2` from `outline_db`.`t1` where multiple equal(1, NULL) |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

FAQ

veDB MySQL 如何处理互相矛盾的 Outline？

如果用户插入了两条矛盾的 Outline，实际起作用的 hint 由 MySQL 的 hint resolve 逻辑决定。详情请参考 MySQL文档。

示例

mysql> CALL dbms_outline.display_outline();
+------+------------+------------------------------------------------------------------+-----------+-------+------+-------------------------------------------------------+------+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ID | SCHEMA | DIGEST | TYPE | SCOPE | POS | HINT | HIT | OVERFLOW | DIGEST_TEXT |
+------+------------+------------------------------------------------------------------+-----------+-------+------+-------------------------------------------------------+------+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 14 | | d9bc7ca4b466e3eff5c35133b62328b340e7996a20c0a3278f44e81908516796 | OPTIMIZER | | 2 | /*+ QB_NAME(subq2) */ | 6 | 0 | SELECT * FROM `db1` . `t1` WHERE `t1` . `col1` IN ( SELECT `col1` FROM `db1` . `t2` ) |
| 15 | | d9bc7ca4b466e3eff5c35133b62328b340e7996a20c0a3278f44e81908516796 | OPTIMIZER | | 2 | /*+ NO_INDEX(t1) */ | 2 | 0 | SELECT * FROM `db1` . `t1` WHERE `t1` . `col1` IN ( SELECT `col1` FROM `db1` . `t2` ) |
| 16 | | d9bc7ca4b466e3eff5c35133b62328b340e7996a20c0a3278f44e81908516796 | OPTIMIZER | | 2 | /*+ INDEX(t1) */ | 2 | 0 | SELECT * FROM `db1` . `t1` WHERE `t1` . `col1` IN ( SELECT `col1` FROM `db1` . `t2` ) |
| 17 | | d9bc7ca4b466e3eff5c35133b62328b340e7996a20c0a3278f44e81908516796 | OPTIMIZER | | 2 | /*+ INDEX(t2) */ | 0 | 0 | SELECT * FROM `db1` . `t1` WHERE `t1` . `col1` IN ( SELECT `col1` FROM `db1` . `t2` ) |
| 18 | | d9bc7ca4b466e3eff5c35133b62328b340e7996a20c0a3278f44e81908516796 | OPTIMIZER | | 2 | /*+ NO_INDEX(t2) */ | 0 | 0 | SELECT * FROM `db1` . `t1` WHERE `t1` . `col1` IN ( SELECT `col1` FROM `db1` . `t2` ) |
+------+------------+------------------------------------------------------------------+-----------+-------+------+-------------------------------------------------------+------+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
mysql> CALL dbms_outline.check_outline('', 'SELECT * FROM db1.t1 WHERE t1.col1 IN (SELECT col1 FROM db1.t2)');
+--------+------------------------------------------------------------------+------------+------------+-------+----------------------------------------------------+
| SCHEMA | DIGEST | BLOCK_TYPE | BLOCK_NAME | BLOCK | HINT |
+--------+------------------------------------------------------------------+------------+------------+-------+----------------------------------------------------+
| | d9bc7ca4b466e3eff5c35133b62328b340e7996a20c0a3278f44e81908516796 | QUERY | | 1 | /*+ NO_INDEX(`t1`@`subq2`) INDEX(`t2`@`subq2`) */ |
| | d9bc7ca4b466e3eff5c35133b62328b340e7996a20c0a3278f44e81908516796 | QUERY | | 2 | /*+ QB_NAME(`subq2`) */ |
+--------+------------------------------------------------------------------+------------+------------+-------+----------------------------------------------------+
2 rows in set, 2 warnings (0.00 sec)
mysql> show warnings;
+---------+------+-----------------------------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------------------------+
| Warning | 3126 | Hint INDEX(`t1` ) is ignored as conflicting/duplicated |
| Warning | 3126 | Hint NO_INDEX(`t2` ) is ignored as conflicting/duplicated |
+---------+------+-----------------------------------------------------------+
2 rows in set (0.00 sec)

示例说明

Query block 2 中只有 t2，但 Outline 指定了对于 t1 的 index，而且对 t2 的 index 同时指定了 NO_INDEX 和 INDEX。这时候虽然所有 hint 都能匹配，但是最后生效的是第一个插入的 Outline：NO_INDEX(t1) 与 INDEX(t2)。

如果出现矛盾的 Outline，会返回警告，使用 SHOW WARNINGS 查看警告内容。

Outline 在什么节点添加、生效？

Outline 相关管理 SQL 接口只能在主节点执行，调用下列 SQL 接口之后，Outline 的修改自动会同步至所有只读节点并且生效：

insert_optimizer_outline

insert_index_outline

delete_outline

Outline 验证以及显示接口可在任意节点执行：

display_outline

check_outline

开启 Statement outline 后，对数据库性能是否有影响？

开启 Statement outline 后，SQL 执行时会计算一次 SQL 指纹。

对性能几乎无影响，在受 SQL 指纹计算影响最大的 CPU-bound 性能测试中，Outline 对性能的影响 < 3%。

备份恢复会恢复系统中 Outline 吗？

如果您选择了「恢复到新实例」，会从新实例全量恢复实例数据到指定时间点，系统中保存的 Outline 也表将会回到指定时间点的状态。

如果您选择了「恢复到原实例」，您将指定用户库表进行恢复，此时 Outline 将保持原样。

升级 MySQL 版本对 Outline 有何影响？

MySQL 5.7 与 MySQL 8.0 对 SQL 指纹的计算方式不同，因此两个版本的 Outline 互不兼容。

如果您已在 MySQL 5.7 上添加了若干 Outline，在将 MySQL 实例的大版本从 5.7 升级到 8.0 时，云数据库 veDB MySQL 版将自动为您迁移相关 Outline。迁移完成后，生效 Outline 的 SQL 指纹将发生改变。

不同 MySQL 大版本支持的 HINT 类型、优化器决策有较大区别区别，MySQL 5.7 上的 Outline 在 MySQL 8.0 版本中未必是最优，并且可能会失效。我们建议您在在升级后，确认并验证所有 Outline，以免您的 SQL 查询使用了非预期的执行计划。