【华为云MySQL技术专栏】GaussDB(for MySQL) DBA运维工具—Statement Outline

1．背景介绍

在生产环境，MySQL数据库实例运行过程中，一些SQL语句会发生执行计划的变化，导致增加了数据库稳定性的风险，这里边有几个因素和场景，比如：表结构发生变化、某些索引的增加或删减、实例升级迁移等过程中MySQL自身优化器的行为和算法变化，以及表数据的变化等。

针对上述查询语句执行计划不稳定的风险，MySQL提供了查询优化提示机制(Hints)解决了该问题。MySQL Hints，是一组特殊的注释或者指令，可以直接嵌入到SQL语句中，改变MySQL优化器的默认行为。

如下所示，MySQL Hints可以添加FORCE INDEX(idx1)强制t1表使用idx1索引进行表扫描。MySQL支持两种类型的Hints，分别为Index Hints[1]和Optimizer Hints[2]，详情请参考官网文档说明。

SELECT c1 FROM t1 FORCE INDEX(idx1) WHERE c2 = 1;

上述示例表明：MySQL Hints 需要对 SQL 语句进行修改，但客户业务的调整相对繁琐，因此通常不建议通过修改业务语句来解决性能下降的问题。

针对上述的问题，GaussDB(for MySQL)设计了一套利用MySQL Hints来稳定执行计划的方法，称为Statement outline。该方法解决了客户在不改变业务的情况下，通过配置对应的outline规则，来稳定语句的执行计划。

2．Statement Outline原理

Statement outline是通过提前设置一些Hint规则存储在数据库系统表中。当执行某条 SQL 语句时，系统会查询相应的存储数据库。如果找到匹配的规则，系统将这些符合条件的Outline 转换为 MySQL 内部的 Hint 数据结构，并将其应用于相应的数据结构。在后续的语句优化阶段，优化器将访问这些 Hint，以选择最佳执行计划，从而使客户无需修改业务语句即可为其 SQL 语句绑定优化规则。

内部转换逻辑如下所示。

Query1 : select * from t1 where a = 5 
Outline : hint : force index(key1) for Query1.

客户端执行Query : select * from t1 where a = 5 
===> 
MySQL内部真实执行语句：select * from t1 force index(key1) where a = 5

3 Statement Outline的使用

3.1 使用场景

Statement Outline支持MySQL8.0提供的所有Hints，可以解决很多种执行计划不稳定的场景。下面是一些常见的使用场景：

• 可以应用在SELECT, UPDATE, INSERT, REPLACE和DELETE类型的语句中。

• 可以设置表的索引选择，解决索引选择不当的问题。

• 可以设置session级别的参数变量值，只影响该语句，不会影响该session上的其他语句。

• 可以设置JOIN的表连接顺序和semijoin的优化策略选择，解决因连接顺序不佳导致的性能问题。

3.2 Statement Outline的开关

rds_opt_outline_enabled是Statement Outline功能开关。当开关设置为ON时，语句执行到解析阶段就会去系统表 "mysql.outline"中查找该语句是否有匹配的outline规则，然后将outline规则添加到对应的数据结构中，语句在优化阶段就会参考outline规则来选定执行计划。该开关不控制outline规则的添加和删除。

3.3 Outline管理接口

为了客户更加方便的管理Statement outline，我们提供了6个内置的存储过程接口：

// 添加Optimizer Hint类型的outline
dbms_outln.add_optimizer_outline();
// 添加Index Hint类型的outline 
dbms_outln.add_index_outline();
// 预览一个SQL语句命中outline的情况
dbms_outln.preview_outline();
// 展示内存中所有outline
dbms_outln.show_outline();
// 删除一条outline
dbms_outln.del_outline(); 
// 更新内存的outline，从mysql.outline表中重新加载到内存里
dbms_outln.flush_outline();

以上接口中，除了show_outline和preview_outline接口外，其他的接口操作只能在主节点上执行，主节点执行完成后会自动同步到其他节点。

为了方便介绍接口的使用方法，这里准备了两个测试表。

create table t1 (
  id int not null, col1 int, col2 varchar(100),
  primary key(id),
  key idx1(col1),
  key idx2(col2)
) engine = innodb;

create table t2 (
  id int not null, col1 int, col2 varchar(100),
  primary key(id),
  key idx1(col1),
  key idx2(col2)
) engine = innodb;

3.3.1 dbms_outln.add_index_outline

语法：

call dbms_outln.add_index_outline(<schema>,<digest>,<position>,<type>,<hint>, <scope>, <query>);

参数说明：

示例：

测试语句：

select * from t1 where t1.col1 > 5 and t1.col2 ='xxx';

使用idx1索引：

// 1. 添加对应的outline规则
call dbms_outln.add_index_outline('test_db', '', 1, 'FORCE INDEX', 'idx1', '', "select * from t1 where t1.col1 > 5 and t1.col2 = 'xxx'");
// 2. 查看是否添加成功
mysql> call dbms_outln.show_outline();
+------+---------+------------------------------------------------------------------+-------------+-------+------+------+------+----------+------------------------------------------------------------------+
| ID   | SCHEMA  | DIGEST                                                           | TYPE        | SCOPE | POS  | HINT | HIT  | OVERFLOW | DIGEST_TEXT                                                      |
+------+---------+------------------------------------------------------------------+-------------+-------+------+------+------+----------+------------------------------------------------------------------+
|    4 | test_db | ee4331923bbcb2d2c69a7200cb11ef688179f9a1e0414826b8aac1702d30f09c | FORCE INDEX |       |    1 | idx1 |    0 |        0 | SELECT * FROM `t1` WHERE `t1` . `col1` > ? AND `t1` . `col2` = ? |
+------+---------+------------------------------------------------------------------+-------------+-------+------+------+------+----------+------------------------------------------------------------------+
1 row in set (0.01 sec)

验证outline：

添加outline规则后，为了验证是否能生效，提供了两种方法：

第一种，使用dbms_outln.preview_outline()接口进行预览查看，可以查看有多少条outline匹配得上；

第二种，直接使用explain查看语句的执行计划，查看执行计划是否满足要求，并且explain执行完成后，可以通过show warnings查看具体的SQL语句，查看是否添加上对应的Hints。

// preview_outline结果可以看出有一条outline规则是符合对应语句的
mysql> call dbms_outln.preview_outline('test_db', "select * from t1 where t1.col1 > 5 and t1.col2 = 'xxx'");
+---------+------------------------------------------------------------------+------------+------------+-------+----------------------+
| SCHEMA  | DIGEST                                                           | BLOCK_TYPE | BLOCK_NAME | BLOCK | HINT                 |
+---------+------------------------------------------------------------------+------------+------------+-------+----------------------+
| test_db | ee4331923bbcb2d2c69a7200cb11ef688179f9a1e0414826b8aac1702d30f09c | TABLE      | t1         |     1 | FORCE INDEX (`idx1`) |
+---------+------------------------------------------------------------------+------------+------------+-------+----------------------+
1 row in set (0.00 sec)

// 查看执行计划，最终t1表的索引走的是idx1，
mysql> explain select * from t1 where t1.col1 > 5 and t1.col2 = 'xxx';
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | range | idx1          | idx1 | 5       | NULL |    1 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

// 查看内部执行的语句，确实将对应的Hint添加到语句中了
mysql> show warnings;
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                                                            |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select `test_db`.`t1`.`id` AS `id`,`test_db`.`t1`.`col1` AS `col1`,`test_db`.`t1`.`col2` AS `col2` from `test_db`.`t1` FORCE INDEX (`idx1`) where ((`test_db`.`t1`.`col2` = 'xxx') and (`test_db`.`t1`.`col1` > 5)) |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

3.3.2 dbms_outln.add_optimzier_outline

语法：

dbms_outln.add_optimizer_outline(<Schema_name>,<Digest>,<Query_block>,<Hint>,<Query>);

参数说明：

示例：

测试语句：

select * from t1 where col1 = 1 and id in (select id from t2 where col1 = 2 and col2 = 'xxx');

指定join的顺序：

// 1. 添加outline
call dbms_outln.add_optimizer_outline('test_db', '', 1, '/*+ join_order(t2, t1)*/', "select * from t1 where col1 = 1 and id in (select id from t2 where col1 = 2 and col2 = 'xxx')");

// 2. 预览是否生效
mysql> call dbms_outln.preview_outline('test_db', "select * from t1 where col1 = 1 and id in (select id from t2 where col1 = 2 and col2 = 'xxx')");
+---------+------------------------------------------------------------------+------------+------------+-------+-------------------------------------------+
| SCHEMA  | DIGEST                                                           | BLOCK_TYPE | BLOCK_NAME | BLOCK | HINT                                      |
+---------+------------------------------------------------------------------+------------+------------+-------+-------------------------------------------+
| test_db | 659a7c3116442066cdf827284c660237eb84c7edf5d966bc86aeef98fed5cd4b | QUERY      |            |     1 | /*+ JOIN_ORDER(@`select#1` `t2`,`t1`) */  |
+---------+------------------------------------------------------------------+------------+------------+-------+-------------------------------------------+
1 row in set (0.00 sec)

// 3. 查看执行计划，最终的执行计划的join顺序为t2,t1
mysql> explain select * from t1 where col1 = 1 and id in (select id from t2 where col1 = 2 and col2 = 'xxx');
+----+-------------+-------+------------+--------+-------------------+---------+---------+---------------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys     | key     | key_len | ref           | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+-------------------+---------+---------+---------------+------+----------+-------------+
|  1 | SIMPLE      | t2    | NULL       | ref    | PRIMARY,idx1,idx2 | idx1    | 5       | const         |    1 |   100.00 | Using where |
|  1 | SIMPLE      | t1    | NULL       | eq_ref | PRIMARY,idx1      | PRIMARY | 4       | test_db.t2.id |    1 |   100.00 | Using where |
+----+-------------+-------+------------+--------+-------------------+---------+---------+---------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

// 4. 查看内部执行语句
mysql> show warnings;
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                                                                                                                                                                                    |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select /*+ JOIN_ORDER(@`select#1` `t2`,`t1`) */ `test_db`.`t1`.`id` AS `id`,`test_db`.`t1`.`col1` AS `col1`,`test_db`.`t1`.`col2` AS `col2` from `test_db`.`t2` join `test_db`.`t1` where ((`test_db`.`t1`.`id` = `test_db`.`t2`.`id`) and (`test_db`.`t2`.`col2` = 'xxx') and (`test_db`.`t2`.`col1` = 2) and (`test_db`.`t1`.`col1` = 1)) |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

3.3.3 dbms_outln.preview_outline

dbms_outln.preview_outline()用于使用具体 SQL 语句，查看匹配Outline的情况，用于手动验证。

语法和参数：

call dbms_outln.preview_outline(<schema>, <query>);
说明：其中schema : 表示数据库名称；query：查询语句

示例如上展示，此处不再赘述。

3.3.4 dbms_outln.show_outline

dbms_outln.show_outline() 展示内存中有效的outline的命中情况。该函数是没有参数的。

示例：

mysql> call dbms_outln.show_outline();
+------+---------+------------------------------------------------------------------+-------------+-------+------+--------------------------+------+----------+-----------------------------------------------------------------------------------------------------------+
| ID   | SCHEMA  | DIGEST                                                           | TYPE        | SCOPE | POS  | HINT                     | HIT  | OVERFLOW | DIGEST_TEXT                                                                                               |
+------+---------+------------------------------------------------------------------+-------------+-------+------+--------------------------+------+----------+-----------------------------------------------------------------------------------------------------------+
|    5 | test_db | 659a7c3116442066cdf827284c660237eb84c7edf5d966bc86aeef98fed5cd4b | OPTIMIZER   |       |    1 | /*+ join_order(t2, t1)*/ |    2 |        0 | SELECT * FROM `t1` WHERE `col1` = ? AND `id` IN ( SELECT `id` FROM `t2` WHERE `col1` = ? AND `col2` = ? ) |
|    4 | test_db | ee4331923bbcb2d2c69a7200cb11ef688179f9a1e0414826b8aac1702d30f09c | FORCE INDEX |       |    1 | idx1                     |    2 |        0 | SELECT * FROM `t1` WHERE `t1` . `col1` > ? AND `t1` . `col2` = ?                                          |
+------+---------+------------------------------------------------------------------+-------------+-------+------+--------------------------+------+----------+-----------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

返回结果里边有两个字段，HIT表示outline命中的次数；OVERFLOW表示outline hint没有找到query block或者相应的table的次数。

3.3.5 dbms_outln.del_outline

dbms_outln.del_outline() 可以删除内存和表中的某一条outline。

语法和参数：

call dbms_outln.del_outline(outline_id);
说明：outlinde_id是来自mysql.outline表里的id列的值。

举例：

mysql> call dbms_outln.del_outline(4);
Query OK, 0 rows affected (0.01 sec)

mysql> call dbms_outln.del_outline(7);
Query OK, 0 rows affected, 2 warnings (0.00 sec)
mysql> show warnings;
+---------+------+-------------------------------------------+
| Level   | Code | Message                                   |
+---------+------+-------------------------------------------+
| Warning | 7534 | Statement outline 7 is not found in table |
| Warning | 7534 | Statement outline 7 is not found in cache |
+---------+------+-------------------------------------------+
2 rows in set (0.00 sec)

如果删除的outline_id不存在，系统会报warnings, 显示该id的outline是不存在的。

3.3.6 dbms_outln.flush_outline

dbms_outln.flush_outline()支持清理cache中outline，并从mysql.outline表中重新load。如果用户直接修改表来加载 outline，需要手动执行该接口重新加载表的数据到内存中。

示例：

直接修改id为5的outline的schema_name：

mysql> select id, schema_name, hint  from mysql.outline;
+----+-------------+--------------------------+
| id | schema_name | hint                     |
+----+-------------+--------------------------+
|  5 | test_db     | /*+ join_order(t2, t1)*/ |
+----+-------------+--------------------------+
1 row in set (0.00 sec)

mysql> update mysql.outline set schema_name = 'outline_db' where id = 5;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select id, schema_name, hint  from mysql.outline;
+----+-------------+--------------------------+
| id | schema_name | hint                     |
+----+-------------+--------------------------+
|  5 | outline_db  | /*+ join_order(t2, t1)*/ |
+----+-------------+--------------------------+
1 row in set (0.00 sec)

查看内存的outline情况：

mysql> call dbms_outln.show_outline();
+------+---------+------------------------------------------------------------------+-----------+-------+------+--------------------------+------+----------+-----------------------------------------------------------------------------------------------------------+
| ID   | SCHEMA  | DIGEST                                                           | TYPE      | SCOPE | POS  | HINT                     | HIT  | OVERFLOW | DIGEST_TEXT                                                                                               |
+------+---------+------------------------------------------------------------------+-----------+-------+------+--------------------------+------+----------+-----------------------------------------------------------------------------------------------------------+
|    5 | test_db | 659a7c3116442066cdf827284c660237eb84c7edf5d966bc86aeef98fed5cd4b | OPTIMIZER |       |    1 | /*+ join_order(t2, t1)*/ |    3 |        0 | SELECT * FROM `t1` WHERE `col1` = ? AND `id` IN ( SELECT `id` FROM `t2` WHERE `col1` = ? AND `col2` = ? ) |
+------+---------+------------------------------------------------------------------+-----------+-------+------+--------------------------+------+----------+-----------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

可以看到，内存的outline没有更新到最新，跟mysql.outline的规则不同。

刷新内存outline：

mysql> call dbms_outln.flush_outline();
Query OK, 0 rows affected (0.00 sec)

mysql> call dbms_outln.show_outline();
+------+------------+------------------------------------------------------------------+-----------+-------+------+--------------------------+------+----------+-----------------------------------------------------------------------------------------------------------+
| ID   | SCHEMA     | DIGEST                                                           | TYPE      | SCOPE | POS  | HINT                     | HIT  | OVERFLOW | DIGEST_TEXT                                                                                               |
+------+------------+------------------------------------------------------------------+-----------+-------+------+--------------------------+------+----------+-----------------------------------------------------------------------------------------------------------+
|    5 | outline_db | 659a7c3116442066cdf827284c660237eb84c7edf5d966bc86aeef98fed5cd4b | OPTIMIZER |       |    1 | /*+ join_order(t2, t1)*/ |    0 |        0 | SELECT * FROM `t1` WHERE `col1` = ? AND `id` IN ( SELECT `id` FROM `t2` WHERE `col1` = ? AND `col2` = ? ) |
+------+------------+------------------------------------------------------------------+-----------+-------+------+--------------------------+------+----------+-----------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

调用flush_outline()接口后，内存statement outlines就更新为最新的。

3.4 持久化

为了表示和抽象这些 Hint，持久化 outline，可以增加一个系统表 mysql.outline。其结构如下：

CREATE TABLE `outline` (
  `Id` bigint 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 NOT NULL,
  `Hint` text COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0 ROW_FORMAT=DYNAMIC COMMENT='Statement outline';

• Digest/Digest_text

Outline根据语句的特征进行匹配，这一特征为 Digest Text。通过对 Digest Text 进行哈希计算，可以得到一个 64 字节的哈希字符串。在语句解析完成后，将使用 [schema + digest] 作为hash key，以查询匹配的 Outlines。

• Type

Type即为Outline规则的类型。所有的Optimizer Hints的type统一为OPTIMIZER，Index Hints 分为三类，分别为IGNORE INDEX、USE INDEX和FORCE INDEX。

• Scope

只针对Index Hints而言，Scope表示作用域。如果Scope参数为空，表示ALL。

• Position

在Optimizer Hints中，position 表示 Query Block，因为所有的 Optimizer Hint 必须作用于 Query Block，且从 1 开始计数。而在 Index Hints 中，position 则表示表的位置，同样从 1 开始。

• Hint

对于Index Hints而言，Hint可以表示索引名字的列表，比如 “ind_1, ind_2”；对于Optimizer Hints来讲，可以表示完整的 Hint 字符串，比如：“/*+ MAX_EXECUTION_TIME(1000) */”。

4 总结

生产环境中，客户的业务语句执行计划经常发生变化，导致性能下降。Statement outline功能是GaussDB(for MySQL)产品的运维工具的一员猛将，客户可以在不修改业务的情况下，解决业务语句不稳定的问题。

5 参考文献

[1] 官网Index Hints文档 :

https://dev.mysql.com/doc/refman/8.4/en/index-hints.html

[2] 官网Optimizer Hints文档：

https://dev.mysql.com/doc/refman/8.4/en/optimizer-hints.html